In a few months ago, I tried to Upgrade MySQL from 5.5 to 5.7 on CentOS. Although the upgrade process is successful, it is a little bit terrible experience because all data may be removed.

Recently, I have got another request to the same upgrade but in Debian. The process is definitely much easier. I take the reference from here.

https://kb.plesk.com/en/127962

No matter how easy the task is, backup is always a must for upgrade!

  • backup all databases in SQL files (use mysqldump or phpMyAdmin)
  • backup the whole database folder (default path: /var/lib/mysql/)

Here is my shell script to backup all databases into single file for each database.

#!/bin/bash
# MySQL Super Admin User
USER='root'
# MySQL Super Admin Password
PASSWORD='root_password'
# Backup Directory - NO TAILING SLASH!
# Before running the script, ensure that the directory exists
OUTPUT="/home/user/database_dir"
 
##### And 
TIMESTAMP=`date +%Y%m%d_%H%M%S`;
mkdir $OUTPUT/$TIMESTAMP;
cd $OUTPUT/$TIMESTAMP;
echo "Starting MySQL Backup";
echo `date`;
databases=`mysql --user=$USER --password=$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
for db in $databases; do
    if [[ "$db" != "information_schema" ]] && [[ "$db" != _* ]] ; then
        echo "Dumping database: $db"
        mysqldump --force --opt --user=$USER --password=$PASSWORD --databases $db > dbbackup-$TIMESTAMP-$db.sql
    fi
done
echo "Finished MySQL Backup";
echo `date`;

find /mnt/backup -mtime +10 -type f -delete

START UPGRADE MySQL NOW

Steps are very similar to the reference link from Plesk.

  1. Make sure you do the backup as mentioned above
  2. Backup the mysql configuration file
    # sudo cp /etc/mysql/my.cnf /etc/mysql/my.cnf.5.5
  3. Click the Download button in this page http://dev.mysql.com/downloads/repo/apt/
    In the next page, copy the link of the text “No thanks, just start my download.”
    You will get the link like http://dev.mysql.com/get/mysql-apt-config_0.7.3-1_all.deb
  4. Download the APT Repository by using wget
    # sduo wget http://dev.mysql.com/get/mysql-apt-config_0.7.3-1_all.deb
  5. Configure the installation package
    # sudo dpkg -i mysql-apt-config_0.7.3-1_all.deb

    You will be prompted to configure the installation package (see screenshot below). By default, MySQL 5.6 is selected. Press “Enter” to select the version 5.7. Other options can leave unchanged. Select the “ok” in the bottom and then press “Enter”

    Screen Shot 2016-07-19 at 1.09.39 PM
  6. Update package information
     # sudo apt-get update
  7. Install MySQL 5.7
     # sudo apt-get install mysql-server

    You will see the message like below

    The following extra packages will be installed:
      libmecab2 libnuma1 mysql-client mysql-common mysql-community-client mysql-community-server
    The following packages will be REMOVED:
      mysql-client-5.5 mysql-server-5.5 mysql-server-core-5.5
    The following NEW packages will be installed:
      libmecab2 libnuma1 mysql-community-client mysql-community-server
    The following packages will be upgraded:
      mysql-client mysql-common mysql-server
    3 upgraded, 4 newly installed, 3 to remove and 0 not upgraded.

    No worry! Go ahead! As you have already backed up the databases

  8. Upgrade MySQL
     # sudo mysql_upgrade -u root -p

    If you do not proceed this step, you may receive the following error messages when logging in phpMyAdmin.

    #1146 – Table ‘performance_schema.session_variables’ doesn’t exist
    Native table ‘performance_schema’.’session_variables’ has the wrong structure

  9. Check current mysql version (should display 5.7)
    # sudo mysql --version

DONE!

You should be able to log into mysql by using previous logins and all databases should be still here.

In my previous post for upgrading MySQL in CentOS, everything will be lost after upgrade. If you know that the above upgrade Debian can work like CentOS, appreciate if you can leave a comment in this post.