MySQL主从同步总结

2015.04.18发布于笔记暂无评论/目录

菜鸟DBA的MySQL主从同步总结,若无特别说明,以下内容均基于MySQL 5.5。

工作中要维护一批主-从架构的MySQL服务器,前段时间遇到了一个很奇怪的同步延迟问题,纠结了挺久,后来发现是配置方面的问题。这次干脆全面总结一下,以防日后纠结,如果有任何问题,请不吝指正。

准备工作

在使用MySQL同步之前,务必仔细阅读对应版本的MySQL参考手册的相关部分。

主从同步简介

MySQL同步的流程大致如下:

  1. 主服务器(master)将变更事件(更新、删除、表结构改变等等)写入二进制日志(master log)。
  2. 从服务器(slave)的IO线程从主服务器(binlog dump线程)获取二进制日志,并在本地保存一份自己的二进制日志(relay log)
  3. 从服务器的SQL线程读取本地日志(relay log),并重演变更事件。

二进制日志主要有三种格式: 基于SQL语句的同步(SBR),基于行的同步(RBR)和这两种的混合格式,MySQL 5.1.29之后默认使用基于语句的同步格式。

主服务器会为每个连接上来的从服务器创建单独的binlog dump线程来发送变更事件。

在MySQL 5.5及之前的版本上,从服务器使用一个IO线程和一个SQL线程来进行同步,从MySQL 5.6.3之后,可以通过配置slave-parallel-workers来启用多线程同步。需要说明的是,MySQL 5.6的多线程同步仅在有多个数据库时才有明显的性能提升,因为按照官方文档的说明,每个数据库同一时间只能有一个worker线程来进行工作。简单的说,如果只有一个数据库,那MySQL 5.6的多线程同步根本没用。不过,MySQL 5.7.2之后,可以通过配置slave-parallel-typeLOGICAL_CLOCK来避开这个限制。

关键配置

若无特别说明,以下配置均基于MySQL 5.5。配置修改后用sudo service mysql restart重启MySQL服务。

主服务器配置

主服务器的配置文档

[mysqld]
server-id=110
log-bin=mysql-bin
binlog_format=mixed
innodb_flush_log_at_trx_commit=1
sync_binlog=1 

需要说明的是,innodb_flush_log_at_trx_commit=1并不能完全保证数据不丢失,因为操作系统或云服务提供商可能会有额外的缓存策略。

从服务器配置

从服务器的配置文档

[mysqld]
server-id=120
# innodb_flush_log_at_trx_commit=2

主服务器的host,用户名和密码等可以直接写在配置里,也可以用CHANGE MASTER TO语句来设置。

innodb_flush_log_at_trx_commit配置为2可以极大的提高从服务器SQL线程同步的速度,但是如果MySQL进程崩溃可能会丢失1秒左右的数据。(日志文件大概每隔一秒fsync到磁盘上)

常用操作

从头搭建一个主从服务器的流程可参考官方手册或其他教程,以下只是选择性的总结一些常用的SQL语句。

设置同步源

在从服务器上设置主服务器的参数,确保已经正确配置主服务器和从服务器,并且已经在主服务器上创建了用于同步的用户,可参考官方手册

先暂停从服务器的同步:

mysql> STOP SLAVE;

根据实际情况修改对应的参数:

mysql> CHANGE MASTER TO 
    MASTER_HOST='xxx.xxx.xxx.xxx',
    MASTER_PORT='3306',
    MASTER_USER='replication user name',
    MASTER_PASSWORD='replication user password',
    MASTER_LOG_FILE='master binlog file',
    MASTER_LOG_POS='master binlog file position';

确认无误后启动同步:

mysql> START SLAVE;

MASTER_LOG_FILEMASTER_LOG_POS这两个参数的配置,如果是第一次同步,可参考检查主服务器状态; 如果是之前有同步过,可参考检查从服务器状态

如果CHANGE MASTER TO里没有设置RELAY_LOG_FILERELAY_LOG_POS,所有的relay log都会被删除,并从主服务器重新同步。

