CODEWITHSUNDEEP
cronphpmyadminbackupmysql
Laurence
Laurence

Reputation: 60048

mysqldump file imports slowly compared to phpmyadmin file

I have a large mySQL database that I backup each night via a cron job:

/usr/bin/mysqldump --opt USERNAME -e -h SERVERNAME -uUSER -pPASSWORD > /home/DIRECTORY/backup.sql

It is working well - except when I go to 'restore' the sql file on another server - it takes a long time (about 3 mins)

This is in contrast to using phpMyAdmin - if I do "export" and export the same mySQL database, then import that sql file into another server it only takes 10 seconds.

Question: how do I make "mysqldump" create the same type of sql file that "phpMyAdmin" does?

Example of some FAST version sql (not all of it):

CREATE TABLE IF NOT EXISTS `absence_type` (
  `absence_type_ID` int(16) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`absence_type_ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=14 ;

--
-- Dumping data for table `absence_type`
--

INSERT INTO `absence_type` (`absence_type_ID`, `name`) VALUES
(1, 'Sick Leave'),
(2, 'Personal Carers'),
(3, 'Other');

Example of some SLOW version sql (not all of it):

        --
    -- Table structure for table `absence_type`
    --

    DROP TABLE IF EXISTS `absence_type`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `absence_type` (
      `absence_type_ID` int(16) NOT NULL AUTO_INCREMENT,
      `name` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
      PRIMARY KEY (`absence_type_ID`)
    ) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
    /*!40101 SET character_set_client = @saved_cs_client */;

    --
    -- Dumping data for table `absence_type`
    --

    LOCK TABLES `absence_type` WRITE;
    /*!40000 ALTER TABLE `absence_type` DISABLE KEYS */;
    INSERT INTO `absence_type` VALUES
 (1,'Sick Leave'),
 (2,'Personal Carers'),
 (3,'Other');
    /*!40000 ALTER TABLE `absence_type` ENABLE KEYS */;
    UNLOCK TABLES;

Upvotes: 1

Views: 756

Answers (1)

Jason McCreary
Jason McCreary

Reputation: 72991

From my comments…

Likely the options between mysqldump and PHPMyAdmin export don't match. For example, inclusion of DROP TABLE, extended INSERT, etc.

I suggest comparing the two files. I'm sure there is something obvious. Then either adjust the options for mysqldump or in PHPMyAdmin. Either should work as the latter uses mysqldump underneath.

Upvotes: 1

Related Questions