MySQL复制原理和配置（5.6.20）

grerww

3.1  MySQL复制

      为了保证应用24小时提供服务，那么我们需要做2件事情，系统数据的备份和冗余。备份可以将数据恢复到崩溃之前的状态，而冗余则是保证在一个或多个节点停止服务的情况下，应用仍能提供服务。

3.1.1 什么是复制

      这里说的复制，就是指MySQL复制主节点（master）的所有数据改变到从节点（slave），并应用改变的数据，与主节点数据保存一致。通常复制就是为了给master创建一个可靠的副本。

      MySQL复制，有什么特点呢？

         (1)   数据分布 (Data distribution )，数据可以分布在不同的数据中心。

         (2)   负载平衡(loadbalancing)，可以分配查询，降低主服务器压力。

         (3)   备份(Backups) ，复制本身就是一种master的备份。

         (4)   高可用性和容错行 Highavailability and failover，当master宕机后，slave可以接管master的任务，同时具有及时纠正错误，挽回数据的容错率。

      从MySQL5.0开始，MySQL默认采用日志复制，效率非常高，可靠性也非常高，前提是主从，一开始是完全相同的数据。当无法保证精准复制时，MySQL会基于行的复制。

3.1.2 异步与同步的区别

MySQL的复制准确的说，是异步的，不是同步复制的。事务首先在主节点提交，然后复制给从节点，并在从节点上应用。这意味着主节点和从节点可能并不一致，如果主节点一直在更新，那么从节点始终是落后与主节点的。

异步复制优点在于，比同步复制更快，扩展性更好，占用系统资源更少。

同步复制则需要master等待所有的slave的写都完成后，才能提交，导致写的速度大大降低，同时需要额外的同步机制来保证一致性，但却有额外的通信消息传递，增加了网络数据量。

3.2 主从复制

搭建一个主从复制，大致看看是一个什么样的效果，一主一从，最简单的开始。

节点	IP	角色	服务ID
Vm11.qq.com	192.168.1.231	master	1
Vm22.qq.com	192.168.1.232	slave	2

这里需要涉及到二进制日志，后面来详细说明，本次主要说明复制过程。

3.2.1 配置复制

第一步：配置master。首先需要开启二进制日志，配置好server-id，然后启动MySQL，如下所示几行添加到/etc/my.cnf文件中：

log-bin=master-bin                           二进制日志文件名称格式。

log-bin-index=master-bin.index         二进制日志的索引文件名

server-id=1                                     server id用于标示主从。

一般情况下log-bin默认是hostname-bin。Hostname的值来自pid-file选项，但是这样也会有问题，主机名修改之后，binlog日志文件名也会随之改变，索引文件还是可以读取到正确的值。

当不给log-bin-index值的时候，默认与binlog文件基本名相同，索引文件随主机名改变而改变，当你重启了服务器之后，索引文件就找不到日志文件了，认为不存在了。

接下来，需要创建一个复制用的用户。例如：      

      第二步：配置slave。跟master一样，也需要配置/etc/my.cnf

      relay-log=slave-relay-bin

  relay-log-index=slave-relay-bin.index

  server-id=2

      这里配置的是中继日志，为什么需要配置中继日志，后面根据原理图来说明。Master和Slave修改配置文件后，都是需要重启的。

      第三步：连接master。

mysql> change master to

    -> master_host='vm11.qq.com',

    -> master_port=3306,

    -> master_user='repl_user',

    -> master_password='mysql';

Query OK, 0 rows affected, 2warnings (0.04 sec)

注意：最好在同步之前，主从两边的数据库是一样的，因为master在没有开启二进制日志之前的所有操作都不会同步过去到slave的。

      我们这里在master没有开启二进制日志之前，先建立了一个库test，slave肯定同步不过去，这时候master删除这个库。然后再次新建test的时候，出现了问题。

      这是master的，看看slave的。

    这里没有同步过去，看看什么问题。

       错误：1008，test库不存在，drop database test语句执行不了，所以slave_sql_running状态变成了NO。生产环境很容易忽略，出现这样的问题。

      

CHANGEMASTER TO MASTER_HOST='vm11.qq.com',

MASTER_USER='repl_user',

      MASTER_PASSWORD='mysql',

      MASTER_LOG_FILE='master-bin.000001',

      MASTER_LOG_POS=756;

Start slave；从新的位置从新同步，前面的忽略。

3.2.2 二进制日志

       什么是二进制日志，就是记录表改变的记录，以上例来看。

        来看看日志都记录了什么；

对比一下，我们以下的操作，那些记录了，那些没有记录。

      mysql> create database test;                 记录

mysql>use test;                                    记录

mysql>create table tab(text text);         记录

mysql>insert into tab values("hello!"); 记录，自动添加use test命令。

mysql>select * from tab;    没有，日志记录了一次commit；

mysql> flush logs;                                   记录

最后一条记录：Info:master-bin.000002;pos=4是slave下一次读取二进制日志的位置，因为我们执行了flush logs；强制切换日志。

当我们想看第二个日志文件的内容时，默认是无法看到的，可以这样：

mysql> show binlog eventsin 'master-bin.000002' \G;

3.2.3 复制工作原理

整体上来说，复制有3个步骤：   

       (1)   master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary logevents）；

       (2)   slave将master的binary log events拷贝到它的中继日志(relay log)；

       (3)   slave重做中继日志中的事件，将改变反映它自己的数据。

1.基于语句复制

也称为逻辑复制，需要保证主从基础数据一样，然后把主库上记录的更改在从库上再执行一遍，实现简单，同步效果较好，网络传输少，但问题就是，存在着一些无法被正确复制的SQL，像时间戳，current_user()这样的函数语句，触发器，存储过程等也容易出现问题。另外一个问题时更新必须串行的，所以会需要更多的锁。

