MySQL to Maria DB Migration

With Oracle's fairly recent acquisition of MySQL, a lot of people are looking to move away from MySQL in fear of Oracle changing the licensing, which could force you to change database backends. As such, today we'll migrate from MySQL to MariaDB, which is a popular alternative to MySQL. MariaDB was initially forked in January of 2009. We can make this transition quickly, but not without some downtime, as we can't have both databases working on the same files simultaneously. These instructions are good for all popular distributions of GNU/Linux, however Debian 7 (Wheezy) will be used in the reference code shown.

This process only works reliably if you are running the same major version of MySQL as MariaDB. Currently this means you must be running MySQL 5.5 and intend on moving to MariaDB 5.5.

First and foremost, shut down all processes that use MySQL. You can't move the database around while it's in use. Doing this is far beyond the scope of this article, but since it is your system, hopefully you have a good idea about what all is running on your server, and can shut processes down appropriately. It is advised to stop MySQL by hand, rather than depending on your package manager to do it for you.

# service stop apache2

# service stop nginx

# service stop mysql

Next, make a simple mysqldump of your current databases. These commands dump every SQL database you have to a single file. Make sure you do this on a partition big enough to hold your data. Doing this in /tmp is a very bad idea.

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

We are now done with MySQL. Use your package manager to remove it. Do not worry about associated libraries, as MariaDB is a drop in replacement. It should remain compatible at the API layer. If your package manager tries to uninstall half the system, cancel the operation and proceed to the next step. apt based distributions will probably want to remove all kinds of things, but since nothing is installed on our test system, this doesn't happen so it's safe to remove in our example.

# apt-get remove mysql-server-core-5.5 mysql-server-5.5 mysql-server mysql-common mysql-client-5.5 libmysqlclient18
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following package was automatically installed and is no longer required:
libaio1
Use 'apt-get autoremove' to remove it.
The following packages will be REMOVED:
libdbd-mysql-perl libmysqlclient18 mysql-client-5.5 mysql-common
mysql-server mysql-server-5.5 mysql-server-core-5.5 php5-mysql
0 upgraded, 0 newly installed, 8 to remove and 0 not upgraded.
After this operation, 94.8 MB disk space will be freed.
Do you want to continue [Y/n]? y

Next, add the MariaDB repositories, and install it. Instructions for your distribution can be found at:

https://downloads.mariadb.org/mariadb/repositories/

If, in the prior step your package manager wanted to uninstall everything, that means it wants to do this upgrade in-place. Installing the new MariaDB packages should have simply overwritten the old MySQL packages, as your package manager sees it as any other upgrade.

# apt-get install mariadb-server
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following extra packages will be installed:
libdbd-mysql-perl libmariadbclient18 libmysqlclient18 mariadb-client-5.5
mariadb-client-core-5.5 mariadb-common mariadb-server-5.5
mariadb-server-core-5.5 mysql-common
Suggested packages:
tinyca mariadb-test
The following NEW packages will be installed:
libdbd-mysql-perl libmariadbclient18 libmysqlclient18 mariadb-client-5.5
mariadb-client-core-5.5 mariadb-common mariadb-server mariadb-server-5.5
mariadb-server-core-5.5 mysql-common
0 upgraded, 10 newly installed, 0 to remove and 0 not upgraded.
Need to get 8,794 B/31.1 MB of archives.
After this operation, 108 MB of additional disk space will be used.
Do you want to continue [Y/n]? y

Some package systems start what they install automatically, but some don't. If not, start up MariaDB now, and verify its sanity.

# mysql -u root -p -Be 'show databases'
Enter password:
Database
information_schema
drupal
mysql
performance_schema
test

This next part won't be pretty, but it shouldn't be tricky. The configuration has changed considerably between MySQL and MariaDB, however it is extremely easy to work through. Most everything that changed is related to mechanisms that have been replaced, such as how replication works. You should be safe in simply copying over the performance tuning options you set in MySQL's my.cnf, and reconfiguring the rest by hand. For small databases with less intricate setups, it is likely that you won't need to do any thing more than copy the performance options that you've changed.

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

Start up MariaDB again and verify sanity once again. Once you've done that and stopped MariaDB, start it up once more, as you normally would through your init system. Note that MariaDB shares the same name for compatibility.

# service restart mysql
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:
Database
information_schema
drupal
mysql
performance_schema
test

Lastly, start up the applications that use a SQL database.

# service apache2 start
# service tomcat start

At this point you're done! There is no conversion of your databases when switching to MariaDB, so if for whatever reason you don't like it, you can freely switch back to MySQL.

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

Article by Michael "mngrif" Griffith