一、升级注意事项:
官网文档: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