Upgrading MySQL on Ubuntu

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

Upgrading MySQL in WHM 

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.

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-F]{40}

#39; ORDER BY User, Host;

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

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

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;

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 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):

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

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.

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.