Mysql中主从复制的log-bin详解以及出现的常见问题

分享个主从不同步的问题

bin-log日志

bin-log日志的定义和作用我就直接百度过来了，也很好理解的。

基本定义：二进制日志，也成为二进制日志，记录对数据发生或潜在发生更改的SQL语句，并以二进制的形式保存在磁盘中；

作用：可以用来查看数据库的变更历史（具体的时间点所有的SQL操作）、数据库增量备份和恢复（增量备份和基于时间点的恢复）、MySQL的复制（主主数据库的复制、主从数据库的复制）

文件位置：默认存放位置为数据库文件所在目录下

文件的命名方式： 名称为hostname-bin.xxxxx （重启mysql一次将会自动生成一个新的binlog）

状态的查看：mysql> show variables like '%log_bin%';

以上这些都是百度复制过来的，我们看完后也很好理解了，bin-log日志也就是二进制日志，只要数据发生改变（增删改），都是记录到日志里。所以我们可以通过bin-log日志进行数据恢复。

1、开启big-log日志

打开MySQL的配置文件：/etc/my.cnf（我这里的MySQL是放在/etc下的），

在配置文件中添加此行代码： log-bin=mysql-bin，

然后重启下MySQL：service mysqld restart

进入数据库查看是否开启成功：mysql> show variables like '%log_bin%';

接着我实际演示一次：

根据介绍流程操作是成功的，大家要自己动手测试下。

2、bin-log日志的使用

首先我们来认识几个与bin-log相关的MySQL命令：

flush logs; 会多一个最新的bin-log日志

show master status; 查看最后一个bin-log日志的相关信息

reset master; 清空所有的bin-log日志

我们先看一下bin-log日志放在哪：

 

这个时候我们进入到MySQL里，查看一下最后一个bin-log日志信息

然后刷新一下bin-log日志，再查看下最后一个bin-log日志，此时已经变成了000002这个版本了，以后的增删改操作就记录在这个最新的bin-log日志里

我们可以看一下bin-log日志存放的目录，这里就多出了新版本的bin-log日志000002，以后的操作都是记录在这里面了

 

 

接着我们创建一个数据库，然后创建一个表，再插入一些记录，然后再查看下日志信息有没有变化

接着我们再看下bin-log信息，此时位置已经发生了变化，也就是说已经对刚才的操作进行了记录。

接着我们打开二进制日志，看看里面就是记录了什么，我们需要知道的是，如果用vi打开二进制日志的乱码的，所以我们需要使用下面的命令进行打开：

/usr/local/mysql/bin/mysqlbinlog --no-defaults mysql-bin.000002

我把打开的日志截图看看（截图太多了希望大家别介意，因为想让大家了解的更清楚），我用红色圈起来的都是我们需要注意的，end_log_pos大概的意思是这这个操作的最后位置在哪，例如：end_log_pos 400，在299-400之间是存了插入操作的记录的，insert into test1 values(1); 其他的能看懂就自己看，不懂看错就跳过也没事。

 

下面我们来做一个利用二进制恢复数据库数据的操作。

流程如下：

（1）先生成一个新的日志文件（000003）；

（2）然后删除test1表的数据；

（3）然后利用前一个日志恢复数据（000002）；

（4）最后查看是否恢复成功。

具体如下：

 

接着用mysql-bin.000002进行数据恢复：我们到数据库里查看下是否恢复成功了：

这个时候，已经证明我们使用bin-log日志进行数据恢复成功了。

在这里可能有博友就会问：“博主，你那实验在测试时是先刷新了个bin-log版本，假如我没刷新，然后删除语句就记录在了000002这个版本了，那么你怎么恢复啊？因为你使用000002进行恢复，也是会执行删除操作的记录的，这不是坑爹吗？”。

没错，假如有博友能想到这个了，那说明上面所讲的知识点基本上理解了，并有自己去思考。这里我们当然有办法在一个有删除记录的bin-log日志进行数据恢复，你有想过为啥会有end-log-pos这个位置记录吗？这就是我们解决以上问题的方法之一：定位恢复，即恢复你想恢复的数据。我打个比方，在一个bin-log日志里，insert操作的end-log-pos为250，而delete的end-log-pos为300。那么，我们可以将数据恢复到250这个位置，而之后的操作就不恢复了，这里我们先看一下mysqlbinlog的常用参数：

