pt-online-schema-change手記

2024-09-12 20:30:08

字體：大中小

來源：轉載

供稿：網友

　　需求：

　　   應大數據部門要求，需要在指定表增加create_time字段并給與當前時間做缺省值。

　　環境：

　　   DB：阿里云RDS 的MySQL5.6

　　   OS: centos7

　　   有好幾張大表，過1億，最大的7個億，考慮使用pt-online-schema-change工具，夜里執行，并根據工作量分幾次執行

　　   選擇方案時，老大意思：要有幾套都列出來，然后比較優缺點，

　　    1.online DDL

　　    2.pt-online-schema-change工具

　　    3.過億表，rename成歷史表，然后新建表包含create_time字段，前端要修改程序。

　　      沒過億的，就pt-online-schema-change工具修改

　　    4.通過mycat 對大表做分庫分表操作，一勞永逸

　　   因rds不能直接ssh上去，所以申請一臺同網段的ECS服務器用于執行 pt-online-schema-change

　　   命令執行機器：centos7.x mysql5.6client pt工具集

　　一、工具安裝

　　yum -y install perl-TermReadKey.x86_64

　　yum -y install perl-IO-Socket-SSL

　　yum -y install perl-DBI.x86_64

　　yum -y install perl-DBD-MySQL.x86_64

　　yum -y install perl-Digest-MD5

　　wget -c https://www.percona.com/downloads/percona-toolkit/3.0.12/binary/redhat/7/x86_64/percona-toolkit-3.0.12-1.el7.x86_64.rpm

　　rpm -ivh percona-toolkit-3.0.12-1.el7.x86_64.rpm

　　二、監控工具：

　　innotop -h xxx.xxx.xxx.xxx

　　 -u 用名

　　 -p 密碼

　　輸入：Q 可看當前查詢情況

　　三、限制

　　 1）服務器空間檢查與評估

　　    如最大的表50G，索引10G，所以至少需要 “60G臨時空間” + “binlog空間也算50G”吧，也就110G以上

　　    使用OSC會使增加一倍的空間，包括索引

　　    而且在 Row Based Replication 下，還會寫一份binlog。不要想當然使用–set-vars去設置 sql_log_bin=0，因為在這個session級別，alter語句也要在從庫上執行，除非你對從庫另有打算。

　　 2）原表不能有trigger檢查

　　 3）外鍵檢查，最好不要有外鍵

　　 4) 主鍵或唯一索引檢查

　　    絕大部分情況下表上需要有主鍵或唯一索引，因為工具在運行當中為了保證新表也是最新的，需要舊表上創建 DELETE和UPDATE 觸發器，同步到新表的時候有主鍵會更快。個別情況是，當alter操作就是在c1列上建立主鍵時，DELETE觸發器將基于c1列。

　　 5）mysql服務器參數檢查

　　   （1）連接時間參數：

　　   wait_timeout 值24小時，

　　   innodb_lock_wait_timeout 50秒

　　   （2）在Online DDL過程中，需要保持這段時間內，“增刪改”的最大日志大小。小了報錯，大了產生鎖表時間長

　　   innodb_online_alter_log_max_size 134217728 （這是RDS的默認值，我沒有修改）

　　   這個參數是mysql 5.6.6引入的，因為在online ddl過程中需要保持delete、update、insert這些數據，所以需要一個日志去保持，這個參數就是限制這個日志的最大大小，當ddl過程中需要的這個日志的大小比這個限制還大的時候就會報錯。

　　   該參數為“動態”參數且“全局”的,設置方法： set global innodb_online_alter_log_max_size=402653184;

　　   （3）主從復制參數

　　   max_binlog_cache_szie

　　   max_allowed_packet

　　四、產生腳本

　　#for i in `seq 0 9`

　　>do

　　>echo “pt-online-schema-change --no-version-check --charset=utf8 --user=mysqldba --ask-pass --host=127.0.0.1 --alter=/"add COLUMN update_time datetime/" P=3306,D=escore,t=t_user_$i --execute --nocheck-replication-filters” >> batch.sh

