Applicable to:
- Plesk for Linux
Question
How to upgrade MySQL 5.5 to 5.6/5.7 or MariaDB 5.5 to 10.X on Linux?
Answer
This article provides instructions for upgrading MySQL 5.5 to 5.6/5.7 and MariaDB 5.5 to 10.0/10.1/10.2/10.3/10.4/10.5/10.6 on Linux.
Note: Upgrade can be performed in a command-line interface with the instructions below at your own risk. This task is supposed to be performed by a system administrator.
If you are not familiar with Linux system administering and would like our professionals to handle this task on your behalf, contact Professional Services team at https://www.plesk.com/professional-services/.
To find out your Linux distribution, run:
# plesk version | grep "OS version"
Or:
# cat /etc/*-release
Warning: MariaDB 10.4 and later versions are supported starting from Plesk Obsidian 18.0.30. Previous versions (Onyx and older) do not support MariaDB starting from 10.3 and there are no plans to implement it currently.
Warning:
Direct upgrade from MySQL 5.1 to MySQL 5.6/5.7 will break tables structure. The instructions for upgrading MySQL from 5.1 to 5.5 on Linux are available here: How to upgrade MySQL from 5.1 to 5.5 on Linux
Warning:
Direct update from MariaDB 5.5 or 5.7 to MariaDB 10.6 or later versions is not possible.
You should first update MariaDB 5.5. or 5.7 to any MariaDB from 10.3 to 10.5, and then perform an update to MariaDB 10.6 or later
It is strongly recommended to create a server snapshot (full backup) before upgrading. Perform MySQL upgrade at your own risk.
Before starting a MySQL upgrade, stop the WatchDog service if it is used on a server. Check its status with the service watchdog status
command.
Note: MySQL will not be upgraded on CentOS/RHEL-based operating systems if it was installed from the Webtatic Yum repository:
# rpm -qa | grep -i mysql
mysql55w-server-5.5.57-1.w6.x86_64
To start an upgrade of MySQL/MariaDB, connect to a server via SSH as root/superuser and follow the instructions for your operating system:
By default, CentOS 8 is shipped with MariaDB 10.3.
-
For security reasons, create a database dump of all databases with the following command:
# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqldump -u admin --verbose --all-databases --routines --triggers > /tmp/all-databases.sql
-
Stop MariaDB:
# service mariadb stop
-
Remove additional package conflicting with 10.4 version:
# yum remove mariadb-gssapi-server
-
For security reasons, copy the database directory in a separate folder:
# cp -v -a /var/lib/mysql/ /var/lib/mysql_backup
-
Configure MariaDB repository: open the Setting MariaDB repositories page, select your OS distro, release, and desired MariaDB version. Once done, the configuration that should be added to the
/etc/yum.repos.d/MariaDB.repo
file will appear.Warning: MariaDB 10.4 and later versions are supported since 18.0.30, make sure the latest Plesk version is installed.
Here is an example for MariaDB 10.4:
5.1. Open/create the MariaDB.repo file in any text editor. In this example, we are using the vi editor:
# vi /etc/yum.repos.d/MariaDB.repo
5.2. Add the content below to the file:
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.4/centos8-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
priority=1
module_hotfixes=1The configuration item
module_hotfixes=1
is a workaround for what MariaDB told is a dnf bug. See MDEV-20673 for more details5.3. Save the changes and close the file.
-
Reset all the cache:
# yum clean all
-
Start an upgrade of MariaDB to 10.4:
# yum install MariaDB-server galera-4 MariaDB-client MariaDB-shared MariaDB-backup MariaDB-common
- In case upgrading to MariaDB 10.6 or newer it is needed to adjust the
my.cnf
configuration file to match the new requirements:
# sed -i 's/bind-address = ::ffff:127.0.0.1/bind-address = 127.0.0.1/g' /etc/my.cnf
-
Once the upgrade is finished, start MariaDB:
# systemctl restart mariadb
-
Upgrade MySQL databases:
# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql_upgrade -uadmin
-
Restart mariadb service:
# systemctl restart mariadb
-
Execute this command to update the package version inside Plesk:
# plesk sbin packagemng -sdf
-
Restore SeLinux context:
# restorecon -v /var/lib/mysql/*
By default, CentOS 7 is shipped with MariaDB 5.5. MariaDB 10.x version is a drop-in replacement for MySQL 5.5-5.7.
Automatic method
Note: The script below is applicable only for upgrading default MariaDB 5.5 to the MariaDB 10.5 version, for other cases apply the steps from the Manual Method section. Make sure Obsidian 18.0.30 or later is installed on the server.
-
Download the attached script and provide it with executable rights:
# wget https://raw.githubusercontent.com/plesk/kb-scripts/master/c7-mariadb-10-5-upgrade/c7-mariadb-10-5-upgrade.sh && chmod +x c7-mariadb-10-5-upgrade.sh
-
Execute the script via CLI:
# ./c7-mariadb-10-5-upgrade.sh
Manual Method
-
For security reasons, create a database dump of all databases with the following command:
# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqldump -u admin --verbose --all-databases --routines --triggers > /root/all-databases.sql
-
Stop MariaDB:
# service mariadb stop
-
Remove additional
mariadb-bench
package if installed:# rpm -e --nodeps mariadb-bench
-
For security reasons, copy the database directory in a separate folder:
# cp -v -a /var/lib/mysql/ /var/lib/mysql_backup
-
Check if the mariadb-server package is already installed:
# rpm -q --whatprovides mariadb-server
If it is installed and the command above gives output, remove using the following command:
# rpm -e --nodeps `rpm -q --whatprovides mariadb-server`
-
Configure MariaDB repository:
Warning: MariaDB 10.4 and later versions are supported since 18.0.30, make sure the latest Plesk version is installed.
Here is an example for MariaDB 10.5:
6.1. The following command adds the MariaDB 10.5 repository:
# curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version=10.5
-
Reset all the cache:
# yum clean all
-
Start an upgrade of MariaDB:
# yum install MariaDB-client MariaDB-server MariaDB-compat MariaDB-shared
- In case upgrading to MariaDB 10.6 or newer it is needed to adjust the
my.cnf
configuration file to match the new requirements:
# sed -i 's/bind-address = ::ffff:127.0.0.1/bind-address = 127.0.0.1/g' /etc/my.cnf
-
start MariaDB:
# systemctl restart mariadb
OR
# systemctl restart mysql
-
Upgrade MySQL databases:
# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql_upgrade -uadmin
-
Restart mysql service:
# systemctl restart mariadb
OR
# systemctl restart mysql
-
Execute this command to update the package version inside Plesk:
# plesk sbin packagemng -sdf
Note: After an upgrade, there may appear 'mysql' init script. It should be removed to avoid conflicts with MariaDB service. The same commands can be used to fix already existing conflict:
# systemctl stop mysql; killall mysqld # to stop the incorrect service if it is started
# rm -f /etc/init.d/mysql && systemctl daemon-reload # to remove the incorrect service script and reload systemctl configuration
# systemctl start mariadb # to start MariaDB if not started
# systemctl enable mariadb # to make sure that MariaDB will start after the server reboot automatically
-
Create a backup of all databases with the following command:
# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqldump -u admin --verbose --all-databases --routines --triggers > /root/all-databases.sql
-
Stop the MySQL service:
# service mysqld stop
-
Copy a databases directory in a separate folder like this (for backup purposes also):
# cp -v -a /var/lib/mysql/ /var/lib/mysql_backup
- In case bind-address directive is present in
/etc/my.cnf
make sure that it is as follows :
# bind-address = 127.0.0.1
-
Start the MySQL service:
# service mysqld start
-
Select MySQL/MariaDB version to install:
Note: The following command does not work if
governor-mysql
package is not installed. In such case, install it:
# yum install governor-mysql# /usr/share/lve/dbgovernor/mysqlgovernor.py --mysql-version=XXXXX
Note: Where 'XXXXX' has to be substituted with one of the following:
mariadb100
for MariaDB v10.0mariadb101
for MariaDB v10.1mariadb102
for MariaDB v10.2mariadb103
for MariaDB v10.3mariadb104
for MariaDB v10.4mariadb105
for MariaDB v10.5mariadb106
for MariaDB v10.6mysql55
for MySQL v5.5mysql56
for MySQL v5.6mysql57
for MySQL v5.7 -
Install selected previously MariaDB engine:
# /usr/share/lve/dbgovernor/mysqlgovernor.py --install
Warning: CloudLinux does not recommend to downgrade from MySQL v5.6, MariaDB 10.x
-
Restart MySQL:
# service mysql restart
-
Upgrade MySQL databases:
# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql_upgrade -uadmin
-
Restart MySQL:
# service mysql restart
-
Execute the following commands to update package version inside Plesk:
# plesk bin service_node --update local
# plesk sbin packagemng -sdf
Debian 9 is basically shipped with MariaDB. MariaDB 10.x version is a drop-in replacement for MySQL 5.5/5.6/5.7
- Backup all existing databases using the KB article How to backup/restore a Plesk database dump and follow instructions on MariaDB site to update it:
Example for MariaDB 10.2
-
Install MariaDB:
1.1. Stop the MariaDB service:
# service mariadb stop
# curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version=mariadb-10.2
# apt install mariadb-server-10.2If the installation fails with unmet dependencies:
CONFIG_TEXT: dpkg: mariadb-server-10.1: dependency problems, but removing anyway as you requested:
plesk-mysql-server depends on virtual-mysql-server | mysql-server; however:
Package virtual-mysql-server is not installed.
Package mariadb-server-10.1 which provides virtual-mysql-server is to be removed.
Package mariadb-server-10.2 which provides virtual-mysql-server is not installed.
Package mysql-server is not installed.
<...>
The following packages have unmet dependencies:
mariadb-server-10.2 : Depends: mariadb-client-10.2 (>= 10.2.19+maria~stretch) but it is not going to be installed
Depends: mariadb-server-core-10.2 (>= 10.2.19+maria~stretch) but it is not going to be installedRun the command below to correct them:
# apt --fix-broken install
-
Check MySQL version:
# mysql -V
mysql Ver 15.1 Distrib 10.2.19-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 -
Start the MariaDB service:
# service mariadb start
Note: It may be already started
-
Upgrade MySQL databases:
# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql_upgrade -uadmin
-
Restart mysql service:
# service mariadb restart
-
Execute the following commands to update package version inside Plesk:
# plesk bin service_node --update local
# plesk sbin packagemng -sdf
By default, Debian 10 is shipped with MariaDB 10.3.
-
For security reasons, create a database dump of all databases with the following command:
# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqldump -u admin --verbose --all-databases --routines --triggers > /root/all-databases.sql
-
Stop MariaDB:
# service mariadb stop
Here is an example for MariaDB 10.4:
Warning: MariaDB 10.4 and later versions are supported since 18.0.30, make sure the latest Plesk version is installed.
-
Install repositories:
# apt-get install software-properties-common dirmngr apt-transport-https
# apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
# curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version=10.5
-
Create a backup of all MySQL data:
# cp -v -a /var/lib/mysql/ /var/lib/mysql_backup
-
Start an upgrade with the following command. Leave all fields empty when asked for a 'root' user password:
# apt-get update
# apt-get install mariadb-server
If the installation fails with unmet dependencies:
CONFIG_TEXT: plesk-mysql-server depends on virtual-mysql-server | mysql-server; however:
Package virtual-mysql-server is not installed.
Package mariadb-server-10.3 which provides virtual-mysql-server is to be removed.
Package mysql-server is not installed.
[...]
dpkg: error processing archive /var/cache/apt/archives/mariadb-server-10.4_1%3a10.4.14+maria~buster_amd64.deb (--unpack):
trying to overwrite '/usr/bin/my_print_defaults', which is also in package mariadb-server-core-10.3 1:10.3.22-0+deb10u1
dpkg-deb: error: paste subprocess was killed by signal (Broken pipe)
Errors were encountered while processing:
/var/cache/apt/archives/mariadb-server-10.4_1%3a10.4.14+maria~buster_amd64.deb
E: Sub-process /usr/bin/dpkg returned an error code (1)OR
CONFIG_TEXT: The following packages have unmet dependencies:
mariadb-server : Depends: mariadb-server-10.4 (>= 1:10.4.14+maria~buster) but it is not going to be installed
plesk-mysql-server : PreDepends: virtual-mysql-server or
mysql-server but it is not installable
Depends: virtual-mysql-server or
mysql-server but it is not installableRun the command below to correct them:
# apt --fix-broken install
-
Start the MariaDB service:
# systemctl start mariadb.service
-
Upgrade MySQL databases:
# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql_upgrade -uadmin
-
Restart mysql service:
# systemctl restart mariadb
-
Execute the following commands to update package version inside Plesk:
# plesk bin service_node --update local
# plesk sbin packagemng -sdf
Ubuntu 18.04 is shipped with MariaDB 10.1. MariaDB 10.x version is a drop-in replacement for MySQL 5.5-5.7. In order to upgrade MariaDB 10.1 to MariaDB 10.x, follow these steps:
-
Create a backup of all databases with the following command:
# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqldump -u admin --verbose --all-databases --routines --triggers > /tmp/all-databases.sql
-
Stop the MariaDB service:
# service mariadb stop
Here is an example for MariaDB 10.2:
Warning: MariaDB 10.4 and later versions are supported since 18.0.30, make sure the latest Plesk version is installed.
-
Install repositories:
# apt-get install software-properties-common
# apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
# curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version=10.2
-
Create a backup of all MySQL data:
# cp -v -a /var/lib/mysql/ /var/lib/mysql_backup
-
Start an upgrade with the following command. Leave all fields empty when asked for a root user password:
# apt-get update
# apt-get install mariadb-server
-
Start the MariaDB service:
# systemctl start mariadb
-
Upgrade MySQL databases:
# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql_upgrade -uadmin
-
Restart mysql service:
# systemctl restart mariadb
-
Execute the following commands to update package version inside Plesk:
# plesk bin service_node --update local
# plesk sbin packagemng -sdf
By default, Ubuntu 20.04 is shipped with MariaDB 10.3.
-
For security reasons, create a database dump of all databases with the following command:
# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqldump -u admin --verbose --all-databases --routines --triggers > /tmp/all-databases.sql
-
Stop MariaDB:
# service mariadb stop
Here is an example for MariaDB 10.4:
Warning: MariaDB 10.4 and later versions are supported since 18.0.30, make sure the latest Plesk version is installed.
-
Install repositories:
# apt-get install software-properties-common
# apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
# curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version=10.4
-
Create a backup of all MySQL data:
# cp -v -a /var/lib/mysql/ /var/lib/mysql_backup
-
Start an upgrade with the following command. Leave all fields empty when asked for a 'root' user password:
# apt-get update
# apt-get install mariadb-server
- Adjust the
my.cnf
configuration file to match the new requirements:
# sed -i 's/bind-address = ::ffff:127.0.0.1/bind-address = 127.0.0.1/g' /etc/mysql/my.cnf
-
Start the MariaDB service:
# service mariadb start
-
Upgrade MySQL databases:
# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql_upgrade -uadmin
-
Restart mysql service:
# service mariadb restart
-
Execute the following commands to update package version inside Plesk:
# plesk bin service_node --update local
# plesk sbin packagemng -sdf
More information available in MySQL documentation at http://dev.mysql.com/.
Comments
254 comments
maartenv thanks for notifying us, article will be fixed
The upgrade script link is broken for CentOS 7
[root@srvr ~]# wget https://plesk.zendesk.com/hc/article_attachments/360022419980/mariadb-10.5-upgrade.sh && chmod +x mariadb-10.5-upgrade.sh
--2022-03-08 21:18:39-- https://plesk.zendesk.com/hc/article_attachments/360022419980/mariadb-10.5-upgrade.sh
Resolving plesk.zendesk.com (plesk.zendesk.com)... 104.16.51.111, 104.16.53.111
Connecting to plesk.zendesk.com (plesk.zendesk.com)|104.16.51.111|:443... connected.
HTTP request sent, awaiting response... 404 Not Found
2022-03-08 21:18:40 ERROR 404: Not Found.
Can someone please provide the updated link?
Hello Javier Cota,
The link was updated. Thank you for bringing it to our attention!
I would recommend to add a:
to the script to prevent problems with old yum cache.
Hello Maximilian Wilke,
Thank you! Both automatic and manual solutions for Centos 7 were updated accordingly.
Can you please update the article and explain how to add MariaDB to the WatchDog service? What are the commands to start MariaDB again using WatchDog?
Regarding Centos 7 instructions are uncorrect as it's not possibile to install automatically the upgrade as Superuser while it's needed to be root to execute the script.
Hello giuseppe amodio,
Thank you for your comment. I've re-tested the script on a test server. It is possible to execute it as Superuser using the 'sudo ./c7-mariadb-10.5-upgrade.sh' command.
So the following statement from the article is correct:
It should be noted that in /etc/yum.conf there is an exclusion by default for MariaDB.
`exclude=MariaDB*`
You should comment this out so the yum install will work and then add the exclusion again after.
Hello Daniel West,
During the Plesk installation, no excludes are added to the `/etc/yum.conf` configuration file from the Plesk side by default. The '/etc/yum.conf ' file may be modified by the server provider/hosting provider. Still, thank you for noticing. This information can be helpful.
If I remember correctly, the "exclude=MariaDB" resulted from a situation where an automatic update updated MariaDB to a version that failed with certain database attributes and was shortly revoked after publication. At that time the advice was to exclude MariaDB from automatic updates (from Yum respectively). So indeed, this is an "extra" user-driven configuration that is not the MariaDB or Yum default.
Hello,
What do I need to change to upgrade to 10.6 on CentOs 7?
Thanks in advance.
Tried to upgrade using the Automatic method but MariaDB failed to start after the upgrade. Then followed the following article to fix the corruption.
https://support.plesk.com/hc/en-us/articles/213939865-How-to-fix-InnoDB-corruption-cases-for-the-MySQL-databases-on-Plesk-for-Linux-
Hello Carlos Lopez,
To update to MariaDB 10.6 on Centos 7 you will need to apply the Manual Solution for Centos 7. In step 6 it will be required to open the Setting MariaDB repositories page, select your OS distro, release, and desired MariaDB version, and place the provided output to the `/etc/yum.repos.d/MariaDB.repo` file as per steps 6.1 and 6.2.
Hello all,
I need to upgrade only MariaDB. Is this possible or have I to upgrade MySQL too?
THX
tony
I have upgraded from version 5.1 to 5.5 and it went very well.
Now I am trying to update from 5.5 to 5.7, when I finish all the steps this is the message I get.
This installation of MySQL is already upgraded to 5.5.55, use --force if you still need to run mysql_upgrade
I've got:
OS: CentOS 6.10 (Final)
Product: Plesk Obsidian 18.0.31
someone who can help me
Hello antonio dolce,
This article describes several scenarios for different OSes and MySQL/MariaDB versions. You may use only the one that is suitable in your case and omit the others.
Thank you Plesk team,
We followed all the instruction from section - Plesk on Ubuntu 18.04, for upgrading mariadb to 10.3.
and we succeeded in one shot without a single error,
just in one place we stuck, that we wanted to upgrade to mariadb 10.4, but when I added the repository, it said that current i386 structure don't support 10.4 mariadb, but later I added repository for mariadb 10.3,, but still it was showing i386 error.
later I moved to "/etc/apt/sources.list" and commented the mariadb10.4 line and continued the previous process of installation, and it installed the latest mariadb10.3 smoothly
Regards Ganesh
Can you please update the list of MariaDB versions? The current list contains versions that are no longer supported by the vendor (end-of-life):
https://endoflife.date/mariadb
Hello maartenv,
The article contains the list of outdated MariaDB versions for better searching, so the servers with EOLed MariaDB versions can also be upgraded. However, thank you for providing the link. It can be helpful for others.
I am on Debian 10.12 with Plesk 18.0.45 and recently updated MariaDB to 10.5 as per this guide.
All went well.
Only thing that I get recently an error notification from the System updates tool.
It states that the repository has no longer a release file...
Does this guide need an update accordingly or is it just a temporary repository fluke?
Edit/Additon: In general wouldnt it be preferable to use links to official repositories?
Form here: https://mariadb.org/download/?t=repo-config&d=Debian+10+"buster"&v=10.5
Hello MSZ,
The error may appear when non-standard repositories are used. The solution from the following article will be helpful in this case: https://support.plesk.com/hc/en-us/articles/360002961854.
It is always preferable to use official repositories.
Anastasia Zyrianova thanks for your response and glad that we agree.
I wrote my comment / question because the example in your guide in this specific article uses a non-official repository:
Therefore my suggestion to change this guide/article here and also use official repos going forward.
Hello MSZ,
The article was updated.
Update Guide for ubuntu 22.04 and latest supported Mariadb version
Hello Robert Krölls,
The latest supported version of MariaDB, for now, is MariaDB 10.6: https://docs.plesk.com/release-notes/obsidian/software-requirements/#s4. Ubuntu 22.04 comes with MariaDB 10.6 so no upgrade path is currently available for this OS.
Upgrade from MariaDB 5.5 to 10.5 using the Automatic method is working on my end.
Currently installed:
- Plesk Obsidian Version 18.0.46
- CentOS Linux 7.9.2009 (Core)
Has anyone experience trouble migrating from Ubuntu to Debian.
experimented issues with migration MySQL db to MariaDB ? some
website do not run.
Please add:
(https://support.plesk.com/hc/en-us/articles/360013751840)
mariadb106
for MariaDB v10.6 (https://changelog.cloudlinux.com/cl-MariaDB106)There's a step missing when upgrading from MariaDB:10.5 to MariaDB:10.6 on CentOS 7
After Step 8, the service will fail to start.
THE CAUSE: MariaDB 10.6 does not allow to bind both
::1
and127.0.0.1
via the settingbind-address = ::ffff:127.0.0.1
in the file/etc/my.cnf
.THE FIX: https://support.plesk.com/hc/en-us/articles/5180170766738-Unable-to-start-MariaDB-10-6-on-a-Plesk-server-Bind-on-TCP-IP-port-Got-error-22-Invalid-argument
After editing your my.cnf file and restarting mariadb service, proceed to Step 10 and finish installation.
Please sign in to leave a comment.