PHP MySQL backup script?

[stealthx32]

Hey guys,

I wonder if you can help me here. I have a site that I need to transfer to my space here at Dathorn, but I'm having issues backing up the MySQL DB. The server is setup extremely poorly, and phpMyAdmin runs out of memory before I can finish the backup. If I gzip it, I can only get 4 KB of the DB. If I don't, it dies around 3 MB with a Out of memory error.

Does anyone have a script that exports to a local file, in bunches of 1000 records or so? I found a script here: http://www.ozerov.de/bigdump.php, which will import in groups of 1000, but this doesn't help me on the old server.

Oh, and its a Windows server, so I don't have any shell access.

Any ideas? Thanks.

[sdjl]

Do you have SSH or telnet access?

David

[stealthx32]

Nope, I'm limited to just FTP-ing PHP/perl/etc. scripts.

[stealthx32]

I figured out how to do this, if any of you are interested, or ever run into other poorly setup boxes w/ no shell access.

Make a php file, that issues a system command as seen below. Use output redirection to output the results to output.sql rather than to stdout (without it, your browser would just fill with the dumped code). FTP that file off the server, and on the new server, you would just do the opposite, and run "mysql -u<username> -p<password> <db_name> < output.sql", using the system() command through PHP, or on the server if you have commandline access.

<?php
  system("mysqldump -u<username> -p<password> --add-drop-table -alq <db_name> > output.sql");
?>

The key to this is using the -q switch. -q causes mysqldump to output unbuffered, so it won't load up the memory and yell at you. Oddly, it took me countless hours of searching to find a site that mentioned this.

http://jeremy.zawodny.com/blog/archives/000690.html

I'm not sure why its a big secret or why its not default, but yeah, now you know. Hacky and not elegant? Yup. But it saved my ass.

[ergo]

there is a script called backupdb() you can find it on hotscripts.com, its very good and fast solution to dump databaseses.

[ZYV]

I have written a backup script myself which dumps the db, compresses it and then mails everything to my backup site. It's a simple SH script which I will probably publish as there is demand when I get some spare time, but as a general advice I would recommend you to change every backup script you download to use the "nice" program:

nice -n 19 mysqldump ....

etc. This gives your backup process the lowest possible priority. Sure it will take much more time, BUT you are sure that your backup process does not affect servers performance and will be completed when server will have some unused ressources.

Be nice to other clients!

[ZYV]

P.S. Man entry: http://www.die.net/doc/linux/man/man1/nice.1.html