MySQL Backup and Recovery Plugin

The database backup and recovery plug-in is based on Percona XtraBackup and supports physical hot backup and full recovery of MySQL database.

For many users who use the Kato platform, the Mysql database will inevitably be used when deploying applications. For the database, it is vital to ensure the availability of data. On this basis, Kato officially made a backup of the Mysql database. And restore the plug-in.

Production Method

Basic Tools

The backup and recovery tool used in the Mysql backup and recovery plug-in is Percona XtraBackup, and the relevant content can be viewed in Percona XtraBackup official document; The timing task command used in the backup plug-in is go-cron. The plug-in supports user-defined configuration of the backup interval. A brief description of how go-cron is configured. Go-cron supports granularity to the second level. For the format, please refer to the example below, where week Are optional, the other five are mandatory

# ┌──────────── second (0-59)
# │ ┌───────────── min (0-59)
# │ │ ┌───────────── hour (0-23)
# │ │ │ ┌────────────── day of month (1-31)
# │ │ │ │ ┌──────────────── month (1-12)
# │ │ │ │ │ ┌───────────────── day of week (0-6) (0 to 6 are Sunday to
# │ │ │ │ │ │ Saturday, or use names; 7 is also Sunday)
# │ │ │ │ │ │
# │ │ │ │ │ │
# * * * * * * command to execute

Explain the characters supported by go-cron so that users can quickly use the plugin

Support CharactersFunction Description
*Indicates all values ​​of the field where the match is located
/Represents the growth interval, the value of the first field is 0/3, which means that the task starts at 0 seconds every minute and the task is executed every 3 seconds
-Indicates all the values ​​that can be matched within the range. The value of the first field is 15-30, which means that the task will be executed once every second within 15-30 seconds of every minute
,Represents the enumeration value, the value in the first field is “2,15,29”, which means that the task will be executed every 2 seconds, 15 seconds, and 29 seconds every minute

