How to Migrate MySQL to MariaDB in Ubuntu?

Migrating data from MySQL to MariaDB is a straightforward process, given the close compatibility between the two database systems. MariaDB is a popular choice for many organizations due to its enhanced features and improved performance over MySQL. Here’s a detailed guide on how to migrate your data from MySQL to MariaDB in four simple steps.

To switch from MySQL to MariaDB on Ubuntu, update your package list, install the MariaDB server, start the MariaDB service, and secure the installation. This process ensures a smooth transition while maintaining database integrity.

Steps to Migrate Data From MySQL to MariaDB

• Step 1: Create MySQL Database Backup
• Step 2: Uninstall MySQL Database Packages
• Step 3: Install MariaDB Database Packages
• Step 4: Load MySQL Backup File to MariaDB

Step 1: Create MySQL Database Backup

• Check for incompatibilities between MySQL and MariaDB.
• The existing Databases of MySQL need to be backed up to be transferred from MySQL to MariaDB.
• The command mysqldump can export all the existing databases in the form of files.
• Binary Logging is required for this command to work, and this can be enabled in the MySQL configurations by modifying my.cnf file and adding the following option under the [mysqld] section.

log-bin=mysql-bin

• Restart the MySQL by using the following command:

$ sudo service mysql restart

• After that enter the command to use mysqldump.

$ mysqldump --all-databases --user=root --password --master-data > backupdb.sql

• A backup of my.cnf can be created to be on the safer side, but this step is optional. To create the backup use the following command:

$ sudo cp /etc/mysql/my.cnf /opt/my.cnf.bak

Step 2: Uninstall MySQL Database Packages

• Stop the MySQL service by using the following command

$ sudo service mysql stop

or

$ sudo systemctl stop mysql 

or

$ sudo /etc/init.d/mysql stop

• Once the services have stopped, remove MySQL packages and configurations. You may leverage any of the following commands based on your operating system:
• On RPM-based systems, (e.g., CentOS, Fedora, or RHEL):

$ sudo yum remove mysql* mysql-server mysql-devel mysql-libs
$ sudo rm -rf /var/lib/mysql

• On Debian based systems (e.g., Debian, Ubuntu, or Mint):

$ sudo apt-get remove mysql-server mysql-client mysql-common
$ sudo apt-get autoremove
$ sudo apt-get autoclean
$ sudo deluser mysql
$ sudo rm -rf /var/lib/mysql

Step 3: Install MariaDB Database Packages

The latest versions of CentOS and Ubuntu have the MariaDB package pre-installed in repositories. For older versions, you can visit the MariaDB website and download it based on the operating system version.

For a better understanding of this example, we will install MariaDB on Ubuntu and CentOS.

Ubuntu 14.04

$ sudo apt-get install software-properties-common
$ sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
$ sudo add-apt-repository 'deb http://mirror.mephi.ru/mariadb/repo/5.5/ubuntu trusty main'
$ sudo apt-get update
$ sudo apt-get install mariadb-server

CentOS 7

Create a custom yum repository file for MariaDB as follows.

$ sudo vi /etc/yum.repos.d/MariaDB.repo

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

$ sudo yum install MariaDB-server MariaDB-client

After all necessary packages have been installed, you may be asked to type a new password for the root user account. After configuring the root password, don’t forget to recover my.cnf backup file.

$ sudo cp /opt/my.cnf /etc/mysql/

Now, start the MariaDB service as follows:

$ sudo service mariadb start

or

$ sudo systemctl start mariadb

or

$ sudo /etc/init.d/mariadb start

Step 4: Load MySQL Backup File to MariaDB

Now import the MySQL backup file into MariaDB using the following command:

$ mysql -u root -p < backupdb.sql

Enter the password and the import process for MySQL to MariaDB migration will start. When the import of files from MySQL to MariaDB is complete the cursor will return to the command prompt.

To verify the success of import from this MySQL to MariaDB converter process, run the following commands:

$ mysql -u root -p

