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.
Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    Senior Coder
    Join Date
    May 2005
    Posts
    2,137
    Thanks
    96
    Thanked 72 Times in 72 Posts

    24 Hour auto backup

    Is there a way to set an automatic backup to backup a mysql database? I have a website control panel, and my scripts are PHP. I would like it either backed up every 24 hours or every 48 hours.
    Rowsdower! has accused me of having mental problems, and the administrator allowed it. What a great forum huh?

  • #2
    Senior Coder tomws's Avatar
    Join Date
    Nov 2007
    Location
    Arkansas
    Posts
    2,644
    Thanks
    29
    Thanked 330 Times in 326 Posts
    You didn't mention that you have shell access, so I'll assume the easy way (mysqldump with a local cron job) is unavailable.

    Three backup options are listed here, but only the first 2 could be automated. Set them up as regular files on the site. You'll need to restrict access - via httpd conf or .htaccess, or you could even hardcode a "password" into the file.

    To actually run it, if you have access to a Linux box, just set up a cron job to hit the "page" at your regular schedule. You can even have a Windows box do it through Task Manager.
    Are you a Help Vampire?

  • #3
    Senior Coder
    Join Date
    May 2005
    Posts
    2,137
    Thanks
    96
    Thanked 72 Times in 72 Posts
    I do have SSH/Shell Access
    Rowsdower! has accused me of having mental problems, and the administrator allowed it. What a great forum huh?

  • #4
    Senior Coder djm0219's Avatar
    Join Date
    Aug 2003
    Location
    Wake Forest, North Carolina
    Posts
    1,294
    Thanks
    4
    Thanked 203 Times in 200 Posts
    Personally I use PHP which resides in a directory outside of where the web server can "see" and call it from cron once a day at 2:00 AM every day.

    It's a very simple file really and makes use of the MySQL mysqldump command to do the actual work. Example with names changed below.

    PHP Code:
    <?php
    error_reporting 
    (E_ALL);
    require_once(
    'xxx.php');  # db details are in the included file
    $bu_File "/home/www/xxx/Private/DB-Backups/" date("Ymd") . "-" $database_VHI_DB ".sql";
    $cmdline "mysqldump --host=$hostname_VHI_DB  --user $username_VHI_DB -p$password_VHI_DB --databases $database_VHI_DB --skip-extended-insert --quick --result-file=$bu_File";
    exec($cmdline);
    ?>
    Last edited by djm0219; 06-20-2009 at 04:23 PM. Reason: Added more details and sample code
    Dave .... HostMonster for all of your hosting needs

  • #5
    Senior Coder tomws's Avatar
    Join Date
    Nov 2007
    Location
    Arkansas
    Posts
    2,644
    Thanks
    29
    Thanked 330 Times in 326 Posts
    Quote Originally Posted by masterofollies View Post
    I do have SSH/Shell Access
    Then I'd use a cron job to call a script using mysqldump. I do this now with these instructions, only modified to handle multiple databases.
    Are you a Help Vampire?

  • #6
    Senior Coder
    Join Date
    May 2005
    Posts
    2,137
    Thanks
    96
    Thanked 72 Times in 72 Posts
    Quote Originally Posted by djm0219 View Post
    Personally I use PHP which resides in a directory outside of where the web server can "see" and call it from cron once a day at 2:00 AM every day.

    It's a very simple file really and makes use of the MySQL mysqldump command to do the actual work. Example with names changed below.

    PHP Code:
    <?php
    error_reporting 
    (E_ALL);
    require_once(
    'xxx.php');  # db details are in the included file
    $bu_File "/home/www/xxx/Private/DB-Backups/" date("Ymd") . "-" $database_VHI_DB ".sql";
    $cmdline "mysqldump --host=$hostname_VHI_DB  --user $username_VHI_DB -p$password_VHI_DB --databases $database_VHI_DB --skip-extended-insert --quick --result-file=$bu_File";
    exec($cmdline);
    ?>
    and where would the file end up being placed?
    Rowsdower! has accused me of having mental problems, and the administrator allowed it. What a great forum huh?

  • #7
    Senior Coder djm0219's Avatar
    Join Date
    Aug 2003
    Location
    Wake Forest, North Carolina
    Posts
    1,294
    Thanks
    4
    Thanked 203 Times in 200 Posts
    For security I have it above the web directory so the web server itself can't even see it. You really don't want something like that available to web users.
    Dave .... HostMonster for all of your hosting needs

  • #8
    Senior Coder
    Join Date
    May 2005
    Posts
    2,137
    Thanks
    96
    Thanked 72 Times in 72 Posts
    Yes I would put it before public_html. If I don't take off the copy when its saved, will it be overwrited? or will it add like [1], [2], etc to the copies?
    Rowsdower! has accused me of having mental problems, and the administrator allowed it. What a great forum huh?

  • #9
    Senior Coder djm0219's Avatar
    Join Date
    Aug 2003
    Location
    Wake Forest, North Carolina
    Posts
    1,294
    Thanks
    4
    Thanked 203 Times in 200 Posts
    The dump file name includes the date in yyyymmdd format so each day you'll get a separate file. I have another job that runs later, via cron, which removes dumps that are more than 7 days old so I always have the last 7 days available (it used to mail off a copy of the latest dump too but the db grew large enough that it may not longer be mailed).

    PHP Code:
    $bu_File "/home/www/xxx/Private/DB-Backups/" date("Ymd") . "-" $database_VHI_DB ".sql"
    Dave .... HostMonster for all of your hosting needs

  • #10
    Senior Coder
    Join Date
    May 2005
    Posts
    2,137
    Thanks
    96
    Thanked 72 Times in 72 Posts
    Oh wow I really need to figure out how to do this, this would be great to have.
    Rowsdower! has accused me of having mental problems, and the administrator allowed it. What a great forum huh?

  • #11
    Senior Coder djm0219's Avatar
    Join Date
    Aug 2003
    Location
    Wake Forest, North Carolina
    Posts
    1,294
    Thanks
    4
    Thanked 203 Times in 200 Posts
    The other part of the logic, which could be included in what I originally posted is:

    PHP Code:
    $KeepDays 6;  # how many days of dumps we'll keep on hand
    $DumpRoot "/home/xxx/www/Private/DB-Backups/";
    $OldFile  $DumpRoot date('Ymd',mktime(0,0,0,date("m"),(date("d") - ($KeepDays 1)),date("Y"))) . "-" $database_HPN_DB ".sql";
    if (
    file_exists($OldFile)) { unlink($OldFile); } # erase oldest dump file 
    That will erase the oldest dump file that is more than KeepDays old.
    Dave .... HostMonster for all of your hosting needs

  • #12
    Senior Coder djm0219's Avatar
    Join Date
    Aug 2003
    Location
    Wake Forest, North Carolina
    Posts
    1,294
    Thanks
    4
    Thanked 203 Times in 200 Posts
    Putting it all together:

    PHP Code:
    <?php
    error_reporting 
    (E_ALL);
    require_once(
    'xxx.php');  # db details are in the included file
    $bu_File "/home/www/xxx/Private/DB-Backups/" date("Ymd") . "-" $database_VHI_DB ".sql";
    $cmdline "mysqldump --host=$hostname_VHI_DB  --user $username_VHI_DB -p$password_VHI_DB --databases $database_VHI_DB --skip-extended-insert --quick --result-file=$bu_File";
    exec($cmdline);
    $KeepDays 6;  # how many days of dumps we'll keep on hand
    $DumpRoot "/home/www/xxx/Private/DB-Backups/";
    $OldFile  $DumpRoot date('Ymd',mktime(0,0,0,date("m"),(date("d") - ($KeepDays 1)),date("Y"))) . "-" $database_VHI_DB ".sql";
    if (
    file_exists($OldFile)) { unlink($OldFile); } # erase oldest dump file  
    ?>
    Dave .... HostMonster for all of your hosting needs

  • #13
    Senior Coder
    Join Date
    May 2005
    Posts
    2,137
    Thanks
    96
    Thanked 72 Times in 72 Posts
    Just put that in a PHP file and run it every 24 hours in a secure directory?
    Rowsdower! has accused me of having mental problems, and the administrator allowed it. What a great forum huh?

  • #14
    Senior Coder djm0219's Avatar
    Join Date
    Aug 2003
    Location
    Wake Forest, North Carolina
    Posts
    1,294
    Thanks
    4
    Thanked 203 Times in 200 Posts
    Yep, as simple as that. You will of course want to run it from the shell using the same call the cron will to make sure that exec will work and that mysqldump is available in the path.

    On my server PHP runs as a CGI so my complete call from cron looks like this (directories changed obviously):

    Code:
    /home/xxx/www/cgi-bin/php /home/xxx/Includes/DumpDB.php > /dev/null 2>&1
    Dave .... HostMonster for all of your hosting needs

  • #15
    Senior Coder
    Join Date
    May 2005
    Posts
    2,137
    Thanks
    96
    Thanked 72 Times in 72 Posts
    Hmm I've never used Shell before, so I guess I will have to read about that.

    I got this far, is this anywhere near correct?

    PHP Code:
    <?php
    error_reporting 
    (E_ALL);
    mysql_connect('localhost''user''pass') or die(mysql_error());
    mysql_select_db('database') or die(mysql_error());

    $bu_File "/ramdisk/bin/php5 -q /home/rodgameo/" date("Ymd") . "-" $database_VHI_DB ".sql";
    $cmdline "mysqldump --host=$hostname_VHI_DB  --user $username_VHI_DB -p$password_VHI_DB --databases $database_VHI_DB --skip-extended-insert --quick --result-file=$bu_File";
    exec($cmdline);
    $KeepDays 6;  # how many days of dumps we'll keep on hand
    $DumpRoot "/ramdisk/bin/php5 -q /home/rodgameo/";
    $OldFile  $DumpRoot date('Ymd',mktime(0,0,0,date("m"),(date("d") - ($KeepDays 1)),date("Y"))) . "-" $database_VHI_DB ".sql";
    if (
    file_exists($OldFile)) { unlink($OldFile); } # erase oldest dump file
    ?>
    Last edited by masterofollies; 06-26-2009 at 06:39 PM.
    Rowsdower! has accused me of having mental problems, and the administrator allowed it. What a great forum huh?


  •  
    Page 1 of 3 123 LastLast

    Posting Permissions

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