Is it possible to have a MySQL master-slave setup where the master has no indexes (for faster insertion) and the slave will be indexed?
Thanks in advance!
migrated from stackoverflow.com Jul 1 '13 at 18:51
This question came from our site for professional and enthusiast programmers.
But 1) you have more trouble later on when doing maintenance on the tables (replicating alter statements might caue issues) and ypu might have issues when havin primary/unique/foreign keys on slaves only as it could reject data the master accepts
And 2) Slaves still have to insert the data and build indexes, yes the maser has a tiny bit less work, the overall system not, though.
WARNING : What I am about to suggestion is probably kind of dangerous, so read this very carefully ...
You could setup a Master that has tables and indexes BUT NO DATA...
Right now, you are probably saying "Huh ???"
I repeat, a Master that has tables and indexes BUT NO DATA...
How did you do that?
Take every table in the Master and convert them to use the BLACKHOLE Storage Engine. Here is script to convert all user-defined table to
Execute this script only on the Master. Backup the Slave just in case.
What are the Benefits ?
Any gotchas, pitfalls, traps with this approach ?
GOTCHA #1 : You cannot do the following on the Master
You can only do that on the Slave. That will create the table to hold manifested data again on the Master, which you need to avoid.
As for any other
on the Slave, go back to the Master and run
to make sure the table structures match.
GOTCHA #2 : You cannot read any data anymore from the Master
You must do all writes to the Master and all reads from the Slave
GOTCHA #3 : Heavy Network Latency can still introduce some Replication Lag
Even though writing data is much faster this way, network problems may prevent needed data from showing up on the Slave in time to read. SUGGESTION : Try using a crossover cable on a separate NIC, assigning it the 192.168.0.x netblock to replicate over in order to prevent replicating through switches, firewalls, long routes, etc.
GOTCHA #4 : There is still diskspace for Binary Logs being piled up
SUGGESTION : Set expire_logs_days to a small number, like 2 or 3. I would not use 1 because you need to fix the Master in less then 24 hours. Having a 1 or 2 day cushion will help.
First, add this to
GOTCHA #5 : No High Availability or Data Redundancy
Scenario #1 : If the Master goes down, point your writes at the Slave and work on reconfiguring everything ASAP. That's a lot of manual labor. Make sure you have a good runbook for this situation.
SUGGESTION: Setup the following
This will give you high availability for writes
Scenario #2 : If the Slave goes down, AAAHHHH !!!! No data read !!!!
Need I say anything else wrong with this ???
SUGGESTION: Setup Multiple Read Slaves
This will facilitate Data Redundancy. Use one of the Slaves to do mysqldump backups (if the database is fairly small) or LVM snapshots (if the database is fairly large)
Give it a Try !!!
CAVEAT : Make plenty of backups of everything before implementing any of this !!!