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