Month: January 2018

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

Converting MyISAM tables over to InnoDB comes with several benefits such as row level locking, being able to commit and roll back transactions and using tractional logs for autorecovery.

While converting MyISAM over to InnoDB is usually straight forward, you must use caution:

- Always create a backup of the database before making any changes!
- Confirm you are running MySQL 5.6.4 or above as that was when full-text indexing was introduced to InnoDB.

You can check to see what tables are using MyISAM in your database by running the following:

[root@db01 ~]# mysql
mysql> SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,TABLE_ROWS FROM information_schema.TABLES WHERE  ENGINE = 'MyISAM' and TABLE_ROWS
+--------------+-----------------------+--------+------------+
| TABLE_SCHEMA | TABLE_NAME            | ENGINE | TABLE_ROWS |
+--------------+-----------------------+--------+------------+
| mysql        | db                    | MyISAM |          3 |
| mysql        | help_category         | MyISAM |         39 |
| mysql        | help_keyword          | MyISAM |        464 |
| mysql        | help_relation         | MyISAM |       1028 |
| mysql        | help_topic            | MyISAM |        508 |
| mysql        | proxies_priv          | MyISAM |          2 |
| mysql        | user                  | MyISAM |          5 |
| example01    | wp_commentmeta        | MyISAM |         15 |
| example01    | wp_comments           | MyISAM |         72 |
| example01    | wp_links              | MyISAM |         11 |
| example01    | wp_options            | MyISAM |        224 |
| example01    | wp_postmeta           | MyISAM |        260 |
| example01    | wp_posts              | MyISAM |        277 |
| example01    | wp_term_relationships | MyISAM |        178 |
| example01    | wp_term_taxonomy      | MyISAM |         62 |
| example01    | wp_terms              | MyISAM |         62 |
| example01    | wp_usermeta           | MyISAM |         22 |
| example01    | wp_users              | MyISAM |          1 |
+--------------+-----------------------+--------+------------+

Alternatively, if you wanted to see MyISAM tables that have more than 5,000 rows, run:

mysql> SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,TABLE_ROWS FROM information_schema.TABLES WHERE  ENGINE = 'myISAM' and TABLE_ROWS > 5000;
+--------------+---------------------+--------+------------+
| TABLE_SCHEMA | TABLE_NAME          | ENGINE | TABLE_ROWS |
+--------------+---------------------+--------+------------+
| example02    | employees           | MyISAM |      80257 |
| example02    | templates           | MyISAM |     155341 |
| example02    | zipcodes            | MyISAM |      33178 |
| example02    | providers           | MyISAM |      34833 |
| example02    | portal              | MyISAM |      25184 |
+--------------+---------------------+--------+------------+

Converting the tables is pretty simple, but keep in mind that this will lock the table while it is running. Make sure you backup your database BEFORE converting any tables! Things can go wrong at the worst times, so be sure to have a solid roll back plan before proceeding!

Convert the desired table from MyISAM to InnoDB by running:

[root@db01 ~]# mysql
mysql> use example02;
mysql> ALTER TABLE providers ENGINE=InnoDB;