MySql8.4单节点、主从、双主的构建方法

发表于 分类于 ， 置顶 精品
阅读次数： 0 本文字数： 7.4k 阅读时长 ≈ 27 分钟

摘要

常见面试题

0.从 8.0 到 8.4，哪些东西变了

8.4 是 8.x 系列的第二个 LTS。它把 8.0 里那些「已废弃很久」的东西真正删掉了，所以按 8.0 的笔记照抄会直接报语法错误。动手前先看这一节。

主从 SQL 语句全部改名（旧名已删除，不是别名）

8.0 写法 8.4 写法
SHOW MASTER STATUS SHOW BINARY LOG STATUS
SHOW MASTER LOGS SHOW BINARY LOGS
PURGE MASTER LOGS PURGE BINARY LOGS
RESET MASTER RESET BINARY LOGS AND GTIDS
CHANGE MASTER TO CHANGE REPLICATION SOURCE TO
START SLAVE / STOP SLAVE START REPLICA / STOP REPLICA
RESET SLAVE RESET REPLICA
SHOW SLAVE STATUS SHOW REPLICA STATUS
SHOW SLAVE HOSTS SHOW REPLICAS

CHANGE 的选项名也删了

只改语句名不够，MASTER_HOST 这类选项在 8.4 同样不认：

MASTER_HOST → SOURCE_HOST，MASTER_PORT → SOURCE_PORT，MASTER_USER → SOURCE_USER，MASTER_PASSWORD → SOURCE_PASSWORD，MASTER_LOG_FILE → SOURCE_LOG_FILE，MASTER_LOG_POS → SOURCE_LOG_POS，MASTER_AUTO_POSITION → SOURCE_AUTO_POSITION，MASTER_CONNECT_RETRY → SOURCE_CONNECT_RETRY，MASTER_RETRY_COUNT → SOURCE_RETRY_COUNT。

SHOW REPLICA STATUS 的列名跟着变

排查主从时看的列不再是 Slave_IO_Running：

Slave_IO_State → Replica_IO_State，Master_Host → Source_Host，Master_Log_File → Source_Log_File，Read_Master_Log_Pos → Read_Source_Log_Pos，Relay_Master_Log_File → Relay_Source_Log_File，Slave_IO_Running → Replica_IO_Running，Slave_SQL_Running → Replica_SQL_Running，Seconds_Behind_Master → Seconds_Behind_Source。

配置项与默认值

项 变化
default_authentication_plugin 8.4.0 已删除，配了服务起不来，改用 authentication_policy
mysql_native_password 默认关闭，要用得显式 mysql_native_password=ON；9.0 已彻底删除
expire_logs_days 8.2 已删除，用 binlog_expire_logs_seconds（默认 2592000，即 30 天）
log_slave_updates 改名 log_replica_updates，默认 ON
master_info_repository、relay_log_info_repository、master-info-file、relay-log-info-file 8.3 已删除，复制元数据只存表（crash-safe）
innodb_log_file_size、innodb_log_files_in_group 已废弃，用 innodb_redo_log_capacity（默认 100MB）
binlog_format 8.0.34 起废弃，默认就是 ROW，新系统不要再配
replica_parallel_workers 默认 4（8.0 是 0），从库默认就是多线程复制
rpl_semi_sync_master_* / semisync_master.so 改用 rpl_semi_sync_source_* / semisync_source.so，从库侧是 replica 那一套

InnoDB 还有一批默认值在 8.4 被调好了，不用再手动配：innodb_log_buffer_size 16M→64M、innodb_io_capacity 200→10000、innodb_flush_method 改成 O_DIRECT（支持时）、innodb_adaptive_hash_index 改成 OFF、innodb_change_buffering 改成 none、innodb_buffer_pool_instances 改成按内存和 CPU 自动算。

警告⚠️
innodb_buffer_pool_size 默认仍是 128M，8.4 不会自动按机器内存放大。独占机器可以开 innodb_dedicated_server=ON，让 InnoDB 按物理内存算 buffer pool、按 CPU 数算 redo 容量。

高可用方案也得换

MySql单节点、主从、双主的构建方法 文末提到的 MHA、MMM 都是 Perl 老工具，内部发的就是 `CHANGE MASTER TO`、`SHOW SLAVE STATUS`，**在 8.4 上跑不起来**（详见 MySql-MHA的构建方法 里的说明）。8.4 要做自动选主，用官方的 **InnoDB Cluster**（Group Replication + MySQL Shell + MySQL Router），本文最后一节给出选型。

1.单节点

安装

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
# 1.下载mysql，从mysql官网下载：https://dev.mysql.com/downloads/mysql/8.4.html
# 注意8.4的通用二进制包是glibc2.28，比8.0的glibc2.12要新，老系统(如CentOS7)装不上
wget https://cdn.mysql.com/Downloads/MySQL-8.4/mysql-8.4.11-linux-glibc2.28-x86_64.tar.xz

# 2.解压
tar -Jxvf mysql-8.4.11-linux-glibc2.28-x86_64.tar.xz

