文档章节

MySQL主从同步总结

五大三粗
 五大三粗
发布于 2015/05/09 13:36
字数 1866
阅读 122
收藏 0

工作中要维护一批主-从架构的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-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

MySQL资源

参考资料


© 著作权归作者所有

共有 人打赏支持
五大三粗
粉丝 160
博文 2264
码字总数 4712446
作品 0
广州
程序员
私信 提问
Linux学习总结(五十一)mysql 主从配置

一 mysql 主从介绍 mysql 主从又叫replication ,AB复制。简单讲就是A和B两台机器做主从后,在A上写数据,另外一台B也会跟着写数据,两者数据实时同步的。 MySQL主从是基于binlog的,主上须开...

jiaoshou0416
05/21
0
0
Mysql配置Replication主从复制-实现读写分离

概述:Mysql集群的方式可以是很多的,主从,一主多从,多组多从,主从的策略还可以进一步选择和配置。可以说是很灵活了。本文介绍的Replication是异步复制同步方案,分别有基于日志的还有基于...

jorgezhong
09/10
0
0
MySQL 同步和主从设置

关于 MySQL 主从复制的配置,网络上可以搜出成筐的文章来,但下面这篇写得很清晰、简洁,值得推荐: 以下文章内容转自阿权的书房。 设置Mysql的主从设置很重要,有如下几点用处: 1 做备份机...

红薯
2010/08/11
3.2K
2
基于Docker的Mysql主从复制搭建

为什么基于Docker搭建? 资源有限 虚拟机搭建对机器配置有要求,并且安装mysql步骤繁琐 一台机器上可以运行多个Docker容器 Docker容器之间相互独立,有独立ip,互不冲突 Docker使用步骤简便,...

撸码那些事
07/26
0
0
重置mysql主从同步:reset master和reset slave

MySQL主从同步的过程中,可能会因为各种原因出现主库与从库不同步的情况,网上虽然有一些解决办法,但是有时很难彻底解决,重置主从服务器也许不是最快的办法,但却是最安全有效的。 下面将自...

首席提问官
08/22
0
0

没有更多内容

加载失败,请刷新页面

加载更多

CentOS配置Tomcat监听80端口,虚拟主机

Tomcat更改默认端口为80 更改的配置文件是: /usr/local/tomcat/conf/server.xml [root@test-a ~]# vim /usr/local/tomcat/conf/server.xml # 找到 Connector port="8080" protocol="HTTP/1......

野雪球
22分钟前
2
0
《稻盛和夫经营学》读后感心得体会3180字范文

《稻盛和夫经营学》读后感心得体会3180字范文: 一代日本经营之圣稻盛和夫凭借刻苦勤奋的精神以及深植于佛教的商业道德准则,成为了“佛系”企业家的代表人物。在《稻盛和夫经营学》“领导人...

原创小博客
今天
1
0
java框架学习日志-5(常见的依赖注入)

依赖注入(dependency injection) 之前提到控制反转(Inversion of Control)也叫依赖注入,它们其实是一个东西,只是看的角度不同,这章详细说一下依赖注入。 依赖——指bean对象创建依赖于...

白话
今天
2
0
红外接收器驱动开发

背景:使用系统的红外遥控软件没有反应,然后以为自己接线错误,反复测试,结果烧坏了一个红外接收器,信号主板没有问题。所以自己开发了一个红外接收器的python驱动。接线参见https://my.os...

mbzhong
今天
2
0
ActiveMQ消息传送机制以及ACK机制详解

AcitveMQ是作为一种消息存储和分发组件,涉及到client与broker端数据交互的方方面面,它不仅要担保消息的存储安全性,还要提供额外的手段来确保消息的分发是可靠的。 一. ActiveMQ消息传送机...

watermelon11
今天
2
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部