Can I have an InnoDB master and MyISAM slaves with Full-Text for searching?

Question

I'd like to use InnoDB on the master, for its transactional ability, but MyISAM on the slaves for the full-text search ability. Is this possible?

score 10 · Accepted Answer · edited Jun 15 '20 at 09:05

10

Absolutely !!!

Just run ALTER TABLE tblname ENGINE=MyISAM; against all tables on the Slave that you want to have the FULLTEXT index. Afterwards, you can run ALTER TABLE tblname ADD FULLTEXT (column[,column]);.

Please be very careful not to run DDL against those tables in the Master that are unique to InnoDB that will replicate to the Slave.

I have suggested using a different Storage Engine on a Slave in my past posts:

Jun 01, 2011 : What can we do in MySQL 5.0 Replication to address bandwidth concerns?
Nov 16, 2011 : Log MySQL DB changing queries and users
Mar 13, 2012 : https://dba.stackexchange.com/questions/14943/setting-up-mysql-across-a-series-of-production-servers/14945#14945

edited Jun 15 '20 at 09:05

Community

1

answered Aug 14 '12 at 02:16

RolandoMySQLDBA

171,728
30
293
486

Good news! You are the man Rolando. – Neil McGuigan Aug 14 '12 at 02:59
1

It works great, I have a syslog system, where the master collects data into the BLACKHOLE storage engine, which then off-loads all the heavy lifting to the slave running combinations of MyISAM and InnoDB. This way I can pause the slave in order to analyse the data, leaving the master collecting and only using log storage :) – Dave Rix Aug 14 '12 at 08:07

Can I have an InnoDB master and MyISAM slaves with Full-Text for searching?

1 Answers1

Absolutely !!!

Linked