Backup Button vs mysqldump filesize

The 2.0.x discussion forum has been locked; this will remain read-only. The 3.0.x discussion forum has been renamed phpBB Discussion.
Locked
DavidWa
Registered User
Posts: 1
Joined: Mon Jun 23, 2008 3:36 am

Backup Button vs mysqldump filesize

Post by DavidWa » Mon Jun 23, 2008 3:42 am

In trying to have a better backup system for my board, I decided to go with an automated mysqldump procedure instead of only using the backup feature in the admin control panel. I am just testing this system for myself now, but I noticed a large difference in filesize between the contents of the backup made from the board (421kb) vs from mysqldump (128kb).

Is this normal behavior? Also, is there a way for me to test to see if this backup works? (other than going to the live board and using the smaller mysqldump file to restore the database, and then restoring the "board-based" backup if the mysqldump one isn't accurate)? I don't want to have a backup system in place and the find out it's not a good backup when I need it.

Thanks in advance!

User avatar
Lumpy Burgertushie
Registered User
Posts: 66588
Joined: Mon May 02, 2005 3:11 am
Contact:

Re: Backup Button vs mysqldump filesize

Post by Lumpy Burgertushie » Mon Jun 23, 2008 5:03 am

make sure that both backups have the same stuff in them.

you should have data and structure in both.

your small one may be only one or the other.

to test a backup, simply install another "test" board and run the restore on it.

robert
I'm baaaaaccckkkk. still doing work on donation basis. PM your needs.

Premium phpBB 3.2 Styles by PlanetStyles.net

If a tree falls in the forest and nobody is there, does it make a sound?

User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Re: Backup Button vs mysqldump filesize

Post by drathbun » Mon Jun 23, 2008 1:00 pm

With mysqldump you can generate "extended insert" syntax which is a HUGE benefit as far as file sizes go. The phpBB backup process generates one insert statement for each row of data. The mysqldump process can generate one insert statement for a group of rows of data and is much more compact. It's one of the many reasons why I dump the admin backup / restore routine... in fact I delete it from my server as a security measure.

Regular inserts on a 2 column table:

Code: Select all

insert into foo values (1, 1);
insert into foo values (2, 2);
insert into foo values (3, 4);
Extended inserts on the same table:

Code: Select all

insert into foo values (1, 1), (2, 2), (3, 3);
The second syntax is much smaller and faster.

Bottom line is what robert said: do the backup, and then use the file to restore on a test board. You should be doing this anyway to test your restore process.
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image

User avatar
Lumpy Burgertushie
Registered User
Posts: 66588
Joined: Mon May 02, 2005 3:11 am
Contact:

Re: Backup Button vs mysqldump filesize

Post by Lumpy Burgertushie » Mon Jun 23, 2008 1:45 pm

good point , I forgot about the extended inserts thing.

I never use it because bigdump chokes on it and I always use bigdump to restore databases.


robert
I'm baaaaaccckkkk. still doing work on donation basis. PM your needs.

Premium phpBB 3.2 Styles by PlanetStyles.net

If a tree falls in the forest and nobody is there, does it make a sound?

User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Re: Backup Button vs mysqldump filesize

Post by drathbun » Mon Jun 23, 2008 3:13 pm

Just for reference, I did a quick test with mysqldump on one of my larger databases. Using extended inserts my backup was completed in under a minute and was 70MB in size. Turning off the standard options and the backup was still completed in less than a minute, but it was 146MB in size. After gzipping the files they were almost identical is size, showing how much duplicate content was included in the larger file. :)

The bottom line was (as you - robert - suggested) you have to test the backup file, otherwise you have no idea if the backup is working. If you don't test the backup, then you might as well not bother making one. :-P
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image

Locked

Return to “2.0.x Discussion”