MySQL升级之路（5.6-8.0）

这几天需要升级 MySQL，又折腾了半天，这个升级之路还是有一点曲折的。

升级之前

在升级之前需要做几件事情：

做好备份

• 备份 /var/lib/mysql 路径下的所有文件

  cp -r /var/lib/mysql /var/lib/mysql_backup

• 备份 /etc/my.cnf 文件

  cp /etc/my.cnf /etc/my_backup.cnf

• 备份整个数据库

  mysqldump -u<username> -p --add-drop-table --routines --events --all-databases --force > data-for-upgrade.sql

确认更新内容

除了备份，还需要注意断档更新内容。主要是8.0的更新内容，详细内容可以参看 官方文档。

关闭数据库

当然还需要将数据库关闭，这是在升级之前的最后步骤。

1、关闭服务

systemctl stop mysqld

2、停止数据库

mysql -u<username> -p --execute="SET GLOBAL innodb_fast_shutdown=0"
mysqladmin -u<username> -p shutdown

从 5.6 升级到 5.7

MySQL的每一次升级都很曲折，它不支持跨版本升级，需要一级一级的升，这就很麻烦。这也是因为每次升级都有断档的内容，所以需要格外谨慎，必须做好备份。

5.6 到 5.7 其实还算稳定，做好备份之后，直接升级即可。

1、下载 rpm

cd ~
wget https://repo.mysql.com/mysql57-community-release-el7.rpm

2、移除 5.6 版本

yum remove mysql-community-release

如果在后面安装 5.7 版本时提示存在冲突错误，可以使用 mysql-community-* 直接删除所有相关内容。

3、下载安装包

rpm -ivh mysql57-community-release-e17.rpm

4、更新

yum update mysql

5、启动

systemctl start mysqld

如果成功，继续向下。如果失败，可以检查一下错误。启动失败，通常报这样的一句话：

Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

此时通过 systemctl status mysqld 可以查看服务的状态和基本错误信息。

通常，如果有以下错误的：

