In Miscellaneous, by Steve
Apart from my previous article on safety features ,it is a well known fact , that it is extremely important to take backup of your MySql database of your live website.
.
Just follow below 3 steps and everything is under control :
Step 1: Configure backup directory on server
1 2 | mkdir /var/lib/mysqlbackup cd /var/lib/mysqlbackup |
.
Step 2: Make the backup script
1 | vi dbbackup.sh |
.
Paste the content as below and change the username, password and dbname as required
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | #!/bin/sh # Username to access the MySQL server USERNAME="username" # Username to access the MySQL server PASSWORD="password" # List of DBNAMES for Backup DBNAME="dbname" #date timestamp for log message DATE=`/bin/date +%Y-%m-%d_%Hh%Mm` #output file OUTDIR="/var/lib/mysqlbackup/" OUTFILE="ip_bindass".$DATE."sql.gz" #working directory DIR="/var/lib/mysqlbackup/" #cd $DIR # MySQL Backup /usr/bin/mysqldump --database $DBNAME --opt --single-transaction -u$USERNAME -p$PASSWORD | /usr/bin/gzip -9 > $OUTDIR$OUTFILE |
.
Change the permission of backup script
1 | chmod +x dbbackup.sh |
.
Step3: Schedule the backup script in crontab
1 | crontab -e |
.
Add below like to take backup everyday at 3.20 pm
1 | 20 15 * * * /var/lib/mysqlbackup/dbbackup.sh |
.
All done!
Cheers!!
Comments
Helpful solution! Thanks
Nice and clean method, but isn’t taking dumps and storing them on the same system as the db kind of a bad idea? If your hard drive crashes, you lose your backups too.
If I can suggest the following. Set up MySQL replication, to you replicate your DB onto another machine (Master Slave) . Then you can take periodic dumps on the slave server.
Good simple and clear instructions!
It’s also a good idea to get the backups off the server to a safe place. I use gpg to encrypt the backups and a python script to automatically send them to gmail, which will store them for 30 days in the bin.
Script and instructions here http://www.basshero.org/59/automatically-backup-your-database-to-gmail-gpg-encrypted/
Hey thanks for the tip. Taking backup of a wp blog is indeed very important.
Here is similar article that I wrote sometimes back that address the same issue.
http://viralpatel.net/blogs/2009/09/take-database-automate-backup-website.html
Nice post but with some good alternatives to do the same!
First you never ever pass a password to a program on the command line. Never! Every other user can now see your database password on the system by listing the active processes (ps). You do not even need an SSH account, there is even a function for that in PHP! Instead, put usernames and passwords into ~/.my.cnf, or when ran by cron, then into /root/.my.cnf like this:
[mysql]
user=username
password=pass
[mysqldump]
user=username
password=pass
This way just add -u username and omit -p on the command line and mysql* commands will look into this file for a password. Remember to set the permissions of .my.cnf properly so mysql commands can but other users can’t read it.
Second, read the manual of mysqldump before you do a backup with it. For example you must add extra arguments to save your views and stored procedures.
Additionally a backup is not a backup without a test case for restoring it. Many-many times ppl do a backup, just to find out that a lot is missing from it when the disaster already happened and they try to recover.
I agree with Michael, its better to take dumps from a slave server. Using mysqldump on a single mysql server uses a lot of resources and is slow. People think if they nice mysqldump it would help but its mysqld that does all the heavy work.
If you can lock a table then mysqlhotcopy is faster but it has a lot of limitations. You can read more about it at http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html
Good solution, a mistake is easy made in PhpMyAdmin, especially when you’re sleepy
!
Your blog is more helpful than most and it contains information that has helped me to get to grips with a problem I have had for a while now. Thank you for the del.icio.us poetry post. Regards, Gregory
thank you for share
This post seems helpful, i have bookmarked it, Good job.
Thanks for the backup script.