Upgrading MySQL is pretty straight forward, however there are some catches. Per MySQL’s official documentation, you must upgrade from 5.1 to 5.5, then upgrade from 5.5 to 5.6, and finally again from 5.6 to 5.7. You cannot upgrade from 5.1 directly to 5.7.
This guide will outline how to upgrade MySQL 5.1 to MySQL 5.7 in sequence and will assume you are using the IUS repositories for MySQL. If the IUS repositories are not already setup, you can install them by:
# CentOS 6 [root@db01 ~]# yum install epel-release [root@db01 ~]# rpm -ivh https://dl.iuscommunity.org/pub/ius/stable/CentOS/6/x86_64/ius-release-1.0-15.ius.centos6.noarch.rpm # CentOS 7 [root@db01 ~]# yum install epel-release [root@db01 ~]# rpm -ivh https://dl.iuscommunity.org/pub/ius/stable/CentOS/7/x86_64/ius-release-1.0-15.ius.centos7.noarch.rpm
Upgrade from MySQL 5.1 to MySQL 5.5
Create a restore point. This will be backing up the databases, the my.cnf, as well as a user and database listing. This can be done by:
[root@db01 ~]# mysql -S mysqldump
[root@db01 ~]# mkdir -p /root/mysqlupgrade/mysql51
[root@db01 ~]# mysql -e 'show databases;' > /root/mysqlupgrade/mysql51/mysql-5.1.databases
[root@db01 ~]# mysql --silent --skip-column-names --execute "select concat('\'',User,'\'@\'',Host,'\'') as User from mysql.user;" | sort | while read u; do echo "-- $u"; mysql --silent --skip-column-names --execute "show grants for $u" | sed 's/$/;/'; done > /root/mysqlupgrade/mysql51/mysql-5.1.grants
[root@db01 ~]# cp /etc/my.cnf /root/mysqlupgrade/mysql51/mysql-5.1.cnf.orig
[root@db01 ~]# yum install xz
[root@db01 ~]# mysqldump --routines --all-databases | xz > /root/mysqlupgrade/mysql51/mysql-5.1.dump.sql.xz
Now stop MySQL and upgrade it to MySQL 5.5:
[root@db01 ~]# service mysqld stop [root@db01 ~]# yum --disableexcludes=all shell remove mysql mysql-server mysql-libs install mysql55 mysql55-server mysql55-libs mysqlclient16 ts solve ts run exit
Generate a version of the my.cnf that is valid for MySQL 5.5. Some options get deprecated or changed between versions, so it is important that this is not skipped. To help with this, we’ll be using dbsake as shown below:
[root@db01 ~]# wget -O /root/mysqlupgrade/dbsake http://get.dbsake.net; chmod u+x /root/mysqlupgrade/dbsake [root@db01 ~]# /root/mysqlupgrade/dbsake upgrade-mycnf --config /root/mysqlupgrade/mysql51/mysql-5.1.cnf.orig --target 5.5 > /root/mysqlupgrade/mysql-5.5.cnf [root@db01 ~]# mv /etc/my.cnf /etc/mysql-5.5.cnf.orig [root@db01 ~]# mv -f /root/mysqlupgrade/mysql-5.5.cnf /etc/my.cnf
Start MySQL without the grant tables to verify it is running MySQL 5.5 and all the databases loaded:
[root@db01 ~]# sed -i 's/\[mysqld\]/[mysqld]\nskip-grant-tables\nskip-networking/' /etc/my.cnf [root@db01 ~]# service mysqld start [root@db01 ~]# mysql -sse "select @@version" [root@db01 ~]# mysql -e "show databases;" > /root/mysqlupgrade/mysql51/mysql-5.5.databases [root@db01 ~]# diff -U0 /root/mysqlupgrade/mysql51/mysql-5.1.databases /root/mysqlupgrade/mysql51/mysql-5.5.databases
Now update the database schema. If mysql_upgrade says that a table needs to repaired, repair it with mysqlcheck -A before moving on:
[root@db01 ~]# mysql_upgrade [root@db01 ~]# sed -i '/\(skip-grant-tables\|skip-networking\)/d' /etc/my.cnf [root@db01 ~]# service mysqld restart
Finally, confirm MySQL is running version 5.5:
[root@db01 ~]# mysqladmin version
Upgrade from MySQL 5.5 to MySQL 5.6
Create a restore point. This will be backing up the databases, the my.cnf, as well as a user and database listing. This can be done by:
[root@db01 ~]# mysql -S mysqldump
[root@db01 ~]# mkdir -p /root/mysqlupgrade/mysql55
[root@db01 ~]# mysql -e 'show databases;' > /root/mysqlupgrade/mysql55/mysql-5.5.databases
[root@db01 ~]# mysql --silent --skip-column-names --execute "select concat('\'',User,'\'@\'',Host,'\'') as User from mysql.user;" | sort | while read u; do echo "-- $u"; mysql --silent --skip-column-names --execute "show grants for $u" | sed 's/$/;/'; done > /root/mysqlupgrade/mysql55/mysql-5.5.grants
[root@db01 ~]# cp /etc/my.cnf /root/mysqlupgrade/mysql55/mysql-5.5.cnf.orig
[root@db01 ~]# yum install xz
[root@db01 ~]# mysqldump --routines --all-databases | xz > /root/mysqlupgrade/mysql55/mysql-5.5.dump.sql.xz
Now stop MySQL and upgrade it to MySQL 5.6:
[root@db01 ~]# service mysqld stop [root@db01 ~]# yum --disableexcludes=all shell remove mysql55 mysql55-server mysql55-libs install mysql56u mysql56u-server mysql56u-libs mysqlclient16 ts solve ts run exit
Generate a version of the my.cnf that is valid for MySQL 5.6. Some options get deprecated or changed between versions, so it is important that this is not skipped. To help with this, we’ll be using dbsake as shown below:
[root@db01 ~]# wget -O /root/mysqlupgrade/dbsake http://get.dbsake.net; chmod u+x /root/mysqlupgrade/dbsake [root@db01 ~]# /root/mysqlupgrade/dbsake upgrade-mycnf --config /root/mysqlupgrade/mysql55/mysql-5.5.cnf.orig --target 5.6 > /root/mysqlupgrade/mysql-5.6.cnf [root@db01 ~]# mv -f /root/mysqlupgrade/mysql-5.6.cnf /etc/my.cnf
Start MySQL without the grant tables to verify it is running MySQL 5.6 and all the databases loaded:
[root@db01 ~]# sed -i 's/\[mysqld\]/[mysqld]\nskip-grant-tables\nskip-networking/' /etc/my.cnf [root@db01 ~]# service mysqld start [root@db01 ~]# mysql -sse "select @@version" [root@db01 ~]# mysql -e "show databases;" > /root/mysqlupgrade/mysql55/mysql-5.6.databases [root@db01 ~]# diff -U0 /root/mysqlupgrade/mysql55/mysql-5.5.databases /root/mysqlupgrade/mysql55/mysql-5.6.databases
Now update the database schema. If mysql_upgrade says that a table needs to repaired, repair it with mysqlcheck -A before moving on:
[root@db01 ~]# mysql_upgrade [root@db01 ~]# sed -i '/\(skip-grant-tables\|skip-networking\)/d' /etc/my.cnf [root@db01 ~]# service mysqld restart
Finally, confirm MySQL is running version 5.6:
[root@db01 ~]# mysqladmin version
Upgrade from MySQL 5.6 to MySQL 5.7
Create a restore point. This will be backing up the databases, the my.cnf, as well as a user and database listing. This can be done by:
[root@db01 ~]# mysql -S mysqldump
[root@db01 ~]# mkdir -p /root/mysqlupgrade/mysql56
[root@db01 ~]# mysql -e 'show databases;' > /root/mysqlupgrade/mysql56/mysql-5.6.databases
[root@db01 ~]# mysql --silent --skip-column-names --execute "select concat('\'',User,'\'@\'',Host,'\'') as User from mysql.user;" | sort | while read u; do echo "-- $u"; mysql --silent --skip-column-names --execute "show grants for $u" | sed 's/$/;/'; done > /root/mysqlupgrade/mysql56/mysql-5.6.grants
[root@db01 ~]# cp /etc/my.cnf /root/mysqlupgrade/mysql56/mysql-5.6.cnf.orig
[root@db01 ~]# yum install xz
[root@db01 ~]# mysqldump --routines --all-databases | xz > /root/mysqlupgrade/mysql56/mysql-5.6.dump.sql.xz
Now stop MySQL and upgrade to MySQL 5.7:
[root@db01 ~]# service mysqld stop [root@db01 ~]# yum --disableexcludes=all shell remove mysql56u mysql56u-server mysql56u-libs mysql56u-common install mysql57u mysql57u-server mysql57u-libs mysqlclient16 ts solve ts run exit
Generate a version of the my.cnf that is valid for MySQL 5.7. Some options get deprecated or changed between versions, so it is important that this is not skipped. To help with this, we’ll be using dbsake as shown below:
[root@db01 ~]# wget -O /root/mysqlupgrade/dbsake http://get.dbsake.net; chmod u+x /root/mysqlupgrade/dbsake [root@db01 ~]# /root/mysqlupgrade/dbsake upgrade-mycnf --config /root/mysqlupgrade/mysql56/mysql-5.6.cnf.orig --target 5.7 > /root/mysqlupgrade/mysql-5.7.cnf [root@db01 ~]# mv -f /root/mysqlupgrade/mysql-5.7.cnf /etc/my.cnf
Start MySQL without the grant tables to verify it is running MySQL 5.6 and all the databases loaded:
[root@db01 ~]# sed -i 's/\[mysqld\]/[mysqld]\nskip-grant-tables\nskip-networking/' /etc/my.cnf [root@db01 ~]# service mysqld start [root@db01 ~]# mysql -sse "select @@version" [root@db01 ~]# mysql -e "show databases;" > /root/mysqlupgrade/mysql56/mysql-5.7.databases [root@db01 ~]# diff -U0 /root/mysqlupgrade/mysql56/mysql-5.6.databases /root/mysqlupgrade/mysql56/mysql-5.7.databases
If MySQL fails to start, check the logs as it may be due to MySQL looking for a /var/lib/mysqltmp directory. You can verify and correct it by:
[root@db01 ~]# cat /var/log/mysqld.log |grep ERROR [ERROR] InnoDB: Unable to create temporary file; errno: 2 [root@db01 ~]# cat /etc/my.cnf |grep tmpdir tmpdir = /var/lib/mysqltmp [root@db01 ~]# mkdir /var/lib/mysqltmp [root@db01 ~]# chown mysql:mysql /var/lib/mysqltmp [root@db01 ~]# service mysqld start
Now update the database schema. If mysql_upgrade says that a table needs to repaired, repair it with mysqlcheck -A before moving on:
[root@db01 ~]# mysql_upgrade [root@db01 ~]# sed -i '/\(skip-grant-tables\|skip-networking\)/d' /etc/my.cnf [root@db01 ~]# service mysqld restart
Finally, confirm MySQL is running version 5.7:
[root@db01 ~]# mysqladmin version
Rollback plan
What happens if you need to roll back? If you followed the instructions in this article to create the backups, restoration is simple. Just keep in mind that you also need to restore the databases themselves from the original backups. So anything that changed in the database since the upgrade will be lost. If this is not acceptable, do not use these rollback instructions!
It is going to be assumed that you are going to roll all the way back from MySQL 5.7 to MySQL 5.1. Simply adjust the instructions below accordingly if you are going to roll back to a different version.
Stop MySQL and rollback to MySQL 5.1 by:
[root@db01 ~]# service mysqld stop [root@db01 ~]# yum --disableexcludes=all shell remove mysql* install mysql mysql-server mysql-libs mysqlclient16 ts solve ts run exit
Restore the original /etc/my.cnf by:
[root@db01 ~]# cp /root/mysqlupgrade/mysql51/mysql-5.1.cnf.orig /etc/my.cnf
Startup MySQL and restore the 5.1-formatted databases:
[root@db01 ~]# mv /var/lib/mysql /var/lib/mysql.bak [root@db01 ~]# mkdir /var/lib/mysql [root@db01 ~]# chown mysql:mysql /var/lib/mysql [root@db01 ~]# service mysqld start [root@db01 ~]# mysql_secure_installation [root@db01 ~]# unxz -c /root/mysqlupgrade/mysql51/mysql-5.1.dump.sql.xz | mysql [root@db01 ~]# service mysqld restart [root@db01 ~]# mysqladmin version