MySQL主從同步（一主一從、一主多從、主從從）等結構的概述與配置

前言：前面我們瞭解了MySQL資料庫的基礎知識，今天及接下來的五天時間裡我會給大家帶來MySQL進階方面的一些學習總結，如有不足，還請大家留言指出；下面我們就開始今天的內容。
**

部署mysql主從同步結構

**
 主從同步概述
MYSQL主從同步
客戶端訪問主庫，從庫複製、同步主庫的所有操作
單項複製時，建議將從庫設定為只讀

主從同步原理——從庫 從主機上同步資料的工作過程
Master，記錄資料更改操作
— 啟用binlog日誌
— 設定binlog日誌格式
— 設定server_id
Slave執行2個執行緒
— Slave_IO：複製master主機binlog日誌檔案裡的SQL到本機的relay-log檔案裡
— Slave_SQL：執行本機relay-log檔案裡的SQL語句，重現Master的資料操作

 構建主從同步
基本構建思路

1. 確保資料相同
   – 從庫必須要有主庫上的資料。

2. 配置主伺服器
   – 啟用 binlog 日誌及設定格式,設定 server_id, 授權使用者

3. 配置從伺服器
   – 設定 server_id ,指定主資料庫伺服器資訊

4. 測試配置
   – 客戶端連線主庫,寫入的資料,在連線從庫的時候也確保資料一致

Master伺服器
– 應包括希望同步的所有庫
– 對採用 MyISAM 的庫,可離線備份
主庫配置選項