--stop-position = "100"    --start-position = "50" 根据开始位置或者结束位置来恢复自己想恢复的参数

--stop-date= "2016-03-02 12:00:00"    --start-date= "2016-03-02 11:55:00" 根据开始日期时间或者结束位置来恢复自己想恢复的参数

例如：

/usr/local/mysql/bin/mysqlbinlog --no-defaults mysql-bin.000001 --stop-position="450"|mysql -uroot -p

此句的意思就是将数据恢复到位置在450之前的就OK了，在450之后（假如后面还有操作记录）的就不需要进行恢复了。

下面我通过实验操作一次：

首先重置一下bin-log日志，让其恢复到初始状态

接着插入几条数据，再删除些数据

这个时候我们可以看一下mysql-bin.000001里的内容（这里我只截取一部分）：

由于我们知道删除的是大于2的数据，所以知道3和4被删除了，这里我们就恢复3和4（当然，假如误删了整个表，这里就恢复到删除操作之前的就可以了）执行如下命令：

 

接着我们进入数据库查看下是否进行恢复成功：

 

到此，bin-log日志的讲解已经告一段落。

常见主从出现的问题

常见错误

最常见的3种情况

这3种情况是在HA切换时，由于是异步复制，且sync_binlog=0，会造成一小部分binlog没接收完导致同步报错。

第一种：在master上删除一条记录，而slave上找不到。

Last_SQL_Error: Could not execute Delete_rows event on table hcy.t1; 
Can't find record in 't1', 
Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; 
the event's master log mysql-bin.000006, end_log_pos 254

第二种：主键重复。在slave已经有该记录，又在master上插入了同一条记录。

Last_SQL_Error: Could not execute Write_rows event on table hcy.t1; 
Duplicate entry '2' for key 'PRIMARY', 
Error_code: 1062; 
handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000006, end_log_pos 924

第三种：在master上更新一条记录，而slave上找不到，丢失了数据。

Last_SQL_Error: Could not execute Update_rows event on table hcy.t1;
Can't find record in 't1', 
Error_code: 1032; 
handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000010, end_log_pos 263

问题：

1、mysql-5.6.20主从同步错误之Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND

方法一： 

1.Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND 是主从更新时丢失数据，导致主从不一致，在从库上mysql> show slave status\G;结果如下

2.在master上，用mysqlbinlog 分析下出错的binlog日志在干什么：

/usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000042 | grep -A '10' 455223179

3.查询master和slave上对应的数据，主库有而从库没有

　select * from log_silver where id=9097514\G;

4.把丢失的数据在slave上填补，然后跳过报错

mysql> insert into t1 values();
mysql> stop slave ;set global sql_slave_skip_counter=1;start slave;

5.查看同步结果

mysql> show slave status\G;

方法二：

1.如果用方法一还是不能主从同步，查询到这个错误是mysql的bug导致的

2.解决的办法：

　　1.最好的办法是升级数据库 保证bug不会重现。

　　2.利用配置参数 来躲避这个bug    vi /etc/my.cnf

　　　　slave-skip-errors = 1032,xxxx,xxxx ....

　　3.临时逃避此次错误。　　　

mysql> stop slave;
Query OK, 0 rows
affected (0.00 sec)
 
mysql> set global sql_slave_skip_counter=1;
Query OK, 0 rows
affected (0.00 sec)
 
mysql> start slave;
Query OK, 0 rows
affected (0.00 sec)

方案三：解决：使用强悍的瑞士×××percona-toolkit 工具 'pt-table-checksum'，'pt-table-sync'
注：这两个工具都是在Master上执行。

问题二：1062错误----主键冲突，出现这种情况就是从库出现插入操作，主库又重新来了一遍，iothread没问题，sqlthread出错

解决方案：

mysql> stop slave;
 
mysql> set global sql_slave_skip_counter=1;

mysql> start slave;