　　>done

　　#for i in {a..f}

　　>do

　　>echo "pt-online-schema-change -no-version-check --charset=utf8 --user=mysqldba --password=xx --host=127.0.0.1 --alter=/"add COLUMN update_time datetime/" P=3306,D=escore,t=t_user_$i --execute --nocheck-replication-filters" >> batch.sh

　　>done

　　select concat('pt-online-schema-change --no-version-check --charset=utf8 --user=xx',

　　       ' --ask-pass --alter="add COLUMN update_time datetime"',

　　       ' h=xx,P=3306,D=', table_schema,

　　       ',t=',

　　       table_name,

　　       ' --execute')

　　from information_schema.tables

　　where table_schema='xx'

　　說明：

　　 1）密碼

　　使用參數 --password 會在shell歷史命令中留下密碼，

　　這里使用 --ask-pass 提示用戶輸入密碼，但這樣就不能批量跑

　　可以在 /etc/my.conf 的[client]下配置

　　 user=xxx

　　 password=xxxx

　　 chmod 600 /etc/my.conf 非root用戶不能看

　　然后就可以不用輸入密碼了

　　 pt-online-schema-change --no-version-check --alter "add column create_time timestamp default current_timestamp comment '入庫時間'" --host=192.168.178.131 P=3310,D=testdb,t=t1 --execute

　　五、執行期間異常回退方法

　　執行期間如果有報錯，舉例表名時候card 則

　　select * from information_schema.triggers where table_name='card'

　　drop trigger triggername

　　刪除新建的表（都是以下劃線_開頭的new表）

　　drop table _card_new

　　調整參數，重新執行

　　六、可調整的參數

　　表：bw_fund_record 主鍵id

　　pt-online-schema-change --no-version-check --check-replication-filters --lock-wait-timeout=3 --alter "add (create_time timestamp not null )" -h292.168.178.131 P=3306,u=user1,p=admin,D=testdb,t=e_card --execute

　　pt-online-schema-change --user=root --password=123456 -h localhost --alter "ADD COLUMN content text" D=db1,t=tb_2--no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --print --charset=utf8 --execute

　　pt-online-schema-change --user=root --password="xxxxx" --host=192.168.xx.xx D=M_xx,t=T_xx --alter "ADD Fxxxxx'" --charset=utf8 --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --print --execute

　　--critical-load 先不加（跟負載有關）

　　--max-load   先不加（跟負載有關）

　　--max-lag

　　當是主從環境，不在乎從的延遲，則需要加 --recursion-method=none 參數。當需要盡可能的對服務產生小的影響，則需要加上 --max-load參數。

　　1）上面的測試都是把原表刪除了，要是不刪除原表則，則使用 --no-drop-old-table選項，這樣會讓原表（_test_binlog_old）保留。

　　2）要是在線上環境上添加字段，但又不想影響到服務，可以用 --max-load選項去執行該工具，默認是Threads_running=25，即當前有這么多線程在運行的時候就暫停數據的復制，等少于該值則繼續復制數據到新表。

　　pt-online-schema-change --host=xxxxx -P 3306 --charset=utf8 -u root -p 'xxxxxx;' --alter='add column door_no varchar(200) comment "居住門樓牌"

　　' --print --execute D=lzmh_wlw_db,t=wlw_room --critical-load="Threads_running=200"

　　七、最中腳本的樣子

　　pt-online-schema-change --no-version-check --execute --alter "add column c1 int" h=xxx.mysql.rds.aliyuncs.com,P=3306,u=jacky,p=xxx,D=jacky,t=x 阿里云

　　pt-online-schema-change --no-version-check   --alter "add column create_time timestamp default current_timestamp comment '創建時間'" P=3306,u=beadwallet,D=beadwalletloan,t=bw_xg_mid_score

　　pt-online-schema-change --no-version-check   --alter "add column create_time timestamp default current_timestamp comment '創建時間'" P=3306,u=beadwallet,D=beadwalletloan,t=bw_borrower_auth

