「MySQL」- 双主复制

  FROM JENKINS AUTOMATION

更新日期:2019年08月09日

系统环境

操作系统:CentOS Linux release 7.4.1708 (Core)

软件版本:MySQL 5.6.45

网络信息:Server01: 10.10.50.115 / Server02: 10.10.50.116

安装服务

(跳过,详细参考「MySQL 5.6 (CentOS 7)」一文)

配置服务

# On Server 01

(0)创建必要的目录:

#!/bin/sh

mkdir -pv /var/log/mysql/
chown mysql.mysql /var/log/mysql/

(1)修改/etc/mysql/my.cnf文件:

[mysqld]
server_id           = 1
auto-increment-offset = 1
auto-increment-increment = 2

log_bin             = /var/log/mysql/mysql-bin.log
log_bin_index       = /var/log/mysql/mysql-bin.log.index
relay_log           = /var/log/mysql/mysql-relay-bin
relay_log_index     = /var/log/mysql/mysql-relay-bin.index
expire_logs_days    = 10
max_binlog_size     = 100M
log_slave_updates   = 1

# 依据情况修改:
# bind-address    = x.x.x.x

(2)重新启动服务:

#!/bin/sh

systemctl restart mysqld.service

# On Server 02

(0)创建必要的目录:

#!/bin/sh

mkdir -pv /var/log/mysql/
chown mysql.mysql /var/log/mysql/

(1)修改/etc/mysql/my.cnf文件:

[mysqld]
server_id           = 2
auto-increment-offset = 2
auto-increment-increment = 2

log_bin             = /var/log/mysql/mysql-bin.log
log_bin_index       = /var/log/mysql/mysql-bin.log.index
relay_log           = /var/log/mysql/mysql-relay-bin
relay_log_index     = /var/log/mysql/mysql-relay-bin.index
expire_logs_days    = 10
max_binlog_size     = 100M
log_slave_updates   = 1

# 依据情况修改:
# bind-address    = x.x.x.x

(2)重新启动服务:

#!/bin/sh

systemctl restart mysqld.service

创建用户

分别在Server01与Server02上创建用于复制的用户。创建用户之后,必要忘记验证。

# On Server 01

-- SQL
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED BY 'BU3CGK6x';

# On Server 02

-- SQL
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED BY 'BU3CGK6x';

启用复制

分别在Server01与Server02上启用复制:

# On Server 01

-- SQL --

STOP SLAVE;

CHANGE MASTER TO master_host='10.10.50.116', master_port=3306, 
	master_user='replication', master_password='BU3CGK6x', 
	master_log_file='mysql-bin.000001', master_log_pos=0;

START SLAVE;    

# On Server 02

-- SQL --

STOP SLAVE;

CHANGE MASTER TO master_host='10.10.50.115', master_port=3306, 
	master_user='replication', master_password='BU3CGK6x', 
	master_log_file='mysql-bin.000001', master_log_pos=0;

START SLAVE;    

复制验证

这里跳过验证。

验证复制是否生效的思路:

在Server01上创建库及表,然后在Server02上查看。
在Server02上创建库及表,然后在Server01上查看。

如果没有问题,就是没有问题。如果没有同步,可以在数据库上执行SHOW SLAVE STATUS \G语句,来查看同步状态和同步错误信息(如果有)。

查看状态

使用SHOW MASTER STATUS \G语句查看自己的状态;使用SHOW SLAVE STATUS \G语句来查看自己作为从库的状态;

参考文献




文章摘要:Database_Management_System:1.MySQL_and_MariaDB:7.High_availability:Replication:Master-Master_Replication

原文链接:「MySQL」- 双主复制