MySQL database automatic local/offsite dual backup/mysql Incremental backup

Construction of high-security e-commerce website (website file and database automatic local/offsite dual backup) architecture diagram

Continue to introduce Linux server file backup, database backup, data security storage related e-commerce system architecture. There are a variety of solutions for security, where data backup is a top priority.
E-commerce sites are more focused on data security, data backup solutions, including local backup, offsite backup architecture. There are a lot of backup schemes for Linux servers, this article describes a popular solution, by writing shell scripts to complete the automatic backup. This architecture includes backup Web files, database, automatic local backup and FTP upload backup script, complete the corresponding local backup, offsite backup, realize two-tier backup solution.

This article highlights:

1.MYSQL Database automatic local/offsite dual backup/mysql database incremental backup.
2. Write the shell script to complete the automatic MySQL backup, the MySQL database incremental backup.
3. Simultaneous automatic local/offsite dual backup, FTP upload backup.
Backup solution for 4.Linux servers.
5.Shell scripts include backup website files, website program files, data files, MySQL database.
6. Automatically complete the backup at regular intervals. Delete the old backup regularly, here is automatically deleted 30 days ago Backup, reuse the backup space.

Directory:

First, the preparatory work
Second, the site operation and maintenance of remote backup scheme and fault emergency standby mirror station structure diagram
Third, the website MySQL database automatic local/offsite double backup/mysql database Incremental backup shell script, complete instance; Backup script for detailed explanation, comments.

In the operation of the e-commerce website, the establishment of the site has been reiterated that it is important to back up their data, because too much uncertainty may result in the loss of the database, and most of the basic service providers can not provide daily backup data. Originally this blog provides a backup method, introduced the shell script MySQL database automatic backup, does not introduce the MySQL database incremental backup. Share one of your own backup scripts today.
Refer to the previous article http://jimmyli.blog.51cto.com/3190309/691069 "Building a high-security e-commerce website (website files and database automatic local/offsite dual backup) [Serial e-commerce system architecture]"

First, the preparatory work:

Linux servers are installed LFTP, and you need to create/home/backup/backup directories on Linux servers in advance. and ensure that FTP can normally use the account password to log on the last file. is to ensure that the FTP service provides the service normally.

Second, the site operation and maintenance of remote backup scheme and fault emergency standby mirror station structure diagram

Third, the website MySQL database automatic local/offsite dual backup/mysql database Incremental backup shell script

Script two:
MySQL Database incremental backup shell script

If the database data volume is large, can be fully prepared once a day, and then incremental backup every hour;
Create an incremental backup directory
The files for incremental backups are placed in the/backup/mysql/daily directory.
Incremental backups have a smaller amount of data, but operate on a full backup basis.

Incremental backups Use bin log, which is the following script:

Double-click code Select All

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

#!/bin/sh   # mysql data backup script   # use mysqldump --help,get more detail.   #Author: Jimmy Li   #Website: http://jimmyli.blog.51cto.com/   # mysql binlog backup script           /usr/bin/mysqladmin flush-logs           DATADIR=/var/lib/mysql   BAKDIR=/backup/mysql/daily           ###如果mysql bin log你做了特殊设置,请修改此处或者修改应用此变量的行:缺省取机器名,mysql缺省也是取机器名   HOSTNAME=`uname -n`   cd $DATADIR   FILELIST=`cat $HOSTNAME-bin.index`           ## COUNTER number   COUNTER=0   for file in $FILELIST   do   COUNTER=`expr $COUNTER + 1 `   done           NextNum=0   for file in$FILELIST   do   base=`basename $file`   NextNum=`expr $NextNum + 1`   if [ $NextNum -eq $COUNTER ]   then echo "skip lastest" else dest=$BAKDIR/$base   if(test -e $dest)   then echo "skip exist $base" else echo "copying $base" cp $base $BAKDIR   fi   fi   done           echo "backup mysql binlog ok"

Script Parsing Instructions:
The incremental backup script is the pre-backup flush-logs,mysql will automatically put the in-memory log into the file, and then generate a new log file, so we only need to back up the previous few, that is, do not back up the last one.
Since the last backup to this backup may also have more than one log file generation, so to detect the file, if it has been backed up, there is no backup.