In particular, ```* 3 * * *'' means that the task will be executed once every second in the 3rd minute of every hour every month, ``0 3 * * *'' means that every day of the month The task will be executed once every hour at the 3rd minute and 0 second. Please be careful when setting

Plug-in Type

The above are some basic tools used by the backup and recovery plug-in. Next, I will explain the selection of the Mysql backup plug-in and the Mysql recovery plug-in type.

The Mysql backup plug-in selects the general type of plug-in. The general type of plug-in and the component run or stop together, share the network of the current component, persistent files and environment variables, etc., suitable for similar Mysql backup plug-ins that need to be run when the component is running Ongoing tasks

The Mysql recovery plug-in chooses the initialization type plug-in. The difference between the initialization plug-in and other plug-ins is that it is a one-time task, and it must be started before the component is completed. From the Mysql recovery plug-in, we can see that Under what circumstances are applicable to the initialization plug-in, the tools used in the Mysql recovery plug-in will no longer be used after the task is performed. There is no need to put these in the component. Using the initialization plug-in can undoubtedly save resources and enhance Security of components

Mirror Production

For related Dockerfile and scripts, please refer to Kato official project addone-mysql-backup and addone-mysql- restore branch

Description of Main Process of Backup Plugin
if ["$1" == "bash" ]; then
    exec /bin/bash
fi

# installing mysql credentials if file does not exist
mysql_config="/root/.my.cnf"
if [! -f "$mysql_config" ]; then
    echo'[xtrabackup]'> /root/.my.cnf
    echo "user=$MYSQL_USER" >> /root/.my.cnf
    echo "password=$MYSQL_PASS" >> /root/.my.cnf
    echo "host=$MYSQL_HOST" >> /root/.my.cnf
    echo "port=$MYSQL_PORT" >> /root/.my.cnf
fi

echo 0> /tmp/backupnum #clear the number of backups

exec go-cron -s "${SCHEDULE:-@every 120s}" - /bin/bash -c "/bin/backup $BACKUP_TYPE" #Call the backup script regularly
#!/bin/bash
#Configure required environment variables
fullPath="/data/backup/full"
incrPath="/data/backup/incremental"
bakdate=`date +'%F-%H-%M'`
backupNum=`cat /tmp/backupnum`
BakBin="/usr/bin/xtrabackup \
--backup \
--throttle=1"

#Create backup directory path
[-d "$fullPath"] || mkdir -p "$fullPath"
[-d "$incrPath"] || mkdir -p "$incrPath"

# Full backup function
function hotbackup_full(){
  logfile=$1
  bakpath=$2
  $BakBin --target-dir=$bakpath> $logfile 2>&1
}

# Incremental backup function
function hotbackup_inc(){
  logfile=$1
  bakpath=$2
  backupcycle=$3
  backupnum=$4
  let j=backupcycle+1
  let i=backupnum%j
  if ["$i" = 0 ]; then
    main "full"
  elif ["$i" = 1 ]; then
    basefile=`ls $fullPath | grep -v log | sort -r | head -n 1`
    basepath="$fullPath/$basefile"
    $BakBin --target-dir=$bakpath --incremental-basedir $basepath> $logfile 2>&1
  else
    basefile=`ls $incrPath | grep -v log | sort -r | head -n 1`
    basepath="$incrPath/$basefile"
    $BakBin --target-dir=$bakpath --incremental-basedir $basepath> $logfile 2>&1
  fi
}

# Backup status pin reminder
function status(){
  if ["$1" == 0 ]; then
    status_info="Full Backup complete"
    curl'https://oapi.dingtalk.com/robot/send?access_token='$DINGTOKEN'' -H'Content-Type: application/json' -d'{"msgtype": "text","text": {"content": "Full Backup complete"}}'
  else
    status_info="Full Backup not complete"
    curl'https://oapi.dingtalk.com/robot/send?access_token='$DINGTOKEN'' -H'Content-Type: application/json' -d'{"msgtype": "text","text": {"content": "Full Backup not complete"}}'
  fi
  echo "$status_info - $DINGTOKEN"
}

#Clear expired backup files
function clean_timeout_file(){
  find ${fullPath}/ -mtime +$1 -name 2* -exec rm -rf {} \;
  find ${incrPath}/ -mtime +$1 -name 2* -exec rm -rf {} \;
}

# ============= Main =============
#According to the input value to determine the required call function
function main(){
  BackFile=`ls ${fullPath}/ | grep -v log | wc -l`
  if ["$BackFile" -ge 2 ];then
    clean_timeout_file $CLEAN_TIME
  fi
  
  if ["$1" == "full" ]; then
    hotbackup_full "${fullPath}/${bakdate}.log" "$fullPath/$bakdate"
    status $? >> ${fullPath}/dd.log
  elif ["$1" == "incremental" ];then
    hotbackup_inc "${incrPath}/${bakdate}.log" "$incrPath/$bakdate" "$BACKUP_CYCLE" "$2"
    status $? >> ${incrPath}/dd.log
  else
    echo'The variable BACKUP_TYPE can be set to "full or incremental"'
  fi
}

# ============= Run ==================
#Judge whether the trigger value is reasonable
if [! -z "$BACKUP_ENABLE" -a "$BACKUP_ENABLE" = "true" ];then
  main $1 $backupNum && ((backupNum+=1))
else
  echo ""
fi

echo $backupNum> /tmp/backupnum
Description of Main Process of Backup and Recovery Plug-in
#!/bin/bash
#Set the required environment variables
fullPath="/data/backup/full"
incrPath="/data/backup/incremental"
FullPreBin="/usr/bin/innobackupex \
--apply-log"
IncPreBin="/usr/bin/innobackupex \
--apply-log \
--redo-only"
ResBin="/usr/bin/innobackupex \
--copy-back \
--datadir=/var/lib/mysql"
fullfile=`ls $fullPath | grep -v log | sort -r | head -n 1`

#Determine the list of required recovery directories
function file_list(){
    echo $1> /tmp/all_back.txt
    ls $incrPath | grep -v log | sort -r >> /tmp/all_back.txt
    cat /tmp/all_back.txt | tr '''\n' | sort | grep -A $BACKUP_CYCLE $1> /tmp/restore_back.txt
}

#Full backup and recovery
function restore_full(){
    $PreBin $1
    $ResBin $1
}

#Incremental backup and recovery
function restore_inc(){
    resfullpath=`sed -nr '1p' /tmp/restore_back.txt`
    $IncPreBin $fullPath/$resfullpath
    if ["$1" -gt 2 ]; then
        sed -r '1d;$d' /tmp/restore_back.txt | while read resincfull ;do
            $IncPreBin $fullPath/$resfullpath --incremental-dir=$incrPath/$resincfull
        done
    fi
    /usr/bin/innobackupex --apply-log $fullPath/$resfullpath --incremental-dir=$incrPath/`sed -nr'$p' /tmp/restore_back.txt`
    $ResBin $fullPath/$resfullpath
}

#Main function, determine other functions that need to be called
function main(){
    file_list $fullfile
    
    FILE_NUM=`cat /tmp/restore_back.txt | wc -l`

    if ["$FILE_NUM" -eq 0 ];then 
        status="The backup file is missing, please check /data/back"
    elif ["$FILE_NUM" -gt "$BACKUP_CYCLE" ];then
        status="Backup file detection error, please check /data/back"
    elif ["$FILE_NUM" -eq 1 ];then
        restore_full $fullPath/$fullfile $FILE_NUM && status="Successfully restored data" || status="Restore failed, please check the backup file"
    else 
        restore_inc $FILE_NUM && status="Data restored successfully" || status="Failed to restore, please check the backup file"
    fi

    echo $status
}

#Judge whether the trigger value is reasonable
if [-z "$BACKUP_CYCLE" -o "$BACKUP_CYCLE" = "0" ];then
    echo "Please enter the correct BACKUP_CYCLE"
else
    main
fi
Mirror Production Summary

Because of the different plug-in types, the two are slightly different when making mirroring again. When the timed task is enabled when the plug-in is backed up, it will always be running, ensuring that the plug-in itself will not exit; while restoring the plug-in is only one-time execution Task. After the task is executed, the plug-in itself will exit. Components and other types of plug-ins will always be in Waiting state during this period. Only when the initialization type plug-in is executed, will it become Running state, which is required when making initialization plug-ins Pay special attention to this

Plug-in Production

Kato: Plug-in production of version 5.2

Log in to the console » enter the team view » plug-ins » new plug-in

Mysql Backup Plugin

Plug-in name: name it yourself

Installation source: Mirror or Dockerfile can be selected. To choose Dockerfile, you need to provide the source code address, code version, startup command, etc. Since I have already made the mirror, I can choose the mirror.

Plug-in category: general type, the reason has been explained in detail above

Mirror address: linux2573/xtrabackup:backup

Minimum memory: choose by yourself

Description: Support Mysql database full backup, incremental backup, timeout file cleaning, Dingding alarm

Mysql Recovery Plugin

Plug-in name: name it yourself

Installation source: Mirror or Dockerfile can be selected. To choose Dockerfile, you need to provide the source code address, code version, startup command, etc. Since I have already made the mirror, I can choose the mirror.

Plug-in category: initialization type, the reason has been explained in detail above

Mirror address: linux2573/xtrabackup:restore

Minimum memory: choose by yourself (recommend to choose 128M and above)

Description: Restore the data in the last backup cycle of Mysql database

Add Configuration

After the plug-in is built, for ease of use, optional configuration items need to be added to facilitate subsequent adjustments

Configuration instructions:

  • Configure group name

    The name of the configuration group can be named according to the actual situation

  • Depends on metadata type

    • not depend on

      Does not depend on component ports

    • Component port

      Depends on the component port where the plugin is installed

    • Downstream component port

      The port of the downstream component that depends on the component where the plugin is installed

  • Injection type

    • Environment variables

      Configuration items are written into the plug-in through environment variables, and the plug-in needs to be obtained through environment variables

    • Active discovery

      The plug-in needs to call the api actively to obtain the configuration, this method is suitable for dynamic configuration changes

  • Configuration items

    • Property name

      Configuration name, environment variable name

    • Agreement

      Protocol type of api when actively discovered

    • Property type

      • String

        Configure the default value

      • Single choice

        Configure default values ​​and options, the default value is one of the options

      • Multiple choice

        Configure default values ​​and options, the default value is multiple of the options

    • Can be modified

      Whether the configuration item can be modified

    • Brief introduction

      Explain the configuration items for others to use

Mysql Backup Plugin

Configuration group name: name it yourself

Depend on metadata type: not dependent

Injection type: environment variable

Configuration items:

Property nameProtocolProperty typeOptional valueCan be modifiedShort description
BACKUP_ENABLENoneSingle selectiontrue,false (default value is true)ModifiableWhether to enable backup
BACKUP_TYPENoneSingle selectionfull, incremental (default value is full)ModifiableBackup strategy is full backup or incremental backup
SCHEDULENoneStringNone (default value is 0 0 3 * *)ModifiableBackup interval, how often the backup task is executed
BACKUP_CYCLENoneStringNone (default value is 7)ModifiableBackup cycle, a full backup is performed every week, and the rest are incremental backups
CLEAN_TIMENoneStringNone (default value is 30)ModifiableBackup file retention time (days), backup files beyond this time will be cleaned up
DINGTOKENNoneStringNone (the default value is empty)ModifiableDingding alarm TOKEBN value
Mysql Recovery Plugin

Configuration group name: name it yourself

Depend on metadata type: not dependent

Injection type: environment variable

Configuration items:

Property nameProtocolProperty typeOptional valueCan be modifiedShort description
BACKUP_CYCLENoneStringNone (default value is 7)ModifiableBackup cycle, used to determine the range of files to be restored, and use the default value when restoring a full backup
Installation and Use

After adding the configuration items, you can use the plug-in. Let’s explain how to use these two plug-ins.

Mysql Backup Plugin

Currently supports Mysql version below 8.0, 8.0 and above are not tested

  • You need to add environment variables MYSQL_HOST, MYSQL_PORT, MYSQL_USER, MYSQL_PASS to the environment variables or dependencies of the database
  • Activate the component in the component that needs to be installed
  • Modify the configuration items to their expected goals and enable the plug-in
  • Persist the /data directory
  • Restart component
Mysql Recovery Plugin

The restored data database needs to be consistent with the version of the backup data database. It is recommended to use it with the Mysql backup plug-in. If you use the xtrabackup tool to back up by yourself, please refer to the data format backed up by the Mysql backup plug-in in the same path.

  • Activate the component in the component that needs to be installed
  • Modify the configuration items to their expected goals and enable the plug-in
  • Make sure that the /data directory has the required data
  • Restart component
  • Uninstall or disable the plug-in after data recovery is complete