Why should I migrate from MySQL to MariaDB?
The answer is very simple. 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 same as MySQL. After Oracle bought MySQL, it is not free and open source anymore, but MariaDB is still free and open source.
According to themukt,
What Oracle is doing wrong:
- New ‘enterprise’ extensions in MySQL are closed source:
- The bugs database is not public anymore
- The MySQL public repositories are not anymore actively updated.
- Security problems are not communicated nor addressed quickly (This is making Linux distributions very annoyed with Oracle)
- Instead of fixing bugs, Oracle is removing features:
- New code in MySQL 5.5 doesn’t have test cases anymore.
- Some of the new code is surprisingly good by Oracle, but unfortunately the quality varies and a notable part needs to be rewritten before we can include it in MariaDB
Well, are you done with MySQL? Come on, let us migrate from MySQL to MariaDB. The migration method is much simpler than you think.
Let’s Migrate
For the testing purpose, let us create two sample databases called testdb1, and testdb2.
Log in to MySQL as root user using the following command:
mysql -u root -p
Enter the mysql root user password. You’ll be redirected to the mysql prompt.
Create test databases:
Enter the following commands from mysql prompt to create test databases.
mysql> create database testdb1; Query OK, 1 row affected (0.00 sec) mysql> create database testdb2; Query OK, 1 row affected (0.00 sec)
To see the list of available databases, enter the following command:
mysql> show databases;
Sample output: +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | testdb1 | | testdb2 | +--------------------+ 5 rows in set (0.00 sec)
As see above, we have totally 5 databases including the newly created databases testdb1 and testdb2.
1. Backup existing databases
The first important step is to take backup of existing databases. To do that, enter the following command from the Terminal (not from MySQL prompt).
mysqldump --all-databases --user=root --password --master-data > backupdatabase.sql
Oops! I got the following error.
mysqldump: Error: Binlogging on server not active
To fix this error, we have to do a small modification in my.cnf file.
Edit my.cnf file:
On Debian/Ubuntu:
sudo vi /etc/mysql/my.cnf
On RPM based systems:
sudo vi /etc/my.cnf
Under [mysqld] section, add the following parameter.
log-bin=mysql-bin
Sample output of my my.cnf file.
[...] [mysqld] # # * Basic Settings # user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking log-bin=mysql-bin [...]
Save and exit file. Restart mysql server.
On RPM based systems:
sudo /etc/init.d/mysqld restart
On Debian based systems:
sudo /etc/init.d/mysql restart
Now, re-run the mysqldump command to backup all databases.
mysqldump --all-databases --user=root --password --master-data > backupdatabase.sql
Sample output:
Enter password: -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
The above command will backup all databases, and stores them in backupdatabase.sql in the current directory.
2. Stop MySQL services and remove all mysql packages
First, backup the my.cnf fie to a safe location.
Note: The my.cnf file will not be deleted when uninstalling MySQL packages. We do it for the precaution. During MariaDB installation, the installer will ask you to keep the existing my.cnf(old backup) file or to use the package containers version (i.e new one).
On RPM systems:
sudo cp /etc/my.cnf my.cnf.bak
On Deb systems:
sudo cp /etc/mysql/my.cnf my.cnf.bak
To stop mysql service, enter the following command from your Terminal.
On RPM based systems:
sudo /etc/init.d/mysqld stop
On Debian based systems:
sudo /etc/init.d/mysql stop
Then, remove mysql packages.
On RPM systems:
sudo yum remove mysql* mysql-server mysql-devel mysql-libs
The above command will take the backup of your current MySQL config file /etc/my.cnf to /etc/my.cnf.rpmsave.
On Debian based systems:
sudo apt-get remove mysql-server mysql-client
3. Install MariaDB
The latest CentOS 7 and Ubuntu 14.10, 14.04 contains MariaDB packages in their official repositories. If you want to use the most recent version MariaDb, add the MariaDB official repositories depending upon your OS version.
Install MariaDB On RHEL 7 based systems:
Create a file /etc/yum.repos.d/mariadb.repo,
vi /etc/yum.repos.d/mariadb.repo
Add the following lines:
# MariaDB 5.5 CentOS repository list - created 2014-12-09 08:11 UTC # http://mariadb.org/mariadb/repositories/ [mariadb] name = MariaDB baseurl = http://yum.mariadb.org/5.5/centos7-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1
Update the repositories and install mariadb using the following commands:
sudo yum update sudo yum install MariaDB-server MariaDB-client
Start mysql service using the following command:
sudo /etc/init.d/mysqld start sudo chkconfig mysqld on
Or,
sudo systemctl start mysqld sudo systemctl enable mysqld
After installing MariaDB, you should set the database root user password. To do that, execute the following command from your Terminal.
mysql_secure_installation
Install MariaDB On Ubuntu 14.04:
Here are the commands to run to install MariaDB on your Ubuntu system:
sudo apt-get install software-properties-common sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db sudo add-apt-repository 'deb http://mirror.mephi.ru/mariadb/repo/5.5/ubuntu trusty main'
Once the key is imported and the repository added you can install MariaDB with:
sudo apt-get update sudo apt-get install mariadb-server
Keep in mind that during MariaDB installation, the installer will ask you either to use the existing my.cnf(old backup) file, or use the package containers version (i.e new one). You can either use the old my.cnf file or the package containers version. If you want to use the new my.cnf version, you can restore the contents of older my.cnf (We already have copied this file to safe location before) later.
For other versions, please refer the MariaDB official repositories page.
4. Copy contents of my.cnf.bak(old backup) file to new my.cnf file
To do that, enter the following command in Terminal. I have the old my.cnf.bak file in my current directory, so I simply copied the file using the following command:
On RPM systems:
sudo cp my.cnf.bak /etc/my.cnf
On DEB systems:
sudo cp my.cnf.bak /etc/mysql/my.cnf
5. Import Databases
Finally, let us import the old databases which we’ve created in the first step using the following command.
mysql -u root -p < backupdatabase.sql
That’s it. We have successfully imported the old databases.
Let us check if the databases are really imported. To do that, log in to mysql prompt using command:
mysql -u root -p
Run show databases; command from mysql prompt to check for the databases.
MariaDB [(none)]> show databases;
Sample output:
+--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | testdb1 | | testdb2 | +--------------------+ 5 rows in set (0.00 sec)
As you see in the above result all old databases including testdb1 and testdb2 have been successfully imported.
That’s all. Start using MariaDb.
Disclaimer: Be careful while using these steps in production environment. Also, refer the MariaDB official documentations for further clarifications. If you don’t know what you’re doing, please ask a Database administrator help. Don’t lose your precious database backup.
Good luck!
