---
title: "Upgrading MySQL on CentOS"
source: "https://docs.nexcess.com/hosting/server-administration/database-management/mariadb-mysql/upgrading-mysql-on-centos/"
description: "Safely upgrade MySQL on an unmanaged CentOS server. Learn how to handle backups, fix old password hashes, update binaries, and import tables via SSH."
vertical: "Hosting"
date: "2024-03-04"
last_modified: "2026-07-27"
---

# Upgrading MySQL on CentOS

| ### **Warning:** |
|---|
| Make sure you complete the backup process before making any changes to your MySQL. See our article [Backing Up a MySQL Database](https://docs.nexcess.com/hosting/server-administration/database-management/mariadb-mysql/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](https://docs.nexcess.com/hosting/server-administration/database-management/mariadb-mysql/upgrading-mysql-on-ubuntu/)

[Upgrading MySQL in WHM](https://docs.nexcess.com/hosting/control-panel/whm/database-management/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)](https://docs.nexcess.com/hosting/server-administration/linux/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](https://downloads.mariadb.org/mariadb/repositories/) 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. |

1. ```
    DATADIR=`mysql -e "show variables;"|grep datadir|awk {'print $2'}`
    ```
2. ```
    DATADIR=${DATADIR%/}
    ```
3. Verify you have the datadir:  
    ```
    echo $DATADIR
    ```

### Move datadir

```
mv $DATADIR $DATADIR.bak
```

### Recreate datadir

```
mkdir $DATADIR
```

### Fix datadir Ownership

1. ```
    chown -R mysql:mysql /var/lib/mysql
    ```
2. Start MySQL

| ### **If MySQL 5.6, check the file tables:** |
|---|
| 1. Add a link to the file tables. 2. 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     ``` 3. 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.