MySQL复制的管理和维护_mysql教程_开发教程

1.查看主库

mysql> show master status;

+------------------+-----------+--------------+------------------+

+------------------+-----------+--------------+------------------+

| mysql-bin.000013 | 971159980 | | |

+------------------+-----------+--------------+------------------+

1 row in set (0.00 sec)

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-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

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

(责任编辑：好模板)