目录
一、实验
1.环境
2.Mysql-shell 检查工具兼容性
3.逻辑备份MySQL数据
4.备份MySQL 数据目录、安装目录、配置文件
5.MySQL 升级
7. slave1 节点升级
8. slave2 节点升级
9.半同步设置
二、问题
1.mysqldump备份报错
2.InnoDB 有哪些关闭模式。
3.master节点执行升级程序报错
4. slave 节点执行升级程序报错
5.监视半同步复制的插件状态报错
一、实验
1.环境
（1）主机
表1 主机
| 架构 | 当前版本 | 目标版本 | IP | 备注 |
| MySQL Master | 5.7.42 | 8.2.0 | 192.168.204.10 | 主服务器 |
| MySQL Slave1 | 5.7.42 | 8.2.0 | 192.168.204.11 | 从服务器 |
| MySQL Slave2 | 5.7.42 | 8.2.0 | 192.168.204.12 | 从服务器 |
(2) 查看版本
Master
[root@localhost ~]# mysql -V
Slave1
[root@localhost ~]# mysql -V
Slave2
[root@localhost ~]# mysql -V
（3）查看状态
Master
[root@localhost ~]# mysql -uroot -p
……
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 | 1372 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
Slave1
[root@localhost ~]# mysql -uroot -p
……
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.204.10
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 1372
Relay_Log_File: relay-log-bin.000047
Relay_Log_Pos: 950
Relay_Master_Log_File: mysql-bin.000007
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: 1372
Relay_Log_Space: 1956
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: 1
Master_UUID: c8246fd9-1c99-11ee-af46-000c29747129
Master_Info_File: /var/lib/mysql/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:
1 row in set (0.00 sec)
mysql>
Slave2
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 5.7.42 MySQL Community Server (GPL)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
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 slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.204.10
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 1372
Relay_Log_File: relay-log-bin.000025
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000007
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: 1372
Relay_Log_Space: 1321
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: 1
Master_UUID: c8246fd9-1c99-11ee-af46-000c29747129
Master_Info_File: /var/lib/mysql/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:
1 row in set (0.00 sec)
mysql> 
（4）查看配置
Master
[root@localhost ~]# vim /etc/my.cnf
Slave1
[root@localhost ~]# vim /etc/my.cnf
Slave2
[root@localhost ~]# vim /etc/my.cnf
(5)查看半同步是否在运行
master
mysql> show status like 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON |
+-----------------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'rpl_semi_sync_master_timeout';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| rpl_semi_sync_master_timeout | 1000 |
+------------------------------+-------+
1 row in set (0.00 sec)
slave1
mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
slave2
mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.01 sec)
2.Mysql-shell 检查工具兼容性
(1) 查看
https://downloads.mysql.com/archives/shell/
最新版本为8.2.1
（2）查看 GLIBC 版本
master
[root@localhost ~]# ldd --version
ldd (GNU libc) 2.17
Copyright (C) 2012 Free Software Foundation, Inc.
This is free software; see the source for copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
由 Roland McGrath 和 Ulrich Drepper 编写。
slave1
[root@localhost ~]# ldd --version
ldd (GNU libc) 2.17
Copyright (C) 2012 Free Software Foundation, Inc.
This is free software; see the source for copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
由 Roland McGrath 和 Ulrich Drepper 编写。
slave2
[root@localhost ~]# ldd --version
ldd (GNU libc) 2.17
Copyright (C) 2012 Free Software Foundation, Inc.
This is free software; see the source for copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
由 Roland McGrath 和 Ulrich Drepper 编写。
（3）下载最新版本（所有主机）
wget https://downloads.mysql.com/archives/get/p/43/file/mysql-shell-8.2.1-linux-glibc2.17-x86-64bit.tar.gz
（4）解压
[root@localhost ~]# tar -xf mysql-shell-8.2.1-linux-glibc2.17-x86-64bit.tar.gz
（5） 在两台slave服务器上，关闭同步
mysql> stop slave;
（6）master测试
[root@localhost ~]# cd mysql-shell-8.2.1-linux-glibc2.17-x86-64bit/
[root@localhost mysql-shell-8.2.1-linux-glibc2.17-x86-64bit]# ls
bin lib libexec share
[root@localhost mysql-shell-8.2.1-linux-glibc2.17-x86-64bit]# cd bin
[root@localhost bin]# ls
mysql-secret-store-login-path mysqlsh
执行
[root@localhost bin]# ./mysqlsh -uroot -p -S /var/lib/mysql/mysql.sock -e "util.checkForServerUpgrade()" > util.checkForServerUpgrade.log
Please provide the password for 'root@/var%2Flib%2Fmysql%2Fmysql.sock': ******
NOTE: No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading.
（7）master查看报告
[root@localhost bin]# ls
mysql-secret-store-login-path mysqlsh util.checkForServerUpgrade.log
[root@localhost bin]# vim util.checkForServerUpgrade.log
Community Server (GPL), will now be checked for compatibility issues for
upgrade to MySQL 8.2.1...
1) Usage of old temporal type
No issues found
2) MySQL 8.0 syntax check for routine-like objects
No issues found
3) Usage of db objects with names conflicting with new reserved keywords
No issues found
4) Usage of utf8mb3 charset
No issues found
5) Table names in the mysql schema conflicting with new tables in 8.0
No issues found
6) Partitioned tables using engines with non native partitioning
No issues found
7) Foreign key constraint names longer than 64 characters
No issues found
8) Usage of obsolete MAXDB sql_mode flag
No issues found
9) Usage of obsolete sql_mode flags
Notice: The following DB objects have obsolete options persisted for
sql_mode, which will be cleared during upgrade to 8.0.
More information:
https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals
global system variable sql_mode - defined using obsolete NO_AUTO_CREATE_USER
option
10) ENUM/SET column definitions containing elements longer than 255 characters
No issues found
11) Usage of partitioned tables in shared tablespaces
No issues found
12) Circular directory references in tablespace data file paths
No issues found
13) Usage of removed functions
No issues found
14) Usage of removed GROUP BY ASC/DESC syntax
No issues found
15) Removed system variables for error logging to the system log configuration
To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
More information:
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-13.html#mysqld-8-0-13-logging
16) Removed system variables
To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
More information:
https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed
17) System variables with new default values
To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
More information:
https://mysqlserverteam.com/new-defaults-in-mysql-8-0/
18) Zero Date, Datetime, and Timestamp values
No issues found
19) Schema inconsistencies resulting from file removal or corruption
No issues found
20) Tables recognized by InnoDB that belong to a different engine
No issues found
21) Issues reported by 'check table x for upgrade' command
No issues found
22) New default authentication plugin considerations
Warning: The new default authentication plugin 'caching_sha2_password' offers
more secure password hashing than previously used 'mysql_native_password'
(and consequent improved client connection authentication). However, it also
has compatibility implications that may affect existing MySQL installations.
If your MySQL installation must serve pre-8.0 clients and you encounter
compatibility issues after upgrading, the simplest way to address those
issues is to reconfigure the server to revert to the previous default
authentication plugin (mysql_native_password). For example, use these lines
in the server option file:
[mysqld]
default_authentication_plugin=mysql_native_password
However, the setting should be viewed as temporary, not as a long term or
permanent solution, because it causes new accounts created with the setting
in effect to forego the improved authentication security.
If you are using replication please take time to understand how the
authentication plugin changes may impact you.
More information:
https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatibility-issues
https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replication
23) Columns which cannot have default values
No issues found
24) Check for invalid table names and schema names used in 5.7
No issues found
25) Check for orphaned routines in 5.7
No issues found
26) Check for deprecated usage of single dollar signs in object names
No issues found
27) Check for indexes that are too large to work on higher versions of MySQL
Server than 5.7
No issues found
28) Check for deprecated '.<table>' syntax used in routines.
No issues found
29) Check for columns that have foreign keys pointing to tables from a diffrent
database engine.
No issues found
Errors: 0
Warnings: 1
Notices: 1
从输出报告可以看出，升级检查器在29个方面进行了检查，最终得出1个警告信息和1个提示。
(8) slave1 测试
[root@localhost ~]# tar -xf mysql-shell-8.2.1-linux-glibc2.17-x86-64bit.tar.gz
您在 /var/spool/mail/root 中有新邮件
[root@localhost ~]# cd mysql-shell-8.2.1-linux-glibc2.17-x86-64bit/bin
[root@localhost bin]# ./mysqlsh -uroot -p -S /var/lib/mysql/mysql.sock -e "util.checkForServerUpgrade()" > util.checkForServerUpgrade.log
Please provide the password for 'root@/var%2Flib%2Fmysql%2Fmysql.sock': ******
NOTE: No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading.
[root@localhost bin]# ls
mysql-secret-store-login-path mysqlsh util.checkForServerUpgrade.log
[root@localhost bin]# vim util.checkForServerUpgrade.log 
从输出报告可以看出，升级检查器在29个方面进行了检查，最终得出1个警告信息和1个提示。
（9）slave2 测试
[root@localhost ~]# tar -xf mysql-shell-8.2.1-linux-glibc2.17-x86-64bit.tar.gz
[root@localhost ~]# cd mysql-shell-8.2.1-linux-glibc2.17-x86-64bit/bin
[root@localhost bin]# ./mysqlsh -uroot -p -S /var/lib/mysql/mysql.sock -e "util.checkForServerUpgrade()" > util.checkForServerUpgrade.log
Please provide the password for 'root@/var%2Flib%2Fmysql%2Fmysql.sock': ******
NOTE: No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading.
[root@localhost bin]# ls
mysql-secret-store-login-path mysqlsh util.checkForServerUpgrade.log
[root@localhost bin]# vim util.checkForServerUpgrade.log
从输出报告可以看出，升级检查器在29个方面进行了检查，最终得出1个警告信息和4个提示。
3.逻辑备份MySQL数据
（1） 查看数据库
master
其内置的四个数据库mysql、information_schema、sys和performance_schema
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_test |
| home |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
（2）逻辑备份
[root@localhost ~]# /usr/bin/mysqldump -uroot -p --routines --set-gtid-purged=OFF --databases db_test home mysql > /root/all-database-20240319.sql
Enter password: 
（3）平滑（优雅的）停止数据库
master
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.42-log |
+------------+
1 row in set (0.00 sec)
mysql> show variables like 'innodb_fast_shutdown';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| innodb_fast_shutdown | 1 |
+----------------------+-------+
1 row in set (0.00 sec)
mysql> set global innodb_fast_shutdown=0;
Query OK, 0 rows affected (0.00 sec)
mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
slave1
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.42 |
+-----------+
1 row in set (0.00 sec)
mysql> show variables like 'innodb_fast_shutdown';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| innodb_fast_shutdown | 1 |
+----------------------+-------+
1 row in set (0.01 sec)
mysql> set global innodb_fast_shutdown=0;
Query OK, 0 rows affected (0.00 sec)
mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
slave2
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.42 |
+-----------+
1 row in set (0.00 sec)
mysql> show variables like 'innodb_fast_shutdown';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| innodb_fast_shutdown | 1 |
+----------------------+-------+
1 row in set (0.01 sec)
mysql> set global innodb_fast_shutdown=0;
Query OK, 0 rows affected (0.00 sec)
mysql> shutdown;
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye
（4）查看进程
master
[root@localhost ~]# ps -ef | grep mysql
slave1
[root@localhost ~]# ps -ef | grep mysql
slave2
[root@localhost ~]# ps -ef | grep mysql
4.备份MySQL 数据目录、安装目录、配置文件
（1）确认数据库状态为关闭状态
master
[root@localhost ~]# systemctl status mysqld
slave1
[root@localhost ~]# systemctl status mysqld
slave2
[root@localhost ~]# systemctl status mysqld
(2) 数据目录备份
master
[root@localhost mysql]# cp -r /var/lib/mysql /var/lib/mysql_bak_`date +%F`
[root@localhost lib]# ls | grep mysql
slave1
[root@localhost mysql]# cp -r /var/lib/mysql /var/lib/mysql_bak_`date +%F`
[root@localhost lib]# ls | grep mysql
slave2
[root@localhost mysql]# cp -r /var/lib/mysql /var/lib/mysql_bak_`date +%F`
[root@localhost lib]# ls | grep mysql
（3）配置文件备份
master
[root@localhost ~]# cp /etc/my.cnf /etc/my.cnf_`date +%F`
[root@localhost etc]# ls | grep my.cnf
slave1
[root@localhost ~]# cp /etc/my.cnf /etc/my.cnf_`date +%F`
[root@localhost etc]# ls | grep my.cnf
slave2
[root@localhost ~]# cp /etc/my.cnf /etc/my.cnf_`date +%F`
[root@localhost etc]# ls | grep my.cnf
5.MySQL 升级
(1) 查询
https://downloads.mysql.com/archives/community/ 
版本
（2）下载
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.2.0-linux-glibc2.17-x86_64.tar.xz
传送至slave1、slave2
[root@localhost ~]# rsync -aXSH --delete mysql-8.2.0-linux-glibc2.17-x86_64.tar.xz 192.168.204.11:~
[root@localhost ~]# rsync -aXSH --delete mysql-8.2.0-linux-glibc2.17-x86_64.tar.xz 192.168.204.12:~
（3）解压
master
[root@localhost ~]# tar -xf mysql-8.2.0-linux-glibc2.17-x86_64.tar.xz
slave1
[root@localhost ~]# tar -xf mysql-8.2.0-linux-glibc2.17-x86_64.tar.xz
slave2
[root@localhost ~]# tar -xf mysql-8.2.0-linux-glibc2.17-x86_64.tar.xz
（4）文件夹重命名为mysql8
master
[root@localhost ~]# mv mysql-8.2.0-linux-glibc2.17-x86_64 mysql8
slave1
[root@localhost ~]# mv mysql-8.2.0-linux-glibc2.17-x86_64 mysql8
slave2
[root@localhost ~]# mv mysql-8.2.0-linux-glibc2.17-x86_64 mysql8
(5)更改文件夹所属
master
[root@localhost local]# chown -Rf mysql:mysql /usr/local/mysql8
slave1
[root@localhost local]# chown -Rf mysql:mysql /usr/local/mysql8
slave2
[root@localhost local]# chown -Rf mysql:mysql /usr/local/mysql8
(6) 修改配置文件
log_replica_updates= ON
binlog_expire_logs_seconds=259200
#for8.0
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
character_set_server=utf8mb4
collation-server=utf8mb4_0900_ai_ci
basedir = /usr/local/mysql8
default_authentication_plugin = caching_sha2_password
default-storage-engine=INNODB
主要注意sql_mode、basedir、密码认证插件及字符集设置，其他参数最好还是按照原5.7的来，不需要做调整。
（7）执行升级程序
会一直卡住
[root@localhost ~]# /usr/local/mysql8/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --upgrade=FORCE
2024-03-19T16:02:35.733625Z mysqld_safe Logging to '/var/log/mysqld.log'.
2024-03-19T16:02:35.772820Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
新开一个窗口，可观察下错误日志
[root@localhost ~]# tailf -n 12 /var/log/mysqld.log
2024-03-19T16:02:35.790837Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2024-03-19T16:02:36.130287Z 0 [Warning] [MY-011070] [Server] 'binlog_format' is deprecated and will be removed in a future release.
2024-03-19T16:02:36.130707Z 0 [System] [MY-010116] [Server] /usr/local/mysql8/bin/mysqld (mysqld 8.2.0) starting as process 8423
2024-03-19T16:02:36.168393Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-03-19T16:02:36.984776Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-03-19T16:02:37.308974Z 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001287 - 'validate password plugin' is deprecated and will be removed in a future release. Please use validate_password component instead
2024-03-19T16:02:37.351393Z 4 [System] [MY-013381] [Server] Server upgrade from '80200' to '80200' started.
2024-03-19T16:02:44.272950Z 4 [System] [MY-013381] [Server] Server upgrade from '80200' to '80200' completed.
2024-03-19T16:02:44.596670Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-03-19T16:02:44.596752Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-03-19T16:02:44.642943Z 0 [System] [MY-010931] [Server] /usr/local/mysql8/bin/mysqld: ready for connections. Version: '8.2.0' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL.
2024-03-19T16:02:44.643653Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
（8）登录数据库
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.2.0 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
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> select version();
+-----------+
| version() |
+-----------+
| 8.2.0 |
+-----------+
1 row in set (0.00 sec)
mysql>
（9）申明变量
[root@localhost ~]# vim /etc/profile
export PATH=/usr/local/mysql8/bin:$PATH
（10）更新
[root@localhost local]# source /etc/profile
（11）查看运行位置
[root@localhost local]# which mysql
/usr/local/mysql8/bin/mysql
（12）查看版本
[root@localhost local]# mysql -V
mysql Ver 8.2.0 for Linux on x86_64 (MySQL Community Server - GPL)
6.master节点 使用systemd管理mysql8
(1) 查看进程
[root@localhost ~]# ps -ef | grep mysql
root 8129 2857 0 00:02 pts/1 00:00:00 /bin/sh /usr/local/mysql8/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --upgrade=FORCE
mysql 8423 8129 2 00:02 pts/1 00:00:16 /usr/local/mysql8/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql8 --datadir=/var/lib/mysql --plugin-dir=/usr/local/mysql8/lib/plugin --user=mysql --upgrade=FORCE --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
root 8934 8715 0 00:16 pts/0 00:00:00 grep --color=auto mysql
（2）awk查询
[root@localhost ~]# ps -ef | grep mysql | awk '{print $2}'
8129
8423
8931
（3）停止mysqld_safe进程
[root@localhost ~]# kill -9 `ps -ef | grep mysql | awk '{print $2}'`
观察之前的命令
确认没有mysql进程
（3）修改systemd配置
修改原先的ExecStart中，basedir的路径，改为mysql8 的路径。
如果不存在就新建一个。
[root@localhost ~]# vim /etc/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql8/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 65535
（4）配置mysql8开机自启
重新加载
[root@localhost ~]# systemctl daemon-reload
开机启动
[root@localhost ~]# systemctl enable mysqld
启动服务
[root@localhost ~]# systemctl start mysqld
查看进程
[root@localhost ~]# ps -ef | grep mysql
mysql 9062 1 9 00:24 ? 00:00:01 /usr/local/mysql8/bin/mysqld --defaults-file=/etc/my.cnf
root 9110 8715 0 00:24 pts/0 00:00:00 grep --color=auto mysql
（5）登录数据库
数据已恢复。
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.2.0 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
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> select version();
+-----------+
| version() |
+-----------+
| 8.2.0 |
+-----------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db_test |
| home |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> exit
Bye
7. slave1 节点升级
（1）修改配置文件
[root@localhost local]# vim /etc/my.cnf
(2) 执行升级程序
会一直卡住
[root@localhost mysql]# /usr/local/mysql8/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --upgrade=FORCE
2024-03-19T17:47:05.177779Z mysqld_safe Logging to '/var/log/mysqld.log'.
2024-03-19T17:47:05.217935Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
新开一个窗口，可观察下错误日志
[root@localhost ~]# tailf -n 16 /var/log/mysqld.log
2024-03-19T17:47:05.258615Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2024-03-19T17:47:05.603689Z 0 [System] [MY-010116] [Server] /usr/local/mysql8/bin/mysqld (mysqld 8.2.0) starting as process 10120
2024-03-19T17:47:05.663382Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2024-03-19T17:47:05.663627Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-03-19T17:47:06.921126Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-03-19T17:47:07.578164Z 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001287 - 'validate password plugin' is deprecated and will be removed in a future release. Please use validate_password component instead
2024-03-19T17:47:08.578589Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2024-03-19T17:47:09.542512Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80200' started.
2024-03-19T17:47:15.410404Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80200' completed.
2024-03-19T17:47:15.583737Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-03-19T17:47:15.583828Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-03-19T17:47:15.628136Z 0 [Warning] [MY-010539] [Repl] Recovery from source pos 1372 and file mysql-bin.000007 for channel ''. Previous relay log pos and relay log file had been set to 950, ./relay-log-bin.000047 respectively.
2024-03-19T17:47:15.632722Z 9 [Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
2024-03-19T17:47:15.641476Z 0 [System] [MY-010931] [Server] /usr/local/mysql8/bin/mysqld: ready for connections. Version: '8.2.0' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL.
2024-03-19T17:47:15.641667Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
2024-03-19T17:47:15.831305Z 9 [System] [MY-014001] [Repl] Replica receiver thread for channel '': connected to source 'myslave@192.168.204.10:3306' with server_uuid=c8246fd9-1c99-11ee-af46-000c29747129, server_id=1. Starting replication from file 'mysql-bin.000007', position '1372'.
（3）登录数据库
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.2.0 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
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> select version();
+-----------+
| version() |
+-----------+
| 8.2.0 |
+-----------+
1 row in set (0.00 sec)
mysql>
（4）申明变量
[root@localhost ~]# vim /etc/profile
export PATH=/usr/local/mysql8/bin:$PATH
（10）更新
[root@localhost local]# source /etc/profile
（11）查看运行位置
[root@localhost local]# which mysql
/usr/local/mysql8/bin/mysql
（12）查看版本
[root@localhost local]# mysql -V
mysql Ver 8.2.0 for Linux on x86_64 (MySQL Community Server - GPL)
（13）使用systemd管理mysql8
关闭进程
观察
启动
（14）查看
8. slave2 节点升级
（1）修改配置文件
[root@localhost local]# vim /etc/my.cnf
(2) 执行升级程序
会一直卡住
[root@localhost mysql]# /usr/local/mysql8/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --upgrade=FORCE
2024-03-19T18:08:36.722141Z mysqld_safe Logging to '/var/log/mysqld.log'.
2024-03-19T18:08:36.764305Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
新开一个窗口，可观察下错误日志
[root@localhost ~]# tailf -n 16 /var/log/mysqld.log
2024-03-19T18:08:36.830085Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2024-03-19T18:08:38.120840Z 0 [System] [MY-010116] [Server] /usr/local/mysql8/bin/mysqld (mysqld 8.2.0) starting as process 7284
2024-03-19T18:08:38.205593Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2024-03-19T18:08:38.205726Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-03-19T18:08:39.349587Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-03-19T18:08:39.754272Z 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001287 - 'validate password plugin' is deprecated and will be removed in a future release. Please use validate_password component instead
2024-03-19T18:08:40.131905Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2024-03-19T18:08:40.914022Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80200' started.
2024-03-19T18:08:46.254503Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80200' completed.
2024-03-19T18:08:46.503504Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-03-19T18:08:46.503609Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-03-19T18:08:46.556282Z 0 [Warning] [MY-010539] [Repl] Recovery from source pos 1372 and file mysql-bin.000007 for channel ''. Previous relay log pos and relay log file had been set to 320, ./relay-log-bin.000025 respectively.
2024-03-19T18:08:46.560468Z 9 [Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
2024-03-19T18:08:46.568407Z 0 [System] [MY-010931] [Server] /usr/local/mysql8/bin/mysqld: ready for connections. Version: '8.2.0' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL.
2024-03-19T18:08:46.578217Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
2024-03-19T18:08:46.790350Z 9 [System] [MY-014001] [Repl] Replica receiver thread for channel '': connected to source 'myslave@192.168.204.10:3306' with server_uuid=c8246fd9-1c99-11ee-af46-000c29747129, server_id=1. Starting replication from file 'mysql-bin.000007', position '1372'.
（3）登录数据库
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.2.0 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
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> select version();
+-----------+
| version() |
+-----------+
| 8.2.0 |
+-----------+
1 row in set (0.00 sec)
mysql>
（4）申明变量
[root@localhost ~]# vim /etc/profile
export PATH=/usr/local/mysql8/bin:$PATH
（5）更新
[root@localhost local]# source /etc/profile
（6）查看运行位置
[root@localhost local]# which mysql
/usr/local/mysql8/bin/mysql
（7）查看版本
[root@localhost local]# mysql -V
mysql Ver 8.2.0 for Linux on x86_64 (MySQL Community Server - GPL)
（8）使用systemd管理mysql8
关闭进程
观察
启动
（9）查看
9.半同步设置
（1）加载插件
要加载插件，在master源和要半同步的每个副本上使用 INSTALL PLUGIN 语句，并根据需要为平台调整 .so 后缀。
master:
INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
slave1:
INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
slave2:
INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
(3) 验证插件是否安装（如报错必须安装 libimf）
master
SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE '%semi%';
slave1
slave2
（4）启用插件
master
[root@localhost mysql]# vim /etc/my.cnf
……
rpl_semi_sync_replica_enabled=1
……
slave1
[root@localhost mysql]# vim /etc/my.cnf
……
rpl_semi_sync_replica_enabled=1
……
slave2
[root@localhost mysql]# vim /etc/my.cnf
……
rpl_semi_sync_replica_enabled=1
……
(5) 全部节点重启
[root@localhost mysql]# systemctl restart mysqld
（6）查看进程
master
slave1
slave2
(7)检查半同步复制状态变量的当前值
mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';
+---------------------------------------------+------------+
| Variable_name | Value |
+---------------------------------------------+------------+
| 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 |
+---------------------------------------------+------------+
6 rows in set (0.02 sec)
（8）监视半同步复制的插件状态
Rpl_semi_sync_source_clients 连接到源服务器的半同步副本的数量变为2
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_source_clients | 2 |
| Rpl_semi_sync_source_net_avg_wait_time | 0 |
| Rpl_semi_sync_source_net_wait_time | 0 |
| Rpl_semi_sync_source_net_waits | 0 |
| Rpl_semi_sync_source_no_times | 0 |
| Rpl_semi_sync_source_no_tx | 0 |
| Rpl_semi_sync_source_status | ON |
| Rpl_semi_sync_source_timefunc_failures | 0 |
| Rpl_semi_sync_source_tx_avg_wait_time | 0 |
| Rpl_semi_sync_source_tx_wait_time | 0 |
| Rpl_semi_sync_source_tx_waits | 0 |
| Rpl_semi_sync_source_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_source_wait_sessions | 0 |
| Rpl_semi_sync_source_yes_tx | 0 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
（9）查看状态
master
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000013
Position: 157
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set, 1 warning (0.00 sec)
slave1
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.204.10
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000013
Read_Master_Log_Pos: 157
Relay_Log_File: relay-log-bin.000059
Relay_Log_Pos: 373
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: 157
Relay_Log_Space: 750
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: 1
Master_UUID: c8246fd9-1c99-11ee-af46-000c29747129
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica 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:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
slave2
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.204.10
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000013
Read_Master_Log_Pos: 157
Relay_Log_File: relay-log-bin.000037
Relay_Log_Pos: 373
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: 157
Relay_Log_Space: 750
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: 1
Master_UUID: c8246fd9-1c99-11ee-af46-000c29747129
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica 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:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
(9)创建数据库
master
mysql> CREATE DATABASE club;
(10) 查看数据库
slave1
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| club |
| db_test |
| home |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
7 rows in set (0.01 sec)
slave2
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| club |
| db_test |
| home |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
7 rows in set (0.01 sec)
二、问题
1.mysqldump备份报错
（1）报错
mysqldump: Got error: 1044: Access denied for user 'root'@'localhost' to database 'information_schema' when using LOCK TABLES
（2）原因分析
mysqldump 命令执行时，需要四种权限，分别是：select,show view,trigger,lock table。但是因为没有lock table的权限，导致上述错误发生。
（3）解决方法
在mysqldump命令之后添加--single-transaction 即可。
执行
[root@localhost ~]# /usr/bin/mysqldump --single-transaction -uroot -p --routines --set-gtid-purged=OFF --databases information_schema db_test home mysql performance_schema sys > /root/all-database-20240319.sql
Enter password:
mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `GLOBAL_STATUS`': The 'INFORMATION_SCHEMA.GLOBAL_STATUS' feature is disabled; see the documentation for 'show_compatibility_56' (3167)
上面出现再次报错，因为MySQL 其内置的四个数据库mysql、information_schema、sys和performance_schema
1）mysql数据库
mysql数据库是存储MySQL服务器的系统和用户权限信息的地方。它包含了用户、权限、角色等相关信息。这个数据库是非常重要的，因为它控制着MySQL服务器的访问和操作权限。
2）information_schema数据库
information_schema数据库是一个元数据信息存储库，它包含了关于MySQL服务器中所有数据库、表、列、索引等对象的信息。通过查询information_schema数据库，可以获取关于数据库结构和元数据的详细信息。
3）sys数据库
sys数据库是MySQL 8.0版本引入的一个新特性，它提供了一组视图和存储过程，用于简化和改进对MySQL服务器的监控和性能分析。sys数据库中的视图可以帮助用户更方便地获取和分析MySQL服务器的性能指标和状态信息。
4）performance_schema数据库
performance_schema数据库也是MySQL 5.5版本引入的一个新特性，它提供了一组性能监控相关的表和视图，用于收集和展示MySQL服务器的性能数据。通过performance_schema数据库，可以监控和分析MySQL服务器的查询性能、锁等待、I/O操作等方面的信息。需要备份这些内置数据库取决于你的具体需求和情况。一般来说，mysql数据库是非常重要的，因为它包含了用户和权限信息，建议定期备份。information_schema、sys和performance_schema数据库通常不需要备份，因为它们是动态生成的，可以通过查询获取最新的信息。
[root@localhost ~]# /usr/bin/mysqldump -uroot -p --routines --set-gtid-purged=OFF --databases db_test home mysql > /root/all-database-20240319.sql
Enter password:
2.InnoDB 有哪些关闭模式。
（1）模式
如果值为 0，InnoDB 会在关闭前进行缓慢关闭、完全清除和更改缓冲区合并。
set global innodb_fast_shutdown=0;如果值为 1（默认值），InnoDB 会在关闭时跳过这些操作，这个过程称为快速关闭。
set global innodb_fast_shutdown=1;如果值为 2，InnoDB 刷新其日志并冷关机，就好像 MySQL 崩溃了；没有提交的事务丢失，但崩溃恢复操作使下一次启动需要更长的时间。 在仍然缓冲大量数据的极端情况下，缓慢关闭可能需要几分钟甚至几小时。
set global innodb_fast_shutdown=2;3.master节点执行升级程序报错
(1) 报错
（2）原因分析
查看日志
[root@localhost ~]# tailf -n 20 /var/log/mysqld.log
2024-03-19T15:16:23.939891Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2024-03-19T15:16:24.362405Z 0 [Warning] [MY-011070] [Server] 'binlog_format' is deprecated and will be removed in a future release.
2024-03-19T15:16:24.362496Z 0 [Warning] [MY-011068] [Server] The syntax 'log_slave_updates' is deprecated and will be removed in a future release. Please use log_replica_updates instead.
2024-03-19T15:16:24.362580Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2024-03-19T15:16:24.362600Z 0 [Warning] [MY-011068] [Server] The syntax '--ssl=off' is deprecated and will be removed in a future release. Please use --tls-version='' instead.
2024-03-19T15:16:24.362884Z 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
2024-03-19T15:16:24.362927Z 0 [System] [MY-010116] [Server] /usr/local/mysql8/bin/mysqld (mysqld 8.2.0) starting as process 7067
2024-03-19T15:16:24.370604Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2024-03-19T15:16:24.370621Z 0 [Warning] [MY-013244] [Server] --collation-server: 'utf8mb3_general_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
2024-03-19T15:16:24.393063Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-03-19T15:16:25.071261Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-03-19T15:16:25.345892Z 0 [Warning] [MY-010918] [Repl] 'rpl_semi_sync_master' is deprecated and will be removed in a future release. Please use rpl_semi_sync_source instead.
2024-03-19T15:16:25.346026Z 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001287 - 'validate password plugin' is deprecated and will be removed in a future release. Please use validate_password component instead
2024-03-19T15:16:25.382686Z 4 [System] [MY-013381] [Server] Server upgrade from '80200' to '80200' started.
2024-03-19T15:16:32.072259Z 4 [System] [MY-013381] [Server] Server upgrade from '80200' to '80200' completed.
2024-03-19T15:16:32.447111Z 0 [ERROR] [MY-000067] [Server] unknown variable 'expire_logs_days=7'.
2024-03-19T15:16:32.448921Z 0 [ERROR] [MY-010119] [Server] Aborting
2024-03-19T15:16:34.293524Z 0 [Warning] [MY-011068] [Server] The syntax 'validate password plugin' is deprecated and will be removed in a future release. Please use validate_password component instead.
2024-03-19T15:16:35.317547Z 0 [System] [MY-010910] [Server] /usr/local/mysql8/bin/mysqld: Shutdown complete (mysqld 8.2.0) MySQL Community Server - GPL.
2024-03-19T15:16:35.318861Z 0 [System] [MY-015016] [Server] MySQL Server - end.
从 MySQL 8.0.26 开始，提供了实现半同步复制的新版本插件，一个用于源服务器，一个用于副本服务器。新插件在系统变量和状态变量中将术语“master”和“slave”替换为“source”和“replica”，您可以安装这些版本而不是旧版本。您不能在一个实例上同时安装新旧版本的相关插件。如果你使用新版本的插件，新的系统变量和状态变量可用，旧的不可用。如果您使用旧版本的插件，旧的系统变量和状态变量可用，但新的不可用。
源服务器，旧术语：rpl_semi_sync_master 插件（semisync_master.so 或 semisync_master.dll 库）
源服务器，新术语（来自 MySQL 8.0.26）：rpl_semi_sync_source 插件（semisync_source.so 或 semisync_source.dll 库）
副本，旧术语：rpl_semi_sync_slave 插件（semisync_slave.so 或 semisync_slave.dll 库）
副本，新术语（来自 MySQL 8.0.26）：rpl_semi_sync_replica 插件（semisync_replica.so 或 semisync_replica.dll 库）
（3）解决方法
注释半同步相关配置：
注释自动清除日志时间
添加
binlog_expire_logs_seconds=259200
修改密码插件
default_authentication_plugin=caching_sha2_password
修改字符集
# 指定编码 utf8mb4
character-set-server=utf8mb4
# utf8mb4的排序规则
collation-server=utf8mb4_0900_ai_ci
注释SSL
注释symbolic-links
注释
开启
log_replica_updates= ON
4. slave 节点执行升级程序报错
（1）报错
（2）原因分析
查看日志
[root@localhost lib]# tailf -n 30 /var/log/mysqld.log
2024-03-19T17:45:33.170538Z 0 [System] [MY-010116] [Server] /usr/local/mysql8/bin/mysqld (mysqld 8.2.0) starting as process 9819
2024-03-19T17:45:33.245991Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2024-03-19T17:45:33.246104Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-03-19T17:45:33.261816Z 1 [ERROR] [MY-012209] [InnoDB] Multiple files found for the same tablespace ID:
2024-03-19T17:45:33.261882Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 2 = ['mysql/mysql/plugin.ibd', 'mysql/plugin.ibd']
2024-03-19T17:45:33.261909Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 3 = ['mysql/mysql/servers.ibd', 'mysql/servers.ibd']
2024-03-19T17:45:33.261927Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 4 = ['mysql/help_topic.ibd', 'mysql/mysql/help_topic.ibd']
2024-03-19T17:45:33.261972Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 5 = ['mysql/help_category.ibd', 'mysql/mysql/help_category.ibd']
2024-03-19T17:45:33.261993Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 6 = ['mysql/help_relation.ibd', 'mysql/mysql/help_relation.ibd']
2024-03-19T17:45:33.262009Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 7 = ['mysql/help_keyword.ibd', 'mysql/mysql/help_keyword.ibd']
2024-03-19T17:45:33.262025Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 8 = ['mysql/mysql/time_zone_name.ibd', 'mysql/time_zone_name.ibd']
2024-03-19T17:45:33.262041Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 9 = ['mysql/mysql/time_zone.ibd', 'mysql/time_zone.ibd']
2024-03-19T17:45:33.262057Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 10 = ['mysql/mysql/time_zone_transition.ibd', 'mysql/time_zone_transition.ibd']
2024-03-19T17:45:33.262073Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 11 = ['mysql/mysql/time_zone_transition_type.ibd', 'mysql/time_zone_transition_type.ibd']
2024-03-19T17:45:33.262089Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 12 = ['mysql/mysql/time_zone_leap_second.ibd', 'mysql/time_zone_leap_second.ibd']
2024-03-19T17:45:33.262105Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 13 = ['mysql/innodb_table_stats.ibd', 'mysql/mysql/innodb_table_stats.ibd']
2024-03-19T17:45:33.262120Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 14 = ['mysql/innodb_index_stats.ibd', 'mysql/mysql/innodb_index_stats.ibd']
2024-03-19T17:45:33.262135Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 15 = ['mysql/mysql/slave_relay_log_info.ibd', 'mysql/slave_relay_log_info.ibd']
2024-03-19T17:45:33.262150Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 16 = ['mysql/mysql/slave_master_info.ibd', 'mysql/slave_master_info.ibd']
2024-03-19T17:45:33.262165Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 17 = ['mysql/mysql/slave_worker_info.ibd', 'mysql/slave_worker_info.ibd']
2024-03-19T17:45:33.262195Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 18 = ['mysql/gtid_executed.ibd', 'mysql/mysql/gtid_executed.ibd']
2024-03-19T17:45:33.262216Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 19 = ['mysql/mysql/server_cost.ibd', 'mysql/server_cost.ibd']
2024-03-19T17:45:33.262271Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 20 = ['mysql/engine_cost.ibd', 'mysql/mysql/engine_cost.ibd']
2024-03-19T17:45:33.262292Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 21 = ['mysql/sys/sys_config.ibd', 'sys/sys_config.ibd']
2024-03-19T17:45:33.262325Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Failed, retry may succeed.
2024-03-19T17:45:33.262409Z 1 [ERROR] [MY-011013] [Server] Failed to initialize DD Storage Engine.
2024-03-19T17:45:33.262697Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2024-03-19T17:45:33.262726Z 0 [ERROR] [MY-010119] [Server] Aborting
2024-03-19T17:45:33.263664Z 0 [System] [MY-010910] [Server] /usr/local/mysql8/bin/mysqld: Shutdown complete (mysqld 8.2.0) MySQL Community Server - GPL.
2024-03-19T17:45:33.265091Z 0 [System] [MY-015016] [Server] MySQL Server - end.
（3）解决方法
删除重复的库。
成功：
5.监视半同步复制的插件状态报错
（1）报错
Rpl_semi_sync_source_clients 连接到源服务器的半同步副本的数量 为0
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_source_clients | 0 |
| Rpl_semi_sync_source_net_avg_wait_time | 0 |
| Rpl_semi_sync_source_net_wait_time | 0 |
| Rpl_semi_sync_source_net_waits | 0 |
| Rpl_semi_sync_source_no_times | 0 |
| Rpl_semi_sync_source_no_tx | 0 |
| Rpl_semi_sync_source_status | ON |
| Rpl_semi_sync_source_timefunc_failures | 0 |
| Rpl_semi_sync_source_tx_avg_wait_time | 0 |
| Rpl_semi_sync_source_tx_wait_time | 0 |
| Rpl_semi_sync_source_tx_waits | 0 |
| Rpl_semi_sync_source_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_source_wait_sessions | 0 |
| Rpl_semi_sync_source_yes_tx | 0 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
（2）原因分析
配置文件错误。
测试发现从节点出现未知变量：
2024-03-19T18:59:42.762527Z 0 [ERROR] [MY-000067] [Server] unknown variable 'rpl_semi_sync_source_enabled=1'.
（3）解决方法
修改slave1 与 slave2 配置文件。
修改前：
修改后：
成功：（Rpl_semi_sync_source_clients 连接到源服务器的半同步副本的数量变为2）