# 3.重命名
mv mysql-8.4.11-linux-glibc2.28-x86_64 mysql8.4

# 4.切换到root
sudo su

# 5.创建mysql用户和mysql组
groupadd mysql
useradd -r -g mysql -s /bin/false mysql

# 6.创建数据目录并赋予权限
cd mysql8.4
mkdir -p datas/mysql
chown -R mysql:mysql datas
chmod -R 750 datas

# 7.设置环境变量
vim /etc/bashrc
export PATH=$PATH:/usr/local/soft/mysql8.4/bin

# 8.创建配置文件
touch my.cnf

警告⚠️
默认情况下，mysql按照下面的文件顺序加载配置，后面的配置会覆盖前面的配置
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
建议将配置文件创建到/etc/my.cnf，这样所有用户都可以使用，特殊配置可以创建在当前用户的~/.my.cnf中。
本文以下内容除特殊指定配置文件路径外，都是基于/etc/my.cnf的默认配置文件

配置

配置段说明

  • [mysql] 和 [client] 都是针对客户端的配置，后面的配置会覆盖前面的相同配置
  • [mysql] 只针对 mysql 命令，可以通过mysql --help查看配置项及其缺省值
  • [client] 针对 mysql、mysqldump、mysqlimport、mysqladmin, 等等，所有的客户端命令
  • [mysqld] 只是针对服务器端的配置，针对 mysqld 命令，可以通过mysqld --verbose --help查看配置项及其缺省值

  • 配置文件示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
vim my.cnf

[mysql]
# 默认字符集，utf8mb4每个中文占四个字节，可以存入表情符号
# varchar(255)所表示的单位是字符，而一个汉字一个字母都是一字符。所以这里可以存储255个汉字或者255个字母。
# varchar的存储上限是65535字节，所以utf8mb4的varchar(16383)是上限(65535/4)
default-character-set=utf8mb4
# mysqlimport和 load data local infile 导入文件开关，mysql和mysqld都要开通，默认关闭
local_infile=ON

[client]
# 部分 client 不支持该属性，比如 mysqlsh
# default-character-set=utf8mb4
port       = 3306
# 本机socket保存路径，执行命令的用户要对该路径有访问权限
socket     = /tmp/mysql.sock

[mysqld]
# 关闭mysql服务端对客户端的DNS解析，可以加快连接效率，mysql主机查询DNS很慢或是有很多客户端主机时会导致连接很慢时可以配置这个选项
skip-name-resolve

# 使用MySQL outfile导出文件，mysqldump
# 限制mysql不允许导出，默认值
# secure_file_prive=null
# 限制mysql的导出只能发生在默认的/path/目录下
# secure_file_priv=/path/
# 不对mysql的导出做限制，可以导出到任意目录
secure_file_priv=""

# mysqlimport和 load data local infile 导入文件开关，mysql和mysqld都要开通，默认关闭
local_infile=ON

# 【8.4变化】default_authentication_plugin 在8.4.0已被删除，配置了会导致mysql启动失败
# 8.4改用 authentication_policy 管理认证因子，默认值就是 '*,,'，即第一因子必填且默认用 caching_sha2_password
# 所以下面这行其实可以不配，写出来只是为了说明默认行为
authentication_policy='*,,'

# 【8.4变化】mysql_native_password 默认已关闭，只有老客户端/老驱动连不上时才临时打开
# 注意9.0已经彻底删除该插件，不要把它当长期方案
# mysql_native_password=ON

# 端口号
port       = 3306
# 主从或集群中要保证唯一性
server-id  = 1001
# 执行用户
user       = mysql
# 本机socket保存路径
socket     = /tmp/mysql.sock
# 安装目录
basedir    = /usr/local/soft/mysql8.4
# 数据存放目录，下面的路径都是基于这个目录的
datadir    = /usr/local/soft/mysql8.4/datas/mysql

# 开启binlog日志，定义日志存储路径
log-bin    = mysql-bin
# 【8.4变化】binlog_format 从8.0.34起已废弃，8.4默认就是ROW，未来只保留ROW，所以这里不再配置
# binlog-format = ROW
# 【8.4变化】binlog过期时间，单位秒，8.4默认2592000(30天)，这里设置为10天
# 老参数 expire_logs_days 在8.2已被删除，配了会启动失败
binlog_expire_logs_seconds = 864000
# 1：每次写入都会与磁盘同步，会影响性能，0：事务提交时mysql不做磁盘操作，由系统决定
sync-binlog=0

# innodb数据存储目录
innodb_data_home_dir      =./
# 【8.4变化】redo日志固定放在 datadir 下的 #innodb_redo 目录，共32个文件，由容量参数控制
innodb_log_group_home_dir =./
#日志及进程数据的存放目录
log-error =mysql.log
pid-file  =mysql.pid

# 服务端使用的字符集，默认值就是utf8mb4
character-set-server=utf8mb4
# 不区分表名称大小写，注意该参数必须在数据库初始化之前设置，初始化之后不能再改
lower_case_table_names=1
# 默认就是1，是否自动提交，如果有事务，则跟着事务提交
autocommit =1

