MySQL5.7版本单节点大数据量迁移到PXC8.0版本集群全记录-2

本文主要记录57版本升级80版本的过程，供参考。

■ 57版本升级80版本注意事项

1. 默认字符集由latin1变为utf8mb4
2. MyISAM系统表全部换成InnoDB表
3. sql_mode参数默认值变化，8.0版本sql_mode不支持 NO_AUTO_CREATE_USER，要避免配置的sql_mode中带有 NO_AUTO_CREATE_USER
4. 密码认证插件变更，由于默认密码策略变更，为了避免连接问题，可仍采用5.7的mysql_native_password认证插件
5. 关于系统表升级，在MySQL 8.0.16版本之前需手动执行mysql_upgrade完成升级，在MySQL 8.0.16版本及之后由mysqld来完成该系统表升级

■ 备份原57版本配置文件，停库
停库前，确保数据都刷到硬盘上，innodb_fast_shutdown 需改成 0

show variables like 'innodb_fast_shutdown';+----------------------+-------+| Variable_name| Value |+----------------------+-------+| innodb_fast_shutdown | 1 |+----------------------+-------+set global innodb_fast_shutdown=0;shutdown;

可见默认的停库模式是fast，需改掉彻底停库，将数据刷到磁盘上。

■ 卸载57版本所有的rpm包，安装80版本相关的rpm包
这一步比较简单，略过。

■ 更改部分配置参数
因5.7版本与8.0版本参数有所不同，为了能顺利升级，需更改部分配置参数，主要注意sql_mode、basedir、密码认证插件及字符集设置，其他参数建议照搬原5.7配置。
以下是本次升级的新配置文件my.cnf重点需要关注的部分：

# From source serverdatadir=/u01/mysql/datasocket=/u01/mysql/mysql.socklog-error=/u01/mysql/log/mysqld.logpid-file=/u01/mysql/mysqld.pidcharacter-set-server=utf8max_connections=1000lower_case_table_names=1max_allowed_packet=500M#sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONsql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'gtid-mode=onenforce-gtid-consistency=1log-bin=mysql-binskip_slave_start=1auto_increment_increment=2auto_increment_offset=1default-time_zone='+8:00'# Other confskip_ssldefault_authentication_plugin=mysql_native_password

■ 直接启动mysql服务
systemctl start mysql@bootstrap

■ 第一次升级
报错如下：

2021-12-02T04:03:07.042653Z 4 [ERROR] [MY-013235] [Server] Error in parsing Routine 'dciom_nrm'.'NEXTVAL2' during upgrade. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE NAME = n);^MIF isnull(nextVal)^MTHEN INSERT INTO SEQUENCE_TABLE VALUES' at line 62021-12-02T04:03:07.046907Z 4 [ERROR] [MY-013235] [Server] Error in parsing Routine 'scidc_rm'.'NEXTVAL2' during upgrade. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE NAME = n);^MIF isnull(nextVal)^MTHEN INSERT INTO SEQUENCE_TABLE VALUES' at line 62021-12-02T04:03:07.968182Z 4 [Waing] [MY-010200] [Server] Resolving dependency for the view 'dciom_monitor.kpi_perform_model_object' failed. View is no more valid to use2021-12-02T04:03:08.333967Z 4 [Waing] [MY-010200] [Server] Resolving dependency for the view 'dciom_monitor_custom.v_last_24hours' failed. View is no more valid to use2021-12-02T04:03:08.519573Z 4 [Waing] [MY-010200] [Server] Resolving dependency for the view 'dciom_nrm.v_resource_union' failed. View is no more valid to use2021-12-02T04:03:09.707344Z 0 [ERROR] [MY-010022] [Server] Failed to Populate DD tables.2021-12-02T04:03:09.709096Z 0 [ERROR] [MY-010119] [Server] Aborting2021-12-02T04:03:09.709123Z 0 [Note] [MY-000000] [WSREP] Initiating SST cancellation2021-12-02T04:03:11.709262Z 0 [Note] [MY-000000] [WSREP] Server status change initializing -> disconnecting2021-12-02T04:03:11.709302Z 2 [Note] [MY-000000] [WSREP] rollbacker thread exiting 22021-12-02T04:03:11.709362Z 0 [Note] [MY-000000] [WSREP] wsrep_notify_cmd is not defined, skipping notification.2021-12-02T04:03:11.709442Z 0 [Note] [MY-000000] [Galera] Closing send monitor...2021-12-02T04:03:11.709497Z 0 [Note] [MY-000000] [Galera] Closed send monitor.2021-12-02T04:03:11.709552Z 0 [Note] [MY-000000] [Galera] gcomm: terminating thread2021-12-02T04:03:11.709609Z 0 [Note] [MY-000000] [Galera] gcomm: joining thread2021-12-02T04:03:11.709718Z 0 [Note] [MY-000000] [Galera] gcomm: closing backend2021-12-02T04:03:11.709816Z 0 [Note] [MY-000000] [Galera] PC protocol downgrade 1 -> 02021-12-02T04:03:11.709860Z 0 [Note] [MY-000000] [Galera] Current view of cluster as seen by this nodeview ((empty))2021-12-02T04:03:11.709901Z 1 [ERROR] [MY-000000] [Galera] Exception: State wait was interrupted2021-12-02T04:03:11.709995Z 1 [ERROR] [MY-000000] [Galera] View callback failed. This is unrecoverable, restart required. (FATAL)

可以看出这个错误的原因是函数语法格式出了问题，升级前先删掉这个函数再试一试：
drop function dciom_nrm.NEXTVAL2;
drop function scidc_rm.NEXTVAL2;
同时删掉几个带警告的视图：
drop view dciom_monitor.kpi_perform_model_object;
drop view dciom_monitor_custom.v_last_24hours;
drop view dciom_nrm.v_resource_union;

■ 再次升级
报错如下：

2021-12-07T09:35:55.942763Z 7 [System] [MY-013381] [Server] Server upgrade from '50700' to '80023' started.2021-12-07T09:35:59.121596Z 7 [ERROR] [MY-000000] [WSREP] Percona-XtraDB-Cluster prohibits use of ADMIN command on a table (dciom_duty.t_duty_class) that resides in non-transactional storage engine with pxc_strict_mode = ENFORCING or MASTER2021-12-07T09:35:59.128650Z 0 [ERROR] [MY-013380] [Server] Failed to upgrade server.2021-12-07T09:35:59.128741Z 0 [ERROR] [MY-010119] [Server] Aborting

看来是pxc_strict_mode = ENFORCING设置问题，改为PERMISSIVE

ok！成功升级到80版本！

mysql> select version();+-------------+| version()|+-------------+| 8.0.23-14.1 |+-------------+

不过关于这个参数pxc_strict_mode的配置是有隐患的，详见之前的博客：
http://liking.site/2018/12/06/mysql-pxc集群如何导入无主键表/