In the Internet project, the final operation is to read the data, which is inseparable from the previous deletion, modification and query. So the data is the top priority, and the backup of the database is particularly important.

But it is obviously unrealistic to directly export the sql file of the whole database every time. It has a great impact on the performance of the database.


Three ways to backup mysql

Hot standby: if mysql service is not shut down, the request can continue to operate the database for backup
Warm standby: supports reading, writing and backup without shutting down mysql service
* cold standby: realize backup when mysql service is shut down

##Type of backup

Full backup: the data structure of the whole database is sql file
Incremental backup: backup the incremental log file. The log file contains the records of operating the database data structure. We can use this as the recovery file for incremental backup

The plan of

Mysqldump is a backup tool in mysql

Use mysqldump to back up the database, mysqladmin to back up the data log, and write two scripts: a full backup script and an incremental backup script Use crontab to schedule tasks, execute the full backup script at 3:00 a.m. every Sunday, and do the incremental backup from Monday to Saturday at 3:00 a.m.

Practice in practice

1. Enable incremental backup of MySQL, and add log bin = / var / lib / MySQL / MySQL bin in my.cnf

vim /etc/my.cnf  

my.cof : 

\# Disabling symbolic-links is recommended to prevent assorted security risks  
\# Settings user and group are ignored when systemd is used.  
\# If you need to run mysqld under a different user or group,  
\# customize your systemd unit file for mariadb according to the  
\# instructions in

\# enable log_bin  


\# include all files from the config directory  
!includedir /etc/my.cnf.d

2. Create a path to store backup files

mkdir -p /home/mysql/backup  

3. Create mysql full backup script

vim /home/mysql/ : 


export LANG=en_US.UTF-8



Date=\`date +%Y%m%d\`

Begin=\`date +"%Y year%m month%d day %H:%M:%S"\`

cd $BakDir



mysqldump -uroot -p123456 --databases test --flush-logs --delete-master-logs --single-transaction > $DumpFile

tar -czvf $GZDumpFile $DumpFile

rm $DumpFile

count=$(ls -l *.tgz |wc -l)

if \[ $count -ge 5 \]


file=$(ls -l *.tgz |awk '{print $9}'|awk 'NR==1')

rm -f $file


#Keep database content only for the past four weeks

Last=\`date +"%Y year%m month%d day %H:%M:%S"\`

echo start:$Begin End:$Last $GZDumpFile succ >> $LogFile

cd $BakDir/daily

rm -f *


4. Create mysql incremental backup script

vim /home/mysql/ : 


export LANG=en_US.UTF-8





mysqladmin -uroot -p123456 flush-logs

#This is used to generate a new mysql-bin.00000 * file

Counter=\`wc -l $BinFile |awk '{print $1}'\`


#This for loop is used to compare the values of $counter and $nextnum to determine whether the file exists or is up to date.

for file in \`cat $BinFile\`


    base=\`basename $file\`

    #basename is used to intercept the file name of mysql-bin.00000 * and remove the one before. / mysql-bin.000005/

    NextNum=\`expr $NextNum + 1\`

    if \[ $NextNum -eq $Counter \]


        echo $base skip! >> $LogFile



        if(test -e $dest)

        #test -e is used to check whether the target file exists. If it exists, write exist! To $LogFile.


            echo $base exist! >> $LogFile


            cp $BinDir/$base $BakDir

            echo $base copying >> $LogFile




echo \`date +"%Y year%m month%d day %H:%M:%S"\` Bakup succ! >> $LogFile


5. Add execution permission for and

chmod 777  

6. Start timing task

vi /etc/crontab  


#Perform a full backup script every Sunday at 3:00 a.m

0 3 * * 0 /home/mysql/ >/dev/null 2>&1

#Do incremental backup from 3:00 a.m. Monday to Saturday

0 3 * * 1-6 /home/mysql/ >/dev/null 2>&1  

Make the above scheduled tasks effective

crontab /etc/crontab  

View scheduled tasks

crontab -l  

This is my public account with the latest it Consultation and personal work record:

! [scan code search joint propagation style wechat standard green version. png](,png)

This is my personal wechat. Welcome to ask:

! [wechat picture. JPG](,png)

Finally, add the high-quality Taobao store: if there is any quality problem, drop me at any time, the old and the young are honest!

! [wechat picture(,png)