Using MariaDB Backup and MariaDB MaxScale to Scale Online

This article is rather practical. What it aims to show is how we can use a script that in turn uses MariaDB Backup to back up a MariaDB Server master, how we can create a MariaDB Server slave from this backup, and then how we can script an online update to MariaDB MaxScale 2.2 to include the new MariaDB Server slave. Let's start with describing our environment.

Our Infrastructure

We currently have one MariaDB Server master, 2 MariaDB Server slaves and one instance of MariaDB MaxScale. They are all running CentOS 7.2 and the IP addresses are 192.168.142.110 (MariaDB MaxScale), 192.168.142.111 (MariaDB Server master), 192.168.142.112 and 192.168.142.113 (MariaDB Server slaves).

Setup of the MariaDB Servers

The setup of the master server is nothing really complicated in this case, but there are a few things we have to configure to use this as a master. We need to enable the binlog and set a server ID, and this means editing the /etc/my.cnf.d/server.cnf file (if you are not on CentOS or RedHat, the location might be different) and add the following to the [mysqld] section:

server_id=111
log-bin=hostonly111

We also need to adjust the [mysqld] section in the same configuration file on the slaves, for example:

server_id=112
log_bin=hostonly112
log_error=error.log
datadir=/var/lib/mysql
report-host=192.168.142.112

This has to be adjusted accordingly of course to fit your setup and also note that not all of these are strictly necessary. I will not show more of the master and slave configuration here, as this is not the goal of this article.

Setup of MariaDB MaxScale

There is a basic setup of MariaDB MaxScale that is assumed here, but note that MariaDB MaxScale, from version 2.2, stores a binary version of its configuration separately. This is useful when you use online dynamic reconfiguration, but it makes things a bit more complicated. What I am showing here is the basic MariaDB MaxScale configuration that is used to support the cluster set up as above and this is stored in the file /etc/maxscale.cnf:

# Global parameters
#
[maxscale]
threads=auto

# Server definitions
#
[server1]
type=server
address=192.168.142.111
port=3306
protocol=MariaDBBackend

[server2]
type=server
address=192.168.142.112
port=3306
protocol=MariaDBBackend

[server3]
type=server
address=192.168.142.113
port=3306
protocol=MariaDBBackend

