mysqldump与还原错误有关的问题:“请在导入之前丢弃表空间" [英] mysqldump problems with restore error: 'Please DISCARD the tablespace before IMPORT'

问题描述

我运行生产数据库(mysql版本5.1.66)的每日备份mysqldump备份:

I run a daily backup mysqldump backup of the production database (mysql version 5.1.66):

mysqldump --user=username --password=secret -C -e --create-options --hex-blob --net_buffer_length=5000 databasename > file

我还每天在开发机(MySQL版本5.6.12)上恢复该数据库

I also do a daily restore of that database on my development machine (mysql version 5.6.12)

mysql --user=username --password=secret databasename < file

我得到了错误: 第25行的错误1813(HY000):表'databasename.tablename'的表空间存在.请在导入之前丢弃表空间.

I get the error: ERROR 1813 (HY000) at line 25: Tablespace for table 'databasename.tablename' exists. Please DISCARD the tablespace before IMPORT.

我的阅读表明这是因为mysql innodb数据库需要以下命令:

My reading indicates this is because the mysql innodb database requires the command:

 ALTER TABLE tbl_name DISCARD TABLESPACE;

要在删除表之前运行-似乎删除表不足以摆脱其索引. (我的开发服务器使用innodb_file_per_table选项)

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)

我不想使用替换"选项，因为我的开发数据库中可能有已在生产数据库中删除的数据.

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.

错误发生后，表不可读，但是重新启动mysqld可以解决该问题.

btw after the error the tables are not readable, but restarting mysqld fixes it.

所以问题是，是否有任何mysql dump选项可以帮助解决此问题，或者是否存在另一种导入数据的方式来防止错误发生?

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?

在此先感谢您的阅读.

推荐答案

听起来像有一个tablename.ibd但没有tablename.frm.

Sounds like you have a tablename.ibd but no tablename.frm.

要检查:

1. cd到mysql数据目录，然后是数据库名称.

1. 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.*

您应该看到两个文件:

tablename.ibd
tablename.frm

但是我猜你没有，只能看到

But I'm guessing you don't and only see

tablename.ibd

要解决此问题，您可以选择以下几种方法:

To fix you have a few options:

1. 在mysqldump中添加以下内容，这将导致在还原之前删除数据库，清理数据目录.

1. Add the follow to mysqldump, which will cause the database to be dropped, cleaning up data directory, before restore.

--add-drop-database

将tablename.frm从prod复制到dev，然后发出delete table语句.

也:

• 转储到localhost上的文件时，无需使用net_buffer_length = 5000.
• 其他备份解决方案- Percona Xtrabackup

这篇关于mysqldump与还原错误有关的问题:“请在导入之前丢弃表空间"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！