CODEWITHSUNDEEP
mysqllockingreplication
David
David

Reputation: 133

Replication slave locking

I have a primary mysql server and 2 slaves as backups.

One of the slaves has been outfitted with solid state storage and due to this is used heavily for reporting.

Some of the data generated takes some time (around half an hour to an hour in some cases) and uses and generates allot of data (on the order of a couple gigs, which makes me hesitant to use transactions). The reporting tables are but a small subset of the total database so fully shutting down replication is somewhat out of the question.

The issue at hand is reports generated while the data is being generated are obviously incomplete and wrong.

  1. What would be the best way to lock the tables on both the master and the reporting server?
  2. Would the "LOCK TABLES" statement be replicated to the slaves or would my best course of action be to generate the data in temporary tables and then copy them to the final table in one INSERT ... SELECT statement.

Upvotes: 3

Views: 3515

Answers (1)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

Try the following

You could try the following:

Step 01) On the Master, run this

FLUSH TABLES WITH READ LOCK; SELECT CONNECTION_ID(); SELECT SLEEP(300);

Step 02) SHOW SLAVE STATUS\G on both Slaves (or just the Reporting Slave)

Step 03) Repeat Step 02 Until

  • Relay_Log_Space stops changing
  • Relay_Log_Pos stops changing
  • Seconds_Behind_Master is 0

At this point, since both Slaves have not received any new SQL to process, you have effectively frozen MySQL on the Slaves at the Same Point-In-Time as the Master

Step 04) On the Slaves (or just the Reporting Slave), run STOP SLAVE;

Step 05) On the Master, (if the CONNECTION_ID() return 789) run mysql> KILL 789; in another mysql session.

Step 06) Run your reports

Step 07) Run START SLAVE; on the Slaves (or just the Reporting Slave)

UPDATE 2012-06-05 15:15 EDT

Since this seems a little heavy handed for the sake of a few tables in one particular schema, the simplest thing would just be to run STOP SLAVE; on the Slave you do the Reporting from.

Upvotes: 2

Related Questions