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
Comments ( 0 )
No comments available