详细介绍MySQL如何开启主从复制实现数据同步

---

主从复制原理

主从复制线程

主节点

   dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events

从节点

   I/O Thread：向Master请求二进制日志事件，并保存于中继日志中
   SQL Thread：从中继日志中读取日志事件，在本地完成重放

跟复制功能相关的文件

   master.info：用于保存slave连接至master时的相关信息，例如账号、密码、服务器地址等

   relay-log.info：保存在当前slave节点上已经复制的当前二进制日志和本地replay log日志的对应关系

主从复制特点

   异步复制
   主从数据不一致比较常见

复制架构

   Master/Slave,Master/Master,环状复制
   一主多从
   从服务器还可以再有从服务器
   一从多主：适用于多个不同数据库

复制需要考虑二进制日志时间记录格式

   STATEMENT（5.0之前）
   ROW（5.1之后，推荐）
   MIXED

搭建环境准备

主机	IP地址	类型
CentOS7.6	192.168.36.101	Master
CentOS7.6	192.168.36.103	Slave
CentOS7.6	192.168.36.104	Slave

搭建之前请先确保MySQL软件包已经安装

一主一从

Master节点修改数据库配置文件

[root@Master ~]#cat /etc/my.cnf
[mysqld]
server_id=1     # 为Master节点设置一个全局唯一的ID号
binlog_format=row       # 基于行复制的数据库语句
log-bin=/data/bin/mysql-bin     # 启用二进制日志

重新启动数据库服务

[root@Master ~]#service mysqld restart
Restarting mysqld (via systemctl):                         [  OK  ]

Master节点上创建带有复制权限的用户账号

MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.36.%' identified by 'centos';
Query OK, 0 rows affected (0.00 sec)

查看Master的日志位置信息

MariaDB [mysql]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |    912372 |
+------------------+-----------+
1 row in set (0.00 sec)

Slave节点修改配置文件

[root@Slave-1 ~]#cat /etc/my.cnf
[mysqld]
server_id=2         # Slave节点设置全局唯一的ID号
read_only           # 只读

重新启动数据库服务

[root@Slave-1 ~]#systemctl restart mariadb

使用Master创建的复制权限的用户账号进行同步

MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.36.101',
    ->  MASTER_USER='repluser',
    ->  MASTER_PASSWORD='centos',
    ->  MASTER_PORT=3306,
    ->  MASTER_LOG_FILE='mysql-bin.000001',
    ->  MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.01 sec)

启动Slave线程

MariaDB [(none)]> slave start;
Query OK, 0 rows affected (0.00 sec)

查看线程是否启动

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.36.101
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 7389
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 7673
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes      # 从节点的IO线程
            Slave_SQL_Running: Yes      # 从节点的SQL线程
....
        Seconds_Behind_Master: 0        # Master与SLave服务器差别延迟
.....
             Master_Server_Id: 1
1 row in set (0.00 sec)

检查数据同步情况

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

主节点中查看数据库的线程

MariaDB [hellodb]> show processlist;
+----+----------+----------------------+---------+-------------+------+------------------------------------------------------------------
| Id | User     | Host                 | db      | Command     | Time | State
+----+----------+----------------------+---------+-------------+------+------------------------------------------------------------------
|  3 | root     | localhost            | hellodb | Query       |    0 | NULL
|  6 | repluser | 192.168.36.103:51516 | NULL    | Binlog Dump |  115 | Master has sent all binlog to slave; waiting for binlog to be upd
+----+----------+----------------------+---------+-------------+------+------------------------------------------------------------------
2 rows in set (0.00 sec)

从节点中查看数据库的线程

MariaDB [(none)]> show processlist;
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+--
| Id | User        | Host      | db   | Command | Time  | State                                                                       | I
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+--
|  4 | root        | localhost | NULL | Query   |     0 | NULL                                                                        | s
|  5 | system user |           | NULL | Connect |   159 | Waiting for master to send event                                            | N
|  6 | system user |           | NULL | Connect | 29259 | Slave has read all relay log; waiting for the slave I/O thread to update it | N
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+--
3 rows in set (0.00 sec)

一主多从：新添加一个从节点

Slave-2 节点安装数据库服务

[root@Slave-2 ~]#yum install -y mariadb-server

修改 Slave-2 节点配置文件

[root@Slave-2 ~]#cat /etc/my.cnf
[mysqld]
server_id=3
read_only
....

