主從復(fù)制查詢狀態(tài)+故障及延時(shí)

1. 查看配置文件

[root@db01 ~]$ mysql -S /data/3308/mysql.sock -e 'show slave status\G;'
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.51
                  Master_User: repl
                  Master_Port: 3307
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 154
               Relay_Log_File: db01-relay-bin.000005
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000007
             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: 154
              Relay_Log_Space: 739
              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: 7
                  Master_UUID: e44991f2-b834-11e9-8c01-000c2992bac2
             Master_Info_File: /data/3308/data/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:

1.1 查看線程狀態(tài)

[root@db01 ~]$ mysql -S /data/3308/mysql.sock -e 'show slave status\G;' | grep "Running"
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

1.2 線程報(bào)錯(cuò)具體信息

[root@db01 ~]$ mysql -S /data/3308/mysql.sock -e 'show slave status\G;' | grep "Last"
                   Last_Errno: 0
                   Last_Error: 
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp:

1.3 主庫(kù)連接信息以及二進(jìn)制信息(master)

[root@db01 ~]$ mysql -S /data/3308/mysql.sock -e 'show slave status\G;' | grep "Master" | grep -v 'SSL'
                  Master_Host: 10.0.0.51
                  Master_User: repl
                  Master_Port: 3307
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 154
        Relay_Master_Log_File: mysql-bin.000007
          Exec_Master_Log_Pos: 154
        Seconds_Behind_Master: 0
             Master_Server_Id: 7
                  Master_UUID: e44991f2-b834-11e9-8c01-000c2992bac2
             Master_Info_File: /data/3308/data/master.info
           Master_Retry_Count: 86400
                  Master_Bind: 
           Master_TLS_Version:

1.4 從庫(kù)和主庫(kù)延時(shí)的時(shí)間

[root@db01 ~]$ mysql -S /data/3308/mysql.sock -e 'show slave status\G;' | grep Behind"_Master"
Seconds_Behind_Master: 0

1.5 過(guò)濾復(fù)制相關(guān)狀態(tài)

[root@db01 ~]$ mysql -S /data/3308/mysql.sock -e 'show slave status\G;' | grep "Replicate"
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
  Replicate_Ignore_Server_Ids: 
         Replicate_Rewrite_DB:

1.6 延時(shí)從庫(kù)的狀態(tài)信息

[root@db01 ~]$ mysql -S /data/3308/mysql.sock -e 'show slave status\G;' | grep "Delay"
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL

1.7 查看監(jiān)控GTID狀態(tài)

[root@db01 ~]$ mysql -S /data/3308/mysql.sock -e 'show slave status\G;' | grep "Gtid"
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set:

1.8 中繼日志監(jiān)控

[root@db01 ~]$ mysql -S /data/3308/mysql.sock -e 'show slave status\G;' | grep "Relay"
               Relay_Log_File: db01-relay-bin.000005
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000007
              Relay_Log_Space: 739

2. 主從復(fù)制故障

2.1 IO線程故障

讀取master.info

損壞
信息錯(cuò)誤change master to信息錯(cuò)誤

連接主庫(kù)

網(wǎng)絡(luò)
防火墻
主機(jī)(主庫(kù))沒有運(yùn)行
連接數(shù)上線

>> 以上問(wèn)題報(bào)錯(cuò):
Slave_IO_running: Connecting
Last_IO_Error: xxxxxxx

>> 排查方法
通過(guò)復(fù)制用戶着帽，手工連接主庫(kù)，查看報(bào)錯(cuò)現(xiàn)象

修復(fù)：
stop slave
reset slave all
change master to
start slave

請(qǐng)求日志

master.info 復(fù)制起點(diǎn)
主庫(kù)：損害移层，誤刪除等操作

接收日志

relaylog損壞
>>修復(fù)
stop slave
reset slave all
change master to
start slave

更新mater.info(拿到的信息存放在relay.info中)

2.2 SQL線程故障

(1) relay.info
(2) 回放relaylog中的日志

