---
title: "Upgrading MySQL on Ubuntu"
source: "https://docs.nexcess.com/hosting/server-administration/database-management/mariadb-mysql/upgrading-mysql-on-ubuntu/"
description: "This tutorial is for the purpose of upgrading MySQL on an unmanaged server with an Ubuntu operating system. If you have CentOS or are upgrading via cPanel, plea…"
vertical: "Hosting"
date: "2024-03-04"
last_modified: "2026-07-02"
---

# Upgrading MySQL on Ubuntu

| ### **Warning:** |
|---|
| Make sure you complete the backup process before making any changes to your MySQL. |

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

[Upgrading MySQL on CentOS](https://docs.nexcess.com/hosting/server-administration/database-management/mariadb-mysql/upgrading-mysql-on-centos/)

[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.

### Old Passwords

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; 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; ``` |

## MySQL Changes

Insert mysql version changes, version to version

### MySQL 5.0 Changes

- basedir – REMOVED

### MySQL 5.1 Changes

- table\_cache -> table\_open\_cache
- log\_slow\_queries -> slow\_query\_log
- key\_buffer -> key\_buffer\_size

### MySQL 5.5 Changes

- safe\_show\_database REMOVED
- skip\_locking REMOVED
- skip\_symlink REMOVED
- Various replication options removed – must be set within MySQL 
    - master-host REMOVED
    - master-password REMOVED
    - master-port REMOVED
    - master-user REMOVED

### MySQL 5.6 Changes

- log\_slow\_queries REMOVED (enabled if log is set)
- performance\_schema=0 NEW is recommended

## Backups and Prep

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

If you don’t already have a folder:

```
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 repository below:

```
wget --silent https://dev.mysql.com/get/mysql-apt-config_0.6.0-1_all.deb dpkg -i mysql-apt-config*.deb
```

To switch major versions:

```
dpkg-reconfigure mysql-apt-config
```

To install:

```
apt-get install mysqld-server
```

## MariaDB binaries

Go to the [MariaDB Foundation page](https://downloads.mariadb.org/mariadb/repositories/#mirror=jmu) 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](https://www.percona.com/doc/percona-server/5.5/installation/apt_repo.html) (the exact URL is subject to change):

```
wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
```

Install the downloaded package with dpkg. To do that, run the following command:

```
dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
```

Once you install the package, the Percona repositories should be added. You can check the repository setup in the **/etc/apt/sources.list.d/percona-release.list** file.

Remember to update the local cache:

```
apt-get update
```

After that, you can install the server package:

```
apt-get install percona-server-server-5.5
```

## Upgrading Tables

### Upgrading

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

If not successful, simply recreate the datadir and reimport. (next section) `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. |

### Find datadir

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 five tables.

Add a link to the file tables.

1. 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.