解答你也看一下

MySQL5.6在線DDL不鎖表,現(xiàn)在我有一張1億的表，需要增加一個字段，假如我讓你去增加這個字段，你應(yīng)該注意什么，具體怎么操作？

操作如下：

1.注意磁盤空間(臨時表目錄 參數(shù) tmpdir ，因為需要創(chuàng)建臨時表使用 algorithm=default，inplace，copy copy是用臨時表的方法

lock=default，none，shared，exclusive)

2.當(dāng)前內(nèi)存剩余量

3.當(dāng)前有沒有大的事務(wù)在執(zhí)行

4.innodb_online_alter_log_max_size參數(shù)

5.然后在從上添加，再在主上添加(不記錄binlog)，處理完成后再開啟

如果直接先在主上操作，那么會導(dǎo)致主從延遲很大(在量比較大的情況下)。因為主從復(fù)制，從庫的SQL線程是單線程，它接收到主庫的BINLOG，要一條一條執(zhí)行，一條SQL卡住了，后面的SQL就會排隊等待，這時同步就延遲了

附注：

在之前的版本,InnoDB引擎是通過以下步驟來進(jìn)行DDL的：

1 按照原始表(original_table)的表結(jié)構(gòu)和DDL語句，新建一個不可見的臨時表(tmp_table)----> create table tmp_table like original_table;

2 在原表上加write lock，阻塞所有更新操作(insert、delete、update等,一行行的把原表數(shù)據(jù)拷貝到臨時表里，且更新索引)

3 執(zhí)行insert into tmp_table select * from original_table

4 rename original_table和tmp_table，最后drop original_table

5 釋放 write lock。

如果采用第三方工具推薦使用pt-online-schema-change

表上不能有任何的外鍵和觸發(fā)器，否則不能在線DDL

OSC為facebook的php腳本，用來mysql 在線DDL

觸發(fā)器將在線DDL期間的DML操作記錄到緩存日志rowlog中，到copy的最后redo這些DML日志到表上，實現(xiàn)數(shù)據(jù)一致

pt-osc工作原理：

如果表有外鍵，除非使用 --alter-foreign-keys-method 指定特定的值，否則工具不予執(zhí)行。

1 判斷各種參數(shù)，是否符合操作條件

2 創(chuàng)建一個和原表一樣的空表結(jié)構(gòu)(tmp_table)

3 執(zhí)行表結(jié)構(gòu)修改，然后從原表中copy數(shù)據(jù)到上面創(chuàng)建的新表中

4 在原表上創(chuàng)建3個觸發(fā)器(名稱格式為pt_osc_庫名_表名_操作類型)，當(dāng)copy 數(shù)據(jù)的過程中，把原表的更新操作更新到新表 (注意:如果表中已經(jīng)定義了觸發(fā)器這個工具就不能工作了)

CREATE TRIGGER `pt_osc_dba_t_del` AFTER DELETE ON `dba`.`t` FOR EACH ROW DELETE IGNORE FROM `dba`.`_t_new` WHERE `dba`.`_t_new`.`id` <=> OLD.`id`

CREATE TRIGGER `pt_osc_dba_t_upd` AFTER UPDATE ON `dba`.`t` FOR EACH ROW REPLACE INTO `dba`.`_t_new` (`id`, `a`, `b`, `c1`) VALUES (NEW.`id`, NEW.`a`, NEW.`b`, NEW.`c1`)

CREATE TRIGGER `pt_osc_dba_t_ins` AFTER INSERT ON `dba`.`t` FOR EACH ROW REPLACE INTO `dba`.`_t_new` (`id`, `a`, `b`, `c1`) VALUES (NEW.`id`, NEW.`a`, NEW.`b`, NEW.`c1`)

5 copy 完成以后，用rename table 新表代替原表，默認(rèn)刪除原表 (可以指定參數(shù)不刪除原表操作)

6 刪除觸發(fā)器

這里要注意的是。。如果是小表。。直接在主上添加了

深圳@邱治軍 mysql 中文網(wǎng) 9:46:41

大表要結(jié)合業(yè)務(wù)和時間點考慮是否在主上還是先在從上添加

這個工具只能用在percona?DB嗎

深圳@邱治軍?mysql中文網(wǎng)群2015/5/29?10:14:02

沒啊

mysql?percona?mariadb都支持

只要是mysql版本

這工具要維護(hù)表的時候最適用了

定期整理mysql?innodb?表碎片

你剛才說在線加字段，需要有前提條件

前提條件是什么

深圳@邱治軍?mysql中文網(wǎng)群 ?10:29:32

沒啥

添加主鍵要注意一下

普通字段沒啥

主從加字段方法 邱治軍在從庫上添加，再在主庫上添加

1、在從庫設(shè)置stop slave; set? sql_log_bin=0;

2、在從庫添加字段，不要用pt-osc，從庫不能創(chuàng)建觸發(fā)器，直接加字段就行alter table add column

3、在從庫設(shè)置start slave; set? sql_log_bin=1;

4、用mha做在線主從切換

5、在主庫設(shè)置set? sql_log_bin=0;stop slave;

6、主庫加字段? ，不要用pt-osc，從庫不能創(chuàng)建觸發(fā)器，直接加字段就行alter table add column

7、在主庫設(shè)置start slave; set? sql_log_bin=1;

總結(jié)

以上是生活随笔為你收集整理的mysql+不锁表添加字段_MySQL5.6在线DDL不锁表（在线添加字段）的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。