MySQL5.7升级到8.0.36

MySQL5.7升级到8.0.36

一.升级前检查

二.升级中

三.升级后

四.回滚

一.升级前检查

mysql版本升级，升级方式有就地升级和逻辑升级2种方法，本次使用in-place就地升级.

In-Place-Upgrade（就地升级）：就地升级关闭旧的MySQL服务器，用MySQL8的文件夹替换MySQL5.7文件夹，重新启动MySQL，以及升级现有安装中需要升级的任何剩余部分。逻辑升级有几种：一般常用的是mysqldump导入导出以及搭建逻辑备库.

1.1 使用mysqlsh升级前检查

根据自己的操作系统选择版本进行下载mysql shell，升级前的数据库检查

ldd 2.28下载地址：https://cdn.mysql.com//Downloads/MySQL-Shell/mysql-shell-8.0.36-linux-glibc2.28-x86-64bit.tar.gz

ldd 2.17下载地址：https://cdn.mysql.com//Downloads/MySQL-Shell/mysql-shell-8.0.36-linux-glibc2.17-x86-64bit.tar.gz

linux查看glibc版本

ldd --version

[root@node1 ~]# ldd --version

ldd (GNU libc) 2.17

Copyright © 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.

Written by Roland McGrath and Ulrich Drepper.

1.2 上传到服务器，解压tar包，并添加到环境变量中

cd /data/software

tar -xvf mysql-shell-8.0.36-linux-glibc2.17-x86-64bit.tar.gz

mv mysql-shell-8.0.36-linux-glibc2.17-x86-64bit mysql-shell

vim /etc/profile

export PATH=$PATH:/data/software/mysql-shell/bin

source /etc/profile

[root@node1 software]# which mysqlsh

/data/software/mysql-shell/bin/mysqlsh

1.3 使用工具进行升级前的检查,检查项有可能返回三种Upgrade_issue，分别是 ERROR、WARNING、NOTICE。

ERROR：必须整改，否则升级失败。

WARNING: 可能需要整改，否则升级后有功能异常或不符合预期。

NOTICE: 升级程序会帮您自动处理好的，仅仅通知您升级程序会干这操作。

使用语句： mysqlsh -uroot -pP@ssw0rd -e “util.checkForServerUpgrade();”

或者以下语法

/data/software/mysql-shell/bin/mysqlsh – util check-for-server-upgrade { --user=root --socket=/tmp/mysql.sock } --target-version=8.0.36 --config-path=/etc/my.cnf

[root@node1 bin]# /data/software/mysql-shell/bin/mysqlsh – util check-for-server-upgrade { --user=root --socket=/tmp/mysql.sock } --target-version=8.0.36 --config-path=/etc/my.cnf

日志如下：

4. Usage of utf8mb3 charset

Warning: The following objects use the utf8mb3 character set. It is

recommended to convert them to use utf8mb4 instead, for improved Unicode

support.


More information:

https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html


…省略

mysql - schema’s default character set: utf8

sbtests - schema’s default character set: utf8

sbtests.sbtest1.c - column’s default character set: utf8

sbtests.sbtest1.pad - column’s default character set: utf8

sbtests.sbtest10.c - column’s default character set: utf8

sbtests.sbtest10.pad - column’s default character set: utf8

sbtests.sbtest2.c - column’s default character set: utf8

sbtests.sbtest2.pad - column’s default character set: utf8

sbtests.sbtest3.c - column’s default character set: utf8

sbtests.sbtest3.pad - column’s default character set: utf8

sbtests.sbtest4.c - column’s default character set: utf8

sbtests.sbtest4.pad - column’s default character set: utf8

sbtests.sbtest5.c - column’s default character set: utf8

sbtests.sbtest5.pad - column’s default character set: utf8

sbtests.sbtest6.c - column’s default character set: utf8

sbtests.sbtest6.pad - column’s default character set: utf8

sbtests.sbtest7.c - column’s default character set: utf8

sbtests.sbtest7.pad - column’s default character set: utf8

sbtests.sbtest8.c - column’s default character set: utf8

sbtests.sbtest8.pad - column’s default character set: utf8

sbtests.sbtest9.c - column’s default character set: utf8

sbtests.sbtest9.pad - column’s default character set: utf8

…省略

5. Table names in the mysql schema conflicting with new tables in 8.0

No issues found

…省略

Errors: 0

Warnings: 25

Notices: 0

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.

1.4 下载对应glibc版本的MySQL 8.0.36 binaries (mysql-8.0.36-linux-glibc2.28-x86_64.tar.xz)

下载地址：https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.36-linux-glibc2.17-x86_64.tar.xz

cd /data/software/

wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.36-linux-glibc2.17-x86_64.tar.xz

