搭建双主双从的MySQL主从同步复制

搭建MySQL双主双从

搭建MySQL双主双从

准备Master1(3306)、Master2(3307)、Slave1(3308)、Slave2(3309)四台MySQL。

Master1与Slave1是主从，Master2与Slave2是主从，Master1与Master2互为主从。

双主MySQL配置

配置Master1，修改配置文件vim /etc/my.cnf

#开启二进制日志
log-bin=mysql-bin
#设置服务id
server-id=1
# 设置不要复制的数据库
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
#设置需要复制的数据库
binlog-do-db=demo
binlog-do-db=mydb
#设置logbin格式;可选值：STATEMENT、ROW、MIXED
binlog_format=STATEMENT
# 作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates
# 自增长字段从哪个数开始
auto-increment-offset=1
# 自增长字段每次递增的量
auto-increment-increment=2

配置Master2，修改配置文件vim /etc/my.cnf

#开启二进制日志
log-bin=mysql-bin
#设置服务id
server-id=2
# 设置不要复制的数据库
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
#设置需要复制的数据库
binlog-do-db=demo
binlog-do-db=mydb
#设置logbin格式;可选值：STATEMENT、ROW、MIXED
binlog_format=STATEMENT
# 作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates
# 自增长字段从哪个数开始
auto-increment-offset=2
# 自增长字段每次递增的量
auto-increment-increment=2

双从MySQL配置

配置Slave1，修改配置文件vim /etc/my.cnf

#开启二进制日志
log-bin=mysql-bin
#设置服务id
server-id=3
#启用中继日志
relay-log=mysql-relay

配置Slave2，修改配置文件vim /etc/my.cnf

#开启二进制日志
log-bin=mysql-bin
#设置服务id
server-id=4
#启用中继日志
relay-log=mysql-relay

重启双主、双从MySQL服务

# 启动
systemctl start mysql

# 停止
systemctl stop mysql

# 重启
systemctl restart mysql

创建授权账号

在Master1节点的MySQL上建立帐户并授权，用于登录主节点，进行数据同步复制

# 登录主库
mysql -u root -P 3306 -p

# 授权主备复制专用账号
mysql> CREATE USER 'slave1'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER USER 'slave1'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.01 sec)

# 刷新权限
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql>

在Master2节点的MySQL上建立帐户并授权，用于登录主节点，进行数据同步复制

# 登录主库
mysql -u root -P 3307 -h IP -p

# 授权主备复制专用账号
mysql> CREATE USER 'slave2'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave2'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER USER 'slave2'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.00 sec)

# 刷新权限
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql>

查询Master1的状态：show master status;，记录下File和Position的值

mysql> show master status;
+------------------+----------+--------------+------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB             | Executed_Gtid_Set |
+------------------+----------+--------------+------------------------------+-------------------+
| mysql-bin.000066 |     1001 | demo,mydb    | mysql,performance_schema,sys |                   |
+------------------+----------+--------------+------------------------------+-------------------+
1 row in set (0.00 sec)

查询Master2的状态：show master status;，分别记录下File和Position的值

mysql> show master status;
+------------------+----------+--------------+------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB             | Executed_Gtid_Set |
+------------------+----------+--------------+------------------------------+-------------------+
| mysql-bin.000005 |     1001 | demo,mydb    | mysql,performance_schema,sys |                   |
+------------------+----------+--------------+------------------------------+-------------------+
1 row in set (0.00 sec)

设置从库向主库同步/复制数据

设置Slava1复制Master1，Slava2复制Master2

操作Slava1

CHANGE MASTER TO MASTER_HOST='IP',
MASTER_USER='slave1',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000066',
MASTER_LOG_POS=1001;

操作Slava2

CHANGE MASTER TO MASTER_HOST='IP',
MASTER_USER='slave2',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=1001,
MASTER_PORT=3307;

分别启动两从MySQL服务的复制功能，查看服务器状态；Slave_IO_Runing和Slave_SQL_Runing都为Yes说明同步成功

start slave;

查看双从服务器状态

show slave status;

show slave status\G;

Slava1

Slava2

配置双主互相复制

Master1复制Master2，Master2复制Master1

操作Master1

CHANGE MASTER TO MASTER_HOST='IP',
MASTER_USER='slave2',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=1001,
MASTER_PORT=3307;

操作Master2

CHANGE MASTER TO MASTER_HOST='IP',
MASTER_USER='slave1',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000066',
MASTER_LOG_POS=1001;

分别启动两主MySQL服务的复制功能，查看从服务器状态：Slave_IO_Runing和Slave_SQL_Runing都为Yes说明同步成功

start slave;

查看从服务器状态

show slave status;

show slave status\G;

Master1

Master2

出现异常：

ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

解决方案：

stop slave;
start slave;

or

reset slave;
start slave;

测试双主双从的同步复制

注意：在测试双主双从复制时，一定要确保每个节点MySQL的Slave_IO_Runing和Slave_SQL_Runing都为Yes。

在Master1节点新建库、新建表、插入数据

mysql>  CREATE DATABASE mydb;
Query OK, 1 row affected (0.01 sec)

mysql> use mydb;
Database changed

mysql> CREATE TABLE mytb(id INT,name VARCHAR(30));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO mytb VALUES(1,'mycat');
Query OK, 1 row affected (0.01 sec)

mysql>