I “inherited” 5.5 master with several 5.5 replicas with replication running for legacy reasons on STATEMENT format. Which of course is not exactly the best setting because STATEMENT based replication can for example cause different IDs on master and replica, different rows returned by query with LIMIT clause and others “funny accidents”…
Another inherited legacy problem was setting of
replicate_do_db . These too parameters can be particularly harmful – read here on Percona.
We had more and more problems especially with these too settings due to broad spectrum of applications we are using. Some changes simply were not replicated. Plus due to STATEMENT binloog format on master we had from time to time different data on replicas. Therefore we decided to remove all
*_do_db settings and switch all instances to ROW binlog format.
We tested solution described in this description and it worked. At the end we needed to restart whole master instance due to maintenance so task was a bit more simple for us.
What we did:
- Prepared all changes in MySQL config file on master.
- Locked tables on master – FLUSH TABLES WITH READ LOCK;
- Stopped replication on all replicas – STOP SLAVE;
- Restarted master with new configuration
- Started again all slaves – START SLAVE;
So far everything works and ROW based replication seems to be much quicker then STATEMENT based replication.
Plus – it works between versions too. ROW binlog from MySQL 5.5 works on 5.7 replica.