MySql--从Mysql5.7升级到Mysql8

发表于 更新于 分类于 ， 置顶 精品
阅读次数： 0 本文字数： 7.4k 阅读时长 ≈ 27 分钟

摘要

从Mysql5.7升级到Mysql8

升级前，请先备份数据库，防止升级失败

mysqlsh

  • 在mysql8以前，升级mysql还是比较麻烦的，除了要处理不兼容的参数和语法外，而且不支持跨版本的升级，比如5.5必须先升级到5.6，然后再升级到5.7。

  • mysqlsh是mysql官方提供的一个命令行工具，是MySQL的高级客户端和代码编辑器。其不仅可以像使用mysql命令一样执行sql，而且还支持js脚本、python脚本，等等，具体可以查看官网。

  • mysqlsh的众多功能之一就是可以检测当前版本是否可以升级到与当前mysqlsh相同的版本。

  • 如果你当前的版本是5.6，你甚至都不需要先将数据库升级到5.7就可以直接升级到8.0，因为mysql8.0支持mysql5.5到mysql8.0的升级。

  • mysqlsh官方文档

安装mysqlsh

注意选择适合的 glibc 版本

  • 我们在下载 mysql server 和 mysqlsh 时，可以看到官网提供了基于不同 glibc 的版本，我们要选择适合当前操作系统的版本，而不建议直接升级系统 glibc。
  • 官网 generic Linux 包名里的 glibc2.12 / glibc2.17 / glibc2.28，表示编译时的最低 glibc 要求，不是「必须刚好等于这个版本」。
  • 比如当前系统自带 glibc 2.26，所以下载时选择 <= glibc2.26 即可。
1
2
3
4
5
6
7
# 查看当前系统的 glibc 版本
$ ldd --version
ldd (GNU libc) 2.26
Copyright (C) 2017 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
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# mysqlsh8.4以前可以使用如下方法，因为其默认就是执行js代码
./mysqlsh username:password@host -e "util.checkForServerUpgrade()"
# 特别注意：mysqlsh 默认优先 X Protocol（33060），此时需要远程的Mysql服务开启 X Plugin 并开放 33060 端口
# 如果连接失败可以使用 Classic MySQL 协议，即通过mysql的端口，比如 3306 进行连接
./mysqlsh username:password@host:3306 -e "util.checkForServerUpgrade()"


# mysqlsh8.4后默认执行的是sql模式，所以运行时需要指定运行模式为js
./mysqlsh username:password@host -e "util.checkForServerUpgrade()" --js
## --sql                            Start in SQL mode, default.
## --js, --javascript               Start in JavaScript mode.
## --py, --python                   Start in Python mode.

# 或者是先登录，然后切换到js环境后再运行上面的代码
# 登录
./mysqlsh username:password@host
 MySQL  localhost  SQL  > \js
Switching to JavaScript mode...
 MySQL  localhost  JS  > util.checkForServerUpgrade()
 ……………………………………………………
 # 退出
 MySQL  localhost  JS > \q
Bye!

检查输出信息说明

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
WARNING: Using a password on the command line interface can be insecure.
The MySQL server at
localhost:3306, version
5.7.44-log - Please upgrade to 8.0 or opt-in to the paid RDS Extended Support
service before 5.7 reaches end of standard support on 29 February, 2024:
https://a.co/hQqiIn0, will now be checked for compatibility issues for upgrade
to MySQL 8.0.30...

# 旧时间类型的使用
1) Usage of old temporal type
  No issues found

# 使用与新保留关键字冲突的数据库对象名称
2) Usage of db objects with names conflicting with new reserved keywords
  No issues found

# 使用utf8mb3字符集
3) 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

  mydb - schema's default character set: utf8
  mysql - schema's default character set: utf8
  mydb.tbl_info_i.a_product - column's default character set: utf8
  mydb.tbl_info_i.a_country - column's default character set: utf8
  mydb.tbl_info_i.a_version - column's default character set: utf8
  mydb.tbl_info_i.a_placement - column's default character set: utf8
………………………………………………………………………………………………………………………………

# MySQL模式中的表名与8.0中的新表冲突
4) Table names in the mysql schema conflicting with new tables in 8.0
  No issues found

# 使用非本机分区引擎的分区表
5) Partitioned tables using engines with non native partitioning
  No issues found

# 外键约束名称超过64个字符
6) Foreign key constraint names longer than 64 characters
  No issues found

# 使用过时的MAXDB sql_mode标志
7) Usage of obsolete MAXDB sql_mode flag
  No issues found

# 使用过时的sql_mode标志
8) Usage of obsolete sql_mode flags
  No issues found

# ENUM/SET列定义包含超过255个字符的元素
9) ENUM/SET column definitions containing elements longer than 255 characters
  No issues found

