MySQL Replication 实现高可用

MySQL Replication 介绍
Replication 是 MySQL 数据库提供的一种高可用性能解决方案，通过多台数据库解决MySQL单点故障，将客户端请求中的 “读” 分摊到多台服务器上，支援安全的备份操作。

Replication 工作原理

Alt text

主服务器 (Master) 把数据更改记录到二进制日志 Binary log 中
从服务I/O线程请求主服务复制二进制日志，并保存为中继日志 (Relay log)
从服务器SQL线程读取中继日志，把更改应用到自己的数据库，已达到同步主服务器数据库的目的

Replication 并不是完全实时地进行同步，而是异步实时，如果主服务器工作繁忙，可能会导致从服务器延时较到。

主/从架构

异步复制

一主一从
Alt text

双主复制
Alt text

一主多从
Alt text

级联复制
Alt text

循环复制
Alt text

半同步复制
- 一丛多主

MySQL 主从复制

Alt text

第一步：同步主从节点的时间
搭建 NTP 服务器，用于其他主机同步时间所用

# 1. 使用 ansible 安装 ntp
[20@root ~]# cat /etc/ansible/hosts
[mysql]
172.18.17.20
172.18.17.21
[20@root ~]# ansible mysql -m yum -a "name=ntp state=latest"

# 2. 172.18.17.20 配置为时间同步服务器
# 2.1 先将自身时间与上一层时间同步器同步
[20@root ~]# ntpdate 172.18.0.1
# 2.2 修改配置文件
[20@root ~]# vim /etc/ntp.conf
restrict 172.18.0.0 mask 255.255.0.0 nomodify notrap  <=允许哪些主机同步
server 172.18.0.1 iburst  <=上层NTP服务器,即本NTP服务器从哪台服务器同步时间
server 127.0.0.1 iburst   <=与上层服务器失联，则使用本机的时间
fuge 127.0.0.1 stratum 10 <=定义本NTP服务器所处的层次
# 2.3 设置ntpd启动，并且下次开机自动启动
[20@root ~]# systemctl enable ntpd
[20@root ~]# systemctl start ntpd


# 3. 配置客户端同步时间
# 3.1 修改配置文件
[21@root ~]# ntpdate 172.18.17.20
[21@root ~]# vim /etc/ntp.conf 
server 172.18.17.20 iburst
# 3.2 设置ntpd启动，并且下次开机自动启动
[20@root ~]# systemctl enable ntpd
[20@root ~]# systemctl start ntpd

第二步：编辑主节点配置文件，并创建 replication 用户

安装 MariaDB

[20@root ~]# ansible mysql -m yum -a "name=mariadb-server state=latest"

修改主节点配置文件

# 1. 创建存放二进制日志文件的路径，避免二进制日志与数据存储于同一路径，防止系统崩溃丢失用于恢复数据的二进制日志
[20@root ~]# mkdir -p /app/data/log
[20@root ~]# chown mysql.mysql /app/data/log

# 2. 修改配置文件
[20@root ~]# vim /etc/my.cnf.d/server.cnf 
[server]
server_id=1
innodb_file_per_table=ON
skip_name_resolve=ON
max_connections=10000
log_bin=/app/data/log/log-bin

创建 replication 用户，为从节点远程连接到主节点上，并复制二进制日志使用

# 1. 创建用户
MariaDB> grant replication client,replication slave on *.* to 'reuser'@'172.18.17.%' identified by '123';
MariaDB> flush privileges;

# 2. 查看当前二进制日志信息
MariaDB> show master logs;
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------+
| log-bin.000001 |     30322 |
| log-bin.000002 |   1038814 |
| log-bin.000003 |       264 |
| log-bin.000004 |       490 |
+----------------+-----------+

第三步：编辑从节点配置文件

修改从节点配置文件

# 1. 创建目录存放中继日志
[21@root ~]# mkdir -p /app/data/log
[21@root ~]# chown mysql.mysql /app/data/log

# 2. 修改配置文件
[21@root ~]# vim /etc/my.cnf.d/server.cnf 
[server]
server_id=2
innodb_file_per_table=ON
skip_name_resolve=ON
max_connections=10000
relay_log=/app/data/log/relay-log
read_only=ON
sync_master_info=1
sync_relay_log_info=1

指定从节点的主服务器等相关信息

