MySQL主主复制配置

MySQL主主复制配置

主主复制需要两台虚拟机，分别为master1（主服务器1：192.168.40.99）和master2（主服务器2：192.168.40.100）。此处使用的mariadb数据库和MySQL差不多的。

主服务器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 'haha'@'%' IDENTIFIED BY 'haha123!';
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 'haha'@'%' IDENTIFIED BY 'haha123!';
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.40.99',MASTER_PORT=3306,MASTER_USER='haha',MASTER_PASSWORD='haha123!';
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.40.100',MASTER_PORT=3306,MASTER_USER='haha',MASTER_PASSWORD='haha123!';
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.40.100
                   Master_User: haha
                   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.40.99
                   Master_User: haha
                   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 hehe;
Query OK, 1 row affected (0.001 sec)

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

主服务器2成功复制主服务器1新建的数据库

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

在master2数据库中添加数据，查看是否复制到master1
主服务器2创建数据库

MariaDB [(none)]> CREATE DATABASE kk;
Query OK, 1 row affected (0.001 sec)

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

主服务器1成功复制主服务器2新建的数据库

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