概述

不知不觉，MySQL8.0已经发布好多个GA小版本了，MySQL8.0版本基本已到稳定期。今天主要介绍从5.7升级到8.0版本的过程及注意事项，有想做版本升级的小伙伴可以参考下。

一、注意事项

mysql从5.7升级到8.0是支持的，但是只支持GA版本的升级，并且要求版本为5.7.9或者更高
在升级到8.0之前，建议升级到5.7的最新版本。仅仅支持从5.7版本升级到8.0，不支持5.6版本升级到8.0

升级之前我们需要了解下MySQL5.7和8.0有哪些不同，简单总结出MySQL8.0以下几点新特性：

• 默认字符集由latin1变为utf8mb4。
• MyISAM系统表全部换成InnoDB表。
• JSON特性增强。
• 支持不可见索引，支持直方图。
• sql_mode参数默认值变化。
• 默认密码策略变更。
• 新增角色管理。
• 支持窗口函数，支持Hash join。

根据版本变化及官方升级教程，列举出以下几点注意事项：

• 注意字符集设置。为了避免新旧对象字符集不一致的情况，可以在配置文件将字符集和校验规则设置为旧版本的字符集和比较规则。
• 密码认证插件变更。为了避免连接问题，可以仍采用5.7的mysql_native_password认证插件。
• sql_mode支持问题。8.0版本sql_mode不支持NO_AUTO_CREATE_USER，要避免配置的sql_mode中带有NO_AUTO_CREATE_USER。
• 是否需要手动升级系统表。在MySQL 8.0.16版本之前，需要手动的执行mysql_upgrade来完成该步骤的升级，在MySQL 8.0.16版本及之后是由mysqld来完成该步骤的升级。

二、准备工作

1、备份数据（包括当前的数据库和日志文件）

--备份数据
mysqldump -uroot -p ycapp_dflg_prod --single_transaction  --flush-logs --master-data=2 >/backup/ycapp.sql

--备份视图、函数、存储过程、事件、触发器的定义
============================================================================
#!/bin/bash
# ./output_db_object_definition.sh > /dev/null 2>&1
db_user="root"
db_pwd="xxxx"
db_host="localhost"
db_port=3306
db_name="xxxx" 
save_file="/home/scripts/${db_name}_object_definition.sql"
# view,function,procedure,event,trigger
output_type='view,function,procedure,event,trigger' 
(cat <<out
/*
ouput object‘s definition for database "$db_name"
ouput time: $(date "+%Y-%m-%d %H:%M:%S")
ouput object type: $output_type
*/
out
)>$save_file
echo "">> $save_file
echo "">> $save_file
 
# 视图
if [[ $output_type == *"view"* ]]
then
	echo "-- ------------------------------------------------------------" >> $save_file
	echo "-- views" >> $save_file
	echo "-- ------------------------------------------------------------" >> $save_file
	mysql -h$db_host -P$db_port -u$db_user -p$db_pwd --skip-column-names \
	-e "select concat('SHOW CREATE VIEW ',table_schema,'.',table_name,';') from information_schema.views where table_schema='$db_name'" |\
	sed 's/;/\\G/g' | mysql -h$db_host -P$db_port -u$db_user -p$db_pwd $db_name |\
	sed 's/Create View: /kk_begin\n/g' | sed 's/[ ]*character_set_client:/;\nkk_end/g' |\
	sed -n '/kk_begin/{:a;N;/kk_end/!ba;s/.*kk_begin\|kk_end.*//g;p}'  >> $save_file
fi
 
# 函数
if [[ $output_type == *"function"* ]]
then
	echo "-- ------------------------------------------------------------" >> $save_file
	echo "-- function" >> $save_file
	echo "-- ------------------------------------------------------------" >> $save_file
	mysql -h$db_host -P$db_port -u$db_user -p$db_pwd --skip-column-names \
	-e "select concat('SHOW CREATE FUNCTION ',routine_schema,'.',routine_name,';') from information_schema.routines where routine_schema='$db_name' and ROUTINE_TYPE='FUNCTION'" |\
	sed 's/;/\\G/g' | mysql -h$db_host -P$db_port -u$db_user -p$db_pwd $db_name |\
	sed 's/Create Function: /kk_begin\ndelimiter $\n/g' | sed 's/[ ]*character_set_client:/$ \ndelimiter ;\nkk_end/g' |\
	sed -n '/kk_begin/{:a;N;/kk_end/!ba;s/.*kk_begin\|kk_end.*//g;p}' >> $save_file
 
