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

線上環境復制使用ROW模式，對于上億的表，使用pt online schema change 在把數據從舊表拷貝到臨時表這步操作，會產生大量的binlog，這會導致主從延遲

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

它提供了一個--log-bin參數，并且默認是關閉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，好處是在不用分別在各個節點執行一次改表操作，只需要在主庫執行一次改表，就會通過binlog讓下面的從庫的表都被修改

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

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

pt-online-schema-change                             
--host=主機                                   
--port=端口號                                        
--user=節點號                                        
--database=數據庫名                                 
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工具的原理是，先創建一個臨時表，表名是 _原來的表名_new，這張臨時表是已經加入了你想要的索引，不停把舊表的數據拷貝到這張臨時表，新插入，修改，刪除的舊表的數據，都會根據觸發器，同樣新插入，修改，刪除到臨時表，等拷貝數據，舊表和臨時表就是一模一樣了，這個時候把臨時表rename成為就表的名字，而實際的舊表就會被drop掉，在線完成

當主庫執行命令是會顯示創建臨時表，創建觸發器

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'的原因，創建表的DDL語句，無法通過binlog在從庫建表，所以從庫是表不存在的，問題是從庫不需要存在臨時表啊，因為只操作主庫一個點就足夠了

這個是posc第一個坑，主庫因觸發器觸發器產生的數據，會產生binlog，從而同步到從庫，當從庫要執行這些數據時，發現表不存在，導致同步中斷

這時解決方法是在從庫，去建立同樣一張臨時表 _xxxx_new，好讓觸發器的數據，能夠順利插入到這張表，當建了以后可以看到從庫的臨時表有數據了，再次驗證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條數據，再看看主庫的臨時表，有差不多1億數據，因為除了觸發器還有來自舊表的

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 數據 =  觸發器產生數據  + 舊表產生數據

從庫臨時表 __t_room_impeach_new數據 = 觸發器產生的數據

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

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

為了不冒險，打算重新執行一次，這次加入2個參數，

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

--no-drop-triggers 觸發器也保留

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

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.

另外還有在從庫先把臨時表建立起來，這次執行到一半的時候，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'，導致從庫臨時表，比主庫臨時表少很多數據，所以很可能一條update語句下來，就會因為找不到數據而中斷

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

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個業務反饋持續幾分鐘有如下，一張臨時表不存在，但這張臨時表應該是對業務透明才對的，業務不會感知到的，然而還是報一個這樣一個錯誤

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

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

關于觸發器產生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

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

• 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產生的數據變動，依然會已statements的模式寫進binlog，估計pt3.0的--set-vars='sql_log_bin=0'參數也是一樣  WTF!