Is it safe to kill a replicating MySQL-process which is 'copying to tmp table'?

Question

I'm having a problem on a master MySQL (5.0, Linux) server: I tried to add a comment to a table row, which translates into an ALTER TABLE command. Now the process is stuck on 'copy to tmp table', copying the 100'000'000+ rows. Disk IO usage is uncomfortably high.

Since the master is using replication, I'm unsure if I can kill this process. The slaves haven't seen the ALTER TABLE command yet.

(To make this clear: I'm talking about killing the process from the MySQL-PROCESSLIST, not the MySQL-Daemon-process itself.)

Answer 1

No, it's not safe. Only if you are having a full backup(recently) of the database, to restore in case of a problem. There might be some locks and you end up after that with tables locked, possible damage on the keys.

As advice if you add new columns for such a large database. It's easier

• to create a copycat of the table schema
• run the alter on that table while it's empty,
• populate from the original with a insert into ... () select fields from ....

This is much faster. Then obviously rename the table to original.

Answer 2

You can kill the operation, but two things can happen:

1. the slave schema is inconsistent with the master (and therefore:)
2. the replication on the slaves may stop.

When the replication stops, you can try to manually fix the slave(s) by skipping over the 'alter table' instruction by entering on the slave server:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START;

Answer 3

Yes you can kill it - the ALTER won't make it into the binlogs until the transaction is committed, i.e. until the ALTER is finished. So the slaves won't see nor execute it, and the master will rollback to the old table structure.

You can easily verify that the ALTER is not yet in the binlogs by using show binlog events or the mysqlbinlog utility.