Backing up large databases?

[R0CKY]

I have a large (35Mb+) SQL database and am struggling to find a way to back it up locally.

I am told PHP can only backup up small databases, and my host won't give me SSH access. When I try a dump via PHP I only get a 6Mb file, i.e. not the whole dbase.

If anyone can help me out with this I'd be very grateful!

[tsinka]

Hi,

you could try to execute mysqldump (system command) with php (e.g. with the system() function and then download the generated file with your ftp client.

Since mysqldump outputs to the shell per default you need to redirect the output, e.g.

mysqldump -umyuser -hdbhost -Ddatabase --all -pmypassword > targetfile.sql

If you use the php function passthru without redirecting the output of mysqldump to a file the output would be sent to the browser. In this case that wouldn't be very useful because that would transfer about 40 MB to your browser.

Please note that the above stuff only works with mysql. Another solution would be to backup each table separately with php.

[R0CKY]

Thanks for the reply. The main bulk of the dbase is in one table which is over 30Mb, so I am right in thinking those idea's wouldn't work? Or should I try the system() command? I'll need to find more info on that though I do not even know how to issue that command, I am thinking in a php webpage yeh?

[tsinka]

Hi,

yes, you can do that with a php page, e.g.

passthru("mysqldump -u theuser -h server -D mydatabase --all -p thepassword > /myhomedirectory/targetfile.sql 2>&1";

You might need to use the full path to mysqldump if mysqldump can't be found.

That would create a dump of the database and send all error messages to the browser.

The targetfile.sql can then be downloaded per ftp.

[R0CKY]

Thanks for the info I'll try it out.