MYSQL5.7.39 升级到 MYSQL8.0.33

1 下载  

mysql-shell-8.0.37-linux-glibc2.12-x86-64bit.tar.gz

https://dev.mysql.com/downloads/shell/

2 安装mysql-shell

#上传mysql-shell安装包，并安装
#tar -xvf mysql-shell-8.0.37-linux-glibc2.12-x86-64bit.tar.gz
#cd /data/app
#mv mysql-shell-8.0.37-linux-glibc2.12-x86-64bit mysql_shell
#echo "PATH=/data/app/mysql_shell/bin:\$PATH">> /etc/profile
#mysqlsh -version

mysqlsh   Ver 8.0.37 for Linux on x86_64 - for MySQL 8.0.37 (MySQL Community Server (GPL))

3 查询数据量

SELECT TABLE_SCHEMA,
            round(SUM(data_length+index_length)/1024/1024,2) AS TOTAL_MB,
            round(SUM(data_length)/1024/1024,2) AS DATA_MB,
            round(SUM(index_length)/1024/1024,2) AS INDEX_MB,
            COUNT(*) AS TABLES
     FROM INFORMATION_SCHEMA.tables
     WHERE TABLE_SCHEMA NOT IN ('sys',
                                'mysql',
                                'INFORMATION_SCHEMA',
                               'performance_schema')
     GROUP BY TABLE_SCHEMA
     ORDER BY 2 DESC;

4 创建备份目录，保证目录足够大

mkdir -p /tmp/bak

5 备份数据库

mysqlsh root@localhost -S /tmp/mysql.sock -- util dump-instance '/tmp/bak' --threads=16
 

6 检查MySQL是否满足升级条件

#mysqlsh root:abcd.1234@localhost:3306 -e "util.checkForServerUpgrade();"

Errors:   0
Warnings: 69
Notices:  1

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.

Errors:   0  表示可以升级

7    登录mysql命令行，设置参数并关闭MySQL服务

mysql> select version();

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.39    |
+-----------+
mysql> show variables like 'innodb_fast_shutdown';
mysql> set global innodb_fast_shutdown=0;
mysql> shutdown;

8 确认mysqld进程不存在，无 3306端口监听

ps -ef | grep mysqld
netstat -antup | grep 3306

9 移动原MySQL5.7安装目录到old目录

#mv /data/pitp/mysql /data/pitp/mysql57_old

10    准备MySQL 8.0 程序文件

解压mysql 8.0 二进制安装包，准备mysql8.0程序目录 
#tar -xvf mysql-8.0.33-linux-glibc2.12-x86_64.tar  
#mv mysql-8.0.33-linux-glibc2.12-x86_64 mysql  
#chown -R mysql:mysql mysql 

11 数据目录，软连接到新库中

ln -s /data/pitp/mysql57_old/data /data/pitp/mysql/data 

12 根据需要修改my.cnf 参数，并启动数据库

#/mysql/app/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &

2024-05-13T02:15:44.066347Z mysqld_safe Logging to '/data/pitp/mysql/data/KYMYSQL.err'.
2024-05-13T02:15:44.124974Z mysqld_safe Starting mysqld daemon with databases from /data/pitp/mysql/data
2024-05-13T02:15:44.616083Z 0 [System] [MY-010116] [Server] /data/pitp/mysql/bin/mysqld (mysqld 8.0.33) starting as process 2830237
2024-05-13T02:15:44.638159Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2024-05-13T02:15:44.638205Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-05-13T02:15:45.709312Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-05-13T02:15:51.882519Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2024-05-13T02:15:59.850131Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80033' started.
2024-05-13T02:16:19.465053Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80033' completed.
2024-05-13T02:16:19.588374Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-05-13T02:16:19.588423Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-05-13T02:16:19.607814Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
2024-05-13T02:16:19.607915Z 0 [System] [MY-010931] [Server] /data/pitp/mysql/bin/mysqld: ready for connections. Version: '8.0.33'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server - GPL.
 

13 升级完成，检查状态

#ps -ef | grep mysqld
#netstat -antup | grep 3306

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.33    |
+-----------+
1 row in set (0.00 sec)

mysql> show engine innodb status;
+--------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------