MySQL 导出后数据库导入错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28507503/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Database Import error after exporting
提问by aluknot
Just like title say. I am using HeidiSQL, and i wan't to duplicate a database so i export the database, create a new empty with name "test". Then i change the name of the database to "test" in the .sql file and try to import the database, then i recieve this error
正如标题所说。我正在使用 HeidiSQL，我不想复制数据库，所以我导出数据库，创建一个名为“test”的新空。然后我在 .sql 文件中将数据库的名称更改为“test”并尝试导入数据库，然后我收到此错误
Error de SQL (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right
syntax to use near '--
-- Host: ' at line 1
SQL 错误 (1064)：您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册是否正确
'-- 附近使用的语法
-- 主机：' 在第 1 行
I think is something about the first part of the sql file, which is
我认为是关于 sql 文件的第一部分，即
-- --------------------------------------------------------
-- Host:
-- Versión del servidor: 5.5.41 - MySQL Community Server (GPL) by Remi
-- SO del servidor: Linux
-- HeidiSQL Versión: 9.1.0.4867
-- --------------------------------------------------------
As far as i know its supposed to be only just comments.
据我所知，它应该只是评论。
Any suggestion?
有什么建议吗？
Thanks in advance and sorry if its an idiot question but don't know how to solve this, i searched without results. Sorry if my english is not perfect too.
提前致谢，对不起，如果这是一个白痴问题，但不知道如何解决这个问题，我搜索了没有结果。对不起，如果我的英语也不完美。
UPDATE:first 100 lines of the file
更新：文件的前 100 行
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
-- Volcando estructura de base de datos para desarrolloweb
CREATE DATABASE IF NOT EXISTS `test` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `test`;
-- Volcando estructura para tabla desarrolloweb.tqg2h_akeeba_common
CREATE TABLE IF NOT EXISTS `tqg2h_akeeba_common` (
`key` varchar(255) NOT NULL,
`value` longtext NOT NULL,
PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Volcando datos para la tabla desarrolloweb.tqg2h_akeeba_common: ~3 rows (aproximadamente)
/*!40000 ALTER TABLE `tqg2h_akeeba_common` DISABLE KEYS */;
INSERT INTO `tqg2h_akeeba_common` (`key`, `value`) VALUES
('stats_lastrun', '1419900936'),
('stats_siteid', '89afdf9407b43fb898c32abbabbdae6f4d9aefc8'),
('stats_siteurl', '94599098f96a3bbdb57077a7334fd064');
/*!40000 ALTER TABLE `tqg2h_akeeba_common` ENABLE KEYS */;
-- Volcando estructura para tabla desarrolloweb.tqg2h_ak_params
CREATE TABLE IF NOT EXISTS `tqg2h_ak_params` (
`tag` varchar(255) NOT NULL,
`data` longtext,
PRIMARY KEY (`tag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Volcando datos para la tabla desarrolloweb.tqg2h_ak_params: ~1 rows (aproximadamente)
/*!40000 ALTER TABLE `tqg2h_ak_params` DISABLE KEYS */;
INSERT INTO `tqg2h_ak_params` (`tag`, `data`) VALUES
('update_version', '1.2.0');
/*!40000 ALTER TABLE `tqg2h_ak_params` ENABLE KEYS */;
-- Volcando estructura para tabla desarrolloweb.tqg2h_ak_profiles
CREATE TABLE IF NOT EXISTS `tqg2h_ak_profiles` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`description` varchar(255) NOT NULL,
`configuration` longtext,
`filters` longtext,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
-- Volcando datos para la tabla desarrolloweb.tqg2h_ak_profiles: ~1 rows (aproximadamente)
/*!40000 ALTER TABLE `tqg2h_ak_profiles` DISABLE KEYS */;
INSERT INTO `tqg2h_ak_profiles` (`id`, `description`, `configuration`, `filters`) VALUES
(1, 'Default Backup Profile', '###AES128###+wNhUfQrnLFFGIxJfsqqq3NIRFdOAXfnDfQGV0GFrv0SEKKsrowqaqikqxO/Ej9ikG8kmDCKRq0Yt/vkM11aVkvLFYyDMXC/FilnxIkcT+BF/SNP0IGj0xT4FUmRZjUhhmBNl9cnWfIVxRioEGI/VJ4zk1TyhNIHc8tDhTwKus/ca5WB0nBe1u+N4hwMWMKOiovdCCSM7/BADHg6blaqhcBVL/J8mAE7pJApN/RmO6k3cMMuH5916XdOhJzQOL0ZmoaO3W+42VLTpZPp1vb83IJmXpsifoXSkwWQNF2Glm178VQR9ESI3uEx0yRNSuF2AAzHWMHm2GW9hBWRsiFzIxUitUQoR14wd+7Jvd6hCQPYcV7OjPyn7LBOpDpRkWoswvZINAaDzw2p3/Cmgow4M9SJmdpNv4DfBya9x1OpZv0fWZJ6kfbKkJ7hHOIluINP4laCOf18Y8JR6BKcbntuhNSYQMJLuqfdwQlbhi1dw73yzEmBjLvOlAkDdQ7oEokDZ60K0ItJz/WYfIUVvMU8YTgRYronEVr6D8uQAZHgfgXDCJRM++0gorbqs9WO1IgIsqctNgiXeaekL1Cgor6YYRaDz4Eo98Zpa+DbyAGxoTZQ0hn5SHsgDNsEug8pjVBh4P5aZ38hXndgJkjQ1KjJQSeErRzGE9bgTOyPSR9P0mCyrLWYkxBHMHE1+sHWZbLAwS3YRMY0gYi67MrFHgoWQU+jHXPQGAWXpwQQR2mGylyW5wIBhVqDrUta8sd9XQgGWeJ9z3jAbuO8KvYUQ5JWsXEvTEok0vg89565hF/9pzmHtSA95MqyqOUhsUbkQiW74PNnby4G+GWdroX6UqX+09yEPws08A4uWmQm4TwUk4eM0HOZ3CCvoe2TaUyTOmuDqHWGfWN2cltHUESVGXA16z3BxNOsCtpX/8wV4G2ZvBWAGNERcOGT0N38LDQb9jlcsUt6ZFOkaZ2rtWZY5g71ahh8tGPC4Isa3pDGJ8pIP/cs3+/hyFODw2ffoc/AfVQ6LWQxk2DBL/HPz5aNH/7kjfXgi0oTi60iMNEJ9HH6BvcVwCzIjcuZolM6XSaDlfC6b+OMMLya7gZjkpVz7nkL0W7aKYkg1ucHKTFHDB0Y3Zg/oNaElWKbj1SeQrA+R0NcjD9XiR+sRK9UgOfSxSH7LVHRhmrLIKyYsQ5vPAYmVDHXOKuN8cUNLJrPxV8n4qZpM+2PdRTyURVmnjOZPNQsdlsFo8npc4DMaTk6yeDB6LL08RH0CHcXkk3j8X4dff7QF37Z06DUHn5va81DO4kfBiCgQGa5oMdYR5uS1zQ7ndKPMuyPJKKjxqF/9DFlI0Sm47H5fLEJeLaFGPUnsIyBMITaynr9bC1bf9Sz9XR+3/DLQSEToXR1d1HNiPxF4FpuZkUgsNYp2gtLnb91tfjtlY5bH7KZMZFwXGScEqt1x/5jtJhmEqqcwGCiYUYye3hsCxM/f+4GOequJ3NyX4f3yT++ZtYRlY4SStactDS062SkpWWxDOCLLu6kVRfzB2Hjt5HkpwYBuHWHZTVARWmNWAt7THPm3E8EHv3mzwp5QCkuAZXsXsLaIEehr6QKLGIAAUGS99lrgvjx2fR5sDD7REm7KgLEN4ztQO4oJc1cZfPrLLLvNHv16kn8jvYbXWXCmfdLQZxkKfIMSYK8r2FvXNXRA8MiFRI/upkK6/b933nqZqvvZKryHhaSfvwzhq7uLR63ynjPwnKDt5Ts6FoXfdVe5O/XiL8ais4sgnKYwflvvuE49f06nxgAJkV6HGj3AeqE9SasD+VyREkTiNGzrNpnmobrTLR06+xNej6MbNpNA8AeyMaOsYiT51rbb+RQ92Lgqm4CC31/eZABOMr04AISl9UzGvTM9xf9nDFRhpa+q4KZEW0YDjiKh/oTWcPJ1qx1MTvp50sK1W2IZjPWh0QQnL5nlT5VZkjPn98SO98/3r56ij6YL82LCVyO6h5x7fbsTVX1jW/IEfdrpEkp1bHDr3ultV3KHwmQaTAIqibtyKr81dTFwuDyp9L3AY/4fCYAPyITU1eIEge0A9kVIg1Bl4mAtbny3Rza45fdHjsGcF9BLUHCRrhvWizs+BSZsxRc9XDYx9qXhRVPKKbMzZcOXNUWYZkmicsMn29ImUUhy9fvOTAFyqmTMiNlSWVkMnBgC6gILAKHf24tkiKYjREx1CYfrWx0MJkS3RdQ40ssC/J/ogEQGjoBeoxCavfXqmk124nVtqnbAyVwvxo4e01D4Ytva9h+r4BakNny07vC4S7FGhaOu8JWYp0h8+iRBCvEGk+pDbkBSHudj5JmG6BA1Eju4wyvTRHE7bp/fv293DTooPg79tDZIbcxH411mWdiry/B5k1S0J/C/quYuu8o9duIdrjrxKtzkZ35eAEKBwAA', '');
/*!40000 ALTER TABLE `tqg2h_ak_profiles` ENABLE KEYS */;
-- Volcando estructura para tabla desarrolloweb.tqg2h_ak_stats
CREATE TABLE IF NOT EXISTS `tqg2h_ak_stats` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`description` varchar(255) NOT NULL,
`comment` longtext,
`backupstart` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`backupend` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`status` enum('run','fail','complete') NOT NULL DEFAULT 'run',
`origin` varchar(30) NOT NULL DEFAULT 'backend',
`type` varchar(30) NOT NULL DEFAULT 'full',
`profile_id` bigint(20) NOT NULL DEFAULT '1',
`archivename` longtext,
`absolute_path` longtext,
`multipart` int(11) NOT NULL DEFAULT '0',
`tag` varchar(255) DEFAULT NULL,
`backupid` varchar(255) DEFAULT NULL,
`filesexist` tinyint(3) NOT NULL DEFAULT '1',
`remote_filename` varchar(1000) DEFAULT NULL,
`total_size` bigint(20) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_fullstatus` (`filesexist`,`status`),
KEY `idx_stale` (`status`,`origin`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Volcando datos para la tabla desarrolloweb.tqg2h_ak_stats: ~0 rows (aproximadamente)
/*!40000 ALTER TABLE `tqg2h_ak_stats` DISABLE KEYS */;
/*!40000 ALTER TABLE `tqg2h_ak_stats` ENABLE KEYS */;
-- Volcando estructura para tabla desarrolloweb.tqg2h_ak_storage
CREATE TABLE IF NOT EXISTS `tqg2h_ak_storage` (
`tag` varchar(255) NOT NULL,
`lastupdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`data` longtext,
PRIMARY KEY (`tag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Volcando datos para la tabla desarrolloweb.tqg2h_ak_storage: ~0 rows (aproximadamente)
/*!40000 ALTER TABLE `tqg2h_ak_storage` DISABLE KEYS */;
/*!40000 ALTER TABLE `tqg2h_ak_storage` ENABLE KEYS */;
回答by Ashwani Panwar
In my case the solution was to change character set in sql file.
Open your database .sqlfile and replace /*!40101 SET NAMES utf8mb4 */;to /*!40101 SET NAMES utf8 */;.
就我而言，解决方案是更改 sql 文件中的字符集。打开您的数据库.sql文件并替换/*!40101 SET NAMES utf8mb4 */;为/*!40101 SET NAMES utf8 */;.
回答by Anse
You should select the right file encoding when you load any SQL file in HeidiSQL. The auto-detection of the file encoding was often enough prone to errors. I already tried several approaches in the HeidiSQL code for fixing the detection but there seem to be a myriad of files outside which are not detected correctly.
当您在 HeidiSQL 中加载任何 SQL 文件时，您应该选择正确的文件编码。文件编码的自动检测通常很容易出错。我已经在 HeidiSQL 代码中尝试了几种方法来修复检测，但似乎有无数文件没有被正确检测到。
In your case, I assume the "Auto detect" setting in the file-open dialog probably misdetected an encoding which must have a BOM(2 to 4 bytes) at the very start of the file.
在您的情况下，我假设文件打开对话框中的“自动检测”设置可能误检测到在文件开头必须具有BOM（2 到 4 个字节）的编码。
So, when you open a .sql file in HeidiSQL, avoid selecting "Auto detect (may fail)" in the "Encoding" dropdown box.
因此，当您在 HeidiSQL 中打开 .sql 文件时，请避免在“编码”下拉框中选择“自动检测（可能失败）”。
回答by Marco Romano
The solution could be only upload the DB.
解决方案只能上传数据库。
If use wp: you can use a plug-in db Migration wp
如果使用wp：可以使用插件db Migration wp
Else: Other solution it's export in MYSQL40.
其他：其他解决方案，它在 MYSQL40 中导出。
in this link there are a better explanation: https://galengidman.com/2015/07/07/migrating-a-wordpress-site-from-utf8mb4-to-utf8/
在这个链接中有一个更好的解释：https: //galengidman.com/2015/07/07/migrating-a-wordpress-site-from-utf8mb4-to-utf8/
