Mysql配置主从同步

一、什么是Mysql主从同步

 将主数据库中的DDL和DML操作通过二进制日志传输到从数据库上，然后将这些日志重新执行（重做）；从而使得从数据库的数据与主数据库保持一致。

基本原理：

• MySQL支持单向、异步复制，复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。
• MySQL复制是基于主服务器在二进制日志中跟踪所有对数据库的更改。因此，要进行复制，必须在主服务器上启用二进制日志。每个从服务器从主服务器接收主服务器已经记录到日志的数据。
• 当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，并在本机上执行相同的更新。然后封锁并等待主服务器通知新的更新。从服务器执行备份不会干扰主服务器，在备份过程中主服务器可以继续处理更新。

二、主从复制的作用

• 主数据库出现问题，可以切换到从数据库。
• 可以进行数据库层面的读写分离。
• 可以在从数据库上进行日常备份。

三、释义图

Binary log:主数据库的二进制日志。

Relay log：从服务器的中继日志。

第一步：master在每个事务更新数据完成之前，将该操作记录串行地写入到binlog文件中（主）。

第二步：salve开启一个I/O Thread，该线程在master打开一个普通连接，主要工作是binlog dump process。如果读取的进度已经跟上了master，就进入睡眠状态并等待master产生新的事件。I/O线程最终的目的是将这些事件写入到中继日志中（从）。

第三步：SQL Thread会读取中继日志，并顺序执行该日志中的SQL事件，从而与主数据库中的数据保持一致（从）。

四、主从配置实操

1、准备两台Mysql服务器（主：192.168.2.64，从：192.168.2.34），并满足以下要求

• 服务器网络互通，端口（3306等）互通。
• Mysql版本一致，不一致的话问题千奇百怪。

2、修改服务器Mysql配置，并重启Mysql服务，Linux下为 /etc/my.cnf ，Windows下为 my.ini 具体位置根据安装时设置为准。

master

[mysqld]
#设置主服务器id，在主从关系中保持唯一
server-id=1
#设置同步哪个数据库，此数据库变更将操作写入二进制日志Binary log
binlog-do-db=bds-oneaccept
#要生成的二进制日志文件名称前缀，例如：mysql-bin.000001
log-bin=mysql-bin

slave

[mysqld]
#设置从服务器id，在主从关系中保持唯一
server-id=2
#要复制的数据库名称，与主Mysql配置保持一致
replicate-do-db=bds-oneaccept
#要生成的二进制日志文件名称前缀，例如：mysql-bin.000001
log-bin=mysql-bin

　　

3、创建用于复制的专门用户,并刷新用户权限（主）

grant replication slave on *.* to 'jianguan'@'192.168.2.34' indentified by 'Spgtest_1';

flush privileges;

4、获取主数据库此刻数据坐标，用于主从启动后，复制数据的起始位置，获取到这个值后，主数据库就不能再有数据的修改操作（主）。

show master status;

 5、在从数据库中设置复制数据的用户、坐标等信息（从），在执行此操作之前需保证主从数据库现时刻数据一致，因为是从设置的坐标处开始复制。

//整行
change master to master_host='192.168.2.64',master_port=3306,master_user='jiangkun',master_password='Spgtest_1',master_log_file='mysql-bin.000001',master_log_pos=23431;

//换行写法
mysql> change master to master_host='192.168.2.64',
    -> master_port=3306,    
    -> master_user='jiangkun',    
    -> master_password='Spgtest_1',    
    -> master_log_file='mysql-bin.000001',   
    -> master_log_pos=23431;

如果执行出现如下报错，则表示当前主从正在进行，需要先stop slave;再设置上述命令。

 6、启动复制

start slave;

 7、开始启动后，需要查看从Mysql复制状态

注意：其中Slave_IO_Running、Slave_SQL_Running两项为YES时，表示同步正在进行。

 

8、做简单测试，在主Mysql创建表，并插入数据，在从表查看，表已建，数据也已插入，主从同步成功!

 9、查看主库数据坐标，已到mysql-bin.000001文件的24587坐标。