# 1. 定义连接主节点的信息
MariaDB> change master to master_host='172.18.17.20', master_user='reuser', master_password='123', master_log_file='log-bin.000004', master_log_pos=490;

# 2. 启用 slave
MariaDB [(none)]> start slave;

查看从节点相关信息

# 1. slave 运行状态

MariaDB [(none)]> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.18.17.20
                  Master_User: reuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log-bin.000004
          Read_Master_Log_Pos: 490
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 527
        Relay_Master_Log_File: log-bin.000004
             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: 490
              Relay_Log_Space: 815
              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

# 2. 连接主节点的记录信息，从节点崩溃可依据此修复
[21@root ~]# cat /var/lib/mysql/master.info 
18
log-bin.000004
490
172.18.17.20
reuser
123
3306
60
0

# 3. 从节点中继日志信息
[21@root ~]# cat /var/lib/mysql/relay-log.info 
/app/data/log/relay-log.000002
527
log-bin.000004
490

MySQL 双主复制

Alt text

第一步：同步主从节点的时间

第二步：配置第一个主节点

第一个主节点配置文件

[20@root ~]# vim /etc/my.cnf.d/server.cnf
[server]
server_id=1
innodb_file_per_table=ON
skip_name_resolve=ON
max_connections=10000
# bin-log 日志文件
log_bin=/app/data/log/log-bin
# relay-log 日志文件
relay_log=/app/data/log/relay-log
# 记录连接主节点的日志信息
sync_master_info=1
# 记录relay-log 日志信息
sync_relay_log_info=1
# 缓存同步到日志的方式及频率
innodb_flush_log_at_trx_commit=2
innodb_support_xa=ON
# 自动增长id，定义第一个节点使用奇数id
auto_increment_offset=1
auto_increment_increment=2

创建 Relication 用户账号及另一个主节点连接所用的用户

# 关闭二进制日志，配置好再开启，避免另一个主节点重复配置
MariaDB> set @@session.sql_log_bin=OFF;

# 创建用户
MariaDB> grant replication client,replication slave on *.* to 'reuser'@'172.18.17.%' identified by '123';

# 配置连接另一个主节点的信息，此步需查看另一个主节点的二进制日志信息
MariaDB> change master to master_host='172.18.17.21', master_user='reuser', master_password='123', master_log_file='log-bin.000003', master_log_pos=245;

# 启动 Slave
MariaDB> start slave;

# 启动二进制日志记录功能
MariaDB> set @@session.sql_log_bin=ON;


# 查看二进制日志信息
MariaDB> show binary logs;
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------+
| log-bin.000001 |     30782 |
| log-bin.000002 |   1069459 |
| log-bin.000003 |       245 |
+----------------+-----------+

第三步：配置第二个主节点

第二个主节点配置文件

[21@root ~]# vim /etc/my.cnf.d/server.cnf
[server]
server_id=2
innodb_file_per_table=ON
skip_name_resolve=ON
max_connections=10000
# bin-log 日志文件
log_bin=/app/data/log/log-bin
# relay-log 日志文件
relay_log=/app/data/log/relay-log
# 记录连接主节点的日志信息
sync_master_info=1
# 记录relay-log 日志信息
sync_relay_log_info=1
# 缓存同步到日志的方式及频率
innodb_flush_log_at_trx_commit=2
innodb_support_xa=ON
# 自动增长id，定义第二个节点使用偶数id
auto_increment_offset=2
auto_increment_increment=2

创建 Replication 用户账号及另一个主节点连接所用的用户

# 关闭二进制日志，配置好再开启，避免另一个主节点重复配置
MariaDB> set @@session.sql_log_bin=OFF;

# 创建用户
MariaDB> grant replication client,replication slave on *.* to 'reuser'@'172.18.17.%' identified by '123';

# 配置连接另一个主节点的信息，此步需查看另一个主节点的二进制日志信息
MariaDB> change master to master_host='172.18.17.20', master_user='reuser', master_password='123', master_log_file='log-bin.000003', master_log_pos=245;

# 启动 Slave
MariaDB> start slave;

# 启动二进制日志记录功能
MariaDB> set @@session.sql_log_bin=ON;

