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: 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
1 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使用，重新同步数据）