Mysql – Will an ALTER TABLE ADD COLUMN on a slave break replication

alter-tablelockingMySQLperformancereplication

I need to add a last_modified column to a MyISAM table tbl_items. The crux of this problem is that tbl_items houses several gigabytes of data. Also of note, I am using a master-slave deployment with one slave.

Obviously, running the necessary ALTER TABLE command on the master is unacceptable since it locks the table for well over a half-hour. I cannot afford this kind of downtime at ANY time of day or night, most unfortunately.

A few sources have suggested some form of the following:

Disable replication to the slave
Run the ALTER TABLE command on the slave
Switch replication back on and wait for slave to catch up
Promote slave to master

Is this a viable solution? It would seem to me that all new tbl_items inserts would be lost between the beginning of step 2 and step 4; that is, is it possible for tbl_items data to replicate throughout the interval that it's missing the new column?

If that's the case, is there an alternate solution that eliminates the possibility of both downtime and data-loss?

Best Answer

I think that, if your purpose is to avoid locking, you should use pt-online-schema-change (a tool from Percona Toolkit) or Gh-ost (from GitHub).

Here is how pt-osc works:

Creates a ghost table identical to the original table, but empty
Runs ALTER TABLE on the ghost table
Creates triggers on the original table, which insert new rows in the new table, and updates and deletes them if they are already there.
It copies the rows in chunks, monitoring the server performance (it can slow down or pause its work if necessary)
Switch the table names

gh-ost is very similar, the main difference is that it creates no triggers, and uses the binary log instead to detect the changes to the original table.

To answer your question more directly - yes, it is possible to add a column on the slave. And most probably it will simply work, as long as it has a default value. But it is very likely that in the future you will regret this choice, because you'll need to do something that will break replication, like adding a column in the master. I recommend to sure to check the documentation and fully understand the dangers, before doing such a thing.

Related Solutions

MySQL – ALTER TABLE on Large Table with Indexed Column

If you are a little adventurous, you could take matters into your hands by performing the ALTER TABLE in stages you can see. Suppose the table you want to change is called WorkingTable. You could perform the changes in stages like this:

#
#  Script 1
#  Alter table structure of a single column of a large table
#
CREATE TABLE WorkingTableNew LIKE WorkingTable;
ALTER TABLE WorkingTableNew MODIFY BigColumn VARCHAR(50);
INSERT INTO WorkingTableNew SELECT * FROM WorkingTable;
ALTER TABLE WorkingTable RENAME WorkingTableOld;
ALTER TABLE WorkingTableNew RENAME WorkingTable;
DROP TABLE WorkingTableOld;

You can perform this on all slaves. What about the master ??? How do you prevent this from replicating to the slaves. Simple: Don't send the SQL into the master's binary logs. Simply shut off binary logging in the session before doing the ALTER TABLE stuff:

#
#  Script 2
#  Alter table structure of a single column of a large table
#  while preventing it from replicating to slaves
#
SET SQL_LOG_BIN = 0;
CREATE TABLE WorkingTableNew LIKE WorkingTable;
ALTER TABLE WorkingTableNew MODIFY BigColumn VARCHAR(50);
INSERT INTO WorkingTableNew SELECT SQL_NO_CACHE * FROM WorkingTable;
ALTER TABLE WorkingTable RENAME WorkingTableOld;
ALTER TABLE WorkingTableNew RENAME WorkingTable;
DROP TABLE WorkingTableOld;

But wait !!! What about any new data that comes in while processing these commands ??? Renaming the table in the beginning of the operation should do the trick. Let alter this code a little to prevent entering new data in that respect:

#
#  Script 3
#  Alter table structure of a single column of a large table
#  while preventing it from replicating to slaves
#  and preventing new data from entering into the old table
#
SET SQL_LOG_BIN = 0;
ALTER TABLE WorkingTable RENAME WorkingTableOld;
CREATE TABLE WorkingTableNew LIKE WorkingTableOld;
ALTER TABLE WorkingTableNew MODIFY BigColumn VARCHAR(50);
INSERT INTO WorkingTableNew SELECT SQL_NO_CACHE * FROM WorkingTableOld;
ALTER TABLE WorkingTableNew RENAME WorkingTable;
DROP TABLE WorkingTableOld;

Script 1 can be executed on any slave that do not have binary logs enabled
Script 2 can be executed on any slave that does have binary logs enabled
Script 3 can be executed on a master or anywhere else

Give it a Try !!!

MySQL Replication: Slave with Different Column From Master

WOW, I remember your last question !!!

If you kept the s_relations_old on the slave, just switch back and start replication.

STOP SLAVE;
ALTER TABLE s_relations RENAME s_relations_bad;
ALTER TABLE s_relations_old RENAME s_relations;
START SLAVE;

You should perform the conversion of the temp_col on the master so that you let that ALTER TABLE replicate to the slave.

You will have to bite the bullet on this one and have some downtime.

Best Answer

Related Solutions

MySQL – ALTER TABLE on Large Table with Indexed Column

MySQL Replication: Slave with Different Column From Master

Related Question