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.

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!

share|improve this question
add comment

migrated from stackoverflow.com Jul 1 '13 at 18:51

This question came from our site for professional and enthusiast programmers.

2 Answers

yes.

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.

share|improve this answer
    
nice insights. do you have any suggestions? the db is used for logging. and it's huge. –  relaxhax Jul 1 '13 at 10:08
add comment

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 BLACKHOLE:

echo "SET SQL_LOG_BIN = 0;" > ConvertEverythingToBlackhole.sql
MYSQL_USERPASS="-uroot -ppassword"
MYSQL_PORT=3306
MYSQL_CONN="-h127.0.0.1 -P${MYSQL_PORT} ${MYSQL_USERPASS}"
SQLSTMT="SELECT CONCAT('ALTER TABLE ',table_schema,'.',"
SQLSTMT="${SQLSTMT} table_name,' ENGINE=BLACKHOLE;') BlackholeConversionSQL"
SQLSTMT="${SQLSTMT} FROM information_schema.tables WHERE table_schema NOT IN"
SQLSTMT="${SQLSTMT} ('information_schema','mysql','performance_schema')"
MYSQL_OPTIONS="--skip-column-names -A"
mysql ${MYSQL_CONN} ${MYSQL_OPTIONS} -e"${SQLSTMT}" > ConvertEverythingToBlackhole.sql

Execute this script only on the Master. Backup the Slave just in case.

What are the Benefits ?

  • No space taken for data
  • No space taken for indexes
  • Only Disk I/O
    • Reads for accessing table structures
    • Writes for writing binary logs

Any gotchas, pitfalls, traps with this approach ?

GOTCHA #1 : You cannot do the following on the Master

  • ALTER TABLE ... ENGINE=MyISAM;
  • ALTER TABLE ... ENGINE=InnoDB;

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 ALTER TABLE ..., if you

ALTER TABLE ... ;

on the Slave, go back to the Master and run

SET SQL_LOG_BIN = 0; ALTER TABLE ... ;

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 /etc/my.cnf

mysql> SET GLOBAL expire_logs_days = 2;

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

  • DRBD/UCARP or DRBD/LinuxHeartbeat for the Master
  • DBVIP for the DRBD Primary
  • Configure Writes to the DBVIP

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 !!!

share|improve this answer
add comment

Your Answer

 
discard

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