SQL語(yǔ)句為什么會(huì)失斎院病？
(1) 語(yǔ)法观话，SQL_Mode版

版本差異予借，SQL_mode不一致

(2) DDL DCL DML為什么會(huì)失敗

create database /table 創(chuàng)建的對(duì)象已經(jīng)存在。
從庫(kù)被提前寫入了

drop database errdb; 要?jiǎng)h除和修改的對(duì)象不存在

alter.insert update,delete

處理方法(以從庫(kù)為核心的處理方案)：

方法一：
stop slave; 
set global sql_slave_skip_counter = 1;
#將同步指針向下移動(dòng)一個(gè)频蛔，如果多次不同步灵迫，可以重復(fù)操作。
start slave;
方法二：
/etc/my.cnf
slave-skip-errors = 1032,1062,1007
常見錯(cuò)誤代碼:
1007:對(duì)象已存在
1032:無(wú)法執(zhí)行DML
1062:主鍵沖突,或約束沖突

但是晦溪，以上操作有時(shí)是有風(fēng)險(xiǎn)的瀑粥，最安全的做法就是重新構(gòu)建主從。把握一個(gè)原則,一切以主庫(kù)為主.

2.3 防止從庫(kù)寫入

>> (1) 可以設(shè)置從庫(kù)只讀.
mysql[(none)]>show variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | OFF   |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.01 sec)
注意：
只會(huì)影響到普通用戶三圆，對(duì)管理員用戶無(wú)效狞换。
>> (2)加中間件
讀寫分離。

2.4 其他擴(kuò)展

pt-xxx 關(guān)于主從方面的工具
檢查主從數(shù)據(jù)一致性
實(shí)現(xiàn)主從數(shù)據(jù)同步

3. 主從延時(shí)

3.1 延時(shí)

主庫(kù)做的事舟肉，從庫(kù)很久才執(zhí)行修噪。

3.2 主從延時(shí)的現(xiàn)象

（1）最直觀：主庫(kù)做變更，從庫(kù)看狀態(tài)
（2）Seconds_Behind_Master:0(只能證明路媚，有或沒有)
（3）計(jì)算日志的差異

3.3 主從延時(shí)的原因

（1）外部因素

網(wǎng)絡(luò)
硬件
版本差異
參數(shù)差異

（2）主從復(fù)制的內(nèi)部原因
主庫(kù)：

二進(jìn)制日志的方面

二進(jìn)制日志落地不及時(shí)

解決方案：
sync_binlog=1
可以將binlog單獨(dú)存放在高性能存儲(chǔ)中

Dump_T(默認(rèn)是串行工作模式)

主庫(kù)的事務(wù)量大
主庫(kù)發(fā)生大事務(wù)

解決方案：

GTID模式

雙一的保證

如何監(jiān)控：

主庫(kù)：
mysql[(none)]>show master status;
+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                    |
+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+
| mysql-bin.000017 |      234 |              |                  | 06073cdc-bfd1-11e9-9a47-000c2992bac2:1-6,
c4e0cd26-b75c-11e9-b4d8-000c2992bac2:1-242 |
+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

從庫(kù)： 
show slave status\G;
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154

從庫(kù)：
（1）IO線程方面
relaylog寫入
解決方案：
可以將relaylog單獨(dú)存放在高性能存儲(chǔ)中
（2）SQL線程方面(只有一個(gè),串行回放)
默認(rèn)SQL線程割按，只能逐條的回放SQL
事務(wù)并發(fā)度高或者大事務(wù)

5.6 版本加入了多SQL復(fù)制

按照庫(kù)(database)級(jí)別，進(jìn)行并發(fā)回放SQL
slave_parallel_workers=16
slave_parallel_type=DATABASE

5.7 版本進(jìn)行了多SQL復(fù)制加強(qiáng)(MTS)

真正按照事務(wù)級(jí)別磷籍，實(shí)現(xiàn)了多SQL線程回放适荣。
slave_parallel_workers=16
slave_parallel_type=logical_clock

注意：必須依賴于GTID復(fù)制