Slave 伺服器
– 離線匯入由 Master 提供的備份
– 清空同名庫(若有的話
從庫配置選項
主從配置常用引數
寫在主庫配置檔案裡引數（對所有從庫都有效）
binlog_do_db=庫名列表 //允許同步的庫
binlog_ignore_db=庫名列表 //不允許同步的庫

寫在從庫配置檔案裡引數（只針對從庫本機有效）
replicate_do_db=庫名列表 //指定只同步的庫
replicate_ignore_db=庫名列表 //指定不同步的庫

 主從同步結構模式
基本應用：
— 單項複製：一主一從
擴充套件應用：
— 一主多從
— 鏈式複製：主從從
— 主主（互為主從）

 mysql主從同步配置步驟
配置主庫
啟用binlog日誌
使用者授權
檢視當前正在使用的binlog日誌
配置從庫
指定server_id
指定主庫資訊
啟動slave程式
檢視slave程式的狀態資訊
【一主一從同步結構配置】
確保資料相同
主庫：192.168.4.51
啟用 binlog 日誌及設定格式,設定 server_id

[root@mysql51 ~]# vim /etc/my.cnf
[mysqld]
server_id=51
log_bin=mysql51   //binlog日誌檔名稱
binlog_format="mixed"   //日誌檔案格式
[root@mysql51 ~]# systemctl restart mysqld
[root@mysql51 ~]# ls /var/lib/mysql/mysql51*

授權使用者

[root@mysql51 ~]# mysql  -uroot -p123asd...A
mysql> grant  replication slave on *.*  to  repluser@"%"  identified  by  "123asd...A";
mysql> select  host,user  from mysql.user where user='repluser';
mysql> show master status;     //檢視使用的日誌檔案及偏移量

從庫：192.168.4.52

[root@mysql52 ~]# vim /etc/my.cnf
[mysqld]
server_id=52
[root@mysql52 ~]# systemctl restart mysqld
[root@mysql52 ~]# mysql  -u root -p123asd...A
mysql> show master status;   
mysql> change  master to master_host='192.168.4.51',    //主庫ip地址
    -> master_user='repluser',       //出庫授權使用者名稱
    -> master_password='123asd...A',  		//授權使用者密碼
    -> master_log_file='mysql51.000001',		//日誌檔案
	-> master_log_pos=441;			//偏移檔案

mysql> start slave;     	//啟動salve程式
mysql> show slave status\G;    //檢視slave狀態
Slave_IO_Running: Yes    //IO執行緒狀態
Slave_SQL_Running: Yes	//SQL執行緒狀態

如果 Slave_IO_Running為NO，檢視下面Last_IO_Error: Fatal error:的報錯資訊

[root@mysql52 mysql]# vim auto.cnf  //此配置檔案檢視sql資料庫的uuid

從伺服器相關檔案

測試主從同步配置：在及客戶端50主機連線主機51對資料庫做訪問
• 在 Master 上操縱資料
– 新建 newdb 庫、 newtbl 表
– 任意插入幾條表記錄
• 在 Slave 上檢視資料更改情況
– 確認新建的 newdb 庫、 newtbl 表
– 列出 newtbl 表的所有記錄

1. 在主庫51上新增授權使用者給客戶端50主機連線使用

    mysql> create  database db1;
    mysql> create table db1.a(id int);
    mysql> grant select,insert,update on db1.* to admin@"%" identified by "123asd...A";
   

2. 在客戶端50主機連線主庫伺服器51，對記錄做訪問

    [root@client50 ~]# mysql  -uadmin  -h192.168.4.51  -p123asd...A
    mysql> select @@hostname;
    mysql> insert into db1.a  values(111),(1231),(324);
    mysql> select * from  db1.a;
   

3. 再從庫伺服器52本機也能檢視到相同的資料為成功

    [root@mysql52 mysql]# mysql  -u root -p123asd...A
    mysql> select  * from  db1.a;
   

【一主多從 同步結構配置】
把資料庫伺服器53 也配置為51的從資料庫伺服器
主庫主機51 已經是主資料庫伺服器，無需再做配置
配置從庫53
與主庫資料一致（把主庫資料的完全備份拷貝到本機執行恢復）

[root@mysql51 ~]# mysqldump  -uroot -p123asd...A  db1  >  /root/db1.sql
[root@mysql51 ~]# scp  db1.sql   192.168.4.53:/root/

[root@mysql53 ~]# mysql  -u root -p123asd...A
mysql> create database db1;
mysql> use db1;
mysql> source  /root/db1.sql;
mysql> select * from db1.a;

[root@mysql53 ~]# vim /etc/my.cnf
[mysqld]
server_id=53
[root@mysql53 ~]# systemctl restart mysqld

檢測授權使用者能否連線資料庫

[root@mysql53 ~]# mysql -h192.168.4.51  -urepluser  -p123asd...A

資料管理員指定主庫資料資訊

[root@mysql53 ~]# mysql -uroot -p123asd...A
mysql> change master  to
	-> master_host='192.168.4.51',
	-> master_user='repluser',
	-> master_password='123asd...A',
	-> master_log_file='mysql51.000001',
	-> master_log_pos=1337;

master_log_pos=1337:從51主機上執行sql命令mysql> show master status;獲取
檢視slave程式的狀態資訊

mysql> start slave;
mysql> show slave status\G;
        Slave_IO_Running: Yes
        Slave_SQL_Running: Yes

測試
客戶端50連線主機51寫入的資料在從庫52和53主機上也能檢視到資料，即成功

[root@client50 ~]# mysql  -uadmin  -h192.168.4.51  -p123asd...A
mysql> insert into db1.a values(66066);

51和52主機：登入資料庫，執行mysql> select * from db1.a;檢視是否有插入的資料

配置從庫暫時不同步主機資料：停止從庫的slave程式即可
把從庫還原成獨立的資料伺服器：讓主機53不做51的從庫
1.停止服務

[root@mysql53 ~]# systemctl stop mysqld    

2.刪除從庫檔案

[root@mysql53 ~]# rm -rf /var/lib/mysql/master.info 
[root@mysql53 ~]# rm -rf /var/lib/mysql/mysql53-relay-bin.*
[root@mysql53 ~]# rm -rf /var/lib/mysql/relay-log.info 

3.修改配置

[root@mysql53 ~]# vim /etc/my.cnf
[mysqld]
#server_id=53    //刪除此行

4.重起服務檢視主從狀態

[root@mysql53 ~]# systemctl start mysqld
[root@mysql53 ~]# mysql -uroot -p123asd...A -e "show slave  status\G"

【主從從同步結構配置】
配置主庫51：
啟用binlog日誌 使用者授權 檢視日誌資訊

配置從庫52（既做主又做從）
主配置檔案裡：要啟用log_slave_updates配置
做主庫的配置：啟用binlog日誌 使用者授權 檢視日誌資訊

[root@mysql52 mysql]# vim /etc/my.cnf
[mysqld]
server_id=52
log_slave_updates     //記錄從庫更新，允許鏈路複製
log_bin=slave52
binlog_format="mixed"
[root@mysql52 mysql]# systemctl restart  mysqld
[root@mysql52 mysql]# mysql -uroot -p123asd...A
mysql> grant replication slave on *.*  to repluser2@"%" identified by  "123asd...A";
mysql> show master status;
mysql> show slave status\G;
        Slave_IO_Running: Yes
        Slave_SQL_Running: Yes

做從庫的配置：指定server_id 指定主庫資訊
啟動slave程式 檢視slave程式狀態資訊

把主機53配置為52的從庫伺服器
在沒有配置為從庫之前要與主庫資料一致
指定server_id 、 指定主庫資訊
啟動slave 程式 、 看slave程式狀態資訊

[root@mysql53 ~]# vim /etc/my.cnf
[mysqld]
server_id=53

[root@mysql53 ~]# systemctl restart mysqld
[root@mysql53 ~]# mysql -urepluser2 -h192.168.4.52 -p123asd...A

[root@mysql53 ~]# mysql -uroot -p123asd...A
mysql> change  master to master_host='192.168.4.52',
    -> master_user='repluser2',
    -> master_password='123asd...A',
    -> master_log_file='slave52.000001',
	-> master_log_pos=442;
mysql> start slave;
mysql> show slave  status\G;
        Slave_IO_Running: Yes
        Slave_SQL_Running: Yes

在客戶端50上連線主機51寫入資料，在52和53主機上都可以檢視到即為成功

【配置主主結構】：
提示：把資料庫伺服器54 55 配置為主主結構
把資料庫伺服器51 52 53 恢復為獨立的資料庫
[此部分操作借鑑上面的知識綜合即可]

 mysql主從同步資料複製模式
複製模式介紹
• 非同步複製( Asynchronous replication )
– 主庫在執行完客戶端提交的事務後會立即將結果返給客戶端,並不關心從庫是否已經接收並處理。
• 全同步複製( Fully synchronous replication )
– 當主庫執行完一個事務,所有的從庫都執行了該事務才返回給客戶端。
• 半同步複製( Semisynchronous replication )
– 介於非同步複製和全同步複製之間,主庫在執行完客戶端提交的事務後不是立刻返回給客戶端,而是等待至少一個從庫接收到並寫到 relay log 中才返回給客戶端

【配置半同步複製模式】

1. 檢視當前的資料庫伺服器是否支援動態載入模組

   mysql> show variables  like  'have_dynamic_loading';
   

2. 使用命令安裝模組
主庫半同步複製的模組

mysql> install plugin  rpl_semi_sync_master  soname  'semisync_master.so';

從庫半同步複製的模組

mysql> install plugin  rpl_semi_sync_slave  soname  'semisync_slave.so';

3.檢視模組狀態

mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name like '%semi%';

4.啟用半同步複製——在安裝完外掛後，半同步複製預設是關閉的

主：mysql> set global  rpl_semi_sync_master_enabled = 1;
從：mysql> set global rpl_semi_sync_slave_enabled = 1;

5.檢視是否啟用成功

mysql> show  variables like 'rpl_semi_sync_%_enabled';

6.把配置寫進配置檔案使其永久生效
– 命令配置臨時配置,重啟服務會失效
– 修改後需要重啟服務
– 寫在主配置檔案 /etc/my.cnf 的 [mysqld] 下方

# vim /etc/my.cnf
plugin-load = "rpl_semi_sync_master=semisync_master.so;
rpl_semi_sync_slave=semisy	nc_slave.so"   //安裝模組
rpl-semi-sync-master-enabled = 1  //啟用半同步複製
rpl-semi-sync-slave-enabled = 1	

# systemctl restart mysqld

檢視模組狀態

mysql> select plugin_name,plugin_status from information_schema.plugins  where  plugin_name like '%semi%';

檢視是否啟用成功

mysql> show variables like "rpl_semi_sync_%_enabled";

今天的內容到這裡就結束了，歡迎評論區留言，期待和您一起探討。