🥇Upgrade MySQL (Percona Server) from 5.7 to 8.0

Progress does not stand still, so the reasons to upgrade to the latest versions of MySQL are becoming more and more significant. Not so long ago, in one of our projects, it was time to upgrade cozy clusters of Percona Server 5.7 to version 8. All this happened on the Ubuntu Linux 16.04 platform. How to perform such an operation with minimal downtime and what problems we encountered during the upgrade - read this article.

Prepare

Any update of the database server is most likely associated with reconfiguring the database: changes in the requirements for limits on system resources and fixing the database configs, which must be cleared of obsolete directives.

Before updating, we will definitely refer to the official documentation:

Let's make a plan of action:

Fix configuration files by removing obsolete directives.
Check compatibility with utilities.
Update slave databases by installing the package percona-server-server.
Update the master by installing the same package.

Let's break down each point in the plan and see what can go wrong.

IMPORTANT! The procedure for updating a Galera-based MySQL cluster has its own subtleties that are not described in the article. Do not use this instruction in this case.

Part 1: Checking configs

Removed in version 8 of MySQL query_cache. Actually he was deprecated still in version 5.7, but now also removed altogether. Accordingly, it is necessary to remove the related directives. And for query caching, you can now use external tools - for example, ProxySQL.

Also in the config there were outdated directives about innodb_file_format. If in MySQL 5.7 it was possible to select the InnoDB format, then the 8th version is already working only with Barracuda format.

Our result is the removal of the following directives:

query_cache_type, query_cache_limit и query_cache_size;
innodb_file_format и innodb_file_format_max.

For verification, we will use the Percona Server Docker image. The server config will be placed in the directory mysql_config_test, and next we will create directories for data and logs. Percona-server configuration test example:

