MySQL 8.x主从搭建

一.准备两台以上的数据库

数据库1（主服务器)：172.16.20.20
数据库2（从服务器）：172.16.30.20

二.配置主服务器

2.1 配置

# Replication Master Server (default)
# binary logging is required for replication
log-bin          = mysql-bin

# binary logging format - ROW recommended
binlog_format    = ROW

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id        = 1

#binlog-do-db    = isosign     # 指定要复制的数据库（可选，不指定则复制所有）
binlog-ignore-db = mysql  # 设置要忽略的数据库（可选）
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys

# 开启GTID（全局事务标识符，推荐）
gtid-mode        = ON
enforce-gtid-consistency = ON

# 设置二进制日志过期时间（天）
expire_logs_days = 7

# 每次事务提交都同步日志到磁盘
sync_binlog      = 1

# InnoDB设置
innodb_flush_log_at_trx_commit = 1

2.2 增加用户并且授权

-- 登录MySQL
mysql -u root -p

-- 创建复制用户
CREATE USER 'sync'@'192.168.1.101' IDENTIFIED WITH mysql_native_password BY 'StrongPassword123!';

-- 授予复制权限
GRANT REPLICATION SLAVE ON *.* TO 'sync'@'192.168.1.101';

-- 刷新权限
FLUSH PRIVILEGES;

2.3 查看主服务器的状态和位移

PS:因为要停机，反正没有数据进入，所以以下面重启的位移为准

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+-------------------------------------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set                        |
+------------------+----------+--------------+-------------------------------------------------+------------------------------------------+
| mysql-bin.000001 |     1396 |              | mysql,information_schema,performance_schema,sys | b5774f98-6f8e-11f0-a67f-002095d7fde1:1-5 |
+------------------+----------+--------------+-------------------------------------------------+------------------------------------------+
1 row in set (0.00 sec)

三.配置从服务器

3.1 配置

# binary logging - not required for slaves, but recommended
log-bin=mysql-bin

#binlog-do-db=isosign     #待同步的数据库
binlog-ignore-db=mysql    #不同步的数据

[mysqld]
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
server-id = 2

# enable relay logs
relay-log = mysql-relay-bin

# enable read-only mode on the replica (optional, protects against accidental writes)
read_only = 1
# also prevent privileged users from writing (recommended for GTID replication)
super_read_only = 1

# enable GTID (Global Transaction Identifier) for replication
gtid-mode = ON
enforce-gtid-consistency = ON

# log all replicated transactions on the replica server
log-slave-updates = 1

# skip specific replication errors (use with caution)
# slave-skip-errors = 1062,1053,1146

四.同步数据并且配置服务

4.1 同步主服务器数据文件至从服务器

[root@v1 srv]# systemctl stop mysqld
[root@v1 srv]# cp -r mysql mysql-bak2
[root@v1 srv]# systemctl start mysqld
[root@v1 srv]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.18 Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      155 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> exit
Bye
[root@v1 srv]# tar czvf mysql-data.tar.gz mysql-bak2
[root@v1 srv]# scp mysql-data.tar.gz 172.16.30.20:/srv/

4.2 恢复数据至从服务器，并且指定主服务器

[root@v2 srv]# tar xzvf mysql-data.tar.gz
[root@v2 srv]# mv mysql-bak2 mysql
[root@v2 srv]# nano -w /srv/mysql/auto.cnf #修改uuid与主服务器不同
[root@v2 srv]# chown -R mysql.mysql mysql
[root@v2 srv]# rm -rf /srv/mysql/data/auto.cnf # 防止id相同

4.3 第三步中配置的从服务器配置先注释掉，然后启动

[root@v2 srv]# systemctl start mysqld
[root@v2 mysql]# mysql -u root -p
-- 停止从服务器（如果之前有配置）
STOP SLAVE;

-- 设置 session 级别的 GTID_NEXT
SET SESSION GTID_NEXT='b5774f98-6f8e-11f0-a67f-002095d7fde1:7';

-- 执行一个空事务跳过它
BEGIN; COMMIT;

-- 恢复为自动
SET SESSION GTID_NEXT='AUTOMATIC';

-- 配置主服务器信息
CHANGE MASTER TO
    MASTER_HOST='192.168.1.100',
    MASTER_USER='replica_user',
    MASTER_PASSWORD='StrongPassword123!',
    MASTER_LOG_FILE='mysql-bin.000001',  -- 使用主服务器的File值
    MASTER_LOG_POS=154;                  -- 使用主服务器的Position值

-- 启动从服务器
START SLAVE;

-- 查看从服务器状态
SHOW SLAVE STATUS\G