Precondition

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 databak.sh and an incremental backup script binlogbak.sh. 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 : 

\[mysqld\]  
datadir=/var/lib/mysql  
socket=/var/lib/mysql/mysql.sock  
\# Disabling symbolic-links is recommended to prevent assorted security risks  
symbolic-links=0  
\# 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 http://fedoraproject.org/wiki/Systemd

\# enable log_bin  
log-bin=/var/lib/mysql/mysql-bin

\[mysqld_safe\]  
log-error=/var/log/mariadb/mariadb.log  
pid-file=/var/run/mariadb/mariadb.pid

#  
\# 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/databak.sh  

databak.sh : 

#!/bin/bash

export LANG=en_US.UTF-8

BakDir=/home/mysql/backup

LogFile=/home/mysql/backup/bak.log

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

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

cd $BakDir

DumpFile=$Date.sql

GZDumpFile=$Date.sql.tgz

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 \]

then

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

rm -f $file

fi

#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/binlogbak.sh  

binlogbak.sh : 

#!/bin/bash

export LANG=en_US.UTF-8

BakDir=/home/mysql/backup/daily

BinDir=/var/lib/mysql

LogFile=/home/mysql/backup/binlog.log

BinFile=/var/lib/mysql/mysql-bin.index

mysqladmin -uroot -p123456 flush-logs

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

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

NextNum=0

#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\`

do

    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 \]

    then

        echo $base skip! >> $LogFile

    else

        dest=$BakDir/$base

        if(test -e $dest)

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

        then

            echo $base exist! >> $LogFile

        else

            cp $BinDir/$base $BakDir

            echo $base copying >> $LogFile

        fi

    fi

done

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

  

5. Add execution permission for databak.sh and binlogbak.sh

chmod 777 binlogbak.sh databak.sh  

6. Start timing task

vi /etc/crontab  

crontab:

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

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

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

0 3 * * 1-6 /home/mysql/binlogbak.sh >/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]( https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy8xODU1NzgxMy05YzcxMDlkZjgzMmJlOTc2LnBuZw?x-oss-process=image/format,png)

This is my personal wechat. Welcome to ask:

! [wechat picture. JPG]( https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy8xODU1NzgxMy03NjJmYTFlOGFlOWZjMjk3LmpwZw?x-oss-process=image/format,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( https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy8xODU1NzgxMy1iZjlkMjQzYjg5OWIyYTA1LnBuZw?x-oss-process=image/format,png)