1.5 更改mysql5.7数据库的参数，要不然启动时候需要数据库恢复（前滚&回滚）

[root@node1 etc]# mysql -h172.17.0.35 -P3306 -uroot -pP@ssw0rd

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 7

Server version: 5.7.44-log MySQL Community Server (GPL)

Copyright © 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.

root@db 16:02: [(none)]> show variables like ‘%shutdown%’;

±------------------------------------±------+

| Variable_name | Value |

±------------------------------------±------+

| innodb_buffer_pool_dump_at_shutdown | ON |

| innodb_fast_shutdown | 1 |

±------------------------------------±------+

2 rows in set (0.00 sec)

root@db 16:02: [(none)]> set global innodb_fast_shutdown=0;

Query OK, 0 rows affected (0.00 sec)

root@db 16:02: [(none)]> show variables like ‘%shutdown%’;

±------------------------------------±------+

| Variable_name | Value |

±------------------------------------±------+

| innodb_buffer_pool_dump_at_shutdown | ON |

| innodb_fast_shutdown | 0 |

±------------------------------------±------+

2 rows in set (0.00 sec)

二.升级中

2.1 备份MySQL5.7整个目录

mkdir -p /u01/backup

cp -rp /data/software/mysql-5.7.44-linux-glibc2.12-x86_64/* /u01/backup

2.2 mysqldump备份整个数据库

/usr/local/mysql/bin/mysqldump -uroot -pP@ssw0rd -S /data/mysql/tmp/mysql.sock -q --single-transaction --events --routines --triggers --set-gtid-purged=OFF --all-databases |gzip > /root/all_db.gz

[root@node1 backup]# ll /root/all_db.gz

-rw-r–r-- 1 root root 96219406 Jan 31 16:22 /root/all_db.gz

2.3 关闭5.7版本数据库

service mysqld stop

或者

mysqladmin -uroot -pP@ssw0rd -S /data/mysql/tmp/mysql.sock shutdown

[root@node1 software]# mysqladmin -uroot -pP@ssw0rd -S /data/mysql/tmp/mysql.sock shutdown

mysqladmin: [Warning] Using a password on the command line interface can be insecure.

[root@node1 software]# ps -ef|grep mysql

root 34115 17292 0 14:40 pts/1 00:00:00 mysql -uroot -px xxxxxx -h172.17.0.35 -P3306

root 61711 55902 0 16:02 pts/4 00:00:00 mysql -h172.17.0.35 -P3306 -uroot -px xxxxxx

root 62106 54385 0 16:04 pts/3 00:00:00 grep --color=auto mysql

2.4 解压MySQL 8.0.36 binaries到MYSQL5.7的目录中，并覆盖，启动mysql

cd /data/software

tar -xvf mysql-8.0.36-linux-glibc2.17-x86_64.tar.xz

[root@node1 software]# ll mysql-8.0.36-linux-glibc2.17-x86_64

total 288

drwxr-xr-x 2 7161 31415 4096 Dec 13 03:38 bin

drwxr-xr-x 2 7161 31415 38 Dec 13 03:38 docs

drwxr-xr-x 3 7161 31415 282 Dec 13 03:38 include

drwxr-xr-x 6 7161 31415 201 Dec 13 03:38 lib

-rw-r–r-- 1 7161 31415 279356 Dec 13 02:09 LICENSE

drwxr-xr-x 4 7161 31415 30 Dec 13 03:38 man

-rw-r–r-- 1 7161 31415 666 Dec 13 02:09 README

drwxr-xr-x 28 7161 31415 4096 Dec 13 03:38 share

drwxr-xr-x 2 7161 31415 77 Dec 13 03:38 support-files

删除软链接

cd /usr/local

[root@node1 local]# rm -rf mysql

ln -s /data/software/mysql-8.0.36-linux-glibc2.17-x86_64 /usr/local/mysql

chown -R mysql.mysql /usr/local/mysql

重启启动数据库

service mysqld start

或者

/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &

以下是mysqld.log的输出，可以看出，数据库启动时候自动升级了数据字典或者后续手动升级也可以,日志如下：

2024-01-31T08:31:46.591326Z 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.

2024-01-31T08:31:46.591343Z 0 [Warning] [MY-011070] [Server] ‘binlog_format’ is deprecated and will be removed in a future release.

2024-01-31T08:31:46.591404Z 0 [Warning] [MY-010097] [Server] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.

2024-01-31T08:31:46.591478Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.36) starting as process 70215

2024-01-31T08:31:46.594272Z 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-01-31T08:31:46.594290Z 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-01-31T08:31:46.599429Z 0 [Warning] [MY-013907] [InnoDB] Deprecated configuration parameters innodb_log_file_size and/or innodb_log_files_in_group have been used to compute innodb_redo_log_capacity=3221225472. Please use innodb_redo_log_capacity instead.

2024-01-31T08:31:46.601845Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.

2024-01-31T08:31:46.601891Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.

2024-01-31T08:31:47.839903Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.

2024-01-31T08:31:48.619071Z 0 [ERROR] [MY-011300] [Server] Plugin mysqlx reported: ‘Setup of socket: ‘/tmp/mysqlx.sock’ failed, can’t create lock file /tmp/mysqlx.sock.lock’

2024-01-31T08:31:51.170169Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.

2024-01-31T08:31:53.594423Z 5 [System] [MY-013381] [Server] Server upgrade from ‘50700’ to ‘80036’ started.

2024-01-31T08:32:03.618145Z 5 [System] [MY-013381] [Server] Server upgrade from ‘50700’ to ‘80036’ completed.

2024-01-31T08:32:03.760918Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.

2024-01-31T08:32:03.760983Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.

2024-01-31T08:32:03.768358Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location ‘/data’ in the path is accessible to all OS users. Consider choosing a different directory.

2024-01-31T08:32:03.786839Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: ‘::’ port: 33060

2024-01-31T08:32:03.786875Z 0 [System] [MY-010931] [Server] /usr/local/mysql/bin/mysqld: ready for connections. Version: ‘8.0.36’ socket: ‘/data/mysql/tmp/mysql.sock’ port: 3306 MySQL Community Server - GPL.

三.升级后

3.1 升级数据字典

MySQL 8.0.16 的新的升級方式（参考：https://dev.mysql.com/doc/mysql-installation-excerpt/8.3/en/upgrading-what-is-upgraded.html）

/usr/local/mysql/bin/mysqld --upgrade=NONE

说明：在 mysqld 額外添加了一個新的選項 --upgrade。可選值爲 NONE，AUTO，MINIMAL，FORCE。

NONE：不尝试升级

AUTO: 默认选项，MYSQL进行数据字典和服务升级

MINIMAL：仅升级数据字典

FORCE：強制升級，类似于 mysql_upgrade –force

MySQL 8.0.16 以前的升级方式：

The mysql_upgrade client is now deprecated. The actions executed by the upgrade client are now done by the server.

mysql_upgrade --uroot -pP@ssw0rd --socket=/usr/local/mysql/mysql5.7.sock --port=3306

3.2 登录数据查看数据字典：

[root@node1 mysql-5.7.44-linux-glibc2.12-x86_64]# mysql -h172.17.0.35 -P3306 -uroot -pP@ssw0rd

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 11

Server version: 8.0.36 MySQL Community Server - GPL

Copyright © 2000, 2024, 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.

root@db 16:36: [(none)]> select user,host,plugin from mysql.user;

±-----------------±----------±----------------------+

| user | host | plugin |

±-----------------±----------±----------------------+

| root | % | mysql_native_password |

| mysql.infoschema | localhost | caching_sha2_password |

| mysql.session | localhost | mysql_native_password |

| mysql.sys | localhost | mysql_native_password |

±-----------------±----------±----------------------+

4 rows in set (0.00 sec)

root@db 16:36: [(none)]> show databases;

±-------------------+

| Database |

±-------------------+

| information_schema |

| mysql |

| performance_schema |

| sbtests |

| sys |

±-------------------+

5 rows in set (0.01 sec)

root@db 16:38: [(none)]> use sbtests;

Database changed

root@db 16:38: [sbtests]> show tables;

±------------------+

| Tables_in_sbtests |

±------------------+

| sbtest1 |

| sbtest10 |

| sbtest2 |

| sbtest3 |

| sbtest4 |

| sbtest5 |

| sbtest6 |

| sbtest7 |

| sbtest8 |

| sbtest9 |

±------------------+

10 rows in set (0.01 sec)

root@db 16:38: [sbtests]> select count(*) from sbtest1;

±---------+

| count(*) |

±---------+

| 100000 |

±---------+

1 row in set (0.01 sec)

3.3 简单数据库操作

root@db 16:39: [sbtests]> create table t1 (a int);

Query OK, 0 rows affected (0.04 sec)

root@db 16:39: [sbtests]> insert into t1 values(1);

Query OK, 1 row affected (0.01 sec)

root@db 16:39: [sbtests]> commit;

Query OK, 0 rows affected (0.00 sec)

root@db 16:39: [sbtests]>

root@db 16:39: [sbtests]>

root@db 16:39: [sbtests]> select * from t1;

±-----+

| a |

±-----+

| 1 |

±-----+

1 row in set (0.00 sec)

四.回滚

如果要回退的话只需要将新版本的base文件和数据文件移动到其他地方，将之前备份的MySQL 5.7的base路径和数据文件拷贝回原始位置，然后重新启动。

参考链接：https://blog.csdn.net/gsforget321/article/details/135777002