MariaDB 10.2.26 xtrabackup Important Steps Record

1. Create test case library tables

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.2.26-MariaDB-log MariaDB Server

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database full charset=utf8;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create table full.incr(id int not null auto_increment primary key,tag varchar(256)) charset=utf8;
Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)]> show table from full;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from full' at line 1
MariaDB [(none)]> show tables from full;
+----------------+
| Tables_in_full |
+----------------+
| incr |
+----------------+
1 row in set (0.00 sec)

MariaDB [(none)]> select * from full.incr;
Empty set (0.00 sec)

MariaDB [(none)]> exit
Bye

2. Perform one full run

[root@rec tmp]# innobackupex --user=root --no-timestamp /tmp/fullbak

3. Add Incremental Data Once

MariaDB [(none)]> insert into full.incr(tag) values (after the first addition);
Query OK, 1 row affected (0.01 sec)

4. Back up incremental data once

[root@rec tmp]# innobackupex --user=root --no-timestamp --parallel=4 --incremental-basedir=/tmp/fullbak/ --incremental /tmp/incr/incr1

5. Add Secondary Incremental Data

MariaDB [(none)]> insert into full.incr(tag) values (after the second backup);
Query OK, 1 row affected (0.01 sec)

6. Back up secondary incremental data

[root@rec tmp]# innobackupex --user=root --no-timestamp --parallel=4 --incremental-basedir=/tmp/incr/incr1/ --incremental /tmp/incr/incr2

7. Add three incremental data

MariaDB [(none)]> insert into full.incr(tag) values (after the third supplement);
Query OK, 1 row affected (0.30 sec)

8. Back up incremental data three times

[root@rec tmp]# innobackupex --user=root --no-timestamp --parallel=4 --incremental-basedir=/tmp/incr/incr2/ --incremental /tmp/incr/incr3

9. Add Four Incremental Data

MariaDB [(none)]> insert into full.incr(tag) values (after the fourth backup);
Query OK, 1 row affected (0.30 sec)

10. Back up incremental data four times

[root@rec tmp]# innobackupex --user=root --no-timestamp --parallel=4 --incremental-basedir=/tmp/incr/incr3/ --incremental /tmp/incr/incr4

11. Readiness for Recovery

[root@rec tmp]# innobackupex --apply-log --redo-only /tmp/fullbak/

12. Recovery preparation for first replenishment

[root@rec tmp]# innobackupex --apply-log --redo-only /tmp/fullbak/ --incremental-dir=/tmp/incr/incr1

13. Resume preparation for second replenishment

[root@rec tmp]# innobackupex --apply-log --redo-only /tmp/fullbak/ --incremental-dir=/tmp/incr/incr2

14. Recovery is ready for the third replenishment, where all incremental backup data has not been recovered

[root@rec tmp]# innobackupex --apply-log --redo-only /tmp/fullbak/ --incremental-dir=/tmp/incr/incr3

15. Close db, delete existing databases for recovery testing, remember not to delete database related data directly from the reproduction environment

[root@rec tmp]# /etc/init.d/mysql stop
Stopping mysql (via systemctl): [OK]
[root@rec tmp]# cd /var/lib/
[root@rec lib]# rm -rf mysql
[root@rec lib]# mkdir -p mysql

16. Recovery--copy-back

[root@rec lib]# innobackupex --defaults-file=/etc/my.cnf --copy-back /tmp/fullbak/
190826 14:37:24 completed OK!

17. Start observation

[root@rec lib]# chown mysql.mysql -R mysql
[root@rec lib]# /etc/init.d/mysql start
Starting mysql (via systemctl): [OK]
[root@rec lib]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.2.26-MariaDB-log MariaDB Server

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select * from full.incr;
+----+------------------+
| id | tag |
+----+------------------+
| 1 | After the first supplement |
| 2 | After the 2nd Supplement |
| 3 | After the third supplement |
+----+------------------+
3 rows in set (0.00 sec)

