pt-online-schema-change手记

需求：

创新互联网站建设由有经验的网站设计师、开发人员和项目经理组成的专业建站团队，负责网站视觉设计、用户体验优化、交互设计和前端开发等方面的工作，以确保网站外观精美、网站设计、网站制作易于使用并且具有良好的响应性。

应大数据部门要求，需要在指定表增加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;

官网文档：

http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html

http://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html

（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

2）

五、执行期间异常回退方法

执行期间如果有报错，举例表名时候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 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解读

https://www.cnblogs.com/xiaoyanger/p/6043986.html

2）pt-online-schema-change使用说明、限制与比较

https://blog.csdn.net/lijingkuan/article/details/68951089

十二、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一样可以指定多个更改，用逗号分隔。

新闻名称：pt-online-schema-change手记
转载来源：http://dzwzjz.com/article/jsjpcc.html

大橙子网站建设，新征程启航

pt-online-schema-change手记

其他资讯