文章

MySQL 主从复制

Posted 2021-03-11 Updated 2024-06- 29
By 韩壮壮
29~37 min read

MySQL主从复制

1、MySQL主从复制概述

MySQL作为世界上使用最为广泛的数据库之一，免费是其原因之一；但不可忽略的是它本身的功能的确很强大。随着技术的发展，在实际的生产环境中，由单台MySQL数据库服务器不能满足实际的需求。此时数据库集群就很好的解决了这个问题了。采用MySQL分布式集群，能够搭建一个高并发、负载均衡的集群服务器。在此之前我们必须要保证每台MySQL服务器里的数据同步。

1.1 MySQL主从复制原理

MySQL服务器之间的主从同步是基于二进制日志机制，主服务器使用二进制日志来记录数据库的变动情况，从服务器通过读取和执行该日志文件来保持和主服务器的数据一致。

主从复制架构图：

MySQL主从复制.png

1.主节点必须启用二进制日志，记录任何修改数据库数据的事件。

2.从节点开启一个线程（I/o Thread)把自己扮演成MySQL的客户端，通过MySQL协议，请求主节点的二进制日志文件中的事件。

3.主节点启动一个线程（dump Thread)，检查自己二进制日志中的事件，跟对方请求的位置对比，如果不带请求位置参数，则主节点就会从第一个日志文件中的第一个事件一个一个发送给从节点。

4.从节点接收到主节点发送过来的二进制日志数据把它放置到中继日志（Relay log）文件中；并记录该次请求到主节点的具体哪个二进制日志文件的哪个位置。

5.从节点启动另外一个线程（sql Thread )，把replaylog中的事件读取出来，并在本地再执行一次。

1.2 MySQL主从复制的分类

MySQL主从复制按照binlog的三种日志格式进行划分：

1、基于SQL语句的复制(statement-based replication, SBR)：

优点：

  • 历史悠久，技术成熟。
  • 产生的binlog文件较小，比较节省空间。
  • binlog中包含了所有数据库更改信息，可以据此来审核数据库的安全等情况；binlog可以用于实时的还原，而不仅仅用于复制。
  • 主从版本可以不一样，从服务器版本可以比主服务器版本高。

缺点：

  • 不是所有的UPDATE语句都能被复制，尤其是包含不确定操作的时候。
  • 调用具有不确定因素的 UDF（用户自定义函数）时复制也可能出问题。
  • INSERT … SELECT会产生更多的行级锁。

2、基于行的复制(row-based replication, RBR)：

优点：

  • 任何情况都可以被复制，这对复制来说是最安全可靠的。
  • 多数情况下，从服务器上的表如果有主键的话，复制就会快了很多。
  • 从服务器上采用多线程来执行复制成为可能。
  • 执行 INSERT，UPDATE，DELETE 语句时行级锁更少。

缺点：

  • binlog 文件太大
  • 复杂的回滚时 binlog 中会包含大量的数据
  • 主服务器上执行 UPDATE 语句时，所有发生变化的记录都会写到 binlog 中，而 SBR 只会写一次，这会导致频繁发生 binlog 的并发写问题
  • UDF（用户自定义函数） 产生的大 BLOB值（较大的二进制对象）会导致复制变慢
  • 无法从 binlog 中看到都复制了写什么语句，无法进行审计。

3、混合模式复制(mixed-based replication, MBR)：是上面两种方式的折中。

以上三种模式对应binlog的三种格式：STATEMENT，ROW，MIXED。

1.3 MySQL主从复制的方式

异步复制：（默认的复制策略是此方式）

  • 主服务器执行Commit提交事务操作并写入BINLOG日志后即可成功返回客户端，无需等待BINLOG日志传送给从服务器，这样从服务器的数据难免会落后于主服务器。
    异步复制.png

半同步复制：

  • 主服务器处理事务过程中，提交完事务后，必须等至少一个从服务器将收到的binlog写入relay log并返回ack确认消息才能继续处理用户后续的事务，这样从服务器最多落后主服务器一个事务，但主服务器必须等待从服务器返回ack后才能继续处理后续的事务，主服务器的性能降低，数据写入延时也增加，如果网络延时过大主服务器等待从服务器返回ack超时后会自动切为异步复制。
    半同步复制.png

增强半同步复制：

  • 为了保证主服务器上的每一个BINLOG事务都能够被可靠地复制到从服务器上，主服务器写数据后并不提交事务而是先将操作写入到BINLOG，然后等待至少一个从服务器接收到BINLOG事务并成功写入中继日志后，主服务器才将事务完成提交，同样也存在主服务器的性能降低，数据写入延时也增加的问题，从服务器返回ack超时后也会自动切为异步复制。
    增强半同步复制.png