# 在共享表空间中使用分区表
10) Usage of partitioned tables in shared tablespaces
  No issues found

# 表空间数据文件路径中的循环目录引用
11) Circular directory references in tablespace data file paths
  No issues found

# 使用已删除的函数
12) Usage of removed functions
  No issues found

# 使用已删除的GROUP BY ASC/DESC语法
13) Usage of removed GROUP BY ASC/DESC syntax
  No issues found

# 已删除的用于错误日志记录到系统日志配置的系统变量
14) Removed system variables for error logging to the system log configuration
  To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
  More information:
    https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-13.html#mysqld-8-0-13-logging

# 已删除的系统变量
15) Removed system variables
  To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
  More information:
    https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed

# 具有新默认值的系统变量
16) System variables with new default values
  To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
  More information:
    https://mysqlserverteam.com/new-defaults-in-mysql-8-0/

# 零日期、日期时间和时间戳值
17) Zero Date, Datetime, and Timestamp values
  Warning: By default zero date/datetime/timestamp values are no longer allowed
    in MySQL, as of 5.7.8 NO_ZERO_IN_DATE and NO_ZERO_DATE are included in
    SQL_MODE by default. These modes should be used with strict mode as they will
    be merged with strict mode in a future release. If you do not include these
    modes in your SQL_MODE setting, you are able to insert
    date/datetime/timestamp values that contain zeros. It is strongly advised to
    replace zero values with valid ones, as they may not work correctly in the
    future.
  More information:
    https://lefred.be/content/mysql-8-0-and-wrong-dates/

  global.sql_mode - does not contain either NO_ZERO_DATE or NO_ZERO_IN_DATE
    which allows insertion of zero dates

# 由文件删除或损坏导致的模式不一致
18) Schema inconsistencies resulting from file removal or corruption
  No issues found

# 被InnoDB识别但属于不同引擎的表
19) Tables recognized by InnoDB that belong to a different engine
  No issues found

# 'check table x for upgrade'命令报告的问题
20) Issues reported by 'check table x for upgrade' command
  No issues found

# 新的默认认证插件考虑
21) New default authentication plugin considerations
  Warning: The new default authentication plugin 'caching_sha2_password' offers
    more secure password hashing than previously used 'mysql_native_password'
    (and consequent improved client connection authentication). However, it also
    has compatibility implications that may affect existing MySQL installations.
    If your MySQL installation must serve pre-8.0 clients and you encounter
    compatibility issues after upgrading, the simplest way to address those
    issues is to reconfigure the server to revert to the previous default
    authentication plugin (mysql_native_password). For example, use these lines
    in the server option file:

    [mysqld]
    default_authentication_plugin=mysql_native_password

    However, the setting should be viewed as temporary, not as a long term or
    permanent solution, because it causes new accounts created with the setting
    in effect to forego the improved authentication security.
    If you are using replication please take time to understand how the
    authentication plugin changes may impact you.
  More information:
    https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatibility-issues
    https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replication

Errors:   0
Warnings: 98
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.

  • 可以看到，其检查的数据库版本是5.7.44，其检查是否可以升级到8.0.30

  • 检测项共有21个，其中警告有98个，错误有0个，注意，如果有错误，则不能直接升级，需要解决错误问题，否则升级会失败。至于警告暂时可以不处理。

  • 警告集中体现在字符集上，5.7.44的字符集是utf8(实际就是utf8mb3)，而mysql8以后的默认字符集是utf8mb4，这个警告不影响升级，可以忽略。如果需要修改字符集到utf8mb4，需要注意原来的字段字符串长度是否符合要求，因为utf8mb4是4字节，而utf8mb3是3字节。另外如果使用了存储过程也要注意参数的长度是否符合要求。

  • 这里说一下default_authentication_plugin，mysql8.0.13开始，默认的认证插件是caching_sha2_password，而之前是mysql_native_password，这个警告虽然可以忽略，但是升级后建议尽快将认证插件修改为caching_sha2_password，因为mysql_native_password的认证方式存在安全漏洞，可以参考官方文档。另外，从mysql8.4.0开始，不再支持default_authentication_plugin，若要使其支持mysql_native_password插件，8.4.0开始为我们增加了一个新的配置项mysql-native-password，在[mysqld]中配置mysql-native-password=ON即可开启支持，默认为OFF。

  • 另外还要注意关键词和保留词的问题，mysql8.0以后，又增加了很多个关键词和保留词，如果检查到则需要修改一下，不然升级会失败。即便表和字段里没有使用这些保留字，也要检查一下业务代码中是否有使用。关于关键词和保留词可以查看官网Keywords and Reserved Words，相比于MySQL 5.7，增加了大约130个，同时也减少了6个。

    这里建议设置表名称和字段名称时，可以为其设置前缀，比如：表名称为：t_user、字段名称为：f_user_id

  • 还有一点需要注意，Mysql8修改了很多参数的默认值，具体可以查看官方网站的说明，如果你不希望使用默认值，则需要将其配置到配置文件中，这里强调一下，重点关注 character_set_server 和 collation_server 这两个参数。所有的参数配置参考官方文档