fi
 
# 存储过程
if [[ $output_type == *"procedure"* ]]
then
	echo "-- ------------------------------------------------------------" >> $save_file
	echo "-- procedure" >> $save_file
	echo "-- ------------------------------------------------------------" >> $save_file
	mysql -h$db_host -P$db_port -u$db_user -p$db_pwd --skip-column-names \
	-e "select concat('SHOW CREATE PROCEDURE ',routine_schema,'.',routine_name,';') from information_schema.routines where routine_schema='$db_name' and ROUTINE_TYPE='PROCEDURE'" |\
	sed 's/;/\\G/g' | mysql -h$db_host -P$db_port -u$db_user -p$db_pwd $db_name |\
	sed 's/Create Procedure: /kk_begin\ndelimiter $\n/g' | sed 's/[ ]*character_set_client:/$ \ndelimiter ;\nkk_end/g' |\
	sed -n '/kk_begin/{:a;N;/kk_end/!ba;s/.*kk_begin\|kk_end.*//g;p}' >> $save_file
fi
 
# 事件
if [[ $output_type == *"event"* ]]
then
	echo "-- ------------------------------------------------------------" >> $save_file
	echo "-- event" >> $save_file
	echo "-- ------------------------------------------------------------" >> $save_file
	mysql -h$db_host -P$db_port -u$db_user -p$db_pwd --skip-column-names \
	-e "select concat('SHOW CREATE EVENT ',EVENT_SCHEMA,'.',EVENT_NAME,';') from information_schema.events where EVENT_SCHEMA='$db_name'" |\
	sed 's/;/\\G/g' | mysql -h$db_host -P$db_port -u$db_user -p$db_pwd |\
	sed 's/Create Event: /kk_begin\ndelimiter $\n/g' | sed 's/[ ]*character_set_client:/$ \ndelimiter ;\nkk_end/g' |\
	sed -n '/kk_begin/{:a;N;/kk_end/!ba;s/.*kk_begin\|kk_end.*//g;p}' >> $save_file
fi
 
# 触发器
if [[ $output_type == *"trigger"* ]]
then
	echo "-- ------------------------------------------------------------" >> $save_file
	echo "-- trigger" >> $save_file
	echo "-- ------------------------------------------------------------" >> $save_file
	mysql -h$db_host -P$db_port -u$db_user -p$db_pwd --skip-column-names \
	-e "select concat('SHOW CREATE TRIGGER ',TRIGGER_SCHEMA,'.',TRIGGER_NAME,';') from information_schema.triggers where TRIGGER_SCHEMA='$db_name';" |\
	sed 's/;/\\G/g' | mysql -h$db_host -P$db_port -u$db_user -p$db_pwd $db_name|\
	sed 's/SQL Original Statement: /kk_begin\ndelimiter $\n/g' | sed 's/[ ]*character_set_client:/$ \ndelimiter ;\nkk_end/g' |\
	sed -n '/kk_begin/{:a;N;/kk_end/!ba;s/.*kk_begin\|kk_end.*//g;p}' >> $save_file
fi
 
# ^M, you need to type CTRL-V and then CTRL-M
sed -i "s/\^M//g" $save_file

2、升级检查

mysqlcheck -u root -p --all-databases --check-upgrade

3、检查分区表（不支持分区的存储引擎）

select table_schema,table_name from information_schema.tables where engine not in ('innodb','ndbcluster') and create_options like '%partitioned';

如果存在记录，必须把对应表修改为innodb引擎或者把表修改为非分区的（alter table table_name engine=INNODB或alter table table_name remove partitioning);

4、在mysql数据库中，必须不存在与mysql8.0数据字典相同名称的表

所有同名的表必须要重命名。

