get a snapshot of a table. I could use innodb-backup, but the table is not in it's own table space yet so I need to take a snapshot of it via mysqldump
mysqldump -uroot DATABASE TABLE --single-transaction > file.sql
Now I add the contents of file.sql to the new database and rename the old table to oldtable_bu
Okay now dev is working but production it's a bit different. I'm building new clusters out of an old single cluster. My goal is to switch writing to the old cluster that has the old database name to the new cluster that has the new database name without downtime.
To do this I need to account for the time that when I push the code there is a small window that writes to the old cluster / old database will occur as well as writes to the new environment. So, what to do? Well use a feature that doesn't get much press and will probably be deprecated soon, replicate-rewrite-db. In my /etc/my.cnf I add
Now I just want to replicate a single table. I add
I next restart mysql and check replication, it seems fine, but I want to make sure that table is actually being updated so I check the max id.
Low and behold it's not changing yet and the slave position is progressing. Sounds like a bug to me.
After a few iterations of changing ordering, trying different syntax, etc, I take the quick solution, and add this to my my.cnf
slave-skip-errors=1146 # Ignore errors where the table doesn't exist.