Warm tips, incremental backups:
Every day at 03 o'clock Noon and at 03 o'clock do a full-time, every hour backup binlog, that is, incremental backup, the specific operation is as follows:

Open Binlog under Linux

Put the script under/root/, modify the parameters in the script as noted above, use the Vim editor and save.
Execute: chmod +x/root/backup.sh Add Execute permissions for the script.
Execution: crontab-e add timed execution.
Add to crontab: 0 3 * * */root/backup.sh
3 o'clock in the morning automatically executes the/root/bakcup.sh script, backs up the data on the Linux server and uploads it to a pre-configured offsite FTP.
Choose to back up in the Wee 3, because this time period is the lowest amount of site visits. This means that the backup operation is done with few people accessing it.

Double-click code Select All

1 2 3 4 5 6 7 8 9

/etc/my.cnf Mysqld section joins:   [mysqld]   Log-bin=. /logs/mysql-bin   max -binlog- Code class= "SQL keyword" >size =512m              windows open binlog    %mysql%/my.ini mysqld section join:   [ mysqld]   log-bin =: /logs/mysql-bin   max -binlog- Code class= "SQL keyword" >size =512m

Script one:
Website and database automatic local backup and FTP upload backup shell script, complete instance:

Double-click code Select All

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

#!/bin/bash   #Funciont: Backup website and mysql database #Author: Jimmy Li   #Website: http://jimmyli.blog.51cto.com/   #IMPORTANT!!!Please Setting the following Values!   Backup_Dir1=/data/wwwroot/jimmyli.jimmyli.jimmyli.blog.51cto.com   MYSQL_UserName=root   MYSQL_PassWord=你的mysql数据库root密码   Backup_Database_Name1=jimmyli.jimmyli.jimmyli.blog.51cto.com   FTP_HostName=jimmyli.jimmyli.jimmyli.blog.51cto.com   FTP_UserName=jimmyli.jimmyli.jimmyli.blog.51cto.com   FTP_PassWord=jimmyli.jimmyli.jimmyli.blog.51cto.com   FTP_BackupDir=jimmyli.jimmyli.jimmyli.blog.51cto.com   TodayWWWBackup=www-*-$(date +"%Y%m%d").tar.gz   TodayDBBackup=db-*-$(date +"%Y%m%d").sql   OldWWWBackup=www-*-$(date -d -30day +"%Y%m%d").tar.gz   OldDBBackup=db-*-$(date -d -30day +"%Y%m%d").sql   tar zcf /home/backup/www-$Backup_Dir1-$(date +"%Y%m%d").tar.gz -C /home/wwwroot/ $Backup_Dir1 --exclude=soft   /usr/local/mysql/bin/mysqldump -u$MYSQL_UserName -p$MYSQL_PassWord $Backup_Database_Name1 > /home/backup/db-$Backup_Database_Name1-$(date +"%Y%m%d").sql   rm $OldWWWBackup   rm $OldDBBackup   cd /home/backup/   lftp $FTP_HostName -u $FTP_UserName,$FTP_PassWord << EOF   cd $FTP_BackupDir   mrm $OldWWWBackup   mrm $OldDBBackup   mput $TodayWWWBackup   mput $TodayDBBackup   bye   EOF

A detailed explanation of the backup script, annotated reference http://jimmyli.blog.51cto.com/3190309/691069 "building a high-security e-commerce site (website files and database automatic local/offsite dual backup) [Serial e-commerce system architecture]"
========================================================================

Site operation and maintenance offsite backup scheme and fault emergency backup mirror station

Enable the emergency Web service when periodically checking for offsite backup failure emergencies
Connection: http://jimmyli.blog.51cto.com/3190309/584992 website operation and maintenance offsite backup scheme and fault emergency backup mirror station

This article is from "Jimmy Li I stand on the shoulders of Giants" blog, please be sure to keep this source http://jimmyli.blog.51cto.com/3190309/888630