Export database from control panel or Sequel Pro? What's the difference?

Question

Since I am involved in a multi-developer, version-controlled setup, I do a lot of database importing and exporting from environments.

I notice today that when I export a database from one of our sites using the control panel "Backup Database" function, it comes out at about 3.6MB and takes about 5 minutes to import.

When I export from Sequel Pro, the database dump comes out at about 1.6MB and imports within 30 seconds.

Note, I always dump the existing tables before import.

Just wondering what the difference is here that there is such a difference in file size and import time and which way is best?

Thanks!

Answer 1

The difference in both size and speed is likely because Sequel Pro (and any other MySQL client) is likely using mysqldump, which is a native CLI command and highly optimized.

Craft can't rely on that for a variety of reasons (PHP might have have exec permissions, the account Craft uses to connect to the database might not have the permissions necessary to perform a backup, etc.)

So Craft has it's own fallback where it manually analyzes the database's DML and DDL and writes it out to a file.

Slower? Yes. Larger file size? Yes. More reliable for Craft? Yes.

It is on our list somewhere to check if Craft has the ability to access mysqldump and use that, if not fall back to our current method.

Answer 2

Not an expert

EDIT: Brad is, see his post

From what I have noticed, the difference seems to be merely one of syntax in the way SQL dump are written. Sequel pro is more generic (and also more optimised as Brad explains). Craft creates files that are tailored for Craft and the way it works.

I have used both indifferently and never had any problem, although I tend to go with Craft generated ones lately.