关于字符集还想多说两句

  • mysql8的character_set_server默认是utf8mb4，collation_server默认是utf8mb4_0900_ai_ci
  • 如果创建数据库或者表的时候没有指定字符集，则默认使用character_set_server和collation_server的默认值
  • 此时新创建的表就有可能与旧表字符集不一致，关联查询时就有可能导致错误或因为需要进行字符集转换导致查询性能下降。
  • 最好的方式是创建数据库或表时明确指定字符集，或者在my.cnf配置文件中配置character_set_server和collation_server的值。

升级数据库的过程

  • 因为我们要升级到mysql8.0.30，所以需要先下载并安装mysql8.0.30

  • 关闭mysql5.7的数据库

  • 备份数据目录(一定要做好备份，升级后不能降级的)

  • 根据检查提示修改my.cnf配置文件中的配置(主要是不兼容的配置，如果检查提示没有则不需要修改)

  • 非常重要的一步，就是修改my.cnf配置文件中的basedir，将其指向mysql8.0.30的安装目录

  • 直接使用mysql8.0.30的命令启动数据库，启动过程中就会完成数据库的升级，可以通过mysql的日志文件(log-error)查看升级情况

  • 升级完成后尽快使用新的密码认证插件修改密码，注意要修改所有用户的密码，包括root用户

1
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '123456';

小贴士

  • mysql8.4.0相比于之前的版本变化比较大，具体可以查看官网文档
  • mysql5.7不要直接升级到8.4.0及以后的版本，可以先升级到8.0.30，然后再升级到8.4.0
  • 升级到8.4.0前依旧要先使用mysqlsh检查是否可以升级，注意要下载对应的mysqlsh版本
  • 升级前要先检查是否所有用户的密码插件都是caching_sha2_password，如果没有则需要修改
  • 升级前要先检查my.cnf中是否配置了default_authentication_plugin，如果有则删除，如果必须要配置mysql_native_password认证插件，则需要配置mysql-native-password=ON
  • 修改my.cnf中的basedir使其指向8.4.0的安装目录
  • 使用8.4.0的命令启动mysql，检查日志是否有错误。

mysql5.7.44升级到8.0.30，再升级到8.4.0的完整过程

  • 准备好各个版本的mysql和mysqlsh

1
2
3
4
5
6
7
# 目录结构
mysql5 # 5.7.44安装目录
mysql8.0.30 # 8.0.30安装目录
mysql8.4.0 # 8.4.0安装目录
mysqlsh8.0.30 # 8.0.30的mysqlsh安装目录
mysqlsh8.4.0 # 8.4.0的mysqlsh安装目录
datas/mysql # 存放数据目录

  • mysql5/my.cnf配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
[mysql]
default-character-set=utf8mb4
local_infile=ON

[client]
port       = 3306
socket     = /tmp/mysql.sock

[mysqld]
skip-name-resolve
secure_file_priv=""
local_infile=ON

default_authentication_plugin=mysql_native_password

port       = 3306
server-id  = 1001
user       = mysql
socket     = /tmp/mysql.sock
basedir    = /usr/local/soft/mysql5
datadir    = /usr/local/soft/datas/mysql

log-bin    = mysql-bin
binlog-format = ROW

expire-logs-days=10

sync-binlog=0
innodb_data_home_dir      =./
innodb_log_group_home_dir =./
log-error =mysql.log
pid-file  =mysql.pid
character-set-server=utf8mb4
lower_case_table_names=1
autocommit =1
slow_query_log=1
slow_query_log_file=db_slow.log
long_query_time=5
log_output=FILE
log_queries_not_using_indexes=1
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 64M
table_open_cache = 1024
sort_buffer_size = 4M
net_buffer_length = 8K
read_buffer_size = 4M
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 64M
thread_cache_size = 64
tmp_table_size = 128M
explicit_defaults_for_timestamp = true
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535
default_storage_engine = InnoDB
innodb_data_file_path = ibdata1:10M:autoextend

5.7.44升级到8.0.30

  • 升级检查

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
# 升级检查
./mysqlsh8.0.30/bin/mysqlsh --uri root:123456@localhost  -e "util.checkForServerUpgrade()"
# 输出
WARNING: Using a password on the command line interface can be insecure.
The MySQL server at /tmp%2Fmysql.sock, version 5.7.44-log - MySQL Community
Server (GPL), will now be checked for compatibility issues for upgrade to MySQL
8.0.30...

