How to Add Index Only on Read Replica of MySQL RDS Instance

amazon-rdsMySQLreplication

I have created read replica of RDS production database for reporting purposes. Cross-team is using the replica for their use.

Consumers of this replica are looking for additional indexes for faster search, but the developers are not in favour of that (adding these indexes to master) concerning more time for the insertion of records in the master database.

Is it possible to add index only on read replica of MySQL database?
How this is practised in the industry? Or what other ways this problem could be solved?

Best Answer

If by any chance you are using AWS MySQL RDS as your database infra, you can refer this:

To further maximize read performance, Amazon RDS for MySQL allows you to add table indexes directly to Read Replicas, without those indexes being present on the master.

Related Solutions

Mysql – Is it possible to recover from a LOST_EVENTS incident from an RDS read replica

In this specific case (a failed grant statement) stopping the slave, skipping the event, and restarting the slave is likely a safe operation... you just have to do it the RDS way.

mysql> CALL mysql.rds_skip_repl_error;

This is one of several stored procedures installed on every RDS instance that partially make up for the lack of SUPER privilege.

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.MySQL.CommonDBATasks.html

MySQL RDS Replication – Synchronizing RDS Instances

DISCLAIMER : I haven't tried this myself

What you asking for is somewhat rare. Nevertheless, it is possible.

Here is a link to Looker.com to explain how.

Basically, you do the following:

Create a read replica
Create a Custom DB Parameter group
Tweak the read_only setting in the Custom DB Parameter Group
When replica has been created, ...
- you modify the instance
- set Parameter Group to the Custom DB Parameter Group

Once you have the accessibility, you can create the DBs directly in the Slave.

I would normally say "Give it a Try !!!" but I never did this before. So, try it out and tell us all if it worked.

Best Answer

Related Solutions

Mysql – Is it possible to recover from a LOST_EVENTS incident from an RDS read replica

MySQL RDS Replication – Synchronizing RDS Instances

DISCLAIMER : I haven't tried this myself

Related Question