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.
I'd prefer to just be able to:
- stop mysql
rsyncproduction schema data folder (
/var/lib/mysql/my_schema/) to the same location on dev.
- start mysql
- everything just works!
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?