环境：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、查看三个节点的复制进程状态：

点击(此处)折叠或打开

mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.8.59
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000013
Read_Master_Log_Pos: 1489066
Relay_Log_File: relay-log.000006
Relay_Log_Pos: 1482198
Relay_Master_Log_File: mysql-bin.000013
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1489066
Relay_Log_Space: 1712245
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
Master_UUID: c79685e5-1ce8-11e8-869c-080027656007
Master_Info_File: /mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:

8、分别在三个节点进行增删改查，创建删除对象操作，测试同步全部正常，到此完毕。

ITPub博客

MySQL环形复制

最新文章