How to implement automatic MySQL backup _ MySQL

How to implement automatic MySQL backup is critical, especially for DBAs. Here we mainly use code to illustrate this problem and hope it will be helpful to you. You can put this script into crontab and execute it once every morning for automatic backup.

This script can be executed only once a day, and only the backups of the last five days are retained on the server.

Code:

　　
#! /Bin/bash
# This is a ShellScript For Auto DB Backup
# Powered by aspbiz
#2004-09

# Setting
# Set the database name, database login name, password, backup path, log path, data file location, and backup mode
# By default, the backup method is tar, mysqldump, or mysqldotcopy.
# By default, log on to the mysql database with root (empty) and back up to/root/dbxxxxx. tgz
DBName = mysql
DBUser = root
DBPasswd =
BackupPath =/root/
LogFile =/root/db. log
DBPath =/var/lib/mysql/
# BackupMethod = mysqldump
# BackupMethod = mysqlhotcopy
# BackupMethod = tar
# Setting End

　　
NewFile = "$ BackupPath" db $ (date + % y % m % d). tgz
DumpFile = "$ BackupPath" db $ (date + % y % m % d)
OldFile = "$ BackupPath" db $ (date + % y % m % d -- date = '5 days ago '). tgz

Echo "-----------------------------------------"> $ LogFile
Echo $ (date + "% y-% m-% d % H: % M: % S") >>$ LogFile
Echo "--------------------------"> $ LogFile
# Delete Old File
If [-f $ OldFile]
Then
Rm-f $ OldFile >>$ LogFile 2> & 1
Echo "[$ OldFile] Delete Old File Success! ">>> $ LogFile
Else
Echo "[$ OldFile] No Old Backup File! ">>> $ LogFile
Fi

If [-f $ NewFile]
Then
Echo "[$ NewFile] The Backup File is exists, Can't Backup! ">>> $ LogFile
Else
Case $ BackupMethod in
Mysqldump)
If [-z $ DBPasswd]
Then
Mysqldump-u $ DBUser -- opt $ DBName> $ DumpFile
Else
Mysqldump-u $ DBUser-p $ DBPasswd -- opt $ DBName> $ DumpFile
Fi
Tar czvf $ NewFile $ DumpFile >>$ LogFile 2> & 1
Echo "[$ NewFile] Backup Success! ">>> $ LogFile
Rm-rf $ DumpFile
;;
Mysqlhotcopy)
Rm-rf $ DumpFile
Mkdir $ DumpFile
If [-z $ DBPasswd]
Then
Mysqlhotcopy-u $ DBUser $ DBName $ DumpFile >>$ LogFile 2> & 1
Else
Mysqlhotcopy-u $ DBUser-p $ DBPasswd $ DBName $ DumpFile >>$ LogFile 2> & 1
Fi
Tar czvf $ NewFile $ DumpFile >>$ LogFile 2> & 1
Echo "[$ NewFile] Backup Success! ">>> $ LogFile
Rm-rf $ DumpFile
;;
*)
/Etc/init. d/mysqld stop>/dev/null 2> & 1
Tar czvf $ NewFile $ DBPath $ DBName >>$ LogFile 2> & 1
/Etc/init. d/mysqld start>/dev/null 2> & 1
Echo "[$ NewFile] Backup Success! ">>> $ LogFile
;;
Esac
Fi

Echo "-----------------------------------------"> $ LogFile