Database backups for selected tables only

Do not post support requests, bug reports or feature requests. Discuss phpBB here. Non-phpBB related discussion goes in General Discussion!
Ideas Centre
Post Reply
User avatar
Marek Muc
Registered User
Posts: 127
Joined: Tue Sep 04, 2018 2:59 pm
Location: Warsaw, Poland
Contact:

Database backups for selected tables only

Post by Marek Muc »

Do you create database backups for selected tables only? I've always clicked 'select all', but I wonder - is there any benefit in having separate backups of specific tables only?
User avatar
KevC
Support Team Member
Support Team Member
Posts: 72343
Joined: Fri Jun 04, 2004 10:44 am
Location: Oxford, UK
Contact:

Re: Database backups for selected tables only

Post by KevC »

Always do all tables.

They are interconnected so if you had to restore with only some of them, the board will not work.
-:|:- Support Request Template -:|:-
Image
"Step up to red alert. Sir, are you absolutely sure? It does mean changing the bulb"
User avatar
david63
Registered User
Posts: 20646
Joined: Thu Dec 19, 2002 8:08 am

Re: Database backups for selected tables only

Post by david63 »

I always backup all tables but you can ignore the sessions and search tables as they can easily be rebuilt
David
Remember: You only know what you know and - you don't know what you don't know!

I now no longer support any of my extensions but they will start to become available here
User avatar
P_I
Community Team Member
Community Team Member
Posts: 2353
Joined: Tue Mar 01, 2011 8:35 pm
Location: Western Canada 🇨🇦
Contact:

Re: Database backups for selected tables only

Post by P_I »

Our standard practices has to perform two nightly mysqldumps. The first one has all tables but excludes the two large search tables and the second one is just the two search tables.

The resultant two files are compressed and then copied to two geographically different offsite locations.

Once a month we take a full site backup of all the files, excluding any cache directories.
Normal people… believe that if it ain’t broke, don’t fix it. Engineers believe that if it ain’t broke, it doesn’t have enough features yet. – Scott Adams
User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 5871
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.
Contact:

Re: Database backups for selected tables only

Post by thecoalman »

Backing up individual tables may be helpful for those experiencing issues with php limits corrupting the backup. You can back up the largest tables like the post and search tables individually. Select the rest of them as one backup. If you are having these issues you really need to look into using SSH instead as it takes php limitations out of the picture.

I do full backup every night, I have been meaning to include incremental backup. The backup itself is handled by automysqlbackup which I have been using very successfully for many years.

https://sourceforge.net/projects/automysqlbackup/

Not sure if it's possible to run that under shared hosting. It has multiple options including FTP to backup serveice like AWS, encryption of the backup or even emailing it. Of course emailing may not work with really large files because they may be rejected by incoming email server.

It's configurable but each day it creates a backup, the daily backups are rotated out so you have the last 7 days. Weekly backups are rotated out every 5 weeks and montly backups are rotated out every six months with default configuration. In my case they are saved to non public folder on the server, using Windows scheduler I schedule WinSCP to download the latest backup each night.

Any time I'm editing, updating or doing anything with phpBB files locally I always populate the local database with the latest backup to test it.
“Results! Why, man, I have gotten a lot of results! I have found several thousand things that won’t work.”

Attributed - Thomas Edison
User avatar
warmweer
Jr. Extension Validator
Posts: 11234
Joined: Fri Jul 04, 2003 6:34 am
Location: Van Allen Bel ... gium
Contact:

Re: Database backups for selected tables only

Post by warmweer »

Marek Muc wrote: Mon Apr 05, 2021 9:42 am Do you create database backups for selected tables only? I've always clicked 'select all', but I wonder - is there any benefit in having separate backups of specific tables only?
Actually quite regularly (for purpose of comparison with a default table).
It's also quite handy when a change has been made to 1 table only >>> export 1 table, and import only 1 table. This is particularly handy when having to "work" on a board without phpMyAdmin access.
Granted, this is a borderline case but I've had a couple of cases where I did get FTP (and Founder) access from the boardowner, but no phpMyAdmin access.
Spelling is freeware, which means you can use it for free.
On the other hand, it is not open source, which means you cannot change it or publish it in a modified form.


Time flies like an arrow, but fruit flies like a banana.
User avatar
Marek Muc
Registered User
Posts: 127
Joined: Tue Sep 04, 2018 2:59 pm
Location: Warsaw, Poland
Contact:

Re: Database backups for selected tables only

Post by Marek Muc »

All clear, thank you for all replies!
Post Reply

Return to “phpBB Discussion”