MySQL5.7升级到8.0(二):配置和参数| dboop.com

研发：MySQL5.7升级到8.0(一):SQL语法变化
DBA：MySQL5.7升级到8.0(二):配置和参数

Note：这里面是升级到8.0,需要DBA参与修改部分或注意部分

1.配置文件.cnf变化

以下参数变化

expire-logs-days  =>  binlog_expire_logs_seconds # 替换 expire-logs-days
tx_isolation      =>  transaction_isolation
tx_read_only      =>  transaction_read_only
innodb_undo_logs  =>  innodb_rollback_segments
have_query_cache  = no      # 永远为 NO
expire-logs-days 后续可能废弃, 使用 binlog_expire_logs_seconds  (目前还支持)

以下参数不再支持

innodb_stats_sample_pages
innodb_locks_unsafe_for_binlog
innodb_file_format   
innodb_file_format_check
innodb_file_format_max
innodb_large_prefix
ignore_builtin_innodb
skip-symbolic-links  # 默认即 skip-symbolic-links.
sync_frm             # 8.0 版本去掉了 .frm 文件, 内置在 ibd 文件中
sql_log_bin          # 仅支持会话级别设置
query_cache_xxx      # 缓存相关的系统变量
metadata_locks_cache_size
metadata_locks_hash_instances
date_format
datetime_format
time_format
max_tmp_tables

2.系统表变化

INNODB_LOCKS      => data_locks
INNODB_LOCK_WAITS => data_lock_waits

3.binlog变化

binlog命令

通过 mysqlbinlog 工具查看, 额外指定 --base64-output 参数避免解析乱码:

mysqlbinlog --verbose --base64-output=decode-rows mysql-bin.0000xx

binlog格式

binlog 头信息中增加了一些时间及版本信息:

# original_commit_timestamp=1587435300248124 (2020-04-21 10:15:00.248124 CST)
# immediate_commit_timestamp=1587435300248124 (2020-04-21 10:15:00.248124 CST)
/*!80001 SET @@session.original_commit_timestamp=1587435300248124*//*!*/;
/*!80014 SET @@session.original_server_version=80019*//*!*/;
/*!80014 SET @@session.immediate_server_version=80019*//*!*/;

4.权限与密码

权限

新版可能会废弃 SUPER 权限, 更多见 dynamic-privileges-migration-from-super, 如果需要管理 slave, 可以赋予 REPLICATION_SLAVE_ADMIN 权限;
增加角色设置, 不同用户可以分配不同的角色连接数据库, 更多见 create-role;
默认加密插件变更为 caching_sha2_password;
更多权限见 8.0-privileges-provided.

密码

8.0 中默认的密码以 caching_sha2_password 插件加密, 不兼容 8.0 以下的版本. 各编程语言的驱动需要查看官方信息确定. 可以对单个用户指定以前的 mysql_native_password 认证插件.

mysql > show global variables like '%auth%plugin%';                            
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+

mysql > alter user 'user'@'xxx' identified with mysql_native_password by 'pass';

如下所示, 默认低版本连接出现的错误:

# mysql -h infodb6 -P 3397 -u root -p
Enter password: 
ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/local/mysql/lib/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

低版本的驱动, 可以通过修改默认的加密插件来连接 DB:

[mysqld]
default_authentication_plugin = mysql_native_password

说明: 如果编程语言的驱动还不支持 caching_sha2_password 方式, 建议修改默认的验证为 mysql_native_password;

5.表变更

没有 frm 文件

8.0 开始去掉了 frm 文件, 表结构定义默认内置到 innodb 的 ibd 文件中, 可以通过 ibd2sdi ..table.ibd 获取详细的字段信息.
更多见:

8.0-ibd2sdi
mysql-8-frm-drop-how-to-recover-table-ddl

没有整形宽度

表结构中去掉了整数类型宽度的声明, 只能看到类型:

mysql root@[localhost:s3397 percona] > show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1681
Message: Integer display width is deprecated and will be removed in a future release.

mysql root@[localhost:s3397 percona] > show create table tests\G
*************************** 1. row ***************************
       Table: tests