1) Usage of old temporal type
  No issues found

2) Usage of db objects with names conflicting with new reserved keywords
  No issues found

3) 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

  mydb - schema's default character set: utf8
  mysql - schema's default character set: utf8
  mydb.tbl_info_i.a_product - column's default character set: utf8
  mydb.tbl_info_i.a_country - column's default character set: utf8
  mydb.tbl_info_i.a_version - column's default character set: utf8
  mydb.tbl_info_i.a_placement - column's default character set: utf8
 …………………………………………………………………………………………



4) Table names in the mysql schema conflicting with new tables in 8.0
  No issues found

5) Partitioned tables using engines with non native partitioning
  No issues found

6) Foreign key constraint names longer than 64 characters
  No issues found

7) Usage of obsolete MAXDB sql_mode flag
  No issues found

8) Usage of obsolete sql_mode flags
  Notice: The following DB objects have obsolete options persisted for
    sql_mode, which will be cleared during upgrade to 8.0.
  More information:
    https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals

  global system variable sql_mode - defined using obsolete NO_AUTO_CREATE_USER
    option

9) ENUM/SET column definitions containing elements longer than 255 characters
  No issues found

10) Usage of partitioned tables in shared tablespaces
  No issues found

11) Circular directory references in tablespace data file paths
  No issues found

12) Usage of removed functions
  No issues found

13) Usage of removed GROUP BY ASC/DESC syntax
  No issues found

14) Removed system variables for error logging to the system log configuration
  To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
  More information:
    https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-13.html#mysqld-8-0-13-logging

15) Removed system variables
  To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
  More information:
    https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed

16) System variables with new default values
  To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
  More information:
    https://mysqlserverteam.com/new-defaults-in-mysql-8-0/

17) Zero Date, Datetime, and Timestamp values
  No issues found

18) Schema inconsistencies resulting from file removal or corruption
  No issues found

19) Tables recognized by InnoDB that belong to a different engine
  No issues found

20) Issues reported by 'check table x for upgrade' command
  No issues found

21) New default authentication plugin considerations
  Warning: The new default authentication plugin 'caching_sha2_password' offers
    more secure password hashing than previously used 'mysql_native_password'
    (and consequent improved client connection authentication). However, it also
    has compatibility implications that may affect existing MySQL installations.
    If your MySQL installation must serve pre-8.0 clients and you encounter
    compatibility issues after upgrading, the simplest way to address those
    issues is to reconfigure the server to revert to the previous default
    authentication plugin (mysql_native_password). For example, use these lines
    in the server option file:

    [mysqld]
    default_authentication_plugin=mysql_native_password

    However, the setting should be viewed as temporary, not as a long term or
    permanent solution, because it causes new accounts created with the setting
    in effect to forego the improved authentication security.
    If you are using replication please take time to understand how the
    authentication plugin changes may impact you.
  More information:
    https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatibility-issues
    https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replication

Errors:   0
Warnings: 98
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.

  • 看到没有error，开始升级

1
2
3
4
5
6
7
8
9
10
11
# 关闭数据库
./mysql5/bin/mysqladmin --defaults-file=./mysql5/my.cnf -uroot -p123456 shutdown

# 复制mysql配置文件
cp ./mysql5/my.cnf ./mysql8.0.30/my.cnf

# 修改新的配置文件中的basedir，使其指向8.0.30的安装路径
basedir    = /usr/local/soft/mysql8.0.30

# 使用8.0.30启动
./mysql8.0.30/bin/mysqld_safe --defaults-file=./mysql8.0.30/my.cnf &

  • 查看启动日志，没有错误说明升级成功

1
2
3
4
5
6
7
8
9
10
11
12
13
14
2024-05-09T06:48:50.524705Z 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-05-09T06:48:50.524819Z 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-05-09T06:48:50.524917Z 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
2024-05-09T06:48:50.524983Z 0 [System] [MY-010116] [Server] /usr/local/soft/mysql8.0.30/bin/mysqld (mysqld 8.0.30) starting as process 26696
2024-05-09T06:48:50.563366Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2024-05-09T06:48:50.563414Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-05-09T06:48:51.260298Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-05-09T06:48:53.614398Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2024-05-09T06:48:55.055320Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80030' started.
2024-05-09T06:49:02.063990Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80030' completed.
2024-05-09T06:49:02.237992Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-05-09T06:49:02.238048Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-05-09T06:49:02.267583Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
2024-05-09T06:49:02.267648Z 0 [System] [MY-010931] [Server] /usr/local/soft/mysql8.0.30/bin/mysqld: ready for connections. Version: '8.0.30'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server - GPL.

