01-MySQL8主從詳解
主從原理
master服務器將數據的改變記錄二進制binlog日誌，當master上的數據發生改變時，則將其改變寫入二進制日誌中；slave服務器會在一定時間間隔內對master二進制日誌進行探測其是否發生改變，
當master服務器數據發生變化，則slave開始一個I/O Thread請求master二進制事件，同時maser節點為每個I/O線程啟動一個dump線程，用於向其發送二進制事件，並保存至從節點本地的中繼日誌Relay_Log_File中，從節點將啟動SQL Thread從中繼日誌中讀取二進制日誌，在本地重放，使得其數據和主節點的保持一致，最後I/O Thread和SQL Thread將進入睡眠狀態，等待下一次被喚醒。
注意幾點：
1–master將操作語句記錄到binlog日誌中，然後授予slave遠程連接的權限（master一定要開啟binlog二進制日誌功能；通常為了數據安全考慮，slave也開啟binlog功能）。
2–slave開啟兩個線程：IO線程和SQL線程。其中：IO線程負責讀取master的binlog內容到中繼日誌relay log里；SQL線程負責從relay log日誌里讀出binlog內容，並更新到slave的數據庫里，這樣就能保證slave數據和 master數據保持一致了。
3–Mysql複製至少需要兩個Mysql的服務，當然Mysql服務可以分佈在不同的服務器上，也可以在一台服務器上啟動多個服務。
4–Mysql複製最好確保master和slave服務器上的Mysql版本相同（如果不能滿足版本一致，那麼要保證master主節點的版本低於slave從節點的版本）
5–master和slave兩節點間時間需同步
1.1 Mysql複製的流程
1、第一階段
Mysql複製過程的第一部分就是master記錄二進制日誌。在每個事務更新數據完成之前，master在二日誌記錄這些改變。MySQL將事務串行的寫入二進制日誌，即使事務中的語句都是交叉執行的。在事件寫入二進制日誌完成後，master通知存儲引擎提交事務。
2、第二階段
就是slave將master的binary log拷貝到它自己的中繼日誌。首先，slave開始一個工作線程——I/O線程。I/O線程在master上打開一個普通的連接，然後開始binlog dump process。Binlog dump process從master的二進制日誌中讀取事件，如果已經跟上master，它會睡眠並等待master產生新的事件。I/O線程將這些事件寫入中繼日誌。
3、第三階段
SQL slave thread（SQL從線程）處理該過程的最後一步。SQL線程從中繼日誌讀取事件，並重放其中的事件而更新slave的數據，使其與master中的數據一致。只要該線程與I/O線程保持一致，中繼日誌通常會位於OS的緩存中，所以中繼日誌的開銷很小。
此外，在master中也有一個工作線程：和其它MySQL的連接一樣，slave在master中打開一個連接也會使得master開始一個線程。複製過程有一個很重要的限制——複製在slave上是串行化的，也就是說master上的並行更新操作不能在slave上並行操作。
1.2 主從複製的前提條件
- 開啟binlog功能
- 主庫master節點建立同步數據賬號
- 從庫要配置master.info（CHANGE MASTER to…相當於配置密碼文件和Master的相關信息）
- start slave 開啟複製功能
主從配置
2.1 基礎環境
操作系統centos7.5，
| 節點名稱 | IP地址 |
|---|---|
| master | 192.168.150.185 |
| slave | 192.168.150.25 |
2.2 基礎配置
關閉selinux
#臨時關閉
setenforce 0
#永久關閉
vi /etc/sysconfig/selinux
...
...
SELINUX=disabled
...
...
關閉防火牆
systemctl stop firewalld&&systemctl stop iptables
時鐘同步配置
#安裝chrony
yum install chrony
#修改配置文件
vi /etc/sysconfig/selinux
...
...
server ntp1.aliyun.com iburst
...
...
#啟動服務
systemctl start chronyd
2.3 master主節點配置
修改配置文件
vim /etc/my.cnf
..........
[mysqld]
#數據庫唯一ID，主從的標識號絕對不能重複。
server-id=1
#開啟bin-log，並指定文件目錄和文件名前綴
log-bin=mysql-bin
#同步test數據庫。如果同時同步多個庫，就以此格式另寫幾行即可。如果不指定某個庫同步，刪除此行，表示同步所有庫（除了ignore忽略的庫）
binlog-do-db=test
#不同步mysql系統數據庫。如果是多個不同步庫，就以此格式另寫幾行；也可以在一行，中間逗號隔開。
binlog-ignore-db=mysql
#設置二進制日誌自動刪除/過期的天數，避免佔用磁盤空間。默認值為0，表示不自動刪除。
expire_logs_days=7
#確保binlog日誌寫入後與硬盤同步
sync_binlog=1
#bin-log日誌文件格式
binlog_format=ROW
溫馨提示：在主服務器上最重要的二進制日誌設置是sync_binlog，這使得mysql在每次提交事務的時候把二進制日誌的內容同步到磁盤上，即使服務器崩潰也會把事件寫入日誌中。
sync_binlog這個參數是對於MySQL系統來說是至關重要的，他不僅影響到Binlog對MySQL所帶來的性能損耗，而且還影響到MySQL中數據的完整性。對於”sync_binlog”參數的各種設置的說明如下：
sync_binlog=0，當事務提交之後，MySQL不做fsync之類的磁盤同步指令刷新binlog_cache中的信息到磁盤，而讓Filesystem自行決定什麼時候來做同步，或者cache滿了之後才同步到磁盤。
sync_binlog=n，當每進行n次事務提交之後，MySQL將進行一次fsync之類的磁盤同步指令來將binlog_cache中的數據強制寫入磁盤。
在MySQL中系統默認的設置是sync_binlog=0，也就是不做任何強制性的磁盤刷新指令，這時候的性能是最好的，但是風險也是最大的。因為一旦系統Crash，在binlog_cache中的所有binlog信息都會被丟失。而當設置為「1」的時候，是最安全但是性能損耗最大的設置。因為當設置為1的時候，即使系統Crash，也最多丟失binlog_cache中未完成的一個事務，對實際數據沒有任何實質性影響。
從以往經驗和相關測試來看，對於高並發事務的系統來說，「sync_binlog」設置為0和設置為1的系統寫入性能差距可能高達5倍甚至更多。
2.4 數據一致性
在同步前保證master和slave中的數據一致，新環境忽略本步驟
導出數據庫之前先鎖定數據庫
#數據庫只讀鎖定命令，防止導出數據庫的時候有數據寫入,unlock tables命令解除鎖定
mysql> flush tables with read lock;
導出master數據庫中需要同步的庫
#導出需要同步的庫
[root@master ~]#mysqldump -uroot test -p123456 >/opt/test.sql
#如不指定，則導出所有庫
mysqldump -uroot -p123456 --all-databases>/opt/all.sql
將導出數據導入salve中
#傳到slave
scp /opt/all slave:/opt
#在slave導入數據庫
mysql> source /opt/all.sql
2.5 創建數據同步賬號
登錄主數據庫創建一個用於從數據庫複製的賬號
mysql> create user 'rep'@'192.168.150.25' identified with mysql_native_password by 'repl123';
grant replication slave on *.* to 'rep'@'192.168.150.25';
mysql> flush privileges;
查看主服務器master狀態(注意File與Position項，從服務器需要這兩項參數)
mysql> show master status;
+-----------+-----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------+-----------+--------------+------------------+-------------------+
| mysql-bin.000065 | 186607472 | | | |
+-----------+-----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
2.6 配置slave
修改my.cnf配置文件
vim /etc/my.cnf
#設置從服務器id，必須於主服務器不同
server-id=2
#啟動MySQ二進制日誌系統
log-bin=mysql-bin
#需要同步的數據庫名。如果不指明同步哪些庫，就去掉這行，表示所有庫的同步（除了ignore忽略的庫）
配置主從同步指令
#執行同步前，要先關閉slave
mysql> stop slave;
mysql> change master to master_host='192.168.150.185',master_user='repl',master_password='repl123',master_log_file='ON.000065',master_log_pos=186607472;
mysql> start slave;
mysql> show slave status \G;
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.150.185
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: ON.000065
Read_Master_Log_Pos: 187730832
Relay_Log_File: xmkjoa02-relay-bin.000002
Relay_Log_Pos: 46151494
Relay_Master_Log_File: ON.000065
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 187730832
Relay_Log_Space: 46151706
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 6c3f5abb-1c32-11ec-96ba-fa163e7a46bf
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 1
Network_Namespace:
1 row in set (0.00 sec)
如上，當IO和SQL線程的狀態均為Yes，則表示主從已實現同步了！