MariaDB [(none)]> exit
Bye

18. Backup script

#backup.sh
#!/bin/sh
#on xtrabackup 2.2.8
#It checks for a full backup the first time it is executed, otherwise create a full library backup first
#When you run it again, it incrementally backs up based on previous full or incremental backups, as set in the script

INNOBACKUPEX_PATH=/usr/bin/innobackupex  #Command for INNOBACKUPEX
INNOBACKUPEXFULL=/usr/bin/innobackupex  #Command Path for INNOBACKUPEX

#mysql target server and user name and password
MYSQL_CMD="--host=localhost --user=root --password=20190826 --port=3306"

MYSQL_UP=" --user=root --password='20190826' --port=3306 "  #Username and password for mysqladmin

TMPLOG="/tmp/innobackupex.$$.log"

MY_CNF=/etc/my.cnf.d/wsrep.cnf #Configuration file for mysql

MYSQL=/usr/bin/mysql

MYSQL_ADMIN=/usr/bin/mysqladmin

BACKUP_DIR=/backup # Home directory for backup

FULLBACKUP_DIR=$BACKUP_DIR/full # Directory for full library backup

INCRBACKUP_DIR=$BACKUP_DIR/incre # Directory for incremental backup

FULLBACKUP_INTERVAL=604800 # Period between full library backups, time: seconds

KEEP_FULLBACKUP=1 # Keep at least a few full library backups

logfiledate=/backup/backup.`date +%Y%m%d%H%M`.txt

#start time
STARTED_TIME=`date +%s`

#############################################################################

# Show error and exit

#############################################################################

error()
{
    echo "$1" 1>&2
    exit 1
}

# Check execution environment

if [ ! -x $INNOBACKUPEXFULL ]; then
error "$INNOBACKUPEXFULL Not installed or linked to/usr/bin."
fi

if [ ! -d $BACKUP_DIR ]; then
error "Backup Target Folder:$BACKUP_DIR Non-existent."
fi

mysql_status=`netstat -nl | awk 'NR>2{if ($4 ~ /.*:3306/) {print "Yes";exit 0}}'`

if [ "$mysql_status" != "Yes" ];then
    error "MySQL No Run Started."
fi

if ! `echo 'exit' | $MYSQL -s $MYSQL_CMD` ; then
error "Incorrect database username or password provided!"
fi

# Header information for backup

echo "----------------------------"
echo
echo "$0: MySQL Backup script"
echo "Start at: `date +%F' '%T' '%w`"
echo

#New directory for full and differential backups

mkdir -p $FULLBACKUP_DIR
mkdir -p $INCRBACKUP_DIR

#Find the latest full backup
LATEST_FULL_BACKUP=`find $FULLBACKUP_DIR -mindepth 1 -maxdepth 1 -type d -printf "%P\n" | sort -nr | head -1`

# Find the latest modified backup time

LATEST_FULL_BACKUP_CREATED_TIME=`stat -c %Y $FULLBACKUP_DIR/$LATEST_FULL_BACKUP`