mkdir -p {mysql_config_test,mysql_data,mysql_logs}
cp -r /etc/mysql/conf.d/* mysql_config_test/
docker run  --name some-percona -v $(pwd)/mysql_config_test:/etc/my.cnf.d/  -v $(pwd)/mysql_data/:/var/lib/mysql/ -v $(pwd)/mysql_logs/:/var/log/mysql/ -e MYSQL_ROOT_PASSWORD=${MYSQL_PASSWORD} -d percona:8-centos

Bottom line: either in the Docker logs or in the log directory - depending on your configs - a file will appear in which the problematic directives will be described.

Here's what we had:

2020-04-03T12:44:19.670831Z 0 [Warning] [MY-011068] [Server] The syntax 'expire-logs-days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
2020-04-03T12:44:19.671678Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2020-04-03T12:44:19.671682Z 0 [Warning] [MY-013244] [Server] --collation-server: 'utf8_general_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.

Thus, we still needed to deal with encodings and replace the obsolete directive expire-logs-days.

Part 2: Checking running installations

The update documentation contains 2 utilities for checking the base for compatibility. Their use helps the administrator to check the compatibility of the existing data structure.

Let's start with the classic mysqlcheck utility. It's easy enough to run:

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

If no problems are found, the utility will exit with code 0:

In addition, a utility is available in modern versions of MySQL. mysql shell (in the case of Percona, this is a package percona-mysql-shell). It is a replacement for the classic mysql client and combines the functions of a client, SQL code editor and MySQL administration tools. To check the server before updating, you can run the following commands through it:

mysqlsh -- util check-for-server-upgrade { --user=root --host=1.1.1.1 --port=3306 } --config-path=/etc/mysql/my.cnf

Here are the comments we received:

In general, nothing critical - only warnings about encodings (see below). The overall result of the execution:

We decided that the update should go without problems.

A note about warnings above indicating problems with encodings. The fact is that UTF-8 in MySQL until recently was not "real" UTF-8, because it stored only 3 bytes instead of 4. In MySQL 8, this is finally decided to fix: alias utf8 will soon lead to encoding utf8mb4, and the old columns in the tables will become utf8mb3. Further coding utf8mb3 will be removed, but not in this release. Therefore, we decided to fix the encodings already on the running DBMS installation after it was updated.

Part 3: Upgrading servers

What can go wrong when there is such a smart plan? .. Knowing full well that nuances always happen, we conducted the first experiment on a MySQL dev cluster.

As already mentioned, official documentation covers the issue of upgrading MySQL servers with replicas. The bottom line is that you should first update all replicas (slave), since MySQL 8 can replicate from the master version 5.7. Some difficulty lies in the fact that we use the mode master <-> masterwhen the remote master is in read-only. That is, in fact, combat traffic enters one data center, and the 2nd one is a backup.

The topology looks like this:

The update should start with replicas mysql replica dc 2, mysql master dc 2 и mysql replica dc 1, and end with the mysql master dc 1 server. For greater reliability, we stopped the virtual machines, made their snapshots, and just before the update, we stopped replication with the command STOP SLAVE. The rest of the update looks like this:

We restart each replica by adding 3 options to the configs: skip-networking, skip-slave-start, skip-log-bin. The fact is that updating the database generates binary logs with updating system tables. These directives guarantee that there will be no changes in the application data in the database, and information about updating system tables will not get into the binary logs. This will avoid problems when you resume replication.
Installing the package percona-server-server. It is important to note that in MySQL version 8 not need to run command mysqlupgrade after server update.
After a successful start, we restart the server again - already without the parameters that were added in the first paragraph.
We make sure that replication is working successfully: we check SHOW SLAVE STATUS and see that tables with counters in the application database are updated.

It all looks simple enough: the dev update was successful. Ok, you can safely schedule a nightly update for production.

There was no sadness - we updated prod

However, the transfer of successful dev experience to production was not without surprises.

Fortunately, the update process itself starts with replicas, so, having encountered difficulties, we stopped work and restored the replica from the snapshot. The study of problems was postponed to the next morning. The logs contained the following entries:

2020-01-14T21:43:21.500563Z 2 [ERROR] [MY-012069] [InnoDB] table: t1 has 19 columns but InnoDB dictionary has 20 columns
2020-01-14T21:43:21.500722Z 2 [ERROR] [MY-010767] [Server] Error in fixing SE data for db1.t1
2020-01-14T21:43:24.208365Z 0 [ERROR] [MY-010022] [Server] Failed to Populate DD tables.
2020-01-14T21:43:24.208658Z 0 [ERROR] [MY-010119] [Server] Aborting

Researching the archives of various mailing lists on Google led to the realization that this problem occurs due to MySQL bug. Although it’s more likely even a utility bug mysqlcheck и mysqlsh.

It turns out that MySQL has changed the way data is represented for decimal fields (int, tinyint, etc.), so mysql-server uses a different way to store them inside. If your database initially was in version 5.5 or 5.1, and then you upgraded to 5.7, you may need to OPTIMIZE for some tables. Then MySQL will update the data files, transferring them to the current storage format.

It can also be checked with the utility mysqlfrm:

mysqlfrm --diagnostic -vv /var/lib/mysql/db/table.frm
...
 'field_length': 8,
  'field_type': 246, # формат поля
  'field_type_name': 'decimal',
  'flags': 3,
  'flags_extra': 67,
  'interval_nr': 0,
 'name': 'you_deciaml_column',
...

If field_type you have 0, then the old type is used in the table - you need to carry out OPTIMIZE. However, if the value is 246, you already have a new type. For more information on types, see code.

Moreover, in this bug the second possible reason is considered, which bypassed us, is the absence of InnoDB tables in the system table INNODB_SYS_TABLESPACESif they, tables, were created in version 5.1. To avoid problems during the update, you can use attached SQL script.

Why didn't we have such problems on dev? The database is periodically copied there from production - thus, tables are recreated.

Unfortunately, on a really working large database, it will not be possible to simply take and execute a ubiquitous OPTIMIZE. This is where percona-toolkit comes in handy: for the online OPTIMIZE operation, the pt-online-schema-change utility is great.

The updated plan is as follows:

Optimize all tables.
Perform database updates.

To check it and at the same time find out the update time, we disabled one of the replicas, and ran the following command for all tables:

pt-online-schema-change --critical-load Threads_running=150 --alter "ENGINE=InnoDB" --execute --chunk-size 100 --quiet --alter-foreign-keys-method auto h=127.0.0.1,u=root,p=${MYSQL_PASSWORD},D=db1,t=t1

Tables are updated without long locks due to the fact that the utility creates a new temporary table into which it copies data from the main table. At the moment when both tables are identical, the original table is locked and replaced by a new one. In our case, the test run showed that it would take about a day to update all the tables, but at the same time, copying the data caused too much load on the disks.

To avoid this, in production we added an argument to the command --sleep with a value of 10 - this parameter controls the length of the wait after transferring a batch of data to a new table. This way you can reduce the load if the actual running application is demanding on response time.

After performing the optimization, the update was successful.

… but not completely!

Half an hour after the update, the client came with a problem. The base worked very strange: periodically started connection drops. Here's how it looked in monitoring:

The screenshot shows a sawtooth graph due to the fact that some of the MySQL server threads periodically crashed with an error. The application has errors:

[PDOException] SQLSTATE[HY000] [2002] Connection refused

A quick look at the logs revealed that the mysqld daemon was unable to obtain the required resources from the operating system. Dealing with errors, we found in the system "orphaned" apparmor policy files:

# dpkg -S /etc/apparmor.d/cache/usr.sbin.mysqld
dpkg-query: no path found matching pattern /etc/apparmor.d/cache/usr.sbin.mysqld
# dpkg -S /etc/apparmor.d/local/usr.sbin.mysqld
dpkg-query: no path found matching pattern /etc/apparmor.d/local/usr.sbin.mysqld
# dpkg -S /etc/apparmor.d/usr.sbin.mysqld
mysql-server-5.7: /etc/apparmor.d/usr.sbin.mysqld
# dpkg -l mysql-server-5.7
rc  mysql-server-5.7 5.7.23-0ubuntu0.16.04.1      amd64

These files came from an upgrade to MySQL 5.7 a couple of years ago and belong to a removed package. Deleting the files and restarting the apparmor service solved the problem:

systemctl stop apparmor
rm /etc/apparmor.d/cache/usr.sbin.mysqld
rm /etc/apparmor.d/local/usr.sbin.mysqld
rm /etc/apparmor.d/usr.sbin.mysqld
systemctl start apparmor

In conclusion

Any, even the most simple operation, can lead to unexpected problems. And even having a well-thought-out plan does not always guarantee the expected result. Now, any update plans of our team also include the obligatory cleaning of unnecessary files that may have appeared as a result of recent actions.

And with this not very professional graphic creation, I would like to say a huge thank you to Percona for their excellent products!

Upgrading MySQL (Percona Server) from 5.7 to 8.0