# 查看二进制日志信息
MariaDB> show binary logs;
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------+
| log-bin.000001 |     30782 |
| log-bin.000002 |   1069459 |
| log-bin.000003 |       245 |
+----------------+-----------+

第四步：测试

Alt text

MySQL 半同步复制

Alt text

第一步：配置主节点

主节点配置文件

[20@root ~]# vim /etc/my.cnf.d/server.cnf
[server]
server_id=1
innodb_file_per_table=ON
skip_name_resolve=ON
max_connections=10000
log-bin=/app/data/log/log-bin
innodb_flush_log_at_trx_commit=2
innodb_support_xa=ON

创建用户，给从节点复制主节点 binlog 所用

MariaDB> grant replication slave, replication client on *.* to 'reuser'@'172.18.17.%' identified by '123';

安装并启用半同步插件

# 1. 安装半同步插件
MariaDB> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

# 2. 启用半同步插件
MariaDB> set @@global.rpl_semi_sync_master_enabled=ON;

查看半同步相关的变量及 binlog 信息

# 半同步插件模块路径
[20@root ~]# ls -1 /usr/lib64/mysql/plugin/semisync_*
/usr/lib64/mysql/plugin/semisync_master.so
/usr/lib64/mysql/plugin/semisync_slave.so

# rpl_semi_sync_master 插件相关的变量信息
MariaDB> show global variables like '%semi%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 10000 |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+

# rpl_semi_sync_master 插件相关的状态信息
MariaDB> show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+

# binlog 信息
MariaDB> show master logs;
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------+
| log-bin.000001 |     30322 |
| log-bin.000002 |   1038814 |
| log-bin.000003 |       416 |
+----------------+-----------+

第二步：配置从节点

从节点配置文件

[20@root ~]# vim /etc/my.cnf.d/server.cnf 
[server]
server_id=2
innodb_file_per_table=ON
skip_name_resolve=ON
max_connections=10000
sync_master_info=1
sync_relay_log_info=1

安装并启用半同步插件

# 安装
MariaDB> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

# 启用
MariaDB> set @@global.rpl_semi_sync_slave_enabled=ON;

设置连接主节点，并启用 slave 功能

# 连接主节点配置
MariaDB> CHANGE MASTER TO MASTER_HOST='172.18.17.20', MASTER_USER='reuser', MASTER_PASSWORD='123', MASTER_LOG_FILE='log-bin.000003', MASTER_LOG_POS=416;

# 启用 slave
MariaDB> start slave;

从节点半同步相关的信息

# 半同步插件
[21@root ~]# ls -1 /usr/lib64/mysql/plugin/semisync_*
/usr/lib64/mysql/plugin/semisync_master.so
/usr/lib64/mysql/plugin/semisync_slave.so

# 半同步插件 rpl_semi_sync_slave 相关的变量信息
MariaDB> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.00 sec)

# 半同步插件 rpl_semi_sync_slave 相关的状态信息
MariaDB> show status like '%semi%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+

# 此时主节点半同步的状态信息显示有一个客户端
MariaDB> show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
...
+--------------------------------------------+-------+

第三步：配置复制过滤器

通过复制过滤器实现仅复制有限一个或几个数据库相关的数据，而非所有。有两种实现方法

主服务器仅向二进制日志中记录有关特定数据库相关的写操作
问题：其它库的time-point recovery将无从实现

# 配置变量
binlog_do_db=
binlog_ignore_db=

从服务器的SQL THREAD仅重放关注的数据库或表相关的事件，并将其应用于本地；
问题：网络IO和磁盘IO加重

# 配置变量
Replicate_Do_DB=
Replicate_Ignore_DB=
Replicate_Do_Table=
Replicate_Ignore_Table=
Replicate_Wild_Do_Table=
Replicate_Wild_Ignore_Table=

由于第一种实现方法导致二进制日志记录不全，影响数据库崩溃后的修复，所以一般不建议使用第一种，下面我们演示第二种的配置

配置从节点

MariaDB> 
MariaDB> stop slave;
MariaDB> set @@global.replicate_do_db=mydb;
MariaDB> select @@global.replicate_do_db;
+--------------------------+
| @@global.replicate_do_db |
+--------------------------+
| mydb                     |
+--------------------------+
MariaDB> start slave;

测试

Alt text

zhubiao

MySQL Replication 实现高可用

MySQL Replication 实现高可用

Table of Contents