MySQL主從同步原理與實戰(zhàn)

MySQL主從同步

1 MySQL的安裝

關(guān)于MySQL的安裝，可以在官網(wǎng)搜索教程或者在網(wǎng)上查找教程，也可以參考 Mysql 8.0.12解壓版安裝

具體配置就不一一介紹了。基本按照上面的鏈接可以搞定。如果不行的話，就是用戶權(quán)限或者目錄權(quán)限的一些問題，調(diào)整下即可。

2 MySQl主從同步實踐

MySQL復(fù)制有兩種方法：

  • 傳統(tǒng)方式：基于主庫的bin-log將日志事件和事件位置復(fù)制到從庫，從庫再加以 應(yīng)用來達到主從同步的目的
  • Gtid方式：global transaction identifiers是基于事務(wù)來復(fù)制數(shù)據(jù)，因此也就不依賴日志文件位置，同時又能更好的保證主從庫數(shù)據(jù)一致性

MySQL復(fù)制有多種類型：

  • 異步復(fù)制：一個主庫，一個或多個從庫，數(shù)據(jù)異步同步到從庫
  • 同步復(fù)制：在MySQL Cluster中特有的復(fù)制方式
  • 半同步復(fù)制：在異步復(fù)制的基礎(chǔ)上，確保任何一個主庫上的事務(wù)在提交之前至 少有一個從庫已經(jīng)收到該事務(wù)并日志記錄下來
  • 延遲復(fù)制：在異步復(fù)制的基礎(chǔ)上，人為設(shè)定主庫和從庫的數(shù)據(jù)同步延遲時間，即保證數(shù)據(jù)延遲至少是這個參數(shù)

復(fù)制的工作原理是數(shù)據(jù)庫修改事件記錄到bin log中并傳遞到slave，然后slave在本地還原的過程。而事件記錄到bin log的格式會有所不同。

MySQL復(fù)制有三種核心格式

  • 基于語句的復(fù)制(statement based replication)：基于主庫將SQL語句寫入到 bin log中完成復(fù)制
  • 基于行數(shù)據(jù)的復(fù)制(row based replication)：基于主庫將每一個行數(shù)據(jù)變化的信息作為事件寫入到bin log中完成日志
  • 混合復(fù)制(mixed based replication)：上述兩者的結(jié)合。默認(rèn)情況下優(yōu)先使用基于語句的復(fù)制，只有當(dāng)部分語句如果基于語句復(fù)制不安全的情況下才會自動切換為基于行數(shù)據(jù)的復(fù)制

以下binlog方式的主從同步實戰(zhàn)

2.1 主從同步配置

2.1.1 配置方式1

mysql-master /etc/my.cnf 的配置

# mysql主節(jié)點編號
[mysqld] 
server-id = 1 #Mysql服務(wù)的唯一編號 每個mysql服務(wù)Id需唯一
log-bin=mysql-bin # logbin的名字
binlog-do-db=test01 #需要同步的數(shù)據(jù)庫的名字，多個時候重復(fù)此配置
binlog-do-db=test02
#binlog-ignore-db=test03 #不需要同步的數(shù)據(jù)庫的名字，多個時候重復(fù)此配置
log-slave-updates=1 # log更新間隔
slave-skip-errors=1 # 是跳過錯誤，繼續(xù)執(zhí)行復(fù)制操作(可選)

mysql-slave /etc/my.cnf 的配置

[mysqld] 
#Mysql服務(wù)的唯一編號 每個mysql服務(wù)Id需唯一
server-id = 2
# read_only=1只讀模式，可以限定普通用戶進行數(shù)據(jù)修改的操作，但不會限定具有super權(quán)限的用戶（如超級管理員root用戶）的數(shù)據(jù)修改操作。
# 如果想保證super用戶也不能寫操作，就可以就需要執(zhí)行給所有的表加讀鎖的命令 “flush tables with read lock;”
read_only = 1

2.1.2 配置方式2

mysql-master /etc/my.cnf 的配置

[mysqld] 
#Mysql服務(wù)的唯一編號 每個mysql服務(wù)Id需唯一
server-id = 1
log-bin=mysql-bin # logbin的名字

mysql-slave /etc/my.cnf 的配置

[mysqld] 
#Mysql服務(wù)的唯一編號 每個mysql服務(wù)Id需唯一
server-id = 2
# read_only=1只讀模式，可以限定普通用戶進行數(shù)據(jù)修改的操作，但不會限定具有super權(quán)限的用戶（如超級管理員root用戶）的數(shù)據(jù)修改操作。
# 如果想保證super用戶也不能寫操作，就可以就需要執(zhí)行給所有的表加讀鎖的命令 “flush tables with read lock;”
read_only = 1
replicate-do-db=test01 #需要復(fù)制的數(shù)據(jù)庫名，如果復(fù)制多個數(shù)據(jù)庫，重復(fù)設(shè)置這個選項即可
replicate-ignore-db=test03 #需要復(fù)制的數(shù)據(jù)庫名，如果復(fù)制多個數(shù)據(jù)庫，重復(fù)設(shè)置這個選項即可

2.2 啟動主從同步

1. 創(chuàng)建同步賬號

在從庫創(chuàng)建同步賬號并授權(quán)

mysql>CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
mysql>GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
mysql>FLUSH PRIVILEGES;

2. 查看 master同步狀態(tài)

查看master同步狀態(tài)，記錄結(jié)果中的file文件名和Position值，在從庫開啟同步的設(shè)置時候需要使用

# 查看主節(jié)點狀態(tài)
mysql>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     3693 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3. 開啟slave同步

在從庫上進行如下操作開啟同步

