MySQL主從復制
MySQL可以通過兩種方式配置主從復制，一種是通過二進制日志（binary log）的方式；另一種是通過GTID（全局事務ID）方式，不過GTID方式仍然依賴MySQL的binary log。
通過binary log配置主從
binary log的核心是事件（event）?；驹硎牵褐鞣掌靼阉袑祿斓牟僮鳎ɡ鐄pdate、delete，create等）作為一個事件，當有事件產生，就把它們寫入到對應的binary log中，每個事件都對應一個位置（可以理解這個位置就是事件的編號）。然后從服務器讀取主服務器中的日志文件來獲取主服務產生的事件（把這些日志保存到從服務器本地的relay-log中），從而把主服務的數據庫操作在從服務器中重復執(zhí)行一次，達到數據復制的目的。
由于一切操作都是基于binary log，因此主服務器必須開啟log-bin選項，另外，主從服務器都必須分配一個唯一的server-id。
binary log方式實戰(zhàn)
master服務器配置步驟
- 為master分配一個唯一id（server-id）
- 開啟log-bin選項
- 重啟服務器
- 創(chuàng)建一個專門用于復制的用戶（非必需，但更安全）
- 鎖表，禁止數據庫寫入數據
- 獲取bin log日志的名稱和位置
修改master服務器配置文件（/etc/mysql/mysql.conf.d/mysqld.cnf）
[mysqld]
log-bin = mysql.bin #binary log日志的前綴名稱，可隨意
server-id = 1
bind-address = 0.0.0.0 # 讓mysql接收外部網絡連接
....
由于MySQL默認只監(jiān)聽127.0.0.1，因此無法被外部網絡連接，因此要把它改為0.0.0.0。
修改完后一定要先重啟服務器，否則后面的步驟無法順利進行
創(chuàng)建一個專門用于復制，且沒有其他權限的用戶
mysql> create user 'repl'@'%' identified by 'repl'; #創(chuàng)建用戶
mysql> grant replication slave on *.* to 'repl'@'%'; #只授予復制權限
接下來就要鎖表，禁止數據庫被修改，目的是獲取當前數據庫的日志名稱和事件位置
mysql> flush tables with read lock;
輸入命令后不能退出MySQL，以保持鎖的狀態(tài)。
數據庫鎖定后，接下來就是最重要的一步，獲取日志的名稱和位置信息
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 334
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
master服務準備完畢，下面就是配置slave服務器。
slave服務器配置步驟
- 和master服務器一樣，slave服務器也要分配一個唯一的id。
- 重啟服務器
- 設置主服務器的相關信息
- 開啟slave工作線程
- 檢查slave服務器的狀態(tài)
修改slave服務器配置文件（/etc/mysql/mysql.conf.d/mysqld.cnf）
[mysqld]
server-id = 2
和master服務器相比，slave服務器目前只需要添加一個唯一的id即可。
重啟服務器
重啟后就可以把主服務器的相關信息設置給從服務器
mysql> change master to MASTER_HOST='192.168.88.204',\
MASTER_USER=’repl',\
MASTER_PASSWORD='repl',\
MASTER_LOG_FILE='mysql-bin.000002',\ #主服務配置最后一步獲得的信息
MASTER_LOG_POS=334; #同樣是主服務器配置最后一步獲得的信息
準備好后就可以開啟從服務器
mysql> start slave;
順利的話，主從復制到這一步就完成了，可以通過下面的命令查看從服務器是否正常工作
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.88.204
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 334
Relay_Log_File: ubunt-host1-relay-bin.000002
Relay_Log_Pos: 500
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
Last_Errno: 0
Last_Error:
...
Master_Server_Id: 1
Master_UUID: bd9480c5-6f83-11e8-a486-000c29e23e48
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
...
1 row in set (0.00 sec)
正常的輸出不止這些，由于內容太多，上面省略了部分輸出
通過GTID配置主從
GTID實際上是對傳統(tǒng)基于binary log的復制進行了增強。在binary log復制方式中，我們必須手動跟蹤主服務器的日志名稱和位置；但在GTID工作方式下面，我們無需跟蹤這兩個值，取而代之的是由MySQL自動跟蹤它們，并使用GTID來標記哪些事務已經被處理，哪些還沒有被處理。GTID的一個優(yōu)點是：相同GTID的事務不會被重復處理，好處是可以最大限度地確保數據的一致性。
GTID由兩部分構成：一部分是服務器的UUID，另一部分是事務ID；如果服務器的UUID是bd9480c5-6f83-11e8-a486-000c29e23e48，那么它和第1個事務組成的GTID如下：
bd9480c5-6f83-11e8-a486-000c29e23e48:1
在GTID方式配置過程中，主從服務器都應該確保開啟了log-bin選項，還有另外兩個新增選項分別是：gtid-mode和enforce-gtid-consistency。
GTID方式實戰(zhàn)
GTID復制的配置步驟如下：
- 為master和slave服務器分配唯一的server id
- master和slave都要開啟log-bin選項
- master和slave都要開啟gtid-mode和enforce-gtid-consistency選項
- slave服務器開啟日志自動跟蹤
修改主從服務器的配置文件，配置如下（主從配置相同）：
[mysqld]
server-id=1 #除了server-id不同外，其他配置主從都相同
log-bin=mysql-bin
gtid-mode = on
enforce-gtid-consistency = on
重啟服務器
重啟后登入slave服務器，修改change master to的內容如下：
注意：執(zhí)行下面的命令前先確保slave線程是停止的，如果當前slave線程正在運行，先使用stop slave停止該線程。
mysql> change master to MASTER_HOST='192.168.88.204',\
MASTER_USER=’repl',\
MASTER_PASSWORD='repl',\
MASTER_AUTO_POSITION=1； #自動跟蹤日志名稱和位置
上面的命令是為了示例的完整性，如果使用GITD配置之前，兩臺服務器已經是傳統(tǒng)的binlog主從服務器，則命令可以更簡單：
mysql> change master to MASTER_AUTO_POSITION=1； #自動跟蹤日志名稱和位置
測試配置是否成功
配置完畢，我們可以往主數據庫創(chuàng)建一些測試數據，之后再運行show master status查看結果：
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 340
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: bd9480c5-6f83-11e8-a486-000c29e23e48:1
1 row in set (0.00 sec)
你會發(fā)現Executed_Gtid_Set多了一個值：bd9480c5-6f83-11e8-a486-000c29e23e48:1，這個值表示這個事務是當前的一個事務。
同樣，登入從服務器運行show slave status觀察結果：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.88.204
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 340
Relay_Log_File: ubunt-host1-relay-bin.000002
Relay_Log_Pos: 553
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
Last_Errno: 0
Last_Error:
...
Master_Server_Id: 1
Master_UUID: bd9480c5-6f83-11e8-a486-000c29e23e48
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
...
Retrieved_Gtid_Set: bd9480c5-6f83-11e8-a486-000c29e23e48:1
Executed_Gtid_Set: bd9480c5-6f83-11e8-a486-000c29e23e48:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
和之前相比，從服務器的輸出信息也有點不一樣了
- Retrieved_Gtid_Set和Executed_Gtid_Set都填充了值
- Auto_Position變成了1
從以上結果可以看到，我們的主從服務器已經切換到了GTID工作模式。
一些注意事項和問題
添加更多從服務器
有時候我們希望配置多臺從服務器。如果是新建立的主從環(huán)境，沒有什么數據，那么無論多少從服務器，搭建方式和上面說的沒有什么兩樣。重點是，如果在已經運行了一段時間的主從環(huán)境中添加更多的從服務器，那么這臺從服務器的配置就有點不一樣。原因是：在主從配置的過程中，我們需要對服務器進行鎖表操作，也就是說，任何數據都不可以繼續(xù)寫入，且持續(xù)的時間有可能會很長，在生產環(huán)境下，基本上是不允許的。
也就是說，我們不能在主服務器上下手，相反，我們應該在已經存在的從服務器這邊下手。基本過程如下：
- 停止舊slave服務器的MySQL服務
- 把舊的slave服務器上的數據復制到新的從服務器里
- 為新服務器分配唯一的server-id和開啟其他選項
- 重啟服務器
詳細步驟
第一步：我們需要停止舊的slave服務器的MySQL
root@slave1:~# mysqladmin -u root -p shutdown
第二步：把舊從庫的數據復制到新的從庫中
可以有多種方式復制數據，例如先使用mysqldump備份舊的數據，然后在新庫中還原，也可以直接打包原始數據，然后復制到新庫的對應目錄下面。這里我選擇了后者：
root@slave1:~# cd /var/lib/mysql
root@slave1:/var/lib/mysql# ls
auto.cnf ibdata1 master.info mysql-bin.index replication slave1-relay-bin.000002
debian-5.7.flag ib_logfile0 mysql performance_schema sys slave1-relay-bin.index
ib_buffer_pool ib_logfile1 mysql-bin.000001 relay-log.info slave1-relay-bin.000001
接下來使用tar命令對這些數據進行打包操作：
root@slave1:/var/lib/mysql# tar -zcvf data.tar.gz2 .
打包完成后，我們就可以把數據復制到新的從庫里面，這里我選擇了使用scp命令，如果數據非常大的話請選擇其他方案。
root@slave1:/var/lib/mysql# scp data.tar.gz2 root@192.168.88.217:/var/lib/mysql/ #復制數據到遠程主機的/var/lib/mysql目錄
The authenticity of host '192.168.88.217 (192.168.88.217)' can't be established.
ECDSA key fingerprint is SHA256:iAoWxPJ22h3p9T9grTgcC5lqpB1Q1Tw7wjznkUab0HI.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.88.217' (ECDSA) to the list of known hosts.
root@192.168.88.217's password:
data.tar.gz2 100% 1443KB 1.4MB/s 00:00
復制完后到新服務器中解壓文件
root@slave2:~# cd /var/lib/mysql
root@slave2:/var/lib/mysql# ls
auto.cnf data.tar.gz2 debian-5.7.flag ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 ibtmp1 mysql performance_schema sys
可以見到，文件已經傳輸過來了，把它們解壓：
root@slave2:/var/lib/mysql# tar -zxf data.tar.gz2
使用ls命令查看：
root@ubuntu-host2:/var/lib/mysql# ls -l
total 124384
-rw-r----- 1 mysql mysql 56 Jun 13 23:37 auto.cnf
-rw-r--r-- 1 root root 1477311 Jun 16 03:47 data.tar.gz2
-rw-r--r-- 1 root root 0 Jun 13 23:37 debian-5.7.flag
-rw-r----- 1 mysql mysql 298 Jun 16 03:35 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Jun 16 03:35 ibdata1
-rw-r----- 1 mysql mysql 50331648 Jun 16 03:35 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Jun 13 23:37 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Jun 15 20:57 ibtmp1
-rw-r----- 1 mysql mysql 125 Jun 16 03:35 master.info
drwxr-x--- 2 mysql mysql 4096 Jun 13 23:37 mysql
-rw-r----- 1 mysql mysql 177 Jun 16 03:35 mysql-bin.000001
-rw-r----- 1 mysql mysql 19 Jun 16 02:57 mysql-bin.index
drwxr-x--- 2 mysql mysql 4096 Jun 13 23:37 performance_schema
-rw-r----- 1 mysql mysql 65 Jun 16 03:35 relay-log.info
drwxr-x--- 2 mysql mysql 4096 Jun 16 03:10 replication
drwxr-x--- 2 mysql mysql 12288 Jun 13 23:37 sys
-rw-r----- 1 mysql mysql 213 Jun 16 03:00 slave1-relay-bin.000001
-rw-r----- 1 mysql mysql 576 Jun 16 03:35 slave1-relay-bin.000002
-rw-r----- 1 mysql mysql 62 Jun 16 03:00 slave1-relay-bin.index
數據都過來了。
第三步：配置mysqld.cnf文件
[mysqld]
server-id = 3
gtid-mode = on
enforce-gtid-consistency = on
重啟服務器
發(fā)現異常
異常1
在新的服務器使用show slave status查看結果：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.88.204
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 340
Relay_Log_File: slave1-relay-bin.000002
Relay_Log_Pos: 553
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: No
Slave_SQL_Running: No
...
Last_Errno: 1872
Last_Error: Slave failed to initialize relay log info structure from the repository
Skip_Counter: 0
...
Last_SQL_Errno: 1872
Last_SQL_Error: Slave failed to initialize relay log info structure from the repository
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID: bd9480c5-6f83-11e8-a486-000c29e23e48
Master_Info_File: /var/lib/mysql/master.info
雖然MySQL服務可以正常運作，但slave線程卻沒有如我們期待的正常開啟，而且還發(fā)現了錯誤：
Last_Errno: 1872
Last_Error: Slave failed to initialize relay log info structure from the repository
通過查看MySQL的日志文件，發(fā)現有類似下面的錯誤信息：
/var/log/mysql/error.log日志文件部分輸出
2018-06-16T07:54:49.511960Z 0 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=slave2-relay-bin' to avoid this problem.
2018-06-16T07:54:49.526580Z 0 [ERROR] Failed to open the relay log './slave1-relay-bin.000002' (relay_log_pos 553).
2018-06-16T07:54:49.526659Z 0 [ERROR] Could not find target log file mentioned in relay log info in the index file './slave2-relay-bin.index' during relay log initialization.
原因在于，默認情況下，slave線程會使用主機名-relay-bin的方式保存日志文件， 而新庫的所有數據文件都是由舊庫那里復制過來的，包括從庫的一些配置文件，這就導致配置信息中的主機名沖突。
解決辦法一：
解決辦法其實錯誤日志中已經給出，就是使用--relay-log選項明確指定relay-log的名稱
修改mysqld.cnf，添加relay-log選項：
[mysqld]
server-id = 3
gtid-mode = on
enforce-gtid-consistency = on
relay-log = slave1-releay-bin #使用舊主機的relay-log
解決辦法二：
當然，也可以不添加relay-log選項，解決辦法如下：
- 把舊slave的slave1-relay-bin.index中的內容追加到新slave的slave2-relay-bin.index文件中
- 重啟新slaveMySQL服務
具體實現
root@slave2:/var/lib/mysql# ls
ai ib_buffer_pool ibtmp1 mysql-bin.index sys slave2-relay-bin.000001
auto.cnf ibdata1 master.info performance_schema slave1-relay-bin.000005 slave2-relay-bin.index
data.tar.gz2 ib_logfile0 mysql relay-log.info slave1-relay-bin.000006
debian-5.7.flag ib_logfile1 mysql-bin.000001 replication slave1-relay-bin.index
root@slave2:/var/lib/mysql# cat slave1-relay-bin.index >> slave2-relay-bin.index
異常2
修改后重啟服務器，再次使用show slave status檢查，發(fā)現另一個問題，錯誤如下：
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'A slave with the same server_uuid/server_id as this slave has connected to the master; the first event '' at 4, the last event read from './mysql-bin.000003' at 493, the last byte read from './mysql-bin.000003' at 493.'
意思是已經有相同uuid的服務器連接到master，造成這種錯誤的原因和上面的一樣。服務器的uuid其實是保存在數據目錄的auto.cnf文件里面，在復制數據到新slave的時候，把這個auto.cnf文件也復制到新的slave中，因此，兩個slave的uuid就發(fā)生了沖突。
解決辦法
如果MySQL發(fā)現沒有auto.cnf文件，就會重新生成一個，也就是說，這要把發(fā)生沖突的auto.cnf文件刪除，服務器就會生成一個新的uuid。所以，只要把這個文件刪除，然后重啟服務器就OK了。
全部問題都解決之后，在master中運行如下命令：
mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 3 | | 3306 | 1 | d77f4132-7142-11e8-b21c-000c29c59da1 |
| 2 | | 3306 | 1 | 46108417-6f84-11e8-a559-000c29c7aaa0 |
+-----------+------+------+-----------+--------------------------------------+
2 rows in set (0.00 sec)
兩個slave都被master識別出來。
