[RFC] Staggered Database Restore (like bigdump)

[MattF]

For those of us with large databases, say greater than 50MB, phpBB's built-in database backup/restore function is not useful.

The backup option works great. But you can not use the restore feature for two reasons, under most hosting situations:
1) Database may be too large to upload via the ACP.
2) Database may be too large to restore in one go, and php execution time will expire before completion.

This usually means we need to use phpMyAdmin to make an export, then upload it to the server via ftp, and then run bigdump to restore it.

I propose phpBB's restore function offer a "staggered restore" option for large databases.

So, lets say you are making your backups as .gz files stored locally to your server.
Why can't phpBB's restore option decompress the selected .gz backup, and then restore it in a method like bigdump, say by processing 3000 lines/queries at a time?

I think this would make the built-in phpBB backup/restore function much more complete (and it could be added to stk too for situations where there has been a serious database melt-down).

[EXreaction]

Bigdump will not help on large database backups through the backup panel as we use extended inserts.

You really want to use the sql tools provided by your database system to import large databases or ask your host to do the import for you.

[keith10456]

Bigdump will not help on large database backups through the backup panel as we use extended inserts.

You really want to use the sql tools provided by your database system to import large databases or ask your host to do the import for you.

But what about the question at hand? Would using a "staggered restore" inside of phpbb be a better option for very large dbs?

[EXreaction]

No, it would be the same problem.

For large boards you should ask your host to do it or do it yourself if you have ssh access, it takes a fraction of the time and is quite easy.

[keith10456]

Question:

BigDump doesn't use "extended inserts"... Why does phpBB use them?

I'm not comparing the two... I'm genuinely interested in the benefit of using or not using them in reference to phpBB.

[EXreaction]

Extended inserts make the size of the backup file much smaller and they also import faster.

[keith10456]

Extended inserts make the size of the backup file much smaller and they also import faster.

Thanks

[3Di]

Extended inserts make the size of the backup file much smaller and they also import faster.

so, perhaps, it could be an option to use them or not and act then as big-dump ?!? mah..

[MattF]

I have moved on to using Mysqldumper which is great.

Now if phpBB built in that sort of functionality - that would really be something!!!
-Scheduled auto-backups
-User defined number of backups to keep
-Uses javascript and php to stagger the back-up and restore processes to avoid script execution timeouts and memory limits
-Can read/write directly to gzip format without having to unpack on each pass
-Email notifications, email and ftp delivery of the backups

Maybe this is a lot to ask to see in phpBB, but hey if you are gonna offer any kind of backup/restore feature, you might as well make it fully featured. I mean this is one board feature you don't want to be let down by! DB backup/restore is kind of important, no?

[Louis7777]

Um, ressurecting this one as I consider it an important one too.

If it is too much right now for 3.1, could it be possible to implement it for 3.2?