Forum Home
Press F1
 
Thread ID: 77020 2007-02-24 08:04:00 MYSQL Database backup stu161204 (123) Press F1
Post ID Timestamp Content User
527565 2007-02-24 08:04:00 Does anyone know if there is any program (Free) that lets you to backup a remote MYSQL Database by its self to your hard drive which I can run using "Scheduled Tasks" manger or via a batch command.

?? 		stu161204 (123)
527566 2007-02-24 08:23:00 You'd generally need to do that sort of thing on the server itself, and then write a script to download it to your PC. There's some documentation on how to do it here: wiki.dreamhost.com which you'll need to alter to suit your particular configuration.

Another way is to use the "mutt" command to email you the database backup. 		somebody (208)
527567 2007-02-24 11:28:00 You'd generally need to do that sort of thing on the server itself, and then write a script to download it to your PC.

yeath, that’s what I thought, but I just thought someone may have made a program that did it.

Thank you for your reply somebody :) 		stu161204 (123)
527568 2007-02-24 11:51:00 Stu - if you give me some details (talk on msn) I'll write you a script to do this automatically. Erayd (23)
527569 2007-02-24 11:58:00 Stu - if you give me some details (talk on msn) I'll write you a script to do this automatically.

Ok, cool :cool: , I will IM you the next time I see you online :) 		stu161204 (123)
527570 2007-02-24 23:08:00 Most hosts who give out MySQL databases usually let you have access to via PHPMyAdmin. If this option is available to you, select the database you want, click "Export" (it's up the top with the tabs), select what you want to backup and then go - it'll create a nice SQL dump for you and give you the option to download it.

Otherwise - if your host gives you SSH access into the server, why not just use the command

mysqldump --user=username -p --databases db_name1 > mydb.sql

Which will do the exact same job? 		Backlash (11925)
527571 2007-02-25 00:24:00 Most hosts who give out MySQL databases usually let you have access to via PHPMyAdmin. If this option is available to you, select the database you want, click "Export" (it's up the top with the tabs), select what you want to backup and then go - it'll create a nice SQL dump for you and give you the option to download it.

Yes I know about that, but doing that every Monday, Wed and Friday, takes time, so that’s why I was hoping for a program that could do it that for me automatically, but I will have to look at a script & using FTP (to FTP it to my computer) (which I did not want to do as FTP isn’t great on Xtra Go Large)


Otherwise - if your host gives you SSH access into the server, why not just use the command

mysqldump --user=username -p --databases db_name1 > mydb.sql

Which will do the exact same job?

Yeath, but unlucky the host I am with does not give its users SSH access.

PS: Welcome to PressF1 Backlash :D 		stu161204 (123)
527572 2007-02-25 02:25:00 Yes I know about that, but doing that every Monday, Wed and Friday, takes time, so that’s why I was hoping for a program that could do it that for me automatically, but I will have to look at a script & using FTP (to FTP it to my computer) (which I did not want to do as FTP isn’t great on Xtra Go Large)

You still have other options
PHP lets you execute commands on the server using the system() (nz2.php.net) function - so you could go for something along the lines of:

<?php
system( 'mysqldump -u [username] -p [password] database > output.sql' )
?>

And then you could just download output.txt?
Of course if you wanted to get fancier you could change the filename to be the date - but then you'd run into the fun of having to delete the output otherwise you'd end up with a nice collection of database dumps...

Or, why not try something like

<?php
mysql_query( "SELECT * INTO OUTFILE [output.sql] FROM [table]" );
?>
Though this second option only does a table, not the entire database

Of course, for both of these you'd probably want to include your database connection scripts rather than having to reconnect to your database manually just to back it up. 		Backlash (11925)
527573 2007-02-25 03:35:00 or even better.. something like this:

<?php
if($_GET['auth'] != 'yourpasshere') die('NOT AUTHORISED');
header('content-disposition: attachment; filename="database.sql.gz"');
header('content-type: application/gzip"');
echo(gzencode(shell_exec('mysqldump --host=localhost --user=youruserhere --password=yourpasshere yourdatabasehere'), 9));
?>


Then just use cron (or whatever scheduler you use) to download 'sqldump.php' (or whatever you named it) and save it somewhere. This method has the added advantage of not leaving random publicly accessable sql files on the server (because there is no file - it dumps straight to whatever app is downloading it), it is reasonably secure unless someone snoops your http traffic (needs password to download), and it also compresses the file with gzip - very handy if you have a large database. 		Erayd (23)
527574 2007-02-26 09:21:00 That last option will fail for large databases as it requires the whole thing to be in memory (both compressed and uncompressed) at once. A simple bash script can stream the results. You can use htaccess/htpasswd to password protect a directory for admin scripts.

Save this as something like dump.cgi (there must be no white space before the first line):

#!/bin/bash
#
# Database Dumper Script
#

echo "Content-disposition: attachment; filename=\"database.sql.gz\""
echo "Content-type: application/gzip"
echo ""

mysqldump --host=localhost --user=youruserhere --password=yourpasshere yourdatabasehere | gzip -cf

# End of script

P.S. This has not been tested. Use at your own risk. 		TGoddard (7263)
1 2