[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[ERROR] Failed to initialize builtin plugins.
[ERROR] Aborting

可以通过删除 /var/lib/mysql/ 文件夹中的以 ib_data 为前缀的文件修复。

如果有以下错误的：

[ERROR] Can‘t start server: Bind on TCP/IP port: Address already in use
[ERROR] Do you already have another mysqld server running on port: 3306 ?
[ERROR] Aborting

说明之前没有完全关闭 mysql，只需要将其关闭即可（方法 见上）。

其他情况大同小异，可以使用 journalctl -xe 查看细节并修正即可。

6、升级

mysql_upgrade -u<username> -p

如果此时报错，可以尝试添加 --force 参数。

升级之后不要忘记重启服务器：

systemctl restart mysqld

此时可以查看版本已经是5.7的了。

mysql -V

关于数据的错误

通常来说，这一步可能会导致系统表出错，在执行 mysql_upgrade 命令时，并不是全部显示 OK，其中一部分表会显示不存在，就像：

一般来说，它会出现在以下5张表：

mysql.innodb_index_stats
mysql.innodb_table_stats
mysql.slave_master_info
mysql.slave_relay_log_info
mysql.slave_worker_info

尝试进入 mysql 中删除这几张表：

use mysql;

drop table innodb_index_stats;
drop table innodb_table_stats;
drop table slave_master_info;
drop table slave_relay_log_info;
drop table slave_worker_info;

如果删除时已经报了不存在的错误，没关系，直接退出即可。

接下来删除表对应的文件，一般在 /var/lib/mysql/mysql 中，通过命令查询一下：

ls /var/lib/mysql/mysql

删除这几个框起来的文件即可：

rm /var/lib/mysql/mysql/innodb_*
rm /var/lib/mysql/mysql/slave_*

删除完成，重启一下服务器：

systemctl restart mysqld

最后，进入数据库重新创建这几张表即可：

use mysql;

CREATE TABLE `innodb_index_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `index_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `stat_value` bigint(20) unsigned NOT NULL,
  `sample_size` bigint(20) unsigned DEFAULT NULL,
  `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;

CREATE TABLE `innodb_table_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `n_rows` bigint(20) unsigned NOT NULL,
  `clustered_index_size` bigint(20) unsigned NOT NULL,
  `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;

CREATE TABLE `slave_master_info` (
  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file.',
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.',
  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last read event.',
  `Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'The host name of the master.',
  `User_name` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.',
  `User_password` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.',
  `Port` int(10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.',
  `Connect_retry` int(10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',
  `Enabled_ssl` tinyint(1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.',
  `Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.',
  `Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.',
  `Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.',
  `Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.',
  `Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.',
  `Ssl_verify_server_cert` tinyint(1) NOT NULL COMMENT 'Whether to verify the server certificate.',
  `Heartbeat` float NOT NULL,
  `Bind` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server',
  `Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs',
  `Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.',
  `Retry_count` bigint(20) unsigned NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.',
  `Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',
  `Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',
  `Enabled_auto_position` tinyint(1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',
  PRIMARY KEY (`Host`,`Port`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';

CREATE TABLE `slave_relay_log_info` (
  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.',
  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',
  `Relay_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.',
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.',
  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.',
  `Sql_delay` int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',
  `Number_of_workers` int(10) unsigned NOT NULL,
  `Id` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',
  PRIMARY KEY (`Id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';

CREATE TABLE `slave_worker_info` (
  `Id` int(10) unsigned NOT NULL,
  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Relay_log_pos` bigint(20) unsigned NOT NULL,
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Master_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Checkpoint_relay_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Checkpoint_master_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_seqno` int(10) unsigned NOT NULL,
  `Checkpoint_group_size` int(10) unsigned NOT NULL,
  `Checkpoint_group_bitmap` blob NOT NULL,
  PRIMARY KEY (`Id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information';

嗯，搞定~

从 5.7 到 8.0

升级前的准备工作

从 5.7 升级到 8.0 之前，需要做一些准备工作。

1、同样是需要检查所有表是否符合升级条件。

mysqlcheck -uroot -p --all-databases --check-upgrade

一定是其结果全部显示 OK 方可，如果有异常，按照提示进行操作。

2、不要使用不具备本级分区支持的存储引擎的分区表。可以通过以下命令查询：

SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE NOT IN ('innodb','ndbcluster') AND CREATE_OPTIONS LIKE '%partitioned%';

查询的结果如果有内容，请修改对应的表引擎或者直接删除分区：

ALTER TABLE <table_name> ENGINE = INNODB;

# or

ALTER TABLE <table_name> REMOVE PARTITIONING;

3、在 mysql 库中，不能有以下表名，如果有，请修改：

SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE LOWER(TABLE_SCHEMA) = 'mysql' AND LOWER(TABLE_NAME) IN ('catalogs', 'character_set', 'collations', 'column_type_elements', 'columns', 'events', 'foreign_key_column_usage', 'foreign_keys', 'index_column_usage', 'index_partitions', 'index_stats', 'indexes', 'parameter_type_elements', 'parameters', 'routines', 'schemeta', 'st_spatial_reference_systems', 'table_partition_values', 'table_partitions', 'table_stats', 'tables', 'tablespace_files', 'tablespaces', 'triggers', 'version', 'view_routine_usage', 'view_table_usage');

4、不能存在超过64个字符的表名，如果有，请修改：

SELECT CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE LENGTH(CONSTRAINT_NAME) > 64;

备份数据

备份数据是一定需要的，我们可以通过命令直接导出所有数据：

cd ~

mysqldump -u root -p --add-drop-table --routines --events --all-databases --ignore-table=mysql.innodb_table_stats --ignore-table=mysql.innodb_index_stats --force > data-for-upgrade.sql

并且在升级完成之后，如果数据出现异常，可以通过下面命令重新导入：

mysql -u<username> -p --force < data-for-upgrade.sql

升级到 8.0

做好了前期检查，就可以开始升级了。

首先关闭 mysql 服务器：

mysql -u<username> -p --execute='SET GLOBAL innodb_fast_shutdown=0;'

然后停止 mysql 的服务：

systemctl stop mysqld

切换仓库：

yum-config-manager --disable mysql57-community

yum-config-manager --enable mysql80-community

切换之后，查看一下是否成功：

yum repolist all | grep mysql8

可以看到已经成功：

下面就可以开始升级：

yum update mysql-server

升级跑完，可以开启 mysql 服务：

systemctl start mysqld

如果你安装的是 8.0.16 之前的版本，可以通过运行 mysql_upgrade 命令来查看更新的兼容问题，而在之后的版本，这不是必须的，并且会提示该工具已经被弃用。

现在可以查看 mysql 的版本：

mysql -V

或者直接进入 mysql 的界面查看都可以：

select version();

至此，升级之路大功告成~