Will an ALTER TABLE ADD COLUMN on a slave break replication?

Question

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:

1. Disable replication to the slave
2. Run the ALTER TABLE command on the slave
3. Switch replication back on and wait for slave to catch up
4. 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?

Answer 1

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.

Answer 2

Try these possible recommendation and solution:

• For MyISAM tables, you can speed up index re-creation (the slowest part of the alteration process) by setting the myisam_sort_buffer_size system variable to a high value.

• If you use ALTER TABLE on a MyISAM table, all nonunique indexes are created in a separate batch (as for REPAIR TABLE). This should make ALTER TABLE much faster when you have many indexes. This feature can be activated explicitly for a MyISAM table. ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating nonunique indexes. ALTER TABLE ... ENABLE KEYS then should be used to re-create missing indexes. MySQL does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup. Using ALTER TABLE ... DISABLE KEYS requires the INDEX privilege in addition to the privileges mentioned earlier.

While the nonunique indexes are disabled, they are ignored for statements such as SELECT and EXPLAIN that otherwise would use them.

• If you are just changing a column name on a MyISAM table and want to avoid duplicating the entire table, try the following (no warranty provided but worked for me):

For peace-of-mind -- try this with some dummy data first!

1. Backup the .frm file from your master table (and the data if you can, but you're probably reading this because you can't).

2. create table with the identical schema to the one you want to alter (type "show create table and just change the name to something). Lets say you called the table "rename_temp1"

3. execute the "alter table change char(128) not null" [substituting your the old definition -- ensuring you keep column type the same]

4. Ensuring you a have made a copy of your original .frm file -- copy the .frm file to .frm.

5. voila -- all going well your column should be renamed without a full copy in/out (very useful for 140G tables...)

6. probably best to run a myisamchck on the table before making live again

Answer 3

There is no fast way to ADD a column (until 5.6, rumor has it).

One way that might work is as follows. It requires that you already have "dual-master, single-writer" set up.

1. Take the backup master offline
2. ALTER there
3. Bring it back online -- but watch out! Anything that touches that table may cause replication to hang because the schema is different. In particular, you probably have to make the new column NULLable so that INSERTs won't fail. An INSERT without a list of column names will fail.
4. Fail over.

Another possibility... Facebook has a posting on how to do an online ALTER. It does have drawbacks... * FOREIGN KEYs may be a problem * It requires a TRIGGER; so you must not already have one. * There is a brief outage when it makes the final switchover. This is probably brief enough to be acceptable.

Percona has a similar animal. Start with theirs.