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

Source: Internet
Author: User
Tags percona

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.