MYSQL database automatic local/remote dual backup/MYSQL incremental backup

MYSQL database automatic local remote dual-backup MYSQL incremental backup to build a high-security E-commerce website (automatic local/remote dual-backup of website files and databases) architecture diagram

We will continue to introduce the e-commerce system architecture related to file backup, database backup, and data security storage on Linux servers. There are a variety of security solutions, data backup is the top priority.
E-commerce websites pay more attention to data security and data backup solutions, including local backup and remote backup architectures. There are many backup solutions for Linux servers. This article introduces a popular solution to complete automatic backup by writing Shell scripts. This architecture includes backing up website files and databases, automatically backing up local data, uploading backup scripts over FTP, and completing local backup and remote backup to implement a dual-layer backup solution.

Key points of this article:

1. MYSQL database automatic local/remote dual backup/MYSQL database incremental backup.
2. write a Shell script to complete automatic MYSQL backup and MYSQL database incremental backup.
3. both local and remote backup and FTP backup are performed simultaneously.
4. backup solution for Linux servers.
5. Shell scripts include backing up website files, website program files, data files, and MYSQL databases.
6. regular and scheduled backups are automatically completed. Regular deletion of old Backup. here, the backup is automatically deleted 30 days ago, and the backup space is reused.

Directory:

I. preparations
II. website O & M remote backup solution and fault emergency backup mirror station architecture
III. Script for automatic local/remote dual-backup of MYSQL database/Shell for incremental backup of MYSQL database, complete instance; detailed explanation and comment on the backup script.

At the beginning of the e-commerce website in operation, we have been reiterated that we must back up our own data, because too much uncertainty may cause database loss, in addition, most basic service providers cannot provide daily backup data. In the past, this BLOG provided a backup method, which introduced the automatic backup of the Shell script MYSQL database, but did not introduce the incremental backup of the MYSQL database. Today I will share my own backup script.
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/remote dual backup) [serialization of e-commerce system architecture]"

I. preparations:

After lftp is installed on the Linux server, you must create the/home/backup directory on the Linux server in advance. And ensure that FTP can normally use the account password to log on to the last file. It ensures that the FTP service provides services normally.

II. website O & M remote backup solution and fault emergency backup mirror station architecture

III. website MYSQL database automatic local/remote dual backup/MYSQL database incremental backup Shell script

Script 2:
MYSQL database incremental backup Shell script

If the database data volume is large, you can perform full backup once a day, and then perform incremental backup once every one hour;
Create an incremental backup directory
The incremental backup files are stored in the/backup/mysql/daily directory.
The incremental backup data volume is small, but must be performed on the basis of the complete backup.

The incremental backup uses the bin log. the script is as follows:

Double-click all code

123456789101112131415161718192021222324252627282930313233343536373839404142434445

#!/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/mysqlBAKDIR=/backup/mysql/daily ### If you have made some special settings for mysql bin log, modify this field or modify the row that applies this variable: the machine name is used by default, and the Machine name is used by mysql by default.HOSTNAME=`uname -n`cd $DATADIR FILELIST=`cat $HOSTNAME-bin.index` ## COUNTER numberCOUNTER=0 forfilein$FILELIST doCOUNTER=`expr $COUNTER + 1 `done NextNum=0 forfilein $FILELIST dobase=`basename $file`NextNum=`expr $NextNum + 1`if [ $NextNum -eq $COUNTER ]then echo"skip lastest" elsedest=$BAKDIR/$baseif(test -e $dest)then echo"skip exist $base" else echo"copying $base" cp $base $BAKDIRfifidone echo"backup mysql binlog ok"

Script parsing description:
The incremental backup script is flush-logs before the backup. mysql will automatically put the logs in the memory into the file and generate a new log file. Therefore, we only need to back up the first few logs, that is, do not back up the last one.
Because multiple log files may be generated from the last backup to the current backup, you do not need to back up the files if the files have been backed up.
Tip: Incremental Backup:
Perform full backup at PM and pm every day, and back up binlog every one hour, that is, incremental backup. the specific operations are as follows:
Enable binlog in Linux
Put the script under/root/, modify the parameters in the script according to the above annotation, use the vim editor and save it.
Run: chmod + x/root/backup. sh to add the execution permission for the script.
Run: crontab-e.
Add: 0 3 ***/root/backup. sh to crontab.
The/root/bakcup. sh script is automatically executed at A.M. to back up data on the Linux server and upload it to the preset remote FTP.
Back up your website at because the website traffic during this period is the lowest. That is to say, backup is performed when few people access it.

Double-click all code

12345678910

Add the mysqld section in/etc/my. cnf:[mysqld]log-bin=../logs/mysql-bin max-binlog-size=512M Enable binlog in windowsAdd the mysqld section in % mysql %/my. ini:[mysqld]log-bin =../logs/mysql-bin max-binlog-size=512M

Script 1:
The website and database are automatically backed up locally and FTP is used to upload the backup Shell script. complete instance:

Double-click all code

123456789101112131415161718192021222324252627282930

#!/bin/bash #Funciont: Backup websiteandmysqldatabase #Author: Jimmy Li#Website: http://jimmyli.blog.51cto.com/ #IMPORTANT!!!Please Setting the followingValues! Backup_Dir1=/data/wwwroot/jimmyli.jimmyli.jimmyli.blog.51cto.comMYSQL_UserName=rootMYSQL_PassWord = your mysql database root passwordBackup_Database_Name1=jimmyli.jimmyli.jimmyli.blog.51cto.comFTP_HostName=jimmyli.jimmyli.jimmyli.blog.51cto.comFTP_UserName=jimmyli.jimmyli.jimmyli.blog.51cto.comFTP_PassWord=jimmyli.jimmyli.jimmyli.blog.51cto.comFTP_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 $OldWWWBackuprm $OldDBBackupcd /home/backup/lftp $FTP_HostName -u $FTP_UserName,$FTP_PassWord << EOFcd $FTP_BackupDirmrm $OldWWWBackupmrm $OldDBBackupmput $TodayWWWBackupmput $TodayDBBackupbyeEOF

Backup Script for a detailed explanation, comments refer to the http://jimmyli.blog.51cto.com/3190309/691069 "to build a high security E-commerce website (website files and database automatic local/remote dual backup) [serialization of e-commerce system architecture]"
========================================================== ======================================

Website O & M remote backup solution and fault emergency backup mirror station

Enable emergency Web service when remote backup fault emergency is regularly checked
Connection: http://jimmyli.blog.51cto.com/3190309/584992 website O & M remote backup solution and fault emergency backup mirror station

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