　　pt-online-schema-change --no-version-check --lock-wait-timeout=3 --alter "add column create_time timestamp default current_timestamp comment '創建時間'" P=3306,u=sasbeadwallet,D=sassevenwallet,t=bw_card_city

　　time ./pt-online-schema-change --no-version-check --alter "modify column create_time timestamp default current_timestamp comment '入庫時間'" -hhostname1 P=3306,u=user1,D=db1,t=bw_bigfintech_report --ask-pass --execute

　　time ./pt-online-schema-change --no-version-check --alter "add column create_time timestamp default current_timestamp comment '入庫時間'" -hhostname1 P=3306,u=user1,D=db1,t=bw_xg_emergency_analysis --ask-pass --execute

　　八、遇到的問題：

　　 1）字符集

　　大量修改后，第二天發現，所有修改過的字段的注釋都變成亂碼了，還還只是注釋亂碼,數據正常。

　　現在回想起來都后怕，犯了這樣一個低級的錯誤，如果pt-online-schema-change 復制表不是在數據塊級別上，那都整個庫都變亂碼了。

　　mysql> show create table bw_city;

　　+---------+-------------------------------------------------------------------------------------------+

　　| Table   | Create Table                                                                              |

　　+---------+-------------------------------------------------------------------------------------------+

　　| bw_city | CREATE TABLE `bw_city` (

　　 `id` int(11) NOT NULL AUTO_INCREMENT,

　　 `parentCode` varchar(50) DEFAULT NULL COMMENT '??code:0????',

　　 `citycode` varchar(50) NOT NULL COMMENT '??code',

　　 `adcode` varchar(50) CHARACTER SET utf8mb4 NOT NULL COMMENT 'adcode',

　　 `name` varchar(100) NOT NULL COMMENT '??',

　　 `level` varchar(50) NOT NULL COMMENT '??',

　　 `levelNum` tinyint(3) NOT NULL COMMENT '???1???2???3?',

　　 `LAT` varchar(200) NOT NULL COMMENT '??',

　　 `LNG` varchar(200) NOT NULL COMMENT '??',

　　 `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'å…¥åº“æ—¶é—´',

　　 PRIMARY KEY (`id`),

　　 KEY `c_i_parentCode` (`parentCode`),

　　 KEY `c_i_cityCode` (`citycode`),

　　 KEY `c_i_adcode` (`adcode`)

　　) ENGINE=InnoDB AUTO_INCREMENT=3608 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

　　因為是RDS，所以我們不能ssh到本地操作，就在另一臺機器（-hxxx）遠程執行 ,沒有加--charset 參數

　　指定session級別為latin1時，新加的字段注釋顯示正常，但原有的字段還時亂碼

　　mysql> set names latin1;

　　Query OK, 0 rows affected (0.00 sec)

　　mysql> SELECT column_comment FROM `information_schema`.`COLUMNS` where table_name='bw_city';

　　+----------------+

　　| column_comment |

　　+----------------+

　　|                |

　　| ??code:0????   |

　　| ??code         |

　　| adcode         |

　　| ??             |

　　| ??             |

　　| ???1???2???3? |

　　| ??             |

　　| ??             |

　　| 入庫時間       |

　　+----------------+

　　10 rows in set (0.00 sec)

　　解決方法：

　　（1）--charset=xxx

　　   默認字符類型Latin1，例如如果值為utf8，就將輸出的字符設置為utf8格式，將mysql_enable_utf8傳遞給DBD::mysql，然后連接MySQL后運行 SET NAMES UTF8 命令

　　   遠程執行時，如果本地字符集和服務器上的字符集一致，需要指定

　　time ./pt-online-schema-change --charset=utf8 --no-version-check --alter "modify column create_time timestamp default current_timestamp comment '入庫時間'" -hrm-uhostname1 P=3306,u=user1,D=db1,t=bw_bigfintech_report --ask-pass --execute

　　（2）在DSN參數中指定字符集

　　【DSN】

　　指定時注意大小寫敏感，“=”左右不能有空格，多個值之間用逗號分隔

　　1. A                charset

　　2. D                database

　　3. F                mysql_read_default_file

　　4. h                host

　　5. p                password

　　6. P                port

　　7. S                mysql_socket

　　8. t                table

　　9. u                user

　　（3）在/etc/my.cnf指定字符集

　　[client]

　　port                            = 3310

　　default-character-set           = utf8

　　socket                          ="/u01/my3310/run/my3310.sock"

　　user=root

　　password=xxx

　　 2）存在trigger

　　pt-online-schema-change -u user -p password -h 10.0.200.195 /

　　--alter="MODIFY COLUMN f_receiver varchar(128)                                                      NOT NULL DEFAULT '' AFTER f_user_id" --dry-run D=db_name,t=table_name

　　The table `db_name`.`table_name` has triggers. This tool needs to create its own triggers, so the table cannot already have triggers.

　　 3）no-version-check

　　pt-online-schema-change -uuser -ppassword --alter "add key id_provice(f_provice)" /

　　D=db_name,t=tb_name -h rdsxxxxxx.mysql.rds.aliyuncs.com

　　Can't use an undefined value as an ARRAY reference at /usr/bin/pt-online-schema-change line 7335.

　　這個錯誤在阿里云RDS上執行時出現的，原生5.6的版本上就沒問題

　　應該是pt去驗證mysql server版本的時候從rds拿到的信息不對，導致格式出錯

　　九、服務器上的參數：

　　mysql> show grants for 'usera'@'%' ;

　　+---------------------------------------------------------------------------------------------------------------------------------------------------------------+

　　| Grants for usera@%                                                                                                                                                                                                          |

　　+---------------------------------------------------------------------------------------------------------------------------------------------------------------+

　　| GRANT PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'usera'@'%' IDENTIFIED BY PASSWORD '*xxx'                                             |

　　| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `db1ra`.* TO 'usera'@'%'   |

　　| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `db2seal`.* TO 'usera'@'%' |

　　| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `db3loan`.* TO 'usera'@'%' |

　　| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `db0`.* TO 'usera'@'%'     |

　　| GRANT SELECT ON `performance_schema`.* TO 'usera'@'%'                                                                                                                                        |

　　| GRANT SELECT ON `mysql`.`time_zone_transition` TO 'usera'@'%'                                                                                                                               |

　　| GRANT SELECT ON `mysql`.`proc` TO 'usera'@'%'                                                                                                                                                         |

　　| GRANT SELECT ON `mysql`.`time_zone_name` TO 'usera'@'%'                                                                                                                                     |

　　| GRANT SELECT ON `mysql`.`time_zone_leap_second` TO 'usera'@'%'                                                                                                                          |

　　| GRANT SELECT ON `mysql`.`event` TO 'usera'@'%'                                                                                                                                                       |

　　| GRANT SELECT ON `mysql`.`time_zone_transition_type` TO 'usera'@'%'                                                                                                                      |

　　| GRANT SELECT ON `mysql`.`general_log` TO 'usera'@'%'                                                                                                                                             |

　　| GRANT SELECT ON `mysql`.`help_topic` TO 'usera'@'%'                                                                                                                                               |

　　| GRANT SELECT ON `mysql`.`func` TO 'usera'@'%'                                                                                                                                                         |

　　| GRANT SELECT ON `mysql`.`help_category` TO 'usera'@'%'                                                                                                                                         |

　　| GRANT SELECT ON `mysql`.`help_relation` TO 'usera'@'%'                                                                                                                                           |

　　| GRANT SELECT ON `mysql`.`help_keyword` TO 'usera'@'%'                                                                                                                                         |

　　| GRANT SELECT ON `mysql`.`time_zone` TO 'usera'@'%'                                                                                                                                               |

　　| GRANT SELECT ON `mysql`.`slow_log` TO 'usera'@'%'                                                                                                                                                 |

　　+-------------------------------------------------------------------------------------------------------------------------------------------------------------+

　　20 rows in set (0.01 sec)

　　mysql> show global variables like 'log%';

　　+----------------------------------------+-------+

　　| Variable_name                          | Value |

　　+----------------------------------------+-------+

　　| log_bin                                | ON    |

　　| log_bin_basename                       |       |

　　| log_bin_index                          |       |

　　| log_bin_trust_function_creators        | ON    |

　　| log_bin_use_v1_row_events              | ON    |

　　| log_error                              |       |

　　| log_output                             | TABLE |

　　| log_queries_not_using_indexes          | OFF   |

　　| log_slave_updates                      | ON    |

　　| log_slow_admin_statements              | ON    |

　　| log_slow_slave_statements              | OFF   |

　　| log_throttle_queries_not_using_indexes | 0     |

　　| log_warnings                           | 2     |

　　+----------------------------------------+-------+

　　13 rows in set (0.00 sec)

　　mysql> show variables like 'long_query_time';

　　+-----------------+----------+

　　| Variable_name   | Value    |

　　+-----------------+----------+

　　| long_query_time | 1.000000 |

　　+-----------------+----------+

　　1 row in set (0.00 sec)

　　mysql> show global variables like 'slave%';

　　+------------------------------+-----------------------+

　　| Variable_name                | Value                 |

　　+------------------------------+-----------------------+

　　| slave_allow_batching         | OFF                   |

　　| slave_checkpoint_group       | 512                   |

　　| slave_checkpoint_period      | 300                   |

　　| slave_compressed_protocol    | OFF                   |

　　| slave_exec_mode              | STRICT                |

　　| slave_load_tmpdir            |                       |

　　| slave_max_allowed_packet     | 1073741824            |

　　| slave_net_timeout            | 60                    |

　　| slave_parallel_workers       | 8                     |

　　| slave_pending_jobs_size_max | 167772160             |

　　| slave_pr_mode                | TABLE                 |

　　| slave_rows_search_algorithms | TABLE_SCAN,INDEX_SCAN |

　　| slave_skip_errors            | OFF                   |

　　| slave_sql_verify_checksum    | ON                    |

　　| slave_transaction_retries    | 10                    |

　　| slave_type_conversions       |                       |

　　+------------------------------+-----------------------+

　　16 rows in set (0.00 sec)

　　mysql> show global variables like 'sql%';

　　+------------------------+----------------------+

　　| Variable_name          | Value                |

　　+------------------------+----------------------+

　　| sql_auto_is_null       | OFF                  |

　　| sql_big_selects        | ON                   |

　　| sql_buffer_result      | OFF                  |

　　| sql_log_bin            | ON                   |

　　| sql_log_off            | OFF                  |

　　| sql_mode               |                      |

　　| sql_notes              | ON                   |

　　| sql_quote_show_create | ON                   |

　　| sql_safe_updates       | OFF                  |

　　| sql_select_limit       | 18446744073709551615 |

　　| sql_slave_skip_counter | 0                    |

　　| sql_warnings           | OFF                  |

　　+------------------------+----------------------+

　　mysql> show global variables like 'innodb%';

　　+------------------------------------------+-------------------------+

　　| Variable_name                            | Value                   |

　　+------------------------------------------+-------------------------+

　　| innodb_adaptive_flushing                 | ON                      |

　　| innodb_adaptive_flushing_lwm             | 10                      |

　　| innodb_adaptive_hash_index               | ON                      |

　　| innodb_adaptive_hash_index_parts         | 8                       |

　　| innodb_adaptive_max_sleep_delay          | 150000                  |

　　| innodb_additional_mem_pool_size          | 2097152                 |

　　| innodb_api_bk_commit_interval            | 5                       |

　　| innodb_api_disable_rowlock               | OFF                     |

　　| innodb_api_enable_binlog                 | OFF                     |

　　| innodb_api_enable_mdl                    | OFF                     |

　　| innodb_api_trx_level                     | 0                       |

　　| innodb_autoextend_increment              | 64                      |

　　| innodb_autoinc_lock_mode                 | 1                       |

　　| innodb_buffer_pool_dump_at_shutdown      | OFF                     |

　　| innodb_buffer_pool_dump_now              | OFF                     |

　　| innodb_buffer_pool_filename              | ib_buffer_pool          |

　　| innodb_buffer_pool_instances             | 8                       |

　　| innodb_buffer_pool_load_abort            | OFF                     |

　　| innodb_buffer_pool_load_at_startup       | OFF                     |

　　| innodb_buffer_pool_load_now              | OFF                     |

　　| innodb_buffer_pool_size                  | 12884901888             |

　　| innodb_change_buffer_max_size            | 25                      |

　　| innodb_change_buffering                  | all                     |

　　| innodb_checksum_algorithm                | innodb                  |

　　| innodb_checksums                         | ON                      |

　　| innodb_cmp_per_index_enabled             | OFF                     |

　　| innodb_commit_concurrency                | 0                       |

　　| innodb_compression_failure_threshold_pct | 5                       |

　　| innodb_compression_level                 | 6                       |

　　| innodb_compression_pad_pct_max           | 50                      |

　　| innodb_concurrency_tickets               | 5000                    |

　　| innodb_data_file_path                    | ibdata1:200M:autoextend |

　　| innodb_data_home_dir                     |                         |

　　| innodb_defragment                        | OFF                     |

　　| innodb_defragment_fill_factor            | 0.900000                |

　　| innodb_defragment_fill_factor_n_recs     | 20                      |

　　| innodb_defragment_frequency              | 40                      |

　　| innodb_defragment_n_pages                | 7                       |

　　| innodb_defragment_stats_accuracy         | 0                       |

　　| innodb_disable_sort_file_cache           | ON                      |

　　| innodb_doublewrite                       | ON                      |

　　| innodb_encrypt_algorithm                 | aes_128_ecb             |

　　| innodb_fast_shutdown                     | 1                       |

　　| innodb_file_format                       | Barracuda               |

　　| innodb_file_format_check                 | ON                      |

　　| innodb_file_format_max                   | Barracuda               |

　　| innodb_file_per_table                    | ON                      |

　　| innodb_flush_log_at_timeout              | 1                       |

　　| innodb_flush_log_at_trx_commit           | 1                       |

　　| innodb_flush_method                      | O_DIRECT                |

　　| innodb_flush_neighbors                   | 1                       |

　　| innodb_flushing_avg_loops                | 30                      |

　　| innodb_force_load_corrupted              | OFF                     |

　　| innodb_force_recovery                    | 0                       |

　　| innodb_ft_aux_table                      |                         |

　　| innodb_ft_cache_size                     | 8000000                 |

　　| innodb_ft_enable_diag_print              | OFF                     |

　　| innodb_ft_enable_stopword                | ON                      |

　　| innodb_ft_max_token_size                 | 84                      |

　　| innodb_ft_min_token_size                 | 3                       |

　　| innodb_ft_num_word_optimize              | 2000                    |

　　| innodb_ft_result_cache_limit             | 2000000000              |

　　| innodb_ft_server_stopword_table          |                         |

　　| innodb_ft_sort_pll_degree                | 2                       |

　　| innodb_ft_total_cache_size               | 640000000               |

　　| innodb_ft_user_stopword_table            |                         |

　　| innodb_io_capacity                       | 2000                    |

　　| innodb_io_capacity_max                   | 4000                    |

　　| innodb_large_prefix                      | OFF                     |

　　| innodb_lock_wait_timeout                 | 50                      |

　　| innodb_locks_unsafe_for_binlog           | OFF                     |

　　| innodb_log_buffer_size                   | 8388608                 |

　　| innodb_log_compressed_pages              | OFF                     |

　　| innodb_log_file_size                     | 1572864000              |

　　| innodb_log_files_in_group                | 2                       |

　　| innodb_log_group_home_dir                |                         |

　　| innodb_lru_scan_depth                    | 1024                    |

　　| innodb_max_dirty_pages_pct               | 75                      |

　　| innodb_max_dirty_pages_pct_lwm           | 0                       |

　　| innodb_max_purge_lag                     | 0                       |

　　| innodb_max_purge_lag_delay               | 0                       |

　　| innodb_mirrored_log_groups               | 1                       |

　　| innodb_monitor_disable                   |                         |

　　| innodb_monitor_enable                    |                         |

　　| innodb_monitor_reset                     |                         |

　　| innodb_monitor_reset_all                 |                         |

　　| innodb_old_blocks_pct                    | 37                      |

　　| innodb_old_blocks_time                   | 1000                    |

　　| innodb_online_alter_log_max_size         | 134217728               |

　　| innodb_open_files                        | 3000                    |

　　| innodb_optimize_fulltext_only            | OFF                     |

　　| innodb_page_size                         | 16384                   |

　　| innodb_print_all_deadlocks               | OFF                     |

　　| innodb_purge_batch_size                  | 300                     |

　　| innodb_purge_threads                     | 1                       |

　　| innodb_random_read_ahead                 | OFF                     |

　　| innodb_rds_quick_lru_limit_per_instance | 4096                    |

　　| innodb_rds_trx_own_block_max             | 128                     |

　　| innodb_read_ahead_threshold              | 56                      |

　　| innodb_read_io_threads                   | 4                       |

　　| innodb_read_only                         | OFF                     |

　　| innodb_replication_delay                 | 0                       |

　　| innodb_rollback_on_timeout               | OFF                     |

　　| innodb_rollback_segments                 | 128                     |

　　| innodb_sort_buffer_size                  | 1048576                 |

　　| innodb_spin_wait_delay                   | 30                      |

　　| innodb_stats_auto_recalc                 | ON                      |

　　| innodb_stats_method                      | nulls_equal             |

　　| innodb_stats_on_metadata                 | OFF                     |

　　| innodb_stats_persistent                  | ON                      |

　　| innodb_stats_persistent_sample_pages     | 20                      |

　　| innodb_stats_sample_pages                | 8                       |

　　| innodb_stats_transient_sample_pages      | 8                       |

　　| innodb_status_output                     | OFF                     |

　　| innodb_status_output_locks               | OFF                     |

　　| innodb_strict_mode                       | OFF                     |

　　| innodb_support_xa                        | ON                      |

　　| innodb_sync_array_size                   | 1                       |

　　| innodb_sync_spin_loops                   | 100                     |

　　| innodb_table_locks                       | ON                      |

　　| innodb_thread_concurrency                | 0                       |

　　| innodb_thread_sleep_delay                | 10000                   |

　　| innodb_undo_directory                    | .                       |

　　| innodb_undo_logs                         | 128                     |

　　| innodb_undo_tablespaces                  | 0                       |

　　| innodb_use_native_aio                    | OFF                     |

　　| innodb_use_sys_malloc                    | ON                      |

　　| innodb_version                           | 5.6.16                  |

　　| innodb_write_io_threads                  | 4                       |

　　+------------------------------------------+-------------------------+

　　129 rows in set (0.01 sec)

　　十、執行時間

　　1.上千萬級表：    40分鐘

　　2.幾張過億的表： 1個多小時

　　這樣的速度算很快了

　　十一、參考：

　　 1）pt-online-schema-change解讀

　　 2）pt-online-schema-change使用說明、限制與比較

　　十二、pt-osc 介紹

　　1. pt-osc工作過程

　　（1）創建一個和要執行 alter 操作的表一樣的新的空表結構(是alter之前的結構)

　　（2）在新表執行alter table 語句（速度應該很快）

　　（3）在原表中創建觸發器3個觸發器分別對應insert,update,delete操作

　　（4）以一定塊大小從原表拷貝數據到臨時表，拷貝過程中通過原表上的觸發器在原表進行的寫操作都會更新到新建的臨時表

　　（5）Rename 原表到old表中，在把臨時表Rename為原表

　　（6）如果有參考該表的外鍵，根據alter-foreign-keys-method參數的值，檢測外鍵相關的表，做相應設置的處理

　　（7）默認最后將舊原表刪除

　　2. 常用選項說明

　　只介紹部分常用的選項

　　–host=xxx –user=xxx –password=xxx

　　連接實例信息，縮寫-h xxx -u xxx -p xxx，密碼可以使用參數–ask-pass 手動輸入。

　　–alter

　　結構變更語句，不需要 ALTER TABLE關鍵字。與原始ddl一樣可以指定多個更改，用逗號分隔。

　　絕大部分情況下表上需要有主鍵或唯一索引，因為工具在運行當中為了保證新表也是最新的，需要舊表上創建 DELETE和UPDATE 觸發器，同步到新表的時候有主鍵會更快。個別情況是，當alter操作就是在c1列上建立主鍵時，DELETE觸發器將基于c1列。

　　子句不支持 rename 去給表重命名。

　　alter命令原表就不支持給索引重命名，需要先drop再add，在pt-osc也一樣。(mysql 5.7 支持 RENAME INDEX old_index_name TO new_index_name)

　　但給字段重命名，千萬不要drop-add，整列數據會丟失，使用change col1 col1_new type constraint（保持類型和約束一致，否則相當于修改 column type，不能online）

　　子句如果是add column并且定義了not null，那么必須指定default值，否則會失敗。

　　如果要刪除外鍵（名 fk_foo），使用工具的時候外鍵名要加下劃線，比如–alter “DROP FOREIGN KEY _fk_foo”

　　D=db_name,t=table_name

　　指定要ddl的數據庫名和表名

　　–max-load

　　默認為Threads_running=25。每個chunk拷貝完后，會檢查 SHOW GLOBAL STATUS 的內容，檢查指標是否超過了指定的閾值。如果超過，則先暫停。這里可以用逗號分隔，指定多個條件，每個條件格式： status指標=MAX_VALUE或者status指標:MAX_VALUE。如果不指定MAX_VALUE，那么工具會這只其為當前值的120%。

　　因為拷貝行有可能會給部分行上鎖，Threads_running 是判斷當前數據庫負載的絕佳指標。

　　–max-lag

　　默認1s。每個chunk拷貝完成后，會查看所有復制Slave的延遲情況（Seconds_Behind_Master）。要是延遲大于該值，則暫停復制數據，直到所有從的滯后小于這個值。–check-interval配合使用，指定出現從庫滯后超過 max-lag，則該工具將睡眠多長時間，默認1s，再檢查。如–max-lag=5 –check-interval=2。

　　熟悉percona-toolkit的人都知道–recursion-method可以用來指定從庫dsn記錄。另外，如果從庫被停止，將會永遠等待，直到從開始同步，并且延遲小于該值。

　　–chunk-time

　　默認0.5s，即拷貝數據行的時候，為了盡量保證0.5s內拷完一個chunk，動態調整chunk-size的大小，以適應服務器性能的變化。

　　也可以通過另外一個選項–chunk-size禁止動態調整，即每次固定拷貝 1k 行，如果指定則默認1000行，且比 chunk-time 優先生效

　　–set-vars

　　使用pt-osc進行ddl要開一個session去操作，set-vars可以在執行alter之前設定這些變量，比如默認會設置–set-vars “wait_timeout=10000,innodb_lock_wait_timeout=1,lock_wait_timeout=60”。

　　因為使用pt-osc之后ddl的速度會變慢，所以預計2.5h只能還不能改完，記得加大wait_timeout。

　　–dry-run

　　創建和修改新表，但不會創建觸發器、復制數據、和替換原表。并不真正執行，可以看到生成的執行語句，了解其執行步驟與細節，和–print配合最佳。。

　　–execute

　　確定修改表，則指定該參數。真正執行alter。–dry-run與–execute必須指定一個，二者相互排斥。

（編輯：武林網）

上一篇：簡單的這些不為人知的小技巧你都知道嗎？

下一篇：重返行數比例高不走索引