Wedge
Public area => The Pub => Topic started by: markham on April 25th, 2012, 06:24 AM
-
Yesterday we learned that SMF's database backup "feature" is somewhat of a mixed-blessing - and I'm being polite here. Apparently the backup files may not necessarily be complete - contain all the records for all the tables. As it stands it serves little more than provide a false sense of security.
As I understand it, that feature was added to help those on free hosting where there may be a limited host Control Panel that doesn't include MyphpAdmin which would otherwise be used to backup and restore databases. But since SMF doesn't include a "restore" counterpart, it's all a bit half-arsed.
Now I do, I think, understand why the backup could fail and if I understand correctly, it's all about maximum execution times. But that surely is down to bad design in the first place?
Here's an idea for you. Would it not be better to have a backup system that runs in background via a scheduled task and rather than emitting a SQL "dump", it outputs table structures and data in XML format. It could do this table by table either as a complete backup or as a partial - all records added from a given date - thus allowing for incremental backups (but I do recognise that may entail time-stamping all records which may not be desirable). A complementary "restore" system would then simply prompt for a (compressed) backup file which is then decoded and updates the database. As both backup and restore would process manageable "chunks" of data, obtained via queries, the maximum execution time limit should not be exceeded.
Or am I being naive and overly simplistic? Forgive me if I am, please.
I mentioned "Repair" in the title for a reason. Would it be possible for Wedge to inspect its various tables and remove records that are incomplete or appear logically to be extraneous? My reason for asking this is simple: I appear to have a number of extra rows in the Aeva Permissions area - ie sets of permissions for membergoups that don't exist and have no membergroup name assigned and I don't know if I can safely remove them (and the only way I can see of doing that would be to edit the SQL to remove them, put the site into maintenance mode, delete that table and recreate it at the server level).
I do appreciate that an analysis/repair function is a bit of a tall order and not really necessary as a core function - although backup/restore arguable should be - and best dealt with by a plug-in.
Again, my apologies if I'm being a prat!
-
We removed that feature last year. Our thinking is that you can't trust your data to a minor admin feature. There are third party pho scripts that do a much better job at that.
-
First up, Wedge doesn't have that feature. It's problems seemingly outweighed its benefits. That said, someone could create a plugin for this if they really wanted to. I have no idea who would, though...
Now I do, I think, understand why the backup could fail and if I understand correctly, it's all about maximum execution times. But that surely is down to bad design in the first place?
thus allowing for incremental backups
Other tables would have to be absolute.
-
SMF's dumper had two main problems, and a few subsidiary ones.
Firstly, yes, it attempted to staunch the timeout, but it never reserved enough memory and especially when gzipping, it would frequently hit memory limits and truncate the backup. (That's the biggest problem.)
Improper handling of special characters is another, and one that we have fortunately simplified in Wedge's case by only having UTF-8, but even that is not really a suitable approach.
I find it interesting to note that even WordPress does not itself offer a DB backup facility, though it does allow for an export facility of sorts.
If I were interested in providing a DB backup facility, I'd bundle functionality and data together, I'd put members+permissions+groups together since odds are that would not be too huge a set of data. Then I'd bundle boards+topics+messages but that's very likely to be the hugest part of data anyway, by a large majority on most sites.
Incremental backups would be tricky to perform since while the messages table has timestamps, you have to contend with both new and modified timestamps, and the fact it is implicitly tied to the members table which would also need to be handled incrementally to make that work.
You're ultimately better off contending with a more dedicated tool - like phpMyAdmin.
-
phpMyAdmin has its own problems, at least when dealing with larger databases. (I've had so much stuff dissapear. :\) There's also the post_max_size and upload_max_filesize limits on most servers.
The only tool I've never had a problem with is mysqldump and "mysql < database.sql". Unfortunately, you can't really expect less experienced webmasters to use them.
In the end I think the export tool of Wordpress, MediaWiki and the like might be the "lest shitty" option to do this without fiddling with mysqldump. That said, I don't really have the need for such facilities.
-
Whatever you do, you're going to get into issues when trying to fudge around those limits. They're not really changeable.
Have you ever seen the output of WP's export? It really isn't actually that useful, IMO, such that I ended up eschewing it entirely, heh, just as I have with SMF's export.
-
I've only used the MediaWiki export function. It works like one would hope it would.
-
Ah, well, the WP export basically pushes out an XML file that contains the content of posts. It's not a full DB export, doesn't necessarily hold all meta data for posts, doesn't necessarily hold all the related information like post owners on multi-author blogs, which to me doesn't seem that useful.
-
How about MySQLDumper (from SourceForge)?
-
Never used it but likely that it would make a better job of it than SMF's would.
-
We use Percona as DB-Server with XtraBackup. Works like a charme. Even with incremental Backups.
-
Yes, but you're not the sort of user for who any of this is a problem. Folks like us who can administer with heavyweight tools do not have a problem because those heavyweight tools do the work. For the purpose of discussing incremental backups, we'd be talking about implementing it at the application level, nor the server level where XtraBackup works.
Folks who can use the command line have no such problems with timeouts or incomplete backups or corrupted backups, it's the poor sods who are on shared hosting who get shafted, every time.
-
How about MySQLDumper (from SourceForge)?
I myself use eskuel because it allows me to upload my dumps in a temp folder. I remember that bigdump was strong on huge databases, too.