MySQL how to properly change a large table structure (Alter table structure of a single column

MySQL how to properly change a large table structure (Alter table structure of a single column

Http://blog.sina.com.cn/s/blog_445e807b0101egpf.html

Found on the Internet a very helpful three-paragraph script, posted for reference, readers, make a note:
#
# 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's The master??? How does 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 is the any new data, 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 to 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 does not have binary logs enabled
Script 2 can executed on any slave that does has binary logs enabled
Script 3 can be executed on a master or anywhere else

MySQL how to properly change a large table structure (Alter table structure of a single column