MySQL Server upgrade with minimum downtime

Pavel Zeger is an open - source data platform consultant at NAYA-Tech.

MySQL server upgrade with minimum downtime

(as a part of migration from Ubuntu to CentOS)

We suggested to one of our client to migrate Linux servers from Ubuntu to CentOS and upgrade MySQL instances to new versions. The client’s requirement was minimum downtime of course. Here I’ll describe in-place upgrade process for MySQL 5.6 to MySQL 5.7.17 (last version at the time of upgrade).

My preferable Linux distro is CentOS not only because it’s free also it is (arguably) more stable and secure. Since CentOS has less frequent updates, that means that the software is tested for a longer period of time and only truly stable versions get released.

Unfortunately, I have to mention that Ubuntu has a rich documentation, active community and lots of tutorials available online as opposed to CentOS.

Prepare a new server

We got a new server from the DevOps team with CentOS 7 operating system. Before starting to migrate a MySQL instance let’s check what we got:

Check hostname:

# hostname -f

Check IP address:

# ip a

or

# ip addr show

Check OS type:

# cat /etc/os-release

or

# cat /etc/redhat-release

Check CentOS file system (we asked to install XFS filesystem instead of ext4 because it supports direct IO, journaling filesystem, better and faster flushing to disk and etc.):

# df -T | awk '{print $1,$2,$NF}' | grep "^/dev" | column -t

or

# mount | grep "^/dev" | column -t

or

# file -sL /dev/sda1

or

# cat /etc/fstab

or

# fsck -N /dev/sda1

Check disks space:

# df -h

We also asked to add a swap file in case of full utilization of memory. It’s not the best solution to allow to MySQL to start using swapping (it’s very slow) but in order to prevent MySQL service’s termination by a machine (when this process utilize to much memory) I prefer to have MySQL server running instead of terminated by OS.

Please remember that in CentOS the default value of swappiness is 30% (in Ubuntu this default is larger!) so as a general recommendation we can change the swappiness on the fly and after thoroughly benchmarking we can change it forever.

First of all check the whole memory of the server and swap file’s size:

# free -h

Change swappiness percent on the fly:

# echo 1 > /proc/sys/vm/swappiness

or

# sysctl -w vm.swappiness=1

Verify the change:

# cat /proc/sys/vm/swappiness

or

# sysctl vm.swappiness

In order to change this value so it will take effect after reboot too go to the file /etc/sysctl.conf and add the following line: vw.swappiness=1. Reboot the system by reboot command.

Check MySQL version on the previous server:

# mysql -u root -p -e ‘SELECT version();’

It’s also highly recommend to do benchmarking and stress your new system before migration in order to examine I/O, memory and etc.

Upgrade process from MySQL 5.6 to MySQL 5.7

We can upgrade MySQL in two ways:

In-Place
Logical

The first involves shutting down the old MySQL version, replacing the old MySQL binaries or packages with the new ones, restarting MySQL on the existing data directory, and running mysql_upgrade.

The second involves exporting existing data from the old MySQL version using mysqldump, installing the new MySQL version, loading the dump file into the new MySQL version, and running mysql_upgrade.

In my case I choose in-place upgrade.

First of all we need to install a new MySQL instance on the new server with CentOS:

# yum update

# wget http://repo.mysql.com/mysql57-community-release-el7-9.noarch.rpm

# rpm -ivh mysql57-community-release-el7-9.noarch.rpm

In order to install the 5.6 version we have to disable 5.7 version by setting “enabled=0" and “enabled=1” to 5.6 in this configuration file:

# vim /etc/yum.repos.d/mysql-community.repo

# yum update

To prevent unneccesary update of other programs run, for example:

# yum --exclude=puppet\* update

Install the MySQL server:

# yum install mysql-server

Verify you install 5.6.XX version before continue installation’s process!

Start MySQL server:

# systemctl start mysqld

Complete installation with security settings:

# mysql_secure_installation

If we want to copy data directory from the previous server we need to stop the MySQL process first:

# systemctl stop mysqld

One of the ways to restore schemas from the previous server on the new is copying of data direstory. Also we copy a my.cnf file and change it as needed (server_id and etc.).

# scp /etc/my.cnf 192.168.0.120:/etc

or

# rsync /etc/my.cnf root@192.168.0.120:/etc/my.cnf

Shutdown the old MySQL server on Ubuntu:

# mysql -u root -p -e 'SET GLOBAL innodb_fast_shutdown = 0;'

# service mysql stop

Copy data directory to the new server:

# rsync -avz --progress /var/lib/mysql root@192.168.0.120:/var/lib/mysql

or

# rsync -avHPe "ssh –p2322" root@10.51.7.18:/root/data_for_upgrade.sql /root/data_for_upgrade/data_for_upgrade.sql

To prevent MySQL server startup’s failure we have to delete an old auto.cnf file and give permissions to all files to mysql user:

# cd /var/lib/mysql

# rm /var/lib/mysql/auto.cnf

# ls -lrtah /var/lib/mysql

# chown -R mysql:mysql /var/lib/mysql

If you decided to create a dump file on the previous server you can use this method (or Percona XtraBackup tool):

# mysql –u root –p –e ‘FLUSH TABLES WITH READ LOCK;’ (optionally)

# mysqldump -h localhost –u root –p[password] --all-databases --master-data --max_allowed_packet=1G --single_transaction --force --add-drop-table --quick --routines --events --extended-insert | gzip [file name].gz 2>> dump-log

# mysql –u root –p –e ‘UNLOCK TABLES;’ (optionally)

Now start both servers:

Ubuntu: # service mysql start

CentOS: # systemctl start mysqld

Verify that services are active:

Ubuntu: # service mysql status

CentOS: # systemctl status mysqld

If you created a dump file restore it on the new server:

# gunzip < [file name].gz | mysql -u root --password=[password] -q --max_allowed_packet=1073740800 --force 2>&1 >> [logfile name]

It’s time to upgrade the new server to MySQL 5.7:

# mysql -u root -p -e “SET GLOBAL innodb_fast_shutdown = 0;”

# systemctl stop mysqld

Upgrade MySQL binaries and packages through yum repository (You have to disable 5.6 version by "enaled=0" and enable 5.7 version by "enable=1"):

# vim /etc/yum.repos.d/mysql-community.repo

# yum update

# systemctl start mysqld

# mysql_upgrade -p --verbose.

# mysqladmin -u root -p shutdown

# systemctl start mysqld

The following procedures involves additional steps to synchronize and check both servers. I will describe it in my next posts. Of can you can find a lot of tutorials how to do it: