Upgrading MySQL on CentOS

This tutorial is for the purpose of upgrading MySQL on an unmanaged server with a CentOS operating system. This tutorial assumes that you are already logged in as root via Secure Shell. Make sure you complete the backup process before making any changes to your MySQL.

Warning:

Make sure you complete the backup process before making any changes to your MySQL. See our article Backing Up a MySQL Database for more information.

This tutorial is for the purpose of upgrading MySQL on an unmanaged server with a CentOS operating system. If you have Ubuntu or are upgrading via cPanel, please see the articles pertaining to them:

Upgrading MySQL on Ubuntu

Upgrading MySQL in WHM

Note:

This tutorial assumes that you are already logged in as root via Secure Shell. See our article Logging into Your Server via Secure Shell (SSH) for information if you are not already logged in.

Planning

Check for corruption

mysqlcheck -Asc

PHP

The version of PHP the server uses should be dynamically linked against libmysqlclient – so you shouldn’t have to worry about that.

Note:

Old password format is broken in MySQL 5.6, removed in 5.7+. Address this during prep.

Check for old passwords with:

mysql> SELECT Host, User, Password AS Hash FROM mysql.user WHERE Password REGEXP '^[0-9a-fA-F]{16}' ORDER BY User, Host;

Hunt the configs down for those users, and update the passwords.

Change Password

Find all users with old style password (no longer available in MySQL version 5.6)

mysql> SELECT Host, User, Password AS Hash FROM mysql.user WHERE Password REGEXP '^[0-9a-fA-F]{16}' ORDER BY User, Host;

Find all users with new style password (ready for transition to MySQL version 5.6)

mysql> SELECT Host, User, Password AS Hash FROM mysql.user WHERE Password REGEXP '^\*[0-9A-F]{40}

#39; ORDER BY User, Host;

Show Lengths of all regardless of style

Old style will be 16, 41 is the current hash length

mysql> SELECT user, Length(Password) FROM mysql.user;

Locate config files that may contain passwords for user-defined database users.

Run from ssh as root (not in the MySQL CLI):

mysql -Ns -e "SELECT User FROM mysql.user WHERE Password REGEXP '^[0-9a-fA-F]{16}';" |grep "_" |sort |uniq |while read dbuser; do acct=`echo $dbuser |cut -d'_' -f1`; config=`grep -Rl "$dbuser" /home/$acct/public_html`; echo $dbuser $config; done

Set Password in Old Format

On a server with the new password hashing format, you can set a password of the old format with either:

mysql> SET PASSWORD for '$USER'@'$HOST' = OLD_PASSWORD('$PASSWORD'); mysql> UPDATE mysql.user SET Password = OLD_PASSWORD('$PASSWORD') WHERE User = '$USER' AND Host = '$Host';
FLUSH PRIVILEGES;

Set Password in New Format

On a server with the old password hashing format, you can set a password of the new format with either:

mysql> SET SESSION old_passwords=FALSE;
SET PASSWORD for '$USER'@'$HOST' = PASSWORD('$PASSWORD'); mysql> SET SESSION old_passwords=FALSE;
UPDATE mysql.user SET Password = PASSWORD('$PASSWORD') WHERE User = '$USER' AND Host = '$HOST';
FLUSH PRIVILEGES;

Backups and Prep

Notice:

These steps should be done immediately before the time of the upgrade.

If you don’t already have a directory:

ts=$(date -u +%Y-%m-%d-%H:%M:%S%z) && mkdir -p /home/lwtemp/mysql_upgrade.$ts && pushd /home/lwtemp/mysql_upgrade.$ts

Dump all databases:

echo "SHOW DATABASES;"|mysql -Bs|grep -v -e 'information_schema' -e 'performance_schema'|while read i; do echo dumping $i; mysqldump --single-transaction $i|gzip -c > $i.sql.gz; done

Upgrading Binaries

If your new MySQL version is already in system repositories, and you want to install it from there, you can.

Repositories/instructions for other versions are below.

MySQL

Add the appropriate repository from below:

CentOS 7

yum install https://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpm

CentOS 6

yum install https://dev.mysql.com/get/mysql57-community-release-el6-7.noarch.rpm

CentOS 5

yum install https://dev.mysql.com/get/mysql57-community-release-el5-7.noarch.rpm

Finally, find your binaries in yum and install them.

MariaDB non-cPanel binaries

Go to the MariaDB Foundation Download page and pick your options to get your repository and installation instructions.

Percona

For switching to Percona Server, you need to first add the Percona repository (the exact URL is subject to change based on the release version):

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

Install the Percona shared libraries:

yum install Percona-Server-shared

Install Percona:

yum install Percona-Server-55

Upgrading Tables

Note:

If not successful, simply recreate the datadir and reimport. (next section)

Upgrading

If upgrading MySQL, run the following. Follow up on any errors.

mysql_upgrade

Recreate and Reimport

Warning:

You must move the old datadir out of the way and create a new datadir at /var/lib/mysql using the instructions below.

DATADIR=`mysql -e "show variables;"|grep datadir|awk {'print $2'}`

```
DATADIR=${DATADIR%/}
```
Verify you have the datadir:
```
echo $DATADIR
```

Move datadir

mv $DATADIR $DATADIR.bak

Recreate datadir

mkdir $DATADIR

Fix datadir Ownership

```
chown -R mysql:mysql /var/lib/mysql
```
Start MySQL

If MySQL 5.6, check the file tables:

Add a link to the file tables.

Reimport databases find:

-maxdepth 1 -type f -name "*.sql.gz"|grep -v 'mysql.sql.gz'|awk -F'[/.]' '{$NF=$(NF-1)=""; print}'|while read db; do echo Importing $db; mysql -Bse "CREATE DATABASE IF NOT EXISTS $db;"; gunzip $db.sql.gz|mysql $db; done

Either reimport mysql.sql.gz or recreate the grants. You must migrate this dump into the current database.

Cleanup

As a final step, test the sites – and have them continue to run for a while. After a week of nominal operation, remove the backup folder you created and any old datadirs.

Was this article helpful?

Thank you for your input.

Thank you for your feedback.