MySql教程

MySQL主从复制-基于binlog

本文主要是介绍MySQL主从复制-基于binlog，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

文章目录

      • 一、概述
        • 软件版本
      • 二、配置主从
        • 1.修改配置文件
        • 2.构建主从复制
      • 三、异常

一、概述

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，直接在磁盘删除。

这篇关于MySQL主从复制-基于binlog的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
您可能喜欢
栏目导航