解决mysql使用GTID主从复制错误问题

xiu12

解决mysql使用GTID主从复制错误问题
　　做MySQL主从的话肯定会遇到很多同步上的问题,大多数都是由于机器宕机,重启,或者是主键冲突等引起的从服务器停止工作,这里专门收集类似问题并提供整理解决方案,仅供参考.
　　1、主从网络中断,或主服务器重启,或从服务器重启,从会根据配置文件中的时间,默认1分钟,去自动重连主服务器,直到网络和服务均可正常连接,连接正常后可自动继续同步之前文件,不需要任何人工干预.
　　2、当主从因为人为原因出现不同步的时候,可以用下面命令进行同步:
　　LOAD DATA FROM MASTER;
　　LOAD TABLE TBLNAME FROM MASTER;
　　注意,上面命令会对主数据库进行锁操作,如果数据库极大,建议在停机的时候进行,或者用短锁备份查看 show master status; 后,拷贝数据库的方式进行.
　　3、当 BIN-LOG 里面出现 SQL 级别错误导致主从不能同步的时候,可以用下面方法掠过该错误语句行,继续同步:
　　stop slave;
　　set global sql_slave_skip_counter=1;
　　start slave;
　　4、.当 set global sql_slave_skip_counter=1;是可能会出现一下错误
　　ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction
　　原因也说的很清楚了,不支持GTID_MODE 模式运行的数据库,那怎么办呢?
　　下面就讲一下GTID模式的主从错误跳过方法,多余的话不说了,直接上方法,按顺序执行即可,首先确定GTID点,也就是同步出错的点记录下来,方法如下,在查看之前您必须先登录MySQL,代码如下:
　　mysql> show slave statusG;
　　查看一下信息并记录下来:
　　Executed_Gtid_Set: 7f8d9eb8-a7fe-11e2-84fd-0015177c251e:1-260
　　接下来重置 slave上的 master和slave的.
　　NOTE:注意这里说的是从服务器上的master 和 slave,如果是主主复制就会很麻烦,这里注意了,reset master会导致此slave上所有的slave重置,reset master的主要目的是使gtid_executed为空。这里不能简单的使用change master to来切换,这样做表面上不会报错,但是实际上slave并不会更新,服务器会参考show slave statusG中的Executed_Gtid_Set参数来获取数据,代码如下:

• 　　mysql> reset master;
  
• 　　Query OK, 0 rows affected (0.20 sec)
  
• 　　mysql> stop slave;
  
• 　　Query OK, 0 rows affected (0.05 sec)
  
• 　　mysql> reset slave;
  
• 　　Query OK, 0 rows affected (0.42 sec)
  

　　下面我们需要重新设置GTID以跳过错误的信息,记得在第一步我们记录下来的Executed_Gtid_set吗? 没错执行它的时候粗错了,那么保守起见直接跳过这一条即可,在其ID上加1即可,代码如下:
　　mysql> set global gtid_purged=’7f8d9eb8-a7fe-11e2-84fd-0015177c251e:1-261′;
　　Query OK, 0 rows affected (0.18 sec)
　　由于我们刚才重置了Master和Slave,所以这里需要重新CHANGE MASTER,代码如下:
　　CHANGE MASTER TO MASTER_HOST=’192.168.1.136′, MASTER_PORT=3306, MASTER_USER=’dbadmin’,MASTER_PASSWORD=’123456′, master_auto_position=1;
　　然后重启slave,代码如下=:
　　start slave;
　　show slave statusG;
　　怎么样? 问题解决了吧? 什么? 还报错? 那你仔细看一下报错的是不是和上一条不一样了呢? 就证明已经跳过上条错误了, 您需要做的就是继续重复上面操作, 直到跳过所有错我,别嫌麻烦,毕竟数据很重要哦!
　　同步复制错误:下午搭了一主三从的mysql复制,结果所有服务器都配置好后,发现从上报如下的错误.

　　Last_IO_Error: Fatal error: The slave I/O thread stops because masterand slave have equal MySQL server>　　意思就是从上的server_id和主的一样的，经查看发现从上的/etc/my.cnf中的server_id=1这行我没有注释掉（在下面复制部分我设置了server_id），于是马上把这行注释掉了，然后重启mysql，发现还是报同样的错误。
　　使用如下命令查看了一下server_id,代码如下:

• 　　mysql> show variables like 'server_id';
  
• 　　+---------------+-------+
  
• 　　| Variable_name | Value |
  
• 　　+---------------+-------+
  
• 　　| server_id | 1 |
  
• 　　+---------------+-------+
  
• 　　1 row in set (0.00 sec)
  

　　发现,mysql并没有从my.cnf文件中更新server_id,既然这样就只能手动修改了,代码如下:
　　mysql> set global server_id=2; #此处的数值和my.cnf里设置的一样就行
　　mysql> slave start;
　　如此执行后,slave恢复了正常,不过稍后蚊子使用/etc/init.d/mysqld restart重启了mysql服务,然后查看slave状态,发现又出现了上面的错误,然后查看server_id发现这个数值又恢复到了1.
　　之后蚊子又重新查看了一下/etc/my.cnf的内容,确认应该不是这个文件的问题,于是去google查了一下,看到mysql在启动的时候会查找/etc/my.cnf、DATADIR/my.cnf，USER_HOME/my.cnf.
　　于是我执行了如下代码:find / -name "my.cnf",居然在/usr/local/mysql这个目录下发现了my.cnf文件,于是蚊子将这个文件删除了,然后再重启mysql服务,发现一切恢复了正常.
　　一些错误处理和日常维护,检查从服务器一般使用show slave status命令来检查,代码如下:

• 　　mysql> SHOW SLAVE STATUSG
  
• 　　*************************** 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 statusG;
  
• 　　*************************** 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) //开源软件:phpfensi.com
  

　　这个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的语句需要从二进制日志中取得两个事件.