MariaDB 双主复制的配置

环境

Master1/Master2	系统	IP	数据库版本
Master1	CentOS6.7	10.10.3.211	mariadb-10.1.19
Master2	CentOS6.7	10.10.3.212	mariadb-10.1.19

 

 

 

一、Master1的配置

（1）修改Master1配置文件/etc/my.cnf，修改如下：

log-bin = /usr/local/mysqllogs/binlogs/master1-bin        #启用二进制日志
relay-log = /usr/local/mysqllogs/relaylogs/relay-bin        #启用中继日志
auto-increment-offset = 1        #自增起始值
auto-increment-increment = 2        #自增步长
server-id = 1        

重启数据库服务

（2）创建有复制权限的用户，命令如下

CREATE USER 'luccy'@'10.10.3.212' IDENTIFIED BY '123456';
REVOKE ALL PRIVILEGES ,GRANT OPTION FROM 'luccy'@'10.10.3.212';
GRANT RELOAD,LOCK TABLES, REPLICATION CLIENT ,REPLICATION SLAVE ON *.* TO 'luccy'@'10.10.3.212';
FLUSH PRIVILEGES;

二、Master2的配置

（1）修改Master1配置文件/etc/my.cnf，修改如下：

log-bin = /usr/local/mysqllogs/binlogs/master2-bin
relay-log = /usr/local/mysqllogs/relaylogs/relay-bin
auto-increment-offset = 2
auto-increment-increment = 2
server-id = 2

重启数据库服务

（2）创建有复制权限的用户，命令如下

CREATE USER 'jerry'@'10.10.3.211' IDENTIFIED BY '123456';
REVOKE ALL PRIVILEGES ,GRANT OPTION FROM 'jerry'@'10.10.3.211';
GRANT RELOAD,LOCK TABLES, REPLICATION CLIENT ,REPLICATION SLAVE ON *.* TO 'jerry'@'10.10.3.211';
FLUSH PRIVILEGES;

三、查看Master1和Master2的状态

SHOW MASTER STATUS;

Master1

Master2

四、互相指向

CHANGE MASTER TO MASTER_USER='jerry',MASTER_HOST='10.10.3.212',MASTER_PASSWORD='123456',MASTER_LOG_FILE='master2-bin.000002',MASTER_LOG_POS=982;

CHANGE MASTER TO MASTER_USER='luccy',MASTER_HOST='10.10.3.211',MASTER_PASSWORD='123456',MASTER_LOG_FILE='master1-bin.000001',MASTER_LOG_POS=1473;

五、启动Master1和Master2服务器复制进程

START SLAVE;

Master1

Master2

六、测试