select table_schema,table_name from information_schema.tables where lower(table_schema)='mysql' 
and lower(table_name) in (
       'catalogs',
       'character_sets',
       'collations',
       'column_statistics',
       'column_type_elements',
       'columns',
       'dd_properties',
       'events',
       'foreign_key_column_usage',
       'foreign_keys',
       'index_column_usage',
       'index_partitions',
       'index_stats',
       'indexes',
       'parameter_type_elements',
       'parameters',
       'resource_groups',
       'routines',
       'schemata',
       'st_spatial_reference_systems',
       'table_partition_values',
       'table_partitions',
       'table_stats',
       'tables',
       'tablespace_files',
       'tablespaces',
       'triggers',
       'view_routine_usage',
       'view_table_usage'
       ); 

5、必须要不存在外键超过64字符的表

SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
       WHERE TABLE_NAME IN
       (SELECT LEFT(SUBSTR(ID,INSTR(ID,'/')+1),
       INSTR(SUBSTR(ID,INSTR(ID,'/')+1),'_ibfk_')-1)
       FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN
       WHERE LENGTH(SUBSTR(ID,INSTR(ID,'/')+1))>64);

如果存在，必须修改表。

6、必须要确保不存在拥有ENUM或者SET列元素并且超过255字符或者1020字节长度的表和存储过程

7、mysql5.7中必须不使用8.0不支持的特性。例如NDB引擎，8.0不再支持；部分启动选项不再支持：例如--ignore-db-dir已被移除。

8、如果innodb使用了XA事务，确保所有XA事务提交或者回滚。

9、如果存在加密的innodb表空间，执行语句

alter instance rotate innodb master key;

10、如果mysql5.7启动设置了innodb_fast_shutdown参数为2（冷停止），通过设置参数为1或者0来实现快速或者慢停止

show variables like 'innodb_fast_shutdown';
# 确保数据都刷到硬盘上，更改成0
set global innodb_fast_shutdown=1; --fast shutdown
set global innodb_fast_shutdown=0; --slow shutdown

11、停止数据库

mysqladmin -u root -p shutdown

12、更改配置文件my.cnf

因5.7版本与8.0版本参数有所不同，为了能顺利升级，我们需要更改部分配置参数。主要注意symbolic-links、sql_mode、binlog_expire_logs_seconds、show_compatibility_56、密码认证插件及字符集设置。下面展示下更改后的配置文件：

[mysqld]
port=3306
datadir=/fsl_data/datafile
log-error=/fsl_data/log/mysqld.log
#mysql8默认禁用symbolic-links
#symbolic-links=0
bind-address=0.0.0.0
lower_case_table_names=1
character_set_server=utf8mb4
max_allowed_packet=500M
#mysql8取消NO_AUTO_CREATE_USER
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
#InnoDB用于缓存数据、索引、锁、插入缓冲、数据字典等
innodb_buffer_pool_size=4G
#InnoDB的log buffer
innodb_log_buffer_size = 64M
#InnoDB redo log大小
innodb_log_file_size = 256M
#InnoDB redo log文件组
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1
#连接数
max_connections=600
max_connect_errors=1000
max_user_connections=400
#设置临时表最大值
max_heap_table_size = 100M
tmp_table_size = 100M
#每个连接都会分配的一些排序、连接等缓冲
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
#mysql8已经没有query cache
#query_cache_size = 0
#如果是以InnoDB引擎为主的DB，专用于MyISAM引擎的 key_buffer_size 可以设置较小，8MB 已足够,如果是以MyISAM引擎为主，可设置较大，但不能超过4G
key_buffer_size = 8M
#设置慢查询阀值，单位为秒
long_query_time = 60
slow_query_log=1
log_output=table,File    #日志输出会写表，也会写日志文件，为了便于程序去统计，所以最好写表
slow_query_log_file=/fsl_data/log/slow.log
#快速预热缓冲池
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
#打印deadlock日志
innodb_print_all_deadlocks=1
#二进制配置
server-id = 1
log-bin = /fsl_data/log/mysql-bin.log
log-bin-index =/fsl_data/log/binlog.index
log_bin_trust_function_creators=1
binlog_format = row
gtid_mode = ON
enforce_gtid_consistency = ON
#mysql8中expire_logs_days参数取消，修改成binlog_expire_logs_seconds参数，单位为秒，以下代表15天
binlog_expire_logs_seconds=1296000