根据日志提示我们应该修改两个配置参数，未来将不可用了，不过这里暂且先不去管它，等检查通不过了再进行处理就可以。

1
2
1.The syntax 'expire-logs-days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
2.'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.

  • 修改密码插件，这个步骤很重要

1
2
3
4
# 登录
./mysql8.0.30/bin/mysql -uroot -p123456
# 升级完成后使用新的密码认证插件修改密码，8.4不再推荐mysql_native_password认证插，所以升级到8.4以前一定要先修改密码插件，如果忘记了也没关系，文末有重置密码的方法
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '123456';

8.0.30升级到8.4.0

  • 同样先用8.4.0的mysqlsh检查一下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
# 升级检查
./mysqlsh8.4.0/bin/mysqlsh --uri root:123456@localhost --js -e "util.checkForServerUpgrade()"
# 输出
WARNING: Using a password on the command line interface can be insecure.
Creating a Classic session to 'root@/tmp%2Fmysql.sock'
The MySQL server at /tmp%2Fmysql.sock, version 8.0.30 - MySQL Community Server
- GPL, will now be checked for compatibility issues for upgrade to MySQL 8.4.0.
To check for a different target server version, use the targetVersion option.

WARNING: Upgrading MySQL Server from version 8.0.30 to 8.4.0 is not supported.
Please consider running the check using the following option: targetVersion=8.0

1) Usage of db objects with names conflicting with new reserved keywords
(reservedKeywords)
  No issues found

2) Removed system variables (removedSysVars)
  Error: Following system variables that were detected as being used will be
    removed. Please update your system to not rely on them before the upgrade.
  More information:
    https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed

  expire_logs_days - Error: The system variable 'expire_logs_days' is set to 10
    (EXPLICIT) and will be removed.

3) System variables with new default values (sysVarsNewDefaults)
  Warning: Following system variables that are not defined in your
    configuration file will have new default values. Please review if you rely on
    their current values and if so define them before performing upgrade.
  More information:
    https://dev.mysql.com/blog-archive/new-defaults-in-mysql-8-0/

  binlog_transaction_dependency_tracking - default value will change from
    COMMIT_ORDER to WRITESET.
  group_replication_consistency - default value will change from EVENTUAL to
    BEFORE_ON_PRIMARY_FAILOVER.
  group_replication_exit_state_action - default value will change from
    READ_ONLY to OFFLINE_MODE.
  innodb_adaptive_hash_index - default value will change from ON to OFF.
  innodb_buffer_pool_in_core_file - default value will change from ON to OFF.
  innodb_buffer_pool_instances - default value will change from 8 (or 1 if
    innodb_buffer_pool_size < 1GB) to MAX(1, #vcpu/4).
  innodb_change_buffering - default value will change from all to none.
  innodb_doublewrite_files - default value will change from
    innodb_buffer_pool_instances * 2 to 2.
  innodb_doublewrite_pages - default value will change from
    innodb_write_io_threads to 128.
  innodb_flush_method - default value will change from fsynch (unix) or
    unbuffered (windows) to O_DIRECT.
  innodb_io_capacity - default value will change from 200 to 10000.
  innodb_io_capacity_max - default value will change from 200 to 2 x
    innodb_io_capacity.
  innodb_log_buffer_size - default value will change from 16777216 (16MB) to
    67108864 (64MB).
  innodb_log_writer_threads - default value will change from ON to OFF ( if
    #vcpu <= 32 ).
  innodb_numa_interleave - default value will change from OFF to ON.
  innodb_page_cleaners - default value will change from 4 to
    innodb_buffer_pool_instances.
  innodb_parallel_read_threads - default value will change from 4 to
    MAX(#vcpu/8, 4).
  innodb_purge_threads - default value will change from 4 to 1 ( if #vcpu <= 16
    ).
  innodb_read_io_threads - default value will change from 4 to MAX(#vcpu/2, 4).
  innodb_redo_log_capacity - default value will change from 104857600 (100MB)
    to MIN ( #vcpu/2, 16 )GB.

4) Issues reported by 'check table x for upgrade' command (checkTableCommand)
  No issues found

5) Check for deprecated usage of single dollar signs in object names
(dollarSignName)
  No issues found

6) Check for deprecated or invalid user authentication methods.
(authMethodUsage)
  Warning: The following users are using the 'mysql_native_password'
  authentication method which is deprecated as of MySQL 8.0.0 and will be
  removed in a future release.
  Consider switching the users to a different authentication method (i.e.
  caching_sha2_password).

  - mydb@%
  - mysql.session@localhost
  - mysql.sys@localhost
  - root@localhost

  More information:
    https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html


7) Check for deprecated or removed plugin usage. (pluginUsage)
  No issues found

8) Check for deprecated or invalid default authentication methods in system
variables. (deprecatedDefaultAuth)
  The following variables have problems with their set authentication method:

  Error: default_authentication_plugin - mysql_native_password authentication
    method was removed and it must be corrected before upgrading to 8.4.0 release.

