Two mysql backup scripts

The importance of data backup does not need to be explained in detail. We need at least two types of backup: one logical backup (mysqldump generates SQL files) and the other physical backup (xtrabackup can be done well ). Logical backup can provide more fine-grained recovery and comparison when a problem occurs. Physical backup can recover data within the entire database or increase the efficiency of database operations.

The importance of data backup does not need to be explained in detail. We need at least two types of backup: one logical backup (mysqldump generates SQL files) and the other physical backup (xtrabackup can be done well ). Logical backup can provide more fine-grained recovery and comparison when a problem occurs. Physical backup can recover data within the entire database or increase the efficiency of database operations.

The importance of data backup does not need to be explained in detail. We need at least two types of backup: one logical backup (mysqldump generates SQL files) and the other physical backup (xtrabackup can be done well ). Logical backup can provide more fine-grained recovery and comparison when a problem occurs. Physical backup can recover data throughout the entire database or increase database efficiency.

First, let's look at the logic backup script, which is relatively simple.

#! /Bin/sh ### run once a day ### Define user Password Backup Directory and other information user = mysqldumppsd = mysqldumpbackup_base =/data/mysql_backupdate = 'date + % Y % m % d 'Old _ date = 'date + % Y % m % d-30days '### Number of days saved ### obtain the Database Name, exclude databases that do not need to be backed up in 'mysql-u $ user-p $ psd-s-N-e "show databases" | grep-v-E "(test | percona | information_schema | cece_schema) "'Do ### create a backup directory if [! -D $ backup_base/$ db]; then mkdir $ backup_base/$ db fi cd $ backup_base/$ db chattr-I. /* rm-f $ old_date. SQL * &>/dev/null # Delete mysqldump-u $ user-p $ psd -- events -- routines $ db before 30 days | gzip> logs # # backup & compression chattr + I. /* ### Add a property done that prevents accidental deletion.

Next we will look at the xtrabackup-based physical (full-database) backup script. This is more interesting than the above script (a rule is followed when the backup is generated, and then logic judgment is added to the recovery script to achieve one-click recovery at any time)

The full set of functions requires three scripts: innobackupex_all.sh, innobackupex_increment.sh, and innobackupex_restore.sh.

We know that xtrabackup can back up full data for the first time, and then set up Incremental backup. Linux crontab uses numbers 0-6 to indicate "Sunday"-"Saturday ". Therefore, I use one week as the cycle: Full backup is performed on Sunday and stored in the 0 directory; backup is added from Monday to Saturday, and stored in the 1-6 directory; the directory is cleared on the next Sunday and then completely backup.

The preceding directory names such as 0-6 are used to store backup data to make the restoration script innobackupex_restore.sh easier to process.The recovery process of xtrabackup can be correctly executed no matter the day of the week.

According to percona official documentation. Xtrabackup backup requires at least the following permissions: grant reload, SUPER, lock tables, replication client, create tablespace on *. * TO 'xtrabackup' @ 'localhost' identified by xxxx

The scheduled task settings are as follows:

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151111/120645GC-0.jpg "title =" 456.png" alt = "wKiom1YjllLjfwWHAABUb2Zxa3Q422.jpg"/>

Full backup script innobackupex_all.sh

#! /Bin/sh ### perform full backup base_dir =/ljk/datarm-rf $ base_dir/* 2> $ base_dir/all at every Sunday. log ### clear base_dir, perform full backup ### back up and record all logs under base_dir. log/usr/bin/innobackupex -- user = xtrabackup -- password = xtrabackup $ base_dir 2> $ base_dir/all. logcd $ base_dir # rename 'full backup directory' named at the current time to '0' mv 20*0 2> $ base_dir/all. log

Add backup script innobackupex_increment.sh

#! /Bin/sh ### week 1 -- Incremental Backup base_dir =/ljk/datatoday = 'date + % U' yesterday = 'expr $ today-1 'echo -e "\ n ======================================== \ n "> $ base_dir/increment. log ### Add a backup log to $ base_dir/increment. log/usr/bin/innobackupex -- user = xtrabackup -- password = xtrabackup -- incremental $ base_dir -- incremental-basedir = $ base_dir/$ yesterday 2> $ base_dir/increment. logcd $ base_dir # rename the backup file. 0 1 2 3 4 5 6 represents the mv from Sunday to Saturday 20 * $ today 2> $ base_dir/increment. log

Restore script innobackupex_restore.sh

#! /Bin/sh ### function check {if [$? -Ne 0]; then echo-e "\ e [1; 31m \ n $1 exec failed, please check it! \ E [0 m \ n "echo" $1 failed, please check it! "Sleep 1 exit-1 fi} base_dir =/ljk/databackup_dir = 'LS-l $ base_dir | grep-e" ^ d. * "| awk '{print $ NF} ''echo-e" the backup dir is: $ backup_dir \ n "sleep 1 ### start to recover cd $ base_direcho-e" ------ preparation stage 0 ------ \ n "sleep 1/usr/bin/innobackupex -- apply-log -- redo -only $ base_dir/0 check "preparation stage 0 "; ### execute the restoration preparation dir_num = 'LS-l $ base_dir for other directories except 0 and the last directory | grep-e "^ d. * "| wc-l' # several backup directories for I in 'seq 1 $ ($ dir_num-2 )) 'Do echo-e "------ preparation phase $ I ------ \ n" sleep 1/usr/bin/innobackupex -- apply-log -- redo-only $ base_dir/0 -- incremental-dir = $ base_dir/$ I check "preparation phase $ I "; done ### last Incremental Backup echo-e "------ preparation phase $ ($ dir_num-1 )) ------ \ n "sleep 1/usr/bin/innobackupex -- apply-log $ base_dir/0 -- incremental-dir = $ base_dir/$ ($ dir_num-1 )) check "preparation phase $ ($ dir_num-1 ))"; ### the above steps apply the changes recorded in all incremental backups to the initial full backup echo-e "------ apply all changes to $ base_dir/0 ------ \ n"/usr/ bin/innobackupex -- apply-log $ base_dir/0 check "apply all changes to $ base_dir/0 "; ### test the data back to the data directory echo-e "------ test the processed data back to the data directory ------ \ n"/usr/bin/innobackupex -- copy-back $ base_dir/ 0 check "copy-back ";

Physical Backup directory structure

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151111/1206452Q5-1.jpg "title =" 456.png" alt = "wKioL1Yl0YuS9eaIAADKyyzqV00547.jpg"/>