MySQL复制之一主二从配置

1.环境#

主机：vhost4, vhost5, vhost6
系统：CentOS7
vhost4: MySQL.slave
vhost5: MySQL.master
vhost6: MySQL.slave
MySQL: v5.7.36

2.配置#

2.1 （一个）master配置#

2.1.1 my.cnf配置#

cat /etc/my.cnf

1
2
3
4
5
6
7
8
9
10
11
12
...略

[mysqld]
# 开启binary log
log-bin=mysql-bin
# 设置server_id
server-id=1
# For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, you should use innodb_flush_log_at_trx_commit=1 andsync_binlog=1 in the source's my.cnf file
innodb_flush_log_at_trx_commit=1
sync_binlog=1

...略

2.1.2 确保MySQL的 skip_networking 变量是OFF的#

1
SHOW VARIABLES LIKE '%skip_networking%';

2.1.3 重启MySQL服务#

1
systemctl restart mysqld

2.1.4 在master上创建用于复制的用户账号#

1
2
CREATE USER 'repl'@'%' IDENTIFIED BY '密码';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

2.1.5 获取master上binary log的坐标#

1
2
3
4
5
6
7
8
mysql> FLUSH TABLES WITH READ LOCK; # 锁定只读
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000354 |  4649036 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

2.1.6 同步现有数据，从master上导出数据#

1
mysqldump -uroot -p --all-databases --master-data > dbdump.db

2.1.7 解除锁定#

在master上执行UNLOCK TABLES;（对应上面的FLUSH TABLES WITH READ LOCK;）

2.2 （每个）slave配置#

2.2.1 replica设置（一般不需要开启binary log)#

cat /etc/my.cnf
两个slave的server-id分别配置为2和3

1
2
3
4
5
6
...略

[mysqld]
server-id=2

...略

2.2.2 在replica上配置master#

MASTER_LOG_FILE和MASTER_LOG_POST参数为master上binary log的坐标

1
2
3
4
5
6
CHANGE MASTER TO
MASTER_HOST='vhost5',
MASTER_USER='repl',
MASTER_PASSWORD='密码',
MASTER_LOG_FILE='mysql-bin.000354',
MASTER_LOG_POS=4649036;

2.2.3 启动slave开始复制#

1.导入master的数据
2.start slave

3.资料#