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的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!