Mysql主从同步的几个要点总结

同步原理

主从同步是Mysql非常常见的一个应用,也是非常重要的监控之处,这里简单总结在配置Mysql时候的几个要点,防止以后自己踩坑。

先说一下主从同步的原理,就是主数据库在数据库更新的时候会更新自己的binlog,同时也会向读数据库(一个或多个)传递这个binlog,此时从库开始一个io_thread这个线程用来接收这个binlog,然后把binlog写入到自己的relaylog,当relaylog发现有数据更新了,就开始一个sql_thread来按照主库更新自己的库,这样达到了“主库读库一致”的效果。图示如下:
paradin

上述过程:
主从延迟:「步骤2」开始,到「步骤7」执行结束。
步骤 2:存储引擎处理,时间极短
步骤 3:文件更新通知,磁盘读取延迟
步骤 4:Bin Log 文件更新的传输延迟,单线程
步骤 5:磁盘写入延迟
步骤 6:文件更新通知,磁盘读取延迟
步骤 7:SQL 执行时长

要监控主从同步是否出现异常,可以通过show slave status\G里的Seconds_Behind_Master字段来查看,如图:
paradin

但是要注意!Seconds_Behind_Master是有前提的,那就是主库跟读库之间的网络情况要良好,因为这个字段是从属服务器SQL线程和从属服务器I/O线程之间的时间差距,(即比较binlogrelaylog执行sql的timestamp时间差),单位是秒。如果主服务器和从属服务器之间的网络连接较快,则从属服务器I/O线程会非常接近主服务器,所以本字段能够十分近似地指示,从属服务器SQL线程比主服务器落后多少。如果网络较慢,则这种指示不准确;从属SQL线程经常会赶上读取速度较慢地从属服务器I/O线程,因此,Seconds_Behind_Master经常显示值为0。即使I/O线程落后于主服务器时,也是如此。换句话说,本列只对速度快的网络有用。

要点总结

  1. 主库和读库的mysql版本保持一致,硬件情况也保持一致;
  2. binlog文件在生产系统中不易过大,建议小于500m,不然容易拖慢数据库性能;
  3. 设置slave前先检查一下设置的账号能不能远程登陆;
  4. 在设置多个库同步时,一个binlog-do-db参数对应一个库,不能一行写多个库;
  5. 如果出现了Slave_IO_Running: No这个状态,去主库上show master status\G,查看一下是否file跟从库的file是不是对不上;
  6. 代码里避免出现“查询读库后马上到主库操作”的字段,由于主从同步有延迟,这样很有可能会出现前端多次请求,而从库一致无法从主库得到最新的数据消息,所以sql被执行了好几次的错误,这样的情况可以考虑加入“可以用唯一索引限制”或者用insert … select … where not exist这种方式;
  7. 主库的慢sql太多的话,也会影响主从同步;

参考资料

https://dba.stackexchange.com/questions/24793/mysql-replication-slave-is-continuously-lagging-behind-master
http://ningg.top/inside-mysql-master-slave-delay/
http://database.51cto.com/art/201108/287653.htm
https://zhuanlan.zhihu.com/p/28554242

paradin

感谢您请我喝咖啡~O(∩_∩)O,如果要联系请直接发我邮箱chenx1242@163.com,我会回复你的
-------------本文结束感谢您的阅读-------------