Duplicate a database on the same server

[gwcoffey]

I’m brand new to xtrabackup as of last night, so forgive me if this is well understood.

I wanted to use xtrabackup to duplicate a database on the same server. In other words, I have a database db_production with all innodb tables, and I want create a new database db_test on the same MySQL server as an copy of db_production.

I wrote a script that does these steps:

• back up the database with --include
• export it
• drop the new database if it exists
• create the new database
• copy the empty table definitions to the new database using mysqldump
• discards all the table spaces in the new database
• copies all the .ibd and .exp files into the new database’s folder in my data directory
• imports all the table spaces

MySQL server crashed on the last step, then failed to restart with this error:

Attempted to open a previously opened tablespace. Previous tablespace db_production/users uses space ID: 313 at filepath: ./db_production/users.ibd. Cannot open tablespace db_test/users which uses space ID: 313 at filepath: ./db_test/users.ibd

I killed all the ibd files to get it up and running again, so no major love lost.

My question is, is what I want to do possible, or does this space ID overlap problem prevent this from working in any way?

db_production is about 25GB and we have a test environment for our users which I resync every night. Because the database has grown so large, the nightly resync kills the production database and takes forever now so I was looking for a faster and less disruptive way to make the copy.

Thanks in advance for any help you can offer.

Geoff

[mirfan]

I don’t see any issues you listed above. However, there are some prerequisites innodb_file_per_table (on source & destination, both) and innodb_expand_import (on destination) should be enable on backup server. And before importing tablespace proper permission need to set on imported tables usually mysql.
BTW, are you using percona server or oracle mysql and what’s the version ?
Check following links, hope it will help.

www.percona.com/doc/percona-xtrabackup/2.1/xtrabackup_bin/exporting_importing_tables.html?id=percona-xtrabackup:xtrabackup:export_and_import
http://www.percona.com/forums/questions-discussions/percona-xtrabackup/8315-restore-one-table-from-xtrabackup-s-full-backup

Also, checking your script will help too.

[jmusbach]

Hi I tried playing with this and this guide documents what needs to be done: http://www.mysqlperformanceblog.com/…ned-ibd-files/. However for me the percona data recovery tool was broken and returned the error “TABLE_ID […] can not be 0” which is documented here https://groups.google.com/forum/#!to…on/TV3MKhm7ccE. When that happens the only solution appears to be to create fake tables until the tablespace id in the mysql server instance hopefully aligns with the tablespace you’re trying to import. Clearly not a realistic solution in most cases. So Xtrabackup really can’t feasibly be used for your solution, just for whole backup and restore of databases.

P.S. I’m not sure why the devs just say on the forum and in the KB that you can just alter the tablespace because that really isn’t the whole story. Would be nice if they also discussed dealing with tablespace IDs as unless you’re just restoring back to the same database you’re going to run into that issue.

[klaas2]

I was just thinking about opening a forum request for this question, but perhaps it’s better to continue in this one.

@gwcoffey did you had any success? I’m dealing with the same idea but can’t effort any downtime (MySQL server crash would be bad)

Perhaps others have a good suggestion or tips how to duplicate the database on the same server? (so i can use it for my development site)
(beste would be if i can simply do a mysqldump and a mysql import, but for a 100GB database live site… is a no go)