...

View Full Version : MySQL Backup using PHP Cron



iLochie
01-16-2012, 03:46 AM
I'm looking to do a backup (probably nightly) of my database, the size is expected to grow to a substantial size. What I'd like to know, because I'm not an expert with database management by any means - if I perform a backup using a PHP script, it is likely to take some time to complete, what would be the expected time for the script to complete? Say if my database was 1 GB. Also, if data was being created in the database while the backup was being performed, could this cause problems?

For example: If a user creates a new account during the database backup, the site will create multiple rows in different tables. Is it possible the script could somehow gather information from some of these new rows and not others?

I could imagine a backup could be made with one table having an auto-increment value at 5000, and the database captures that, but then a table which is supposed to contain corresponding values only gets to 4999 when the back up is made. Can this happen?

Thanks in advance for the help.

12k
01-16-2012, 03:58 AM
1: I wouldn't use PHP to execute a backup at a given time, strictly because it wont be called unless a user visits the page.

2. It won't make a difference if a user executes a query because mysql has its own pooling built in.

3. Why not just use the built in automatic database backup that comes with mysql?

iLochie
01-16-2012, 06:26 AM
1: I wouldn't use PHP to execute a backup at a given time, strictly because it wont be called unless a user visits the page.

2. It won't make a difference if a user executes a query because mysql has its own pooling built in.

3. Why not just use the built in automatic database backup that comes with mysql?
1. Hence "Cron"
2. Ok, thanks.
3. I have to back up files on the site as well, not just MySQL, so I want 1 script doing it all.

BluePanther
01-16-2012, 08:23 AM
All your features are possible, but building in the management is complex. You would have to record the last unique id of every table, then make sure you only grab the latest ones. But, what happens if an admin deletes something in the database below the last ID you backed up? Or, if a user gets banned that is, again, below the last ID you backed up? Not just that. If an entry gets changed/modified this method won't pick it up without having to scour and compare every item in the database (or have a 'last modified' timestamp in every table, which wouldn't be so bad).

I would suggest a change in tact, and create a new 'snapshot' every time. That way, you have a snapshot of the site as it is at the time, without having to combine/compare any results. You could either archive the old snapshots, or delete them completely. That's how I would do it anyway.

djm0219
01-16-2012, 09:08 AM
If your host allows you to run commands from PHP simply use mysqldump to create a snapshot of your database. The same script may do other things too of course.

Here's an example that I've been using for years ... just change directories etc as needed. The file name of each backup contains the date so it's easy to trim older backups if/when needed (I do it from a different cron job).



$bu_File = '/home/xxx/yyy/aaa/DB-Backups/' . date("Ymd") . "-" . $db_name . ".sql";
$cmdline = "/usr/bin/mysqldump --host=$hostname --user $username -p$password --databases $db_name --skip-extended-insert --quick --result-file=$bu_File";
exec($cmdline);



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum