CentOS7操作系统下实现mysql数据库的主主复制

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=1auto_increment_increment=2[root@node1 ~]#systemctl start mariadb.service   #开启服务[root@node1 ~]#mysqlMariaDB [(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 changedMariaDB [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上的数据库。

由此，我们实现了两台主机的互相复制，互为主从！

总结：主主复制是在主从复制上衍生过来的，自己是别人的从也同时是别人的主，做为主数据库，需要有写的权限，并且二进制日志需要开启，从数据库只需要有读的权限，但是我们这里是主主，即两台主机都需要开启读写权限。在生产中，我们需要不停机的状态下中途搭建主主复制模式，那么之前的数据就需要全量备份到从数据库中，然后再开启复制。