# 慢查询分析工具
# 1.mysqldumpslow，mysql自带
# 2.pt-query-digest，第三方:https://www.percona.com/downloads/percona-toolkit/LATEST/
# 开启慢查询日志，默认关闭
slow_query_log=1
# 慢查询日志存放路径
slow_query_log_file=db_slow.log
# 超过5秒就认为是慢查询语句，默认10秒
long_query_time=5
# 输出类型为文件类型，支持TABLE和FILE类型，如果是TABLE，select * from mysql.slow_log;
#log_output=FILE,TABLE
# 默认就是文件
log_output=FILE
# 记录没有使用索引的查询语句，默认关闭
log_queries_not_using_indexes=1

# 跳过外部锁定，默认配置。External-locking用于多进程条件下为MyISAM数据表进行锁定
skip-external-locking
# 索引块的缓冲区的大小
key_buffer_size = 256M
# 指mysql服务器端和客户端在一次传送数据包的过程当中最大允许的数据包大小
max_allowed_packet = 64M
# 数据库打开表的缓存数量，即表的高速缓存
table_open_cache = 1024
# 排序缓冲区大小
sort_buffer_size = 4M
net_buffer_length = 8K
read_buffer_size = 4M
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 64M
# 线程缓存的数量，当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)
thread_cache_size = 64

# 临时表的内存缓存大小
tmp_table_size = 128M
# 数据行更新时，timestamp类型字段不更新为当前时间
explicit_defaults_for_timestamp = true
# 最大连接数
max_connections = 500
# 某一客户端尝试连接服务器端，允许最大的失败次数，超过这个设置，则服务端会强制阻止该客户端的连接
max_connect_errors = 100
# 使用的最大文件描述(FD)符数量，这个值不一定是这个设置的值，与操作系统设置以及最大连接数等有关
open_files_limit = 65535

# 创建新表时将使用的默认存储引擎
default_storage_engine = InnoDB
innodb_data_file_path = ibdata1:10M:autoextend
# buffer_pool缓冲区大小，8.4默认仍是128M，不会按机器内存自动放大，生产环境必须按内存调整
innodb_buffer_pool_size = 1024M
# 【8.4变化】redo日志总容量，替代已废弃的 innodb_log_file_size / innodb_log_files_in_group
# 默认100M，InnoDB会维持32个文件，每个文件是该值的1/32
innodb_redo_log_capacity = 1G
# 【8.4变化】redo log buffer，8.4默认值已从16M提高到64M，这里不再手动降低
# innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
# 独占机器时建议打开，让InnoDB按物理内存算buffer pool、按CPU数算redo容量
# 打开后 innodb_buffer_pool_size 和 innodb_redo_log_capacity 的手动配置会被覆盖
# innodb_dedicated_server=ON
# 修改默认的事务隔离级别，默认为REPEATABLE-READ
# transaction-isolation=READ-COMMITTED

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 4M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

数据库初始化

1
2
3
4
5
6
# 说明：--initialize-insecure 初始化时无密码
# 从默认位置查找配置文件，因为配置文件中已经指定了user，所以这里其实可以不指定user
sudo mysqld --user=mysql --initialize-insecure

# 也可以指定配置文件的位置，注意这里--defaults-file要放在第一个参数位置
sudo mysqld --defaults-file=/usr/local/soft/mysql8.4/my.cnf --user=mysql --initialize-insecure

警告⚠️
lower_case_table_names 必须在初始化之前写进配置文件，初始化完成后再改会导致启动失败。

启动数据库

1
2
3
4
5
6
7
8
9
# 后台启动
# 从默认位置查找配置文件
sudo mysqld_safe --user=mysql &

# 也可以指定配置文件的位置
sudo mysqld_safe --defaults-file=/usr/local/soft/mysql8.4/my.cnf --user=mysql &

# 查看是否启动成功
ps -ef|grep mysql

注意：mysqld_safe 在通用二进制包(tar.xz)里仍然提供；但用 RPM/DEB 安装时不会安装它，那种情况直接用 systemd 管理。

开机启动+启动关闭mysql命令

方法1[推荐]:使用mysql提供的脚本

  • 开机自启动

1
2
3
4
5
6
7
8
9
10
cd /usr/local/soft/mysql8.4/support-files/
# 拷贝mysql启动脚本到启动路径，/etc/init.d 软连接到 /etc/rc.d/init.d
sudo cp mysql.server /etc/init.d/mysqld
# 加入开机启动服务,相当于 chkconfig mysqld on
sudo systemctl enable mysqld
# 查看开机启动项
chkconfig --list

# 关闭开机自启动,相当于 chkconfig mysqld off
sudo systemctl disable mysqld

  • 启动关闭

1
2
3
4
# 启动mysql,start/stop/status/reload/restart
# 注意，如果启动mysql时不是通过systemctl启动的，则需要先 sudo mysqladmin -uroot -p shutdown 关闭后再启动
sudo systemctl start mysqld
sudo systemctl stop mysqld

