4

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
  • Please run SHOW CREATE TABLE mytable\G and run SELECT COUNT(1) FROM mytable; and display it in the question please – RolandoMySQLDBA Jul 26 '14 at 0:47
3

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

  • So if I just add the column, it will eventually duplicate on the Slave, so there is no need to shut down or anything? also how long will it take to finish this replication (assuming we do it after work hours when the system is not so busy) – david72 Jul 27 '14 at 5:49
  • @david72 Replication will be almost instant, assuming you have no replicaton lag. But alter will take some time, first on the master, then on the slave, depending on your hardware and configuration. For reference, an add column on a 1.5 million MyISAM table took me over 2 minutes in an Amazon microinstance. – jynus Jul 27 '14 at 9:52

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.