MySQL 5.6 – ALTER TABLE Blocking Issues

MySQLmysql-5.1mysql-5.6replicationschema

I have a question with a MySQL 5.6 master-slave setup. If I were to modify the master's schema with an ALTER TABLE SQL query, then when the slave tries to execute that ALTER TABLE statement too, would it just block all reads to just that specific table until that query finishes executing? I assume for sure that not all tables would get blocked but I am concerned if the table being ALTER-ed itself is going to get blocked.

I did some research on my question and am referring to the online ALTER TABLE operation at http://blogs.oracle.com/mysqlinnodb/entry/online_alter_table_in_mysql

I have heard from others that the slave does not block reads in this case if it were running 5.1 MySQL, but does it block for 5.6?

And yes, all the tables in the DB are InnoDB

Thanks!

Best Answer

If your table is InnoDB format, then try pt-online-schema-change (from Percona Toolkit), for example:

pt-online-schema-change
    --charset utf8 \
    --alter 'ADD COLUMN baz INT UNSIGNED' \
    --execute -h db-test -u username -p password D=foo,t=bar
Related Question