pt-online-schema-change手记

2022-07-15 12:02:44

字体：大中小

来源：转载

供稿：网友

　　需求：

　　   应大数据部门要求，需要在指定表增加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必须指定一个，二者相互排斥。

（编辑：错新网）

上一篇：重返行数比例高不走索引

下一篇：简单的这些不为人知的小技巧你都知道吗？