案例 - percona-online-schema-change各種坑

線上環(huán)境復(fù)制使用ROW模式，對于上億的表，使用pt online schema change 在把數(shù)據(jù)從舊表拷貝到臨時表這步操作，會產(chǎn)生大量的binlog，這會導(dǎo)致主從延遲

在pt工具包2.1之前，pt-online-schema-change是不會打印binlog的，如果要在主從上加索引，需要分別在主庫執(zhí)行一次，在從庫執(zhí)行一次

它提供了一個--log-bin參數(shù)，并且默認是關(guān)閉binlog的

• --bin-log

• Allow binary logging (SET SQL_LOG_BIN=1). By default binary logging is turned off because in most cases the --tmp-tabledoes not need to be replicated. 

而在pt工具2.2版本以后，會默認打binlog，好處是在不用分別在各個節(jié)點執(zhí)行一次改表操作，只需要在主庫執(zhí)行一次改表，就會通過binlog讓下面的從庫的表都被修改

pt工具3.0版本，有一個 --set-vars='sql_log_bin=0' 參數(shù)能替代 --bin-log=0 效果

有一個1.5億表加索引的需求，預(yù)計1.5億生成的binlog預(yù)計會有20G，為了不產(chǎn)生binlog，準備在每個點執(zhí)行一次，先在主庫執(zhí)行如下命令

pt-online-schema-change                             
--host=主機                                   
--port=端口號                                        
--user=節(jié)點號                                        
--database=數(shù)據(jù)庫名                                 
t=t_room_impeach                                   
--alter="ADD INDEX idx_psr(A,B,C)"  
--set-vars='sql_log_bin=0'                           
--execute

這條語句一下去，主庫下面的4個從庫同步都中斷了，show slave status報錯

Last_SQL_Errno: 1146
Last_SQL_Error: Error executing row event: 'Table 'live_oss._t_room_impeach_new' doesn't exist'

報錯_t_room_impeach_new表存在，為什么這張臨時表在從庫要存在呢？

posc工具的原理是，先創(chuàng)建一個臨時表，表名是 _原來的表名_new，這張臨時表是已經(jīng)加入了你想要的索引，不停把舊表的數(shù)據(jù)拷貝到這張臨時表，新插入，修改，刪除的舊表的數(shù)據(jù)，都會根據(jù)觸發(fā)器，同樣新插入，修改，刪除到臨時表，等拷貝數(shù)據(jù)，舊表和臨時表就是一模一樣了，這個時候把臨時表rename成為就表的名字，而實際的舊表就會被drop掉，在線完成

當主庫執(zhí)行命令是會顯示創(chuàng)建臨時表，創(chuàng)建觸發(fā)器

Creating new table...
Created new table live_oss._t_room_impeach_new OK.
Altering new table...
Altered `live_oss`.`__t_room_impeach_new` OK.
2017-08-02T16:38:48 Creating triggers...
2017-08-02T16:38:48 Created triggers OK.
2017-08-02T16:38:48 Copying approximately 141559863 rows...

因為 --set-vars='sql_log_bin=0'的原因，創(chuàng)建表的DDL語句，無法通過binlog在從庫建表，所以從庫是表不存在的，問題是從庫不需要存在臨時表啊，因為只操作主庫一個點就足夠了

這個是posc第一個坑，主庫因觸發(fā)器觸發(fā)器產(chǎn)生的數(shù)據(jù)，會產(chǎn)生binlog，從而同步到從庫，當從庫要執(zhí)行這些數(shù)據(jù)時，發(fā)現(xiàn)表不存在，導(dǎo)致同步中斷

這時解決方法是在從庫，去建立同樣一張臨時表 _xxxx_new，好讓觸發(fā)器的數(shù)據(jù)，能夠順利插入到這張表，當建了以后可以看到從庫的臨時表有數(shù)據(jù)了，再次驗證sql_log_bin=0沒有效果

explain select count(*) from  __t_room_impeach_new;
+----+-------------+----------------------+-------+---------------+------+---------+------+----------+-------------+
| id | select_type | table                | type  | possible_keys | key  | key_len | ref  | rows     | Extra       |
+----+-------------+----------------------+-------+---------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | __t_room_impeach_new | index | NULL          | uid  | 4       | NULL | 176| Using index |
+----+-------------+----------------------+-------+---------------+------+---------+------+----------+-------------+

幾個從庫都有176條數(shù)據(jù)，再看看主庫的臨時表，有差不多1億數(shù)據(jù)，因為除了觸發(fā)器還有來自舊表的

explain select count(*) from  __t_room_impeach_new;
+----+-------------+----------------------+-------+---------------+------+---------+------+----------+-------------+
| id | select_type | table                | type  | possible_keys | key  | key_len | ref  | rows     | Extra       |
+----+-------------+----------------------+-------+---------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | __t_room_impeach_new | index | NULL          | uid  | 4       | NULL | 10527757 | Using index |
+----+-------------+----------------------+-------+---------------+------+---------+------+----------+-------------

當時有個擔心