#Perform full backup if full backup is effective for incremental backup otherwise
if [ "$LATEST_FULL_BACKUP" -a `expr $LATEST_FULL_BACKUP_CREATED_TIME + $FULLBACKUP_INTERVAL + 5` -ge $STARTED_TIME ] ; then 
    # If the latest full-time has not expired, create a new directory under the incremental backup directory named after the latest full-file name
    echo -e "Full backup $LATEST_FULL_BACKUP Not expired,Will be based on $LATEST_FULL_BACKUP Name as Incremental Backup Base Directory Name"
    echo "                     "
    NEW_INCRDIR=$INCRBACKUP_DIR/$LATEST_FULL_BACKUP
    mkdir -p $NEW_INCRDIR

    # Find out if the latest incremental backup exists. Specify a backup path as the basis for the incremental backup
    LATEST_INCR_BACKUP=`find $NEW_INCRDIR -mindepth 1 -maxdepth 1 -type d -printf "%P\n"  | sort -nr | head -1`
        if [ ! $LATEST_INCR_BACKUP ] ; then
            INCRBASEDIR=$FULLBACKUP_DIR/$LATEST_FULL_BACKUP
            echo -e "Incremental backup will be $INCRBASEDIR As backup base directory"
            echo "                     "
        else
            INCRBASEDIR=$INCRBACKUP_DIR/${LATEST_FULL_BACKUP}/${LATEST_INCR_BACKUP}
            echo -e "Incremental backup will be $INCRBASEDIR As backup base directory"
            echo "                     "
        fi

    echo "Use $INCRBASEDIR Base directory for this incremental backup."
    $INNOBACKUPEXFULL --defaults-file=$MY_CNF --galera-info --kill-long-query-type=select --use-memory=4G $MYSQL_CMD --incremental $NEW_INCRDIR --incremental-basedir $INCRBASEDIR > $TMPLOG 2>&1

    #Keep a detailed log of the backup

    cat $TMPLOG>$logfiledate

    if [ -z "`tail -1 $TMPLOG | grep 'completed OK!'`" ] ; then
    echo "$INNOBACKUPEX Command execution failed:"; echo
    echo -e "---------- $INNOBACKUPEX_PATH error ----------"
    cat $TMPLOG
    /bin/rm -f $TMPLOG
    exit 1
    fi

    THISBACKUP=`awk -- "/Backup created in directory/ { split( \\\$0, p, \"'\" ) ; print p[2] }" $TMPLOG`
    /bin/rm -f $TMPLOG

    echo -n "Database successfully backed up to:$THISBACKUP"
    echo

    # Prompt for starting point of backup files that should be retained

    LATEST_FULL_BACKUP=`find $FULLBACKUP_DIR -mindepth 1 -maxdepth 1 -type d -printf "%P\n" | sort -nr | head -1`

    NEW_INCRDIR=$INCRBACKUP_DIR/$LATEST_FULL_BACKUP

    LATEST_INCR_BACKUP=`find $NEW_INCRDIR -mindepth 1 -maxdepth 1 -type d -printf "%P\n"  | sort -nr | head -1`

    RES_FULL_BACKUP=${FULLBACKUP_DIR}/${LATEST_FULL_BACKUP}

    RES_INCRE_BACKUP=`dirname ${INCRBACKUP_DIR}/${LATEST_FULL_BACKUP}/${LATEST_INCR_BACKUP}`

    echo
    echo -e '\e[31m NOTE:---------------------------------------------------------------------------------.\e[m' #gules
    echo -e "Must be retained $KEEP_FULLBACKUP Full ready ready ${RES_FULL_BACKUP}and ${RES_INCRE_BACKUP}All incremental backups in the directory."
    echo -e '\e[31m NOTE:---------------------------------------------------------------------------------.\e[m' #gules
    echo