方法2:自己写命令

  • 开机自启动

1
2
3
4
5
6
7
8
9
10
11
sudo chmod +x /etc/rc.d/rc.local
# /etc/rc.local 软连接到 /etc/rc.d/rc.local
sudo vim /etc/rc.local

# start mysql8.4
# 从默认位置查找配置文件
sudo /usr/local/soft/mysql8.4/bin/mysqld_safe --user=mysql &

# 也可以指定配置文件的位置
sudo /usr/local/soft/mysql8.4/bin/mysqld_safe --defaults-file=/usr/local/soft/mysql8.4/my.cnf --user=mysql &

  • 启动关闭

1
2
3
4
5
6
7
8
9
10
# 也可以指定配置文件的位置
sudo /usr/local/soft/mysql8.4/bin/mysqld_safe --defaults-file=/usr/local/soft/mysql8.4/my.cnf --user=mysql &

# 关闭mysql
sudo /usr/local/soft/mysql8.4/bin/mysqladmin  -uroot -ppassword  shutdown

# 为了方便使用可以设置别名
sudo vim /etc/bashrc
alias mysql-start="sudo /usr/local/soft/mysql8.4/bin/mysqld_safe  --user=mysql &"
alias mysql-stop="sudo /usr/local/soft/mysql8.4/bin/mysqladmin  -uroot -ppassword  shutdown"

检查mysql是否启动成功

1
2
3
4
5
# 查看进程
> ps aux | grep mysqld

# 查看端口，33060是mysqlx协议端口
> netstat -tunpl | grep 3306

登录

1
2
3
4
5
6
7
8
9
# 无密码登录，第一次登录没有密码，所以使用这种方式登录
# 只有没有密码时才能使用这种方式，如果已经设置了密码是不能使用这种方式的
mysql -uroot --skip-password

# 密码登录
mysql -uroot -p

# 远程登录
mysql -uroot -p -hxxx.xxx.xxx.xxx -P3306

  • 遇到的问题：8.4 的账号默认用 caching_sha2_password，该插件要求加密连接或基于 RSA 公钥交换密码。自带的 mysql 客户端默认会协商 TLS，一般不用管；老客户端或部分驱动可能报认证失败。

1
2
3
4
5
# 方式一：让客户端向服务端索取RSA公钥（未启用TLS时）
mysql -uroot -p -hxxx.xxx.xxx.xxx -P3306 --get-server-public-key

# 方式二：指定本地保存的公钥文件
mysql -uroot -p -hxxx.xxx.xxx.xxx -P3306 --server-public-key-path=/path/to/public_key.pem

修改密码

1
2
3
4
5
6
7
8
9
10
# 8.4默认插件就是caching_sha2_password，直接改密码即可
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

# 【8.4变化】不要再用 mysql_native_password，默认插件未加载，执行会报：
# ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded
# 确实需要兼容老客户端时，先在配置文件[mysqld]里加 mysql_native_password=ON 并重启
# mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

# 刷新权限
mysql> FLUSH PRIVILEGES;

授予root用户system_user权限

1
2
mysql> grant system_user on *.* to 'root'@'localhost';
mysql> flush privileges;

开启远程访问

1
2
3
4
5
# 允许远程访问
mysql> use mysql;
mysql> update user set user.Host='%' where user.User='root';
mysql> flush privileges;
mysql> select user,host from user;

警告⚠️
非常不建议开启root用户的远程访问权限，建议新创建一个用户，并仅授予必要的权限

1
2
3
4
5
6
7
8
9
10
11
12
# 创建新用户，8.4默认就是caching_sha2_password
mysql> CREATE USER 'username'@'%' IDENTIFIED BY 'password';
# 创建数据库
mysql> CREATE DATABASE my_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# 授予权限
mysql> GRANT all privileges ON my_database.* TO 'username'@'%';
# 刷新权限
mysql> FLUSH PRIVILEGES;
# 查询用户权限
mysql> show grants for username@"%"\G
# 查询当前系统用户
mysql> select user,host from mysql.user;

关闭mysql

1
sudo mysqladmin -uroot -p shutdown

2.主从

  • 参照单节点的构建方式搭建好两台mysql，主从计划如下:

1
2
master: 10.250.0.243
slave:  10.250.0.82

  • master和slave的server-id不能相同

  • master必须开启binlog功能

  • slave必须开启中继日志

  • 在云环境中，如果slave是通过master的镜像创建的，要修改slave的datadir中auto.cnf文件中的uuid的值，master和salve是不能相同的。

auto.cnf

1
2
[auto]
server-uuid=6e9a571e-330e-11ed-a3f8-0a53e7cced43

master配置文件

1
2
3
4
# 主从或集群中要保证唯一性
server-id = 1001
# 开启binlog日志
log-bin    = mysql-bin

