MySQL (mariadb) timed automatic hot backup + incremental + remote backup script

Pros: Hot backup, no impact on business, incremental backup, remote backup.

Purpose: Automatically package backup to remote 10.0.0.111 backup server

Premise:

1. Installing Xtrabackup

Yum Install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

Yum Install Percona-xtrabackup-22

2. Create a Directory

Mkdir-p/data/backup

Mkdir-p/data/backuptar/

3. Add the MySQL public key to the backup server (10.0.0.111), can password-free remote login.

4. Create a backup user

GRANT reload,lock tables,replication CLIENT on * * to ' bkuser ' @ ' localhost ' identified by ' passwd ';

FLUSH privileges;

------------------------

#!/bin/bash

Backdir=/data/backup

Backupbin=/usr/bin

Begintime= ' date + '%y-%m-%d%h:%m:%s '

Format_time= ' date + '%y-%m-%d_%h_%m_%s '

time_cost= $backdir/xtrabackup_time.txt

week= ' Date +%w '

User_name=bkuser

Password= "passwd"

File_cnf=/etc/my.cnf.d/wsrep.cnf

backtar=/data/backuptar/

Remote_host= "10.0.0.111"

Remote_dir=/data/mysqlbak_remote_23

Remote_user= "Wanzi"

out_log= $backdir/xtrabackup_log_$format_time

If [-d ' $backdir/rec5 "];then

echo "Start packing up to 5 times"

CD $backdir

TAR-ZCF lastweek.gz./*

MV lastweek.gz $backtar/$format _time.lastweek.gz

echo "Complete 5 Backup Packages"

CD $backtar

RM-RF ' ls |grep-v ' $format _time.lastweek.gz "'

echo "Delete Local last backup complete"

echo "Start deleting remote backup ..."

SSH [email protected] $remote _host "CD $remote _DIR;RM-RF./*"

echo "Delete Remote redundant backup complete"

echo "Start copying local backup to remote server ..."

SCP $backtar/$format _time.lastweek.gz $remote _host:/$remote _dir

echo "Remote backup Complete"

RM-RF $backdir

mkdir $backdir

Fi

#fullbackup

if [!-d "$backdir/fullbackup"];then

echo "Start full backup at $begintime" >> $time _cost

$backupbin/innobackupex--defaults-file= $file _cnf--user= $user _name--password= $password--no-timestamp-- Slave-info $backdir/fullbackup 1> $out _log 2>&1

echo "Complete fully prepared"

elif [!-d "$backdir/rec0"];then

echo "# # # #start 0 Incremental backup at $BEGINTIME to directory rec0" >> $time _cost

$backupbin/innobackupex--defaults-file= $file _cnf--no-timestamp--user= $user _name--password= $password-- Slave-info--incremental--incremental-basedir= $bac

Kdir/fullbackup $backdir/rec0 1> $out _log 2>&1

echo "Completing the No. 0 Incremental backup"

elif [!-d "$backdir/rec1"];then

echo "# # # #start 1 Incremental backup at $BEGINTIME to directory Rec1" >> $time _cost

$backupbin/innobackupex--defaults-file= $file _cnf--no-timestamp--user= $user _name--password= $password-- Slave-info--incremental--incremental-basedir= $bac

KDIR/REC0 $backdir/rec1 1> $out _log 2>&1

echo "Completing the 1th incremental backup"

elif [!-d "$backdir/rec2"];then

echo "# # # #start 2 Incremental backup at $BEGINTIME to directory rec2" >> $time _cost

$backupbin/innobackupex--defaults-file= $file _cnf--no-timestamp--user= $user _name--password= $password-- Slave-info--incremental--incremental-basedir= $bac

KDIR/REC1 $backdir/rec2 1> $out _log 2>&1

echo "Completing the 2nd incremental backup"

elif [!-d "$backdir/rec3"];then

echo "# # # #start 3 Incremental backup at $BEGINTIME to directory rec3" >> $time _cost

$backupbin/innobackupex--defaults-file= $file _cnf--no-timestamp--user= $user _name--password= $password-- Slave-info--incremental--incremental-basedir= $bac

KDIR/REC2 $backdir/rec3 1> $out _log 2>&1

echo "Completing the 3rd incremental backup"

elif [!-d "$backdir/rec4"];then

echo "# # # #start 4 Incremental backup at $BEGINTIME to directory Rec4" >> $time _cost

$backupbin/innobackupex--defaults-file= $file _cnf--no-timestamp--user= $user _name--password= $password-- Slave-info--incremental--incremental-basedir= $bac

KDIR/REC3 $backdir/rec4 1> $out _log 2>&1

echo "Completing the 4th incremental backup"

elif [!-d "$backdir/rec5"];then

echo "# # # #start 5 Incremental backup at $BEGINTIME to directory Rec5" >> $time _cost

$backupbin/innobackupex--defaults-file= $file _cnf--no-timestamp--user= $user _name--password= $password-- Slave-info--incremental--incremental-basedir= $bac

KDIR/REC4 $backdir/rec5 1> $out _log 2>&1

echo "Completing the 5th incremental backup"

Fi

Endtime= ' date + '%y-%m-%d%h:%m:%s '

Begin_data= ' date-d ' $BEGINTIME ' +%s '

End_data= ' date-d ' $ENDTIME ' +%s '

Spendtime= ' expr $end _data-$begin _data '

echo "It takes $spendtime sec for packing the data directory" >> $time _cost

echo "Backup end ..." ' Date '

This article is from the "Jorden blog" blog, please be sure to keep this source http://jin544642965.blog.51cto.com/1389736/1769710

MySQL (mariadb) timed automatic hot backup + incremental + remote backup script