1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySQL backups - confused

Discussion in 'Server Configuration and Hosting' started by MarkMark, Aug 5, 2015.

  1. MarkMark

    MarkMark Member

    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?
     
  2. Mouth

    Mouth Well-Known Member

    .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.
     
  3. MarkMark

    MarkMark Member

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

    Mr Lucky Well-Known Member

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

    OperaManiac Well-Known Member

    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.
     
  6. Tracy Perry

    Tracy Perry Well-Known Member

    And I'd suggest staying away (FAR FAR AWAY) from backing your forum DB up by phpMyAdmin exports.
     
    Set3sh, eva2000 and BoostN like this.
  7. Set3sh

    Set3sh Active Member

    Hello,

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

    mysqldump -u root -p database_name > database_name.sql

    Kind regards,
    George.
     
    OperaManiac likes this.
  8. OperaManiac

    OperaManiac Well-Known Member

    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.
     
  9. teletubbi

    teletubbi Active Member

    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.
     
    Set3sh likes this.

Share This Page