数据库主从复制配置方法-CSDN博客

MySQL主从同步的原理

MySQL内建的复制功能是构建大型，高性能应用程序的基础。将MySQL的数据分布到多个系统上去，这种分布的机制，是通过将mysql的某一台主机的数据复制到其它主机（slave）上，并重新执行一遍来实现。

复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循坏，这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知的更新。

需注意的是：

在进行mysql复制时，所有对复制中的表的更新必须在主服务器上进行。否则必须要小心，以避免用户对主服器上的表进行更新与对从服务器上的表所进行更新之间的冲突。

MySQL复制过程：

如上图所示：

Mysql复制过程的第一步是master节点开启二进制日志。在每个事务更新提交之前，master在二进制日志中记录这些改变。MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。
第二步,slave将master的binary log（二进制日志）拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。
最后一步，SQL slave thread（SQL从线程）从中继日志读取事件，并重现日志中的事务而更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。
此外，在master中也有一个工作线程：和其它MySQL的连接一样，slave在master中打开一个连接也会使得master开始一个线程。复制过程有一个很重要的限制——复制在slave上是串行化的，也就是说master上的并行更新操作不能在slave上并行操作。

MySQL主从复制配置过程

配置数据库主从复制之前需要注意：slave版本需要大于等于master，否则master上的一些操作slave不支持时，会导致数据不一致。

1. 准备两台服务器（master,slave）并安装MySQL数据库。

2. 编辑master服务器上MySQL数据库的配置文件（Windows:my.ini, Linux:mysqld.cnf），增加如下配置：

server-id=1   #设置当前数据库的唯一标识，不可与其他数据库的server-id重复，否则无法进行主从复制
log-bin=mysql-bin  #开启二级制日志，用来主从同步数据
binlog_do_db = sbtest #设置主从复制的数据库名称，若需要同步多个数据库，则设置多个binlog_do_db=XXX项。
binlog_ignore_db = mysql #设置不需要进行主从复制的数据库，一般MySQL系统数据库不需要进行同步。
expire_logs_days = 3 #二进制日志过期时间，此处为：删除3天以前的日志文件。
复制代码

3. 重启数据库，并执行`show master status;`命令查看当前master服务器的状态。如下:

注意：记录下File和Position字段的值。

4. 编辑slave服务器上MySQL数据库的配置文件，增加如下配置：

server-id=2 #如上,配置不可重复
relay_log=mysql-relay-bin #配置中继日志
read_only=1 #只读模式
expire_logs_days = 3 #二进制日志过期时间，此处为：删除3天以前的日志文件。
# 如果此slave需要作为其他mysql的master，则需要把下边两行注释打开。
# log-bin=mysql-bin
# log_slave_updates=1
复制代码

5. 重启数据库，在slave服务器上运行如下命令：

mysql> CHANGE MASTER TO
MASTER_HOST='172.60.20.34',
MASTER_USER='root',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000059', #上文提到的File字段值
MASTER_LOG_POS=48113969;  #上文提到的Position字段值
mysql> start slave;
复制代码

6. 运行如下命令，查看slave节点运行状态

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.60.20.34
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000059
          Read_Master_Log_Pos: 53015005
               Relay_Log_File: sn_20_46-relay-bin.000181
                Relay_Log_Pos: 53015218
        Relay_Master_Log_File: mysql-bin.000059
             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: 53015005
              Relay_Log_Space: 53015475
              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: 
             Master_Server_Id: 222
                  Master_UUID: 372f0366-9924-11e8-84fc-000c29938ebe
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     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.00 sec)
复制代码

查看Slave_IO_Running和Slave_SQL_Running节点的值是否为Yes。否则slave节点开启失败。解决方案可参看MySQL同步故障：" Slave_SQL_Running:No" 两种解决办法

个人博客：aikiller.github.io/