Sustainable HA MySQL/MariaDB

I ran into this problem just yesterday, and thought I'd write about what I'm trying to do to fix it.  Use at your own risk; hopefully this will work well.

I needed to run updates on my DB cluster.  It's a two-node cluster, and generally stable on Ubuntu 12.04.2 LTS.  Unfortunately, the way I had configured my HA databases, when I ran updates one of the nodes completely broke.  Unable to start MariaDB, the update process failed.  MariaDB couldn't start because the database files were nowhere to be found on that node at that time.

Not liking the idea of having to update the database server "hot," then migrating over to the second node and updating it "hot" again, I thought perhaps this would be a good time for some manual package management.  This would mean the following:

• I'd have to get the packages manually and configure the essentials accordingly - factory-default paths be damned!
• No more automatic updates - a mixed bag: they're awesome when they work and terrible when they don't.  Luckily they usually "Just Work" (tm)
• I'd have the latest and greatest that MariaDB has to offer.
• I would have to be more mindful in the future about updates and making sure things don't break en-route to a new version.

OK, so originally I had installed MariaDB via apt-get, and put the database files themselves on an iSCSI target.  I used bind-mounts to place everything (from configuration files to the actual db files) where MySQL/MariaDB expected everything to be.  For this fix, my first thought was to put the binaries (well, the whole MariaDB install) on the iSCSI target.  This would mean one upgrade, one copy of binaries, and only one server capable of starting said database.

That didn't work - Pacemaker needs access to the binaries to make sure the database isn't started elsewhere on the cluster.  So, I set up a directory structure as follows:

• /opt/mariadb
• .../versions/ (put your untarred-gzipped deployments here)
• .../current --> links to versions/(current version you want to use)
• .../var   --> this is where the iSCSI target will now be mounted
• .../config  --> my.cnf and conf.d/... will be here

MariaDB offers precompiled tar.gz deployments, which is really nice.  I can put these wherever I want. In this case I'm going the route of having an escape-route for future upgrades by putting the fresh deployment files in a versions/ directory and linking to the version that I want to use.  No changes to configuration files or Pacemaker should be necessary, and upgrades won't stomp existing deployments this way.  Of course, back up your databases frequently and before each upgrade.

Inside /opt/mariadb/var, I've placed a log and db directory.  log originally came from /var/log, and has a variety of transaction logs in it.  The db folder contains the actual database files, what would normally be found in /var/lib/mysql.  

The configuration files MIGHT work under the /opt/mariadb/var folder, which would mean it ought to be named something more appropriate.  I left them out for sake of having them always available on both nodes.  I felt this was a safer route and don't have time to experiment much.

The my.cnf file has to be properly configured.  I snagged the my.cnf file that the original MariaDB apt-get install provided, and changed paths accordingly.  Now there are no bind-mounts, and for all intents and purposes I could simply duplicate the entire /opt/mariadb directory on a new node and be up and running in no-time.  (New node deployment is technically untested as of this writing.)

Note that if you happen to be moving existing log files (especially a .index file), the .index file will contain file paths that need to be updated.  sed will be your friend here, and you can cat the file to see the contents.  Once everything is done, you should be able to perform the following command and see a successful MariaDB launch:

/opt/mariadb/current/bin/mysqld --defaults-file=/opt/mariadb/config/my.cnf

In case you don't know, here's how you shut down your successful launch:

/opt/mariadb/current/bin/mysqladmin --defaults-file=/opt/mariadb/config/my.cnf shutdown -p

The MySQL primitive in Pacemaker needs to be properly configured.  Here is what mine looks like:

primitive p_db-mysql0 ocf:heartbeat:mysql \
params binary="/opt/mariadb/current/bin/mysqld" \
                    config="/opt/mariadb/config/my.cnf" \
                    datadir="/opt/mariadb/var/db" \
                    pid="/var/run/mysqld/mysqld.pid" \
                    socket="/var/run/mysqld/mysqld.sock" \
op start interval="0" timeout="120s" \
op stop interval="0" timeout="120s" \
op monitor interval="20s" timeout="30s"


So far, this new configuration seems to work.  Comments and suggestions are welcome.