1、MySQL5.7升级8.0

原MySQL5.7安装路径

  • 注意：${datadir}也就是数据路径，原来是什么，升级时就用什么，如果和原来路径不一样，升级时会报错
[root@dbs-slave opt]# cd mysql
[root@dbs-slave mysql]# ls
backup  bin  data  docs  include  lib  LICENSE  log  man  nohup.out  README  share  support-files
1.1 预检查

使用mysql-shell工具检查当前5.7版本是否具备条件升级到8.0，要升级到8.0的哪个版本，建议下载哪个版本的mysql-shell。

下载地址：https://downloads.mysql.com/archives/shell/

[root@dbs-slave ~]# tar -zxvf mysql-shell-8.0.30-linux-glibc2.12-x86-64bit.tar.gz
[root@dbs-slave ~]# mv mysql-shell-8.0.30-linux-glibc2.12-x86-64bit mysql-shell-8.0.30
[root@dbs-slave ~]# cd mysql-shell-8.0.30/bin/
[root@dbs-slave bin]# ./mysqlsh -uroot -proot -S /opt/mysql/data/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.
1.2 停止原5.7数据库

切换到对应用户下，登录MySQL5.7数据库内

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

设置参数值为0，干净的关闭

mysql> set global innodb_fast_shutdown=0;
innodb_fast_shutdown = 0。这个表示在MySQL关闭的时候，执行slow shutdown，不但包括日志的刷盘，数据页的刷盘，还包括数据的清理(purge)，ibuf的合并，buffer pool dump以及lazy table drop操作(如果表上有未完成的操作，即使执行了drop table且返回成功了，表也不一定立刻被删除)。 
innodb_fast_shutdown = 1。这个是默认值，表示在MySQL关闭的时候，仅仅把日志和数据刷盘。 
innodb_fast_shutdown = 2。这个表示关闭的时候，仅仅日志刷盘，其他什么都不做，就好像MySQL crash了一样。 
这个参数值越大，MySQL关闭的速度越快，但是启动速度越慢，相当于把关闭时候需要做的工作挪到了崩溃恢复上。另外，如果MySQL要升级，建议使用第一种方式进行一次干净的shutdown。

关闭5.7数据库

[mysql@dbs-slave ~]$ mysqladmin -uroot -proot -S /opt/mysql/data/mysql.sock shutdown
1.4 冷备份
[root@dbs-slave ~]# cp /opt/mysql/ /opt/mysql57_backup
1.5 安装MySQL8.0数据库
[root@dbs-slave ~]# tar -xvf mysql-8.0.30-linux-glibc2.12-x86_64.tar.xz
[root@dbs-slave mysql]# cd mysql && rm bin/ docs/ include/ lib/ man/ share/ support-files/ -rf
[root@dbs-slave ~]# mv /opt/mysql-8.0.30-linux-glibc2.12-x86_64/* /opt/mysql/
[root@dbs-slave ~]# chown mysql:mysql /opt/mysql/ -R

在配置文件/etc/my.cnf中添加：

[mysqld]
default_authentication_plugin=mysql_native_password
1.6 升级

使用MySQL8.0软件直接挂原数据启动

[root@dbs-slave opt]# su - mysql
[mysql@dbs-slave bin]$ ./mysqld_safe --skip-grant-tables --skip-networking &
[mysql@dbs-slave bin]$ tail -f /opt/mysql/log/mysqld.log
2023-01-30T09:36:10.305263Z mysqld_safe Logging to '/opt/mysql/log/mysqld.log'.
2023-01-30T09:36:10.383694Z mysqld_safe Starting mysqld daemon with databases from /opt/mysql/data
2023-01-30T09:36:10.922659Z 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.
2023-01-30T09:36:10.922958Z 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
2023-01-30T09:36:10.922995Z 0 [System] [MY-010116] [Server] /opt/mysql/bin/mysqld (mysqld 8.0.30) starting as process 6425
2023-01-30T09:36:10.959125Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2023-01-30T09:36:10.959177Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-01-30T09:36:24.970402Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-01-30T09:41:28.468528Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2023-01-30T09:42:29.824424Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80030' started.
2023-01-30T09:45:25.825058Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80030' completed.
2023-01-30T09:45:28.681952Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2023-01-30T09:45:28.682084Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2023-01-30T09:45:33.421914Z 8 [Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2023-01-30T09:45:33.424078Z 0 [System] [MY-010931] [Server] /opt/mysql/bin/mysqld: ready for connections. Version: '8.0.30'  socket: '/opt/mysql/data/mysql.sock'  port: 0  MySQL Community Server - GPL.
2023-01-30T09:45:33.424025Z 8 [System] [MY-010562] [Repl] Slave I/O thread for channel '': connected to master 'slave@10.131.54.83:3306',replication started in log 'mysql-bin.000168' at position 148603768
2023-01-30T09:45:33.424289Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: /tmp/mysqlx.sock

连接测试

[mysql@dbs-slave ~]$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 8.0.30 MySQL Community Server - GPL

Copyright (c) 2000, 2022, 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.
1.7 重启8.0数据库
[mysql@dbs-slave bin]$ mysqladmin -uroot -proot -S /opt/mysql/data/mysql.sock shutdown
[mysql@dbs-slave support-files]$ ./mysql.server start

2、MySQL5.6升级5.7

  • 数据库升级记得备份，在数据库停止时，拷贝一份日志和data数据目录进行备份，以便升级失败恢复。
2.1、关闭mysql5.6数据库
# 设置参数，干净的关闭
mysql> set global innodb_fast_shutdown=0;
$ ./mysqladmin -S /opt/mysql56/mysql56.sock shutdown
2.2、安装mysql5.7数据库，不用做数据库初始化操作，直接挂mysql5.6的data目录启动
$ ./mysqld_safe --skip-grant-tables --skip-networking &
2.3、数据库启动成功后，升级系统表
$ ./mysql_upgrade -S /opt/mysql56/mysql56.sock --force
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                 OK
....
sys.sys_config                                     OK
Upgrade process completed successfully.
Checking if update is needed.
2.4、重启数据库
# ./mysqladmin -S /opt/mysql56/mysql56.sock shutdown
# ./mysql.server start
2.5、进入数据库验证