slave配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 主从或集群中要保证唯一性
server-id = 1002
# 打开mysql中继日志，从库必须打开
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
# 是否自动清空不再需要中继日志，默认1：自动清空，0：不清空
relay_log_purge=1
# 【8.4变化】老参数 log_slave_updates 已改名为 log_replica_updates，且默认就是ON
# 即从库默认会把同步来的数据也写进自己的binlog，级联复制/被MHA类工具选主都依赖它
# 只有明确不想让从库记binlog时才关闭
log_replica_updates=ON
# 【8.4变化】从库默认就是多线程复制，replica_parallel_workers 默认4（8.0默认0即单线程）
# replica_preserve_commit_order 默认ON，保证从库binlog里的提交顺序和主库一致
# replica_parallel_workers=4

警告⚠️
master_info_repository、relay_log_info_repository、master-info-file、relay-log-info-file 这四个参数在 8.3 已被删除。8.4 的复制元数据只存在 mysql.slave_master_info、mysql.slave_relay_log_info 表里（crash-safe），配置文件里写这些参数会导致启动失败。

主库创建同步帐号

1
2
3
4
5
6
# '10.250.%.%' 表示只能局域网段内的ip才能访问，如果不限制ip，可以配置为'%'
# 8.4默认插件是caching_sha2_password，直接创建即可，不要再改成mysql_native_password
mysql> CREATE USER 'vagrant'@'10.250.%.%' IDENTIFIED BY 'vagrant';
# REPLICATION SLAVE 是权限名称，8.4没有改名，仍然这么写
mysql> GRANT REPLICATION SLAVE ON *.* TO 'vagrant'@'10.250.%.%';
mysql> FLUSH PRIVILEGES;

8.4 建复制账号的坑
复制账号用 caching_sha2_password 时，如果主从之间没有启用 TLS，从库必须拿到主库的 RSA 公钥才能完成认证，否则 START REPLICA 之后 Replica_IO_Running 一直是 No，报认证失败。两种解法：

  • 在下面的 CHANGE REPLICATION SOURCE TO 里加 GET_SOURCE_PUBLIC_KEY=1（本文采用）
  • 或者加 SOURCE_PUBLIC_KEY_PATH='/path/to/public_key.pem' 指定公钥文件

如果主从走加密连接（SOURCE_SSL=1 并配好证书），就不需要这两个参数。

如果此时主库已经有数据，则需要先将主库数据导入到从库后再开启主从复制

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 主库执行导出数据
mysqldump -uroot --all-databases --triggers --routines --events -p > all_databases.sql

# 更推荐这种方式，一致性快照导出，文件头部会带上位点，可以直接拿来做增量起点
mysqldump -uroot -p --all-databases --triggers --routines --events \
  --single-transaction --source-data=2 > all_databases.sql

# 导出数据时，为了防止数据变化可以先锁表
# 锁
mysql> FLUSH TABLES WITH READ LOCK;

# 解锁
mysql> UNLOCK TABLES;

# 从库执行导入数据
mysql -uroot -p < all_databases.sql

从节点设置同步

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
# 【8.4变化】查看主节点binlog状态，记录File和Position两个信息
# 老命令 SHOW MASTER STATUS 已被删除
mysql> SHOW BINARY LOG STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 |     1140 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# 【8.4变化】从节点设置同步信息，语句名和所有选项名都变了
# 老写法 CHANGE MASTER TO ... MASTER_HOST=... 会直接报语法错误
mysql> CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='10.250.0.243',
    SOURCE_PORT=3306,
    SOURCE_USER='vagrant',
    SOURCE_PASSWORD='vagrant',
    SOURCE_LOG_FILE='mysql-bin.000008',
    SOURCE_LOG_POS=1140,
    GET_SOURCE_PUBLIC_KEY=1;

# 【8.4变化】启动从库的复制线程，老命令 START SLAVE 已删除
mysql> START REPLICA;

# 【8.4变化】查看从库同步状态，老命令 SHOW SLAVE STATUS 已删除
mysql> SHOW REPLICA STATUS \G;
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 10.250.0.243
                  Source_User: vagrant
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mysql-bin.000008
          Read_Source_Log_Pos: 1140
               Relay_Log_File: slave-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Source_Log_File: mysql-bin.000008
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
          Exec_Source_Log_Pos: 1140
        Seconds_Behind_Source: 0

【8.4变化】判断主从是否正常，看的是Replica_IO_Running和Replica_SQL_Running都为Yes；老的Slave_IO_Running/Slave_SQL_Running这两个列名在8.4已经不存在了。

Read_Source_Log_Pos 和 Exec_Source_Log_Pos 的差值表示已收到但还没应用完的量，配合 Seconds_Behind_Source 看延迟。

此时在master中执行SHOW PROCESSLIST;可以查看到同步信息

