How to safely add column to replicated mysql database

Question

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`),
  KEY `key1` (`column7`),
  KEY `key2` (`column21`),
  KEY `key3` (`column4`,`column5`),
  KEY `key4` (`column8`),
  KEY `key5` (`column24`),
  KEY `key6` (`column38`)
) ENGINE=MyISAM AUTO_INCREMENT=489814 DEFAULT CHARSET=latin1

Answer 1

"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.