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.
  • Master version: 5.5.13-1
  • Slave version: 5.5.14-1
  • Binary log format: MIXED

My Slave database (~ 40GB) has been out of sync from the Master. I cannot find anything interesting in the error log. Google gives me a very helpful link.

I'm going to re-sync the database follow this instruction to minimal downtime on the Master. But before doing this, I just want to make sure that this situation is limited in the future. I will scan through the parts in the above to show you what I've done:

  • Slave database was configured with read-only option
  • There is some unsafe queries. Does it get some problems with MIXED based replication?
  • I replicated all databases
  • I used both InnoDB and MyISAM storage engines
  • Developers use alot of temporary tables

Should I:

  • Don't use the unsafe queries
  • Ask developers put all the temporary tables into a separated database

Is there anything else? In case of out of sync, is mk-table-sync reliable enough to re-sync automatically? Does anyone use it on production?


UPDATE: Tue Feb 28 23:27:13 ICT 2012

My Slave database (~ 40GB) has been out of sync from the Master. I cannot find anything interesting in the error log.

To get more information about what was happening, the Slave should be started with --log-warnings=2.

share|improve this question
    
I would like a confirmation on this, but you should be able to resynchronize the slave without bringing down the master. I would just simply stop replication on the slave, backup each database to replicate on the master, restore them to the slave, then reinitalize and restart replication on the slave. I would however prevent any inserts/deletes/updates on the master during the operation. Does the replication break often? –  Craig Efrein Dec 23 '11 at 16:20
add comment

1 Answer 1

up vote 3 down vote accepted

OBSERVATION #1

You mentioned Ask developers put all the temporary tables into a separated database

If your developers are using CREATE TEMPORARY TABLE commands to create temporary tables, they need to use CREATE TABLE instead. Here is why:

With MySQL Replication processing a temporary table, this is what occurs

  • 1) Master run CREATE TEMPORARY TABLE
  • 2) Command inserted into binary log
  • 3) Replication copies this over to the Slave's Relay Logs via I/O Thread
  • 4) Slave SQL Thread runs CREATE TEMPORARY TABLE
  • 5) Slave processes data with that temp table

Once in a while, someone may run STOP SLAVE; to run a backup. If STOP SLAVE; is issued just after step 4, the temp created disappears and so does its data. When you run START SLAVE; Replication breaks instantly complaining the table does not exist. This is normal because when a DB Connections terminates deliberately or accidently, all temp tables opened using CREATE TEMPORARY TABLE in the DB session are dropped. Running STOP SLAVE; kill the SQL thread who was holding opening the temp table.

The only workaround for this is to create the table using CREATE TABLE instead of CREATE TEMPORARY TABLE. When run STOP SLAVE;, the temp table you created normally does not disappear.

I have seen this happen maybe 10 times in my DBA career. Fixing it using the binary logs to find out the name of the temp tables, to create those tables using CREATE TABLE, then starting replication up was the only maintenance possible without just brute force copying the master.

OBSERVATION #2

mk-table-sync only works on tables with primary keys and/or unique keys. It works maybe 99% of the time. I have seen instances where the checksum of a table on the master and slave were different. I would run mk-table-sync, there were still differences (Of course, I was doing mk-table-sync in circular replication with 3 masters, which can be a little dangerous. Using it in Master/Slave is far more stable)

OBSERVATION #3

You mentioned There is some unsafe queries. Does it get some problems with MIXED based replication?

It depends. The most popular unsafe query is any UPDATE or DELETE that uses ORDER BY ... LIMIT. With SBR, this could possibly cause MySQL to UPDATE or DELETE rows from a table on the Slave in a different order tham that of the Master. With RBR, I believe the exact changes in a row are more identifiable to UPDATE or DELETE on the Slave.

SOLUTION : Avoid using unsafe queries. Then, you will not worry !!!

OBSERVATION #4

I just read your second link. ROFL !!! I am familiar with the poster of the answer.

share|improve this answer
    
Rolando, what are the steps you typically take to get replication back up and running between two servers when the master is in production and can't be stopped? –  Craig Efrein Dec 23 '11 at 18:44
    
You could 1) zap all binary logs, 2) run multiple rsyncs against /var/lib/mysql, 3) FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400) in one DB Session (No table can be updated, but can be read as long as site does reads. Any logging gets held up), 4) final rsync, 5) get name and size of last binary log, 6) kill DB session running SELECT SLEEP(86400), 7) setup up replication with log file and position from step 5. CAVEAT: If you have InnoDB around, run SET GLOBAL innodb_max_dirty_pages_pct-0; about 1 hour before this process. –  RolandoMySQLDBA Dec 23 '11 at 18:59
    
Thank you for the explanation. –  Craig Efrein Dec 23 '11 at 19:23
    
@RolandoMySQLDBA: Is it safe to use --replicate-wild-ignore-table? –  quanta Dec 26 '11 at 8:42
    
@quanta : I would say yes as long as it is not used with other similar flags at the same time. For example, you cannot use --replicate-wild-ignore-table and --replicate-do-db at the same time. While the options can be set simultaneously, such parameters are mutually exclusive in theory. Because of this, you may see two situations: 1) some data may replicate when it should not, and 2) some data may not replicate when it should. Make sure you test the option --replicate-wild-ignore-table alone and relpication take its course. –  RolandoMySQLDBA Dec 26 '11 at 14:40
add comment

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.