1

I have two MySQL servers configured in a multi-master replication setup. Each is a slave and master to the other. My application requires that some large queries be ran in the background, and the results of these queries would be used to populate a table. I figured, I could have these large queries run on one server, and the application front-end use the other. This way, the application wouldn't be slowed while the server is running these queries.

These queries are very large INSERT .... SELECT. With my replication setup, it seems that when one server finishes the query, instead of just sending the INSERTs to the slave, it has the slave run the original large INSERT/SELECT.

Is this actually happening? Or is there a way to see what commands were sent to the slave from the master to verify this is the behavior? The only way I can tell is from CPU load.

Is there a way for the slave to only get the resulting INSERT from an INSERT... SELECT ran on the master?

Brad
  • 159,648
  • 54
  • 349
  • 530

2 Answers2

5

Is this actually happening?

Probably.

Or is there a way to see what commands were sent to the slave from the master to verify this is the behavior?

Well, you could deconstruct the binlog, but I expect that reading up on replication format options will be much less headache-inducing.

You're probably in statement-based mode which has been the default for ages. You want to be in row-based mode or hybrid mode, if those INSERT INTO ... SELECT statements are a pain. These options are available only in MySQL 5.1 or better.

Charles
  • 50,943
  • 13
  • 104
  • 142
  • 1
    A warning about row-based replication: The binary logs generated are usually a lot bigger that with statement-based. – RolandoMySQLDBA Mar 13 '11 at 22:44
  • You can use a tool such as mytop that will show you queries running on both MASTER and SLAVE. You can also directly run SHOW PROCESSLIST; There's a trade-off to consider between statement-based and row-based. I wanted to write about those but the link above is good enough reading material. – Collector Feb 09 '12 at 06:49
0

The actual queries are run. The only way around you INSERT...SELECT is to break them up yourself. Run the select, store the result in memory, then do a bulk insert.

Brent Baisley
  • 12,641
  • 2
  • 26
  • 39