MYSQL Scheduled backups?

Question

I have mysql installed on my web server, which I am accessing using PHPMyAdmin. My client asked me following:

1. Install PhpBB
2. Setup MYSQL Scheduled backup

I have installed PhpBB successfully but hwo to do the 2nd one. I am using PhpMyAdmin to access mysql database, and I don't see any scheduling type of facility in phpmyadmin as well as in phpbb.

What should I do here? Please help me, Thanks!

Solution 1

Automation can be done by creating CRON jobs on your server. The following link has complete example code to backup the database automatically

http://atoztechnical.blogspot.com/2010/08/how-to-automate-database-backups.html

or

First write a script that uses mysqldump command to backup your database and store in specific directory.Now we are going to automate the process by executing this script at a particular time everyday.

Execute the following command as root user

[root@vasu /]# crontab -e

This allows you to edit the crontab file for a user. It has six fields they are

1.Minute

2.Hour

3.Day of Month

4.Month

5.Day of Week

6.Command to be executed

Add the following line to the crontab file and close it.

[root@vasu /]# crontab -e
39  19  *  *  *  backup.sh 
~    
~    
~    
~    
~    
ESC:x
crontab: installing new crontab
[root@vasu /]#

Now your backup.sh script will be executed 39 minutes, 19th hour everyday. If u want to see the list of cronjobs for a user, then execute

[root@vasu /]# crontab -l
39 19 * * * backup.sh

backup.sh :

mysqldump -h hostname -u userbane -ppassword dbname > path/to/directory/backupname.sql 

Solution 2

You might want to read the MySQL documentation on Database Backup Methods. PHPMyAdmin has an "Export" option you can use to backup your database, but there is no way to automate this. Automating a backup of MySQL usually requires creating a CRON Job that calls mysqldump. There are other ways to do it, such as with a file system snapshot, but using mysqldump will probably easier, and just as good for a PHPBB database. Also, if you're using shared hosting, you most likely will not be able to create a file system snapshot, making it a non-option. Creating a CRON Job can either be done by accessing your server over SSH, or some hosting providers provide an web interface for creating CRON Jobs

Also, you might want to check with your hosting provider. My hosting provider (dreamhost) automatically takes a backup every day, and stores the last 5 days of backups. They also provide simple options for downloading your backups, and for restoring one in case you need to.

Just a note, it's important that you regularly test that your backups to ensure they can actually be restored.

Solution 3

this is good links to start :

10 Ways to Automatically & Manually Backup MySQL Database http://www.noupe.com/how-tos/10-ways-to-automatically-manually-backup-mysql-database.html

How to setup and verify a backup solution for MySQL in 15 minutes - all using open source software http://www.zmanda.com/quick-mysql-backup.html

Comments

[Sobhan]

where would we store backup.sh file??