☰
✕
✕

How to Migrate from MySQL to MariaDB on Ubuntu.

To Migrate from MySQL to MariaDB on Ubuntu

MariaDB and MySQL are similar to each other in many aspects, so much so that many have started to switch to MariaDB from MySQL. MariaDB is considered to be a drop in replacement to MySQL as it is a community-developed fork of the MySQL database system. Following are the features of MariaDB.

Features

  • Backwards compatible with MySQL
  • Forever open source
  • Maintained by MySQL' s creator
  • More cutting edge features
  • More storage engines
  • Large websites have switched

Migrating from MySQL to MariaDB

Having a sample database is essential for testing purpose. So, Login into MySQL server to create a database.

root@linuxhelp1:~# mysql -u root &ndash p
mysql>  create database linux_db 
mysql>  show databases 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linux_db           |
| mysql              |
| performance_schema |
+--------------------+

mysql>  q 
Bye

Creating a backup for the existing database is another important thing to do. Do it by invoking the following command.

root@linuxhelp1:~# mysqldump --all-databases --user=root --password --master-data >  back_db.sql
Enter password: 
mysqldump: Error: Binlogging on server not active

The Binlogging error is showing to fix this error do the following steps.

Now, configure the my.cnf as follows.

root@linuxhelp1:~# vim /etc/mysql/my.cnf-bin
[mysqld]
Log-bin=mysql-bin

Once it is done. restart the MySQL service by triggering the following command. 

root@linuxhelp1:~# systemctl restart mysql.service

Run the mysqldump command again to backup all the databases. 

root@linuxhelp1:~# mysqldump --all-databases --user=root --password --master-data >  back_db.sql

Also, take a backup of my.cnf file by using the following command.

root@linuxhelp1:~# cp /etc/mysql/my.cnf my.cnf.copy

Now is the time to cease the MySQL service. Do it by triggering the following command. 

root@linuxhelp1:~# /etc/init.d/mysql stop
[ ok ] Stopping mysql (via systemctl): mysql.service.

Also, remove the mysql server as follows. 

root@linuxhelp1:~# apt-get remove mysql-server 
Reading package lists... Done
Building dependency tree       
Reading state information... Done
.
.
.
(Reading database ... 177417 files and directories currently installed.)
Removing mysql-server (5.6.31-0ubuntu0.15.10.1) ...

MariaDB can be installed now. Use the following command for the same purpose. 

root@linuxhelp1:~# apt-get install mariadb-server
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following extra packages will be installed:
  libreadline5 mariadb-client-10.0 mariadb-client-core-10.0 mariadb-common
.
.
.

Processing triggers for libc-bin (2.21-0ubuntu4) ...
Processing triggers for ureadahead (0.100.0-19) ...
Processing triggers for systemd (225-1ubuntu9) ...

MariaDB is installed, restore the configuration file as follows. 

root@linuxhelp1:~# cp my.cnf.copy /etc/mysql/my.cnf

Import the backed-up database into MariaDB server.

root@linuxhelp1:~# mysql -u root -p <  back_db.sql

You have successfully switched to MariaDB from MySQL. Verify the migration process by logging into MariaDB. 

root@linuxhelp1:~# mysql -u root -p

MariaDB [(none)]>  show databases 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linux_db           |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]>  q 
Bye
Tag : MariaDB MySQL
FAQ
Q
How to Migrate from MySQL to MariaDB using Drop-in-replacement?
A
1. Install the MariaDB software repo for your operating system
2. Install MariaDB with your package manager.
3. Restart the MySQL service.
4. Done. Continue using the database as if it were MySQL.
Q
How to Migrate from MySQL to MariaDB using Master-Slave Replication?
A
In this method, it will be possible to switch from MySQL to MariaDB without causing downtime.
To seamlessly switch between databases, we use HAProxy as a load balancer for multiple MySQL instances.
1.1. First, install MariaDB on a new database server. This new host will later become the master database server
so it is important to use sufficient hardware.
2. Configure master-slave MySQL replication such that the new database server becomes the slave.
3.HAProxy must be configured to failover to the MariaDB slave once we take the MySQL master offline.
Q
How to Migrate from MySQL to MariaDB using Master-Slave Replication?
A
In this method, it will be possible to switch from MySQL to MariaDB without causing downtime.
To seamlessly switch between databases, we use HAProxy as a load balancer for multiple MySQL instances.
Q
Why should I use MariaDB instead of MySQL?
A
MariaDB is an enhanced drop-in replacement and community-developed fork of the MySQL database system.
It was developed by MariaDB foundation and is being led by original developers of MySQL. Working with MariaDB is entirely the same as MySQL.
Q
How to Create a backup for the existing database?
A
Run the followin command:
# mysqldump --all-databases --user=root --password --master-data > back_db.sql