Replication enhancements in MySql 5.5

31 Jan

Recently we upgraded all our databases from MySql 5.1 to 5.5 Version and we see Couple of good enhancements done on the Replication side, couple of them i noticed are.

1) Ignore any DDL commands if they doesn’t comply with master : For ex: in a  Master-Master Setup environment

Master1 (MySql 5.1 Version)  – Slave of Master2
Master2 (MySql 5.1 Version)  – Slave of Master 1
Replica 1 (Mysql 5.5 Version)
Replica 2 (MySql 5.5 Version)

i)  we had 2 temporary tables created on Master and replicated to all the slaves where slaves are upgraded to Mysql 5.5 and Both Masters running MySql 5.1 version (bad scenario),
ii) I deleted 2 temporary tables with set Sql_log_bin=0  on master1 (not Master 2)
iii) I ran the same script on all Replication Slaves separately and Deleted temp tables.
iv) i forgot to set SQL_LOG_BIN=0 on Master2 and executed the script.  Master1  slave stopped with an error “unable to locate temp tables 1 and 2. but on Replication Slaves never been stopped or had any errors.  MySql 5.5 Version simply ignored those commands when tables doesn’t exist.

This is great enhancement.

 2) Show slave status Command now has more improvements. below variables has been added for additional verfication purposes.

i) Replicate_Ignore_Server_Ids : if a Server id has been duplicated while setting up multiple slaves, MySql now ignores server id duplication and replicates normally from master, so it wouldn’t interrupt the replication process.
ii) Master_Server_Id : identifies to which Master it is connected to.


Tags: , , , , , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: