Highly Available MySQL Server

Previously, I have discussed how to setup a highly available block device and also a highly available file system.  In this article, I further demonstrate how to setup a highly MySQL database service.

Installing the Packages

As usual, one will need to install the package on two hosts and it can be easily done by:

# pkg install mysql57-server

I know there are alternatives.  Forgive my laziness.

Running for the First Time

We are starting MySQL once so that it generates the file structures.  Try to login, and then Ctrl-D to exit.

# service mysql-server onestart
# cat /root/.mysql_secret
# mysql -u root -p
Password: **********
root@localhost [(none)]> ^D
# service mysql-server onestop

It is then discovered (through educated guess) some directories are created in the “/var/db” directory, namely “mysql”, “mysql_tmpdir”, and “mysql_secure”.  Suppose you already have the “/db” mounted (as in the previous article), move them there and make the replacement symbolic links.

# mv /var/db/mysql /db
# mv /var/db/mysql_tmpdir /db
# mv /var/db/mysql_secure /db
# ln -s /db/mysql /var/db/
# ln -s /db/mysql_tmpdir /var/db/
# ln -s /db/mysql_secure /var/db/
# ls -ld /var/db/mysql*
lrwxr-xr-x  1 root  wheel   9 Apr 19 20:37 /var/db/mysql -> /db/mysql
lrwxr-xr-x  1 root  wheel  16 Apr 19 20:37 /var/db/mysql_secure -> /db/mysql_secure
lrwxr-xr-x  1 root  wheel  16 Apr 19 20:37 /var/db/mysql_tmpdir -> /db/mysql_tmpdir

Some would question why not change the configuration for the new paths.  I find it mostly a matter of taste.  If you want to make lives easier for those who have recited the default paths, do make the symbolic links.


You will want to modify the configuration file “/usr/local/etc/mysql/my.cnf”.  For your reference, there is a sample file “my.cnf.sample”.  At minimum, you will need to modify the bind address (default so that the service is available not just locally, but to the other computers in the same intranet.

The Script

The script for starting and stopping the MySQL server is simpler than the NFS one and are as follows.  Like last time, automatic switching is skipped due to my conflict of interest.  You will need a mechanism to call “start” and “stop” properly.

#!/bin/sh -x

start() {
 ifconfig vtnet1 add
 hastctl role primary db_block
 while [ ! -e /dev/hast/db_block ]
 sleep 1
 fsck -t ufs /dev/hast/db_block
 mount /dev/hast/db_block /db
 service mysql-server onestart

stop() {
 service mysql-server onestop
 umount /db
 hastctl role secondary db_block
 ifconfig vtnet1 delete

status() {
 ifconfig vtnet1 | grep && \
 service mysql-server onestatus && \
 ls /dev/hast/db_block

residue() {
 ifconfig vtnet1 | grep || \
 service mysql-server onestatus || \
 mount | grep /db || \
 ls /dev/hast/db_block

clean() {
 if [ $? -ne 0 ]
 exit 0
 exit 1

if [ "$1" == "start" ]
elif [ "$1" == "stop" ]
elif [ "$1" == "status" ]
elif [ "$1" == "clean" ]


If there are any issues MySQL fails to start, you can verify its absence with the command “service mysql-server onestatus”.  There are also log files located in the MySQL data directory; in our context, it is “/db/mysql/<hostname>.err”.  Please note the end of the log is most likely a graceful shutdown.  You will need to scroll upwards for the actual reason why the startup failed.


3 thoughts on “Highly Available MySQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s