Backing up a huge MySQL table using PHP and a cronjob

Question

I'm working on developing a regular dump of our database. I'm using this script to create the backup and then feeding it through a regular cron job. In the end we end up with a text file as well as an email archive of everything.

The problem we've encountered is the size of two of our tables. They each have 60k fields and grow daily. I'm thinking incremental backup are the best solution for backup, but if it ever came to restoring it... It will be a huge project.

My question is a two parter:

a) Is there a more straight forward way to backup huge tables on a daily basis and, if not,

b) Is there an easy way to restore a backup from daily/weekly incremental backups?

Thanks!

Grant Limberg · Accepted Answer · 2008-11-18 04:37:20Z

9

You may wish to check out Maatkit. It's a bunch of perl scripts. One of which is mk-parallel-dump which spawns multiple copies of mysqldump (by default, 1 per CPU in the machine) allowing the dump to go MUCH faster. You can set this up in a cron job as well, like Daniel suggested.

answered Nov 18, 2008 at 4:37

Grant Limberg

21.5k11 gold badges71 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

danielrsmith · Accepted Answer · 2008-11-18 04:33:58Z

6

You can use a crob job to execute mysqldump to dump the tables down that you wish to backup. Then just use a differential backup on that file for the daily backup and do a full backup once a week. The backup's heavy lifting should be done by your backup engine of choice.

Restoring a DB is never fun, so regardless it will be a big project.

http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html

Let me know if this works for you.

edited Nov 18, 2008 at 4:33

answered Nov 17, 2008 at 21:44

danielrsmith

4,0603 gold badges29 silver badges32 bronze badges

Comments

Jon Topper · Accepted Answer · 2008-11-20 17:03:35Z

0

Consider using LVM snapshots to take periodic snapshots of your datasource, then use maatkit on the snapped copy in order to store an SQL dump. Do this on a slave MySQL db so that your backups don't cause performance problems for your live platform.

answered Nov 20, 2008 at 17:03

Jon Topper

3,0951 gold badge21 silver badges15 bronze badges

Collectives™ on Stack Overflow

Backing up a huge MySQL table using PHP and a cronjob

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related