Migration From 5.6 to 8.xx.xx

Luis Dias - Oracle MySQL - MySQL Track Room - Percona Live 2021

Intro

I start working in a Belgium company that use Mysql 5.6. We use 6 production servers and we decide to migrate from this old version to mysql 8.0.19. All the servers have Centos 7 system (so RPM and YUM available)

The first test was done by devops team, they use mysqldump to make this job, but as each server is about 4T the down time was over 30 hours and it’s not acceptable for customer. It’s when I take this task and start reading docs.. I saw that there is the IN - PLACE upgrade that just change the binarys and catalog, but don’t move any byte of the DB data.

I In TEST

First I connect to slack mysql Channel and I start talking with Fred. He give me a very nice documentation and advises to achieve this migration with a big % of probability to be fine.

I create a copy of the main DB in test ENV, and I apply the procedure with yum. The migration complete after 2 hours. This down time is acceptable.

II Issues found

The migration process is simple, but depending on what you have in your DB, you can face some stranges situations. For example, we use puppet in our servers, and puppet manage the /etc/my.cnf le, so in TEST I have the first migration CRASH because of puppet. We just comment puppet in the crontab to avoid this issue

Other issue was some warnings after the util checkForServerUpgrade

‘NO_ZERO_DATE’, ‘NO_ZERO_IN_DATE’
The syntax ’expire-logs-days’ is deprecated
character-set-server: ‘utf8’

Warnings easy to resolve

III Timings

For 4T of data, the miration was about 2 hours for the whole process. The upgrade catalog is the process that take more time. Use screen or tmux for this step :)

IV Why mysql 8

We stay with Mysql DB because for us it’s a very good product, the performences are fine and the new features created by the version 8.xx are amazing, this are the most important for us:

The shell dump

I make some comparative tests and the same server instance can be dumped in 2 hours when mysqldump was over 9 hours to complete. And I check that the shell dump don’t create locks. The fact that this shell dump don’t create locks is important because CLONE procedure use shell dump to create the replicas, and this performances and no locks allow to create replicas during the working period.

Replicaset

I start working 15 years ago as Oracle DBA, and I use Oracle dataguard broker to manager switchovers for example. With Replicaset I found a very nice set of commands to manage Source and Replicas very easy. I use it to move old Centos 6 version servers to Centos 7 without downtime, just switching the Master role.

Plugins

I was looking for easy ways to catch locks or bad queries or metadata stats from Mysql Catalog, the plugins are the answer. There are a lot of them already created and people can create their own plugin. ∎