本篇内容主要讲解“mysql如何全量备份和增量备份”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“mysql如何全量备份和增量备份”吧!

mysql 全量备份：

vim/root/mysql_bakup.sh

#!/bin/bash#Date:2017/5/2#Author:wangpengtai#Blog:http://wangpengtai.blog.51cto.com#AtSunday,wewillbackupthecompleteddatabasesandtheincresedbinarylogduringSaturdaytoSunday.#Inotherweekdays,weonlybackuptheincreaingbinarylogatthatday!#################################thegloblevariablesforMySQL#################################DB_USER='root'DB_PASSWORD='123456'DB_PORT='3306'BACKUPDIR='/tmp/mysqlbakup'BACKUPDIR_OLDER='/tmp/mysqlbakup_older'DB_PID='/data/mysql/log/mysqld.pid'DB_SOCK='/data/mysql/log/mysql.sock'LOG_DIR='/data/mysql/log'BACKUP_LOG='/tmp/mysqlbakup/backup.log'DB_BIN='/usr/local/mysql/bin'#timevariablesforcompletedbackupFULL_BAKDAY='Sunday'TODAY=`date+%A`DATE=`date+%Y%m%d`############################timevariablesforbinlog#############################liftcycleforsavingbinlogDELETE_OLDLOG_TIME=$(date"-d14dayago"+%Y%m%d%H%M%S)#Thestarttimepointtobackupbinlog,theusageofmysqlbinlogis--start-datetime,--stop-datetime,timeformatis%Y%m%d%H%M%S,eg:20170502171054,timezonesis[start-datetime,stop-datetime)#Thedatetostartbackupbinlogisyesterdayatthisverymoment!START_BACKUPBINLOG_TIMEPOINT=$(date"-d1dayago"+"%Y-%m-%d%H:%M:%S")#BINLOG_LIST=`cat/data/mysql/log/mysql-bin.index`BINLOG_INDEX='/data/mysql/log/mysql-bin.index'###############################################Judgethemysqlprocessisrunningornot.##mysqlstopreturn1,mysqlrunningreturn0.###############################################functionDB_RUN(){iftest-a$DB_PID&&test-a$DB_SOCK;thenreturn0elsereturn1fi}####################################################################################################Judgethebacupdirectoryisexsitnot.##Ifthemysqlbakupdirectorywasexsited,therewilledreturn0.##Ifthereisnoamysqlbakupdirectory,thefuctionwillcreatethedirectoryandreturnvalue1.####################################################################################################functionBACKDIR_EXSIT(){iftest-d$BACKUPDIR;then#echo"$BACKUPDIRwasexist."return0elseecho"$BACKUPDIRisnotexist,nowcreateit."mkdir-pv$BACKUPDIRreturn1fi}####################################################################################################Judgethebinlogisconfigedornot.##Ifthemysqlbakupdirectorywasexsited,therewilledreturn0.##Ifthereisnoamysqlbakupdirectory,thefuctionwillcreatethedirectoryandreturnvalue1.####################################################################################################functionBINLOG_EXSIT(){iftest-f$BINLOG_INDEX;then#echo"$BACKUPDIRwasexist."return0fi}####################################################ThefullbackupforallDatabases##Thisfunctionisusetobackupthealldatabases.####################################################functionFULL_BAKUP(){echo"At`date+%D\%T`:StartingfullbackuptheMySQLDB..."#rm-fr$BACKUPDIR/db_fullbak_$DATE.sql#fortest!!$DB_BIN/mysqldump--lock-all-tables--flush-logs--master-data=2-u$DB_USER-p$DB_PASSWORD-P$DB_PORT-A|gzip>$BACKUPDIR/db_fullbak_$DATE.sql.gzFULL_HEALTH=`echo$?`if[[$FULL_HEALTH==0]];thenecho"At`date+%D\%T`:MySQLDBincresedbackupsuccessfully"elseecho"MySQLDBfullbackupfailed!"fi}#python#>>>withopen('/data/mysql/log/mysql-bin.index','r')asobj:#...foriinobj:#...printos.path.basename(i)#...#mysql-bin.000006#mysql-bin.000007#mysql-bin.000008#mysql-bin.000009functionINCREASE_BAKUP(){echo"At`date+%D\%T`:StartingincreasedbackuptheMySQLDB..."$DB_BIN/mysqladmin-u$DB_USER-p$DB_PASSWORD-P$DB_PORTflush-logs$DB_BIN/mysql-u$DB_USER-p$DB_PASSWORD-P$DB_PORT-e"purgemasterlogsbefore${DELETE_OLDLOG_TIME}"foriin`cat$BINLOG_INDEX`do$DB_BIN/mysqlbinlog-u$DB_USER-p$DB_PASSWORD-P$DB_PORT--start-datetime="$START_BACKUPBINLOG_TIMEPOINT"$i|gzip>>$BACKUPDIR/db_daily_$DATE.sql.gzdone#$DB_BIN/mysqlbinlog-u$DB_USER-p$DB_PASSWORD-P$DB_PORT--start-datetime="$START_BACKUPBINLOG_TIME"$LOG_DIR/mysql-bin.[0-9]*|gzip>>$BACKUPDIR/db_daily_$DATE.sql.gzINCREASE_HEALTH=`echo$?`if[[$INCREASE_HEALTH==0]];thenecho"At`date+%D\%T`:MySQLDBincresedbackupsuccessfully"elseecho"MySQLDBincresedbackupfailed!"fi}functionOLDER_BACKDIR_EXSIT(){iftest-d$BACKUPDIR_OLDER;then#echo"$BACKUPDIR_OLDERwasexist."return0elseecho"$BACKUPDIR_OLDERisnotexist,nowcreateit."mkdir-pv$BACKUPDIR_OLDER#return1fi}functionBAKUP_CLEANER(){#movethebackupedfilethatcreatedtimeoutof7daystotheBACKUPDIR_OLDERdirectoryreturnkey=`find$BACKUPDIR-name"*.sql.gz"-mtime+7-execls-lh{}\;`returnkey_old=`find$BACKUPDIR_OLDER-name"*.sql.gz"-mtime+14-execls-lh{}\;`if[[$returnkey!='']];thenecho"----------------------"echo"Movingtheolderbackupedfileoutof7daysto$BACKUPDIR_OLDER."echo"Themovedfilelistis:"find$BACKUPDIR-name"*.sql.gz"-mtime+7-execmv{}$BACKUPDIR_OLDER\;echo"-----------------------"elif[[$returnkey_old!='']];then#deletethebackupedfilethatcreatedtimeoutof14daysfromBACKUPDIR_OLDERdirectory.echo"Deletetheolderbackupedfileoutof14daysfrom$BACKUPDIR_OLDER."echo"Thedeletedfileslistis:"find$BACKUPDIR_OLDER-name"*.sql.gz"-mtime+14-execrm-fr{}\;fi}#####################################--------------main----------------#####################################functionMAIN(){DB_RUN#Judgetheprocessisrunornot,ifnotrun,thescriptwillnotbakupdbRun_process=`echo$?`echo$?if[[$Run_process==0]];thenBINLOG_EXSITbinlog_index=`echo$?`if[[$binlog_index==0]];thenecho"**********START**********"echo$(date+"%y-%m-%d%H:%M:%S%A")echo"~~~~~~~~~~~~~~~~~~~~~~~"if[[$TODAY==$FULL_BAKDAY]];thenecho"Startcompletedbakup..."INCREASE_BAKUPFULL_BAKUP#fullbackuptoallDBBAKUP_CLEANERelseecho"Startincreaingbakup..."INCREASE_BAKUPfiecho"~~~~~~~~~~~~~~~~~~~~~~~"echo$(date+"%y-%m-%d%H:%M:%S%A")echo"**********END**********"elseecho"**********START**********"echo$(date+"%y-%m-%d%H:%M:%S%A")echo"~~~~~~~~~~~~~~~~~~~~~~~"echo"Sorry,MySQLbinlogwasnotconfiged,pleaseconfigthemy.cnffirstly!"echo"~~~~~~~~~~~~~~~~~~~~~~~"echo$(date+"%y-%m-%d%H:%M:%S%A")echo"**********END**********"fielseecho"**********START**********"echo$(date+"%y-%m-%d%H:%M:%S%A")echo"~~~~~~~~~~~~~~~~~~~~~~~"echo"Sorry,MySQLwasnotrunning,thedbcouldnotbebackuped!"echo"~~~~~~~~~~~~~~~~~~~~~~~"echo$(date+"%y-%m-%d%H:%M:%S%A")echo"**********END**********"fi}#startingruningBACKDIR_EXSIT$BACKUP_LOGOLDER_BACKDIR_EXSIT$BACKUP_LOG

MAIN>>$BACKUP_LOG

到此，相信大家对“mysql如何全量备份和增量备份”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！