1
2
3
4
5
6
7
8
9
mysql>  SHOW PROCESSLIST;
+----+-----------------+-------------------+------+-------------+------+-----------------------------------------------------------------+------------------+
| Id | User            | Host              | db   | Command     | Time | State                                                           | Info             |
+----+-----------------+-------------------+------+-------------+------+-----------------------------------------------------------------+------------------+
|  5 | event_scheduler | localhost         | NULL | Daemon      |   49 | Waiting on empty queue                                          | NULL             |
|  8 | vagrant         | 10.250.0.82:33924 | NULL | Binlog Dump |   49 | Source has sent all binlog to replica; waiting for more updates | NULL             |
|  9 | root            | 127.0.0.1:42410   | NULL | Query       |    0 | init                                                            | SHOW PROCESSLIST |
+----+-----------------+-------------------+------+-------------+------+-----------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

【8.4变化】在主库上查看挂了哪些从库，老命令是SHOW SLAVE HOSTS

1
mysql> SHOW REPLICAS;

主从架构，从库应该是禁止写操作的，否则有可能会导致主从同步失败（如主键冲突），所以为了保证数据的一致性，应该禁止从库写操作。

1
2
3
4
5
# 从库的配置文件增加如下内容，并重启从库
# 开启普通用户只读
read_only=1
# 开启root用户只读
super_read_only=1

开启GTID（全局事务ID）主从复制模式，8.4 推荐直接用 GTID，不用再手工记 File/Position

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
# master和slave的my.cnf中分别加入，并重启mysql服务
gtid_mode=on
enforce_gtid_consistency=on

# 从库改用自动定位，不需要指定 SOURCE_LOG_FILE / SOURCE_LOG_POS
mysql> STOP REPLICA;
mysql> CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='10.250.0.243',
    SOURCE_PORT=3306,
    SOURCE_USER='vagrant',
    SOURCE_PASSWORD='vagrant',
    SOURCE_AUTO_POSITION=1,
    GET_SOURCE_PUBLIC_KEY=1;
mysql> START REPLICA;

# 当有数据更新后查询主库状态，可以看到Executed_Gtid_Set 中有值了，这个值会随着数据更新不断更新
mysql> SHOW BINARY LOG STATUS;
+------------------+----------+--------------+------------------+----------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000014 |     1183 |              |                  | 6e9a571e-330e-11ed-a3f8-0a53e7cced42:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES like "%gtid%";

主从架构，一个master可以配置多个slave。

重建主从时的清理命令

1
2
3
4
5
6
7
8
9
10
11
12
13
# 【8.4变化】从库清空复制信息，老命令 RESET SLAVE ALL
mysql> STOP REPLICA;
mysql> RESET REPLICA ALL;

# 【8.4变化】主库清空binlog和GTID，老命令 RESET MASTER
# 注意该命令会删掉所有binlog并重置GTID，只在重建环境时用，生产环境慎用
mysql> RESET BINARY LOGS AND GTIDS;

# 【8.4变化】查看binlog列表，老命令 SHOW MASTER LOGS
mysql> SHOW BINARY LOGS;

# 【8.4变化】按需清理binlog，老命令 PURGE MASTER LOGS
mysql> PURGE BINARY LOGS TO 'mysql-bin.000010';

主从模式高可用架构

8.0 时代常见的做法是套 MHA 做自动选主，但 MHA 在 8.4 上不可用：它最后一个版本是 2018 年的 0.58，内部发的是 CHANGE MASTER TO、SHOW SLAVE STATUS 这些已被删除的语句。详见 MySql-MHA的构建方法。

8.4 要做自动故障转移，选型见本文最后一节。如果只是想让从库在主库连不上时自动改连另一个源，8.4 自带的 Asynchronous Connection Failover 可以做到（注意它只切换复制连接，不做主库提升）：

1
2
3
4
5
6
7
8
9
# 需要GTID + 自动定位
mysql> CHANGE REPLICATION SOURCE TO
    SOURCE_AUTO_POSITION=1,
    SOURCE_CONNECTION_AUTO_FAILOVER=1,
    SOURCE_RETRY_COUNT=3,
    SOURCE_CONNECT_RETRY=10;

# 注册备用源，权重越大越优先
mysql> SELECT asynchronous_connection_failover_add_source('', '10.250.0.82', 3306, '', 80);

3.双主

  • 双主模式就是两个mysql互为主从

  • 两个master都不能设置只读

  • 以上面主从为例，我们继续搭建双主架构

从库关闭只读并开启binlog

1
2
3
4
5
6
7
8
9
# 开启普通用户只读
# read_only=1
# 开启root用户只读
# super_read_only=1

# 主从或集群中要保证唯一性
server-id = 1002
# 开启binlog日志
log-bin    = mysql-bin

主库也要开启中继日志

1
2
3
4
5
6
7
8
9
# 主从或集群中要保证唯一性
server-id = 1001
# 打开mysql中继日志
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
# 是否自动清空不再需要中继日志，默认1：自动清空，0：不清空
relay_log_purge=1
# 【8.4变化】老参数 log_slave_updates 已改名，且默认ON
log_replica_updates=ON

从库上创建同步帐号

1
2
3
4
# '10.250.%.%' 表示只能局域网段内的ip才能访问，如果不限制ip，可以配置为'%'
mysql> CREATE USER 'vagrant'@'10.250.%.%' IDENTIFIED BY 'vagrant';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'vagrant'@'10.250.%.%';
mysql> FLUSH PRIVILEGES;

