MySQL主从复制-基于binlog

最新推荐文章于 2024-02-17 23:05:56 发布
最新推荐文章于 2024-02-17 23:05:56 发布
阅读量655 收藏 1
点赞数
分类专栏： 数据库 文章标签： mysql 主从复制 集群
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/duke147/article/details/121482642
版权

文章目录

一、概述

MySQL进行实时数据同步，不会回放已经执行的导入数据库脚本。所以同步开始前，两端的数据要保持一致。

  • 数据同步：

    • 主从复制（主库挂掉，从库不会自动变为master，应用场景：读写分离）
    • 主主复制（并发高容易主键冲突）

  • 实现原理：

    • 基于Binlog（二进制日志文件位置）本文基于binlog
    • 基于GTID（全局事务标示符）
软件版本

mysql5.7.20

二、配置主从

1.修改配置文件

Linux : /etc/my.cnf

windows : mysql.ini

  • 主库配置

    [mysqld]
# 数据库ID号， 为1时表示为Master,其中master_id必须为1到232–1之间的一个正整数值，主从server-id不能一样;[必须]服务器唯一ID，默认是1，一般取IP最后一段（主从都设置成各自IP最后一段）
server-id=1
# 主库启用二进制日志，从库非必须； 
log-bin=mysql-bin 
# 表示每次事务提交，MySQL都会把binlog刷到磁盘上，是最安全但是性能损耗最大的设置。
sync-binlog=1
auto_increment_increment=1   #步长 auto_imcrement。一般有n台主MySQL就填n
auto_increment_offset=1   #起始值。一般填第n台主MySQL。此时为第一台主MySQL

# 需要同步数据库名，同步多个数据库，复制即可 （主库设置，从库不用设置）是指定binlog日志记录那些库的二进制日志。
binlog-do-db=wr

# binlog_format           = MIXED                         //binlog日志格式，mysql默认采用statement，建议使用mixed
# log-bin                 = /data/mysql/mysql-bin.log    //binlog日志文件
# expire_logs_days        = 7                           //binlog过期清理时间
# max_binlog_size         = 100m                       //binlog每个日志文件大小
# binlog_cache_size       = 4m                        //binlog缓存大小
# max_binlog_cache_size   = 512m                     //最大binlog缓存大小

    重启MySQL

    mysql restart

  • 从库配置

    [mysqld]
# 数据库ID号， 为1时表示为Master,其中master_id必须为1到232–1之间的一个正整数值，主从server-id不能一样;[必须]服务器唯一ID，默认是1，一般取IP最后一段（主从都设置成各自IP最后一段）
server-id=2
# 主库启用二进制日志，从库非必须； 
log-bin=mysql-bin 
# 表示每次事务提交，MySQL都会把binlog刷到磁盘上，是最安全但是性能损耗最大的设置。
sync-binlog=1
auto_increment_increment=1   #步进值auto_imcrement。一般有n台主MySQL就填n
auto_increment_offset=1   #起始值。一般填第n台主MySQL。此时为第一台主MySQL
# 在slave库中指定同步那些库的binlog日志。  默认所有？
replicate-do-db=wr

# 官方说明：https://dev.mysql.com/doc/refman/5.7/en/replication-options-replica.html
# binlog_format           = MIXED    //binlog日志格式，mysql默认采用statement，建议使用mixed
# log-bin                 = /data/mysql/mysql-bin.log    //binlog日志文件
# expire_logs_days        = 7                           //binlog过期清理时间
# max_binlog_size         = 100m                       //binlog每个日志文件大小
# binlog_cache_size       = 4m                        //binlog缓存大小
# max_binlog_cache_size   = 512m                     //最大binlog缓存大小

    重启MySQL：

    mysql restart

    查看日志模式：

    默认：
mysql> show variables like '%binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

mysql参数：binlog-do-db和replicate-do-db
http://blog.sina.com.cn/s/blog_747f4c1d0102w9pp.html
2.构建主从复制

  • 在主库创建一个从库可以登录的专门用户同步的账户

    mysql>CREATE USER 'sync_dba'@'%' IDENTIFIED BY '<password>';