Create Table: CREATE TABLE `tests` (
  `id` int NOT NULL AUTO_INCREMENT,
  `host` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `port` smallint NOT NULL DEFAULT '3306',
  `tag` varchar(100) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `location` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unq_hostmark` (`host`,`port`,`tag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

DATETIME和时区

从 8.0.19 版本开始, TIMESTAMP 和 DATETIME 两个时间类型都支持时区相关的设置. 以前仅有 TIMESTAMP 支持, DATETIME 支持后时间转换方便不少.

大小写敏感

8.0 版本中, 初始化和启动的时候, 选项 lower_case_table_names 的值必须相同. 更多见 sysvar_lower_case_table_names.

innodb 变更

一个库中的表可以共用一个 ibd 表空间文件, 也可以一个表一个 idb 文件;
undo log 不再存放在字典信息中, 单独出来在 undo log 文件中;
默认在 data/#innodb_temp 目录中创建 10 个 ibt 文件被 innodb 内部和用户空间的临时表使用;
information_schema 中表的列名存在变化, 更多见 8.0-information_schema.

6.索引变更

增加 invisible index(隐藏索引), 一个索引被设置为 invisible 后, 优化器会忽略该索引. 适合性能调试;
支持 descending index(降序索引);
支持 functional index(函数索引), 更多见 8.0-functional-key;
索引命中优化, 更多见 optimizer hints;

7.默认编码变成UTF8MB4

去掉了 UTF8 编码, 使用 UTF8MB3 代替以前的 UTF8, 8.0 中建议使用 UTF8MB4 编码. 默认的编码亦从 latin1 改为 utf8mb4, 默认的编码排序规则从 latin1_swedish_ci 改为 utf8mb4_0900_ai_ci;

8.mysqldump 备份

使用较低的 5.7.x 或 8.0.x 版本进行 mysqldump 备份的时候, 默认指定了 sql 模式 NO_AUTO_CREATE_USER, 包含此模式的 dump 文件在恢复的时候都会失败, 需要手动删除该模式.

9.主从复制

从5.7到8.0的复制中避免使用 caching_sha2_password
从5.7到8.0的复制中避免使用不同的sqlmode

升级步骤

mysql版本:低版本5.7.26 高版本8.0.20

1.执行升级程序

1.1 登陆5.7.26数据库执行如下sql。确保数据刷到硬盘

/data/mysql5.7/bin/mysql -h {} -P {} -u {} -p

show variables like 'innodb_fast_shutdown';

set global innodb_fast_shutdown=0;

shutdown;

exit;

1.2退出至终端用mysql8.0.20客户端直接启动

/usr/local/mysql3307/bin/mysqld_safe –defaults-file=/data/mysql3308/conf/my.cnf –user=mysql –lc-messages-dir=/usr/local/mysql3307/share –lc-messages=en_US &

2.问题总结

2.1配置文件信息(重要)
主要注意sql_mode、basedir、密码认证插件及字符集设置，标注为#的表示8.0取消的参数。配置会有报错。

basedir = /data/mysql3308
skip_ssl
default_authentication_plugin=mysql_native_password
#query_cache_limit = 1M
#sql_mode = NO_AUTO_CREATE_USER
#default-time_zone = '+8:00'
#innodb_support_xa = 1
#query_cache_min_res_unit = 4096
#query_cache_size = 0 #query_cache_type = 0
#innodb_undo_logs=128
mysqlx_port = 33080
mysqlx_socket = /data/mysql3308/mysqlx.sock

#innodb_undo_tablespaces=4

2.2 –lc-messages报错

更改完配置以后。mysql启动正常，但是检查日志。有 –lc-messages报错。

[ERROR] [MY-010338] [Server] Can't find error-message file '/data/mysql3308/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.

启动脚本指定定–lc-messages-dir=/usr/local/mysql3307/share –lc-messages=en_US后正常

/usr/local/mysql3307/bin/mysqld_safe --defaults-file=/data/mysql3308/conf/my.cnf --user=mysql --lc-messages-dir=/usr/local/mysql3307/share --lc-messages=en_US &

>> Home

MySQL5.7升级到8.0(二):配置和参数