Hi all
I have a mysql database that I want to automatically backup. I was told by my hosts that I can use a program called mysqldump but it doesn’t seem to be able to update another database on another server. How can I do this? thanks!
what does “doesn’t seem to be able to update another database on another server” mean?
what are you trying to do?
i use mysqldump to take backups of my live database on my web host, and then create and populate an identical database on my desktop computer
needless to say it’s not a huuuuuge database if it’ll fit on my desktop
but the process works just fine
I just want to have a copy of my database automatically stored on another server I have not on my desk top.
so did you test it?
Hi Rudy
You mean test if I can use mysqldump to automatically update another database on another server at a specific time? Well I haven’t yet unfortunately as I just want to see if it is worth even going down that route if it doesn’t do what I want. Like the ad for the roast chicken machine says I just want to “set it and forget it”. So sorry to labour the point but will mysqldump do what I want? Cheers!
yes it will
Thanks!
mysqldump doesn’t have any built-in scheduling, you’ll have to use cron or some other scheduler to fire off mysqldump at the desired time. Then just have mysqldump save it’s output as a temporary file then use the mysql program on the target server to load the dump file to the desired database on the backup server.
You can do the dump and reload in one step by piping the dump output if you wish.
But why do you want to have the backup actually reloaded in another mysql anyway? If you need some kind of real-time access to the backup data maybe you want to use replication instead (I’ve never used mysql replication but it’s available. http://dev.mysql.com/doc/refman/5.0/en/replication.html)
If you want, you can create a php file on the source machine that runs mysqldump and outputs the result:
<?php
echo `mysqldump blah parameters blah blah`;
?>
Notice I’m using back ticks. That’s how you run a shell command from php.
You can then use wget, curl, or whatever from the destination server … run via cron to take that data and update the database with that info.
Example from PHP
<?php
$dbCreateCode = `wget http://www.yoursite.com/dbbackup.php`;
// code to set up db connection
$db->execute($dbCreateCode);
That’s a VERY basic example. Sorry for my lack of detail. I’m bored out, and I’m on a tablet. I actually have the working code at home for something similar.
Thanks for the replies, I just want a back up in case my host loses everything, or they go down for a while, it is just information that I have now moved from excel to mysql so I don’t have any copies of it.
Hello,
I’m trying to set up a ‘dump and download’ procedure as discussed above.
I’ve not used ‘mysqldump’ before, and I’m having trouble working out which options I need. At present I’ve got something along these lines:
for ( $i=0; $i<count($tb_names); $i++ ) {
$do = $dump_path . "mysqldump -h" . $host . " -u" . $user . " -p" . $pass . " --opt " . $tb_names[$i] . " > " . $save_path . "/" . $tb_names[$i] . ".sql";
passthru($do,$result);
echo '<br />Do = ' . $do;
echo '<br />' . $result;
echo '<br />';
}
which I’ve copied from another thread in Sitepoint Forums (and added the ‘echo’ statements). There’s other code before that which sets up the variables, gets the table names from the DB, etc. That part seems to work OK, so I’ve not quoted it here.
If I’ve accessed the database earlier in the script, do I need to do so again here with all the -h, -u, -p stuff ?
The code as shown is creating the table files in the target folder, but they’re all empty. The echo for the first iteration is:
Do = c:/Program Files/MySQL/MySQL Server 5.5/bin/mysqldump -hlocalhost -uroot -ppassword --opt avail_2012 > d:/websites/mullbed2/backup/avail_2012.sql
1
‘avail_2012’ is the DB table name (one of several tables)
I hope that as $result is ‘1’ that indicates a successful run of ‘mysqldump’, but if so why are the files empty ?
If I run:
$do = $dump_path . "mysqldump --add-drop-database " . $data_loc . "bb_guests > " . $save_path . "/" . $today . "/dump.sql";
passthru($do,$result);
echo '<br />Do = ' . $do;
echo '<br />' . $result;
echo '<br />';
I duly get the ‘dump.sql’ file (which should contain all the tables) but it’s still empty. The echo is:
Do = c:/Program Files/MySQL/MySQL Server 5.5/bin/mysqldump --add-drop-database d:/SERVER/MySQL/data/bb_guests > d:/websites/mullbed2/backup/2012-07-12/dump.sql
1
I suspect the fact that all files are empty (no matter which version I run) indicates that neither form of $do is accessing the database properly. However it IS being accessed properly earlier in the script.
I’ve been reading the MySQL Manual (5.5), but I’m still confused about which parameters I need.
Can anyone help to set me straight, please ?
Following my last posting, I’ve now got this, which works to the point of creating the dump in a file accessible to the server:-
<?php
$dbuser = "myusername";
$dbpass = "mypassword";
$dbhost = "localhost";
$dbname = "mydatabase";
$backupfile = 'd:/websites/mullbed2/backup/' . $dbname . date("Y-m-d") . '.sql';
system("mysqldump -h $dbhost -u $dbuser -p$dbpass $dbname --single-transaction > $backupfile");
?>
Having something simple that works makes it much easier to learn more and experiment with the syntax !
yes,you can take backup automatically on anther server by using the simple code.
<?php
$user = “username”;
$pswd = “password”;
$backupfile = ‘//websites/mullbed2/backup/’ . $dbname . date(“Y-m-d”) . ‘.sql’;
system(“mysqldump -h $dbhost -u $user -p$pswd $dbname --single-transaction > $backupfile”);
?>
Hi I have this script that works to make a backup of the db on the same server as my files. No I want to save the db copy on another. Where do I change the code to add the other server? Thank you
<?php
backup_tables(‘MYSERVERIP’,‘MYUSERNAME’,‘MYPASSWORD’,‘MYDATABASENAME’);
/* backup the db OR just a table /
function backup_tables($host,$user,$pass,$name,$tables = '')
{
$link = mysql_connect($host,$user,$pass);
mysql_select_db($name,$link);
//get all of the tables
if($tables == ‘*’)
{
$tables = array();
$result = mysql_query(‘SHOW TABLES’);
while($row = mysql_fetch_row($result))
{
$tables = $row[0];
}
}
else
{
$tables = is_array($tables) ? $tables : explode(‘,’,$tables);
}
//cycle through
foreach($tables as $table)
{
$result = mysql_query('SELECT * FROM '.$table);
$num_fields = mysql_num_fields($result);
$return.= 'DROP TABLE '.$table.';';
$row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
$return.= "\
“.$row2[1].”;
";
for ($i = 0; $i < $num_fields; $i++)
{
while($row = mysql_fetch_row($result))
{
$return.= 'INSERT INTO '.$table.' VALUES(';
for($j=0; $j<$num_fields; $j++)
{
$row[$j] = addslashes($row[$j]);
$row[$j] = ereg_replace("\
“,”\
“,$row[$j]);
if (isset($row[$j])) { $return.= '”‘.$row[$j].’“’ ; } else { $return.= '”"'; }
if ($j<($num_fields-1)) { $return.= ‘,’; }
}
$return.= ");
“;
}
}
$return.=”
";
}
//save file
$handle = fopen(‘db-backup-’.time().‘-’.(md5(implode(‘,’,$tables))).‘.sql’,‘w+’);
fwrite($handle,$return);
fclose($handle);
}
?>