else
    echo  "*********************************"
    echo -e "find expire backup file...........waiting........."
    echo -e "Find expired full files and delete them">>$logfiledate
    for efile in $(/usr/bin/find $FULLBACKUP_DIR/ -mtime +4)
    do
        if [ -d ${efile} ]; then
        /bin/rm -rf "${efile}"
        echo -e "Delete expired full-time files:${efile}" >>$logfiledate
        elif [ -f ${efile} ]; then
        /bin/rm -rf "${efile}"
        echo -e "Delete expired full-time files:${efile}" >>$logfiledate
        fi;

    done

    if [ $? -eq "0" ];then
        echo
        echo -e "No expired full-fledged files were found that could be deleted"
    fi
    echo -e "Performing a new full backup...One moment please..."
    echo  "*********************************"
    $INNOBACKUPEXFULL  --defaults-file=$MY_CNF --galera-info --kill-long-query-type=select --use-memory=4G  $MYSQL_CMD $FULLBACKUP_DIR > $TMPLOG 2>&1
    #Keep a detailed log of the backup

    cat $TMPLOG>$logfiledate

    if [ -z "`tail -1 $TMPLOG | grep 'completed OK!'`" ] ; then
    echo "$INNOBACKUPEX Command execution failed:"; echo
    echo -e "---------- $INNOBACKUPEX_PATH error ----------"
    cat $TMPLOG
    /bin/rm -f $TMPLOG
    exit 1
    fi

    THISBACKUP=`awk -- "/Backup created in directory/ { split( \\\$0, p, \"'\" ) ; print p[2] }" $TMPLOG`
    /bin/rm -f $TMPLOG

    echo -n "Database successfully backed up to:$THISBACKUP"
    echo

    # Prompt for starting point of backup files that should be retained

    LATEST_FULL_BACKUP=`find $FULLBACKUP_DIR -mindepth 1 -maxdepth 1 -type d -printf "%P\n" | sort -nr | head -1`

    RES_FULL_BACKUP=${FULLBACKUP_DIR}/${LATEST_FULL_BACKUP}

    echo
    echo -e '\e[31m NOTE:---------------------------------------------------------------------------------.\e[m' #gules
    echo -e "No Incremental Backup,Must be retained $KEEP_FULLBACKUP Full ready ready ${RES_FULL_BACKUP}."
    echo -e '\e[31m NOTE:---------------------------------------------------------------------------------.\e[m' #gules
    echo

fi

#Delete expired full set

echo -e "find expire backup file...........waiting........."
echo -e "Find expired full files and delete them">>$logfiledate
for efile in $(/usr/bin/find $FULLBACKUP_DIR/ -mtime +7)
do
    if [ -d ${efile} ]; then
    /bin/rm -rf "${efile}"
    echo -e "Delete expired full-time files:${efile}" >>$logfiledate
    elif [ -f ${efile} ]; then
    /bin/rm -rf "${efile}"
    echo -e "Delete expired full-time files:${efile}" >>$logfiledate
    fi;

done

if [ $? -eq "0" ];then
echo
echo -e "No expired full-fledged files were found that could be deleted"
fi
#Supplementary
echo -e "Find expired supplements and delete them" >>$logfiledate
for exfile in $(/usr/bin/find $INCRBACKUP_DIR/ -mtime +7)
do
    if [ -d ${exfile} ]; then
    /bin/rm -rf "${exfile}"
    echo -e "Delete Expired Additional Files:${exfile}" >>$logfiledate
    elif [ -f ${exfile} ]; then
    /bin/rm -rf "${exfile}"
    echo -e "Delete Expired Additional Files:${exfile}" >>$logfiledate
    fi;
done

if [ $? -eq 0 ]
then
    echo
    echo -e "No expired supplement file was found to delete"
fi

echo
echo "Completed on: `date +%F' '%T' '%w`"

exit 0

Problem encountered

1. Mariadb opened innodb_safe_truncate with the following error
InnoDB: Unsupported redo log format. The redo log was created with MariaDB 10.2.26. Please follow the instructions at http://dev.mysql.com/doc/refman/5.7/en/upgrading-downgrading.html
Close it
[root@rec mnt]# cat /etc/my.cnf.d/server.cnf |grep safe
innodb_safe_truncate=0
[root@rec mnt]#

2. Long queries cause blocking, causing delays in master-slave replication
--kill-long-queries-timeout=120 ,--kill-long-query-type=select

3. Common parameters of innobackupex:

--user=USER Specifies the backup user, if not the current system user
 --password=PASSWD Specifies Backup User Password
 --port=PORT Specifies the database port
 --defaults-group=GROUP-NAME used when multiple instances
 --host=HOST Specifies the backup host for remote database servers
 --apply-log rollback log
 --database specifies the database to be backed up, with multiple databases separated by spaces
 --defaults-file Specifies the configuration file for mysql
 --copy-back Copies the backup data back to its original location
 --incremental incremental backup followed by the path to incremental backup
 --incremental-basedir=DIRECTORY incremental backup using directory pointing to the last incremental backup
 --incremental-dir=DIRECTORY Incremental Backup Restore is used to merge incremental backups to full, specifying full path
 --redo-only merges incremental backups
 --rsync speeds up local file transfer for the non-InnoDB database engine.Not shared with--stream
 --backup files generated by no-timestamp do not have a timestamp as a directory.