#schedule
event_scheduler = on
#mysql8取消了兼容5.6参数
#show_compatibility_56=on
#处理TIMESTAMP with implicit DEFAULT value is deprecated
explicit_defaults_for_timestamp=true
#mysql8改了默认加密方式为"caching_sha2_password",这里改回来
default_authentication_plugin=mysql_native_password
#禁用SSL提高性能
skip_ssl

三、MySQL8升级过程

下面以Linux系统为例，展示下具体升级过程。我的系统是CentOS7.7，原版本是MySQL5.7.27，以In-Place方式直接升级到MySQL8.0.19。

1、下载解压安装包

官网下载对应版本的tar包，可通过wget下载或者本地下载后上传。

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

>选择mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz

执行以下步骤解压tar包：

mkdir -p  /usr/local/mysql8
tar -xvf mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz -C /usr/local/mysql8
mv /usr/local/mysql8/mysql-8.0.19-linux-glibc2.12-x86_64/* /usr/local/mysql8
chown -R mysql.mysql /usr/local/mysql8/

2、执行升级程序

在mysql8.0.16版本后只需要使用新版本软件包启动MySQL Server，此时Server会自动检查并执行第一步升级操作，升级数据字典表。在第一步升级完成之后，Server分析系统表和用户表是否需要升级，如果指定了升级选项 upgrade 为AUTO或者FORCE，Server将会执行第二步升级操作，对系统表和用户表进行升级，如果第二步不需要升级，Server将会跳过第二步。

对于所有数据库对象，包括数据库、表空间、系统和用户表、视图，以及存储程序（存储过程和函数、触发器、事件调度程序事件）。服务器同时删除以前用于元数据存储的文件。例如升级后数据表不再有.frm文件。

如果升级完成，server将会创建一个backup_metadata_57的目录，目录中将会备份db.opt及以.frm,.par,.TRG,.TRN,.isl。

# 用mysql8.0.19客户端直接启动（/fsl_data/datafile/为需要升级的数据文件目录）
/usr/local/mysql8/bin/mysqld_safe --defaults-file=/etc/my.cnf 
    --datadir=/fsl_data/datafile/ --user=mysql &

3、重启数据库并验证

#停止数据库，再启动数据库查看升级结果:
/usr/local/mysql8/bin/mysqladmin -u root -p shutdown
/usr/local/mysql8/bin/mysqld_safe --user=mysql --datadir=/fsl_data/datafile/ 

4、卸载旧mysql数据库

停止数据库服务后操作如下：

# 查看MySQL服务
rpm -qa|grep mysql
# 卸载 mysql
rpm -qa|grep mysql|xargs -i rpm -e --nodeps {} 
# 还原配置文件
cp /etc/my.cnf.rpmsave /etc/my.cnf

5、设置MySQL8 自启动服务脚本
因basedir变成了/usr/local/mysql8，故相关环境变量推荐修改下。可按照以下步骤来操作验证：

vim /usr/lib/systemd/system/mysqld.service
====================================================================
[Unit]
Description=MySQL Community Server
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target
Alias=mysql.service

[Service]
# 启动服务
User=mysql
Group=mysql
# Start main service
ExecStart=/usr/local/mysql8/bin/mysqld  --defaults-file=/etc/my.cnf

# Sets open_files_limit
LimitNOFILE = 50000
====================================================================
# chmod u+x /usr/lib/systemd/system/mysqld.service
# systemctl daemon-reload
# systemctl start mysqld
# systemctl enable mysqld

6、设置环境变量

echo "PATH=$PATH:/usr/local/mysql8/bin" >> /etc/profile
source /etc/profile
which mysql

至此，我们的数据库由5.7成功升级至8.0！对比MySQL安装过程及升级过程，发现二者很相似，其实升级过程并不复杂，复杂的是升级后的验证及兼容测试，特别是对于复杂的业务库，MySQL版本升级还是要小心的。真实环境建议先升级从库，验证无误后再逐步对主库进行升级。