Linux 环境下搭建 MySQL 主从同步

MySQL 主从复制是指数据可以从一个 MySQL 数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

目标

搭建两台 MySQL 服务器，一主一从，实现主从复制。

主从同步原理

MySQL 数据复制的原理图大致如下：

上图可以看出 MySQL 数据库的复制需要启动 3 个线程来实现:

其中 1 个在主服务器上，另 2 个在从服务器上。

master 服务器将数据的改变记录二进制 binlog 日志，当 master 上的数据发生改变时，则将其改变写入二进制日志中
slave 服务器会在一定时间间隔内对 master 二进制日志进行探测其是否发生改变，如果发生改变，则开始一个 I/O Thread 请求 master 二进制事件
同时主节点为每个 I/O 线程启动一个 dump 线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动 SQL 线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后 I/O Thread 和 SQL Thread 将进入睡眠状态，等待下一次被唤醒。

搭建流程

环境

centos 7.4 系统环境

主节点：172.31.186.8
从节点：172.31.187.172

MySQL 安装参见

配置步骤

保证两个数据库中的库和数据是一致的；
创建同步账号
在主数据中创建一个同步账号（可不创建使用现有的），如果仅仅为了主从复制创建账号，只需要授予 REPLICATION SLAVE 权限。
创建一个账号，账号：master 密码： 123456

1
2
3

Mysql>CREATE USER 'master'@'%' IDENTIFIED BY '123456';
Mysql>FLUSH PRIVILEGES;
Mysql>GRANT REPLICATION SLAVE ON *.* TO 'master'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

配置主数据库

1）主数据库，必须要启用二进制日志（binary logging），并且创建一个唯一的 Server ID，这步骤可能要重启 MySQL。
2）主服务器发送变更记录到从服务器依赖的是二进制日志，如果没启用二进制日志，复制操作不能实现（主库复制到从库）。
3）复制组中的每台服务器都要配置唯一的 Server ID，取值范围是 1 ~ (232) − 1，可自主决定取值。
4）配置二进制日志和 Server ID，需要关闭 MySQL 和编辑 my.cnf 或者 my.ini 文件，在 [mysqld] 节点下添加配置。
5）下面是启用二进制日志，日志文件名以 “master-bin” 作为前缀，Server ID 配置为 1，如下：

修改 /etc/my.cnf 文件 mysqld 节点：

[mysqld]

#### master node ####
# server-id 要求唯一
server-id=1
# 设置弃用二进制日志和索引
log-bin=master-bin
log-bin-index=master-bin.index
# 设置要同步的数据库的名称 xiaolong_sync是要同步的数据库的名称
binlog_do_db=xiaolong_sync  
binlog_ignore_db=mysql
user=master

重启 MySQL

1	service mysqld restart

查看主服务器状态

mysql> show master status;
+-------------------+----------+---------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB  | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+---------------+------------------+-------------------+
| master-bin.000003 |     1478 | xiaolong_sync | mysql            |                   |
+-------------------+----------+---------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

字段解释：

File :
Position :
Binlog_Do_DB :
Binlog_Ignore_DB :
Executed_Gtid_Set :

配置从服务器

同样修改 /etc/my.cnf 文件 mysqld 节点：

[mysqld]
server-id=2
log-bin=slave-bin
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index

在从服务器里配置连接主服务器的信息：

1 2	mysql> stop slave; mysql> change master to master_host='172.31.186.8', master_port=3306, master_user='master', master_password='123456', master_log_file='master-bin.000003', master_log_pos=1478;

说明：172.31.186.8 是主服务器的地址，master_log_file=’master-bin.000003’ 是主服务器的 File（主服务器 master status 查出来的值），master_log_pos=1478 是主服务器的 Position（同样是主服务器 master status 查出来的值）
每次重新启动主服务器或者数据操作，master_log_file 和 master_log_pos 都会变。

查看从服务器状态：

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.31.186.8
                  Master_User: master
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000003
          Read_Master_Log_Pos: 1478
               Relay_Log_File: slave-relay-bin.000007
                Relay_Log_Pos: 1216
        Relay_Master_Log_File: master-bin.000003
             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: 1478
              Relay_Log_Space: 2067
              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: 1
                  Master_UUID: d0a84d4d-280a-11ec-bacb-fa163e6b8fef
             Master_Info_File: /data/mysql/data/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)

ERROR:
No query specified

mysql>

如果 Slave_SQL_Running: no 请重复执行以下内容，直至yes：

1
2
3

mysql> stop slave; 
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; 
mysql> start slave;

远程连接需要授权：

1	mysql> GRANT ALL PRIVILEGES ON . TO 'master'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

测试

主数据库：

1	mysql> CREATE DATABASE xiaolong_sync DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

会发现在主数据库创建了名为 xiaolong_sync 的数据库，从数据也会同步创建，在主服务器里进行表及数据的增删改，从数据库都会实现同步。

总结

从库通过手工执行 change master to 语句连接主库，提供了连接的用户一切条件（user 、password、port、ip），并且让从库知道，二进制日志的起点位置（file 名 position 号）； start slave
从库的 IO 线程和主库的 dump 线程建立连接。
从库根据 change master to 语句提供的 file 名和 position 号，IO 线程向主库发起 binlog 的请求。
主库 dump 线程根据从库的请求，将本地 binlog 以 events 的方式发给从库IO线程。
从库 IO 线程接收 binlog events，并存放到本地 relay-log 中，传送过来的信息，会记录到 master.info 中。
从库 SQL 线程应用 relay-log，并且把应用过的记录到 relay-log.info 中，默认情况下，已经应用过的 relay 会自动被清理 purge。

注意事项

这样读取和执行语句被分成两个独立的任务。如果语句执行较慢则语句读取任务没有慢下来。例如，如果从服务器有一段时间没有运行了，当从服务器启动时，其I/O线程可以很快地从主服务器索取所有二进制日志内容，即使SQL线程远远滞后。如果从服务器在SQL线程执行完所有索取的语句前停止，I/O 线程至少已经索取了所有内容，以便语句的安全拷贝保存到本地从服务器的中继日志中，供从服务器下次启动时执行。这样允许清空主服务器上的二进制日志，因为不再需要等候从服务器来索取其内容。

master 将操作语句记录到 binlog 日志中，然后授予 slave 远程连接的权限（ master 一定要开启 binlog 二进制日志功能；通常为了数据安全考虑，slave 也开启 binlog 功能）。
slave 开启两个线程：IO 线程和 SQL 线程。其中：IO 线程负责读取 maste r的 binlog 内容到中继日志 relay log 里；SQL 线程负责从 relay log 日志里读出 binlog 内容，并更新到 slave 的数据库里，这样就能保证 slave 数据和 master 数据保持一致了。
Mysql 复制至少需要两个 Mysql 的服务，当然 Mysql 服务可以分布在不同的服务器上，也可以在一台服务器上启动多个服务。
Mysql 复制最好确保 master 和 slave 服务器上的 Mysql 版本相同（如果不能满足版本一致，那么要保证 master 主节点的版本低于 slave 从节点的版本）。
master 和 slave 两节点间时间需同步。