检查主服务器状态

开启一个MySQL会话

$ mysql -u root -p

关闭所有数据库的所有表并申请一个全局的读锁,防止写数据。

mysql> FLUSH TABLES WITH READ LOCK;

如果是第一次同步,不要关闭上面的MySQL会话(退出会话或关闭连接都会自动释放全局读锁),因为之前没有写二进制日志(没法同步),可以参考参考手册里的方法用mysqldump将数据库的老数据备份并同步到从服务器上。

下面查看master状态

mysql> SHOW MASTER STATUS\G

输出

            File: mysql-bin.000002
        Position: 107
    Binlog_Do_DB:
Binlog_Ignore_DB:

记住上面SHOW MASTER STATUS输出的File和Position,并在从服务器上用CHANGE MASTER TO配置主服务器即可。

最后,从服务器启动同步后,记得在之前运行FLUSH TABLES语句的MySQL会话里释放全局读锁

mysql> UNLOCK TABLES;

START TRANSACTION不会自动释放全局读锁。[1]

检查从服务器状态

开启一个MySQL会话,然后执行

mysql> SHOW SLAVE STATUS\G

输出

               Slave_IO_State: Waiting for master to send event
                  Master_Host: master1
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 931
               Relay_Log_File: slave1-relay-bin.000056
                Relay_Log_Pos: 950
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 931
              Relay_Log_Space: 1365
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids: 0

下面介绍几个比较关键的参数,全面的说明可阅读参考手册

  • Slave_IO_Running: IO线程是否正常运行
  • Slave_SQL_Running: SQL线程是否正常运行
  • Master_Log_File: IO线程正在读取的主服务器日志文件
  • Read_Master_Log_Pos: IO线程正在读取的主服务器日志文件的位置
  • Relay_Log_File: SQL线程正在读取和执行的本地日志文件
  • Relay_Log_Pos: SQL线程正在读取和执行的本地日志文件的位置
  • Relay_Master_Log_File: SQL线程正在重演的事件所在的主服务器日志文件
  • Exec_Master_Log_Pos: SQL线程正在重演的事件在主服务器日志文件中的位置
  • Seconds_Behind_Master: 如果网络没有明显的延迟,该参数标志着SQL线程的事件重演速度。如果该值较大且不断递增,调整innodb_flush_log_at_trx_commit会有比较明显的效果,但是可能会在MySQL进程崩溃时丢失数据。

如果需要切换从服务器的同步源,比如要切换到另一台主服务器,需要在SHOW SLAVE STATUS前暂停IO和SQL线程:

mysql> STOP SLAVE;

然后执行SHOW SLAVE STATUS并记录Relay_Master_Log_FileExec_Master_Log_Pos的值,在CHANGE MASTER TO中设置好同步坐标,最后启动同步:

mysql> START SLAVE;

常见问题

一般性的问题,建议阅读参考手册的Troubleshooting Replication部分。

同步兼容性

根据参考手册的说明,MySQL支持从低版本的服务器同步到下一个版本的服务器,但是如果一个集群中有多个主服务器,那么最多只能有两种不同的MySQL版本。

调查延迟

  • 查看MySQL日志,是否有报错。

  • 检查从服务器状态,对比主服务器的状态判断是IO线程还是SQL线程的问题,如果IO线程延迟,多半是网络问题。

      # on slave
      mysql> SHOW SLAVE STATUS\G
      # on master
      mysql> SHOW MASTER STATUS\G
    
  • 检查MySQL配置,可以尝试调整innodb_flush_log_at_trx_commit

  • 检查数据库的状态。

      mysql> SHOW ENGINE INNODB STATUS\G
    
  • 检查操作系统的状态。

      $ top
      $ free -m
      $ iostat
      $ vmstat
    
  • 检查MySQL进程的状态。

      $ strace -c -f -p
    

MySQL资源

参考资料


  1. MySQL 5.5 Reference Manual#13.7.6.3 FLUSH Syntax. 2015.04.18.

#database#mysql#总结

评论