Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In MySQL, when does the master send a query to the slaves?

Here's a specific scenario that I'm trying to understand.

  • Admin entered an update on master that took a long time to execute.
  • Admin hits Ctrl-C to stop the query.

I see four scenarios that may have happened:

  1. Nothing happened on the slave, because master waits until the query is finished executing before it sends the query to the slave.
  2. A huge update happened on the slave, because the master passed the query along immediately, and didn't pass the ^C along to the slave.
  3. The same update happened on the slave as the master, because the master passed the query along immediately, and also passed the ^C along to the slave.
  4. The same updates happened on the slave as the mater, because the master passed each individual update along to the slave.

Which one of these actually happened ?

Thanks so much,

Alan

share|improve this question

1 Answer 1

up vote 2 down vote accepted

This is a local phenomenon within the MySQL Replication Architecture. Here is a sample message that can sometimes appear when you interrupt a query on a Master

Query partially completed on the master (error on master: 1053) and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; . Query: (INSERT vhc (v_id, vc_id) SELECT 7421594, vc_id FROM vc WHERE label='st' AND value='re')

Error 1053 is a Server Shutdown. Obviously, the intent was not to shutdown mysql and not tell connected Slaves. Hitting Ctrl-C on a Master would kill the DB Connection. However, such disconnects are simply interpreted as a shutdown. In my old post Does MySQL support replicating all databases?, I described how MySQL replicates from Master to Slave:

  • Master has Binary Logging enabled
  • Master records completed SQL Statements
  • Master will inject 'USE dbname;' among the SQL Statements it records
  • Slave connects to Master (IO Thread)
  • Slave IO Thread requests next SQL statement from the Binary Logs
  • Slave IO Thread copies it to its Relay Logs
  • Slave SQL Thread Processes its Relay Log Entries FIFO (Queue)

Looking back at the error message I described, the Slave detected that a binlog event in its relay logs that came from the Master. It was anticipating another event with the actual SQL saying everything went well on the Master. Error 1053 (the phantom shutdown caused by Ctrl-C) came up instead. This will give you the chance to say OH MASTER HAD THAT QUERY INTERRUPTED. I"LL GO LOOK AT THE DATA TO SEE IF THEY MATCH.

When it comes to your actual question, I would go with #1

Nothing happened on the slave, because master waits until the query is finished executing before it sends the query to the slave.

but with a caveat

Since Ctrl-C was done on the Master, the Master recorded the SQL in the binlog along with error 1053. The Slave intercepted this scenario.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.