MySQL – Indexes on Slave in Replication

MySQLreplication

I already have a my system with Master-Slave replication.

I have started using Slave for data fetching. So, I need to create some extra indexes on Slave.[Not In master – however be informed that the Master already has indexes].

I already read some posts but couldn't clear my doubts. They say If you create primary key-foreign key-unique index on slave then there could be an issue. however I'm not going to go for any of these constraints on slave.

I simply want to create non-clustered/secondary indexs on slave. would I face any issues if I do so ?

Thank you.

Best Answer

You can create secondary indexes on slave whenever required. There is no issue in creating secondary indexes (Non Primary and Non Unique).

If you create Primary and Unique on slave there will be chances that your replication will fail with duplicate key error on slave.

Take a example I have a table testRepl on master without having any index on it and has following structure and data

show create table testRepl\G
*************************** 1. row ***************************
       Table: testRepl
Create Table: CREATE TABLE `testRepl` (
  `id` int(11) DEFAULT NULL,
  `name` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

SELECT * FROM testRepl;
+------+-------+
| id   | name  |
+------+-------+
|    1 | Abdul |
|    2 | Jai   |
+------+-------+
2 rows in set (0.00 sec)

Initially we will have same table structure and data on slave as well , but assume you created a Primary key Index on id column on slave, and someone inserted id value as 2 on master on master the insertion would be successful but it will fail on slave as value 2 already exists there and the column is primary key indexed. So the insertion will fail and replication will break.

Related Question