xtrabackup backup and recovery data script

This script is used to back up and restore MySQL databases.

Summarize the two pits of xtrabackup backup:

1. In the process of data recovery, if there is an error in the middle of the process, the data will be destroyed and it is difficult to recover later.

2. In the recovery process, if the version is too low, it will be stuck waiting for the full amount of data to be prepared, and it is unable to proceed to the next step.

If I use xtrabackup 2.4.4 for backup, the 2.4.5 recovery will be stuck in the front. I heard it's a bug. Later, when it was changed to 2.4.13, the time would be much faster.

#author: chentufeng
#create time: 2019 05 07
#mail: 15219234405@139.com
#version: 1.0
incre_time=`date +%Y%m%d`
current_month=`date +%Y%m`
full_amount_day=`date +%d`
three_month_ago=`date -d "-3 month" +%Y%m`
#mysql configuration file
#Define the backup directory. Even if it is not created, it will be created automatically during backup
#Define mysql login information
#Full backup directory
#Incremental backup directory
#Determine whether the directory exists or not. If it does not exist, create it
[ ! -d ${bak_month_dir} ] && mkdir -p ${mysql_bak}/${current_month}
#Full backup on the 1st of every month
if [ ${full_amount_day} -eq 01 ];then
innobackupex --defaults-file=${mysql_cfg} --user="${mysql_user}" --password=${mysql_pwd} --parallel=8 --slave-info --no-timestamp ${full_amount_dir}
if [ $? -eq 0 ];then
echo "Full amount backup sucessful!"
#Delete backup files three months ago
rm -rf ${mysql_bak}/${three_month_ago}
echo "Full amount backup failure!!!!!!!!!!!"
#Because the data volume is not large, incremental backup should be done every day on the basis of full backup at the beginning of the month. If there is any problem, only one recovery is needed
innobackupex --defaults-file=${mysql_cfg} --user="${mysql_user}" --password=${mysql_pwd} --no-timestamp --incremental-basedir=${full_amount_dir} --incremental ${incre_dir}
[ $? -eq 0 ] && echo "Incremental backup sucessful!" || echo "Incremental backup failure!!!!!!!!!!!"

[Data recovery description]
//Data recovery steps: (Note: the steps are only applicable to the recovery of the backup script)
1,establish data Table of contents, according to my.cnf Configuration to create
1)Full backup preparation
#innobackupex --defaults-file=/etc/my.cnf --apply-log --redo-only Full backup directory( full_amount_dir) # Specify the full directory, - redo only to roll forward the committed transactions, only the last full recovery does not need to add this parameter
2)Restore incremental backup data preparation (Note: the directory of full backup must be specified, otherwise only the data of full backup can be recovered)
#innobackupex --defaults-file=/etc/ my.cnf  --Apply log -- redo only full backup directory -- incremental dir = last incremental backup directory (inch_ dir)
# --Incremental dir specifies the last additional directory (absolute path)

3,again prepare(Prepare) full backup
# innobackupex --defaults-file=/etc/ my.cnf  --Apply log full backup directory_ amount_ dir)
#Note: you do not need to add the -- redo only parameter

4,The final step is to copy the consolidated full file at my.cnf in datadir Specify path
#innobackupex --defaults-file=/etc/ my.cnf  --Copy back full backup directory_ amount_ dir)
5,to data Catalogue assignment mysql jurisdiction:
#chown -R  mysql.mysql  /Var / lib / MySQL (view my.cnf Determine the data directory)

6,start-up mysql
#/etc/init.d/mysql.server start



Tags: MySQL

Posted on Thu, 04 Jun 2020 10:50:08 -0400 by juschillin