...

View Full Version : 24 Hour auto backup



masterofollies
06-20-2009, 12:28 AM
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.

tomws
06-20-2009, 04:11 AM
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 (http://www.php-mysql-tutorial.com/wikis/mysql-tutorials/using-php-to-backup-mysql-databases.aspx), 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.

masterofollies
06-20-2009, 04:08 PM
I do have SSH/Shell Access

djm0219
06-20-2009, 04:16 PM
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
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);
?>

tomws
06-20-2009, 04:55 PM
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 (http://wiki.dreamhost.com/Automatic_Backup), only modified to handle multiple databases.

masterofollies
06-24-2009, 07:54 PM
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
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?

djm0219
06-24-2009, 07:58 PM
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.

masterofollies
06-24-2009, 11:26 PM
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?

djm0219
06-25-2009, 11:45 AM
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).


$bu_File = "/home/www/xxx/Private/DB-Backups/" . date("Ymd") . "-" . $database_VHI_DB . ".sql";

masterofollies
06-25-2009, 10:11 PM
Oh wow I really need to figure out how to do this, this would be great to have.

djm0219
06-26-2009, 12:56 PM
The other part of the logic, which could be included in what I originally posted is:



$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.

djm0219
06-26-2009, 12:58 PM
Putting it all together:



<?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
?>

masterofollies
06-26-2009, 01:52 PM
Just put that in a PHP file and run it every 24 hours in a secure directory?

djm0219
06-26-2009, 02:33 PM
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):



/home/xxx/www/cgi-bin/php /home/xxx/Includes/DumpDB.php > /dev/null 2>&1

masterofollies
06-26-2009, 06:34 PM
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
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
?>

djm0219
06-26-2009, 07:36 PM
Close but you'll want to assign your database host name, database name, user and password to variables. If you look at $bu_file you'll see that the database name itself is part of the file name.

That and the other variables I mentioned are used in the $cmdline variable that will eventually be passed to mysqldump. Any place you see a _VHI_DB there's a variable you'll need to replace those (with your own names of course).

To test in/from the shell SSH in after putting the finished file where it will reside. Chances are good you'll be able to do php /full/path/to/the/file.php and have it execute.

masterofollies
06-26-2009, 07:54 PM
I think I am getting closer. Hows this looking?


<?php
error_reporting (E_ALL);
$host="localhost"; // Host name
$username="x"; // Mysql username
$password="x"; // Mysql password
$db_name="x"; // Database name


// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
$bu_File = "/ramdisk/bin/php5 -q /home/rodgameo/" . date("Ymd") . "-" . $db_name . ".sql";
$cmdline = "mysqldump --host=$hostname_VHI_DB --user $username_$db_name -p$password_$db_name --databases $db_name --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"))) . "-" . $db_name . ".sql";
if (file_exists($OldFile)) { unlink($OldFile); } # erase oldest dump file
?>

djm0219
06-26-2009, 09:05 PM
Almost there, you've left $database_ on some of the variables where they're now defined, for example, as simply $db_name instead of $database_db_name. The $bu_file, $OldFile and $cmdline variables in particular.

masterofollies
06-26-2009, 09:49 PM
Should be perfect now, want to double check?

djm0219
06-27-2009, 02:58 PM
Err, check what/where sir?

masterofollies
06-27-2009, 03:57 PM
I edited the previous post.

djm0219
06-28-2009, 09:41 AM
Ah, sorry. You've still got some old variable names in there. Try this:



$cmdline = "mysqldump --host=$host --user $username -p$password --databases $db_name --skip-extended-insert --quick --result-file=$bu_File";

masterofollies
06-28-2009, 05:25 PM
Got it, so now it's time to test.

masterofollies
06-29-2009, 12:19 AM
I get the following error.

Parse error: syntax error, unexpected T_VARIABLE in
/home/rodgameo/public_html/backup.php on line 12


<?php
error_reporting (E_ALL);
$host="localhost"; // Host name
$username="x"; // Mysql username
$password="x"; // Mysql password
$db_name="x"; // Database name


// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
$bu_File = "/ramdisk/bin/php5 -q /home/rodgameo/" . date("Ymd") . "-" . $database_$db_name . ".sql";
$cmdline = "mysqldump --host=$host --user $username -p$password --databases $db_name --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_$db_name . ".sql";
if (file_exists($OldFile)) { unlink($OldFile); } # erase oldest dump file
?>

djm0219
06-29-2009, 12:44 AM
There were several little things wrong but give this one a try (it doesn't have any syntactical errors).



<?php
error_reporting (E_ALL);
$host='localhost'; // Host name
$username='x'; // Mysql username
$password='x'; // Mysql password
$db_name='x'; // Database name


// Connect to server and select databse.
mysql_connect($host,$username,$password)or die("cannot connect");
mysql_select_db($db_name)or die("cannot select DB");
$bu_File = '/ramdisk/bin/php5 -q /home/rodgameo/' . date("Ymd") . '-' . $db_name . '.sql';
$cmdline = "mysqldump --host=$host --user $username -p$password --databases $db_name --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"))) . '-' . $db_name . '.sql';
if (file_exists($OldFile)) { unlink($OldFile); } # erase oldest dump file
?>

masterofollies
06-29-2009, 02:35 AM
I manually ran it by putting it in the public_html root to test it for errors, and there wasn't anything but a blank page which is normal. But no sql was saved?

djm0219
06-29-2009, 01:08 PM
SSH to your host and try manually running the command that is built up in the $cmdline variable and see what you get. It's possible that exec isn't allowed via a web server process but since you'll be using this as if from a cmd line it may work there.

masterofollies
06-29-2009, 04:53 PM
I couldn't figure out how to use SSH/Shell, it's insanely confusing.

djm0219
06-29-2009, 06:24 PM
Should resemble a command prompt in the Windows world. Once connected and logged in the mysqldump command should be able to be run.

maggie59
06-29-2009, 07:10 PM
BTW, SQL Server 2008 Express (http://www.microsoft.com/sqlserver/2008/en/us/compare-mysql.aspx) (the free one) includes online backup, and restore detects media errors. Scheduling is included in SSMS, no extra scripting involved.

(I know, it only runs on a proprietary platform. But it has some stuff built in that I've found helpful.)

masterofollies
06-29-2009, 08:17 PM
It won't let me connect to my website. I tried using the control panel login, but that didn't work.

mioot
06-30-2009, 09:41 AM
Check this page also and hople it will help you.
http://www.php-mysql-tutorial.com/wikis/mysql-tutorials/using-php-to-backup-mysql-databases.aspx



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum