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)