import mysql schema data folder instead of from a dump

Question

current state

I have prod and dev mySQL databases.

To create the dev database, I used the various schema backups generated from production and imported them like so:

mysql -u my_user -p target_db < target_db_backup.sql

This was an extremely time-consuming process (over 24 hours for some schemas). I'd like to speed it up.

ideal solution

I'd prefer to just be able to:

1. stop mysql
2. rsync production schema data folder (/var/lib/mysql/my_schema/) to the same location on dev.
3. start mysql
4. everything just works!

the issue

When I attempt to do something simple like select data, the following errors are generated:

2016-01-11 21:47:14 7f293d454780  InnoDB: Error: table 'my_schema/my_table'
InnoDB: in InnoDB data dictionary has tablespace id 1450,
InnoDB: but a tablespace with that id does not exist. There is
InnoDB: a tablespace of name my_schema/my_table and id 2451, though. Have
InnoDB: you deleted or moved .ibd files?

So I assume there's some innoDB finagling that needs to happen to get this to work.

Is it possible to just mess with innoDB settings in mysql to allow the data files in the schema to be read properly without having to import data from a dump?

Answer 1

The file ibdata1 has "tablespace ids" in it that need to correspond to the .ibd files containing the data, etc. Since these ids are generated by the instance, when you reloaded the dump, they may be different than the ones on the production machine. Hence, the rsync could be copying wrong ids.

Moral of the story: Don't mix source dumps (mysqdump, xtrabackup, etc) with file backups (rsync, etc).

Your backup is hosed; you will need to start over.

Some backup techniques (starting with least-invasive):

Zero downtime (after set up): Master-Slave; the Slave is continually updated as a viable backup.

Near zero downtime: LVM. After setting up a snapshot area, you (1) stop mysqld, (2) "snapshot" the filesystem, (3) restart mysqld, and (4) rsync the snapshot to wherever. Step 4 is the only slow part; it does not impact the running of the db other than chewing up lots of I/O.

incremental backup via binlog

Xtrabackup, with some of the non-trivial features.

Answer 2

Since MySQL 5.6 import tablespace is possible (http://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html). Since it's quite cumbersome I would only do that though if you need to 'merge' production schemas from multiple physical servers into a single dev database. Otherwise as Rick wrote replication, snapshotting or xtrabackup are all better options.