MySQL Full library backup/incremental backup script

Source: Internet
Author: User
Tags mysql backup

http://hongge.blog.51cto.com/

Backups of MySQL databases in a production environment are recurring operations, so it is common to write script implementations that periodically execute backup scripts through Crond scheduled tasks

mysqldump Backup scenarios:

Sunday 1 o'clock in the morning full-Library backup

From Monday to Saturday, incremental backups every 4 hours

Set up crontab tasks, execute backup scripts daily

# crontab–e

#每个星期日凌晨1:00 Perform a full backup script

0 1 * * 0/root/mysqlfullbackup.sh >/dev/null 2>&1

#周一到周六每隔4个小时增量备份一次

0 */4 * * 1-6/root/mysqldailybackup.sh >/dev/null 2>&1

mysqlfullbackup.sh Script content:

[email protected] ~]# cat mysqlfullbackup.sh

#!/bin/sh

# Name:mysqlFullBackup.sh

# define Database Directory

Mysqldir=/usr/local/mysql

# define the user name and password used to back up the database

User=root

userpwd=123456

dbname=test_db

# define Backup Directory

Databackupdir=/opt/mysqlbackup

[!-D $databackupdir] && mkdir $databackupdir

# define message body file

emailfile= $databackupdir/email.txt

# define Email Address

[Email protected]

# define BACKUP log files

logfile= $databackupdir/mysqlbackup.log

Date= ' Date-i '

echo "" > $emailfile

echo $ (date + "%y-%m-%d%h:%m:%s") >> $emailfile

CD $databackupdir

# define backup file name

Dumpfile=mysql_$date.sql

Gzdumpfile=mysql_$date.sql.tar.gz

# using Mysqldump to back up the database, set the parameters according to the specific situation

$mysqlDir/bin/mysqldump-u$user-p$userpwd--flush-logs-x $dbname > $dumpfile

# Compress backup Files

If [$?-eq 0]; Then

Tar czf $gzdumpfile $dumpfile >> $emailfile 2>&1

echo "BackupFileName: $gzdumpfile" >> $emailfile

echo "DataBase Backup success!" >> $emailfile

Rm-f $dumpfile

Else

echo "DataBase Backup fail!" >> $emailfile

Fi

# Write Log file

echo "--------------------------------------------------------" >> $logfile

Cat $emailfile >> $logfile

# Send an email notification

Cat $emailfile | Mail-s "MySQL Backup" $email

mysqldailybackup.sh Script content:

[email protected] ~]# cat mysqldailybackup.sh

#!/bin/sh

# Name:mysqlDailyBackup.sh

# define database directory and data directory

Mysqldir=/usr/local/mysql

datadir= $mysqldir/data

# define the user name and password used to back up the database

User=root

userpwd=123456

# define backup directory, daily backup file backup to $databackupdir/daily

Databackupdir=/opt/mysqlbackup

dailybackupdir= $databackupdir/daily

[!-D $dailybackupdir] && mkdir-p $databackupdir/daily

# define message body file

emailfile= $databackupdir/email.txt

# define Email Address

[Email protected]

# define log files

logfile= $databackupdir/mysqlbackup.log

echo "" > $emailfile

echo $ (date + "%y-%m-%d%h:%m:%s") >> $emailfile

#

# Refresh the log to make the database use the new binary log file

$mysqldir/bin/mysqladmin-u$user-p$userpwd Flush-logs

CD $datadir

# Get a binary log list

Filelist= ' Cat Mysql-bin.index '

Icounter=0

For file in $filelist

Do

Icounter= ' expr $icounter + 1 '

Done

Nextnum=0

Ifile=0

For file in $filelist

Do

Binlogname= ' basename $file '

nextnum= ' expr $nextnum + 1 '

# Skip the last binary log (the binary log file currently used by the database)

If [$nextnum-eq $icounter]; Then

echo "Skip lastest!" >/dev/null

Else

dest= $dailybackupdir/$binlogname

# Skip binary log files that have been backed up

If [-e $dest]; Then

echo "Skip exist $binlogname!" >/dev/null

Else

# Back up log files to backup directory

CP $binlogname $dailybackupdir

If [$?-eq 0]; Then

ifile= ' expr $ifile + 1 '

echo "$binlogname backup success!" >> $emailfile

Fi

Fi

Fi

Done

If [$ifile-eq 0];then

echo "No Binlog backup!" >> $emailfile

Else

echo "Backup $ifile File (s)." >> $emailfile

echo "Backup MySQL Binlog ok!" >> $emailfile

Fi

# Send an email notification

Cat $emailfile | Mail-s "MySQL Backup" $email

# Write Log file

echo "--------------------------------------------------------" >> $logfile

Cat $emailfile >> $logfile

http://hongge.blog.51cto.com/

MySQL Full library backup/incremental backup script

Related Article

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.