0

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!

0

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
  • Hi gslin, thanks for the reply on using pt-online-schema-change. So do we assume then that in 5.6, the table will lock when using ALTER TABLE to warrant the use for pt-online-schema-change? I don't think that is confirmed in the documentation. Thanks – user1805458 Dec 2 '14 at 20:41
  • Oracle did not guarantee all ALTER TABLE not blocking (for MySQL 5.6 + InnoDB), you can reference dev.mysql.com/doc/refman/5.6/en/… this page. (especially Summary of Online Status for DDL Operations this table) – Gea-Suan Lin Dec 3 '14 at 7:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.