查看从库的binlog状态

1
2
3
4
5
6
7
mysql> SHOW BINARY LOG STATUS;
+------------------+----------+--------------+------------------+----------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000014 |     1183 |              |                  | 6e9a571e-330e-11ed-a3f8-0a53e7cced42:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)

主库上配置主从信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
mysql> CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='10.250.0.82',
    SOURCE_PORT=3306,
    SOURCE_USER='vagrant',
    SOURCE_PASSWORD='vagrant',
    SOURCE_LOG_FILE='mysql-bin.000014',
    SOURCE_LOG_POS=1183,
    GET_SOURCE_PUBLIC_KEY=1;

# 启动复制线程
mysql> START REPLICA;

mysql> SHOW REPLICA STATUS \G;
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 10.250.0.82
                  Source_User: vagrant
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mysql-bin.000014
          Read_Source_Log_Pos: 1183
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 326
        Relay_Source_Log_File: mysql-bin.000014
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes

此时双主架构搭建完成。
双主架构下，每个master还可以配置多个slave用于数据备份。

双主架构自增主键冲突问题解决方法

1
2
3
4
5
6
7
8
9
10
# master1
# auto_increment字段产生的数值是：1, 3, 5, 7, …等奇数ID了
auto_increment_offset = 1
auto_increment_increment = 2


# master2
# auto_increment字段产生的数值是：2, 4, 6, 8, …等偶数ID了
auto_increment_offset = 2
auto_increment_increment = 2

8.0 笔记里提到的双主高可用工具 MMM 同样是 Perl 老项目，早已停更，不要在 8.4 上使用。双主想做 VIP 漂移，用 Keepalived 之类自己控，或者直接上 InnoDB Cluster。

4.半同步复制

1
2
3
4
5
6
无论是主从复制还是双主复制，默认数据同步都是异步进行，主服务在向客户端反馈执行结果时，是不知道binlog是否同步成功了的。
这时候如果主服务宕机了，而从服务还没有备份到新执行的binlog，那就有可能会丢数据。
那怎么解决这个问题呢，这就要靠MySQL的半同步复制机制来保证数据安全。
半同步复制机制是一种介于异步复制和全同步复制之间的机制。
主库在执行完客户端提交的事务后，并不是立即返回客户端响应，而是等待至少一个从库接收并写到relay log中，才会返回给客户端。
MySQL在等待确认时，默认会等10秒，如果超过10秒没有收到ack，就会降级成为异步复制。

安装半同步复制插件

1
2
3
4
5
6
7
8
# 【8.4变化】插件和库文件都换成了source/replica命名
# 主库安装
mysql> INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
Query OK, 0 rows affected (0.02 sec)

# 从库安装
mysql> INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
Query OK, 0 rows affected (0.01 sec)

如果是双主架构，则两边都需要安装这两个插件

警告⚠️
新旧插件不能共存。从 8.0 升级上来的实例如果之前装过 rpl_semi_sync_master / rpl_semi_sync_slave，会在错误日志里看到：
Cannot install the rpl_semi_sync_source plugin when the rpl_semi_sync_master plugin is installed.
处理办法是先卸载旧插件、清掉配置文件里的旧变量、重启，再装新插件：

1
2
mysql> UNINSTALL PLUGIN rpl_semi_sync_master;
mysql> UNINSTALL PLUGIN rpl_semi_sync_slave;

开启配置

1
2
3
4
5
6
7
8
9
# 【8.4变化】变量名同步改成 source/replica
# master设置
rpl_semi_sync_source_enabled=1
# slave设置
rpl_semi_sync_replica_enabled=1
# 等待ack的超时时间，默认10000毫秒，超时降级为异步复制
rpl_semi_sync_source_timeout=10000
# 需要几个从库ack才返回客户端，默认1
rpl_semi_sync_source_wait_for_replica_count=1

重启mysql后查看是否配置成功

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> show global variables like 'rpl_semi%';
+-------------------------------------------------+------------+
| Variable_name                                   | Value      |
+-------------------------------------------------+------------+
| rpl_semi_sync_replica_enabled                   | ON         |
| rpl_semi_sync_replica_trace_level               | 32         |
| rpl_semi_sync_source_enabled                    | ON         |
| rpl_semi_sync_source_timeout                    | 10000      |
| rpl_semi_sync_source_trace_level                | 32         |
| rpl_semi_sync_source_wait_for_replica_count     | 1          |
| rpl_semi_sync_source_wait_no_replica            | ON         |
| rpl_semi_sync_source_wait_point                 | AFTER_SYNC |
+-------------------------------------------------+------------+

注意：rpl_semi_sync_master_* 这套旧变量在装了新插件之后是看不到的，两套变量不会同时存在。

5.MySql8.4 的高可用怎么选

主从/双主 + 半同步只解决「少丢数据」，不解决自动选主。8.4 上的可选方案：

