Follow us on Social Media:

MySQL Database Administrator

Boost Your Quick Knowledge in MySQL DBA Work, Concepts, Tips and Trick

Wednesday, 24 January 2018

Alter table operation in huge MySQL production table without table locking

Alter table Add/drop/Modify Column , Create/Drop INDEX in large database without any downtime:

MySQL 5.6: You can now perform read and write operations while an Alter Table operation is being perform even with InnoDB tables - http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html

In MySQL 5.6 and higher, the table remains available for read and write operations while the index is being created or dropped. The CREATE INDEX or DROP INDEX statement only finishes after all transactions that are accessing the table are completed, so that the initial state of the index reflects the most recent contents of the table. Previously, modifying the table while an index is being created or dropped typically resulted in a deadlock that cancelled the INSERT, UPDATE, or DELETE statement on the table.

MySQL 5.6 and higher has introduce Online DDL, this feature becomes more general: you can read and write to tables while an index is being created, and many more kinds of ALTER TABLE operations can be performed without copying the table, without blocking DML operations, or both. Thus in MySQL 5.6 and higher, we typically refer to this set of features as online DDL rather than Fast Index Creation.

Example:-

ALTER TABLE getmysql .session ADD INDEX time_idx(timestamp), ALGORITHM=INPLACE, LOCK=NONE;

ALTER TABLE `session` ADD `session_count` TINYINT(1) DEFAULT NULL, ALGORITHM=INPLACE, LOCK=NONE;


No comments:

Please Like and Subscribe us ....

Please visit the website on daily basis , Your New articles will publish at any moment. Please Subscribe us for getting Latest Updates...