重新启动 Slave-2 节点数据库服务

[root@Slave-2 ~]#systemctl restart mariadb

Slave-2 节点配置同步信息

MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.36.101',
    ->  MASTER_USER='repluser',
    ->  MASTER_PASSWORD='centos',
    ->  MASTER_PORT=3306,
    ->  MASTER_LOG_FILE='mysql-bin.000001',
    ->  MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.00 sec)

启用线程

MariaDB [(none)]> slave start;
Query OK, 0 rows affected (0.00 sec)

检查同步情况

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)

主主复制：两台服务器互为主从

主机	IP地址	类型
CentOS7.6	192.168.36.101	Master-1
CentOS7.6	192.168.36.103	Master-2

Master-1 修改配置文件

[root@Master-1 data]#cat /etc/my.cnf
[mysqld]
server_id=1
log_bin     # 启用日志

# 为避免ID冲突问题，需要添加下面两行配置：Master-1 为奇数增长，Master-2 为偶数增长
auto_increment_offset=1     # 开始点
auto_increment_increment=2  # 增长幅度
....

Master-2 修改配置文件

[root@Master-2 data]#cat /etc/my.cnf
[mysqld]
server_id=2
log_bin
auto_increment_offset=2
auto_increment_increment=2

重新启动服务

[root@Master-1 ~]#systemctl restart mariadb
[root@Master-2 ~]#systemctl restart mariadb

Master-1 创建权限用户，Master-2 先设置为从服务器进行同步 Master-1 的数据库

MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.36.%' identified by 'centos';
Query OK, 0 rows affected (0.00 sec)

Master-2 同步Master-1

MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.36.101',
    ->  MASTER_USER='repluser',
    ->  MASTER_PASSWORD='centos',
    ->  MASTER_PORT=3306,
    ->  MASTER_LOG_FILE='mysql-bin.000001',
    ->  MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.00 sec)

启用线程

MariaDB [(none)]> slave start;
Query OK, 0 rows affected (0.00 sec)

检查同步情况

MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> select user,password,host from user;
+----------+-------------------------------------------+--------------+
| user     | password                                  | host         |
+----------+-------------------------------------------+--------------+
| root     |                                           | localhost    |
| root     |                                           | 127.0.0.1    |
| root     |                                           | ::1          |
| repluser | *128977E278358FF80A246B5046F51043A2B1FCED | 192.168.36.% |
+----------+-------------------------------------------+--------------+
7 rows in set (0.00 sec)

Master-1 向 Master-2 进行同步

MariaDB [mysql]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.36.103',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='centos',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.05 sec)

Master-1 同步状态

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.36.103
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 245
               Relay_Log_File: mariadb-relay-bin.000003
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
....
           Master_SSL_Allowed: No
....
             Master_Server_Id: 2
1 row in set (0.01 sec)

此时Master-1、Master-2 实现主主复制功能

半同步复制

   半同步复制的作用是：主服务器宕机后，所有备份服务器主动向同步数据最多的服务器进行数据的同步，以确保数据损失降到最低。

半同步是通过插件功能达成的

Master节点安装semisync_master.so插件

MariaDB [db1]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.07 sec)

启用插件功能

MariaDB [db1]> SET GLOBAL rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)

设置超时时长

MariaDB [db1]> SET GLOBAL rpl_semi_sync_master_timeout=6000;
Query OK, 0 rows affected (0.00 sec)

查看Master插件状态

MariaDB [db1]> show variables like '%semi%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 6000  |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
4 rows in set (0.00 sec)

Master修改配置文件并重启数据库服务

[root@Master data]#cat /etc/my.cnf
[mysqld]
...
rpl_semi_sync_master_enabled        # 启用插件功能
...

[root@Master data]#systemctl restart mariadb

Slave节点安装semisync_slave.so插件

MariaDB [db1]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)

启用slave插件功能

MariaDB [db1]> SET GLOBAL rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)

Slave修改配置文件并重启数据库服务

[root@Slave data]#cat /etc/my.cnf
[mysqld]
...
rpl_semi_sync_slave_enabled        # 启用插件功能
...

[root@Slave data]#systemctl restart mariadb

停止Slave 数据库服务进行测试

MariaDB [(none)]> create database db3;
Query OK, 1 row affected (6.00 sec)     # 由等待时长可以看出，半同步插件已经起到效果