9) Check for deprecated or invalid authentication methods in use by MySQL
Router internal accounts. (deprecatedRouterAuthMethod)
  No issues found

10) Checks for errors in column definitions (columnDefinition)
  No issues found

11) Check for allowed values in System Variables. (sysvarAllowedValues)
  No issues found

12) Checks for user privileges that will be removed (invalidPrivileges)
  Verifies for users containing grants to be removed, since privileges are
    removed as part of the upgrade, raises a NOTICE to inform the user about
    users that will be losing invalid privileges

  'mysql.session'@'localhost' - The user 'mysql.session'@'localhost' has the
    following privileges that will be removed as part of the upgrade process:
    SET_USER_ID
  'root'@'localhost' - The user 'root'@'localhost' has the following privileges
    that will be removed as part of the upgrade process: SET_USER_ID

13) Checks for partitions by key using columns with prefix key indexes
(partitionsWithPrefixKeys)
  No issues found

Errors:   2
Warnings: 24
Notices:  2

ERROR: 2 errors were found. Please correct these issues before upgrading to avoid compatibility issues.

  • 看到了两处错误，就是我们上面说的那两个参数需要修改，修改./mysql8.0.30/my.cnf

1
2
3
4
5
6
7
8
9
# binlog日志过期时间，单位秒，过期的binlog日志会被删除，保证磁盘空间，这里设置为10天，mysql8以前的版本这个参数是expire-logs-days，单位是天
binlog_expire_logs_seconds =864000
#expire-logs-days=10


# 8.4+开始不再支持该配置，默认认证插件为 caching_sha2_password
# 升级前需要将所有用户的认证插件全部修改为 caching_sha2_password
# 但笔者没有修改，所以升级后发现用户无法登录了，下面有说明解决方法
#default_authentication_plugin=mysql_native_password

  • 修改后要重启数据库

1
2
3
4
5
# 关闭数据库
 ./mysql8.0.30/bin/mysqladmin --defaults-file=./mysql8.0.30/my.cnf -uroot -p123456 shutdown

 # 使用8.0.30启动
 ./mysql8.0.30/bin/mysqld_safe --defaults-file=./mysql8.0.30/my.cnf &

  • 再次进行升级检查

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
# 升级检查
./mysqlsh8.4.0/bin/mysqlsh --uri root:123456@localhost --js -e "util.checkForServerUpgrade()"
# 输出
WARNING: Using a password on the command line interface can be insecure.
Creating a Classic session to 'root@/tmp%2Fmysql.sock'
The MySQL server at /tmp%2Fmysql.sock, version 8.0.30 - MySQL Community Server
- GPL, will now be checked for compatibility issues for upgrade to MySQL 8.4.0.
To check for a different target server version, use the targetVersion option.

WARNING: Upgrading MySQL Server from version 8.0.30 to 8.4.0 is not supported.
Please consider running the check using the following option: targetVersion=8.0

1) Usage of db objects with names conflicting with new reserved keywords
(reservedKeywords)
  No issues found

2) Removed system variables (removedSysVars)
  No issues found

