单机测试环境Mysql5.7.34升级Mysql8.0.27实践

一、升级注意事项：
官网文档：Upgrade from MySQL 5.7 to 8.0 is supported. However, upgrade is only supported between General
Availability (GA) releases. For MySQL 8.0, it is required that you upgrade from a MySQL 5.7 GA
release (5.7.9 or higher). Upgrades from non-GA releases of MySQL 5.7 are not supported.
支持从5.7.9之后的版本升级8.0
二、inplace升级过程(生产思路)
1.部署新版本的数据库软件
2. 设置参数：innodb_fast_shutdown=1，然后关闭数据库。 #表示不干净的关闭数据库，建议设置0，表示干净的关闭，该落盘的数据都落盘
3.冷备份数据库数据
4.使用新版本数据库，拉起旧版本的数据进行启动，参数（–skip-grant-tables --skip-networking）
5.启动完成后，验证业务功能
6.恢复业务，升级完成。
三、inplace升级到8.0
5.7.34升级到8.0.27。
注意：注意备份，冷备！
3.1 准备工作
1、预检查，8.0新特性：mysql8.0有检查工具：mysql-shell,升级前通过该工具检查当前版本是否具备条件升级到8.0，下载：https://downloads.mysql.com/archives/shell/。也可以yum安装：
yum install -y mysql-shell-8.0.27-1.el7.x86_64.rpm
注意：要升级到8.0的哪个版本，建议下载哪个版本的mysql-shell
2、部署mysql-shell

  [root@localhost local]# tar -xf mysql-shell-8.0.24-linux-glibc2.12-x86-64bit.tar.gz
  [root@localhost local]# ln -s /home/mysql/opt/mysql-shell-8.0.24-linux-glibc2.12-x86-64bit /usr/local/mysqlsh

配置环境变量：

[root@localhost local]# vim /etc/profile
export PATH=/usr/local/mysqlsh/bin:$PATH

3、检查

mysqlsh root:123@10.0.0.51:20001 -e "util.checkForServerUpgrade()"

或

mysqlsh -uroot -p123 -S /home/mysql/db_ngcsf/mysql.sock -e "util.checkForServerUpgrade()"

例如：

[root@localhost ~]# mysqlsh -uroot -p123 -S /tmp/mysql.sock -e "util.checkForServerUpgrade()"
输出信息省略
Errors:   0  # 检查是否有错误，如果没有，则可以进行升级
Warnings: 1
Notices:  1

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 ~]# 

3.2 部署新版本的MySQL(8.0)

tar -zxvf mysql-8.0.27-el7-x86_64.tar.gz

3.3 停服务
设置：

mysql> select @@innodb_fast_shutdown;
+------------------------+
| @@innodb_fast_shutdown |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

mysql> set global innodb_fast_shutdown=0;
Query OK, 0 rows affected (0.05 sec)

停服务：

[root@localhost ~]# /home/mysql/mysql-5.7.34/bin/mysqladmin -uroot -p123 -S /home/mysql/db_ngcsf/mysql.sock shutdown

3.4 备份数据
3.5 升级
使用8.0版本软件挂5.7版本数据启动

/home/mysql/mysql-8.0.27/bin/mysqld_safe --defaults-file=/home/mysql/db_ngcsf/conf/my.cnf --skip-grant-tables --skip-networking &

另一个会话窗口执行：

tail -f /home/mysql/db_ngcsf/elog/mysql.err

查看日志有报错：

[ERROR] [MY-010340] [Server] Error message file '/home/mysql/mysql-5.7.34/share/english/errmsg.sys' had only 1121 error messages, but it should contain at least 5522 error messages. Check that the above file is the right version for this program!
...
[ERROR] [MY-010901] [Server] Can't open shared library '/home/mysql/mysql-5.7.34/lib/plugin/semisync_master.so' (errno: 0 
/home/mysql/mysql-5.7.34/lib/plugin/semisync_master.so: undefined symbol: PSI_server).
[ERROR] [MY-010736] [Server] Couldn't load plugin named 'rpl_semi_sync_master' with soname 'semisync_master.so'.
...
[ERROR] [MY-000067] [Server] unknown variable 'query_cache_type=OFF'.

修改my.cnf配置文件，注释掉以下参数：

#plugin_load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
#loose_rpl_semi_sync_master_enabled=1
#loose_rpl_semi_sync_slave_enabled=1
#loose_rpl_semi_sync_master_timeout=5000
#query_cache_type                =OFF
#query_cache_size                =0
#sql_mode                        =NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO
#internal_tmp_disk_storage_engine    =InnoDB

再次执行以下命令，成功

/home/mysql/mysql-8.0.27/bin/mysqld_safe --defaults-file=/home/mysql/db_ngcsf/conf/my.cnf --skip-grant-tables --skip-networking &

连接测试：