优点：适应场景广泛，可以非常灵活，都是SQL执行方式，当出现问题可以很好的去定位。

缺点：有些语句存在无法正确复制问题，特别是存储过程和触发器。

2.基于行的复制

       就是直接复制改变的行数据，在从库中也做update操作，也种实现方式比较复杂，因为复制的数据可能不正确，从库更新量大，主库一个update操作更新2000条数据，但在从库可能需要update2000次才能将数据与主库保持一致，优点是直接复制数据，会比较高效，不用去分析日志，但是缺点也非常明显。网络传输量较大，

       优点：这种方式什么情况都可以使用，存储过程，触发器等都没有问题，但是备库与主库的字段有不一致的情况，那么复制就会失败。

       缺点：无法获知执行了什么操作，只能看到变化了的值，当问题出现后，很难去判断故障的原因，或是什么导致的，而且很多情况下，备库的开销非常大，而且网络传输也更高。

3.2.4 推荐复制配置

       此小结主要用来分析复制的配置，有些配置是没有必要的，有些也是需要分适用场景的，主要还是以安全为第一，最小化可能出现的问题为前提，然后是性能上的建议。

       主库上最重要的一个参数：sync_binlog=1

开启，MySQL每次提交事务前都会将二进制日志同步到磁盘上，能够保证崩溃时不丢失事务，关闭则无需再提交之前写到磁盘，对于中继日志来说，不适用该参数，所以建议主库上开启，从库上关闭。

如果使用InnoDB，那么建议配置以下设置：

innodb_flush_logs_at_trx_commit：设为1当然是最安全的，但性能页是最差的（相对其他两个参数而言，但不是不能接受）。如果对数据一致性和完整性要求不高，完全可以设为2,如果只最求性能，例如高并发写的日志服务器，设为0来获得更高性能。

Innodb_support_xa=1  保证binlog里面存在的事务一定在redo log里面存在，同时binlog里面事务顺序与redo log事务顺序一致。

      Innodb_safe_binlog  表示在5.0版本之前，跟Innodb_support_xa参数意思一样。

       log-bin=/data/binlog/master-bin   一定要配置文件名，不然在其它应用的时候，容易出现问题，同时制定了它的路径。

在备库上推荐配置：

Relay_log=/data/relaylog/relay-bin     制定路径避免不同版本之间的bug问题，

Skip_slave_start    表示备库在崩溃后不自动复制，因为可能已经不一致了，自动复制，可能导致更多的损坏，所以一般都需要禁用掉。

Read_only  可以阻止大部分用户更改非临时表，除了SQL线程和超级权限用户外。

3.2.5 复制延迟

       复制延迟，在大部分的同步系统中，都存在这样的问题，目前较为成熟的亚秒级实时同步的解决方案是GoldenGate，当然大部分系统是不需要这么高的要求，所以只要延迟在我们接受的范围内都是科学的解决方案。

       复制延迟一般存在2种：一种是产生延迟然后再跟上，一种是稳定的延迟增大，前一种基本都是由于运行一条运行很长时间的查询导致，而后者，可能存在非常严重的问题导致。通常没有太大的调优空间，简单的办法是配置InnoDB，可以不那么频繁的刷新磁盘，事务提交更快。可以设置innodb_flush_log_at_trx_commit值为2来做，还可以禁止备库上的二进制日志记录，配置innodb_locks_unsafe_for_binlog为1，并配置MyISAM的delay_key_write为all，但这些都是牺牲安全为代价提升效率，还有一点，是这样配置备库后，那么这个永远就只能是备库，不能切换为主库了，如果需要切换，需要将配置改回来。

优化主库高代价操作

       在主库上任何昂贵的操作，都会在备库上重复一次，某些工作可以移动到备库，就不会让高代价的操作在主从上执行2次。例如：一个大表，需要汇总到一个小表用于日常维护：

Replayce into s_db.ip_table(col1,col2,…)

Select col1,sum(col2,….) from  s_db.epc_table group by col1;

如果在主库上执行，那么备库也需要执行庞大的group by操作，当进行的太多的话，那么备库就容易跟不上了，如果在备库上操作，完成之后可以select into outfile，然后再load data infile将结果集加载到主库中，这样重负的工作变成了load data infile操作了。

备库并行写入

       由于备库要求串行写入，某些应用更新就很吃力，那么我们能不能并行写入呢，当然复制本身是不具备的，这需要区分写入的类别了，例如禁止归档日志记录到二进制日志，然后再主库和备库上分别执行，提高效率，这就是OLTP系统的归档数据操作，往往这样来做比让数据库自己靠复制完成要高效的多。

还需要注意一个问题，主库和备库的max_allowed_packet配置不一致，主库可能会记录一个备库认为过大的包，备库获取到该二进制事件后，可能会无限报错和重试，或中继日志损坏。

3.2.6 半同步和复制心跳

       半同步复制在提交过程中增加了一个延迟，当提交事务时，客户端接收到查询结束反馈前必须保证二进制日志已经传输到至少一台备库上。主库将事务提交到磁盘上之后会增加一个延迟。那么速度上延迟增加，但同步复制也会增加2段提交，增加的额外消息交换而性能不高。

       也就是说主库执行完事务后，是不会管备库进度的，这样提高了主库的效率，而备库上的事务至少有一个是跟主库一致的，所以某种程度上讲，实现了事务同步，不保证备库已经执行完成，在性能上主库提升较大。

       复制心跳，就是主库和备库相互联系功能，避免不声不响的断开连接。当出现断开连接，备库会注意到丢失的心跳数据，当再次恢复的时候，以此为依据，再次同步数据。