Linux 服务升级：MySQL 主从(半同步复制) 平滑升级
目录
一、实验
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 eventMaster_Host: 192.168.204.10Master_User: myslaveMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000007Read_Master_Log_Pos: 1372Relay_Log_File: relay-log-bin.000047Relay_Log_Pos: 950Relay_Master_Log_File: mysql-bin.000007Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 1372Relay_Log_Space: 1956Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1Master_UUID: c8246fd9-1c99-11ee-af46-000c29747129Master_Info_File: /var/lib/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_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 eventMaster_Host: 192.168.204.10Master_User: myslaveMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000007Read_Master_Log_Pos: 1372Relay_Log_File: relay-log-bin.000025Relay_Log_Pos: 320Relay_Master_Log_File: mysql-bin.000007Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 1372Relay_Log_Space: 1321Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1Master_UUID: c8246fd9-1c99-11ee-af46-000c29747129Master_Info_File: /var/lib/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_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 typeNo issues found2) MySQL 8.0 syntax check for routine-like objectsNo issues found3) Usage of db objects with names conflicting with new reserved keywordsNo issues found4) Usage of utf8mb3 charsetNo issues found5) Table names in the mysql schema conflicting with new tables in 8.0No issues found6) Partitioned tables using engines with non native partitioningNo issues found7) Foreign key constraint names longer than 64 charactersNo issues found8) Usage of obsolete MAXDB sql_mode flagNo issues found9) Usage of obsolete sql_mode flagsNotice: The following DB objects have obsolete options persisted forsql_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-removalsglobal system variable sql_mode - defined using obsolete NO_AUTO_CREATE_USERoption10) ENUM/SET column definitions containing elements longer than 255 charactersNo issues found11) Usage of partitioned tables in shared tablespacesNo issues found12) Circular directory references in tablespace data file pathsNo issues found13) Usage of removed functionsNo issues found14) Usage of removed GROUP BY ASC/DESC syntaxNo issues found15) Removed system variables for error logging to the system log configurationTo run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionaryMore information:https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-13.html#mysqld-8-0-13-logging16) Removed system variablesTo run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionaryMore information:https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed17) System variables with new default valuesTo run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionaryMore information:https://mysqlserverteam.com/new-defaults-in-mysql-8-0/18) Zero Date, Datetime, and Timestamp valuesNo issues found19) Schema inconsistencies resulting from file removal or corruptionNo issues found20) Tables recognized by InnoDB that belong to a different engineNo issues found21) Issues reported by 'check table x for upgrade' commandNo issues found22) New default authentication plugin considerationsWarning: The new default authentication plugin 'caching_sha2_password' offersmore secure password hashing than previously used 'mysql_native_password'(and consequent improved client connection authentication). However, it alsohas compatibility implications that may affect existing MySQL installations.If your MySQL installation must serve pre-8.0 clients and you encountercompatibility issues after upgrading, the simplest way to address thoseissues is to reconfigure the server to revert to the previous defaultauthentication plugin (mysql_native_password). For example, use these linesin the server option file:[mysqld]default_authentication_plugin=mysql_native_passwordHowever, the setting should be viewed as temporary, not as a long term orpermanent solution, because it causes new accounts created with the settingin effect to forego the improved authentication security.If you are using replication please take time to understand how theauthentication 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-issueshttps://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replication23) Columns which cannot have default valuesNo issues found24) Check for invalid table names and schema names used in 5.7No issues found25) Check for orphaned routines in 5.7No issues found26) Check for deprecated usage of single dollar signs in object namesNo issues found27) Check for indexes that are too large to work on higher versions of MySQL
Server than 5.7No issues found28) Check for deprecated '.<table>' syntax used in routines.No issues found29) Check for columns that have foreign keys pointing to tables from a diffrent
database engine.No issues foundErrors: 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= ONbinlog_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_passworddefault-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 - GPLCopyright (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 ServerDocumentation=man:mysqldDocumentation=http://dev.mysql.com/doc/refman/en/using-systemd.htmlAfter=network.targetAfter=syslog.target[Install]WantedBy=multi-user.target[Service]User=mysqlGroup=mysqlExecStart=/usr/local/mysql8/bin/mysqld --defaults-file=/etc/my.cnfLimitNOFILE = 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 - GPLCopyright (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 - GPLCopyright (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 - GPLCopyright (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_source_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.000013Position: 157Binlog_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 eventMaster_Host: 192.168.204.10Master_User: myslaveMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000013Read_Master_Log_Pos: 157Relay_Log_File: relay-log-bin.000059Relay_Log_Pos: 373Relay_Master_Log_File: mysql-bin.000013Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 157Relay_Log_Space: 750Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1Master_UUID: c8246fd9-1c99-11ee-af46-000c29747129Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_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 eventMaster_Host: 192.168.204.10Master_User: myslaveMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000013Read_Master_Log_Pos: 157Relay_Log_File: relay-log-bin.000037Relay_Log_Pos: 373Relay_Master_Log_File: mysql-bin.000013Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 157Relay_Log_Space: 750Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1Master_UUID: c8246fd9-1c99-11ee-af46-000c29747129Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_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）
相关文章：
Linux 服务升级：MySQL 主从(半同步复制) 平滑升级
目录 一、实验 1.环境 2.Mysql-shell 检查工具兼容性 3.逻辑备份MySQL数据 4.备份MySQL 数据目录、安装目录、配置文件 5.MySQL 升级 6.master节点 使用systemd管理mysql8 7. slave1 节点升级 8. slave2 节点升级 9.半同步设置 二、问题 1.mysqldump备份报错 2.Inn…...
python与excel第一节
python与excel第一节 由于excel在日常办公中大量使用，我们工作中常常会面对高频次或者大量数据的情况。使用python语言可以更加便捷的处理excel。 python与vba的比较 python语法更加简洁，相较于vba冗长复杂的语法，python更加容易学习。 p…...
开发者必备神器 | 全能AI工具助你免费提升开发效率，每日轻松编写代码
全能AI工具助你免费提升开发效率，每日轻松编写代码 前提介绍CodeGeex多语言生成模型支持的编程语言适配多种主流IDE多种IDE插件支持安装VS Code的CodeGeeX插件安装Jetbrains IDEs插件(IntelliJ IDEA,PyCharm)功能实现1. 自动生成和补全代码2. 多语言的代码翻译3. 自动添加注释…...
【RabbitMQ | 第七篇】RabbitMQ实现JSON、Map格式数据的发送与接收
文章目录 7.RabbitMQ实现JSON、Map格式数据的发送与接收7.1消息发送端7.1.1引入依赖7.1.2yml配置7.1.3RabbitMQConfig配置类——（非常重要）（1）创建交换器方法（2）创建队列方法（3）绑定…...
ios symbolicatecrash 符号化crash
一、准备 1.1 .crash 文件获取 设备连接电脑 打开XCode， 依次 XCode -> Windows -> Device and Simulator -> Open Recent Logs 找到 (对应app名+时间点) -> 右键 Show in Finder 1.2 .dSYM 和 .app 文件获取 .dSYM是十六进制函数地址映射信息的中转文件，调试的…...
Rust 语言的 HashMap
HashMap 在 Rust 中是一个非常常用且强大的数据结构，它允许你存储键值对(key-value pairs)，并且能够快速地基于键检索值。 下面是使用 HashMap 的一些基本示例： 首先，你需要在你的文件中引入 HashMap： use std::col…...
【目标检测基础篇】目标检测评价指标：mAP计算的超详细举例分析以及coco数据集标准详解（AP/AP50/APsmall.....)）
学习视频： 霹雳吧啦Wz-目标检测mAP计算以及coco评价标准 【目标检测】指标介绍：mAP 1 TP/FP/FN TP(True Positive) : IoU>0.5的检测框数量(同一Ground truth只计算一次)FP(False Positive) : IoU<0.5的检测框(或者是检测到同一个GT的多余检测框的…...
服务器与普通电脑的区别，普通电脑是否可以作为服务器使用
服务器在我们日常应用中非常常见，手机APP、手机游戏、PC游戏、小程序、网站等等都需要部署在服务器上，为我们提供各种计算、应用服务。服务器也是计算机的一种，虽然内部结构相差不大，但是服务器的运行速度更快、负载更高、成本更高…...
长安链Docker Java智能合约引擎的架构、应用与规划
#功能发布 长安链3.0正式版发布了多个重点功能，包括共识算法切换、支持java智能合约引擎、支持后量子密码、web3生态兼容等。我们接下来为大家详细介绍新功能的设计、应用与规划。 在《2022年度长安链开源社区开发者调研报告》中，对Java合约语言支持是开…...
STM32 ESP8266模块的曲折探索
这是本文的配套资料，最终工程请参考 新_ESP8266资料\stm32f103成功移植的项目 【免费】stm32f103c8t6esp8266资料资源-CSDN文库 一、等到了ready 产品参数 我使用的是ai-thinker的esp8266-01s，以下为产品规格书 引脚定义： 依据引脚定义&…...
letcode::根据二叉树创建字符串
根据二叉树创建字符串 题目描述： 给你二叉树的根节点 root ，请你采用前序遍历的方式，将二叉树转化为一个由括号和整数组成的字符串，返回构造出的字符串。 空节点使用一对空括号对 “()” 表示，转化后需要省略所有不影…...
6个免费的ChatGPT网站
AI 大模型的出现给时代带来了深远的影响： 改变了产业格局：AI 大模型的发展推动了人工智能技术在各行业的广泛应用，改变了传统产业的运作方式，促进了新兴产业的崛起，如智能驾驶、医疗健康、金融科技等。提升了科学研究…...
每天几道面试题｜Kafka（一）基础概念
文章目录 什么是 Apache Kafka？它是用来解决什么问题的？Kafka 的主要组件有哪些？它们各自的作用是什么？Kafka 中的生产者和消费者是什么？它们之间的关系是怎样的？Kafka 中的分区是什么？为什么要…...
PLC与智能制造——蛋糕增大？谁来先行？
PLC的特点 图1 PLC的特点 PLC与智能制造 “中国制造2025”把智能制造作为自动化和信息化深度融合的主攻方向，其支撑在于强大的工业自动化系统，而PLC是工业自动化系统的“大脑”，它不仅可控制机械装备和生产线，还是信息的采集器和…...
基于spring boot框架的发艺美发店管理系统
摘 要 系统根据现有的管理模块进行开发和扩展，采用面向对象的开发的思想和结构化的开发方法对发艺美发店管理的现状进行系统调查。采用结构化的分析设计，该方法要求结合一定的图表，在模块化的基础上进行系统的开发工作。在设计中采用“自下而…...
Linux - IO
目录 四种典型IO方式阻塞IO非阻塞IO信号驱动异步IO 多路转接IOselect模型接口 四种典型IO方式 IO：输入输出–过程：等待IO就绪，进行数据拷贝 阻塞：为了完成某功能，发起一个调用，若完成功能条件不具备&#…...
Cmake和opencv环境安装
1 Cmake下载及安装 Download CMake 根据需要下载，历史版本下载方法如下 CMake 的版本号中的后缀 "rc1" 和 "rc2" 表示 Release Candidate 1 和 Release Candidate 2，它们都是候选版本，用于测试新功能和修复 bug。通常情…...
Redis是如何避免“数组+链表”的过长问题
目录 一、扩展和收缩 二、使用高质量的哈希函数 三、使用跳跃表（skiplist）或其他数据结构 四、哈希表分片 一、扩展和收缩 Redis通过动态调整哈希表的大小来解决“数组链表”的长度问题，这涉及到两个过程：扩展(Expand)和收缩(S…...
Grass手机注册使用教程，利用闲置手机WiFi带宽赚钱
文章目录 Grass是什么？ 项目介绍Grasss手机使用步骤第一步：下载狐猴浏览器第二步：注册账户（已注册直接跳过）第三步：安装Grass Chrome插件1、推荐离线安装2、在线安装 第四步：登录第五步…...
java NIO群聊系统
demo要求： 1）编写一个NIO群聊系统，实现服务器端和客户端之间的数据简单通讯（非阻塞） 2）实现多人群聊 3）服务器端：可以监测用户上线，离线，并实现消息转发功…...
（十）学生端搭建
本次旨在将之前的已完成的部分功能进行拼装到学生端，同时完善学生端的构建。本次工作主要包括： 1.学生端整体界面布局 2.模拟考场与部分个人画像流程的串联 3.整体学生端逻辑 一、学生端 在主界面可以选择自己的用户角色 选择学生则进入学生登录界面…...
逻辑回归：给不确定性划界的分类大师
想象你是一名医生。面对患者的检查报告（肿瘤大小、血液指标），你需要做出一个**决定性判断**：恶性还是良性？这种“非黑即白”的抉择，正是**逻辑回归（Logistic Regression）** 的战场&a…...
遍历 Map 类型集合的方法汇总
1 方法一 先用方法 keySet() 获取集合中的所有键。再通过 gey(key) 方法用对应键获取值 import java.util.HashMap; import java.util.Set;public class Test {public static void main(String[] args) {HashMap hashMap new HashMap();hashMap.put("语文",99);has…...
mongodb源码分析session执行handleRequest命令find过程
mongo/transport/service_state_machine.cpp已经分析startSession创建ASIOSession过程，并且验证connection是否超过限制ASIOSession和connection是循环接受客户端命令，把数据流转换成Message，状态转变流程是：State::Created 》 St…...
五年级数学知识边界总结思考-下册
目录 一、背景二、过程1.观察物体小学五年级下册“观察物体”知识点详解：由来、作用与意义**一、知识点核心内容****二、知识点的由来：从生活实践到数学抽象****三、知识的作用：解决实际问题的工具****四、学习的意义：培养核心素养…...
江苏艾立泰跨国资源接力：废料变黄金的绿色供应链革命
在华东塑料包装行业面临限塑令深度调整的背景下，江苏艾立泰以一场跨国资源接力的创新实践，重新定义了绿色供应链的边界。 跨国回收网络：废料变黄金的全球棋局 艾立泰在欧洲、东南亚建立再生塑料回收点，将海外废弃包装箱通过标准…...
[10-3]软件I2C读写MPU6050 江协科技学习笔记（16个知识点）
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16...
【学习笔记】深入理解Java虚拟机学习笔记——第4章 虚拟机性能监控，故障处理工具
第2章 虚拟机性能监控，故障处理工具 4.1 概述 略 4.2 基础故障处理工具 4.2.1 jps:虚拟机进程状况工具 命令：jps [options] [hostid] 功能：本地虚拟机进程显示进程ID（与ps相同），可同时显示主类&#x…...
中关于正整数输入的校验规则)
Element Plus 表单(el-form)中关于正整数输入的校验规则
目录 1 单个正整数输入1.1 模板1.2 校验规则 2 两个正整数输入（联动）2.1 模板2.2 校验规则2.3 CSS 1 单个正整数输入 1.1 模板 <el-formref"formRef":model"formData":rules"formRules"label-width"150px"…...
算法笔记2
1.字符串拼接最好用StringBuilder，不用String 2.创建List<>类型的数组并创建内存 List arr[] new ArrayList[26]; Arrays.setAll(arr, i -> new ArrayList<>()); 3.去掉首尾空格...