10、从库的执行位置表示同步已经完成，至此主从同步设置完成。

 11、理解从库中的同步状态

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.2.64  //主库的ip地址
                  Master_User: jianguan  //master上的一个用户，用来主从复制，创建主从复制的时候建立，具有reolication
                  Master_Port: 3306
                Connect_Retry: 60   //连接中断后，重新尝试连接的时间间隔，默认值60s
              Master_Log_File: mysql-bin.000001     //当前I/O线程正在读取的主服务器二进制日志文件的名称
          Read_Master_Log_Pos: 24587     //当前I/O线程正在读取的二进制日志的位置             
               Relay_Log_File: data-anal-relay-bin.000002    //SQL线程当前正在读取和执行的中继日志文件的名称
                Relay_Log_Pos: 1476      //当前已读取和执行的中继日志的位置                      
        Relay_Master_Log_File: mysql-bin.000001   
             Slave_IO_Running: Yes   //YES表示I/O线程和主库连接正常并能实施复制工作，No则说明与主库通讯异常，一般只要网络端口通，复制所用用户名、秘密正常，很少出问题
            Slave_SQL_Running: Yes   //YES表示正常，非常容易出问题，比如手动在从表插入数据，主复制到从主键冲突就会导致同步终止
              Replicate_Do_DB: bds-oneaccept  //复制的数据库
          Replicate_Ignore_DB:   //复制中忽略的数据库
           Replicate_Do_Table:   //复制的表
       Replicate_Ignore_Table:   //复制中要忽略的表
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0   //slave的SQL线程读取日志参数的错误数量
                   Last_Error:     //错误消息
                 Skip_Counter:     //用于设置跳过sql执行步数。
          Exec_Master_Log_Pos: 24587   //slave SQL线程当前执行的事件，对应在master相应的二进制日志中的position。（结合Relay_Master_Log_File理解，而且在Relay_Master_Log_File这个值等于Master_Log_File值的时候，Exec_Master_Log_Pos是不可能超过Read_Master_Log_Pos的，如果实时同步完成时，这俩值相等。）
              Relay_Log_Space: 1687   //所有原有的中继日志结合起来的总大小。
              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   //这个值是时间戳的差值。是slave当前的时间戳和master记录该事件时的时间戳的差值。
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0   //最后一次IO错误号
                Last_IO_Error:     //最后一次IO错误消息
               Last_SQL_Errno: 0   //最后一次SQL错误号
               Last_SQL_Error:     //最后一次SQL错误消息
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1    //主库服务器id号
                  Master_UUID: cc63533c-b9b5-11ea-83e9-000c29ae6191   //主库服务器的UUID
             Master_Info_File: /var/lib/mysql/master.info  //从库中保存主库服务器相关的目录位置
                    SQL_Delay: 0     //一个非负整数，表示秒数，slave滞后于master多少秒
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400   //连接主库失败最多的重试次数
                  Master_Bind:     //slave从库在多网络接口的情况下使用，以确定用哪一个slave网络接口连接到master
      Last_IO_Error_Timestamp:    //最后一次I/O线程错误时的时间戳。
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.01 sec)

 12、异常处理

如果主从复制中途出现问题，例如：

• 从机宕机后，重启同步出错
• 人为在从库增删数据导致同步出错（主键冲突、更新失败）。
• .......

一旦出错，有以下命令可以使用

reset master;

删除所有的binglog日志文件，并将日志索引文件清空，重新开始所有新的日志文件。用于第一次进行搭建主从库时，进行主库binlog初始化工作，也可修复时使用，建议修复时停掉应用，或者锁库。

stop slave

停止主从同步

reset slave

用于删除slave数据库的relaylog日志文件，并重新启用新的relaylog文件。

将使slave 忘记主从复制关系的位置信息。该语句将被用于干净的启动, 它删除master.info文件和relay-log.info 文件以及所有的relay log 文件并重新启用一个新的relaylog文件。

留存内存中与主数据库的连接信息，change后可直接启动 start slave 启动主从复制。

reset slave all

删除所有与主库之间的关联，如需再次启动需要重新配置连接信息