DB-유

Upgrading MySQL 5.6 to 5.7 In Place

· mysql, upgrade, linux

I still have a few 5.6 servers that I kept putting off, and with 8.0 out the door I ran out of excuses. 5.7 has been GA for a while and gives you native JSON, better InnoDB performance, the sys schema and multi-threaded replication. An in-place upgrade, where the 5.7 binaries start on the existing 5.6 data directory, is the quickest path, as long as you prepare for the handful of incompatible changes.

I do this on CentOS 7 with the Oracle yum repository and Ubuntu 16.04 with the MySQL APT repository. Three things first. Have a full backup (mysqldump --all-databases or XtraBackup) and a tested restore, because an in-place upgrade has no downgrade button. Be on the latest 5.6.x patch release, since skipping a series (5.5 straight to 5.7) isn't supported. And plan a window of 15 to 60 minutes; mysql_upgrade touches every table.

Look for incompatibilities first

Look for things 5.7 rejects or changes:

-- old-style password hashes must go
SELECT user, host, plugin FROM mysql.user WHERE plugin = 'mysql_old_password' OR LENGTH(authentication_string) = 16;
-- partitioned tables with a non-native partitioning handler
SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.TABLES WHERE CREATE_OPTIONS LIKE '%partitioned%' AND ENGINE <> 'InnoDB';
-- DATETIME columns in pre-5.6.4 format
SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.TABLES WHERE ROW_FORMAT IS NOT NULL AND ENGINE='InnoDB' AND TABLE_SCHEMA NOT IN ('mysql','performance_schema','information_schema');

Also review my.cnf for options removed in 5.7. The ones I run into most:

  • innodb_additional_mem_pool_size and innodb_use_sys_malloc: removed; delete them.
  • storage_engine: renamed to default_storage_engine.
  • thread_concurrency, innodb_locks_unsafe_for_binlog: removed or deprecated.
  • sql_mode: the 5.7 default adds STRICT_TRANS_TABLES, NO_ZERO_DATE, NO_ZERO_IN_DATE, ERROR_FOR_DIVISION_BY_ZERO and ONLY_FULL_GROUP_BY. Applications written for 5.6 often break on ONLY_FULL_GROUP_BY.

If you can't fix the application before the upgrade, set sql_mode explicitly to what 5.6 used. I do this on the first pass and tighten it later, because changing the engine and the SQL behavior in the same window makes it hard to tell what broke:

[mysqld]
sql_mode = NO_ENGINE_SUBSTITUTION

Slow shutdown

Do a slow shutdown so InnoDB flushes all dirty pages and the undo logs are clean; 5.7 changes the redo log format and refuses to start otherwise.

SET GLOBAL innodb_fast_shutdown = 0;

sudo systemctl stop mysqld     # CentOS
sudo systemctl stop mysql      # Ubuntu

Confirm in the error log that the shutdown completed (Shutdown complete).

Swap the packages

CentOS 7 with the MySQL community repository:

sudo yum install yum-utils
sudo yum-config-manager --disable mysql56-community
sudo yum-config-manager --enable mysql57-community
sudo yum update mysql-community-server

Ubuntu 16.04 with the MySQL APT repository:

sudo dpkg-reconfigure mysql-apt-config    # select MySQL Server 5.7
sudo apt-get update
sudo apt-get install mysql-server

If you were using the Ubuntu-distribution 5.7 packages in xenial all along, apt-get dist-upgrade does the same.

Start 5.7 and run mysql_upgrade

sudo systemctl start mysqld
sudo tail -n 50 /var/log/mysqld.log

Then upgrade the system tables and check every user table:

mysql_upgrade -u root -p

mysql_upgrade rebuilds mysql.user into the 5.7 layout (the password column becomes authentication_string), creates the sys schema, upgrades Performance Schema tables and runs CHECK TABLE ... FOR UPGRADE on everything. On a server with thousands of tables it can take a while; add --skip-sys-schema if you do not want sys, or --force to rerun it after a failed attempt. Restart afterwards:

sudo systemctl restart mysqld

After the restart

SELECT VERSION();
SHOW VARIABLES LIKE 'sql_mode';
SELECT * FROM sys.version;
SHOW ENGINE INNODB STATUS\G

Run the application test suite or at least the most important queries. On a replica, check SHOW SLAVE STATUS\G shows both threads running; 5.7 slaves can replicate from a 5.6 master, which is the recommended order for upgrading a replication topology (slaves first, then promote).

What broke for me

  • [ERROR] InnoDB: Unsupported redo log format. The redo log was created with MySQL 5.7.x after a failed upgrade and attempted rollback: 5.6 cannot read 5.7 redo logs. This is why the backup matters; restore it into a fresh 5.6 data directory.
  • [ERROR] unknown variable 'innodb_additional_mem_pool_size=20M': a removed option in my.cnf. The server refuses to start until it is deleted.
  • ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ... incompatible with sql_mode=only_full_group_by: the new default sql_mode. Either fix the query or set sql_mode as shown above.
  • mysql_upgrade: [ERROR] 1524: Plugin 'mysql_old_password' is not loaded for a user: that account has a pre-4.1 hash and cannot log in anymore. Reset its password with ALTER USER.
  • Table 'mysql.plugin' doesn't exist or Native table 'performance_schema'.'...' has the wrong structure in the log: mysql_upgrade has not run yet or failed. Run it again with --force.

The upgrade itself is boring. The audit of my.cnf and sql_mode is where the time goes.