mysql master slave - yaokun123/php-wiki GitHub Wiki
mysql主从同步原理
1、查看binlog
show variables like '%log_bin%'
2、查看所有的binlog文件
show binary logs
3、查看binlog文件中的内容
show BINLOG EVENTS in 'mysql-bin.010716';(谨慎使用)
4、查看默认事务是否开启
show variables like '%AUTOCOMMIT%'
其中Master负责写操作的负载，也就是说一切写的操作都在Master上进行，而读的操作则分摊到Slave上进行。这样一来的可以大大提高读取的效率。在一般的互联网应用中，经过一些数据调查得出结论，读/写的比例大概在 10：1左右 ，也就是说大量的数据操作是集中在读的操作，这也就是为什么我们会有多个Slave的原因。
但是为什么要分离读和写呢？熟悉DB的研发人员都知道，写操作涉及到锁的问题，不管是行锁还是表锁还是块锁，都是比较降低系统执行效率的事情。我们这样的分离是把写操作集中在一个节点上，而读操作其其他的N个节点上进行，从另一个方面有效的提高了读的效率，保证了系统的高可用性。
一、复制原理
MySQL的主从复制支持两种方式：基于行 和 基于语句
基于语句的复制在MySQL3.23中就已经有了，而基于行的方式则在5.1中才实现。其本质都是基于主库的binlog来实现的，主库记录binlog，然后从库将binlog在自己的服务器上重放，从而保证了主、从的数据一致性。
1.1、binlog
MySQL中日志分为两个维度，一个是MySQL服务器的，一个是底层存储引擎的。而上文提到的binlog就是属于MySQL服务器的日志，binlog也叫二进制日志，记录了所有对MySQL所做的更改。
基于行、语句的复制方式跟binlog的存储方式有关系。binlog有三种存储格式，分别是Statement、Row和Mixed。
Statement基于语句，只记录对数据做了修改的SQL语句，能够有效的减少binlog的数据量，提高读取、基于binlog重放的性能
Row 只记录被修改的行，所以Row记录的binlog日志量一般来说会比Statement格式要多。基于Row的binlog日志非常完整、清晰，记录了所有数据的变动，但是缺点是可能会非常多，例如一条update语句，有可能是所有的数据都有修改；再例如alter table之类的，修改了某个字段，同样的每条记录都有改动。
Mixed Statement和Row的结合，怎么个结合法呢。例如像update或者alter table之类的语句修改，采用Statement格式。其余的对数据的修改例如update和delete采用Row格式进行记录。
为什么会有这么多方式呢？因为Statement只会记录SQL语句，但是并不能保证所有情况下这些语句在从库上能够正确的被重放出来。因为可能顺序不对。
MySQL什么时候会记录binlog呢？是在事务提交的时候，并不是按照语句的执行顺序来记录，当记录完binlog之后，就会通知底层的存储引擎提交事务，所以有可能因为语句顺序错误导致语句出错。
1.2、查看binlog
这里拿MySQL 5.6举例子，binlog默认是处于关闭状态的。我们可以通过命令show variables like '%log_bin%' 来查看关于binlog的配置。
log_bin 代表是否开启了binlog，其默认值为OFF
log_bin_basename binlog存储文件的完整名称，会在默认的文件名后面添加上递增的序号，就例如mysql-bin.000001
log_bin_index binlog索引文件名称，例如mysql-bin.index
sql_log_bin 在binlog开启的时候，可以禁用当前session的binlog
你可以在MySQL中通过命令show binary logs查看所有的binlog文件
知道了有哪些文件之后我们可以来看看binlog文件中的内容，可以在MySQL通过show binlog events命令来查看。
show binglog events 查看第一个binlog文件，我们也可以通过in参数来指定，假设我们想看的文件名是mysql-bin.000001，
那么可以使用命令show binlog events in 'mysql-bin.000001'来查看指定的binlog文件
1.3、复制的核心步骤
分为四步走：
- 主库对所有DDL和DML产生的日志写进binlog；
- 主库生成一个 binlog dump 线程，读取主库上的binlog event发送给从库的I/O线程
- 从库的I/O线程获取到binlog event之后将其写入到自己的Relay Log中。
- 从库的SQL Thread会读取relay log文件中的日志解析成具体操作，将主库的DDL和DML操作事件重放。
总结来说，主库上只会有一个线程，而从库上则会有两个线程。
1.4、Relay Log
relay log其实和binlog没有太大的区别，在MySQL 4.0 之前是没有Relay Log这部分的，整个过程中只有两个线程。但是这样也带来一个问题，那就是复制的过程需要同步的进行，很容易被影响，而且效率不高。例如主库必须要等待从库读取完了才能发送下一个binlog事件。这就有点类似于一个阻塞的信道和非阻塞的信道。
引入了Relay Log之后，让原本同步的获取事件、重放事件解耦了，两个步骤可以异步的进行，Relay Log充当了缓冲区的作用。Relay Log有一个relay-log.info的文件，用于记录当前复制的进度，下一个事件从什么Pos开始写入，该文件由SQL线程负责更新。
1.5 Relay Log核心参数
接下来让我们了解一下Relay Log的核心参数。
max_relay_log_size 中继日志的最大size，默认值0，如果为0就会取默认的size 1G，否则就为设置的值
relay_log 定义relay的名称，默认为主机名+relay-bin，例如像hostname-relay-bin
relay_log_basename 中继日志的全路径，即路径 + 文件名，例如/path/to/hostname-relay-bin，最大长度为256
relay_log_index 定义中继日志的索引文件的全路径，同样其最大的长度为256. 其默认值为hostname + relay-bin.index，例如/path/to/hostname-relay-bin.index
relay_log_info_file 定义relay-log.info文件的名称
relay_log_info_repository 存放relay log重放的数据的方式，可以设置为FILE和TABLE。FILE代表将中继日志重放的数据记录在relay-info.log中，TABLE则将其存放在slave_relay_log_info这张表里。
relay_log_purge 是否自动清空不需要的中继日志，默认值为ON
relay_log_recovery 当从库宕机后，如果relay log损坏了导致部分的中继日志没有进行同步，则自动放弃所有未进行重放的中继日志，并从主库重新获取，默认值为OFF
relay_log_space_limit 设置中继日志的最大值，防止写满磁盘。但是不建议设置这个值，建议还是给中继日志需要的空间，0就是不限制，0也是默认值
sync_relay_log 用于控制中继日志写入磁盘的变量，假设值为n，那么在中继日志每接受n次binlog事件之后就会调用fdatasync()函数将中继日志强制的刷入磁盘；相反，如果值为0，则写入OS的缓冲区内，由OS调度决定何时将中继日志刷入磁盘，这样一来如果在没有刷入之前报错了，那么中继日志就会丢失。默认值是10000，也就是每向中继日志中写入1w次binlog事件就将中继日志强制的刷入磁盘。
sync_relay_log_info 该参数的影响跟参数relay_log_info_repository有一定关系，同时也跟是否使用支持事务的存储引擎有关系。该值默认也是10000
relay_log_info_repository为FILE，假设设置的值为N，那么每N次事务都会都会调用fdatasync()强制将relay-log.info刷入磁盘
relay_log_info_repository为TABLE，如果使用了支持事务的引擎，则该表每次事务结束都会被更新；如果没有使用事务引擎则会在写入N个binlog事件的时候更新该表。
relay_log_info_repository为FILE，MySQL不会调用fdatasync()，而是将刷入磁盘的调度交给OS；
relay_log_info_repository为TABLE，如果使用了支持事务的存储引擎，则每次事务的时候该表都会被更新；如果没有使用事务引擎，则永远不会被更新
当sync_relay_log_info为0时
当sync_relay_log_info大于0时
##二、 SQL语言共分为以下几大类
DQL（Data QUERY Languages）语句：即数据库定义语句，用来查询SELECT子句，FROM子句，WHERE子句组成的查询块，比如：select–from–where–grouop by–having–order by–limit
DDL（Data Definition Languages）语句：即数据库定义语句，用来创建数据库中的表、索引、视图、存储过程、触发器等，常用的语句关键字有：CREATE,ALTER,DROP,TRUNCATE,COMMENT,RENAME。增删改表的结构
DML（Data Manipulation Language）语句：即数据操纵语句，用来查询、添加、更新、删除等，常用的语句关键字有：SELECT,INSERT,UPDATE,DELETE,MERGE,CALL,EXPLAIN PLAN,LOCK TABLE,包括通用性的增删改查。增删改表的数据
DCL（Data Control Language）语句：即数据控制语句，用于授权/撤销数据库及其字段的权限（DCL is short name of Data Control Language which includes commands such as GRANT and mostly concerned with rights, permissions and other controls of the database system.）。常用的语句关键字有：GRANT,REVOKE。
TCL（Transaction Control Language）语句：事务控制语句，用于控制事务，常用的语句关键字有：COMMIT,ROLLBACK,SAVEPOINT,SET TRANSACTION。
三、复制方式
3.1 异步复制
首先就是异步，这也是MySQL默认的方式。在异步复制下，主库不会主动的向从库发送消息，而是等待从库的I/O线程建立连接，然后主库创建binlog dump线程，把binlog event发送给I/O线程，流程如下图。
主库在执行完自己的事务、记录完binlog之后就会直接返回，不会与客户端确认任何结果。然后后续由binlog dump线程异步的读取binlog，然后发送给从库。处理请求和主从复制是两个完全异步化的过程。
3.2 同步复制
同步模式则是，主库执行一个事务，那么主库必须等待所有的从库全部执行完事务返回commit之后才能给客户端返回成功，
值得注意的是，主库会直接提交事务，而不是等待所有从库返回之后再提交。MySQL只是延迟了对客户端的返回，并没有延后事务的提交。
同步模式用脚趾头想知道性能会大打折扣，它把客户端的请求和主从复制耦合在了一起，如果有某个从库复制线程执行的慢，那么对客户端的响应也会慢很多。
3.3 半同步复制
半同步相对于同步的区别在于，同步需要等待所有的从库commit，而半同步只需要一个从库commit就可以返回了。如果超过默认的时间仍然没有从库commit，就会切换为异步模式再提交。客户端也不会一直去等待了。
因为即使后面主库宕机了，也能至少保证有一个从库节点是可以用的，此外还减少了同步时的等待时间。
四、复制中的数据一致性
我们在1.3中讨论了复制的核心步骤，看似很简单的一个流程，主库的binlog dump去读取binlog，然后从库的I/O线程去读取、写入Relay Log，进而从库的SQL线程再读取Relay Log进行重放。
那如果I/O线程复制到一半自己突然挂掉了呢？又或者复制到一半主库宕机了呢？如果和保证数据一致性的呢？
我们上面提到过，有一个relay-log.info的文件，用于记录当前从库正在复制的binlog和写入的Relay Log的Pos，只要这个文件还在，那么当从库意外重启之后，就会重新读取文件，从上次复制的地方开始继续复制。这就跟Redis中的主从复制类似，双方要维护一个offset，通过对比offset，来进行psync增量数据同步。
但是在MySQL 5.5以及之前，都只能将复制的进度记录在relog-log.info文件中。换句话说，参数relay_log_info_repository只支持FILE，可以再回到上面的1.5 Relay Log核心参数看一下。所以只有在sync_relay_log_info次事务之后才会把relay-log.info文件刷入磁盘。
如果在刷入磁盘之前从库挂了，那么重启之后就会发现SQL线程实际执行到位置和数据库记录的不一致，数据一致性的问题就这么产生了。
所以在MySQL 5.6时，参数relay_log_info_repository支持了TABLE，这样一来我们就可以将复制的进度放在系统的mysql.slave_relay_log_info表里去，并且把更新进度、SQL线程执行用户事务绑定成一个事务执行。即使slave宕机了，我们也可以通过MySQL内建的崩溃恢复机制来使实际执行的位置和数据库保存的进度恢复到一致。
其次还有上面提到的半同步复制，主库会先提交事务，然后等待从库的返回，再将结果返回给客户端，但是如果在主库等待的时候，从库挂了呢？
此时主库上由于事务已经提交了，但是从库上却没有这个数据。所以在MySQL 5.7时引入了无损半同步复制，增加了参数rpl_semi_sync_master_wait_point的值，在MySQL 5.7中值默认为after_sync，在MySQL 5.6中默认值为after_commit。
after_sync 主库先不提交事务，等待某一个从库返回了结果之后，再提交事务。这样一来，如果从库在没有任何返回的情况下宕机了，master这边也无法提交事务。主从仍然是一致的
after_commit 与之前讨论的一样，主库先提交事务，等待从库返回结果再通知客户端
五、MySQL数据库主从同步延迟产生原因
MySQL的主从复制都是单线程的操作，主库对所有DDL和DML产生的日志写进binlog，由于binlog是顺序写，所以效率很高。Slave的SQL Thread线程将主库的DDL和DML操作事件在slave中重放。DML和DDL的IO操作是随即的，不是顺序的，成本高很多。另一方面，由于SQL Thread也是单线程的，当主库的并发较高时，产生的DML数量超过slave的SQL Thread所能处理的速度，或者当slave中有大型query语句产生了锁等待那么延时就产生了。
常见原因：Master负载过高、Slave负载过高、网络延迟、机器性能太低、MySQL配置不合理。
六、主从延时排查方法
通过监控 show slave status 命令输出的Seconds_Behind_Master参数的值来判断：
NULL，表示io_thread或是sql_thread有任何一个发生故障；
0，该值为零，表示主从复制良好；
正值，表示主从已经出现延时，数字越大表示从库延迟越严重。
七、解决方案
1. 半同步复制
从MySQL5.5开始，MySQL已经支持半同步复制了，半同步复制介于异步复制和同步复制之间，主库在执行完事务后不立刻返回结果给客户端，需要等待至少一个从库接收到并写到relay log中才返回结果给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一个TCP/IP往返耗时的延迟。
2. 主库配置sync_binlog=1，innodb_flush_log_at_trx_commit=1
sync_binlog的默认值是0，MySQL不会将binlog同步到磁盘，其值表示每写多少binlog同步一次磁盘。
innodb_flush_log_at_trx_commit为1表示每一次事务提交或事务外的指令都需要把日志flush到磁盘。
注意:将以上两个值同时设置为1时，写入性能会受到一定限制，只有对数据安全性要求很高的场景才建议使用，比如涉及到钱的订单支付业务，而且系统I/O能力必须可以支撑！
解决从库复制延迟的问题：
- 优化网络
- 升级Slave硬件配置
- Slave调整参数，关闭binlog，修改innodb_flush_log_at_trx_commit参数值
- 升级MySQL版本到5.7，使用并行复制