# Monitor for the servers
#
[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=server1,server2,server3
user=myuser
passwd=mypwd
monitor_interval=1000

# Service definitions
#
[Read-Write-Service]
type=service
router=readwritesplit
servers=server1,server2,server3
user=myuser
passwd=mypwd

# This service enables the use of the MaxAdmin interface
#
[MaxAdmin-Service]
type=service
router=cli

# Listener definitions for the services
#
[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MariaDBClient
port=4006

[MaxAdmin-Listener]
type=listener
service=MaxAdmin-Service
protocol=maxscaled
socket=default

Note that I am not going to cover all the aspects of configuring MariaDB MaxScale here.

Backing Up the MariaDB Server Using MariaDB Backup

Mariabackup is fully documented in the MariaDB Knowledge Base, so I will not get into details. Rather, what I aim to show is a basic bash-script that runs a backup. How you run this is not really important, but it has to be run, and the compressed and archived backup has to be placed in the agreed location. Also note that the script also runs a prepare, which means that it does any recovery necessary on the backup to create a consistent copy of the running database.

The script is far from complete, but it performs the basics. It does keep old backups and does a few other things. It is intended to be run in the background, which is why it is configured using environments variables at the top of the script and not any command line arguments.

#!/bin/bash -eu
#
set -o pipefail
MDB_USER=root
MDB_PWD=
MDB_BACKUPBASE=/home/anders
MDB_BACKUPNAME=backup
MDB_ARCHIVENAME=backuparchive
MDB_BACKUPDIR=$MDB_BACKUPBASE/$MDB_BACKUPNAME
MDB_BACKUPARCHIVEPFX=$MDB_BACKUPBASE/$MDB_ARCHIVENAME
MDB_BACKUPARCHIVEDIR=""
MDB_BACKUPARCHIVETGZ=""
MDB_BACKUPLOG=/tmp/backup.log
MDB_BACKUPCMD=/usr/bin/mariabackup

if [ "$EUID" != "0" ]; then
   echo "$0 must be run as root" >&2
   exit 1
fi

# Check if the backup directory exists.
if [ -e "$MDB_BACKUPDIR" -o -e "$MDB_BACKUPDIR.tgz" ]; then
# Find a backup archive directory.
   for I in {1..10000}; do
      if [ ! -e "$MDB_BACKUPARCHIVEPFX$I" -a ! -e "$MDB_BACKUPARCHIVEPFX$I.tgz" ]; then
        MDB_BACKUPARCHIVEDIR="$MDB_BACKUPARCHIVEPFX$I"
        MDB_BACKUPARCHIVETGZ="$MDB_BACKUPARCHIVEPFX$I.tgz"
        break
      fi
   done

   # Check that a directory was found.
   if [ "x$MDB_BACKUPARCHIVEDIR" = "x" ]; then
      echo "Can't find a suitable backup archive directory" >&2
      exit 1
   fi

   if [ -e "$MDB_BACKUPDIR" ] ; then
      mv $MDB_BACKUPDIR $MDB_BACKUPARCHIVEDIR
   fi

   if [ -e "$MDB_BACKUPDIR.tgz" ] ; then
      mv $MDB_BACKUPDIR.tgz $MDB_BACKUPARCHIVETGZ
   fi
fi

echo >> $MDB_BACKUPLOG
echo "Starting backup on `date +"%Y-%m-%d %H:%M:%S"`" >> $MDB_BACKUPLOG

# Do the backup.
echo "Backup up to $MDB_BACKUPDIR" >> $MDB_BACKUPLOG
$MDB_BACKUPCMD --backup -u $MDB_USER ${MDB_PASS:+"-p$MDB_PASS"} --target-dir=$MDB_BACKUPDIR >> $MDB_BACKUPLOG 2>&1

# Prepare and make the backup consistent.
$MDB_BACKUPCMD --prepare -u $MDB_USER ${MDB_PASS:+"-p$MDB_PASS"} --target-dir=$MDB_BACKUPDIR >> $MDB_BACKUPLOG 2>&1

# Compress and archive the backup.
cd $MDB_BACKUPBASE
tar cvfz $MDB_BACKUPNAME.tgz $MDB_BACKUPNAME >> $MDB_BACKUPLOG 2>&1

As you can see, it is nothing too complicated, and the resulting backup will be placed in the archive file /home/anders/backup.tgz.

Creating a Slave From a Master Backup

The second script to present is the one that creates a slave from a master, using the content of a backup as described above. It is assumed that this slave has MariaDB Server and MariaBackup already installed and that "ssh" is configured so that files can be copied from the master, including appropriate keys. Then, it is time to have a look at the script.

The script does quite a few things. First, it figures out a suitable server_id for this slave, and then it copies a backup archive from the master server and unpacks this. For a replication slave to be set up appropriately, we need to know the GTID at the point when the backup was taken, so this is recovered from a file that MariaDB Backup generates for us that is included with the backup archive. At this point, the MariaDB Server is shut down, and the current datadir is saved.

It is then time to recover data from the backup and set up the correct ownership of the MariaDB Server directories. At this point, we can start the MariaDB Server. All we need to do next is to configure the MariaDB Server as a slave and we are all set.

The script, which will run on the server of the MariaDB Server slave, takes two options: the IP address of the MariaDB Server master of this slave, and the host IP of this MariaDB Server slave. The latter isn't as obvious as one might think, and many servers, possibly most, have several host IPs (excluding localhost there is often one connected internally and one to a Firewalled DMZ).

#!/bin/bash
#
set -o pipefail
MDB_MASTERHOST=$1
MDB_SLAVEHOST=$2
MDB_USER=root
MDB_PWD=
MDB_CNFFILE=/etc/my.cnf.d/server.cnf
MDB_REPLUSER=repl
MDB_REPLPWD=repl
MDB_DEFAULTDATADIR=/var/lib/mysql
MDB_BACKUPNAME=backup
MDB_RESTORELOG=/tmp/createslave.log
MDB_BACKUPLOC=$MDB_MASTERHOST:$MDB_BACKUPNAME.tgz
MDB_BACKUPCMD=/usr/bin/mariabackup
MDB_MASTEROPTS="-h $MDB_MASTERHOST -u $MDB_REPLUSER ${MDB_REPLPWD:+"-p$MDB_REPLPWD"} --batch --column-names=0"

if [ "$EUID" != "0" ]; then
   echo "$0 must be run as root" >&2
   exit 1
fi

if [ "$#" -lt 2 ]; then
   echo "Usage: $0 <master host> <slave host> [<slave server id>]" >&2
   exit 1
fi

# Handle server id.
if [ "$#" -gt 2 ]; then
   MDB_SLAVEID=$3
   if [ "x`echo $MDB_SLAVEID | sed "s/^[0-9]*$//"`" != "x" ]; then
      echo "Slave server id invalid. It must be numeric" >&2
      exit 1
   fi
else
# Get a server id from the master if not specified.
   MDB_SLAVEID="`mysql $MDB_MASTEROPTS -e "SHOW SLAVE HOSTS" | awk '{print $1}' | sort -n | tail -1`"
   if [ "x$MDB_SLAVEID" == "x" ]; then
      MDB_SLAVEID="`mysql $MDB_MASTEROPTS -e "SELECT @@SERVER_ID"`"
   fi
   MDB_SLAVEID=$(($MDB_SLAVEID + 1))
fi

# Check if we have mariabackup
if [ ! -e "$MDB_BACKUPCMD" ]; then
   echo "Cannot find $MDB_BACKUPCMD command. Please install it" >&2
   exit 1
fi
# Check if datadir is set, else set the default.
MDB_DATADIR="`(grep "^ *datadir *=" $MDB_CNFFILE || true) | awk -F= '{print $2}'`"
if [ "x$MDB_DATADIR" == "x" ]; then
   MDB_DATADIR=$MDB_DEFAULTDATADIR
fi

# Print to log.
echo >> $MDB_RESTORELOG
echo "Starting restore on `date +"%Y-%m-%d %H:%M:%S"`" >> $MDB_RESTORELOG

# Copy backup from master.
scp $MDB_BACKUPLOC . >> $MDB_RESTORELOG 2>&1

# Remove old backup, if one exists.
if [ -e "$MDB_BACKUPNAME" ]; then
  rm -rf $MDB_BACKUPNAME
fi

# Unpack backup.
tar xvfz $MDB_BACKUPNAME.tgz >> $MDB_RESTORELOG 2>&1

# Get the GTID from the backup
GTID_POS=`cat $MDB_BACKUPNAME/xtrabackup_binlog_info | awk '{print $3}'`
echo >> $MDB_RESTORELOG 2>&1
echo "Restoring GTID: $GTID_POS" >> $MDB_RESTORELOG 2>&1

# Get MariaDB server status
STATUS=`systemctl is-active mariadb || true`
echo "MariaDB status: $STATUS" >> $MDB_RESTORELOG 2>&1

# Stop MariaDB if it is running.
if [ "$STATUS" = "active" ]; then
   echo "Stopping MariaDB" >> $MDB_RESTORELOG 2>&1
   systemctl stop mariadb >> $MDB_RESTORELOG 2>&1
   STATUS=`systemctl is-active mariadb || true`
   if [ "$STATUS" = "active" ]; then
      echo "Error stopping MariaDB" >> $MDB_RESTORELOG 2>&1
      exit 1
   fi
fi

# Save current datadir if that exists.
if [ -e "$MDB_DATADIR" ]; then
   MDB_DATADIR_SAVE="$MDB_DATADIR`date +\"%Y%m%d_%H%M%S\"`"

   if [ -e "$MDB_DATADIR_SAVE" ]; then
      for I in {1..100000}; do
         MDB_DATADIR_SAVE="$MDB_DATADIR`date +\"%Y%m%d_%H%M%S\"`_$I"
         if [ ! -e "$MDB_DATADIR_SAVE" ]; then
            break
         fi
      done
      if [ -e "$MDB_DATADIR_SAVE" ]; then
         echo "Can't find location for saved datadir" >> $MDB_RESTORELOG 2>&1
         exit 1
      fi
   fi

# Move datadir to saved location.
   mv $MDB_DATADIR $MDB_DATADIR_SAVE
fi


# Find mysqld group in config file.
GRPLINE=`grep -n "\[mysqld\]" $MDB_CNFFILE | tail -1 | awk -F: '{print $1}'`
# If a group wasn't found, then add one.
if [ "x$GRPLINE" == "x" ]; then
   echo "[mysqld]" >> $MDB_CNFFILE
   GRPLINE=`grep -n "\[mysqld\]" $MDB_CNFFILE | awk -F: '{print $1}'`
fi

# Set up section of variables to set.
NEWCNF=""
if [ "x`grep \"^ *server[-_]id *=\" $MDB_CNFFILE`" == "x" ]; then
   NEWCNF="server_id=$MDB_SLAVEID"
fi
if [ "x`grep \"^ *datadir *=\" $MDB_CNFFILE`" == "x" ]; then
   NEWCNF="${NEWCNF}${NEWCNF:+\n}datadir=/var/lib/mysql"
fi
if [ "x`grep \"^ *report[-_]host *=\" $MDB_CNFFILE`" == "x" ]; then
   NEWCNF="${NEWCNF}${NEWCNF:+\n}report_host=$MDB_SLAVEHOST"
fi

# Set up required variables in cnf if necessary.
if [ "x$NEWCNF" != "x" ]; then
   sed -i "${GRPLINE}a$NEWCNF" $MDB_CNFFILE
fi

# Restore from backup.
$MDB_BACKUPCMD --move-back --target-dir=$PWD/$MDB_BACKUPNAME >> $MDB_RESTORELOG 2>&1

# Set correct ownership.
chown -R mysql:mysql $MDB_DATADIR
chmod 755 $MDB_DATADIR

# Start MariaDB again.
systemctl start mariadb >> $MDB_RESTORELOG 2>&1

# Get MariaDB server status
STATUS=`systemctl is-active mariadb || true`

# Stop if MariaDB is not running.
if [ "$STATUS" != "active" ]; then
   echo "Error starting MariaDB" >> $MDB_RESTORELOG 2>&1
   exit 1
fi

# Set up node as slave.
mysql -u $MDB_USER ${MDB_PWD:+"-p$MDB_PWD"} -e "SET GLOBAL gtid_slave_pos = '$GTID_POS'" >> $MDB_RESTORELOG 2>&1
mysql -u $MDB_USER ${MDB_PWD:+"-p$MDB_PWD"} -e "CHANGE MASTER TO MASTER_HOST='$MDB_MASTERHOST',\ 
  MASTER_USER='$MDB_REPLUSER', MASTER_PASSWORD='$MDB_REPLPWD', MASTER_USE_GTID=current_pos" >> \ 
  $MDB_RESTORELOG 2>&1
mysql -u $MDB_USER ${MDB_PWD:+"-p$MDB_PWD"} -e "START SLAVE" >> $MDB_RESTORELOG 2>&

After running the script above, we have yet another slave attached to the master, but one thing remains, which is to tell MariaDB MaxScale to route reads also to the newly attached server.

Automated and Online Reconfiguration of MariaDB MaxScale

The last step is to add our new slave to the MariaDB MaxScale configuration. The way this script works is that it attaches to the master and uses that to determine which slaves exist. This is compared to the slaves that MariaDB MaxScale knows about and if one is missing, it is added. Among the programs used by this script, curl and above all jq, are used to parse JSON. These tools have to be installed to use this script.

#!/bin/bash
#
MAX_HOST=localhost
MAX_PORT=8989
MAX_USER=admin
MAX_PWD=mariadb
MDB_PORT=3306
MDB_USER=myuser
MDB_PWD=mypwd
SERVERID_PREFIX=server
CURL_OPTS="--user $MAX_USER:$MAX_PWD --silent"
if [ "x`which curl 2> /dev/null`" == "x" ]; then
   echo "Cannot find curl program" 1>&2
   exit 1
fi
if [ "x`which mysql 2> /dev/null`" == "x" ]; then
   echo "Cannot find mysql program" 1>&2
   exit 1
fi
if [ "x`which jq 2> /dev/null`" == "x" ]; then
   echo "Cannot find jq program" 1>&2
   exit 1
fi

#
# Function to add a server.
# Arguments:
# Server address
# Server node name
#
function addserver {
   curl $CURL_OPTS -H "Content-Type:application/json" http://$MAX_HOST:$MAX_PORT/v1/servers -d '{
   "data": {
      "id": "'$2'",
      "type": "servers",
      "attributes": {
         "parameters": {
            "address": "'$1'",
             "port": 3306,
             "protocol": "MariaDBBackend"
         }
      },
      "relationships": {
         "services" : {
            "data": ['$SERVICELIST']
         },
         "monitors" : {
            "data": ['$MONITORLIST']
         }
      }
   }
}'
}

#
# Function to generate a suitable server_id
#
function get_serverid {
   for I in {1..10000}; do
      found=0
      for S in $SERVERIDS; do
         if [ "$SERVERID_PREFIX$I" == "$S" ]; then
            found=1
            break
         fi
      done
      if [ $found -eq 0 ]; then
         echo "$SERVERID_PREFIX$I"
         break
      fi
   done
   return 0
}

MASTER=`curl $CURL_OPTS http://$MAX_HOST:$MAX_PORT/v1/servers | jq --raw-output '.data[].attributes | select(.state == "Master, Running") | .parameters.address'`
MASTERID=`curl $CURL_OPTS http://$MAX_HOST:$MAX_PORT/v1/servers | jq --raw-output '.data[] | select(.attributes.state == "Master, Running") | .id'`
if [ "x$MASTER" == "x" ]; then
   echo "Cannot find a master node" 1>&2
   exit 1
fi
MASTER_SERVICES=`curl $CURL_OPTS http://$MAX_HOST:$MAX_PORT/v1/servers/$MASTERID | jq --raw-output '.data.relationships.services.data[].id'`
MASTER_MONITORS=`curl $CURL_OPTS http://$MAX_HOST:$MAX_PORT/v1/servers/$MASTERID | jq --raw-output '.data.relationships.monitors.data[].id'`
SERVERS=`curl $CURL_OPTS --silent http://$MAX_HOST:$MAX_PORT/v1/servers | jq --raw-output '.data[].attributes.parameters.address' | sort`
SERVERIDS=`curl $CURL_OPTS --silent http://$MAX_HOST:$MAX_PORT/v1/servers | jq --raw-output '.data[].id' | sort`
SLAVES=`mysql -h $MASTER -P $MDB_PORT -u $MDB_USER -p$MDB_PWD -e "show processlist" --batch | grep "Binlog Dump" | awk '{sub(/:[0-9]*/, "", $3); print $3;}'`

# Create JSON list of services.
SERVICELIST=""
for S in $MASTER_SERVICES; do
   SERVICELIST="${SERVICELIST:+$SERVICELIST,}{\"id\":\"$S\",\"type\":\"services\"}"
done

# Create JSON list of monitors.
MONITORLIST=""
for S in $MASTER_MONITORS; do
   MONITORLIST="${MONITORLIST:+$MONITORLIST,}{\"id\":\"$S\",\"type\":\"monitors\"}"
done

# Loop for all slaves and see if they are defined in maxscale.
for S in $SLAVES; do
   found=0
   for SE in $SERVERS; do
      if [ "$S" == "$SE" ]; then
         found=1
         break;
      fi
   done

# If server is not found in maxscale, then add it.
   if [ $found -eq 0 ]; then
      echo "Server $S not found in MaxScale. Adding"
      SRVID=$(get_serverid)
      echo $SRVID
      addserver $S $SRVID
      SERVERIDS="$SERVERIDS $SRVID"
   fi
done

Conclusion

MariaDB MaxScale provides a powerful, flexible, and convenient means to build a scalable MariaDB Server cluster, be it Galera or a Replicated Cluster. MariaDB Backup, on the other hand, is a powerful and flexible online backup solution for MariaDB Server. Combining these technologies means that a powerful and scalable environment can easily be built, and it is scalable and flexible without downtime.

References

• jq documentation
• curl documentation
• MariaDB MaxScale REST API

Happy SQL'ing!