mysql>GRANT REPLICATION SLAVE ON *.* TO 'sync_dba'@'%' IDENTIFIED BY '<password>';
mysql>FLUSH PRIVILEGES;

  • 在从库指定主库信息

    • 在主库查看主库信息

      mysql>show master status;

    • 在从库指定主库信息

      mysql>CHANGE MASTER TO MASTER_HOST='192.168.0.1', MASTER_PORT=60001, MASTER_USER='sync_dba', MASTER_PASSWORD='<password>', MASTER_LOG_FILE='loganalysis.000002', MASTER_LOG_POS=194;
# MASTER_LOG_POS ： 同步点（同步点和端口是数值类型）

    • 启动从服务器复制功能

      # 建库无法复制
mysql>start slave;

    • 查看主从复制是否配置成功

      mysql>show slave status\G; # \G为按行垂直显示结果

# Master_Log_File，Read_Master_Log_Pos 记录了IO thread读到的当前master binlog文件和位置， 对应master的binlog文件和位置。
Master_Log_File: loganalysis.000006
Read_Master_Log_Pos: 194
# Relay_Log_File，Relay_Log_Pos记录了SQL thread执行到relay log文件和位置，对应的是slave上的relay log文件和位置。
Relay_Log_File: weileyi-officeapp.000016
Relay_Log_Pos: 322
# Relay_Master_Log_File，Exec_Master_Log_Pos记录的是SQL thread执行到master binlog的文件和位置，对应的master上binlog的文件和位置。
Relay_Master_Log_File: loganalysis.000004
# Slave_IO_Running & Slave_SQL_Running == Yes ===> 主从复制正常
Slave_IO_Running: Yes # 从库与主库IO通信 : 正常运行
Slave_SQL_Running: Yes # 从库进程 ： 正常运行

三、异常

  • 异常：在从库指定主库信息

    • 现象：
    ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL '' first.

    • 原因：

      已绑定过。

    • 解决：

      # 停止已启用绑定
stop slave;
# 从库：重置绑定
reset master;
reset slave;
# 重新指定主库信息
CHANGE MASTER TO MASTER_HOST='192.168.0.1', MASTER_PORT=60001, MASTER_USER='sync_dba', MASTER_PASSWORD='blockchain@2019', MASTER_LOG_FILE='loganalysis.000001', MASTER_LOG_POS=154;
# 重新启动复制
start slave;

  • 异常：Slave_IO_Running: No

    • 现象：

      Slave_IO_Running: No

    • 原因：

      从库Master_Log_File不对应主库的File。

      • 1.查看主库

        mysql> show master status\G;
*************************** 1. row ***************************
             File: loganalysis.000006
         Position: 194
     Binlog_Do_DB: cbei_core
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 705fa506-ddfa-11ea-99c4-fa163e6d1441:1-183
1 row in set (0.00 sec)

      • 查看从库

        mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.2
                  Master_User: sync_dba
                  Master_Port: 60001
                Connect_Retry: 60
              Master_Log_File: loganalysis.000006 # 对应主库File
          Read_Master_Log_Pos: 194 # 对应主库Position
               Relay_Log_File: adam-officeapp.000016
                Relay_Log_Pos: 322
        Relay_Master_Log_File: loganalysis.000004
             Slave_IO_Running: No
            Slave_SQL_Running: No

    • 解决：

      mysql> slave stop;                 
mysql> change master to MASTER_LOG_FILE='loganalysis.000006', MASTER_LOG_POS=0;  
mysql> slave start;                               
mysql> show slave status\G;

  • 异常：Slave_SQL_Running: No

    • 现象：

      Slave_SQL_Running: No

    • 原因：

      从库上进行了写操作或者重启，事务回滚造成记录File和Position对应的值和主库不同。

      MasterSlave
      Filemaster_log_file
      Positionmaster_log_pos

    • 解决：

      # 重新指定
mysql> stop slave;
# 重新指定主库信息
mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.1', MASTER_PORT=60001, MASTER_USER='sync_dba', MASTER_PASSWORD='blockchain@2019', MASTER_LOG_FILE='loganalysis.000006', MASTER_LOG_POS=194;
# 重新启动复制
mysql> start slave;
mysql> show slave status\G;

  • 异常：主从状态正常却无法同步。

    • 现象：

      Slave_IO_Running: Yes # 从库与主库IO通信 : 正常运行
