MySQL複製的管理和維護

標籤：

1.查看主庫

mysql> show master status;

mysql> show master status;+------------------+-----------+--------------+------------------+| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+-----------+--------------+------------------+| mysql-bin.000013 | 971159980 |              |                  |+------------------+-----------+--------------+------------------+1 row in set (0.00 sec)

mysql> show master logs;

mysql> show master logs;+------------------+------------+| Log_name         | File_size  |+------------------+------------+| mysql-bin.000011 | 1073949250 || mysql-bin.000012 | 1073751139 || mysql-bin.000013 |  971159980 |+------------------+------------+3 rows in set (0.00 sec)

mysql> show binlog events in ‘mysql-bin.000013‘ from 971245404;

mysql> show binlog events in ‘mysql-bin.000013‘ from 971245404;+------------------+-----------+-------------+-----------+-------------+-----------------------------------------+| Log_name         | Pos       | Event_type  | Server_id | End_log_pos | Info                                    |+------------------+-----------+-------------+-----------+-------------+-----------------------------------------+| mysql-bin.000013 | 971245404 | Query       |         1 |   971245474 | BEGIN                                   || mysql-bin.000013 | 971245474 | Table_map   |         1 |   971245651 | table_id: 175 (attend.sys_user)         || mysql-bin.000013 | 971245651 | Update_rows |         1 |   971246116 | table_id: 175 flags: STMT_END_F         || mysql-bin.000013 | 971246116 | Table_map   |         1 |   971246185 | table_id: 176 (attend.sys_user_branch)  || mysql-bin.000013 | 971246185 | Write_rows  |         1 |   971246266 | table_id: 176 flags: STMT_END_F         || mysql-bin.000013 | 971246266 | Table_map   |         1 |   971246335 | table_id: 176 (attend.sys_user_branch)  || mysql-bin.000013 | 971246335 | Delete_rows |         1 |   971246416 | table_id: 176 flags: STMT_END_F         || mysql-bin.000013 | 971246416 | Xid         |         1 |   971246443 | COMMIT /* xid=3065249192 */             |           |+------------------+-----------+-------------+-----------+-------------+-----------------------------------------+8 rows in set (0.00 sec)

 

2.查看複製庫延遲

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.10.106                  Master_User: repl                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000013          Read_Master_Log_Pos: 971434196               Relay_Log_File: mysql-relay-bin.000002                Relay_Log_Pos: 126333146        Relay_Master_Log_File: mysql-bin.000013             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: 971434196              Relay_Log_Space: 126333302              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: 0Master_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: 11 row in set (0.00 sec)

Seconds_Behind_Master：

·顯示備庫延遲，但不準確

·將伺服器當前的時間戳記與二進位日誌中的事件時間戳記相對比，所以只有在執行複製事件時才能報告延遲

·如果複製線程沒有運行，延遲報告為null

·有時複製發生錯誤時，該參數為顯示為0，而不是顯示錯誤

·有時，即使複製線程正常運行，也不能估算延遲，而是顯示0或null

·大事務會導致延遲波動

 

檢測延遲比較好的方法是：使用heartbeat record。

 

3. 確認主備是否一致

mysql沒有內建方法來確認主備是否一致。checksum table可以校正資料，但是複製進行中時，這種方法不可行。

可以使用percona提供的pt-table-checksum來校正主備資料是否一致。（結合pt-table-sync使用，重新同步資料）

 

MySQL複製的管理和維護