mysql双主同步

MySQL8的双主同步（也称为双主复制或双向复制）是指两个MySQL服务器相互之间进行数据复制，从而确保数据的实时同步和数据冗余备份。

服务器说明

DB-MASTER(服务器A):172.165.32.132

DB-SLAVE(服务器B):172.165.32.182

DB VIP(Keepalived) :172.165.32.192

登录账号信息：

root/Cpcnet123$%^

TychonicUser/Cpcnet123$%^

登录命令：

mysql -uroot -p'Cpcnet123$%^'

配置同步步骤

1、在服务器A，my.cnf中配置同步信息

Mysql8位置在/etc/mysql/my.cnf

[mysqld]
log-bin=mysql-bin                      #开启主从binlog（二进制日志），主库必须开启
binlog_format=mixed                    #指定二进制日志格式为mixed，即在语句和行两种模式之间自动切换
server-id=1                            #配置server-id（唯一）标识主机，必须与从库不一致
relay-log=relay-bin                    #指定中继日志文件的名称为relay-bin，用于在主从复制中传输数据
relay-log-index=slave-relay-bin.index  #指定中继日志索引文件的名称，用于记录中继日志文件的位置。
auto_increment_increment=1             #指定自增长ID的增量为1，用于在主从复制中避免ID冲突。
auto_increment_offset=1                #指定自增长ID的起始值为1，用于在主从复制中避免ID冲突。
expire_logs_days=30

2、重启mysql

sudo systemctl restart mysql

3、使用root账号登录mysql服务器A

mysql -uroot -p'Cpcnet123$%^'

4、进入mysql，创建同步用户

CREATE USER 'replica'@'%' IDENTIFIED BY 'SE$rft6y';

GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';

FLUSH PRIVILEGES;

5、在服务器B，my.cnf中配置同步信息

Mysql8位置在/etc/mysql/my.cnf

[mysqld]
log-bin=mysql-bin                            #开启主从binlog（二进制日志），主库必须开启
binlog_format=mixed                          #指定二进制日志格式为mixed，即在语句和行两种模式之间自动切换
server-id=2                                  #配置server-id（唯一）标识主机，必须与从库不一致
relay-log=relay-bin                          #指定中继日志文件的名称为relay-bin，用于在主从复制中传输数据
relay-log-index=slave-relay-bin.index        #指定中继日志索引文件的名称，用于记录中继日志文件的位置。
auto_increment_increment=2                   #指定自增长ID的增量为2，用于在主从复制中避免ID冲突。
auto_increment_offset=2                      #指定自增长ID的起始值为2，用于在主从复制中避免ID冲突。
expire_logs_days=30

6、重启mysql

sudo systemctl restart mysql

7、使用root账号登录mysql服务器B

mysql -uroot -p'Cpcnet123$%^'

8、进入mysql，创建同步用户

CREATE USER 'replica'@'%' IDENTIFIED BY 'SE$rft6y';

GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';

FLUSH PRIVILEGES;

9、在服务器B执行命令

show master status;

使用查询出来的log file名称（mysql-bin.000055）和pos（90427）配置服务器A的同步

10、在服务器A用root账号登录mysql执行以下命令

stop slave;

reset slave;

CHANGE MASTER TO
MASTER_HOST='172.165.32.182',
MASTER_USER='replica',
MASTER_PASSWORD='SE$rft6y',
MASTER_LOG_FILE='mysql-bin.000055',
MASTER_LOG_POS=90427,
get_master_public_key=1;


start slave;

show slave status\G;

如果配置正确，状态应该显示Slave_IO_Running和Slave_SQL_Running均为Yes。

11、在服务器A使用root账号登录mysql执行命令

show master status;

使用查询出来的log file名称（mysql-bin.000050）和pos（94845）配置服务器B的同步

12、在服务器B使用root账号登录mysql执行以下命令

stop slave;

reset slave;

CHANGE MASTER TO
MASTER_HOST='172.165.32.132',
MASTER_USER='replica',
MASTER_PASSWORD='SE$rft6y',
MASTER_LOG_FILE='mysql-bin.000050',
MASTER_LOG_POS=94845,
get_master_public_key=1;


start slave;

show slave status\G;

如果配置正确，状态应该显示Slave_IO_Running和Slave_SQL_Running均为Yes。

测试：

在master-A主机上创建同步数据库

CREATE DATABASE IF NOT EXISTS abcd DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

use abcd;

create table t1(id int,name varchar(20));

在master-B上查看是否同步了master-A的变化

show databases;

use abcd;

show tables;

在master-B上插入一条数据查看master-A上是否同步更新

insert into t1 (id,name) values (5,'王五');

在mster-A上查询在master-B上插入的数据

Select * from t1;

测试通过删除测试库abcd;

DROP DATABASE IF EXISTS abcd;

在MASTER-A导出完整数据

mysqldump -u root-p miind_sdwan_1_5 > /home/cpcnet/backup.sql

在MASTER-A删除miind_sdwan_1_5;

DROP DATABASE IF EXISTS miind_sdwan_1_5;

MASTER-B新建miind_sdwan_1_5;

CREATE DATABASE IF NOT EXISTS miind_sdwan_1_5;

在MASTER-A导入数据

source /home/cpcnet/full_backup.sql;

排查异常

select * from performance_schema.replication_applier_status_by_worker\G;