MySQL to Maria DB: Quick Migration Guide for Debian

When a product that works and pays off, and is open source, falls into the hands of a company whose goal is none other than to make more and more money, the world trembles.

It already happened with OpenOffice at the time and now it's the turn of MySQL. Bearing to Oracle behind nobody knows what can happen and it is good to know that there are alternatives and specifically the best of all is Maria D.B..

Quoting Wikipedia:

MariaDB is a database management system derived from MySQL with GPL license. It is developed by michael widenius (founder of MySQL) and the developer community FOSS. Enter two storage engines new, one called Aria -which replaces with advantages MyISAM- and another call XtraDB -replacing InnoDB. It has a high compatibility with MySQL since it has the same commands, interfaces, APIs and libraries, its objective being to be able to change one server for another directly.

So without further ado let's see how to go from MySQL a Maria D.B..

Migrating from MySQL to Maria DB

This process cannot be done hot. In other words, we have to stop for a moment our services and processes that are executing MySQL.

# service stop apache2 # service stop nginx # service stop mysql

In this case we stop Apache or NGinx depending on which one we are using, and of course we also stop MySQL.

Later we make a backup of our MySQL database:

# mysqldump -u root -p --all-databases > mysqlbackup.sql

And we remove all packages related to MySQL:

# aptitude remove mysql-server-core-5.5 mysql-server-5.5 mysql-server mysql-common mysql-client-5.5 libmysqlclient18

Now we have to install Maria DB. Unfortunately, it is not in the Debian repositories yet, but we can install it using its own repositories. For other distributions, you can see the instructions here.

We add the following to our /etc/sources.list file:

# MariaDB 5.5 repository list - created 2013-08-02 13:48 UTC # http://mariadb.org/mariadb/repositories/ deb http://ftp.osuosl.org/pub/mariadb/repo/5.5/debian wheezy main deb-src http://ftp.osuosl.org/pub/mariadb/repo/5.5/debian wheezy main

Then we update and install Maria DB:

sudo aptitude update sudo apt-get install mariadb-server

We start Maria DB (if it did not do it automatically) and check that it is working:

# mysql -u root -p -Be 'show databases' Enter password:

Some settings have changed considerably between MySQL and MariaDB, however it is very easy to work with. Almost everything that changed has to do with the mechanisms that have been replaced, for example, replication. We only have to copy the performance optimization options that we had in the file my.cnf de MySQL, and reconfigure the rest by hand.

For example, these data:

bind-address = 127.0.0.1 max_connections = 10 connect_timeout = 30 wait_timeout = 600 max_allowed_packet = 16M thread_cache_size = 256 OR sort = 16M bulk_insert_buffer_size = 16M tmp_table_size = 64M max_heap_table_size = 64M

We make the necessary changes and restart Maria DB.

# service mysql restart Stopping MariaDB database server: mysqld. Starting MariaDB database server: mysqld. . . Checking for corrupt, not cleanly closed and upgrade needing tables .. # mysql -u root -p -Be 'show databases' Enter password:

Yes, Maria D.B. keep the same mysql name to restart the service, in order to maintain better compatibility. If everything is fine, then we start the rest of the services:

# service apache2 start # service nginx start

And ready. If we want to go back (which I don't recommend), we just have to run:

# service mysql stop # apt-get remove mariadb-server-5.5 mariadb-common mariadb-client-5.5 libmariadbclient18 # apt-get install mysql-server

Source: Article taken and modified from BeginLinux