The--kill-long-queries-timeout parameter is used to create a thread inside xtrabackup to connect to the database to execute show when a global read lock is acquired after Xtrabackup executes FLUSH TABLES WITH READ LOCK and if the transaction being executed will block the wait, the kill-long-queries-timeout parameter is not 0Full processlist, kills threads if TIME exceeds kill-long-queries-timeout
 --kill-long-query-type sets the sql type that can be killed out.When the official document describes the kill-long-query-type default value, all, that is, all statements are killed.However, in use, it is found that the parameter does not work when kill-long-queries-timeout is set only and kill-long-query-type is not set!
--defaults-extra-file This option specifies from which additional file MySQL configuration is read before the standard defaults-file and must be in the location of the first option on the command line.A configuration file typically used to store the user name and password of a backup user.
--The source directory of the dataDir backup, the data directory of the mysql instance.Read from my.cnf or specify from the command line.
--slave-info This option means to use when backing up slaves, print out the master's name and binlog pos, and also change this information.The master's command is written to the xtrabackup_slave_info file.
--safe-slave-backup This option means to ensure a consistent replication state. This option stops the SQL thread and waits until the slave_open_temp_tables in show status are 0 to start the backup. If the temporary table is not opened, the bakcup will start immediately, otherwise the SQL thread will close until the temporary table is not opened.If slave_open_temp_tables are not zero after--safe-slave-backup-timeount (default 300 seconds), the SQL thread from the library restarts when the backup is complete.
--ftwrl-wait-query-type This option means that the query is allowed to complete before a global lock is acquired. The default is ALL, optional update.
--history This option indicates that the backup history of percona server is in the percona_schema.xtrabackup_history table.
--backup creates a backup and puts it in the--target-dir directory.
--parallel specifies the number of processes that copy multiple data files concurrently at backup time, defaulting to 1.
--compress This option represents a backup of compressed innodb data files.
--compress-threads This option represents the number of parallel compression worker threads.
--stream This option represents the format of streaming backups, which are sent to STDOUT in the specified format after backup is complete. Currently, only tar and xbstream are supported.
--encrypt This option indicates that the backup of the innodb data file is encrypted by the ENCRYPTION_ALGORITHM algorithm, currently supported by ASE128,AES192,AES256.
--encrypt-threads This option represents the number of worker threads encrypted in parallel.
--encryption-key-file This option means that the file must be a simple binary or text file, and the encryption key can be generated from the following command line command: OpenSSL rand-base64 24.
--galera-info This option means that a file xtrabackup_galera_info containing the state of the local node at the time the backup was created was generated. This option only applies to backup PXC.

Keywords: MySQL MariaDB Database SQL

Added by MrOnline on Tue, 27 Aug 2019 00:13:14 +0300

Programming VIP

MariaDB 10.2.26 xtrabackup Important Steps Record

1. Create test case library tables

2. Perform one full run

3. Add Incremental Data Once

4. Back up incremental data once

5. Add Secondary Incremental Data

6. Back up secondary incremental data

7. Add three incremental data

8. Back up incremental data three times

9. Add Four Incremental Data

10. Back up incremental data four times

11. Readiness for Recovery

12. Recovery preparation for first replenishment

13. Resume preparation for second replenishment

14. Recovery is ready for the third replenishment, where all incremental backup data has not been recovered

15. Close db, delete existing databases for recovery testing, remember not to delete database related data directly from the reproduction environment

16. Recovery--copy-back

17. Start observation

18. Backup script

Problem encountered

Popular Keywords