Slave_SQL_Running: Yes # 从库进程 ： 正常运行

      状态正常，但是从库不同步。

    • 原因：

      1.从库日志回放位置是主库最新位置。

      2.主库空间问题，日志被截断。

    • 解决：

      • Slave

        # 停止从库同步
mysql> stop slave;

      • Master

        • 导出数据

        • 清理日志

          # 查看主库信息
mysql> show master status\G;
# 主库重新创建一个binlog文件，Position还原。
# mysql> flush logs;
# 清除所有binlog文件，并创建binlog.000001
mysql> reset master;

      • Slave

        指定MASTER_LOG_FILE和MASTER_LOG_POS;

        # 清除所有binlog文件，并创建binlog.000001
mysql> reset master;
# 重新指定主库信息
mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.1', MASTER_PORT=60001, MASTER_USER='sync_dba', MASTER_PASSWORD='blockchain@2019', MASTER_LOG_FILE='loganalysis.000001', MASTER_LOG_POS=154;
# 重新启动复制
mysql> start slave;
mysql> show slave status\G;

      • Master

        • 导入数据

  • 异常：从机找不到日志文件。

    • 现象：

      MySQL [(none)]> show slave status\G;
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'

    • 原因：

      BINLOG目录中.index日志索引文件中没有.000001号索引文件。

    • 解决：

      清除BINLOG中所有文件，然后重启MySQL。

      reset slave;无法清除BINLOG，直接在磁盘删除。