/home/mysql/mysql-5.7.34/bin/mysql -uroot -p -S /home/mysql/db_ngcsf/mysql.sock
[mysql@node2 bin]$ ./mysql -uroot -p -S /home/mysql/db_ngcsf/mysql.sock
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, 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> 

3.6 重启服务
1、升级完成后，还处理安全模式，因为在拉起数据的库的时候加了参数“–skip-grant-tables --skip-networking”，此时需要重启数据库，且配置文件中的basedir可以调整成新版本的
修改my.cnf配置：

basedir=/home/mysql/mysql-8.0.27
default_authentication_plugin=mysql_native_password

2、重启服务

###关闭服务
/home/mysql/mysql-5.7.34/bin/mysqladmin -uroot -p -S /home/mysql/db_ngcsf/mysql.sock shutdown
###启动服务
/home/mysql/mysql-8.0.27/bin/mysqld_safe --defaults-file=/home/mysql/db_ngcsf/conf/my.cnf &
[1] 20109
[mysql@node2 bin]$ 2022-02-22T14:58:22.421703Z mysqld_safe Logging to '/home/mysql/db_ngcsf/elog/mysql.err'.
2022-02-22T14:58:22.444462Z mysqld_safe Starting mysqld daemon with databases from /home/mysql/db_ngcsf/data

3、验证

[mysql@node2 bin]$ ./mysql -uroot -p -S /home/mysql/db_ngcsf/mysql.sock
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, 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> 
mysql> 
mysql> 
mysql> use db_ngcsf;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 
mysql> 
mysql> select * from test;
Empty set (0.00 sec)

mysql> insert into test values(1,'apple');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> 
mysql> 

至此，完成升级。

另：
1、主从模式或者主主模式中配置的主从相关参数，会有以下告警信息

2022-02-25T14:08:52.777054+08:00 0 [Warning] [MY-011068] [Server] The syntax 'expire-logs-days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
2022-02-25T14:08:52.777144+08:00 0 [Warning] [MY-011068] [Server] The syntax 'master_verify_checksum' is deprecated and will be removed in a future release. Please use source_verify_checksum instead.
2022-02-25T14:08:52.777155+08:00 0 [Warning] [MY-011068] [Server] The syntax 'sync_master_info' is deprecated and will be removed in a future release. Please use sync_source_info instead.
2022-02-25T14:08:52.777164+08:00 0 [Warning] [MY-011068] [Server] The syntax 'skip_slave_start' is deprecated and will be removed in a future release. Please use skip_replica_start instead.
2022-02-25T14:08:52.777173+08:00 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.
2022-02-25T14:08:52.777187+08:00 0 [Warning] [MY-011068] [Server] The syntax 'slave_load_tmpdir' is deprecated and will be removed in a future release. Please use replica_load_tmpdir instead.
2022-02-25T14:08:52.777195+08:00 0 [Warning] [MY-011068] [Server] The syntax 'slave_sql_verify_checksum' is deprecated and will be removed in a future release. Please use replica_sql_verify_checksum instead.
2022-02-25T14:08:52.777202+08:00 0 [Warning] [MY-011068] [Server] The syntax 'slave_preserve_commit_order' is deprecated and will be removed in a future release. Please use replica_preserve_commit_order instead.
2022-02-25T14:08:52.777209+08:00 0 [Warning] [MY-011068] [Server] The syntax 'slave_parallel_type' is deprecated and will be removed in a future release. Please use replica_parallel_type instead.
2022-02-25T14:08:52.777215+08:00 0 [Warning] [MY-011068] [Server] The syntax 'slave_parallel_workers' is deprecated and will be removed in a future release. Please use replica_parallel_workers instead.

建议按照告警修改参数名字
expire-logs-days修改为binlog_expire_logs_seconds，注意单位是秒
master_verify_checksum修改为source_verify_checksum
sync_master_info修改为sync_source_info
skip_slave_start修改为skip_replica_start
log_slave_updates修改为log_replica_updates
slave_load_tmpdir修改为replica_load_tmpdir
slave_sql_verify_checksum修改为replica_sql_verify_checksum
slave_preserve_commit_order修改为replica_preserve_commit_order
slave_parallel_type修改为replica_parallel_type
slave_parallel_workers修改为replica_parallel_workers
半同步参数中的master修改为source，slave修改为replica：
rpl_semi_sync_source_enabled =1
rpl_semi_sync_source_timeout =1000
rpl_semi_sync_source_trace_level =32
rpl_semi_sync_source_wait_for_replica_count =1
rpl_semi_sync_source_wait_no_replica =1
rpl_semi_sync_source_wait_point =AFTER_SYNC
rpl_semi_sync_replica_enabled =ON
rpl_semi_sync_replica_trace_level =32

如果配置了半同步参数，mysql8.0启动时需要将参数注释掉，不然不报错，无效的参数，启动半同步需要参考以下链接https://blog.csdn.net/y_18800130246/article/details/123138332