文档章节

MySQL主从同步总结

五大三粗
 五大三粗
发布于 2015/05/09 13:36
字数 1866
阅读 108
收藏 0
点赞 0
评论 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资源

参考资料


© 著作权归作者所有

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

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

jiaoshou0416 ⋅ 05/21 ⋅ 0

MySQL 同步和主从设置

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

红薯 ⋅ 2010/08/11 ⋅ 2

mysql 主从配置

1. MySQL主从原理以及应用场景MySQL的Replication原理非常简单,总结一下: 每个从仅可以设置一个主。 主在执行sql之后,记录二进制log文件(bin-log)。 从连接主,并从主获取binlog,存于本...

zhui_yi_520 ⋅ 2015/08/31 ⋅ 0

详解MySQL数据库设置主从同步的方法

简介 MySQL主从同步是目前使用比较广泛的数据库架构,技术比较成熟,配置也不复杂,特别是对于负载比较大的网站,主从同步能够有效缓解数据库读写的压力。 MySQL主从同步的机制: MySQL同步的...

xiaocao13140 ⋅ 05/23 ⋅ 0

生产环境配置mysql主从复制

配置mysql主从复制 环境:redhat 7.3 系统 两台主机 一台master 一台slave 1、主从复制的步骤 1、搭建时间NTP服务器,同步时间 1)建立时间同步环境,在主节点上搭建时间同步服务器 时间同步...

暖暖miss ⋅ 04/18 ⋅ 0

linux Mysql 主从复制 原理介绍和步骤详解

mysql主从复制 灵活 用途及条件 mysql主从复制用途 主从部署必要条件: 主从原理 mysql主从复制原理 从库生成两个线程,一个I/O线程,一个SQL线程; i/o线程去请求主库 的binlog,并将得到的...

霸王卸甲 ⋅ 03/22 ⋅ 0

企业中MySQL高可用集群架构三部曲之MM+keepalived

各位老铁们,老张与大家又见面了。看到各位在博客里面给我的留言和访问量的情况,我很是欣慰,也谢谢大家对我的认可。我写这些博客,就是想把自己对于MySQL数据库的一些看法和自己平时的实战...

superZS ⋅ 2017/08/03 ⋅ 0

mysql 主从复制存在问题及解决方法

主从形式 mysql主从复制 灵活 一主一从 主主复制 一主多从---扩展系统读取的性能,因为读是在从库读取的; 多主一从---5.7开始支持 联级复制--- 用途及条件 mysql主从复制用途 实时灾备,用于...

浅景尘 ⋅ 2017/07/29 ⋅ 0

MySQL阶段五——主从复制原理、主从延迟原理与解决

MySQL主从复制原理、主从延迟原理与解决 MySQL主从复制画图描述: MySQL主从复制原理上图详解: ① 用户做crud操作,写入数据库,更新结果记录到binlog中; ② 主从同步是主找从的,从库I...

花开半夏qb ⋅ 2017/05/24 ⋅ 0

MySQL传统主从复制(第二弹)

0、引言 GTID是MySQL 5.6 的新特性之一,全称global transaction identifier全局事务标志符。GTID官方定义是: GTID = sourceid:transactionid sourceid:源服务器标识(一般是serveruuid),...

IT--小哥 ⋅ 2016/12/14 ⋅ 2

没有更多内容

加载失败,请刷新页面

加载更多

下一页

关于“幂等”操作

一个幂等(idempotent)操作的特点是其任意多次执行所产生的影响均与一次执行的影响相同. 开发中, 我们经常考虑幂等操作的场景有“接口调用”、“MQ消费”、“自动任务”等 接口调用, 可能出现...

零二一七 ⋅ 6分钟前 ⋅ 0

Dubbo服务服务暴露之ProxyFactory Invoker

Dubbo服务暴露过程中有涉及到调用ProxyFactory 中方法获取Invoker对象的过程,现在我们来深究下源码,来看下这个过程是在做些什么,返回的Invoker 对象是什么,我们来看一下代码的切入点: ...

哲别0 ⋅ 21分钟前 ⋅ 0

GP两种连接方式性能测试

GP两种连接方式性能测试 Pivotal import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; public class GPQueryStrin......

仔仔1993 ⋅ 25分钟前 ⋅ 0

jsonrpc-4j代码解析

解析文件 AutoJsonRpcServiceImplExporter JsonServiceExporter AutoJsonRpcServiceImplExporter 路径:com.googlecode.jsonrpc4j.spring.AutoJsonRpcServiceImplExporter AutoJsonRpcServi......

郭恩洲_OSC博客 ⋅ 47分钟前 ⋅ 0

百度搜索

from selenium import webdriver import time brower=webdriver.Firefox() brower.get('http://www.baidu.com') input=brower.find_element_by_id('kw') input.send_keys('中南大学') time.s......

南桥北木 ⋅ 53分钟前 ⋅ 0

tomcat 日志记录器

1、日志记录器是记录消息的组件 日志记录器需要与某个servlet 容器相关联 2、Logger 接口 共定义了5种日志级别:FATAL、ERROR、WARNING、INFORMATION、DEBUGGER setVerbosity 设置级别 setC...

职业搬砖20年 ⋅ 55分钟前 ⋅ 0

Thrift RPC实战(三) Thrift序列化机制

1.Thrift基础架构 Thrift是一个客户端和服务端的架构体系,数据通过socket传输; 具有自己内部定义的传输协议规范(TProtocol)和传输数据标准(TTransports); 通过IDL脚本对传输数据的数据结构...

lemonLove ⋅ 55分钟前 ⋅ 0

网站建设就要像2018世界杯的俄罗斯队大杀四方[图]

今天心情不错,因为昨天晚上观看了世界杯比赛,尤其是对俄罗斯队的大杀四方感到十分霸气侧漏啊,因此我联想到了自己的博客网站,我的博客是去年年底上线的,一直想建设一个关于读书和读后感作...

原创小博客 ⋅ 今天 ⋅ 0

linux 信号机制

signal(SIGPIPE, SIG_IGN); TCP是全双工的信道, 可以看作两条单工信道, TCP连接两端的两个端点各负责一条. 当对端调用close时, 虽然本意是关闭整个两条信道, 但本端只是收到FIN包. 按照TCP协...

xxdd ⋅ 今天 ⋅ 0

my.cnf, my-small.cnf, my-medium.cnf, my-large.cnf

1. my-small.cnf # Example MySQL config file for small systems.## This is for a system with little memory (<= 64M) where MySQL is only used# from time to time and it's importa......

周云台 ⋅ 今天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部