CentOS7操作系统下实现mysql主主复制
实验环境:两台主机,172.18.24.107,172,18.24.27
操作系统为Centos7.3
安装mariadb服务,源码编译安装或者yum安装都可,这里选择yum安装
172.18.24.107主机上:
停掉mariadb服务,修改配置文件
[root@node1 ~]#systemctl stop mariadb.srevice
vim /etc/my.cnf.d/server.cnf
[server]
skip_name_resolve=ON #跳过解析过程
innodb_file_per_table=ON
max_connection=20000 #最大连接数
log_bin=master-log #开启二进制日志
server_id=1 #服务器di号
relay_log=relay-log #开启中继日志
auto_increment_offset=1
auto_increment_increment=2
[root@node1 ~]#systemctl start mariadb.service #开启服务
[root@node1 ~]#mysql
MariaDB [(none)]> SHOW MASTER STATUS;
MariaDB [(none)]> STOP SLAVE;
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'172.18.24.%' IDENTIFIED BY 'replpass';
查看172.18.24.27主机上二进制日志文件记录的位置:
MariaDB [test]> SHOW BINARY LOGS;
+-------------------+-----------+
| Log_name | File_size | +-------------------+-----------+
| master-log.000001 | 30824 |
| master-log.000002 | 1069459 |
| master-log.000003 | 507 | +-------------------+-----------+
从上面可以看到172.18.24.27主机上二进制记录的结束位置是507,那么需要从507开始复制
回到在172.18.24.107主机上:
MariaDB [test]>CHANGE MASTER TO MASTER_HOST='172.18.24.107',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='masterlog.000003',MASTER_LOG_POS=507;
MariaDB [test]>START SLAVE;
MariaDB [test]>SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.18.24.107
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000003
Read_Master_Log_Pos: 723
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 746
Relay_Master_Log_File: master-log.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
172.18.24.107节点上:
systemctl stop mariadb.srevice
vim /etc/my.cnf.d/server.cnf
[server]
skip_name_resolve=ON #跳过解析过程
innodb_file_per_table=ON
max_connection=20000 #最大连接数
log_bin=master-log #开启二进制日志
server_id=1 #服务器di号
relay_log=relay-log #开启中继日志
auto_increment_offset=2 #初始值
auto_increment_increment=2 #步进值
systemctl start mariadb.service
mysql
>SHOW MASTER STATUS;
MariaDB [test]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'172.18.24.%' IDENTIFIED BY 'replpass';
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.18.24.27',MASTER_USER='repluser',
MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-log.000003',MASTER_LOG_POS=507;
MariaDB [(none)]> STOP SLAVE;
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.18.24.27
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000003
Read_Master_Log_Pos: 507
Relay_Log_File: relay-log.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-log.000003
Slave_IO_Running: No
Slave_SQL_Running: No
MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.18.24.27
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000003
Read_Master_Log_Pos: 507
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 530
Relay_Master_Log_File: master-log.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
配置完成,开始测试:
在172.18.24.107上
MariaDB [test]> show tables; #107上测试数据库test中没有表
Empty set (0.00 sec)
在172.18.24.27上
ariaDB [(none)]> use test;
Database changed
MariaDB [test]> show tables; #27上测试数据库test中没有表
Empty set (0.00 sec)
MariaDB [test]> CREATE TABLE students(id INT UNSIGNED PRIMARY KEY,name CHAR(30) NOT NULL,age TINYINT UNSIGNED,gender ENUM('F','M')); #在27上创建数据库
在172.18.24.107上查看测试数据库中是否同步了172.18.24.27上的表students
MariaDB [test]> show tables;
+----------------+
| Tables_in_test | +----------------+
| students | +----------------+
1 row in set (0.00 sec)
查看表结构,根27上的表一样,说明172.1824.107主机上同步了172.18.24.27主机
MariaDB [test]> DESC students;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | NULL | |
| name | char(30) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('F','M') | YES | | NULL | | +--------+---------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
如上,我们可以确定,172.18.24.107主机上的数据库同步了172.18.24.27上的数据库
然后,我们在172.1824.107主机上插入数据如下:
MariaDB [test]> INSERT INTO students VALUE (1,'xiaoming',22,'M');
Query OK, 1 row affected (0.01 sec)
MariaDB [test]> SELECT * FROM students;
+----+----------+------+--------+
| id | name | age | gender | +----+----------+------+--------+
| 1 | xiaoming | 22 | M | +----+----------+------+--------+
1 row in set (0.00 sec)
在172.18.24.27主机上查看是否同步了172.1824.107主机上的插入更改:
MariaDB [test]> select * from students;
+----+----------+------+--------+
| id | name | age | gender | +----+----------+------+--------+
| 1 | xiaoming | 22 | M | +----+----------+------+--------+
1 row in set (0.00 sec)
如上,我们可以确定,172.18.24.27主机上的数据库同步了172.18.24.107上的数据库。
由此,我们实现了两台主机的互相复制,互为主从!
总结:主主复制是在主从复制上衍生过来的,自己是别人的从也同时是别人的主,做为主数据库,需要有写的权限,并且二进制日志需要开启,从数据库只需要有读的权限,但是我们这里是主主,即两台主机都需要开启读写权限。在生产中,我们需要不停机的状态下中途搭建主主复制模式,那么之前的数据就需要全量备份到从数据库中,然后再开启复制。