Monday, April 02, 2007


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.


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.

Problem solved.


Mike L 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 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.


sexy said...



A片,色情,成人,做愛,情色文學,A片下載,色情遊戲,色情影片,色情聊天室,情色電影,免費視訊,免費視訊聊天,免費視訊聊天室,一葉情貼圖片區,情色,情色視訊,免費成人影片,視訊交友,視訊聊天,視訊聊天室,言情小說,愛情小說,AIO,AV片,A漫,av dvd,聊天室,自拍,情色論壇,視訊美女,AV成人網,色情A片,SEX