Xsank's Blog

mysql主从同步分析

工作中由于临时有个数据库数据同步的需求,上海+深圳同步,更多的是保证数据一致,tps和qps都很低。于是简单配置一个主从同步是一个稳妥的方案

配置步骤

  1. 修改my.cnf
    • 一定要开启binlog
    • 注意server-id不重
  2. master配置同步账号
  3. master调用show master status \G命令,记录binlog及position
  4. slave设置读取master的binlog及position,start slave,通过调用show slave status \G,观察配置结果

原理分析

  1. 由于mysql数据同步的原理是通过获取binlog记录的所有sql操作并同步执行而实现的,所以作为master的服务器必须开启binlog,另外由于区分主次,server-id必须不同
  2. slave服务器必须有可以连接master数据库的账号
  3. slave服务器在开始同步时,需要知道当前从master的哪个binlogposition开始执行同步

这里有一点小迷惑,暂时还没有时间去深究,先记录下来:
网上资料都说master-slave的数据同步是通过masterpush过去的,这里我感觉有点奇怪:

  1. 如果使用push模式,那么master肯定知道slave的进度,那么master可以直接推给slave
  2. 因为是pull模式,slave才必须配置binlog+position
    先留下这个坑,以后填。。。

实践

错误的binlog+position

slave状态如下:

1
2
3
4
Slave_IO_Running: No
...
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

很明是slave找不到,不知道应该怎么读了,重新配置正确的binlog+position,错误消失,io thread恢复
从这里就明显感觉这个数据同步应该是pull模式。

冲突的sql

Slave_SQL_Running: No
...
Last_Errno: 1062
Last_Error: Error 'Duplicate entry '9527' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'INSERT INTO `test` (`data`, `create_time`) VALUES ('/test?show&o=N4IglgJiBcB2CuAbRAaEAXAhgcwM4wG0BdNXdAT0QFMYQJMAnAaxDXTAFsqAvAe1hrQQAIwa8A7rioNWIKhDBZh1GADNMiKWg6YmVAKIKl1ACq9eidgAdaAWV1UABIcWZlNbQ5fGqAYUSYuPhC6gC0iLwAxixoABaQfvzoYppqGloguLGM8r5iQdlgMtDqmlSkmABuVABC5HlUmOi8xaUZYLgNTS0wyfDlIGKShKCxVGDYsei0AEwArAAMVgAeslaYAqnQBKDs6CpCAAobVIiOJooqaNJirekDuOuwMACMM9dGbgd9AxRWgiBsAxMFZYrIOgA5KjiXoMfpoSCvNiMbBUdDBHYgHBAqjYbrFTLwDiyIEbJCMRTkWiYeDNWQQCSwXCYDhWagAQWwOLxzQJVWw9MZzNZ1AAYmBkIcLGBIlShLB+B5BlRVABJKBCdmyLjJGW0BnRaQAOio1Vg6CN2QAzAB9SLwc2YXg2hY2si2gDs8CN5mJAF8SHQmoFePAGJFBAhkGgGFRYBBpNJaKoItM0ORMMsqBjQAFhKcYFHUJlYhJYfCQBFsDVAoIXtowM84EhizpVs3oyBVC0dNMhFkWtM-Shc24Cx3iwOYdAfmgqzWpEiQBxG4WW5520W0N2GL3aAOGEPA8tMx0YKAp+WqMPAQxEdBdrFYwPEBAXmvO+gn9nS6+Zh-iy-Z9fzfXwLB6IQGGwYRMAAChmF4ADYUEcGYFgWFCZg9FCFiNLCAEpZCAn8LAgGYwIiAkoJg+CrQAFhQl43kY5jHFwmYZkIm9EEbbMr23CVEBgDDK148RIC-JcrF4Rt0TSMo0Gk2TgQUeBgjm

这里通常就是数据库数据不一致的问题了,slave中的primary key和binlog同步过来的sql发生冲突,这会导致sql thread挂掉,不会再继续同步数据。
由于mysql并不会自动处理此类情况,所以你需要重新配置slave才能恢复。