MySQL复制的管理和维护

1.查看主库

mysql> show master status;

+ View Code?

1 2 3 4 5 6 7

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;

+ View Code?

1 2 3 4 5 6 7 8 9

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;

+ View Code?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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

+ View Code?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43

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