MySQL数据库ab主从复制出错及解决过程
一、mysql主从服务器报错描述：
Slave_IO_Running=NO，Slave_SQL_Running=YES，Last_Errno=0 mysql slave stop ; mysql slave start; mysql show slave status ; 如果Slave_IO_Running=YES ...
解决过程 ：
1 如果：Slave_IO_Running=NO，Slave_SQL_Running=YES，Last_Errno=0
mysql> slave stop ;
mysql> slave start;
mysql> show slave status ;
如果Slave_IO_Running=YES，Slave_SQL_Running=YES，Last_Errno=0
那么，数据库的replication已经成功启动。2 如果提示有duplicated数据，直接delete那条数据之后的，然后
mysql> slave stop ;
mysql> slave start;
mysql> show slave status ;
如果Slave_IO_Running=YES，Slave_SQL_Running=YES，Last_Errno=0
那么，数据库的replication已经成功启动。3 如果还不行，查看错误日志，看看是卡在那个binlog上了，比如 ： 卡在000079
则：
SLAVE STOP;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000080', MASTER_LOG_POS=0;
SLAVE START;
4 如果错误提示： [mysql]Table tblName is marked as crashed and should be repaired
Mysql提示tblName表格已损坏，需要修复，解决方法：
进入到对应的数据库目录：
cd /var/lib/mysql/dbname
使用myisamchk修复：
shell> myisamchk -r tblName
如果提示failed
shell> myisamchk -f tblName
强制修复
恢复时间比较长，耐心等待修复完成
然后重启mysql就行
二、mysql主从服务器报错描述：
报警mysqla is down...机房人员反馈是HARDWARE ERROR（后面省略），让机房人员记下错误 信息，让他们帮忙重启，结果又正常工作了，（导出数据），过 了一会又出现报警，mysql_AB error。
解决过程 ：1、检查从库show slave status \G;显示
Slave_IO_Running:Yes
Slave_SQL_Running:No
还出现 了1062错误，还提示：
Last_SQL_Error: Error 'Duplicate entry '1001-164761-0' for key 'PRIMARY'' on query. Default database: 'bug'. Query: 'insert into misdata (uid,mid,pid,state,mtime) values (164761,1001,0,-1,1262623560)'
很显然，由于主库重启导致 从库数据不同步而且主键冲突。查看error 日志发现error日志文件变得好大，比以前大了将近好几倍，
tail -f mysql_error.log 最开始查看到的是这条信息
发现这条信息
[ERROR] Slave SQL: Error 'Duplicate entry '1007-443786-0' for key 'PRIMARY'' on query. Default database: 'ufo'. Query: 'insert into misdata (uid,mid,pid,sta
te,mtime) values (443786,1007,0,-1,1262598003)', Error_code: 1062
100104 17:39:05 [Warning] Slave: Duplicate entry '1007-443786-0' for key 'PRIMARY' Error_code: 1062
100104 17:39:05 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'ufolog.000058
8' position 55793296
报错和上面的意思差不多,最先想到的就是首先手动同步一下，从库上首先 stop slave;停止同步
进入主库锁表，
FLUSH TABLES WITH READ LOCK；
mysql> show master status;
+-------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+-----------+--------------+------------------+
| ufo.000063 | 159164526 | | |
+-------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)
进入从库
mysql>change master to master_host='192.168.1.141', master_user='slave',
master_password='xxx',
master_port=3306,
master_log_file='ufo.000063',
master_log_pos=159164526;完成上面这些后
start slave;
回到主库
unlock tables; 解锁回到从库 查看
show slave status \G;
发现正常了，长处了一口气。可是还没过一分钟，发现又开始报错了，还是最开始那个错误，再次查看一下日志，让我感到意外的是tail -f mysql_error.log 出现大量的
.......
100106 16:54:21 [Warning] Statement may not be safe to log in statement format. Statement: delete from `system_message_1` where `to_uid` = 181464 ORDER BY `id` ASC LIMIT 1
.........
日志里面有大量的这种警告，意思应该是statement 格式不安全，用vim 打开他看了一下，发现好多这类警告，我说为什么错误日志怎么变这么大了呢！！
statement format 应该是 binlog的一种格式，进入从库查看一下 www.it165.net
show global variables like 'binlog_format';
果然当前的格式为statement我需要把格式改为 mixed格式
修改从库的 my.cfg
在[mysqld]下面加入下面这行
binlog_format=mixed然后重启mysql服务，发现错误日志里的 警告 都停止了。
三、报错描述：
MySQL5.6之前的备库复制有两个工作线程：IO线程和SQL线程。SQL线程异常导致复制被中断。许多DBA会遭受备库复制线程中断的报警，具体说来常见的错误有如下几类：
1032错误 – HA_ERR_KEY_NOT_FOUND
1062错误 – HA_ERR_FOUND_DUPP_KEY （HA_ERR_FOUND_DUPP_KEY 或 HA_ERR_FOUND_DUPP_UNIQUE）问题背景：
主备数据一不致，往往是由于MySQL的Bug导致，其中MySQL复制相关的Bug从每次Release Notes都可以看到相关的身影，就我们最近两周线上遇到的列举如下：
Table map set to 0 after altering MyISAM table （对InnoDB表也会触发）
Failing assertion: trx->active_trans when renaming a table with active trx
Querying I_S.GLOBAL_TEMPORARY_TABLES or TEMPORARY_TABLES crashes threads working with temp tables（非DEBUG版本也存在）当然，最大的一个未知的因素是主库宕机，甚至为保证服务可用而做的主备切换，有多少数据不一致，我们心里没有谱。只有依赖后续的定时扫描来做事后检测。虽然我们目前做的MHA从一定程度上回减少数据不一致的数量，但也无法做到数据严格一致。这个问题是MySQL社区的一大难题，在此不展开讨论。
另外，有发生异常时，DBA的不严格操作也可能会加剧这种数据不一致的影响，例如，遇到错误时，跳过若干错误，直到复制能正常工作。
root@(none) 09:43:26>set global sql_slave_skip_counter=1;
解决思路：
对于我们线上最常见的两种类型进行智能化地处理，即 HA_ERR_KEY_NOT_FOUND 或 HA_ERR_FOUND_DUPP_KEY的错误，利用ROW模式复制的特点，在执行事件遇到错误时，进行如下转化：HA_ERR_KEY_NOT_FOUND：
对于update事件 UPDATE_ROWS_EVENT 直接插入前镜像再做更新
对于delete事件 DELETE_ROWS_EVENT 直接插入前镜像再做删除。
HA_ERR_FOUND_DUPP_KEY：
对于update事件 UPDATE_ROWS_EVENT先删除后镜像再更新
对于insert事件 WRITE_ROWS_EVENT 直接以覆盖形式插入（即先删再插或转化成更新）。新增变量及状态
对slave_exec_mode增加 SMART 模式(STRICT | IDEMPOTENT | SMART)
root@(none) 01:39:34>set global slave_exec_mode=SMART;
Query OK, 0 rows affected (0.00 sec)root@(none) 01:39:39>select @@slave_exec_mode;
+——————-+
| @@slave_exec_mode |
+——————-+
| SMART |
+——————-+
1 row in set (0.00 sec)增加对smart处理结果的统计：
root@(none) 01:46:51>show status like ‘smart%’;
+———————+——-+
| Variable_name | Value |
+———————+——-+
| Smart_handle_dup_pk | 1 |
| Smart_handle_failed | 0 |
| Smart_handle_no_pk | 1 |
+———————+——-+
3 rows in set (0.00 sec)
运行情况：从线上运行情况下，此patch解决了绝大部分复制中断异常，大大减少DBA半夜三更被警告吵醒手动修复异常复制的频率。总体而言，是一个非常成功的补丁。
但仍然存一此尚未完善的场景，例如唯一键约束或外键约束情况下，更新失败还是无法处理。在线上某个应用上，SMART还是不能处理unique key更新失败问题。
四、slave日志中的错误代码1：
[ERROR] Error reading packet from server: Client requested master to start replication from impossible position; the last event was read from 'mysql-bin.000016' at 455562731, the last byte read was read from 'mysql-bin.000016' at 4. ( server_errno=1236)
120725 23:19:17 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position; the last event was read from 'mysql-bin.000016' at 455562', Error_code: 1236解决办法：
先查看主服务器中对应日志文件是否有此行，使用的命令是
mysqlbinlog /YOURPATH/'mysql-bin.000016 >>/test
查找有没有455562对应的行 或者和这个数字很接近的行 然后使用chang master to 命令跳过这行，命令格式如下
stop slave;
CHANGE MASTER TO MASTER_HOST='192.168.192.45',MASTER_USER='XXXX',MASTER_PASSWORD='XXXX',MASTER_LOG_FILE='mysql-bin.000016',MASTER_LOG_POS=455678;
start slave;
如果你找不到455562或者和这个数字很接近的行，你可以使用如下命令查看'mysql-bin.000016 的行数
ls -la mysql-bin.000016
如果统计的行数远远少于455562，你可以跳过这个日志，从下一个日志开始复制，命令如下
stop slave;
CHANGE MASTER TO MASTER_HOST='192.168.192.45',MASTER_USER='backup',MASTER_PASSWORD='weiphone',MASTER_LOG_FILE='mysql-bin.000017',MASTER_LOG_POS=4;
start slave;五、错误类型代码2：
mysql slave Error_code: 1062 1054解决过程 ：
如果日志中出现了这样代码，可能是错误的select，或update操作，master是跳过这些操作，但是被记录到了二进制日志中，slave会依据二进制中的语句做相同的动作，就会报错，知道原理了要做的操作就很简单了
stop slave;
set gloable sql_slave_skip_counter=n; //n为你想跳过错误语句的个数
start slave;
如果你不想再遇到这种错误，可以写到slave配置文件中格式如下：
slave_skip_errors = 1062
slave_skip_errors = all //跳过所有类型的错误码六、错误提示：
mysql> start slave;
ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log[root@slave ~]# tail /var/log/mysqld.log
090105 11:53:52 [ERROR] Failed to open the relay log '/var/run/mysqld/mysqld-relay-bin.000013' (relay_log_pos 235)
090105 11:53:52 [ERROR] Could not find target log during relay log initialization
...[root@slave mysql]# ls /var/run/mysqld/
mysqld-relay-bin.000001 mysqld-relay-bin.000003 mysqld-relay-bin.000005 mysqld-relay-bin.index
mysqld-relay-bin.000002 mysqld-relay-bin.000004 mysqld-relay-bin.000006
[root@slave mysql]# cat master.info
14
mysql-bin.000010
1235
192.168.3.21
slave
29019853
3307
60
0
[root@slave mysql]# cat relay-log.info
/var/run/mysqld/mysqld-relay-bin.000013
235
mysql-bin.000010
12353
50703
解决过程：
[root@slave ~]# service mysqld stop
[root@slave mysql]# rm -fr master.info
[root@slave mysql]# rm -fr relay-log.info
[root@slave ~]# grep skip-slave-start /etc/my.cnf
skip-slave-start
[root@slave ~]# service mysqld start
[root@master ~]# mysql -uroot -p
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000011 | 98 | | |
+------------------+----------+--------------+------------------+
[root@slave ~]# mysql -uroot -pmysql> change master to
master_log_file='mysql-bin.000011',
master_log_pos=98,
master_host='192.168.3.21',
master_port=3307,
master_user='slave',
master_password='12345678';
Query OK, 0 rows affected (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.3.21
Master_User: slave
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000011
Read_Master_Log_Pos: 98
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000011
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: 98
Relay_Log_Space: 235
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: 01 row in set (0.00 sec)
导致 mysql 主从复制出错的原因有很多修复的办法也很多，希望遇到和这次一样问题的朋友看到这篇文章后会得到 一些启发和解决问题的方法
转载于:https://blog.51cto.com/dulinux/1704702
330
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