MariaDB [(none)]> show databases;
MariaDB [(none)]> use test01;
MariaDB [test01]> select * from pet;

While mysqldump is a popular and straightforward tool for migrating data from MySQL to MariaDB, it does come with certain limitations and potential pitfalls:

1. Compatibility Issues: When migrating between different versions of MySQL and MariaDB, there might be compatibility issues. It’s crucial to ensure that the versions you are migrating between are compatible.
2. Schema Mismatches: Sometimes, the schema definitions in MySQL might not be fully compatible with MariaDB, leading to errors during the import process. This can be due to differences in data types, default values, or indexing methods.
3. Privileges and Permissions: MySQL and MariaDB handle user privileges and permissions differently. When migrating, the privileges set up in MySQL might not translate correctly to MariaDB, causing errors or requiring manual adjustments.
4. Performance Overhead: Using mysqldump for large databases can be time-consuming and resource-intensive. The process involves creating a complete logical backup, which can be slower than other methods, especially for large datasets.
5. Need for mysql_upgrade: After migrating data, you might need to run mysql_upgrade to ensure that the new MariaDB installation can properly handle the imported data. This is especially important when migrating between major versions.

Although MariaDB started as a fork of MySQL and maintains a high level of compatibility, there are some differences that can lead to issues during migration:

1. Storage Engines: While both databases support various storage engines, there might be differences in the features and optimizations of these engines.
2. Functions and Features: Some functions and features present in MySQL may not be available or may work differently in MariaDB, and vice versa.
3. Default Values and Behaviors: Default values and behaviors for certain configurations and operations might differ between MySQL and MariaDB.

It’s essential to check the compatibility of the versions before starting the migration process. Here is a table summarizing the compatibility between MySQL and MariaDB versions:

If the MySQL source database utilizes a SHA256 password hash, ensure to reset passwords for users with SHA256 hashing before establishing a connection to the MariaDB database. Below is a code snippet demonstrating how to reset a password hashed with SHA256.

SET old_passwords = 0;
UPDATE mysql.user SET plugin = 'mysql_native_password',
Password = PASSWORD('new_password')
WHERE (User, Host) = ('master_user_name', %);
FLUSH PRIVILEGES;

• Improved Performance: MariaDB often outperforms MySQL in benchmarks and real-world scenarios due to optimizations and enhancements in query execution and indexing algorithms. This can lead to faster query response times and better overall system performance, especially for complex queries and large datasets.
• Enhanced Features: MariaDB includes additional features not found in MySQL or improves upon existing MySQL features. These include support for more storage engines, advanced data types, and enhanced JSON support, among others. These features provide greater flexibility and capability when managing and querying data.
• Community Support: MariaDB has a vibrant and growing community that actively contributes to its development and support. This community-driven aspect can result in quicker bug fixes, security patches, and the availability of additional plugins and extensions to extend functionality.
• Open-Source Nature: Like MySQL, MariaDB is open-source, providing transparency in code and operations. This openness allows for greater customization, auditability, and control over your database environment, which is particularly important for organizations with specific security or compliance requirements.
• Compatibility and Forking: MariaDB initially forked from MySQL, ensuring a high degree of compatibility with MySQL databases. This makes migration relatively straightforward, with minimal changes needed in most cases. MariaDB continues to maintain compatibility while also innovating and diverging in areas where improvements are needed.
• Scalability and High Availability: MariaDB supports advanced replication and clustering options, making it easier to scale horizontally and ensure high availability. Features like Galera Cluster for synchronous replication and MariaDB MaxScale for database proxying enhance scalability and fault tolerance capabilities.

While MySQL remains a robust and widely-used database management system, MariaDB offers compelling advantages in terms of performance, feature set, community support, and scalability. Organizations facing challenges with MySQL’s performance limitations or seeking to leverage newer database technologies should consider migrating to MariaDB. The process is facilitated by their shared heritage, ensuring compatibility and easing the transition. As database requirements evolve and data volumes grow, MariaDB provides a modern and efficient alternative to MySQL, supporting organizations in achieving higher performance and scalability goals.