1.4 MySQL主从复制的好处

  • 实时备份，提高数据安全性，同时用于故障切换，避免影响业务。
  • 读写分离（mysql-proxy），将读请求交给从服务器，实现负载分担。
  • 数据库性能提高，通过增加从数据库组建集群，提高总性能。

2、MySQL主从复制配置

主从复制需要两台虚拟机，分别为master（主服务器）和slave（从服务器）。

//主服务器和从服务器安装并初始化mariadb
[root@master ~]# dnf -y install mariadb mariadb-server
[root@master ~]# systemctl enable mariadb
[root@master ~]# systemctl start mariadb
[root@master ~]# mysql_secure_installation 

[root@slave ~]# dnf -y install mariadb mariadb-server
[root@slave ~]# systemctl enable mariadb
[root@slave ~]# systemctl start mariadb
[root@slave ~]# mysql_secure_installation 

//主服务器关闭防火墙和selinux
[root@master ~]# systemctl stop firewalld.service 
[root@master ~]# systemctl disable firewalld.service 
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

[root@master ~]# sed -i s/SELINUX=enforing/SELINUX=disabled/g /etc/selinux/config 
[root@master ~]# setenforce 0

//从服务器关闭防火墙和selinux
[root@slave ~]# systemctl stop firewalld.service 
[root@slave ~]# systemctl disable firewalld.service 
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

[root@slave ~]# sed -i s/SELINUX=enforing/SELINUX=disabled/g /etc/selinux/config 
[root@slave ~]# setenforce 0

//在主服务器上配置主从复制，开启二进制日志，设置服务id
[root@master ~]# vim /etc/my.cnf
[mysqld]
log_bin=mysql-bin			//开启二进制日志
server_id=1				//服务id号，不可重复,为0则拒绝从服务器连接

[root@master ~]# systemctl restart mariadb.service 

//在主服务器授权一个数据库用户用于复制，创建完成后在从服务器测试登录
mysql> GRANT REPLICATION SLAVE ON *.* TO 'zcfz'@'%' IDENTIFIED BY 'hzz123!';
Query OK, 0 rows affected (0.001 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.001 sec)

//如果主服务器中有数据，需要将现有数据与从服务器同步,可以使用备份工具将数据导出，再导入至从服务器
mysqldump -u用户名 -p密码 --all-databases --master-data=1 > dbdump.db

//在从服务器配置主从复制，设置服务id，如果主服务器有数据记得先导入
[root@slave ~]# vim /etc/my.cnf
[mysqld]
server_id=2			//服务id号，不可重复,为0则拒绝从服务器连接

[root@slave ~]# systemctl restart mariadb.service 

//登录到从服务器数据库，在数据库中配置主服务器信息
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.52.128',
MASTER_PORT=3306,
MASTER_USER='zcfz',
MASTER_PASSWORD='hzz123!',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=444,
MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.002 sec)

//启动主从复制
mysql> START SLAVE;
Query OK, 0 rows affected (0.001 sec)

//查看从服务器主从复制是否开启
mysql> SHOW SLAVE STATUS \G 
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.52.128
                   Master_User: zcfz
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000002
           Read_Master_Log_Pos: 444
                Relay_Log_File: mariadb-relay-bin.000003
                 Relay_Log_Pos: 937
         Relay_Master_Log_File: mysql-bin.000002
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
.....省略.....


//STOP SLAVE 是停止从主服务器复制,也可单独开启或停止I/O线程和SQL线程
mysql> STOP SLAVE;
mysql> START/STOP SLAVE IO_THREAD;
mysql> START/STOP SLAVE SQL_THREAD;

//测试，在主服务器上创建数据库，然后在从服务器上查看是否会复制到从服务器
//主服务器创建数据库
mysql> CREATE DATABASE hzz;
Query OK, 1 row affected (0.001 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| hzz                |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
5 rows in set (0.000 sec)

//从服务器成功复制数据库
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| hzz                |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)

3、MySQL增强半同步复制配置

增强半同步复制基于异步复制，需要先配置异步复制。

//查看MySQL有没有增强半同步复制插件，基本现在的MySQL版本自带此插件不需要加载
mysql> show global status like '%rpl%';
+--------------------------------------------+-------------+
| Variable_name                              | Value       |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients               | 0           |
| Rpl_semi_sync_master_get_ack               | 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_request_ack           | 0           |
| Rpl_semi_sync_master_status                | OFF         |
| 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           |
| Rpl_semi_sync_slave_send_ack               | 0           |
| Rpl_semi_sync_slave_status                 | OFF         |
| Rpl_status                                 | AUTH_MASTER |
| Rpl_transactions_multi_engine              | 0           |
+--------------------------------------------+-------------+
20 rows in set (0.001 sec)

//如果没有在主服务器和从服务器的数据库中加载plugin插件
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (4.52 sec)

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.07 sec)

//先在从服务器开启增强半同步复制
mysql> SET GLOBAL rpl_semi_sync_slave_enabled =1;

mysql> stop  slave io_thread;
mysql> start  slave io_thread;

//然后主服务器上再开启增强半同步参数
mysql> SET GLOBAL rpl_semi_sync_master_enabled =1;

//在主服务器上查看增强半同步复制状态，发现已经有一个客户端连接
MySQL > show global status like '%rpl%';
+--------------------------------------------+-------------+
| Variable_name                              | Value       |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients               | 1           |
| Rpl_semi_sync_master_get_ack               | 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_request_ack           | 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           |
| Rpl_semi_sync_slave_send_ack               | 0           |
| Rpl_semi_sync_slave_status                 | OFF         |
| Rpl_status                                 | AUTH_MASTER |
| Rpl_transactions_multi_engine              | 0           |
+--------------------------------------------+-------------+
20 rows in set (0.001 sec)

//以上登录到数据库系统中为临时开启增强半同步复制，当数据库系统重启后失效

//下面方式将配置写入数据库配置文件中，数据库系统重启后仍然有效
//主服务器配置，记得重启服务
[root@master ~]# vim /etc/my.cnf
[mysqld]
server_id=1
plugin_load_add=semisync_master.so	//如果mysql本身自带此插件则不需要此项
plugin_load_add=semisync_slave.so	//如果mysql本身自带此插件则不需要此项
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000	//1000(1 second) 同步复制中由于网络原因导致复制时间超过1s后，增强半同步复制就变成了异步复制

//从服务器配置，记得重启服务
[root@slave ~]# vim /etc/my.cnf
[mysqld]
server_id=2
plugin_load_add=semisync_master.so	//如果mysql本身自带此插件则不需要此项
plugin_load_add=semisync_slave.so	//如果mysql本身自带此插件则不需要此项
rpl_semi_sync_slave_enabled=1

注意：实际操作中不建议把参数rpl_semi_sync_master_enabled 和rpl_semi_sync_slave_enabled 直接写入配置文件；当从服务器挂掉，重新开启从服务器或者是从服务器重启后，会自动开启增强半同步复制；如果从服务器断开主服务器的时间较长，最好是先开启MySQL异步复制，让从服务器追赶上主服务器后，然后再开启增强半同步复制，这样不会拖垮主服务器，要是直接开启增强半同步复制可能会拖垮主服务器的。

4、GTID复制

主服务器更新数据时，会在事务前产生GTID，一同记录到binlog日志中。从服务器的io线程将变更的binlog，写入到本地的relay log中。从服务器的sql线程从relay log中获取GTID，然后对比本地的binlog是否有记录，如果有记录，说明该GTID的事务已经执行，slave会忽略。如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。

//主库配置
[root@master ~]# vim /etc/my.cnf
[mysqld]
log_bin=mysql-bin			//开启二进制日志
binlog_format=mixed			//二进制日志记录模式
server_id=1
gtid-mode=on				//启动事务ID模式
enforce-gtid-consistency=true		//强制GTID一致，不允许事务违反GTID一致性
log-slave-updates=1			//告诉从库将主从复制语句也记录在binlog日志，从库需要开启binlog

//从库配置
[root@slave ~]# vim /etc/my.cnf
[mysqld]
log_bin=mysql-bin
server_id=2
autocommit=0
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1

//重启服务生效

//在主服务器授权一个数据库用户用于复制
mysql> GRANT REPLICATION SLAVE ON *.* TO 'zcfz'@'%' IDENTIFIED BY 'hzz123!';
Query OK, 0 rows affected (0.001 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.001 sec)

//登录到从服务器数据库，在数据库中配置主服务器主从复制信息
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.52.128',
MASTER_PORT=3306,
MASTER_USER='zcfz',
MASTER_PASSWORD='hzz123!',
MASTER_AUTO_POSITION=1;		//根据GTID自动确定主服务器同步位置
Query OK, 0 rows affected (0.002 sec)

//启动主从复制
mysql> START SLAVE;
Query OK, 0 rows affected (0.001 sec)

//在同步时，可能会遇到两种情况
1、从库没有备份：自动从主库的第1个gtid对应的pos号开始复制。

2、从库有备份：GTID主从复制有备份的情况下恢复主从关系;设置好从库复制开始复制位置点,从库会自动从第11个gtid开始复制
SET @@GLOBAL.GTID_PURGED='2386f449-98a0-11ea-993c-000c298e182d:1-10';

//若复制时有重复事件，需要设置从库跳过某个错误事务，避免主从复制失败。
mysql> show binlog events in 'mysql_bin.000003';  //事先在主库上查看报错的事件ID

mysql> stop slave;
mysql> SET @@SESSION.GTID_NEXT='54b37ba3-a10b-11ec-98cb-000c296abc8d:10';	//从库跳过某事件
mysql> start slave;
MySQL
MySQL
License: