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:
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.
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.
Hi I tried playing with this and this guide documents what needs to be done: [URL=“Connecting orphaned .ibd files - Percona Database Performance Blog”]http://www.mysqlperformanceblog.com/...ned-ibd-files/[/URL]. However for me the percona data recovery tool was broken and returned the error “TABLE_ID […] can not be 0” which is documented here [URL=“Redirecting to Google Groups”]Redirecting to Google Groups. 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.
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)