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.
I will answer each question as follows:
1: is statement-based replication preferred over the row-based variant or vice-versa?
If you are replicating to a Slave with all MyISAM tables, statement-based is more sensible because the SQL statement would be more compact to log and ship.
If the Slave has all InnoDB, row-based is better because of the granular changes. Just be willing to live with bloated binary logs.
2: does either the InnoDB or the MyISAM side need some specific settings so data consistency would not break all by itself as the result of "normal" replication activity?
Data consistency falls out of the picture with MyISAM. Measures for crash-safe replication always falls in favor of InnoDB. MySQL 5.6 supports storing log file and position in an InnoDB table.
3: are DDL executions on the master handled on the slave in a sane manner?
Yes
4: how would I prevent an ALTER TABLE blah ENGINE=InnoDB statement run at the master from propagating to the slave?
Run it like this:
SET SQL_LOG_BIN=0;
ALTER TABLE blah ENGINE=InnoDB;
SET SQL_LOG_BIN=1;
5: Is there a recommended way of setting up such a "split" setup? I could think of a direct ALTER TABLE blah ENGINE=MyISAM on the slave, is this viable?
Yes, I have recommended this before : Can I have an InnoDB master and MyISAM slaves with Full-Text for searching?
6: any other caveats one should know about?
All the caveats you needs are in this post : Using MyISAM for reading and InnoDB for writing data
Best Answer
Ok,
Here a sample of config to setup your replication :
On Master in your my.cnf add :
On your slave in my.cnf add :
Now you have two solutions, dump or binary copy.
In both case you should made your transformation (myisam to InnoDB) on your slave slave and switch your slave to master. Also you should active binlogs to catch the master position.
First solution, you can make a dump of your master ( with the master-data option to catch master position) On your .sql dump file, search and replace ENGINE=myisam to InnoDB and load thé dump to thé slave server. You have all tables on InnoDB engine :) after started réplication and catching up your the late : "second behind master= 0 " it´s time to switch slave to master ( with a new dump (now from slave to master) and take care to switch the conf also).
The second solution is the same but with a cold backup, stop mysql, copy datadir (4gb Will probably done in ~ 10mins dépends on your solution : scp, usb keys...) start your slave and alter each table with engine=innodb. Like thé first one, when it´s done, you should switch master/slave and alter ex-master tables.
Caution with "alter table engine=InnoDB" Mysql Will create a InnoDB table and copy data in it so your table size is tablesize x 2 (when innodb table is dobe, mysql drop myisam table)
I'm on my phone so sorry if there are many strange word... :)
(I didn't detailed replication set up cause it's not the subject but we let me know if you want some help.)