MySQL 主主复制

Strong 2021年03月14日 644次浏览

MySQL主主复制

1、MySQL主主复制概述

MySQL主从复制虽然能够提高数据库总体性能、实时备份、利用mysql-proxy实现读写分离,但当主服务器宕机时业务还是中断,所以就需要主主复制,两台主服务器实现负载。

1.1 MySQL主主复制原理

主从复制原理是当从库IO线程接受到主库传递来的二进制日志(Binlog)并将之保存为从库的中继日志(relay log),然后从库SQL线程将中继日志(relay log)的事件重做到从库上,实现主从数据同步。如果SQL线程发现重复的事件并且server_id与当前从库的server_id相同,则会丢弃该事件,因此两台MySQL服务器互为主从,不会导致相同的事件被重复执行。

主主复制架构图:
MySQL主主复制.png

在MySQL集群中通过双主或多主架构,可以解决一主多从架构的单点故障,减少主从切换的故障处理时间,增加MySQL群集的高可用性。

1.2 MySQL主主复制优点

  • 主主模式能将读写请求分摊到两个主节点,有效提升服务器使用率。
  • 主节点发生故障后,能快速进行主从切换。
  • 当故障节点恢复后,故障节点能通过复制进行数据恢复(应用其他节点数据)和数据同步(将未同步数据发生给其他节点)。

1.3 MySQL主主复制缺点

  • 当主节点上MySQL实例发生故障后,可能会存在部分数据(Binlog)未同步到另外的主节点,导致数据丢失(直到故障节点恢复)。
  • 主主模式下,很容易因数据访问控制不当导致数据冲突。
  • 为提高系统高可用性,双主架构会被扩展成双主多从结构,同样存在主节点发生故障后多个从库选主和恢复复制的问题。

1.4 MySQL主主复制存在的问题

在主主模式下,两个主库都提供读写服务,如果应用通过两个主库操作相同数据,则会发生冲突导致数据覆盖(使用语句模式复制)或复制异常(使用行模式复制),因此需要对读写服务进行控制:

  • 基于自主主键控制,通过设置自增属性auto_increment_offset和auto_increment_increment来控制每个主节点生产不同的自增值,并根据不同自增值访问不同主节点。
  • 基于库级别或表级别控制,如应用APP1访问节点node1上的DB1库,而应用APP2访问节点node2上的DB2库,两个主节点间不会操作相同表的数据,因此不会存在事务冲突。

2、MySQL主主复制配置

主主复制需要两台虚拟机,分别为master1(主服务器1:192.168.52.128)和master2(主服务器2:192.168.52.129)。

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

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

//主服务器1和主服务器2关闭防火墙和selinux
[root@master1 ~]# systemctl stop firewalld.service 
[root@master1 ~]# systemctl disable firewalld.service 
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@master1 ~]# sed -i s/SELINUX=enforing/SELINUX=disabled/g /etc/selinux/config 
[root@master1 ~]# setenforce 0

[root@master2 ~]# systemctl stop firewalld.service 
[root@master2 ~]# systemctl disable firewalld.service 
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@master2 ~]# sed -i s/SELINUX=enforing/SELINUX=disabled/g /etc/selinux/config 
[root@master2 ~]# setenforce 0


//在主服务器1上配置主从复制,开启二进制日志
[root@master1 ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
log_bin=mysql-bin		//开启二进制日志
server_id=1			//服务id号,不可重复,为0则拒绝从服务器连接
skip_name_resolve=on		//优化连接速度
auto_increment_offset=1   	//自动增长起始值。一般填第n台主MySQL。此时为第一台主MySQL
auto_increment_increment=2	//自动增长步进值,一般有n台主MySQL就填n
binlog-ignore=mysql   		//忽略mysql库,一般不写
replicate-do-db=test		//要同步的数据库,默认所有库,一般也不写
log-slave-updates=1		//将写的操作全部写入二进制日志,多主多从时开启
:wq!

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

//在主服务器2上配置主从复制,开启二进制日志
[root@master2 ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
log_bin=mysql-bin		//开启二进制日志
server_id=2			//服务id号,不可重复,为0则拒绝从服务器连接
skip_name_resolve=on		//优化连接速度
auto_increment_offset=2   	//自动增长起始值。一般填第n台主MySQL。此时为第二台主MySQL
auto_increment_increment=2	//自动增长步进值,一般有n台主MySQL就填n
:wq!

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

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

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.001 sec)

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

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.001 sec)

//登录到主服务器2的数据库,在数据库中配置主服务器1的信息
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.52.128',MASTER_PORT=3306,MASTER_USER='zcfz1',MASTER_PASSWORD='hzz123!';
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.001 sec)

//登录到主服务器1的数据库,在数据库中配置主服务器2的信息
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.52.129',MASTER_PORT=3306,MASTER_USER='zcfz2',MASTER_PASSWORD='hzz123!';
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.001 sec)

//查看主服务器1和主服务器2是否互为主从
//master1
MariaDB [(none)]> SHOW SLAVE STATUS \G 
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.52.129
                   Master_User: zcfz2
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000002
           Read_Master_Log_Pos: 638
                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
.....省略.....

//master2
MariaDB [(none)]> SHOW SLAVE STATUS \G 
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.52.128
                   Master_User: zcfz1
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000002
           Read_Master_Log_Pos: 638
                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
.....省略.....

//在主服务器数据库中添加数据,查看是否复制到主服务器2
//主服务器1创建数据库
MariaDB [(none)]> CREATE DATABASE hzz;
Query OK, 1 row affected (0.001 sec)

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

//主服务器2成功复制主服务器1新建的数据库
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| hzz                |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)

//在master2数据库中添加数据,查看是否复制到master1
//主服务器2创建数据库
MariaDB [(none)]> CREATE DATABASE zzh;
Query OK, 1 row affected (0.001 sec)

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

//主服务器1成功复制主服务器2新建的数据库
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| hzz                |
| information_schema |
| mysql              |
| performance_schema |
| zzh                |
+--------------------+
4 rows in set (0.000 sec)