首页 > 未分类 > MySQL主从同步总结

MySQL主从同步总结

2016年12月16日

MySQL主从同步总结
https://www.mawenbao.com/note/mysql-replication-summary.html
菜鸟DBA的MySQL主从同步总结,若无特别说明,以下内容均基于MySQL 5.5。

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

准备工作

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

主从同步简介

MySQL同步的流程大致如下:

主服务器(master)将变更事件(更新、删除、表结构改变等等)写入二进制日志(master log)。
从服务器(slave)的IO线程从主服务器(binlog dump线程)获取二进制日志,并在本地保存一份自己的二进制日志(relay log)
从服务器的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-type为LOGICAL_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_FILE和MASTER_LOG_POS这两个参数的配置,如果是第一次同步,可参考检查主服务器状态; 如果是之前有同步过,可参考检查从服务器状态。

如果CHANGE MASTER TO里没有设置RELAY_LOG_FILE或RELAY_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_File和Exec_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

分类: 未分类 标签:
本文的评论功能被关闭了.