Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Jan 2010
    Posts
    101
    Thanks
    17
    Thanked 5 Times in 5 Posts

    MySQL Backup using PHP Cron

    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.

  • #2
    12k
    12k is offline
    New Coder
    Join Date
    Jan 2012
    Posts
    29
    Thanks
    0
    Thanked 6 Times in 6 Posts
    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?

  • #3
    Regular Coder
    Join Date
    Jan 2010
    Posts
    101
    Thanks
    17
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by 12k View Post
    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.

  • #4
    Senior Coder
    Join Date
    Jul 2011
    Posts
    1,226
    Thanks
    3
    Thanked 171 Times in 171 Posts
    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.
    Useful function to retrieve difference in times
    The best PHP resource
    A good PHP FAQ
    PLEASE remember to wrap your code in [PHP] tags.
    PHP Code:
    // Replace this
    if(isset($_POST['submitButton']))
    // With this
    if(!empty($_POST))
    // Then check for values/forms. Some IE versions don't send the submit button 
    Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live.

  • #5
    Senior Coder djm0219's Avatar
    Join Date
    Aug 2003
    Location
    Wake Forest, North Carolina
    Posts
    1,302
    Thanks
    4
    Thanked 204 Times in 201 Posts
    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).

    PHP Code:
    $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); 
    Dave .... HostMonster for all of your hosting needs

  • Users who have thanked djm0219 for this post:

    iLochie (01-17-2012)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •