How to safely add column to replicated mysql database

We have 2 databases, one is a replication used for read only, with around a million records, we want to add a new column to the master db, to one of the tables.

Do I just go on MySql workbench and add the column, or do I need to shutdown the database first?

SELECT COUNT(1) FROM mytable;

===>489170

``
CREATE TABLE mytable (
column1 int(11) NOT NULL auto_increment,
column2 char(1) NOT NULL default '',
column3 varchar(32) NOT NULL default '',
column4 varchar(32) NOT NULL default '',
column5 varchar(32) NOT NULL default '',
column6 datetime NOT NULL default '0000-00-00 00:00:00',
column7 varchar(96) NOT NULL default '',
column8 int(11) NOT NULL default '0',
column9 varchar(32) NOT NULL default '',
column10 tinyint(1) NOT NULL default '1',
column11 varchar(32) default NULL,
column12 tinyint(1) NOT NULL default '1',
column13 varchar(32) default NULL,
column14 tinyint(1) NOT NULL default '1',
column15 tinyint(1) NOT NULL default '1',
column16 varchar(32) default NULL,
column17 varchar(32) default NULL,
column18 tinyint(1) NOT NULL default '1',
column19 varchar(40) NOT NULL default '',
column20 char(1) default '1',
column21 varchar(11) default NULL,
column22 tinyint(3) unsigned NOT NULL default '0',
column23 int(11) NOT NULL default '1',
column24 int(10) default '0',
column25 text,
column26 varchar(64) default NULL,
column27 varchar(64) default NULL,
column28 varchar(64) NOT NULL default '',
column29 tinyint(2) default NULL,
column30 varchar(12) NOT NULL default '',
column31 varchar(12) NOT NULL default '',
column32 varchar(32) NOT NULL default '',
column33 varchar(32) default NULL,
column34 int(10) default NULL,
column35 int(10) default NULL,
column36 tinyint(1) default '0',
column37 int(1) NOT NULL default '0',
column38 int(11) NOT NULL,
column39 varchar(64) default NULL,
PRIMARY KEY (column1`),
K

"Safely" can have several meanings:

-
If you mean transactionally-safe, that is not possible, as you are using MyISAM. Operations on MyISAM are not guaranteed to be atomic, and so recoverable. Fully crash-safe tables require InnoDB (or Aria). The good news is that most MyISAM ALTER TABLE operations are implemented by recreating the table as a separate set of files, so in the event of a crash, nothing should be lost

-
If you mean safe, as in no operational disruption, a table like that may take a couple of minutes to be ALTERed, during which the original table could be read without problems for most of the process. Writes to that table, however, are blocked "Waiting for table metadata lock" and queued during the whole process. CPU and IOPS will increase -as you are doing a copy of the whole table-, but nothing alarming unless you are on full capacity already.

-
Regarding replication, ALTER TABLE will be transmitted through the replication process in a fully consistent manner. However, no writes to that table are sent to the slave (as they are not applied to the master) until the alter finishes. The table is not ALTERed and locked on the slave until then, when the alter will be applied. Replication will pause for that period (as replication is applied serially). Everything will be done consistently (as you shouldn't be doing out of band writes on the slave), but a lag equal to the time the alter takes to execute on the slave will occur.

If lag is a no-go for you, I recommend you to use an online alter table tool like pt-online-schema-change. The tools is not without problem (as rows are copied through SQL, it will generate a lot of traffic through the binary log), but a) it will allow writes to the table while the copy is done -although with reduced throughput, due to using MyISAM- b) it will minimize lag between the nodes, as the tables on the master and on the slave require almost no locking at all c) it will throttle automatically its own operations, stopping them temporarily if it detects that a slave more than 1 seconds (by default) behind the master due to the increased replication traffic.

StackExchange Database Administrators Q#72442, answer score: 3