備注:測(cè)試數(shù)據(jù)庫版本為MySQL 8.0

這個(gè)blog我們來聊聊MySQL 的主從(異步復(fù)制)

概述

1.MySQL主從拓?fù)鋱D

image.png

2.MySQL配置從庫的用處

2.1. 讀寫分離
通過MySQL復(fù)制可以實(shí)現(xiàn)讀寫分離纺酸，將讀操作分布到多個(gè)不同的服務(wù)器上训枢，減輕服務(wù)器的壓力昨登。
2.2. 備份
從庫可以作為數(shù)據(jù)的異地實(shí)時(shí)備份。
2.3. 故障切換
當(dāng)主庫遇到故障，系統(tǒng)可以切換到從庫宿稀，實(shí)現(xiàn)故障切換愕难。
2.4. 升級(jí)測(cè)試
從庫可作為測(cè)試服務(wù)器的數(shù)據(jù)庫。

3.復(fù)制數(shù)據(jù)的步驟

3.1 在主庫上把數(shù)據(jù)更改記錄到二進(jìn)制日志（Binary Log）中妻柒。
3.2 備庫將主庫上的日志復(fù)制到自己的中繼日志（Relay Log）中扛拨。
3.3 備庫讀取中繼日志中的事件，將其在備庫上重新執(zhí)行一遍举塔。

一.主從搭建步驟

1.數(shù)據(jù)庫安裝

自己寫的MySQL 8.0編譯安裝： MySQL 8.0 CENTOS 7環(huán)境編譯安裝

2.主庫開啟binlog

配置文件[mysqld]下添加如下:

log-bin
server-id=1

重啟mysql服務(wù)

mysqladmin -uroot -p -S /u01/my3306/mysql.sock shutdown  
/u01/my3306/bin/mysqld_safe --defaults-file=/u01/my3306/my.cnf --user=mysql &

3.授權(quán)

在主庫創(chuàng)建同步賬號(hào)

CREATE USER 'repl'@'10.31.1.113' IDENTIFIED BY 'test';
grant replication slave, replication client on *.* to 'repl'@'10.31.1.113';
flush privileges;

mysql> CREATE USER 'repl'@'10.31.1.113' IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave, replication client on *.* to 'repl'@'10.31.1.113';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

4.登陸主庫查看此時(shí)日志狀態(tài)

show master status;

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000009 |      441 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

5.導(dǎo)出主庫當(dāng)前數(shù)據(jù)

備份此時(shí)主庫的內(nèi)容

-- 備份之前鎖表
mysql> flush table with read lock;
mysqldump -uroot -pabc123 -A -B --events --master-data=2 > /backup/mysql/db-$(date +%F)-all.sql
-- 備份完成后取消鎖
mysql> unlock tables;

6.從庫指定serverid

配置文件[mysqld]下添加如下:

server-id=2

server-id不能重復(fù)

重啟mysql服務(wù)

mysqladmin -uroot -p -S /u01/my3306/mysql.sock shutdown  
/u01/my3306/bin/mysqld_safe --defaults-file=/u01/my3306/my.cnf --user=mysql &

7.從庫導(dǎo)入主庫數(shù)據(jù)

通過scp將主庫的備份文件同步過來

mysql -uroot -p  </backup/mysql/db-2020-07-24-all.sql

8.指定開始同步位置

具體同步位置參考步驟4

change master to
master_host='10.31.1.112',
master_user='repl',
master_password='test',
MASTER_LOG_FILE='binlog.000009',
MASTER_LOG_POS=441 ;

mysql> change master to
    -> master_host='10.31.1.112',
    -> master_user='repl',
    -> master_password='test',
    -> MASTER_LOG_FILE='binlog.000009',
    -> MASTER_LOG_POS=441 ;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

9.開啟復(fù)制

-- 啟動(dòng)復(fù)制
start slave;
-- 查看復(fù)制狀態(tài)
show slave status\G

mysql>  show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.31.1.112
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000009
          Read_Master_Log_Pos: 441
               Relay_Log_File: ipctest-relay-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: binlog.000009
             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: 441
              Relay_Log_Space: 532
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           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: 08260f93-cbe5-11ea-bd0d-000c293fa60d
             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: 0
            Network_Namespace: 
1 row in set (0.00 sec)

二.復(fù)制相關(guān)參數(shù)

三.slave啟停常用命令

從庫執(zhí)行

四.保證主從一致

五.常見問題

5.1 IO線程出問題

Slave_IO_Running: No
Slave_SQL_Running: Yes
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

問題排查：在主庫上
[root@master mysql3306]# cat mysql-bin.index
/home/mysql3306/mysql3306/mysql-bin.000001
發(fā)現(xiàn)binlog文件名不正確，需要重新設(shè)置嗽测。

