Moving a single-instance MySQL server to MariaDB Galera Cluster

I’ve been keeping an eye on MariaDB for a while.  Since MySQL was acquired by Oracle, there have been concerns in the community about how Oracle will manage it going forward.  MariaDB is a fork of MySQL, run by some of the people who originally created MySQL, that was created to address these concerns.  It’s recently been picking up a fair amount of attention, in particular with some Linux distributions choosing to use it as a replacement for MySQL.  (This reminds me of what happened with LibreOffice a couple years back.)

Anyway, I work with a MySQL system and I now have a need to get it running on a cluster of some sort, for scalability and redundancy.  Since MariaDB Galera Cluster appears to be a little easier to set up and more flexible than MySQL Cluster, I figured that this would be a good time to put it to the test.

The purpose of this post is to document the process that I went through getting a MySQL database moved over to MariaDB and set up in a cluster.  I hit a few snags along the way, so I figure that this could be useful to others.

The MySQL server in question is running on top of Ubuntu Server 10.04.4 LTS, using the stock MySQL packages.  As such, I was running MySQL 5.1.  The current version of MariaDB Galera Cluster is 5.5, which includes most if not all of the improvements made in MySQL 5.5, so even getting a single MariaDB instance up would bring in some benefits.  Fortunately, performing an in-place “upgrade” from MySQL 5.1 to MariaDB 5.5 turned out to be quite simple.

MariaDB has Debian repositories set up which can be added to Ubuntu.  They have a repository configuration tool that will help you add the keys and repository locations in Ubuntu.  After getting the repository set up, you’ll notice that package updates are already available.  Go ahead and run them, and pay attention as it will bring in changes to your my.cnf file that you might have to merge if you have already made changes to the default values there.

After that is out of the way, it just takes a sudo apt-get install mariadb-galera-server galera to perform the “upgrade” to MariaDB Galera Cluster.  The MySQL packages will be replaced with MariaDB packages automatically, and when that is done, the server will start right up.  It should be a painless process, with the only downtime being a short period of time during the install when MySQL is removed and MariaDB is installed.  Note that even though I am installing the mariadb-galera-server package here in order to use clustering down the road, it will work just fine as a standalone server for now.  Since the database has been upgraded from 5.1 to 5.5, you should run a sudo -u mysql mysql_upgrade --user=root --password (providing your server’s root password at the prompt) to upgrade some of the data formats.  At this point, I recommend testing the server for a few days to make sure that there are no surprises, before moving on to clustering.

Now, before we start adding other servers to the database cluster, we should examine the different SST methods that are available.  These methods are what are used to “bootstrap” other servers into the cluster, giving them a current copy of the data.  There are three methods listed at Codership.  The first two, mysqldump and rsync, are probably familiar tools and you can imagine how they work here; however, using either of these will block queries on the “donor” server until they are finished. Because of this, I recommend using the third method, xtrabackup, which will allow the server to operate normally while it is copying data over to the cluster, but is a little more obscure and requires a separate installation.

Visit Percona’s apt repository site and add the necessary repository information to Ubuntu.  Afterwards, sudo apt-get install xtrabackup to get xtrabackup installed on the server.

Now, MariaDB Galera Cluster should be run on at least three servers, so you’ll now need to think about setting up the extra ones.  I recommend using the same version of Ubuntu for all of them (though I don’t believe that this is required).  Install the mariadb-galera-cluster, galera, and xtrabackup packages on the extra servers as described above.  Also, set them up so that the my.cnf configuration is identical to the one on your original server.

Codership lists the configuration values required to get MariaDB Galera Cluster running.  Here are the values that I ended up using.

In my.cnf, I changed query_cache_size to 0.

In /etc/mysql/conf.d/mariadb.cnf, I added:

wsrep_provider = /usr/lib/galera/
wsrep_provider_options = "gcache.size=1G; gcache.page_size=128M"
wsrep_cluster_address = gcomm://,,
wsrep_cluster_name = 'dev_cluster'
wsrep_node_address = ''
wsrep_node_name = 'node1'
wsrep_sst_method = xtrabackup
wsrep_sst_auth = root:root_password_here

binlog_format = ROW
default_storage_engine = innodb
innodb_autoinc_lock_mode = 2
open_files_limit = 10240

Note that wsrep_cluster_address and wsrep_cluster_name should be changed by you to match your environment.  Also, wsrep_node_address and wsrep_node_name should be changed for each individual node.  Finally, wsrep_cluster_address should be just gcomm:// for your original server.

The new cluster servers will need root access to the original server, so you’ll need to make sure that you have that set up in such a way that they will be able to access it.  wsrep_sst_auth will need to be updated with the correct root password.

One other thing.  MySQL has a default “max open files” value of 1024, which will be too small for the cluster data copy to work if you have a lot of tables.  While I did change open_files_limit above, it is also necessary to increase the limit at the system level.  See the highest voted post on this page for information about this.  This should be addressed on all of your servers.

After applying the changes, restart MariaDB on the original server and it should come back up normally.  Start MariaDB on one of your new cluster servers and it should begin to copy over data from the original server.  You can run the query SHOW STATUS LIKE 'wsrep_%'; on the original server to get some information including which servers are currently clustered and the cluster status, and you can watch /var/lib/mysql/innobackup.backup.log on your original server to see data transfer progress.  If there is a problem, you might be able to find information in innobackup.backup.log file mentioned here or the general MariaDB error logs in /var/log/mysql. Be sure to check the logs on both the original server and the new server.

After the transfer is finished, the database on the new server should be accessible just like the original server.  Once it is done, change wsrep_cluster_address on your original server to match the value used on the rest of them.  (If you ever shut down all of the servers in your cluster, the first one booted up will need to be started with just “gcomm://“.)  And finally, remember again that Galera needs at least three servers or you might run into data consistency issues.

Giving credit

In addition to the pages linked above, this article and this article were very useful in figuring this stuff out.

One thought on “Moving a single-instance MySQL server to MariaDB Galera Cluster”

Leave a Reply

Your email address will not be published. Required fields are marked *