There is a compelling justification for switching from MySQL to MariaDB if you’re thinking about doing so. As one of the original database management system (DBMS) systems, MySQL has long dominated the market and has unusually high staying power.
However, because of its slower processing and ineffectiveness in managing complicated data, corporations are gradually turning away from it. Many of these businesses use MariaDB.
A more recent, lightweight database management system (DBMS), MariaDB, has better performance, more cutting-edge functionality, and a greater ability to manage complicated datasets. MariaDB also has a more active community than MySQL, which updates and introduces novel improvements to its public repository.
You can follow the instructions in this article to transfer your data from MySQL to MariaDB.
How To Change Databases From MySQL To MariaDB
Migrating from MySQL to MariaDB is ridiculously easy. You can break down the whole process into two significant steps:
- Back up your database in MySQL by dumping it into a SQL file.
- Log in to your MariaDB server and load the backup file to create the database.
MariaDB is compatible with MySQL, so you won’t have problems migrating your database.
After migrating your database from MySQL to MariaDB and cross-checking that the tables are complete, you can update your website to pull data from MariaDB instead of MySQL.
Requirements
Since you’re looking to migrate from MySQL to MariaDB, you should already have a MySQL database containing some data you want to move.
Naturally, you should also have a copy of MariaDB. Although not required, this tutorial uses phpMyAdmin, an open-source visual tool that lets you administer MySQL and MariaDB databases using a web browser.
To access phpMyAdmin, install either WAMPServer or XAMPP. These server packages come pre-installed with phpMyAdmin, MySQL, and MariaDB — everything you need to follow in this tutorial. (XAMPP can run on Windows, macOS, and Linux).
If you don’t have phpMyAdmin, don’t worry. The article includes the commands you can run on your terminal to replicate the same actions on MySQL and MariaDB.
Out With MySQL
Start by creating a backup of your target database in MySQL. Start your MySQL server and log in to your MySQL database using phpMyAdmin. From WAMP or XAMPP, you can access phpMyAdmin by navigating to http://localhost/phpMyAdmin/.
Once in the MySQL environment, click the database you want to back up. This example exports a WordPress database, which contains the typical WordPress tables.
Click the Export tab above the tables, then choose the SQL format. You’ll import it into MariaDB later on.
Click Go to download the database backup to your local computer as a SQL file. Alternatively, if you don’t have phpMyAdmin or prefer using commands, use the following command to dump your database into an SQL file. Replace your-name and your-pass with your database username and password.
$ mysqldump --user=your-name --password="your-pass" wordpress > wordpress.sqlIn this case, the command creates a backup file named wordpress.sql containing the SQL code from the WordPress database.
Finally, uninstall MySQL.
In With MariaDB
First, ensure you have MariaDB installed on your local machine. You won’t need to do this manually if you’re using WAMP, XAMPP, or similar distributions pre-installed with MariaDB.
Now it’s time to load the backup MySQL file into MariaDB. Stop the MySQL server from your server admin panel. Log out of the MySQL server and into your MariaDB server (just switch from MySQL to MariaDB when signing in to phpMyAdmin).
On the admin panel, create a new database. You do this in phpMyAdmin by clicking New, giving your database a name, and clicking Create.
Click the new database you just created, then click the Import tab at the top and import the backup file by clicking the Choose File or Browse option.
Click Go to load the file. The process may take a while, and if all goes well, phpMyAdmin informs you that the query was successful.
If you want to use the command line instead, follow these steps.
Log in to your MariaDB server and create the new database as follows:
$ mysql --user=your-name --password="your-pass" -e "CREATE DATABASE wordpress";Load the backup file into MariaDB.
$ mysql --user=your-name --password="your-pass" --database=wordpress < wordpress.sqlOnce you have successfully imported the files from MySQL to MariaDB, the cursor will become active again.
How To Update Your WordPress Site
After moving from MySQL to MariaDB, it’s time to get your WordPress site to start using the new database. To achieve this, you just have to update your site’s wp-config.php file with the new database details:
// ** MariaDB settings** //
define('DB_NAME', 'database_name_here');
define('DB_USER', 'database_username_here');
define('DB_PASSWORD', 'database_password_here');
define('DB_HOST', 'localhost');
define('DB_CHARSET', 'utf8');
/** The Database Collate type. Don't change this if in doubt. */
define('DB_COLLATE', '');After you save the file, the WordPress site will start pulling data from your new database.
Summary
Migrating from MySQL to MariaDB is a simple process. In short, you need to back up your database and uninstall MySQL, then install MariaDB and import your database backup.
Remember that you might encounter some issues while migrating from MySQL to MariaDB. For example, you might get an error if the schema of MySQL does not match that of MariaDB. Also, ensure you run mysql_upgrade when migrating from one release to another. In most cases, the solution to migration problems is to upgrade both databases to their latest versions before trying again.
As MariaDB continues to innovate, it will likely be less compatible with MySQL on a rudimentary level. So, if you’re using MySQL and are considering moving to MariaDB for the extra benefits, we recommend doing it sooner rather than later.
