Depends on the Storage Engine
The auto_increment column will behave but the Replica will become very bloated. Why ???
For any given non-unique index in an InnoDB table, each entry in s secondary index will have a copy of the
PRIMARY KEY as part of the entry.
If your InnoDB table has
PRIMARY KEY (id) (which is 8 bytes), then each secondary index entry will have the
Now if you change the
PRIMARY KEY to
(username,id), then each secondary index entry will have the (
id) attached. That can be up to 28 (8 + 20) bytes to each entry.
This will make INSERTs slower on the Replica.
This also allow duplicates of
You are better off creating a
UNIQUE KEY on
For those still using MyISAM, PLEASE DO NOT ATTEMPT THIS UNDER ANY CIRCUMSTANCES !!! Why ???
Any MyISAM table that has a compound key that involves an auto_increment column with increment based on the presences of the other columns
CREATE TABLE animals (
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (grp,id)
INSERT INTO animals (grp,name) VALUES
SELECT * FROM animals ORDER BY grp,id;
which prints out
| grp | id | name |
| fish | 1 | lax |
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
Do you see how
id increments on a common value when
grp already exists in the table ?
The Source and the Replica will be so horribly out-of-sync. There will be no way to know which row in the Source belongs to a row in the Replica, and vice versa.
This makes changing the PRIMARY KEY of a MyISAM table totally dangerous for a Replica.
I have discussed this subject over 6 years ago
UPDATE 2019-04-20 08:52 EDT
Of course, it is possible but BEWARE !!! : My post was just about the dangers of doing so. In the event of problems, MyISAM would be an absolute nightmare to unravel. InnoDB would be somewhat OK but you are messing with the PRIMARY KEY.
Aug 14, 2012, I have recommended changing the storage engine and adding an index on a replica (Can I have an InnoDB master and MyISAM slaves with Full-Text for searching?). I still recommend do something like this for older versions of MySQL (When FULLTEXT indexes for InnoDB did not exist yet). The
PRIMARY KEY can be a different story.
I can easily see the EXPLAIN plan of a SELECT being radically different on a replica from the EXPLAIN plan of its source given a completely different order of
PRIMARY KEY columns.
You could never use a tool like pt-table-checksum and pt-table-sync to reconcile differences should the table become out of sync or replication breaking for unrelated reasons. Why ??? Both tools rely on the
PRIMARY KEY columns. Even with plain MySQL, just running
CHECKSUM TABLE table_name; would always be different between Source and Replica.
Your hoping for better performance runs the risk of MySQL interpreting the table as not being the same (because it simply won't be the same).
If you looking for the username to be unique, FORGET IT !!! Having
(username,id) as a
PRIMARY KEY allows for it. If you already have a
UNIQUE KEY on username on the Source, then the Replica already has the key it needs.
Make sure the Source has a
UNIQUE KEY on the username.
Don't do anything to the
PRIMARY KEY on the Replica. If you want to the Source to have it, then do it on the Source so it transfers to the Replica.
Giving you a simple
it's possible or
it's not possible answer would be absolutely unfair to you given the bullet-riddled problems it would produce down the road for anyone who tries.
I would never recommend changing the
PRIMARY KEY of a Replica regardless of storage engine or DBMS. If you are still willing to accept the responsibility of changing the
PRIMARY KEY on a Replica after reading this, Godspeed, Spiderman !!!