# 停止正在進行的slave(如果有)
mysql>stop slave;
# 需要主機名，上面步驟的賬戶密碼以及日志文件名字和位置(請根據(jù)實際情況自行修改)
mysql>change master to master_host='192.168.109.129', master_user='slave', master_password='123456', 
\ master_log_file='mysql-bin.000001', master_log_pos=3693;
# 啟動
mysql>start slave;
# 查看狀態(tài)驗證是否成功
mysql>show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.109.128
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 3693
               Relay_Log_File: localhost-relay-bin.000005
                Relay_Log_Pos: 624
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes # 網(wǎng)絡(luò)OK
            Slave_SQL_Running: Yes # SQL同步OK
              Replicate_Do_DB: test_rp
                ......其他就不復(fù)制了.....
1 row in set (0.00 sec)

注意：
如果遇到Slave_IO_Running/Slave_SQL_Running 為NO 的情況，一般原因可能有：

  • 1：機器網(wǎng)絡(luò)不通
  • 2：配置項寫錯，例如ip，密碼等
  • 3：防火墻問題
  • 4：my.cnf的server-id或者 auto.cnf里面的server-uuid 重復(fù)

2.3 主從同步的異常處理

#在Slave上查看 
mysql> show slave status\G 
Slave_IO_Running: Yes 
Slave_SQL_Running: No 
#可見是Slave不同步

針對這類同步異常的情況，一般有以下兩種方式解決

  • 方式1：跳過錯誤，繼續(xù)執(zhí)行后續(xù)同步
  • 方式2：dump主庫數(shù)據(jù)，恢復(fù)到從庫后，重新開始同步

方式1： 適合于主從數(shù)據(jù)相差不大，數(shù)據(jù)一致性要求不是特別嚴(yán)格的情況，允許跳過部分錯誤，繼續(xù)進行。操作如下：

## 進入從庫的mysql
mysql>stop slave; 
mysql>et global sql_slave_skip_counter =1; #表示跳過一步錯誤，后面的數(shù)字可變 
mysql>start slave; 
mysql>show slave status\G; #查看
Slave_IO_Running: Yes 
Slave_SQL_Running: Yes

出現(xiàn)以上結(jié)果，表示主從同步狀態(tài)正常了。。。

方式2： 適合主從數(shù)據(jù)相差比較大，數(shù)據(jù)一致性要求比較高。
實現(xiàn)思路就是先把主庫寫停止，然后dump數(shù)據(jù)，在從庫恢復(fù)，恢復(fù)之后再重新開啟主從同步

操作如下：

1.先進入主庫，進行鎖表，防止數(shù)據(jù)寫入 
使用命令： 
mysql> flush tables with read lock; 
注意：該處是鎖定為只讀狀態(tài)，語句不區(qū)分大小寫 

2.進行數(shù)據(jù)備份 
#把數(shù)據(jù)備份到mysql.bak.sql文件 
[root@server01 mysql]#mysqldump -uroot -p -hlocalhost -D dasenamexxx > mysql.bak.sql 
這里注意一點：數(shù)據(jù)庫備份一定要定期進行，可以用shell腳本或者python腳本，都比較方便，確保數(shù)據(jù)萬無一失 

3.查看master 狀態(tài) 
mysql> show master status; 
+-------------------+----------+--------------+-------------------------------+ 
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
+-------------------+----------+--------------+-------------------------------+ 
| mysqld-bin.000001 | 3260 | |  | 
+-------------------+----------+--------------+-------------------------------+ 
1 row in set (0.00 sec) 

4.把mysql備份文件傳到從庫機器，進行數(shù)據(jù)恢復(fù) 
#使用scp命令 
[root@server01 mysql]# scp mysql.bak.sql root@192.168.109.129:/tmp/ 

5.停止從庫的狀態(tài) 
mysql> stop slave; 

6.然后到從庫執(zhí)行mysql命令，導(dǎo)入數(shù)據(jù)備份 
mysql> source /tmp/mysql.bak.sql; 

7.設(shè)置從庫同步，注意該處的同步點，就是主庫show master status信息里的| File| Position兩項 
mysql>change master to master_host = '192.168.109.129', master_user = 'slave', master_port=3306, master_password='123456', 
\ master_log_file = 'mysqld-bin.000001', master_log_pos=3260; 

8.重新開啟從同步 
mysql> stop slave; 

9.查看同步狀態(tài) 
mysql> show slave status\G; 
Slave_IO_Running: Yes 
Slave_SQL_Running: Yes

Mysql主從（主從不同步解決辦法，常見問題及解決辦法，在線對mysql做主從復(fù)制）

3 MySQl主從同步原理

mysql-master-slave.jpg

  1. 在master機器上的操作：

    當(dāng)master上的數(shù)據(jù)發(fā)生變化時，該事件變化會按照順序?qū)懭隻in-log中。當(dāng)slave鏈接到master的時候，master機器會為slave開啟binlog dump線程。
    當(dāng)master的binlog發(fā)生變化的時候，bin-log dump線程會通知slave，并將相應(yīng)的binlog內(nèi)容發(fā)送給slave。

  2. 在slave機器上操作：

    當(dāng)主從同步開啟的時候，slave上會創(chuàng)建兩個線程：I\O線程。該線程連接到master機器，master機器上的binlog dump 線程會將binlog的內(nèi)容發(fā)送給該I\O線程。
    該I/O線程接收到binlog內(nèi)容后，再將內(nèi)容寫入到本地的relay log；sql線程。該線程讀取到I/O線程寫入的ralay log。并且根據(jù)relay log 的內(nèi)容對slave數(shù)據(jù)庫做相應(yīng)的操作。

參考鏈接

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

友情鏈接更多精彩內(nèi)容