方案 说明 适合
InnoDB Cluster 官方方案，Group Replication + MySQL Shell(AdminAPI) + MySQL Router，至少3节点，自动选主并自动把写流量路由到新Primary 新部署首选
InnoDB ClusterSet 多个 InnoDB Cluster 跨机房，主集群不可用时由管理员触发切换 跨机房容灾
Percona XtraDB Cluster 8.4 Galera 准同步，强调数据一致性，前端配 ProxySQL / HAProxy 对一致性要求更高
本文的主从/双主 + 半同步 不自动选主，主库挂了要人工切或自己写脚本 读写分离、备份、容量扩展
云数据库多可用区 RDS / Aurora / 云厂商托管，平台负责选主和地址漂移 能上云优先

需要注意的两点：

  • ProxySQL、HAProxy、Keepalived 只做连接路由或 VIP，不会安全地把某个从库提升成新主，它们必须配合一个真正的选主组件。

  • 前面提到的 Asynchronous Connection Failover 只是让从库换源，不做主库提升。

主从会丢数据吗？双主自增怎么防冲突？

场景：单机改一主一从读写分离，并评估是否上双主。以下命令均为 8.4 写法。

1. 搭主从时主库、从库至少要配对什么？

  • 两边：server-id 必须不同；若从库是主库镜像克隆的，还要改 datadir/auto.cnf 里的 server-uuid，uuid 也不能相同。

  • 主库：必须开 binlog（log-bin），复制账号要有 REPLICATION SLAVE；已有数据时先 mysqldump 导入从库，再按 SHOW BINARY LOG STATUS 的 File/Position 做 CHANGE REPLICATION SOURCE TO（开了 GTID 就用 SOURCE_AUTO_POSITION=1）。

  • 从库：必须开中继日志（relay-log）；START REPLICA 后 Replica_IO_Running 与 Replica_SQL_Running 都为 Yes 才算通。log_replica_updates 在 8.4 默认就是 ON，级联复制不用额外开。

  • 写保护：从库应 read_only / super_read_only，禁止业务写入，避免主键冲突把复制打挂。

  • 8.4 特有：复制账号默认 caching_sha2_password，非加密连接下必须给 GET_SOURCE_PUBLIC_KEY=1 或 SOURCE_PUBLIC_KEY_PATH，否则 IO 线程起不来。

2. 默认异步复制会丢数据吗？

  • 会。 异步复制下，主库本地事务提交成功就会给客户端返回「成功」，不等 binlog 传到从库。

  • 若返回成功后主库立刻宕机，而这条 binlog 还没到从库（甚至没 flush 完），切换到从库后这条提交就没了——客户端以为成功，从库上却看不到。

  • 主库 SHOW PROCESSLIST 里能看到 Binlog Dump 线程，那只说明「在推」；默认链路不保证「推到了才对客户端 ack」。

3. 半同步解决哪一步？等到什么？等不到怎样？

  • 半同步卡的是**「主库已提交 → 客户端收到成功」之间：至少等一个从库收到 binlog 并写入 relay log** 后，主库才返回客户端。

  • 注意：等到的是「写进中继日志」，不保证从库 SQL 线程已经应用完。所以是「至少进了从库磁盘侧的中继日志」，不是全同步。

  • 默认超时约 10 秒（rpl_semi_sync_source_timeout=10000）。超时收不到 ack，会降级成异步复制，之后又回到可能丢数的模式，直到半同步重新建立。

  • 代价：提交延迟上升，换的是宕机时少丢已向客户端确认的事务。8.4 要装的是 rpl_semi_sync_source / rpl_semi_sync_replica，且不能和 8.0 的 master/slave 版插件共存。

4. 双主自增冲突怎么防？解决的是什么？

  • 两边都能写时，默认两边自增都从 1,2,3… 长，很容易主键冲突，复制中断。

  • 配法（文中示例）：

    • master1：auto_increment_offset=1，auto_increment_increment=2 → 1,3,5,7…
    • master2：auto_increment_offset=2，auto_increment_increment=2 → 2,4,6,8…

  • 只解决自增主键撞号，不是强一致：双主仍是复制（默认异步/半同步），两边并发改同一行仍可能冲突；也解决不了业务唯一键、无自增主键的冲突。很多团队双主实际当主备，同一时刻只让一个节点接写。

5. 8.4 和 8.0 在这块有什么不一样？

  • 主从语句和 CHANGE 选项全部改名，旧名已删除而非别名，8.0 的脚本必须改。

  • 判定列变成 Replica_IO_Running / Replica_SQL_Running。

  • 认证默认 caching_sha2_password，default_authentication_plugin 已删除。

  • 半同步插件换成 source/replica 版。

  • MHA / MMM 这类老工具不可用，自动选主要走 InnoDB Cluster。

面试可背：主从要不同 server-id/uuid，主开 binlog、从开 relay，从库只读；异步提交先返回可能丢数；半同步等到 relay log 再返回，超时降级异步；双主用奇偶自增防撞号，不是分布式强一致；8.4 起主从语句全面改名、认证默认 caching_sha2_password、MHA 不可用。