MySQL backups - confused

[MarkMark]

I have previously always backed up my database via Cpanel. However, I am confused about file size discrepancies.

If I download the backup via Cpanel, the resulting file (.gz) is about 3mb in size.

If I download the backup via phpmyadmin, the resulting file (.sql) is 43.5mb in size

If I view the database direct in Cpanel, it reports the database is 139mb in size.

So, am I simply looking at different levels of compression? Or am I failing to backup my database properly?

 

[Mouth]

So, am I simply looking at different levels of compression?

.sql is raw text without any compression. .gz is compressed (gzip). DB size will be allowance for growth and individual data files being larger than extracted .sql text representation.

 

[MarkMark]

.sql is raw text without any compression. .gz is compressed (gzip). DB size will be allowance for growth and individual data files being larger than extracted .sql text representation.

Very helpful, thanks. I'd expected that to be the case, but the size discrepancies were large enough to make me query it.

 

[Mr Lucky]

NB: You can also export gzipped with phpmyadmin if you enable custom display settings then choose your compression type under output.

 

[Chromaniac]

download size from cpanel should not be that large. gz and sql are going to be much much different in size from each other of course because of compression.

i would suggest comparing the content of the sql provided by phpmyadmin and cpanel to see which one is complete.

 

[TPerry]

And I'd suggest staying away (FAR FAR AWAY) from backing your forum DB up by phpMyAdmin exports.

 

[PlayStage]

Hello,

I'd suggest to backup any database using the command line:

mysqldump -u root -p database_name > database_name.sql

Kind regards,
George.

 

[Chromaniac]

then compress the sql file for saving local storage. and try to keep a copy offsite. amazon s3 is a good cheap location. i run cronjobs at low traffic time to backup the database, compress them and move to them to a s3 bucket. works well.

 

[teletubbi]

Or if you run percona, use xtrabackup.
You need more space but the backup is significant faster.
Also no impact of the forum because don`t need to lock tables.

My DB is about 5GB.
I run a full backup every midnight and incremental all 3 hours.

With mysqldump i made only a full backup each night when the forum is not so busy.
And since i have user with different time zones online is har to find the right time. Now no problem at all.