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.

Configurations

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 127.0.0.1) 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 10.65.10.14/24
 hastctl role primary db_block
 while [ ! -e /dev/hast/db_block ]
 do
 sleep 1
 done
 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 10.65.10.14
}

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

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

clean() {
 residue
 if [ $? -ne 0 ]
 then
 exit 0
 fi
 exit 1
}

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

Troubleshoot

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.