Migrating from MySql to MariaDB

A few years back, Oracle acquired the popular open source project MySQL and as you all likely well know, much of the open source community was very upset about this acquisition. Putting aside what can almost be a religion called open source, we currently have a predicament known as a 'conflict of interest'. Simply said, Oracle owns one of the biggest competing database server software titles AND its biggest open source competitor. It goes without saying that it is in Oracle's best interest to ensure that the software title that makes them money is the most successful. Now, I don't want to make any accusations here because to be honest, I hardly know anything about the development of MySQL, so I really am in no position to offer an opinion on the matter. All that aside though, some other people seem to feel that Oracle owning MySQL is not in the project's best interest, so they have forked it into several other projects. The most notable of these is MariaDB due to its creator being Michael Widenius (aka Monty), the creator of MySQL. Monty has made some pretty neat improvements since starting MariaDB. In light of that and the fact that its future as an open source software are not nearly as in question as that of MySQL, I have migrated from point A to point B. Here's how I did it.

Before I continue though, a thing or two. First, I prefer to run applications in user space if at all possible. In keeping with this, I will be doing this tutorial with a non-root user without sudo. Secondly, if your database gets horked, please feel free to ask me questions (I will be quite happy to assist if I can), but don't blame it on me. I am not responsible for your system.

Now that the obligatory disclaimer is out of the way, let's get our user created and log in (you will need root, or at least sudo access to do this step). useradd -m mariadb su mariadb

Head on over to our home directory... cd /home/mariadb Download the binary... To do this, head over here to determine the URL for the file you need to download. I grabbed the Linux gzipped tar file. Keep in mind that as this post ages, that URL will become out of date since it references version 5.3.1. Please check his site for the URL to the latest binaries.

wget http://downloads.askmonty.org/f/mariadb-5.3.1-beta/...

Extract...

tar -xvf ./mariadb

Head on in to the extracted directory move its contents one directory up (to keep things clean), and configure the database for startup.

cd mariadb-5.3.1-beta-Linux... mv ./* ../ cd .. chmod +x ./configure ./configure

This will output a lot of text which basically says the following: Change your root password and how (./bin/mysqladmin -u root -h password 'new-password') support-files/mysql.server is your startup script

It will also start your MariaDB server for you with default settings. So, let's go ahead and set our root pass (don't forget it). From your mariadb home directory, run ./bin/mysqladmin -u root -h password 'new-password'

Now that people can't readily connect via root (because that would be bad :) ), let's configure our new server. The instance that the configuration starts up likely will not work in place of your old MySQL instance (I received socket errors when I tried to connect). We still need to grab the old configuration file, adapt it for the new location, and restart the server with the new settings.

Now, if you've read much of the MariaDB website, you'll notice that it makes it quite clear that MariaDB is a 'drop-in' replacement for MySql. I wasn't exactly sure what that meant until I performed this migration myself once. I initially thought that 'drop-in' means you could import a mysqldump and that a lot of the commands were the same.

Nope.

It means that you can actually point your MariaDB installation to the data directory (typically /var/lib/mysql) of your old server and it will work. You can even use your my.cnf file (mostly I did have one problem). As Monty put it (better than I did), "For all practical purposes, MariaDB is a binary drop in replacement of the same MySQL version". So, let's get started with configuring our new server.

First off, we need our old my.cnf file. If you are running a Debian system with that installed through your package manager, it is likely located at /etc/mysql/my.cnf. Copy that to your MariaDB installation directory (in our example, that is /home/mariadb). Also, rename the old one in /etc/mysql to something like my.cnf_old. If you skip this step, it will likely cause problems for you. MariaDB checks the old location for the my.cnf file and if it finds it, it will overwrite your updated configuration on startup (no damage should be done, it just won't work). cp /etc/mysql/my.cnf /home/mariadb/my.cnf mv /etc/mysql/my.cnf /etc/mysql/my.cnf_old

Alrighty, now that we have that, we need to go into our new my.cnf file and update a few things. vi /home/mariadb/my.cnf

Scroll on down for about line 31-ish. You should see the following:

[mysqld] 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 language       = /usr/share/mysql/english skip-external-locking
 * 1) * Basic Settings
 * 1) * Basic Settings

Change that to look like the following (once again, as per our example installed at in /home/mariadb). Changes are bolded.

[mysqld] user           = mariadb pid-file       = /var/run/mysqld/mysqld.pid socket         = /var/run/mysqld/mysqld.sock port           = 3306 basedir        = /home/mariadb datadir        = /home/mariadb/data tmpdir         = /tmp #language      = /usr/share/mysql/english skip-external-locking
 * 1) * Basic Settings
 * 1) * Basic Settings

Now that we've made the changes to my.cnf, we're only two steps away from having this set up. We need to copy our startup script to the installation location (or to init.d). cp /home/mariadb/support-files/mysql.server /home/mariadb On start, that should search the running directory (/home/mariadb in this case) for my.cnf. Now that our configuration file is in place, all we have to do is copy our old data to the new location and start er' up. Your old data is likely located in /var/lib/mysql. cp /var/lib/mysql/* /home/mariadb/data Now that we've got our data, we just start up our server. From the installation location (or init.d), run ./mysql.server start You should now have a fancy new MariaDB server running in place of your old MySQL server. I'm not sure if it's just because it's shiny and new or what, but it seems a lot faster than MySQL. Please let me know if there are any questions.

Many thanks to Monty for branching this project from MySQL.