Monday, April 02, 2007

replicate-rewrite-db

So, in my dev environment I have 1 server to represent a particular cluster. I need to change the code to represent that a single table has been moved a new cluster. So, since I have one server I create a new database and put the table there.

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:

Anonymous said...

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?

Dathan Pattishall said...

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.

Anonymous said...

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