Database backup failed on Large Database

[seanspeng]

It happened that I installed phpbb together with EE within one database, and now my database grows to over 400MB. When I use the Database backkup script now, it looks busy for a few seconds, then the screen completely stops with blank screen. When I look at it, it only backs up 58 MB of database as incomplete backup. In the past I was able to back up 250MB.

Pekka, any tips to get it work with large database?

Thanks!

[MikeCaine]

Can you select just the EE tables in the database and back them up?

My gallery is down at the moment due to the recent hack and I can't check, but I'm sure you can select which tables to back up

[pukkita]

You may be hitting a (php/apache) server max execution time limit, try what MikeCaine suggests, backiing up seperate tables. Another approach would be installing phpMyAdmin.

Do you have shell access? you could try a dump (use mysqldump) of the entire database from the shell...

Another option is using MySQL Administrator, see http://dev.mysql.com/downloads/administrator/1.1.html the only caveat is you should restrict connections to the mysql server only from your IP if you don't use crypted connections.

[seanspeng]

Thanks for your reply. I do have phpmysql installed, and the server cron backup also works. I just like the added security of periodical backup from my end to the local computer.

I guess it's related to the max limitation, however, I am not sure which one, since in the past, we have it all set, now it's getting to me again.

[Pekka]

Do not backup ee_message and ee_uncookied tables. In 1.5 they might get very big if you do not clean them up in editor and you could reach couple of sql limits.

[seanspeng]

Pekka,

I have increased the max_execution_time from 60 to 600. Given the database is 400MB, I think this setting is ok. However, when I tried, the backup stops at around 58Mb again. The screen just stops without any error message, and went blank on firefox.

My question is: is there any other php variable setting that I need to adjust to make this work?

[Pekka]

Have you tried to backup only EE tables?

If the screen goes blank it usually means you ran out of PHP RAM. In php.ini try e.g.

memory_limit = 64M

[lost]

How many pictures are in your EE? I have 2000 and my db backup is only 3.27mb. If you have not yet, you may want to clear your Messages under utilites -- Messages -- then Click Clear Messages. That should bring you DB down to a manageable size and improve general performance.

EDIT: If for some reason you want to retain the old EE Messages you can deselect ee_message under "Do the backup". That would cut down the backup size but not clean up the DB.

[seanspeng]

Have you tried to backup only EE tables?

If the screen goes blank it usually means you ran out of PHP RAM. In php.ini try e.g.

memory_limit = 64M

This change got it working. Thank you!