3) System variables with new default values (sysVarsNewDefaults)
  Warning: Following system variables that are not defined in your
    configuration file will have new default values. Please review if you rely on
    their current values and if so define them before performing upgrade.
  More information:
    https://dev.mysql.com/blog-archive/new-defaults-in-mysql-8-0/

  binlog_transaction_dependency_tracking - default value will change from
    COMMIT_ORDER to WRITESET.
  group_replication_consistency - default value will change from EVENTUAL to
    BEFORE_ON_PRIMARY_FAILOVER.
  group_replication_exit_state_action - default value will change from
    READ_ONLY to OFFLINE_MODE.
  innodb_adaptive_hash_index - default value will change from ON to OFF.
  innodb_buffer_pool_in_core_file - default value will change from ON to OFF.
  innodb_buffer_pool_instances - default value will change from 8 (or 1 if
    innodb_buffer_pool_size < 1GB) to MAX(1, #vcpu/4).
  innodb_change_buffering - default value will change from all to none.
  innodb_doublewrite_files - default value will change from
    innodb_buffer_pool_instances * 2 to 2.
  innodb_doublewrite_pages - default value will change from
    innodb_write_io_threads to 128.
  innodb_flush_method - default value will change from fsynch (unix) or
    unbuffered (windows) to O_DIRECT.
  innodb_io_capacity - default value will change from 200 to 10000.
  innodb_io_capacity_max - default value will change from 200 to 2 x
    innodb_io_capacity.
  innodb_log_buffer_size - default value will change from 16777216 (16MB) to
    67108864 (64MB).
  innodb_log_writer_threads - default value will change from ON to OFF ( if
    #vcpu <= 32 ).
  innodb_numa_interleave - default value will change from OFF to ON.
  innodb_page_cleaners - default value will change from 4 to
    innodb_buffer_pool_instances.
  innodb_parallel_read_threads - default value will change from 4 to
    MAX(#vcpu/8, 4).
  innodb_purge_threads - default value will change from 4 to 1 ( if #vcpu <= 16
    ).
  innodb_read_io_threads - default value will change from 4 to MAX(#vcpu/2, 4).
  innodb_redo_log_capacity - default value will change from 104857600 (100MB)
    to MIN ( #vcpu/2, 16 )GB.

4) Issues reported by 'check table x for upgrade' command (checkTableCommand)
  No issues found

5) Check for deprecated usage of single dollar signs in object names
(dollarSignName)
  No issues found

6) Check for deprecated or invalid user authentication methods.
(authMethodUsage)
  Warning: The following users are using the 'mysql_native_password'
  authentication method which is deprecated as of MySQL 8.0.0 and will be
  removed in a future release.
  Consider switching the users to a different authentication method (i.e.
  caching_sha2_password).

  - mydb@%
  - mysql.session@localhost
  - mysql.sys@localhost
  - root@localhost

  More information:
    https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html


7) Check for deprecated or removed plugin usage. (pluginUsage)
  No issues found

8) Check for deprecated or invalid default authentication methods in system
variables. (deprecatedDefaultAuth)
  No issues found

9) Check for deprecated or invalid authentication methods in use by MySQL
Router internal accounts. (deprecatedRouterAuthMethod)
  No issues found

10) Checks for errors in column definitions (columnDefinition)
  No issues found

11) Check for allowed values in System Variables. (sysvarAllowedValues)
  No issues found

12) Checks for user privileges that will be removed (invalidPrivileges)
  Verifies for users containing grants to be removed, since privileges are
    removed as part of the upgrade, raises a NOTICE to inform the user about
    users that will be losing invalid privileges

  'mysql.session'@'localhost' - The user 'mysql.session'@'localhost' has the
    following privileges that will be removed as part of the upgrade process:
    SET_USER_ID
  'root'@'localhost' - The user 'root'@'localhost' has the following privileges
    that will be removed as part of the upgrade process: SET_USER_ID

13) Checks for partitions by key using columns with prefix key indexes
(partitionsWithPrefixKeys)
  No issues found

Errors:   0
Warnings: 24
Notices:  2

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
2
3
4
5
6
7
8
9
10
11
# 关闭数据库
./mysql8.0.30/bin/mysqladmin --defaults-file=./mysql8.0.30/my.cnf -uroot -p123456 shutdown

# 复制mysql配置文件
cp ./mysql8.0.30/my.cnf ./mysql8.4.0/my.cnf

# 修改新的配置文件中的basedir，使其指向8.4.0的安装路径
basedir    = /usr/local/soft/mysql8.4.0

 # 使用8.4.0启动
./mysql8.4.0/bin/mysqld_safe --defaults-file=./mysql8.4.0/my.cnf &

  • 查看日志，依旧是没有错误，升级很顺利

1
2
3
4
5
6
7
8
9
10
11
12
13
14
2024-05-09T07:21:55.325669Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2024-05-09T07:21:55.740435Z 0 [Warning] [MY-011070] [Server] 'binlog_format' is deprecated and will be removed in a future release.
2024-05-09T07:21:55.740549Z 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-05-09T07:21:55.740629Z 0 [System] [MY-010116] [Server] /usr/local/soft/mysql-8.4.0-linux-glibc2.17-x86_64/bin/mysqld (mysqld 8.4.0) starting as process 28414
2024-05-09T07:21:55.796983Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-05-09T07:21:56.421873Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-05-09T07:21:56.442008Z 1 [System] [MY-011090] [Server] Data dictionary upgrading from version '80023' to '80300'.
2024-05-09T07:21:57.106002Z 1 [System] [MY-013413] [Server] Data dictionary upgrade from version '80023' to '80300' completed.
2024-05-09T07:22:00.128935Z 4 [System] [MY-013381] [Server] Server upgrade from '80030' to '80400' started.
2024-05-09T07:22:06.850838Z 4 [System] [MY-013381] [Server] Server upgrade from '80030' to '80400' completed.
2024-05-09T07:22:07.063386Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-05-09T07:22:07.063456Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-05-09T07:22:07.085760Z 0 [System] [MY-010931] [Server] /usr/local/soft/mysql-8.4.0-linux-glibc2.17-x86_64/bin/mysqld: ready for connections. Version: '8.4.0'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server - GPL.
2024-05-09T07:22:07.340333Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock

