Reputation: 23
When I export my SQL database via the Export tab in phpMyAdmin I do a Custom export and use all the defaults with two additions:
Using the following import command, I can import the generated export file into my local SQL database successfully:
const importCommand = `${sqlPath}/mysql -u[user] -p[password] ${database} < "${file}"`;
I have an export command to automate this that uses mysqldump
:
const command = `mysqldump --user='${user}' --password='${password}' ${database}`;
This works to export the database but it gives me unreliable results when importing. Sometimes it works fine, other times the import fails. When I export through phpMyAdmin using the steps above, import always works.
So my question is, does phpMyAdmin use mysqldump
behind the scenes for export? If so, how can I find it's default mysqldump
options?
Alternatively, I believe what makes this work is the Truncate table before insert
option. According to docs and other sources mysqldump
doesn't have an --add-truncate-table
option, or equivalent.
Docs for mysqldump
: https://dev.mysql.com/doc/refman/8.4/en/mysqldump.html
e.g. structure of custom export file from phpmyadmin (works):
-- --------------------------------------------------------
--
-- Table structure for table `cb_commentmeta`
--
DROP TABLE IF EXISTS `cb_commentmeta`;
CREATE TABLE `cb_commentmeta` (
`meta_id` bigint(20) UNSIGNED NOT NULL,
`comment_id` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
`meta_key` varchar(255) DEFAULT NULL,
`meta_value` longtext DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
--
-- Truncate table before insert `cb_commentmeta`
--
TRUNCATE TABLE `cb_commentmeta`;
e.g. structure of export through mysqldump
command (import fails):
DROP TABLE IF EXISTS `cb_commentmeta`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `cb_commentmeta` (
`meta_id` bigint(20) unsigned NOT NULL,
`comment_id` bigint(20) unsigned NOT NULL DEFAULT 0,
`meta_key` varchar(255) DEFAULT NULL,
`meta_value` longtext DEFAULT NULL,
PRIMARY KEY (`meta_id`),
KEY `comment_id` (`comment_id`),
KEY `meta_key` (`meta_key`(191))
);
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40000 ALTER TABLE `cb_commentmeta` DISABLE KEYS */;
/*!40000 ALTER TABLE `cb_commentmeta` ENABLE KEYS */;
Import fails due to ERROR 1064 (42000) at line 62083: You have an error in your SQL syntax
. Notice key differences like the ALTER TABLE comments and the PRIMARY KEY, and others KEY statements that aren't in the first file. I'm wondering how to get the same format using mysqldump
.
Upvotes: 0
Views: 39