Backup database to file with CakePHP or normal PHP

I wrote a script to dump my database into a file but it involved querying the database, storing the results in an array and then using fopen and fwrite to write the file.

Since my database is very large it tries to allocate too much memory and fails.

Does anyone know a simple way to backup my database to an SQL file the same as a MySQL export would do, that won't use 开发者_运维技巧too much memory?

If you can run external programs on the server, you can try mysqldump - it makes a dump of the database specified, as a text file.

Example:

mysqldump -u someuser -p somepassword yourdatabase > /some/path/dump.sql

Yes use the mysqldump command, in PHP you can call it like:

shell_exec("mysqldump dbname > /some/path/dump.sql");

An ideal solution would be to use the mysqldump command line tool, as this is very low memory (especially if you're calling it via system/exec, etc.) and is considerably more efficient that any "in-script" solution.

Why reinvent the wheel? mysqldump is perfectly functional.

I've wrote a cakephp console application which you can use to backup your database using a cron job or manually of course. https://bitbucket.org/mmahgoub/cakephp-backupme/

Just type the command cake backup

You can specify the rows returned per iteration to limit memory usage

cakephp backup default 1000

but it will take more time of course.