Explains how to automatically back up MySQL Data in Linux.

Explains how to automatically back up MySQL Data in Linux.

1. Create a database backup directory first:

mkdir backupcd backupmkdir mysqlbackupcd mysqlbackup

2. Create a backup script

vi mysqlautobackup

3. Compile the script:

Filename = 'date + % Y % m % D'/mysql bin directory/mysqldump -- opt database name-u (mysql account)-p (mysql password) | gzip>/directory to which the backup is saved/name1_filename.gz

Note: gzip compression and name can be written at will. There is no space or parentheses between-u and mysql accounts, and the-p and mysql passwords are also used.
4. Set the script as executable:

chmod +x autobackup

5. Write a scheduled task:

Crontab-e01 01 ***/bin/bash/Script directory/mysqlautobackup

Execute the script at every morning.
Restart a scheduled task:

/etc/rc.d/init.d/crond restart

So far, all the steps have been completed.

Some common skills
1. Create the path to save the backup file/mysqldata

#mkdir /mysqldata

2. Create the/usr/sbin/baktest file.
Enter the following content:

mysqldump -uroot -proot test | gzip > /mysqldata/test`date +%Y-%m-%d_%H%M%S`.sql.gz

3. Modify file attributes so that they can be executed

#chmod +x /usr/sbin/baktest

4. Modify/etc/crontab
Add

01 3 * * * root /usr/sbin/baktest

Indicates that the backup is performed at three o'clock every day.

5. Restart crond.

#/etc/rc.d/init.d/crond restart

Complete.

PS:
Full backup script:

#! /Bin/bash # Name: qbk full backup script # mysql qbk scripts # By zxsdw.com # Last Modify: # define the script storage path # scriptsDir =/usr/sbin # define the user name and password user = rootuserPWD = PASSWORD # define the database to be backed up = database # define the full backup file storage path bakDir =/backup/mysql # eMailFile = $ bakDir/email.txt # eMail = admin@zxsdw.com # define the log file LogFile = $ bakDir/mysqlbak. logDATE = 'date + % Y % m % d' echo ""> $ LogFileecho "" >>$ LogFileecho "--------------------------" >>$ LogFileecho $ (date + "% y- % M-% d % H: % M: % S ")> $ LogFileecho "-----------------" >>$ LogFilecd restart mysqldump -- flush-logs-u $ user-p $ userPWD -- quick $ database | gzip> $ DumpFileecho "Dump Done"> $ LogFileecho "[$ DumpFile] Backup Success! ">>$ LogFiledaily_databakDir = $ bakDir/daily_backupcd $ bakDir/daily_backupfind $ daily_databakDir-name" daily * "-type f-mtime + 35-exec rm {}\;> /dev/null 2> & 1

Incremental backup script

#! /Bin/bash # Name: zbk Incremental Backup # mysql zbk scripts # By zxsdw.com # Last modify: # define database username and password user = rootuserPWD = PASSWORD # define database = database # generate a new mysql-bin.00000X file, if the err log is cleared, a new one is automatically created. /Usr/local/mysql/bin/mysqladmin-u $ user-p $ userPWD flush-logs # define the Incremental backup location daily_databakDir =/backup/mysql/daily_backup # define the mysql data log directory mysqlDataDir =/usr/local/mysql/var # defines the incremental log and directory eMailFile = $ daily_databakDir/email.txt # eMail = admin@zxsdw.com # defines the variable DATE format as 20150127 DATE = 'date + % Y % m % d' # define a total logFile log logFile = $ daily_databakDir/mysql $ DATE. log # beautify log template echo ""> $ eMailFileecho "-----------------------" >>$ eMailFile # Time Format Is 15-01-27 01: 06: 17 echo $ (date + "% y-% m-% d % H: % M: % S")> $ eMailFileecho "-----------------------"> $ eMailFile # defines the time range for deleting bin logs, the format is 20150124010540 TIME = $ (date "-d 3 day ago" + % Y % m % d % H % M % S) # defines the TIME range for Incremental backup data, format: 2015-01-26 01: 04: 11 StartTime = $ (date "-d 1 day ago" + "% Y-% m-% d % H: % M: % S ") ########## start the Delete operation to beautify the log title ############## echo "Delete 3 days before the log" >>$ eMailFile # Delete the binfile three days ago, and update index records in the index to beautify the log title mysq L-u $ user-p $ userPWD-e "purge master logs before $ {TIME}" & echo "delete 3 days before log" | tee-a $ eMailFile # search for index bin2 base file in the index and assign it to I. Filename = 'cat $ mysqlDataDir/mysql-bin.index | awk-F "/" '{print $2} ''for I in $ filenamedo ######## start Incremental Backup operation, beautification log title ########## echo "$ StartTime start backup binlog"> $ eMailFile # Use mysqlbinlog to back up the added data one day ago, decompress and package gzip to the Incremental Backup Directory/usr/local/mysql/bin/mysqlbinlog-u $ user-p $ userPWD-d $ database -- start-datetime = "$ StartTime" $ mysqlDataDir/$ I | gzip >>$ daily_databakDir/daily$DATE. SQL .gz | tee-a $ eMailFiledone # If the preceding backup Run the following Delete script if [$? = 0] then # Delete the incremental log backup file of mtime> 32 find $ daily_databakDir-name "*. log "-type f-mtime + 32-exec rm {}\;> /dev/null 2> & 1cd $ daily_databakDirecho "Daily backup succeed"> $ eMailFileelseecho "Daily backup fail" >>$ eMailFile # mail-s "MySQL Backup" $ eMail <$ eMailFile # Send email notification after backup failure # fi end IF judgment fi # Replace the content of the variable eMailFile with the content of logFile cat $ eMailFile> $ logFile # IF the above IF judgment fails, run the "find $ daily_databakDir-name" * command again to delete the incremental log backup file of mtime> 32 "*. log "-type f-mtime + 32-exec rm {}\;> /dev/null 2> & 1 rsync-vzrtopg -- delete -- progress -- password-file =/usr/local/rsync. passwd root @ $ ip:/zxs/allimg/$ (date-d-1day + % y % m % d) /zxs/allimg/gunzip </backup/mysql/daily_backup/ceshi. SQL .gz |/usr/local/mysql/bin/mysql-u user name-p Password Database Name -- force parameter ignored error