If you’re considering migrating from MySQL to MariaDB, there’s a solid case for making the switch. As one of the first database management system (DBMS) platforms, MySQL has dominated the industry for a long time and remains remarkably relevant.
However, organizations are slowly moving away from it due to its slower processing and inefficiency in handling complex data. Many of these organizations turn to MariaDB.
MariaDB is a newer, lightweight DBMS that offers improved performance, more cutting-edge features, and a greater capacity to handle complex datasets. Compared to MySQL, MariaDB also boasts a more vibrant community that keeps its public repository updated and makes innovative changes.
This article will guide you through migrating 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.
MariaDB and Kinsta
MariaDB is one of the fastest-innovating DBMS platforms, and that trend will likely continue for the foreseeable future. At Kinsta, we believe in the power of MariaDB. That’s why we use MariaDB as part of our performance-driven server stack to provide excellent server performance. Our hosting plans enable you to leverage the best and fastest technology available.
Kinsta makes it easy to use MariaDB. You don’t have to worry about updating your database software, cleaning the database, or checking for errors. We take care of all these nitty-gritty tasks for you so that you can focus on creating the best content for your site users.
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.
With Kinsta’s database hosting services, you can spin up a database and use fast, secure internal connections.
Leave a Reply