从日志看到’binlog_format’也要不支持了，不过先不用管，等到不支持了再处理吧。

如果升级到8.4之前忘记给用户修改密码插件了，则升级之后就不能正常登录了，此时怎么办呢？

  • 升级到8.4之前忘记修改密码插件了，则升级之后登录数据库会报告如下错误

1
ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded

  • 解决方案
    在mysql的配置文件中增加mysql-native-password=ON，然后重启数据库即可，但还是建议尽快修改用户的认证插件为caching_sha2_password吧，毕竟mysql_native_password这个认证方式存在安全漏洞。
    另外从 MySQL9.0 开始，就不再支持该配置，升级到 MySQL9.0 前必须将所有用户的认证插件配置为 caching_sha2_password。

1
2
[mysqld]
mysql_native_password=ON

小贴士
笔者之前没有注意到8.4.0新增的mysql-native-password=ON这个配置，所以采用了如下重置密码的方式进行插件修改，留个纪念吧。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
# kill掉mysql服务
killall mysqld

# 无权限模式启动
./mysql8.4.0/bin/mysqld_safe --defaults-file=./mysql8.4.0/my.cnf --skip-grant-tables &

# 登录，此时不需要密码
./mysql8.4.0/bin/mysql -uroot

mysql> USE mysql;
# 修改密码插件为 caching_sha2_password
mysql> UPDATE user SET plugin='caching_sha2_password' WHERE User='root';
# 将密码设置为空
mysql> UPDATE user SET authentication_string=null WHERE User='root';
# 刷新权限：
mysql> FLUSH PRIVILEGES;
# 退出MySQL
mysql> exit;

# 关闭数据库，此时不需要密码即可关闭
./mysql8.4.0/bin/mysqladmin --defaults-file=./mysql8.4.0/my.cnf -uroot -p shutdown

# 重启启动mysql服务
./mysql8.4.0/bin/mysqld_safe --defaults-file=./mysql8.4.0/my.cnf &

# 登录，此时依旧不需要密码
./mysql8.4.0/bin/mysql -uroot
# 设置密码
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
# 刷新权限：
mysql> FLUSH PRIVILEGES;
# 退出MySQL
mysql> exit;

# 登录，此时使用密码就可以正常登录了
./mysql8.4.0/bin/mysql -uroot -p123456

# 之后再修改其他用户的密码即可，比如
mysql> ALTER USER 'other'@'%' IDENTIFIED WITH caching_sha2_password BY 'otherpass';

# 查看修改后的user信息
mysql> use mysql;
mysql> select user,host,plugin from user;

迁移 datadir 时，log-bin 是绝对路径遇到的坑

本文前面的配置里，log-bin 写的是相对路径：

1
2
datadir    = /usr/local/soft/datas/mysql
log-bin    = mysql-bin

这种写法下，Binlog 文件会放在 datadir 里，mysql-bin.index 记录的也是相对路径，例如：

1
2
./mysql-bin.000043
./mysql-bin.000044

之后就算把整个数据目录搬走，MySQL 会按当前的 datadir 去拼路径，所以不会出问题。

但如果把 log-bin 配成了绝对路径，路径一旦变更就会出问题。

实战

旧服务器原来的配置是：

1
2
datadir = /usr/local/soft/mysql8/datas/mysql
log-bin = /usr/local/soft/mysql8/datas/mysql/mysql-bin

于是 mysql-bin.index 里保存的是：

1
2
/usr/local/soft/mysql8/datas/mysql/mysql-bin.000043
/usr/local/soft/mysql8/datas/mysql/mysql-bin.000044

后来把整个数据目录迁移到了：

1
/usr/local/soft/mysql-data-dir/datas/mysql/

Binlog 文件本身跟着复制过去了：

1
2
mysql-bin.000043
mysql-bin.000044

但是 mysql-bin.index 只是一个文本索引文件，里面的绝对路径不会自动跟着 datadir 改变。

所以即便你修改了my.cnf中对应的配置

1
2
datadir = /usr/local/soft/mysql-data-dir/datas/mysql
log-bin = /usr/local/soft/mysql-data-dir/datas/mysql/mysql-bin

MySQL 还是报：

1
mysqld: File '/usr/local/soft/mysql8/datas/mysql/mysql-bin.000044' not found (OS errno 2 - No such file or directory)

问题在于 Binlog 索引文件中已经写死了旧路径。

处理也很简单：停库后改 mysql-bin.index，把旧路径换成新路径，或者直接改成相对路径：

1
2
./mysql-bin.000043
./mysql-bin.000044

同时把配置改回相对路径，避免下次搬家再踩坑：

1
log-bin = mysql-bin