Steps
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
[mysqld]
replicate-rewrite-db="Olddb->NewDB"
Now I just want to replicate a single table. I add
[mysqld]
replicate-rewrite-db="Olddb->NewDB"
replicate-do-table=Olddb.TABLE
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
[mysqld]
replicate-rewrite-db="Olddb->NewDB"
slave-skip-errors=1146 # Ignore errors where the table doesn't exist.
Problem solved.
3 comments:
So, in my environment, we're planning a migration and upgrade. Getting all our old 4.0 and 4.1 systems bumped to 5.0. And one of the sustaining engineering people has latched onto DRBD with all the strength his jaws can muster.
This is the first I'd heard of it. It's block level replication, so how well does it deal with cached indexes, unflushed tables, all that fun stuff? Have you worked with it at all?
I have not worked with DRBD specifically, but with similar products and I can say that since there is cached indexes, and unflushed tables replicating the data underneath the mysql process will replicate data that will appear corrupt to another mysql process from a restore. For instance a lot of companies use LVM snapshot to make backups but report that the restore of these backups are unusable for a myriad of reasons. In theory it should work, yet a lot of info is tied to what's in mysql memory and has not made it to disk - so use with caution.
Try this: I believe it works well.
You should be replicating using the new database name.
[mysqld]replicate-rewrite-db="Olddb->NewDB"
replicate-do-table=NewDB.TABLE
Post a Comment