Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am looking to create a cron job to backup my MySQL database. I am using this command:

$ mysqldump --user="User" --password="Password" -A 
    --single-transaction > /home/user/Export-`date +"%F"`.sql

This results in a 2.5 MB file size. However, when I use the phpMyAdmin export feature using the same user, I get a 3.7 MB file.

Can someone please explain the differences, and if I'm doing anything wrong?

share|improve this question

2 Answers 2

DISCLAIMER : Not a User of PhpMyAdmin

I can explain what mysqldump does. When you run mysqldump, only data is dumped in SQL statements. Indexed are not copied. Indexes get rebuilt when the SQL is loaded into another server.

Here is an example

I have a query I run to show index and data usage.

Here is that query and its output

mysql> SELECT IFNULL(B.engine,'Total') "Storage Engine",
    -> CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
    -> SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
    -> FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
    -> SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
    -> FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
    -> SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
    -> (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
    -> SUM(data_length+index_length) TSize FROM
    -> information_schema.tables WHERE table_schema NOT IN
    -> ('mysql','information_schema','performance_schema') AND
    -> engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
    -> (SELECT 2 pw) A ORDER BY TSize;
+----------------+----------------------+----------------------+----------------------+
| Storage Engine | Data Size            | Index Size           | Table Size           |
+----------------+----------------------+----------------------+----------------------+
| BLACKHOLE      |             0.000 MB |             0.000 MB |             0.000 MB |
| MEMORY         |             0.000 MB |             0.000 MB |             0.000 MB |
| FEDERATED      |             0.142 MB |             0.000 MB |             0.142 MB |
| MyISAM         |             8.049 MB |             0.368 MB |             8.417 MB |
| InnoDB         |            10.828 MB |             6.766 MB |            17.594 MB |
| Total          |            19.019 MB |             7.134 MB |            26.153 MB |
+----------------+----------------------+----------------------+----------------------+
6 rows in set (0.62 sec)

mysql>

Notice that my PC has 19.019 MB of data and 7.134 MB. mysqldump will only dump the 19.019 MB of data. The indexes are not exported.

If phpmyadmin uses mysqldump, then I only have one possible explanation. mysqldump uses the option --opt by default. Here is is what --opt does

  --opt               Same as --add-drop-table, --add-locks, --create-options,
                      --quick, --extended-insert, --lock-tables, --set-charset,
                      and --disable-keys. Enabled by default, disable with
                      --skip-opt.

If phpmyadmin summons mysqldump using --skip-opt, then notice --extended-insert is not enabled. With --extended-insert enabled, a batch of rows is inserted using INSERT INTO. Using --skip-opt or --skip-extended-insert will cause each row in every table to have a separate INSERT INTO. Consequently, this would blow up the size of the SQL script. Subsequently, a reload would be longer than usual.

share|improve this answer
    
The PHPMyAdmin export has several options to decide on, if I rememebr well, so this is probably causing the difference in size. –  ypercube May 15 '12 at 20:37

you are not doing something wrong.

PHPmyAdmin and mysqlDump have different way export the file of a database, even if almost all the information is the "same".

I did a backup with Mysqldump and the same backup with phpMyAdmin, I opened it with a text editor and this is part of the text (if you make your own test you'll see the full file).

- Mysqldump

DROP TABLE IF EXISTS `default_messages`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `default_messages` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `subject` varchar(150) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,

  `message` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,

  `sender_user_id` int(11) NOT NULL,

  `reply_to_message_id` int(11) NOT NULL,

  `thread_root_message_id` int(11) NOT NULL,

  `date` datetime NOT NULL,

  `deleted` int(11) NOT NULL DEFAULT '0',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;

- PHPMyAdmin

--
-- Table structure for table `default_messages`
--
CREATE TABLE `default_messages` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `subject` varchar(150) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,

  `message` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,

  `sender_user_id` int(11) NOT NULL,

  `reply_to_message_id` int(11) NOT NULL,

  `thread_root_message_id` int(11) NOT NULL,

  `date` datetime NOT NULL,

  `deleted` int(11) NOT NULL DEFAULT '0',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

This is only with one table,If you compare both you'll see that mysqldump have a "Drop table" and phpmyadmin don't, PHPmyadmin have like more comments, both file have a lot of things different, like comments, and DDL structure, etc. That makes a different of number of character so the size of both files is not the same.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.