?
Automated MySQL Database Backup
Version 1
Want to backup your MySQL databases to another machine on a nightly basis??
Then create a /etc/cron.daily/mysqlbackup.sh job like this:
?
Run
chmod +x /etc/cron.daily/mysqlbackup.sh
.? And change the $ parameters to the appropriate 'real' values.
Or if you are not backing up to a separate host, run:
?
Version 2
This one is slightly more advanced. It's a bash script that will basically do the same as above but also only keep a certain number of old copies.
Note that this script is prepared to backup as the root user, if you are going to be running this as a non root user you should modify paths to reflect that.
#?List?of?databases?to?be?backed?up?separated?by?space
dblist = " db_name_1?db_name_2?db_name_3?etc. "
# ?Directory?for?backups
backupdir =/ root / mysql_dumps
# ?Number?of?versions?to?keep
numversions = 4
# ?Full?path?for?MySQL?hotcopy?command
#?Please?put?credentials?into?/root/.my.cnf
#hotcopycmd=/usr/bin/mysqlhotcopy
hotcopycmd = " /usr/bin/mysqldump?--lock-tables?--databases "
# ?Create?directory?if?needed
mkdir ? - p? " $backupdir "
if ?[? ! ? - d? " $backupdir " ?];?then
???echo? " Invalid?directory:?$backupdir "
??? exit ? 1
fi
# ?Hotcopy?begins?here
echo? " Dumping?MySQL?Databases... "
RC = 0
for ?database?in? $dblist ;? do
???echo
???echo? " Dumping?$database?... "
???mv? " $backupdir/$database.gz " ? " $backupdir/$database.0.gz " ? 2 > ? / dev / null
??? $hotcopycmd ? $database ? | ?gzip? > ? " $backupdir/$database.gz "
???RC = $ ?
??? if ?[? $RC ? - gt? 0 ?];?then
????? continue ;
???fi
??? # ?Rollover?the?backup?directories
???rm? - fr? " $backupdir/$database.$numversions.gz " ? 2 > ? / dev / null
???i = $numversions
??? while ?[? $i ? - gt? 0 ?];? do
?????mv? " $backupdir/$database.`expr?$i?-?1`.gz " ? " $backupdir/$database.$i.gz " ? 2 > ? / dev / null
?????i = `expr? $i ? - ? 1 `
???done
done
if ?[? $RC ? - gt? 0 ?];?then
???echo? " MySQL?Dump?failed! "
??? exit ? $RC
else
??? # ?Hotcopy?is?complete.?List?the?backup?versions!
???ls? - l? " $backupdir "
???echo? " MySQL?Dump?is?complete! "
fi
exit ? 0
?
So this system has three parts. First is the file that holds the credentials, e.g. /root/.my.cnf and it's in standard mysql config format like so:
?
Second is the script itself, as listed above. Enter the names of your dbs, how many copies to keep, and where to put them.
?
Third is the crontab entry that will run this script for you. For example, once per day:
#?-----------------?minute?(0?-?59)
#?|??--------------?hour?(0?-?23)
#?|??|??-----------?day?of?month?(1?-?31)
#?|??|??|??--------?month?(1?-?12)
#?|??|??|??|??-----?day?of?week?(0?-?7)?(Sunday=0?or?7)
#?|??|??|??|??|
#?*??*??*??*??*??command?to?be?executed
35 ? 01 ? * ? * ? * ? / root / mysql_backup . sh
?
完！
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
