For those that might not know: MariaDB is a binary-safe, drop-in MySQL replacement. In my opinion, apart from some extra functionalities (that I’m interested in – like using indexes on subqueries) it has one major advantage when compared to MySQL: it’s not ORACLE. Unfortunately there are rumours that MySQL eventually might become a closed source product – and that of course would be bad for millions of websites (but good for Larry).

At sysCore we are migrating all our MySQL servers to MariaDB – and whenever possible for new projects we go with PostgreSQL.

Today’s HOWTO is quick and simple – migrating from MySQL to MariaDB on a Ubuntu 12.04 LTS machine (precise pangolin).

Before we add MariaDB repository there’s a bit of cryptowork ahead – i.e. you have to import the key that is used to sign MariaDB deb packages. Full information can be obtained here: http://kb.askmonty.org/en/installing-mariadb-deb-files/.

At the time of writing the key ID was ”0xcbcb082a1bb943db” and the fingerprint:

1993 69E5 404B D5FC 7D2F E43B CBCB 082A 1BB9 43DB

To import the key you should use the ”apt-key” application:

sudo apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db

Output should be similar to this:

gpg: requesting key 1BB943DB from hkp server keyserver.ubuntu.com
gpg: key 1BB943DB: public key "Daniel Bartholomew (Monty Program signing key) " imported
gpg: no ultimately trusted keys found
gpg: Total number processed: 1
gpg:               imported: 1

Now it’s time to configure your MariaDB repository. Maintainers of the project have created a very nice repository configuration page: http://downloads.mariadb.org/mariadb/repositories/. Just go there and select your repository configuration. At the time of writing I am selecting ubuntu -> precise -> 5.5 -> switch.ch 

# MariaDB 5.5 repository list - created 2012-09-16 14:28 UTC
# http://downloads.mariadb.org/mariadb/repositories/
deb http://mirror.switch.ch/mirror/mariadb/repo/5.5/ubuntu precise main
deb-src http://mirror.switch.ch/mirror/mariadb/repo/5.5/ubuntu precise main

To keep things in order I suggest creating a separate apt source file called: /etc/apt/sources.list.dk/mariadb.list. It’s a bit easier to have extra repositories configured separately from the management point of view. Now it’s time update our package list:

sudo apt-get update

Right now we’re ready to install MariaDB. The installation that I was performing was on a live server. You should definitely backup all your databases before you continue.

sudo apt-get install mariadb-server mariadb-client

MySQL and MariaDB cannot in this case coexist – you have to be aware of the fact that your current MySQL installation will be removed.

The following packages will be REMOVED:
  mysql-client-5.5 mysql-client-core-5.5 mysql-server mysql-server-5.5 mysql-server-core-5.5
The following NEW packages will be installed:
  libaio1 libmariadbclient18 mariadb-client mariadb-client-5.5 mariadb-client-core-5.5 mariadb-common mariadb-server mariadb-server-5.5 mariadb-server-core-5.5
The following packages will be upgraded:
  libmysqlclient18 mysql-common

Database directory will be of course preserved – MariaDB is a drop-in, binary compatible replacement for MySQL, so it should be able to start working with your old DB files without any problems (nevertheless, it’s always better to have a backup). The upgrade/change process consists of the following steps:

  • MySQL will be stopped and removed (but db files will be preserved).
  • MariaDB will be installed. You will be asked for new root password for your MariaDB SQL server.
  • MariaDB will check if there are any files/tables requiring fixing.
  • MariaDB will be started. You should verify if everything works as it should.

That’s it! You are now running MariaDB on your Ubuntu 12.04 LTS.

A few important comments:

  • if you plan to perform this change on a very busy server – I suggest you announce to your customers that there will be a maintenance downtime; love your data and make sure it stays in its logical consistency also after the upgrade
  • you have been informed to take a backup;mysqldump is a very good option for that
  • just copying the /var/lib/mysql/ directory as the mysql server is running IS NOT considered a backup; it’s ok to copy when mysql is not running (if you copy it while the server is running you might end up with corrupted data – in other words your db files might be missing all the data that was not flushed from the memory)
  • mysqlhotcopy cannot be used for databases with tables other than MyISAM and ARCHIVE
  • if your MySQL database is enormous – or you run replication services or other enterprise features – this HOWTO might not be a solution to your problem