How stable is master-slave replication in MySQL?

Question

MySQL

Hello. We have such a simple infrastructure for hosting ~150 sites (and the same number of databases):

When the combat server crashes, the monitoring script automatically switches the failover IP to the backup server. We switch back only manually when the combat server is repaired.
And they would live happily with this economy, but the trouble is: once or twice a week, for one or another database, replication errors occur. The system requires constant attention.
Please share your experience. How stable and independent replication works for you.

And, just in case, a spoiler for those who are excited about the presence of Apache ...

Please do not suggest to remove Apache, because:

• we like mpm-itk;
  
• у нас есть сайты на всяких мутных cms, для которых .htaccess за 5 минут не перепишешь;
  
• вопрос не про nginx.

Answer 1

The replica has been working for more than a year on a rather big web project on several servers. WP, HyperDB, multiple fronts. All cases of rassinka were associated with an erroneous entry in the R / O database. The replication mechanism imho is reliable, asynchronous, temporary disconnections are not a problem.
As advised in the comments, you need to look at the logs to understand why errors occur.

Answer 2

Replication errors happen quite often. There is a handy tool for data synchronization for replicas from Percona: www.percona.com/doc/percona-toolkit/2.1/pt-table-s... Also a handy plugin for monitoring replication status: www.percona.com/doc/percona -monitoring-plugins/1.0...
Alternatively, look at the possibility of using sharding .