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.
No matter how easy the task is, backup is always a must for upgrade!
Here is my shell script to backup all databases into single file for each database.
# MySQL Super Admin User
# MySQL Super Admin Password
# Backup Directory - NO TAILING SLASH!
# Before running the script, ensure that the directory exists
echo "Starting MySQL Backup";
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
echo "Finished MySQL Backup";
find /mnt/backup -mtime +10 -type f -delete
Steps are very similar to the reference link from Plesk.
- Make sure you do the backup as mentioned above
- Backup the mysql configuration file
# sudo cp /etc/mysql/my.cnf /etc/mysql/my.cnf.5.5
- 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
- Download the APT Repository by using wget
# sduo wget http://dev.mysql.com/get/mysql-apt-config_0.7.3-1_all.deb
- 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”
- Update package information
# sudo apt-get update
- 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
- 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
- Check current mysql version (should display 5.7)
# sudo mysql --version
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.