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?
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.
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.
After investigation, I observed the following:
Initial State:
Output:
At transaction number 2805, phpMyAdmin uses another "INSERT" MySQL command.
MySQL Dump uses only one (1) "INSERT" MySQL Command.
Conclusion:
The phpMyAdmin Export Tool requires more than one (1) MySQL Command, e.g. "INSERT", thereby causing the increase in file size.
In addition, we understand that each character, e.g. letter, number, increases used space in an output file.
Thank you. Peace.
