Skip to main content
MariaDB
Get startedContact us
MariaDB
Contact us

Menu

Get started
Webinar

Built for the Agentic Era: Introducing MariaDB Enterprise Platform 2027

Register now

Product

Using MariaDB Backup and MariaDB MaxScale to Scale Online

By Anders Karlsson

14 min read
Share
This blog post 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 primary, how we can create a MariaDB Server replica from this backup and then how we can script an online update to MariaDB MaxScale 2.2 to include the new MariaDB Server replica. Lets start with describing our environment.

Our infrastructure

  Infrastructure2.JPG We currently have one MariaDB Server primary, 2 MariaDB Server replicas and one instance of MariaDB MaxScale. They are all running CentOS 7.2 and the IP adresses are 192.168.142.110 (MariaDB MaxScale), 192.168.142.111 (MariaDB Server primary), 192.168.142.112 and 192.168.142.113 (MariaDB Server replica).

Setup of the MariaDB servers

The setup of the primary server is nothing really complicated in this case, but there are a few things we have to configure to use this as a primary. 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 replicas, for example: 
server_id=112
log_bin=hostonly112
log_error=error.log
datadir=/var/lib/mysql
report-host=192.168.142.112
And 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 primary and replica configuration here, this is not the goal of this blog.

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 it’s configuration separately. This is useful when you use online dynamic reconfiguration, but it makes things a bit more complicated. What I am showing here then 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 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, nothing too complicated and the resulting backup will be placed in the archive file /home/anders/backup.tgz.

Creating a replica from a primary backup

The second script to present is the one that creates a replica from a primary, using the content of a backup as described above. It is assumed that this replica has MariaDB Server and MariaBackup already installed and that ssh is configured to that files can be copied from the primary, 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 replica and then it copies a backup archive from the primary server and unpacks this. For a replication replica 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, if it is running, 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 replica and we are all set. The script, which will run on the server of the MariaDB Server replica, takes two options: the IP address of the MariaDB Server primary of this replica, and the host IP of this MariaDB Server replica. 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 replica attached to the primary, 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 replica to the MariaDB MaxScale configuration. The way this script works is that it attaches to the primary and use that to determine which replicas exist. This is compared to the replicas that MariaDB MaxScale knows about and if one it missing, it is added. Among the programs used by this script is curl and above all jq, which is 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

Happy SQL’ing /Karlsson

Tags

  • backup
  • Cluster
  • flexible
  • Galera
  • MariaDB Server
  • MaxScale
  • MaxScale REST API
  • Primary/Replica
  • REST API
  • scalable
  • Scale

Author

Anders Karlsson

Principal Sales Engineer, MariaDB

View blog post in markdown

More from the blog

Recommended blogs

View all blogs
Product4 min read

Introducing MariaDB Enterprise Cluster on MariaDB Cloud

The modern enterprise doesn’t just run on data, it survives on the absolute certainty of that data. As applications become […]

Product4 min read

MariaDB Documentation Got a Major Upgrade: Welcome to Our New Unified Docs Platform!

Product3 min read

Simplify Database Management with the MariaDB Kubernetes Operator for Enterprise

View all blogs