MySQL的Binlog与主从复制

在MySQL中，可以使用多种存储引擎。其中最常用的InnoDB引擎支持事务，Redo Log和Undo Log就是InnoDB里面的工具，用于实现事务。而Binlog是MySQL层面的东西，用于实现主从复制，与使用的存储引擎无关。

通过监听并解析Mater的Binlog，也可以实现将MySQL中的数据同步到其他应用组件中（比如更新缓存）的效果。

在不发生宕机的情况下，未提交的事务和已回滚的事务是不写入Binlog日志中的，只有提交成功的事务才写入Binlog日志。这一点和Redo Log不一样，Redo Log中会记录未提交、已回滚的事务内容。

Binlog是一种逻辑日志——例如Binlog的statement格式记录原始SQL语句、RAW格式记录某一行修改前后的值——且一个事务的日志在Binlog中是连续排列的，因此要求每个事务都要串行地写入，这意味着每个事务在写Binlog之前都要排他地锁住Binlog，这会导致写的效率很低。MySQL5.6之后，通过pipline技术异步地批量化将已提交的事务内容写入Binlog。

一个事务的提交既要写Binlog日志又要写Redo Log日志，如何保证双写的原子性？一个写成功，写另外一个时发生宕机，重启后如何处理？在讨论这个问题之前，先说下Binlog自身写入的原子性问题：Binlog刷盘到一半，出现宕机，这个问题和Redo Log的写入原子性是同样的问题，通过类似于checksum的办法或者Binlog中的结束标记来判断出某个事务的Binlog这是不是不完整的Binlog，从而把不完整的部分截掉。对于客户端来说，此时宕机，事务肯定是没有提交成功的，所以截掉也没问题。下面来讲如何保证双写Binlog和Redo Log的原子性。由于双写Binlog和Redo Log发生在同一台机器上，这其实是一个内部分布式事务，可以使用两阶段提交法来实现双写的原子性。简单来说就是：

1）第一阶段（准备阶段）：MySQL Server要求innoDB完成将事务内容写入Redo Log中的工作，只等事务提交；以及，MySQL Server完成Binlog内容写入内存的工作，只等刷盘。两个都准备好之后，会向MySQL Server发送OK反馈，MySQL Server紧接着执行第二阶段。

2）第二阶段（提交阶段）：收到客户端的Commit指令，MySQL Server先将内存中的Binlog刷盘，然后让innoDB执行事务的提交。两个都完成之后，会向MySQL Server发送OK反馈，两阶段提交结束。

若双写Binlog和Redo Log的过程中发生宕机，处理思路为：

1）若宕机发生在第一阶段，此时Binlog还在内存中，宕机导致全部消失。而Redo Log记录了未提交的日志，MySQL Server重启后感知到Binlog中不存在Redo Log中记录的未提交事务，会自行回滚未提交事务的Redo Log日志；

2）若宕机发生在第二阶段，Binlog写了一半，innoDB还未执行提交，MySQL Server重启后会对Binlog做截断，对Redo Log中记录的未提交事务做回滚；

3）若宕机发生在第二阶段，Binlog写入成功，innoDB还未执行提交，MySQL Server重启后会通过checksum的办法或者Binlog中的结束标记感知到Binlog写入成功，紧接着对Binlog中存在的、但Redo Log未提交的事务发起提交。

在MySQL的Master / Slave集群模式中，有三种主从复制模式：

1）同步复制：所有的Slave都收到Master发送的Binlog，并且接收完，Master才认为事务提交成功，再对客户端返回成功。这种方式最安全，但是性能很差；

2）异步复制：只要Master事务提交成功，就对客户端返回成功。后台线程异步地将Binlog发送给Slave，然后Slave回放Binlog。这种方式性能最好，但是可能会导致数据丢失；

3）半同步复制：Master事务提交后，同时把Binlog同步给Slave，只要有部分（数量可以配置）Slave收到了Binlog，就认为事务提交成功，对客户端返回。

对于半异步复制，如果Slave超时后还未返回，也会退化为异步复制。所以无论是异步复制还是半异步复制，都无法严格保证主从中的数据完全一致，主从复制的延迟会导致主节点宕机后部分数据未来得及同步到从节点，从而丢失数据。但是主节点宕机后，还是要立即切换到从节点，保证服务的可用（牺牲一致性保证可用性），数据的丢失可以通过后续的人工干预来补偿。

Mysql5.5和Mysql5.6主从同步设置

主服务器(MySQL5.5)

从服务器(MySQL5.6)

1、在主库创建从库用户

insert into mysql.user(Host,User,Password) values('localhost','slaveuser',password('123456'))

flush privileges

grant replication slave on *.* to 'slaveuser' ' identified by '123456' with grant option

2、修改主库配置文件my.cnf

#编辑配置文件，在[mysqld]部分添加下面内容

vi /etc/my.cnf

#设置服务器id

server-id=80

#启动MySQ二进制日志系统

log_bin=mysql-bin

#需要同步的数据库名，如果有多个数据库，可重复此参数，每个数据库一行

binlog-do-db=api

#不同步mysql系统数据库

binlog-ignore-db=mysql

#重启MySQL

service mysqld restart

#进入mysql控制台

mysql -u root -p

#查看主库同步状态

show master status\G

3、修改从库配置文件my.cnf（保证主从server-id不一样，一般用ip最后的字段）

vi /etc/my.cnf

#设置服务器id

server-id=90

3、从库增加配置

#进入mysql控制台

mysql -u root -p

#停止slave同步进程

stop slave

#执行同步语句

change master to master_host=' ',master_user='slaveuser',master_password='123456',master_log_file='mysql-bin.000001' ,master_log_pos=

#开启slave同步进程

start slave

#查看从库同步状态

show slave status\G

Slave_SQL_Running: No mysql同步故障解决

如果数据不同步可以尝试该资料

mysql>show slave status\G

Slave_IO_Running: Yes

Slave_SQL_Running: No

Last_Errno: 1062

....

Seconds_Behind_Master:NULL

原因：

1.程序可能在slave上进行了写 *** 作

2.也可能是slave机器重起后，事务回滚造成的.

解决办法I：

1.首先停掉Slave服务：slave stop

2.到主服务器上查看主机状态：

记录File和Position对应的值。

mysql>show master status

+------------------+-----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+-----------+--------------+------------------+

| mysql-bin.000020 | 135617781 | | |

+------------------+-----------+--------------+------------------+

1 row in set (0.00 sec)

3.到slave服务器上执行手动同步：

mysql>change master to

>master_host='master_ip',

>master_user='user',

>master_password='pwd',

>master_port=3307,

>master_log_file='mysql-bin.000020',

>master_log_pos=135617781

1 row in set (0.00 sec)

mysql>slave start

1 row in set (0.00 sec)

再次查看slave状态发现：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

...

Seconds_Behind_Master: 0

解决办法II：

mysql>slave stop

mysql>set GLOBAL SQL_SLAVE_SKIP_COUNTER=1

mysql>slave start

---