Database Backup
05-24-2010 @ 5:26AM EDT
This is a very simple bash script that will backup your database(s) for you each day. This can get very large in size so be sure to backup your .tar file and clean out the /root/DBDaily/ folder once and a while.
How to use it:
If you don't already have a mydir and a DBDaily folder then create them and set the permissions for the DBDaily (mkdir /mydir && mkdir /root/DBDaily && chmod 0770 /root/DBDaily). Then copy and paste the code into a new dbackkup.sh file in the /mydir folder (vi /mydir/dbackkup.sh). Don't forget to set your permissions to make the file executable (chmod 0771 /mydir/dbackkup.sh). Now you can copy and paste the following line into your crontab (type crontab -e after you SSH into your server):
5 23 * * * /mydir/dbackkup.sh 2>&1 | mail -s "Daily DB Backup"
The above line will run the script every evening at 11:05AM and will generate an e-mail with the uptime statistics.
You can adjust the time but this normally takes a few minutes to run so there is little to no impact on your CPU load.
A breakdown of this script:
#!/bin/bash <- Tells the server to run this script with the BASH interpreter.
NOW="$(date +"%m%d%Y")" <- Sets the date formatting for the script.
mkdir /root/DBDaily/$NOW/ <- Creates the new directory using the current date with the preset format.
chmod 0770 /root/DBDaily/$NOW/ <- Sets the permission of the new directory.
mysqldump -hlocalhost -ucUSERNAME -pPASSWORD DATABASENAME | gzip > /root/DBDaily/$NOW/DB1.sql.gz <- Exports a database to the new directory.
mysqldump -hlocalhost -ucUSERNAME -pPASSWORD DATABASENAME | gzip > /root/DBDaily/$NOW/DB2.sql.gz <- Exports another database to the new directory.
cd /root/DBDaily/ <- Changes the working directory.
tar -czf DBDaily.tar * <- Compresses the whole folder (including all of the created directories) so that you have a database backup of each day.
mv DBDaily.tar /backup/ <- Moves the compressed file to the /backup/ directory (the default backup folder for cPanel).
uptime <- Reports the uptime statistics to check the current CPU load after your script has run to help with scheduling.
Additional information:
You can add as many databases as you like to have backed up but don't forget to replace the USERNAME, PASSWORD, and DATABASENAME!
Code:
#!/bin/bash
NOW="$(date +"%m%d%Y")"
mkdir /root/DBDaily/$NOW/
chmod 0777 /root/DBDaily/$NOW/
mysqldump -hlocalhost -ucUSERNAME -pPASSWORD CLIENTDATBASE | gzip > /root/DBDaily/$NOW/DBClient.sql.gz
mysqldump -hlocalhost -ucUSERNAME -pPASSWORD FORUMDATABASE | gzip > /root/DBDaily/$NOW/DBForum.sql.gz
cd /root/DBDaily/
tar -czf DBDaily.tar *
mv DBDaily.tar /backup/
uptime
NOW="$(date +"%m%d%Y")"
mkdir /root/DBDaily/$NOW/
chmod 0777 /root/DBDaily/$NOW/
mysqldump -hlocalhost -ucUSERNAME -pPASSWORD CLIENTDATBASE | gzip > /root/DBDaily/$NOW/DBClient.sql.gz
mysqldump -hlocalhost -ucUSERNAME -pPASSWORD FORUMDATABASE | gzip > /root/DBDaily/$NOW/DBForum.sql.gz
cd /root/DBDaily/
tar -czf DBDaily.tar *
mv DBDaily.tar /backup/
uptime
mysql, tutorials, linux, scripts, servers
Comments
This article hasn't been commented yet.
Write a comment
* = required field