MySQL复制原理与配置

一、Mysql复制基本原理

fzyl

  1. Mysql主库在事务提交时会将数据变更作为Events记录在Binlog中,Mysql主库的sync_binlog参数(默认值为0
    可参考http://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_sync_binlog)控制Binlog日志刷新到磁盘
  2. 主库推送Binlog中的事件到从库的Relay Log,之后从库根据Relay Log重做DML操作
  3. Mysql通过3个线程完成主从复制:Binlog Dump线程跑在主库,I/O线程和SQL线程跑在从库;
    当从库启动复制,首先创建I/O线程连接到主库,主库随后创建Binlog Dump线程读取数据库事件并发给I/O线程,I/O线程获取到事件数据后更新到从库的Relay Log中去,之后从库上的SQL线程读取Relay Log中更新的数据库事件并应用

注释:
从库上两个重要文件:master.info:记录I/O线程连接主库的一些参数;relay-log.info:记录SQL线程应用Relay Log的一些参数

二、Mysql复制中Binlog的三种格式
三种格式的介绍

Statement (statement-based replication:SBR):基于SQL语句级别的Binlog,每条修改数据的SQL都会保存在Binlog里面;
Row(RBR):基于行级别,记录每一行数据的变化,也就是将每行数据的变化都记录到Binlog里面,记录得非常详细,单并不记录原始SQL;在复制过程,并不会因为存储过程或者触发器造成主从数据不一致问题,但记录的binlog大小会比Statement格式大很多,CREATE、DROP、ALTER操作只记录原始SQL,而不会记录每行数据的变化到Binlog;
Mixed(MBR):混合Statement和Row模式,默认是Statement模式记录,某些情况下会切换到Row模式,例如SQL中包含与时间、用户相关的函数等statement无法完成主从复制的操作;

Binlog格式的优缺点

基于Statement复制(Mysql5.5默认格式):
优点:
Binlog日志量少,节约IO,和减少了主从网络binlog传输量
只记录在master上所执行的语句的细节,以及执行语句的上下文信息
同时,审计数据库变的更容易

缺点:
由于此格式是记录原始执行的SQL,保证能在slave上正确执行必须记录每条语句的上下文信息
部分修改数据库时使用的函数可能出现无法复制:sleep()、last_insert_id()、 load_file()、uuid()、user()、found_rows()、sysdate()(除非启动时—sysdate-is-now=true)
可能会导致触发器或者存储过程复制导致数据不一致,如调用NOW()函数
INSERT…SELECT 可能会产生比RBR更多的行级锁,例如没有order by的insert…select
复制需要执行全表扫描(WHERE中没有使用索引)的UPDATE时,需比row请求更多的行级锁
对于AUTO_INCREMENT字段的InnoDB引擎表,INSERT会阻塞其他INSERT语句

注:如果statement不能保证主从正常复制,error日志会有提示:Statement may not be safe to log in statement format

基于Row复制:
优点:
只记录每一行数据变化的细节,不需要记录上下文信息
不会出现某些情况下auto_increment columns,timestamps,.triger、function、procedure无法正常复制的问题
新的row格式已经有了优化, CREATE、DROP、ALTER操作只记录原始SQL,而不会记录每行数据的变化到Binlog
适用于主从复制要求强一致性的环境

缺点:
update、delete、load data local infile等频繁更新或者删除大量行时会产生大量的binlog日志,会有一定的I/O压力,主从同步产生不必要的流量
如:UPDATE products set status=‘sold’ where product_id BETWEEN 30000 and 50000;
无法很好的进行数据库审计

Binlog基本配置

修改配置文件my.cnf

三、Mysql常见两种复制方式

 异步复制(Asynchronous Replication)

ybfz

  主库执行完Commit后,在主库写入Binlog日志后即可成功返回客户端,无需等等Binlog日志传送给从库

异步复制主从配置:

主 : 192.168.10.216
从 : 192.168.10.217

步骤:主从版本一致—>主库授权复制帐号—>确保开启binlog及主从server_id唯一—>主库只读,记录主binlog名称及偏移量—>拷贝主数据文件到从相应位置—>从库change master to —>slave start—>检查两个yes

1.主MySQL配置

2.从MySQL配置

注: 异步复制中只要binlog不丢失即可保证数据的完整性;当主宕机,从库未收到binlog时,就会丢失数据(主磁盘正常时可以提取差异binlog在从执行),此时就需要用到半同步复制方式

半同步复制(Semi-synchroous Replicaion)

btb

  主库每次事务成功提交时并不及时反馈给前端,而是等待其中一个从库也接收到Binlog事务并成功写入Relay Log之后,才返回Commit操作成功给客户端;如此半同步就保证了事务成功提交后,至少有两份日志记录,一份在主库Binlog上,另一份在从库的Relay Log上,从而进一步保证数据完整性;半同步复制很大程度取决于主从网络RTT(往返时延);以插件形式存在,

半同步复制主从配置:

主 : 192.168.10.216
从 : 192.168.10.217

1.判断是否支持动态增加插件

2.检查是否存在半同步插件,分别在主从安装
/usr/local/mysql/lib/mysql/plugin/semisync_master.so
/usr/local/mysql/lib/mysql/plugin/semisync_slave.so

主MySQL上安装semisync_master.so:
mysql>install plugin rpl_semi_sync_master SONAME ‘semisync_master.so’

从MySQL上安装semisync_slave.so:
mysql>install plugin rpl_semi_sync_slave SONAME ‘semisync_slave.so’

安装后通过show plugins;查看安装的插件

3.分别在主从打开semi-sync(默认关闭)
主:
修改my.cnf

从:
修改my.cnf

4.验证
主:

semi

从:

semion

四、提升主从复制性能的方法

方案1:多级主从架构,将不同库分开复制到不同从上

fzjg

注意事项:
M2上打开log-slave-updates配置,保证M1传送的binlog能够被记录在M2的RelayLog和Binlog;M2可以选择BLACKHOLE引擎降低M2的I/O;并且Binlog日志的过滤可以在M2去做
BLACKHOLE引擎的使用测试参考:   http://jroller.com/dschneller/entry/mysql_replication_using_blackhole_engine
http://blog.csdn.net/kylinbl/article/details/8903336

方案2:多线程复制(MySQL5.6+)
多线程复制是基于库的,允许从库并行更新,若单库压力大,此处的多线程复制没有意义;从库设置slave_parallel_workers=4表示MySQL从库在复制时启动4个SQL线程
MySQL5.6一下版本可以尝试Transfer补丁http://dinglin.iteye.com/blog/1888640

五、Mysql复制遇到的一些问题

1.指定特定的数据库或者表

2.从库复制出错跳过

3.log event entry exceeded max_allowed_packet的处理

  适当增加max_allowed_packet大小

4.因主库大量滞后binlog,启动slave时,可能会跑满网卡带宽

  前段时间微博上@zolker遇到这类问题,网友也给了很多解决办法,趁此blog总结下
A.级联备库方式,避免主MySQL网卡跑满影响
B.脚本的方式每隔几秒(sleep)把io_thread停一会,进行缓解 (这种方法简单、粗暴、有效,但有抖动)
C.使用facebook的patch https://github.com/facebook/mysql-5.6/commit/d3b0c7814090bded6563fee7d46d2ae41ed32a60

  以上是本人在学习过程中的笔记,一码一字敲出来的,有错误地方请留言~

参考文档:

http://www.ovaistariq.net/528/statement-based-vs-row-based-replication/
http://www.orczhou.com/index.php/2011/06/mysql-5-5-semi-sync-replication-setup-config/
http://www.linuxde.net/2013/09/15194.html
http://dev.mysql.com/doc/refman/5.6/en/replication.html
《深入浅出MySQL》

发表评论

电子邮件地址不会被公开。 必填项已用*标注