架构	当前版本	目标版本	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) 修改配置文件

对原有5.7的配置文件，新增如下配置mysql8.0的配置项：


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_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.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）

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/凡人多烦事01/article/detail/296835

Linux 服务升级：MySQL 主从(半同步复制) 平滑升级

一、实验

1.环境

2.Mysql-shell 检查工具兼容性

3.逻辑备份MySQL数据

4.备份MySQL 数据目录、安装目录、配置文件

5.MySQL 升级

6.master节点 使用systemd管理mysql8

7. slave1 节点升级

8. slave2 节点升级

9.半同步设置

二、问题

1.mysqldump备份报错

2.InnoDB 有哪些关闭模式。

3.master节点执行升级程序报错

4. slave 节点执行升级程序报错

5.监视半同步复制的插件状态报错

6.master节点使用systemd管理mysql8