Applicable to:
- Plesk Obsidian for Linux
Question
Is it possible to replace MySQL with MariaDB and vice versa?
Answer
It is highly dependent on the MySQL/MariaDB versions and OS type. Each such scenario should be reviewed separately before making any changes on the server.
MariaDB is considered to be a drop-in replacement for the corresponding MySQL version (i.e. MariaDB 10.2 and MySQL 5.7).
There is no support for in-place replacement to MySQL if the server already runs MariaDB. It is always recommended to use the OS-provided packages whenever possible.
Please check the following before making any decision:
- In almost any case when it is required to have different database server types for customers' databases, the recommended solution is to use a Docker container to host separate MySQL or MariaDB instance in Plesk and add it as an external SQL database server using IP address 127.0.0.1 and the external Docker port. Alternatively, the required MySQL/MariaDB running on a remote server can be added as an external database.
- Please review feature differences between MariaDB and MySQL to make sure the switch won't affect applications using database server. If application installation requirements mention that both MySQL/MariaDB are supported like WordPress for example, switch should not make any harm.
- The procedure should be performed by a system administrator. Or you can refer to Plesk Professional Services services for in-place replacement of Mysql to MariaDB, if supported, so they can perform this task for you.
For frequently encountered scenarios when the Ubuntu 18 (with MySQL installed) needs to be dist-upgraded to Ubuntu 20 (shipped with MariaDB) the following steps can be applied to make an in-place replacement of MySQL to MariaDB on Ubuntu 18 before the dist-upgrade:
Note: create a server-wide snapshot before applying the next steps.
Note: in-place replacement from MySQL to MariaDB should be done within same base version (for example MySQL 5.5 -> MariaDB 5.5, MySQL 5.6 -> MariaDB 10.0, and MySQL 5.7 -> MariaDB 10.2), according to the following documentation page.
1. Check the currently installed MySQL version:
# mysql -V
2. 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
3. Stop the MySQL service:
# systemctl stop mysql
4. Configure the server for required repositories:
# apt-get install software-properties-common
# apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
# touch /etc/apt/sources.list.d/mariadb.list
4.1. The earlier created /etc/apt/sources.list.d/mariadb.list should have the following content (if the MySQL 5.7 was installed the following versions of MariaDB will function as limited drop-in replacements: 10.2, 10.3, 10.4 change the version of MariaDB in command below if required):
# cat /etc/apt/sources.list.d/mariadb.list
deb https://mirror.rackspace.com/mariadb/repo/10.4/debian stretch main
5. Create a backup of all MySQL data:
# cp -v -a /var/lib/mysql/ /var/lib/mysql_backup
6. Start an upgrade with the following commands:
# apt-get update
# apt-get install mariadb-server
7. Start the MariaDB service:
# systemctl start mariadb
8. Upgrade MySQL databases:
# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql_upgrade -uadmin
9. Execute the following commands to update package version inside Plesk:
# plesk bin service_node --update local
# plesk sbin packagemng -sdf
Comments
1 comment
We did this, upgraded to the newest Version and we get now an Backup Error. Everything else works.
Unfortunately there is no documentation on how to update your command.
Unable to make database dump. Error: Failed to exec mysqldump: Exit code: 2: mysqldump: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb-dump' instead
mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'XXX_DB'': Cannot load from mysql.proc. The table is probably corrupted (1728)
Please sign in to leave a comment.