database backup options

[Rhydian]

For the database backup I think it would be good if we could select what data we want to backup to save on downloading unnecessary data for example I could select "Backup members, posts, threads likes" or Chose whether or not to backup installed themes/plugins.

 

[valdet]

Having an option similar to Paul's mod should definitely be included.

Having a cron job, to perform regular backups is essential.
It's set it and forget it

 

[mlx]

Paul's mod might work just fine in some or even most cases, but it uses PHP to dump the database.

No offense but if you are setting up a cronjob anyway, why would you want to use PHP to dump the database?

I definitely don't want to do that on our 1GiB post table.

IMHO something like AutoMySQLBackup is the way to go there.

 

[Dream]

I'd be happy with a shell script inside the /tools/ folder cause I suck at shell.

 

[Shamil]

On Linux, I use SSH with crons. On Windows, I used scheduling with mysql/bin/paths.

 

[feldon30]

A lot of people have spend years backing up from admincp, never checking the .sql dump, not realizing it timed out and they actually have an incomplete backup.

Get a VPS or better, so you have access to crontab and bash shell. Life will be good.

Sorry but requiring people to have these specs:

• $50+/month web hosting
• SSH access
• knowledge of the command line

in order to have proper backups of their forum is not right.

To offer an easy forum software package, and then expect users to have a certain kind of hosting, with SSH access (becoming rarer and rarer, not more common), and write or use command line functions to make a backup of their forum is pretty silly.

I would guess 90-95% of forum admins do not make backups because it's just too difficult, or their hosting doesn't offer such access. Or they make backups from the AdminCP (vB3) and they're incomplete.

A SQL backup is just a text file.

It is trivially easy to make a backup script that exports X number of rows, or calculates the size of different tables to export certain tables. Further, it's trivially easy to write a script that reports how many KB a backup should be, and verifies that the file is of the correct size, or even checks the end of the file to ensure that the last line is END;

I personally have had to split up SQL backups table-by-table in order to do an import on a webhost with a 30 second execution limit. Splitting text files, and directing mysqlbackup to backup small portions of your database are both very easy programming challenges.

XenForo is awesome forum software. Providing rock-solid easy backups is a place where they could excel and leave other forums in the dust.

 

[mlx]

Well, that's the thing. It's usually not rock-solid to do it via PHP.

And if you really have to do it via PHP there are already tools for that. If your hosting provider sucks you can usually still use MySQLDumper.

And as mentioned above if you have a proper hosting provider you can use AutoMySQLBackup.

This might be a little egoistic as I usually have root access to the boxes where I'm running forums, but I really don't see the importance of adding something like MySQLDumper to the XenForo core.

 

[Ingenious]

I use SSH - it works and it's quick. But I'd still rather have a reliable way of doing this within the forum admin panel

 

[feldon30]

If MySQLDumper and/or AutoMySQLBackup are of high quality and XenForo will stand behind them, then that's fine. If they are half-baked, then I'd include a backup tool that produces multiple small files.

 

[yoghurtfarmer]

Yes, Paul's Mod is really a great tool that works very well and never had issue with it.
When I moved my site to a new server I couldn't use the vB built in back-up functions as it never backed my DB fully. I then used the SSH but I had an issue with encoding in some tables and never got it right. I then used Paul's Mod and it worked very well.

Something like Paul's Mod will be a great features so that the DB will be backed-up automatically in a daily basis or whatever the admin scheduled the back-up process.

And again, I'd prefer to have that as a built-in feature, not an add-on as it's really important.

Agreed

 

[Dean]

It would be cool, not informed enough to really give an opinion though.

I can say that at this time only 1 person has Liked post #1 of this thread, at this time.

 

[Dream]

I liked the first post. Would love an automized way of backing up this if I buy xen.

 

[borbole]

For the database backup I think it would be good if we could select what data we want to backup to save on downloading unnecessary data for example I could select "Backup members, posts, threads likes" or Chose whether or not to backup installed themes/plugins.

For small forums the backup from the Acp is ok but if you have a medium/large forum then it is best to backup the db from phpmyadmin, if your host hasn''t placed file size limit on it and/or other third parties tool, like SSH/Telnet (if your host allows access), mysqldumper etc etc.

 

[Dream]

The main problem of creating a shell script to backup the database, is CLOSING your forum while the backup is being made, hence why I personally would like the backup made by the forum. Of course my boards are between small-medium size, 200mb dump.

 

[Luke F]

Why would you need to close the forum while dumping the database?

 

[borbole]

The main problem of creating a shell script to backup the database, is CLOSING your forum while the backup is being made, hence why I personally would like the backup made by the forum. Of course my boards are between small-medium size, 200mb dump.

Closing the forum or leaving it open while you are backing up the database should not be of any importance at all, unless you will perform an upgrade, move it to a different server and/or perform some other maintenance work/customization. Then you can close the forum and make a backup before you do that kind of work.

 

[Dream]

So, mysqldump locks all tables when it does the backup? If not, there could be discrepancies on the backup to my knowledge.

 

["Mark"]

So, mysqldump locks all tables when it does the backup? If not, there could be discrepancies on the backup to my knowledge.

Any proper backup solution has to backup in a consistent state. For instance, you wouldn't want to backup the threads table first, then the messages table, and have the messages table reference threads that aren't in the threads table.

To do this properly, the forum software must first make database changes in single commits. I sure hope XenForo is using InnoDB with transactional updates...

Secondly, the backup tool should make a database dump in a single transaction across all tables. This means all the tables are in a consistent state throughout the dumping process. The `mysqldump` command has the convenient option "--single-transaction". This works with transactional tables engines like InnoDB. It doesn't not work with antiquated MyISAM tables. If not using transactional tables, the only option to do a proper dump is to lock all tables, and for that mysqldump has the "--lock-tables" option.

The only reasonable exception for not using transactional tables is for temporary or reproducible data. For instance, there's no need to backup a "who's online" table, so using the MEMORY engine is advisable there.

 

[onthefly]

While I do agree with the statement that there are nothing better than SSH backups, something like the Paul's mod will be welcome by people without SSH access to the server (like the ones hosted on shared plans).

This is a great mod! i would like to see this.

 

[steven s]

A lot of people have spend years backing up from admincp, never checking the .sql dump, not realizing it timed out and they actually have an incomplete backup.

Get a VPS or better, so you have access to crontab and bash shell. Life will be good.

I used mysqldumper running religiously everyday thinking I was doing the right thing.
When I needed to restore my forum I found that the backup was not complete.

And back to the topic. Auto rotation is important.

 

[mlx]

I used mysqldumper running religiously everyday thinking I was doing the right thing.
When I needed to restore my forum I found that the backup was not complete.

And back to the topic. Auto rotation is important.

Just use AutoMySQLBackup. It supports automatic backup rotation.