How to upgrade MySQL 5.5 to 5.6/5.7 or MariaDB 5.5 to 10.x on Linux?

By default, CentOS 8 is shipped with MariaDB 10.3.

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

2. Stop MariaDB:

   # service mariadb stop

3. Remove additional package conflicting with 10.4 version:

   # yum remove mariadb-gssapi-server

4. For security reasons, copy the database directory in a separate folder:

   # cp -v -a /var/lib/mysql/ /var/lib/mysql_backup

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

   The configuration item module_hotfixes=1 is a workaround for what MariaDB told is a dnf bug. See MDEV-20673 for more details

   5.3. Save the changes and close the file.

6. Reset all the cache:

   # yum clean all

7. Start an upgrade of MariaDB to 10.4:

   # yum install MariaDB-server galera-4 MariaDB-client MariaDB-shared MariaDB-backup MariaDB-common

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

9. Once the upgrade is finished, start MariaDB:

   # systemctl restart mariadb

10. Upgrade MySQL databases:

    # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql_upgrade -uadmin

11. Restart mariadb service:

    # systemctl restart mariadb

12. Execute this command to update the package version inside Plesk:

    # plesk sbin packagemng -sdf

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

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

2. Execute the script via CLI:

   # ./c7-mariadb-10-5-upgrade.sh

Manual Method

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

2. Stop MariaDB:

   # service mariadb stop

3. Remove additional mariadb-bench package if installed:

   # rpm -e --nodeps mariadb-bench

4. For security reasons, copy the database directory in a separate folder:

   # cp -v -a /var/lib/mysql/ /var/lib/mysql_backup

5. 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`

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

   Note: Make sure that other MariaDB repositories are not enabled in /etc/yum.repos.d/.

7. Reset all the cache:

   # yum clean all

8. Start an upgrade of MariaDB:

   # yum install MariaDB-client MariaDB-server MariaDB-compat MariaDB-shared

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

10. start MariaDB:

    # systemctl restart mariadb

    OR

    # systemctl restart mysql

11. Upgrade MySQL databases:

    # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql_upgrade -uadmin

12. Restart mysql service:

    # systemctl restart mariadb

    OR

    # systemctl restart mysql

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

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

2. Stop the MySQL service:

   # service mysqld stop

3. Copy a databases directory in a separate folder like this (for backup purposes also):

   # cp -v -a /var/lib/mysql/ /var/lib/mysql_backup

4. In case bind-address directive is present in /etc/my.cnf make sure that it is as follows :
   

   # bind-address = 127.0.0.1

5. Start the MySQL service:

   # service mysqld start

6. 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.0
   mariadb101 for MariaDB v10.1
   mariadb102 for MariaDB v10.2
   mariadb103 for MariaDB v10.3
   mariadb104 for MariaDB v10.4
   mariadb105 for MariaDB v10.5
   mariadb106 for MariaDB v10.6
   mysql55 for MySQL v5.5
   mysql56 for MySQL v5.6
   mysql57 for MySQL v5.7

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

8. Restart MySQL:

   # service mysql restart

9. Upgrade MySQL databases:

   # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql_upgrade -uadmin

10. Restart MySQL:

    # service mysql restart

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

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

• https://mariadb.com/kb/en/mariadb/installing-mariadb-deb-files/

Example for MariaDB 10.2

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

   If 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 installed

   Run the command below to correct them:

   # apt --fix-broken install

2. Check MySQL version:

   # mysql -V
   mysql Ver 15.1 Distrib 10.2.19-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

3. Start the MariaDB service:

   # service mariadb start

   Note: It may be already started

4. Upgrade MySQL databases:

   # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql_upgrade -uadmin

5. Restart mysql service:

   # service mariadb restart

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

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

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

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

    

4. Create a backup of all MySQL data:

   # cp -v -a /var/lib/mysql/ /var/lib/mysql_backup

5. 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 installable

   Run the command below to correct them:

   # apt --fix-broken install

6. Start the MariaDB service:

   # systemctl start mariadb.service

7. Upgrade MySQL databases:

   # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql_upgrade -uadmin

8. Restart mysql service:

   # systemctl restart mariadb

9. 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:

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

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

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

4. Create a backup of all MySQL data:

   # cp -v -a /var/lib/mysql/ /var/lib/mysql_backup

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

6. Start the MariaDB service:

   # systemctl start mariadb

7. Upgrade MySQL databases:

   # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql_upgrade -uadmin

8. Restart mysql service:

   # systemctl restart mariadb

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

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

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

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

4. Create a backup of all MySQL data:

   # cp -v -a /var/lib/mysql/ /var/lib/mysql_backup

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

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

7. Start the MariaDB service:

   # service mariadb start

8. Upgrade MySQL databases:

   # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql_upgrade -uadmin

9. Restart mysql service:

   # service mariadb restart

10. Execute the following commands to update package version inside Plesk:

    # plesk bin service_node --update local
    # plesk sbin packagemng -sdf