主从复制经常遇到的问题

最新推荐文章于 2024-01-03 19:55:09 发布

原创最新推荐文章于 2024-01-03 19:55:09 发布 · 708 阅读

CC 4.0 BY-SA版权

本文详细介绍了MySQL主从同步的基本原理及操作步骤，包括如何使用SHOW SLAVE STATUS命令检查从服务器状态，解决从服务器更新失败的方法，以及如何通过FLUSH TABLES WITH READ LOCK和MASTER_POS_WAIT()函数确保主从服务器数据同步。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

检查从服务器一般使用show slave status命令来检查

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.100
Master_User: root
Master_Port: 3306
Connect_Retry: 3
Master_Log_File: mysql-bin.003
Read_Master_Log_Pos: 79
Relay_Log_File: mysql -relay-bin. 003
Relay_Log_Pos: 548
Relay_Master_Log_File: mysql -bin. 003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Last_Errno: 0
…..

在上面这些信息中我们主要关注的是Slave_IO_Running和Slave_SQL_Running
Slave_IO_Running:从服务器正从主服务器上读取BINLOG日志,并写入从服务器的中继日志
Slave_SQL_Running:进程正在读取从服务器的BINLOG中继日志，并转化为SQL执行
以前有一个进程是no状态，表示复制的进程停止，在Last_Errno会看到是什么情况

有时候因为主服务器的更新过于频繁,造成了从服务器更新速度较慢,当然问题是多种多样,有可能是网络搭建的结构不好或者硬件的性能较差,从而使得主从服务器之间的差距越来越大,最终对某些应用产生了影响,在这种情况下,我们需要定期进行主从服务器的数据同步,具体步骤如下
在主服务器上

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.03 sec)
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000004
Position: 102
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

记录出日志的名字和偏移量,这些是从服务器复制的目的目标

在从服务器上,使用MASTER_POS_WAIT()函数得到复制坐标值

mysql> select master_pos_wait('mysql-bin.000004','102');
+-------------------------------------------+
| master_pos_wait('mysql-bin.000004','102') |
+-------------------------------------------+
| 0 |
+-------------------------------------------+
1 row in set (0.00 sec)

这个select 语句会阻塞直到从服务器达到指定日志文件和偏移量后,返回0,如果是-1,则表示超时推出,查询是0时,表示从服务器与主服务器已经同步

在某些情况下,会出现从服务器更新失败,首先需要确定是否从服务器的表与主服务器的不同造成的,如果是表结构造成的,则需要修改从服务器的表和主服务器一致,然后重新运行start slave

如果不是表结构不同造成的更新失败，则需要确认手动更新是否安全，然后忽视来自主服务器的更新失败语句，跳过来来自主服务器的语句，命令为SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n,其中,n=1表示来自主服务器的更新语句不使用AUTO_INCREMENT或LAST_INSERT_ID(),n=2时则反之,原因是使用AUTO_INCREMENT或LAST_INSERT_ID的语句需要从二进制日志中取得两个事件.

在主数据库上查看POS位置：
/usr/local/mysql/bin/mysql -u admin -p -S /var/lib/mysql/mysql.sock
mysql> show master status;
在从数据库上修改
mysql> slave stop;
mysql> change master to Master_Log_File='mysql-bin.000001',Master_Log_Pos=98;
mysql> slave start;