MySQL环形复制

原创

那海蓝蓝 2021-04-09 11:15:17 博主文章分类：MySQL复制 ©著作权

文章标签 java 文章分类 Java 后端开发

©著作权归作者所有：来自51CTO博客作者那海蓝蓝的原创作品，请联系作者获取转载授权，否则将追究法律责任

环境：CentOS6.5（三个），MySQL5.7.18
master1：192.168.8.57
master2：192.168.8.58
master3：192.168.8.59

1、配置基本环境
安装操作系统，安装MySQL数据库，关闭防火墙和selinux
2、修改MySQL配置文件
master1

点击(此处)折叠或打开

[root@master2 ~]# cat /etc/my.cnf
[mysqld]
user=mysql
datadir=/mysql/data
default-storage-engine=InnoDB
#skip-grant-tables
socket=/tmp/mysqld.sock
skip_ssl
log-error=/mysql/data/mysql-err.log
server-id = 2
log-bin = /mysql/data/mysql-bin
relay-log = relay-log
read-only = false
log-slave-updates
auto_increment_offset=2
auto_increment_increment=3
[client]
default-character-set=utf8
socket=/tmp/mysqld.sock
[mysql]
socket=/tmp/mysqld.sock
default-character-set=utf8

master2

点击(此处)折叠或打开

[root@master2 ~]# cat /etc/my.cnf
[mysqld]
user=mysql
datadir=/mysql/data
default-storage-engine=InnoDB
#skip-grant-tables
socket=/tmp/mysqld.sock
skip_ssl
log-error=/mysql/data/mysql-err.log
server-id = 2
log-bin = /mysql/data/mysql-bin
relay-log = relay-log
read-only = false
log-slave-updates
auto_increment_offset=2
auto_increment_increment=3
[client]
default-character-set=utf8
socket=/tmp/mysqld.sock
[mysql]
socket=/tmp/mysqld.sock
default-character-set=utf8

master3

点击(此处)折叠或打开

[root@master3 ~]# cat /etc/my.cnf
[mysqld]
user=mysql
datadir=/mysql/data
default-storage-engine=InnoDB
#skip-grant-tables
socket=/tmp/mysqld.sock
skip_ssl
log-error=/mysql/data/mysql-err.log
server-id = 3
log-bin = /mysql/data/mysql-bin
relay-log = relay-log
read-only = false
log-slave-updates
auto_increment_offset=2
auto_increment_increment=3
[client]
default-character-set=utf8
socket=/tmp/mysqld.sock
[mysql]
socket=/tmp/mysqld.sock
default-character-set=utf8

注意：此次配置的是环形同步，所以需要处理主键自增问题，需要注意这两个参数
auto_increment_increment：自增值
auto_increment_offset：漂移值,也就是步长
3、在三个节点添加复制用户，并授权
master1：
grant replication slave on *.* to 'repl'@'192.168.8.58' identified by 'mysql';
master2：
grant replication slave on *.* to 'repl'@'192.168.8.59' identified by 'mysql';
master3：
grant replication slave on *.* to 'repl'@'192.168.8.57' identified by 'mysql';
4、初始化三个节点的数据
master1：
flush tables with read lock;
mysqldump -uroot -p --database test > test.sql
master2和master3同步数据
unlock tables;
5、查看三个节点的二进制日志名和偏量值，改操作的目的是从库启动之后，从这个点开始恢复数据
mysql> show master status;
+----------------------+------------+--------------------+----------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+------------+--------------------+----------------------+-------------------+
| mysql-bin.000010| 1675384 | | | |
+----------------------+------------+--------------------+----------------------+-------------------+
master1：
change master to
master_host='192.168.8.59',
master_port=3306,
master_user='repl',
master_password='mysql',
master_log_file='mysql-bin.000012',
master_log_pos=110251;
master2：
change master to
master_host='192.168.8.57',
master_port=3306,
master_user='repl',
master_password='mysql',
master_log_file='mysql-bin.000009',
master_log_pos=13142983;
master3：
change master to
master_host='192.168.8.58',
master_port=3306,
master_user='repl',
master_password='mysql',
master_log_file='mysql-bin.000011',
master_log_pos=92962;
6、启动复制进程
start slave
7、查看三个节点的复制进程状态：