主庫臨時表 __t_room_impeach_new 數(shù)據(jù) =  觸發(fā)器產(chǎn)生數(shù)據(jù)  + 舊表產(chǎn)生數(shù)據(jù)

從庫臨時表 __t_room_impeach_new數(shù)據(jù) = 觸發(fā)器產(chǎn)生的數(shù)據(jù)

如果所有點執(zhí)行最后一步操作 rename 臨時表__t_room_impeach_new to t_room_impeach 正式表，豈不是主從數(shù)據(jù)不一致,從庫少了很多數(shù)據(jù)？ 

不過按道理這種情況不會發(fā)生，因為--set-vars='sql_log_bin=0'會把rename這個DDL語句，像create table一樣給阻隔掉，不會導(dǎo)致從庫改表成功

為了不冒險，打算重新執(zhí)行一次，這次加入2個參數(shù)，

--no-drop-old-table 即使執(zhí)行完了命令，也不要drop表，讓我確認舊表新表是一致的再手動drop

--no-drop-triggers 觸發(fā)器也保留

執(zhí)行命令之前，先把臨時表，觸發(fā)器都手動刪除，正如提示說的

Not dropping triggers because the tool was interrupted.  To drop the triggers, execute:
DROP TRIGGER IF EXISTS `live_oss`.`pt_osc_live_oss_t_room_impeach_del`;
DROP TRIGGER IF EXISTS `live_oss`.`pt_osc_live_oss_t_room_impeach_upd`;
DROP TRIGGER IF EXISTS `live_oss`.`pt_osc_live_oss_t_room_impeach_ins`;
Not dropping the new table `live_oss`.`_t_room_impeach_new` because the tool was interrupted.  To drop the new table, execute:
DROP TABLE IF EXISTS `live_oss`.`_t_room_impeach_new`;
`live_oss`.`t_room_impeach` was not altered.

另外還有在從庫先把臨時表建立起來，這次執(zhí)行到一半的時候，4個從庫又報錯，同步中斷了

Last_SQL_Errno: 1032

Last_SQL_Error: Could not execute Update_rows event on table live_oss._t_room_impeach_new; Can't find record in '_t_room_impeach_new', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.056637, end_log_pos 41767716

這次的報錯是update語句失敗了，row模式的update語句是 set 新值 where 舊值 ，如果在從庫的臨時表上，找不到where的舊的值，就無法update成新值，，同樣因為--set-vars='sql_log_bin=0'，導(dǎo)致從庫臨時表，比主庫臨時表少很多數(shù)據(jù)，所以很可能一條update語句下來，就會因為找不到數(shù)據(jù)而中斷

另外如果使用--no-drop-old-table和--no-drop-triggers參數(shù)，最終結(jié)果是命令到99%一直卡住，一直保持這個狀態(tài)，也是一個坑

Copying `live_oss`.`t_room_impeach`:  99% 01:01 remain

Copying `live_oss`.`t_room_impeach`:  99% 00:47 remain

Copying `live_oss`.`t_room_impeach`:  99% 00:35 remain

Copying `live_oss`.`t_room_impeach`:  99% 00:21 remain

Copying `live_oss`.`t_room_impeach`:  99% 00:09 remain

它沒有按照文檔的去做，先重命名，然后不drop舊表

When the tool finishes copying data into the new table, it uses an atomic RENAME TABLE operation to simultaneously rename the original and new tables. After this is complete, the tool drops the original table.

還有一個坑就是2個業(yè)務(wù)反饋持續(xù)幾分鐘有如下，一張臨時表不存在，但這張臨時表應(yīng)該是對業(yè)務(wù)透明才對的，業(yè)務(wù)不會感知到的，然而還是報一個這樣一個錯誤

Base table or view not found: 1146 Table 'live_oss.__t_room_impeach_new' doesn't exist

thread run: Table 'live_oss.__t_room_impeach_new' doesn't exist

結(jié)論：單點mysql或者僅修改從庫表結(jié)構(gòu)時，可以使用--set-vars='sql_log_bin=0'來減少binlog產(chǎn)生，但在主從復(fù)制過程中，還是不要用這個參數(shù)，對主從復(fù)制最大的損傷是延遲，可以用--max-lag這個參數(shù)來限制延遲時間，比如限制在30秒，15秒，代價是改表時間會很長，但總比中斷主從復(fù)制要好得多

關(guān)于觸發(fā)器產(chǎn)生binlog的問題，在pt2.1,2.2,3.0的文檔都沒有提及，pt2.0文檔有提及

• Replicaiton will break if you alter a table on a master that does not exist on a slave

在修改過程中，主庫有表，從庫沒表，會導(dǎo)致主從復(fù)制中斷，這是上面遇到的問題

• Although the tool sets SQL_BIN_LOG=0 by default (unless --bin-log is specified), triggers which track changes to the table being altered still write statements to the binary log

盡管使用了SQL_BIN_LOG=0 ，但triggers產(chǎn)生的數(shù)據(jù)變動，依然會已statements的模式寫進binlog，估計pt3.0的--set-vars='sql_log_bin=0'參數(shù)也是一樣  WTF!