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!
