mysql主从不同步问题解决

stop slave;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

start slave;

flush privileges;

然后观察一段时间，一直到244应用日志跟上243的master信息。。。。

补充内容：

mysqlbinlog主从写的机制：

一：在主库243操作数据库，然后分别在243和244上格式化当前的binlog,并且查看相关内容，结果如下：

create table liuliuliu ( id int)

insert into liuliuliu values(111)

insert into liuwenhe.liuliuliu values(11)

[root@S243 mybinlog]# mysqlbinlog --base64-output=decode-rows -v -v mysql-bin.001473 > binlog

[root@S243 mybinlog]# cat binlog | grep liuliuliu

create table liuliuliu ( id int)

insert into liuliuliu values(111)

insert into liuwenhe.liuliuliu values(11)

[root@S244 mybinlog]# mysqlbinlog --base64-output=decode-rows -v -v mysql-bin.001513 > binlog

[root@S244 mybinlog]# cat binlog | grep liuliuliu

create table liuliuliu ( id int)

insert into liuliuliu values(111)

insert into liuwenhe.liuliuliu values(11)

结论：在主库操作的数据库，相关记录必然记录到主库binlog，值得注意的是从库也把相关的信息记录进它自己的binlog中. 然后我猜应该是做了特别的标记，使得244并不会把从243接收到的相关操作信息再次传回给243，

二：binlog的三种格式以及binlog的组提交（摘自网络）：

2.1：Mysql binlog日志有三种格式，分别为Statement,MiXED,以及ROW！

2.1.1.Statement：每一条会修改数据的sql都会记录在binlog中。

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。(相比row能节约多少性能与日志量，这个取决于应用的SQL情况，正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题。)

缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同 的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数， last_insert_id()，以及user-defined functions(udf)会出现问题).

使用以下函数的语句也无法被复制：

* LOAD_FILE()

* UUID()

* USER()

* FOUND_ROWS()

* SYSDATE() (除非启动时启用了 --sysdate-is-now 选项)

同时在INSERT ...SELECT 会产生比 RBR 更多的行级锁

2.1.2：Row:不记录sql语句上下文相关信息，仅保存哪条记录被修改。

优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题

缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。

2.1.3：Mixedlevel: 是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种.新版本的MySQL中队row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。

2.1.4：Binlog基本配制与格式设定

1.基本配制

Mysql BInlog日志格式可以通过mysql的my.cnf文件的属性binlog_format指定。如以下：

binlog_format = MIXED //binlog日志格式

log_bin =目录/mysql-bin.log //binlog日志名

expire_logs_days = 7 //binlog过期清理时间

max_binlog_size 100m //binlog每个日志文件大小

2.1.5：Binlog日志格式选择

Mysql默认是使用Statement日志格式，推荐使用MIXED.

由于一些特殊使用，可以考虑使用ROWED，如自己通过binlog日志来同步数据的修改，这样会节省很多相关操作。对于binlog数据处理会变得非常轻松,相对mixed，解析也会很轻松(当然前提是增加的日志量所带来的IO开销在容忍的范围内即可)。

2.1.6：针对binlog的三种格式而产生相应的主从复制的三种方式：

（１）：基于语句（Statement）的复制： 在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。

（２）：基于行（row)的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持

（３）：混合类型（mixed)的复制: 默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

2.2： binlog组提交（5.6），5.6默认就是组提交，不需要开启，这是它的内部机制

它的基本思想是：引入队列机制保证innodb commit顺序与binlog落盘顺序一致，并将事务分组，组内的binlog刷盘动作交给一个事务进行，实现组提交目的。binlog提交将提交分为了3个阶段，FLUSH阶段，SYNC阶段和COMMIT阶段。每个阶段都有一个队列，每个队列有一个mutex保护，约定进入队列第一个线程为leader，其他线程为follower，所有事情交由leader去做，leader做完所有动作后，通知follower刷盘结束。在 mysql 5.5 中，只有当 sync_binlog = 0 时，才能使用 group commit，在 mysql 5.6中都可以进行 group commit log组提交基本流程如下：

FLUSH 阶段

1) 持有Lock_log mutex [leader持有，follower等待]

2) 获取队列中的一组binlog(队列中的所有事务)

3) 将binlog buffer到I/O cache

4) 通知dump线程dump binlog

SYNC阶段

这个阶段和参数sync_binlog有关系，

1) 释放Lock_log mutex，持有Lock_sync mutex[leader持有，follower等待]

2) 将一组binlog 落盘(sync动作，最耗时，假设sync_binlog为1)。

COMMIT阶段

1) 释放Lock_sync mutex，持有Lock_commit mutex[leader持有，follower等待]

2) 遍历队列中的事务，逐一进行innodb commit

3) 释放Lock_commit mutex

4) 唤醒队列中等待的线程

说明：由于有多个队列，每个队列各自有mutex保护，队列之间是顺序的，约定进入队列的一个线程为leader，因此FLUSH阶段的leader可能是SYNC阶段的follower，但是follower永远是follower。

通过上文分析，我们知道MYSQL目前的组提交方式解决了一致性和性能的问题。通过二阶段提交解决一致性，通过redo log和binlog的组提交解决磁盘IO的性能。

2.3：关于参数sync_binlog的理解：

sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。

sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘，当数据库crash的时候至少会丢失N-1个transactions

sync_binlog=1，每一个transaction commit都会调用一次fsync()，此时能保证数据最安全但是性能影响较大。

总结：mysql主从复制，正常情况下slave读取master的binlog_buffer中的binlog,并不是等写到binlog底层文件后才读取的，只有当slave出现故障后，但是此时maser库依旧在跑业务，当从新开始start slave；这时候读取的binlog就会从磁层磁盘binlog文件读取。

延伸内容：

异 步复制：咱们现在大多数都是异步复制的，MySQL本身支持单向的、异步的复制。异步复制意味着在把数据从一台机器拷贝到另一台机器时有一个延时 – 最重要的是这意味着当应用系统的事务提交已经确认时数据并不能在同一时刻拷贝/应用到从机。通常这个延时是由网络带宽、资源可用性和系统负载决定的。然 而，使用正确的组件并且调优，复制能做到接近瞬时完成。

 同步复制：使用MyISAM或者InnoDB存储引擎的MySQL本身并不支持同步复制，同步复制可以定义为数据在同一时刻被提交到一台或多台机器，通常这是通过众所周知的“两阶段提交”做到的，也就是说保证数据至少在一台slave上正常commit。虽然这确实给你在多系统中保持一致性，但也由于增加了额外的消息交换而造成性能下降。

半同步复制：是基于Google为MySQL开发的半同步复制的插件。半同步复制的原理是，一个事务在主服务器上执行完成后，必须至少确保至少在一台从服务器上执行完成后，事务才算提交成功。如果在一定时间内从服务器没有响应，则会自动降级为异步复制。

这个半同步复制是建立在异步复制的基础之上进行的。