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.
Thanks in advance for any help you can offer.