码上富贵
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
MySQL 主从复制搭建，基于日志（binlog）
12-14
什么是MySQL主从复制 　　简单来说，是保证主SQL（Master）和从SQL（Slave）的数据是一致性的，向Master插入数据后，Slave会自动从Master把修改的数据同步过来（有一定的延迟），通过这种方式来保证数据的一致性，是主从复制。 　　MySQL主从能解决什么问题 　　一、高可用 　　因为数据都是相同的，所以当Master挂掉后，可以指定一台Slave充当Master继续保证服务运行，因为数据是一致性的（如果当插入Master挂掉，可能不一致，因为同步也需要时间），当然这种配置不是简单的把一台Slave充当Master，毕竟还要考虑后续的Salve同步Master，
MySQL主从复制原理学习
weixin_44265650的博客
10-25 499
master 服务器将数据的改变记录二进制 binlog 日志，当 master 上的数据发生改变时，则将其改变写入二进制日志中；slave 服务器会在一定时间间隔内对 master 二进制日志进行探测其是否发生改变，如果发生改变，则开始一个 I/OThread 请求 master 二进制事件；
MYSQL 主从复制 --- binlog
最新发布
qq_43842093的博客
02-17 1072
在 Master 端并不 Care 有多少个 Slave 连上了自己，只要有 Slave 的 IO 线程通过了连接认证，向他请求指定位置之后的 Binary Log 信息，他就会按照该 IO 线程的要求，读取自己的 Binary Log 信息，返回给 Slave 的 IO 线程。默认MySQL是未开启该日志的。如果读压力加大，就需要更多的 slave 来解决，但是如果slave的复制全部从 master 复制，势必会加大 master 的复制IO的压力，所以就出现了级联复制，减轻 master 压力。
mysql主从复制
m0_46673598的博客
09-24 535
mysql的主从复制介绍以及实现
MySQL主从复制
junge的博客
03-30 1485
MySQL作为世界上使用最为广泛的数据库之一，免费是其原因之一；但不可忽略的是它本身的功能的确很强大。随着技术的发展，在实际的生产环境中，由单台MySQL数据库服务器不能满足实际的需求。此时数据库集群就很好的解决了这个问题了。采用MySQL分布式集群，能够搭建一个高并发、负载均衡的集群服务器。在此之前我们必须要保证每台MySQL服务器里的数据同步。
Mysql之主从复制
2301_76858832的博客
06-25 3245
一.Mysql主从复制介绍。
MySQL主从复制（基于binlog日志方式）
一个认真学习的女孩子的博客
10-27 702
主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库；主数据库一般是准实时的业务数据库。主从复制的作用1.做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。2.架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。3.读写分离，使数据库能支撑更大的并发。a.从服务器可以执行查询工作(就是我们常说的读功能)，降低主服务器压力;（主库写，从库读，降压）
Mysql主从复制
qq_65166273的博客
05-26 232
msyql主从复制流程
mysql主从复制配置过程
01-21
其中db1和db2是计划进行主从复制的库，如果有多个，写多行即可。配置完毕后，重启数据库： service mysqld restart 2. 添加复制用户 通过phpmyadmin，添加新用户，并授予REPLICATION SLAVE权限。或者通过root用户...
基于Docker如何实现MySQL主从复制详解
01-10
MySQL的主从复制是实现应用的高性能，高可用的基础。对于数据库读操作较密集的应用，通过使数据库请求负载均衡分配到不同MySQL服务器，可有效减轻数据库压力。当遇到MySQL单点故障中，也能在短时间内实现故障切换。...
MySQL主从复制配置心跳功能介绍
12-15
在 MySQL 主从复制时，有时候会碰到这样的故障：在 Slave 上 Slave_IO_Running 和 Slave_SQL_Running 都是 Yes，Slave_SQL_Running_State 显示 Slave has read all relay log; waiting for the slave I/O thread to ...
MySQL主从复制延迟原因以及解决方案
12-14
2、主库会有binlog dump线程实时监测binlog的变更并将这些新的events推给从库（Master has sent all binlog to slave; waiting for more updates） 3、从库的IO Thread接收这些events，并将其记录入re
MySQL命令执行脚本文件
学习笔记
08-21 9008
原因：1.客户端导入导出大数据量速度非常慢。 2. Navicat导入某些脚本会报错，而脚本本身没有问题。 方法一 ： windows下使用cmd命令执行，unix或linux在的其控制台下 【Mysql的bin目录】\mysql -u用户名 -p密码 -D要导入到的数据库名&lt;【sql脚本文件路径全名】 windows： D:\mysql\bin&gt;mysql -uroot -p123...
查看Mysql集群状态
学习笔记
10-21 4258
查看Mysql集群状态 依赖sshpass命令，所以需要先安装sshpass-1.06-1.el7.x86_64.rpm 授权 chmod +x check-mysql.sh #!/bin/sh #set -ex #日志名称 LOG_FILE=host_check_$(date "+%Y%m%d").log #注释变量或密码留空则执行脚本时输入 SSHPASS=[password] ...
Mysql批量建库建表Shell脚本（水平分库分表用到）
学习笔记
01-20 2155
#!/bin/sh dbuser='root' passwd='123456' mycmd="mysql -u$dbuser -p$passwd" for db_i in {0..15};do # 删库 $mycmd -e "drop database if exists db_enterprise_$db_i;" # 建库 $mycmd -e "CREATE DATABASE db_enterprise_$db_i DEFAULT CHARACTER SET utf8
Linux系统上安装MySQL 5.6
学习笔记
12-19 1477
安装环境 :   软件 版本 位数 MySQL 5.6.22 32 CentOS 6.4 32 VMware 9.0.1 32 Mysql官方下载地址，可以根据当前系统自动识别适合当前系统版本的mysql版本： http://dev.mysql.com/downloads/mysql/ 官网比较慢，百度云盘包含32位和64位的...
MySQL主主复制-虚机实践
学习笔记
11-25 1151
文章目录一、概述方案：架构：软件：二、搭建主主架构1.配置2台Mysql0.卸载MariaDB1.配置Mysql2.安装依赖3.初始化Mysql4.启动停止5.修改密码6.验证端口通讯2.配置主主复制三、安装Keepalived1.安装Keepalived2.配置Keepalived1.配置说明2.配置keepaived.conf3.监测Mysql4.重启测试7.查看日志 一、概述 keepalived采用arp广播模式，无法跨网段虚拟IP。所以只能在同一网段，并且所有Master的端口和账号密码要保持一致
mysql 主从复制 server-id=200
09-18
MySQL主从复制是一种常用的数据复制技术，通过将一个MySQL服务器（主服务器）的数据复制到另一个或多个MySQL服务器（从服务器），以实现数据的备份、负载均衡和高可用性。 在MySQL主从复制中，每个MySQL服务器都有...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

码上富贵

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值