MySQL replication risks for the master

Posted on

Question :

I have a production MySQL server, fairly heavily loaded with an INSERT-only load on one of its databases. I’d like to attach a non-production (sitting on my desk, more or less) replication slave to it so I can run slow, expensive analysis queries on recently inserted data.

The slave would be outside the regular domain of our DBAs so there is a risk of it going down, running slowly, having an incompatible schema, erroring on replicated statements, etc. What risks does this pose to the availability and performance of the master?

Given my limited ability to configure the master and complete control over the slave, is there anything I can do to mitigate these risks or shift them towards the (unimportant) slave?

Answer :

If binary logging is already turned on and you are simply creating a new slave, unless you are nearing the limit of network bandwidth on the master (in which case, you have bigger problems), there are really no risks. From the master’s perspective, the slave is just a single client which happens to be grabbing binary logs instead of executing SQL.

If binary logging is not already on the master, this will add to the I/O and storage overhead on the master because each transaction will have to be written to the binary log file after it completes. This is sequential writes, though, so unless you are already nearing the limits of your I/O subsystem, it shouldn’t be a huge deal.

Leave a Reply

Your email address will not be published. Required fields are marked *