MySQL mysqldump 问题与还原错误：“请在导入之前丢弃表空间”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17914446/
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
mysqldump problems with restore error: 'Please DISCARD the tablespace before IMPORT'
提问by pgee70
I run a daily backup mysqldump backup of the production database (mysql version 5.1.66):
我运行生产数据库的每日备份 mysqldump 备份（mysql 版本 5.1.66）：
mysqldump --user=username --password=secret -C -e --create-options --hex-blob --net_buffer_length=5000 databasename > file
I also do a daily restore of that database on my development machine (mysql version 5.6.12)
我还在我的开发机器上每天恢复那个数据库（mysql 版本 5.6.12）
mysql --user=username --password=secret databasename < file
I get the error:
ERROR 1813 (HY000) at line 25: Tablespace for table 'databasename.tablename' exists. Please DISCARD the tablespace before IMPORT.
我收到错误：ERROR 1813 (HY000) at line 25: Tablespace for table ' databasename。tablename' 存在。请在 IMPORT 之前丢弃表空间。
My reading indicates this is because the mysql innodb database requires the command:
我的阅读表明这是因为 mysql innodb 数据库需要命令：
ALTER TABLE tbl_name DISCARD TABLESPACE;
to be run before the table is dropped -- it seems that dropping the table isn't sufficient to get rid of its indexes. (my development server uses the innodb_file_per_table option)
在删除表之前运行 - 似乎删除表不足以摆脱其索引。（我的开发服务器使用 innodb_file_per_table 选项）
I don't want to use 'replace' option because i could potentially have data in my development database that was deleted on the production database.
我不想使用“替换”选项，因为我的开发数据库中可能有在生产数据库中被删除的数据。
btw after the error the tables are not readable, but restarting mysqld fixes it.
顺便说一句，错误后表不可读，但重新启动 mysqld 修复了它。
So the question is, is there any mysql dump option that will help fix this issue, or is there another way to import the data that will prevent the error?
所以问题是，是否有任何 mysql 转储选项可以帮助解决此问题，或者是否有其他方法可以导入数据以防止出现错误？
thanks in advance for reading.
提前感谢您的阅读。
回答by Dave
Sounds like you have a tablename.ibd but no tablename.frm.
听起来你有一个 tablename.ibd 但没有 tablename.frm。
To check:
去检查：
- cd to your mysql data directory then the database name.
cd /var/lib/mysql/database_name
Search for the table name that is giving the error.
ls tablename.*
You should see two files:
tablename.ibd tablename.frm
But I'm guessing you don't and only see
tablename.ibd
- cd 到您的 mysql 数据目录，然后是数据库名称。
cd /var/lib/mysql/database_name
搜索给出错误的表名。
ls tablename.*
您应该看到两个文件：
tablename.ibd tablename.frm
但我猜你不会，只会看到
tablename.ibd
To fix you have a few options:
要修复您有几个选项：
- Add the follow to mysqldump, which will cause the database to be dropped, cleaning up data directory, before restore.
--add-drop-database
- Copy the tablename.frm from prod over to dev and then issue a delete table statement.
- 将以下内容添加到 mysqldump 中，这将导致数据库被删除，在恢复之前清理数据目录。
--add-drop-database
- 将 tablename.frm 从 prod 复制到 dev，然后发出删除表语句。
Also:
还：
- No need to use net_buffer_length=5000 when you're dumping to a file on localhost.
- Other backup solutions - Percona Xtrabackup
- 当您转储到本地主机上的文件时，无需使用 net_buffer_length=5000。
- 其他备份解决方案 - Percona Xtrabackup
回答by SamTzu
I found the easiest way to skip this problem was to manually edit phpmyadmin database dump and edit/change the table that had problems to something else than INNODB. I changed the problem table to ENGINE=MyISAMand voila. Import worked.
我发现跳过这个问题的最简单方法是手动编辑 phpmyadmin 数据库转储并将有问题的表编辑/更改为INNODB. 我把问题表改成了ENGINE=MyISAM，瞧。进口工作。
CREATE TABLE IF NOT EXISTS `home3_acymailing_tag` (
`tagid` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(250) NOT NULL,
`userid` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`tagid`),
KEY `useridindex` (`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
回答by ?MER TA?CI
I also encountered that problem while dropping a schema and creating it again. I overcome this issue by going C:\ProgramData\MySQL\MySQL Server 5.6\data\my_database_nameand deleting the tables which remained from the previous database creation. You can also delete the entire database, if you wish.
我在删除架构并再次创建它时也遇到了这个问题。我通过C:\ProgramData\MySQL\MySQL Server 5.6\data\my_database_name删除之前创建数据库时留下的表来解决这个问题。如果愿意，您也可以删除整个数据库。
回答by Noman Hassan
if you are using XAMPP then first ("stop") MySQL Then go to C:\xampp\mysql\data\dnb where in my case dnb is my database name folder. so then open it and delete .ibd file hence you can only delete it when you already stop MYsql . then go to phpmyadmin 1 click on phpmyadmin . 2 click on databases that appear below (server.127.0.0.1 in your case my be change) 3 then check your database which you want to drop,and click on drop. 4 then you can create database with same name and import your database successfully .here you can see how you drop database from phpmyadmin
如果您使用的是 XAMPP，则首先（“停止”）MySQL 然后转到 C:\xampp\mysql\data\dnb，在我的情况下 dnb 是我的数据库名称文件夹。所以然后打开它并删除 .ibd 文件，因此你只能在你已经停止 MYsql 时删除它。然后转到 phpmyadmin 1 单击 phpmyadmin 。2 单击下面出现的数据库（server.127.0.0.1 在您的情况下我是更改） 3 然后检查您要删除的数据库，然后单击删除。4 然后你就可以创建同名的数据库并成功导入你的数据库了。在这里您可以看到如何从 phpmyadmin 中删除数据库
