Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.4k views
in Technique[技术] by (71.8m points)

mysql - mysqldump problems with restore error: 'Please DISCARD the tablespace before IMPORT'

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

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

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

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

 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)

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.

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?

thanks in advance for reading.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

To check:

  1. cd to your mysql data directory then the database name.
    cd /var/lib/mysql/database_name
  2. 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

To fix you have a few options:

  1. Add the follow to mysqldump, which will cause the database to be dropped, cleaning up data directory, before restore.
    --add-drop-database
  2. Copy the tablename.frm from prod over to dev and then issue a delete table statement.

Also:

  • No need to use net_buffer_length=5000 when you're dumping to a file on localhost.
  • Other backup solutions - Percona Xtrabackup

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...