處理辦法
stop slave;
reset slave all;
change master to master_host='192.168.3.10',master_user='rep',master_password='rep', master_log_file='mysql-bin.000001',master_log_pos=154,MASTER_PORT=3306;
start slave;

5.2 SQL線程問題

5.2.1 ERROR 1062

主從數(shù)據(jù)庫不一致的時(shí)绪励，slave已經(jīng)有該條記錄，但是我們又在master上插入了同一條記錄唠粥，此時(shí)就會(huì)報(bào)錯(cuò).
在從庫上執(zhí)行跳過該insert語句
mysql> stop slave;
mysql> set global sql_slave_skip_counter=1;
mysql> start slave;
問題解決疏魏！

5.2.2 ERROR 1032

從庫刪除一條數(shù)據(jù)，主庫更新該條刪除的數(shù)據(jù)晤愧，發(fā)生報(bào)錯(cuò)
手工把缺失的數(shù)據(jù)補(bǔ)上
mysql> stop slave;
mysql> set global sql_slave_skip_counter=1;
mysql> start slave;
問題解決大莫！

5.2.3 ERROR 1452

無法在外鍵的表中插入或者更新參考主鍵沒有的數(shù)據(jù)

5.2.4 將從庫設(shè)置為readonly

建議大家線上打開這兩個(gè)參數(shù)，防止主從不一致的產(chǎn)生官份。
set global read_only=1
只讀參數(shù)只厘，需要大家注意的對(duì)于擁有super權(quán)限的用戶烙丛，該參數(shù)不起作用
set global super_read_only=1（5.7新參數(shù)）
如果想對(duì)擁有super權(quán)限用戶只讀，使用super_read_only羔味，開啟該參數(shù)后河咽，read_only會(huì)自動(dòng)開啟。

5.3 主從延遲問題

5.3.1.從庫同步延遲的現(xiàn)象

• show slave status顯示參數(shù)Seconds_Behind_Master不為0赋元，這個(gè)數(shù)值可能會(huì)很大
• show slave status顯示參數(shù)Relay_Master_Log_File和Master_Log_File顯示bin-log的編號(hào)相差很大忘蟹，說明bin-log在從庫上沒有及時(shí)同步，所以近期執(zhí)行的bin-log和當(dāng)前IO線程所讀的bin-log相差很大
• MySQL的從庫數(shù)據(jù)目錄下存在大量mysql-relay-log日志搁凸，該日志同步完成之后就會(huì)被系統(tǒng)自動(dòng)刪除媚值，存在大量日志，說明主從同步延遲很厲害

5.3.2.產(chǎn)生原因

當(dāng)主庫的TPS并發(fā)較高時(shí)护糖，產(chǎn)生的DDL數(shù)量超過slave一個(gè)sql線程所能承受的范圍褥芒，那么延時(shí)就產(chǎn)生了，當(dāng)然還有就是可能與slave的大型query語句產(chǎn)生了鎖等待椅文。
首要原因：數(shù)據(jù)庫在業(yè)務(wù)上讀寫壓力太大喂很，CPU計(jì)算負(fù)荷大惜颇，網(wǎng)卡負(fù)荷大皆刺，硬盤隨機(jī)IO太高
次要原因：讀寫binlog帶來的性能影響，網(wǎng)絡(luò)傳輸延遲凌摄。

5.3.3.解決方案

• 更換從庫更快的硬盤
• 網(wǎng)絡(luò)羡蛾，網(wǎng)卡，更換帶寬更大的網(wǎng)卡
  mysql考慮 從庫作為備份數(shù)據(jù)庫來說
• 增加從庫的innodb_buffer_pool_size,可以緩存更多數(shù)據(jù)防止由于轉(zhuǎn)換造成的IO壓力
• 增加innodb_log_file_size和innodb_log_files_in_group锨亏，減少buffer落盤
• 修改參數(shù)innodb_flush_method痴怨，提高寫入性能（SSD強(qiáng)烈推薦使用）
• 從庫binlog關(guān)閉（如果可以） log_slave_updates關(guān)閉
• 修改innodb_flush_log_at_trx_commit為0或者2
• 修改master_info_repository和relay_log_info_repository為TABLE，防止直接落盤壓力

參考文獻(xiàn):

1.https://dev.mysql.com/doc/refman/8.0/en/replication-configuration.html
2.https://zhuanlan.zhihu.com/p/115011360
3.http://blog.itpub.net/15412087/viewspace-2153117/
4.http://blog.itpub.net/31559985/viewspace-2220695/
5.http://blog.chinaunix.net/uid-20639775-id-3254611.html