mysql pt online_MySQL使用pt-online-schema-change实现在线加字段

本文描述的是企业MySQL使用pt-online-schema-change在线添加字段：

工作原理及步骤 ：

1. 创建需要执行alter操作的原表的一个临时表，然后在临时表中更改表结构。

2. 在原表中创建触发器(3个)三个触发器分别对应insert,update,delete操作

3. 从原表拷贝数据到临时表，拷贝过程中在原表进行的写操作都会更新到新建的临时表。

4. Rename 原表到old表中，在把临时表Rename为原表，最后将原表删除，将原表上所创建的触发器删除

使用注意事项：

1. 该工具所适用的表必须是单一列的主键或者单一唯一键。

2. 在使用之前需要对磁盘容量进行评估，使用OSC会使增加一倍的空间

举例：

/usr/bin/pt-online-schema-change --user=mysqldba --password=PEFD89PgtMg0ats7KUDeh --host=127.0.0.1 --alter=add COLUMN update_time datetime D=escore,t=t_user_3 --execute --nocheck-replication-filters

特别说明的几个参数：

--dry-run   这个参数不建立触发器，不拷贝数据，也不会替换原表。只是创建和更改新表。

--execute  这个参数的作用和前面工作原理的介绍的一样，会建立触发器，来保证最新变更的数据会影响至新表。

--chunk-size  对每次导入行数进行控制，已减少对原表的锁定时间。

--nocheck-replication-filters ：不检查复制过滤器，建议启用。

结论:

1. 该工具有一定的风险，对表批量更新、锁表、优化表的操作时，OSC的性能受到一定的影响。

2. 在表不进行批量的更新操作的时候，OSC是能满足我们的需求的。

需求：对escore库的t_user_0~f 表添加update_time字段

mysql>use escore;

mysql>desc t_user_0;

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

| Field | Type | Null | Key | Default | Extra |

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

| id | bigint(20) | NO | PRI | NULL | auto_increment |

| uuid | varchar(128) | NO | MUL | NULL | |

| imei | varchar(128) | YES | | NULL | |

| tel_num | varchar(40) | YES | | NULL | |

| tel_model | varchar(80) | YES | | NULL | |

| net_env | varchar(40) | YES | | NULL | |

| area_code | varchar(40) | YES | | NULL | |

| operator | varchar(40) | YES | | NULL | |

| os | varchar(200) | YES | | NULL | |

| brand | varchar(400) | YES | | NULL | |

| screen_width | int(11) | YES | | NULL | |

| screen_heigh | int(11) | YES | | NULL | |

| score | bigint(20) | YES | | NULL | |

| create_time | datetime | YES | | NULL | |

| mac | varchar(100) | YES | | NULL | |

| pda_type | int(11) | YES | | NULL | |

| app_id | bigint(20) | YES | | NULL | |

| imsi | varchar(100) | YES | | NULL | |

| area_province | varchar(40) | YES | | NULL | |

| openudid | varchar(200) | YES | | NULL | |

| idfa | varchar(200) | YES | | NULL | |

| jailbroken | varchar(200) | YES | | NULL | |

| idfv | varchar(200) | YES |      | NULL |       |

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

mysql>show databases;     //查看所有的数据库

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

| Database                     |

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

| information_schema  |

| escore                        |

| luckcat                        |

| mysql                          |

| performance_schema |

| test                              |

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

6 rows in set (0.00 sec)

mysql>use escore;    //切换到相应的库中

mysql>show tables;    //查看表，确定要操作的表

..

t_user_0

t_user_1

..

t_user_f

..

mysql>show full processlist;   //查看连接数，几个从库

略。。。

mysql>show variables like '%port%';   //查看端口

...

| report_port | 3306 |

| report_user | |

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

10 rows in set (0.04 sec)

mysql>select host,user,password from mysql.user;

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

| host | user | password |

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

| localhost | root | *6A2D657874282FB53A0E084A77ACCAE8DE1C0B50 |

| 127.0.0.1 | root | *6A2D657874282FB53A0E084A77ACCAE8DE1C0B50 |

| ::1 | root | *6A2D657874282FB53A0E084A77ACCAE8DE1C0B50 |

| % | sst | *0391A114C40BD96CD6F67412C48B1A92D6057391 |

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

4 rows in set (0.06 sec)

mysql>show variables like '%character%';        //查看字符集

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

| Variable_name | Value |

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

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | latin1 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

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

8 rows in set (0.01 sec)

执行在线给escore.t_user_0添加updata_time字段

#pt-online-schema-change --user=mysqldba --password=PEFD89PgtMg0ats7KUDeh --host=127.0.0.1 --alter="add COLUMN update_time datetime" D=escore,t=t_user_0 --execute --nocheck-replication-filters

执行完成后，在主库中可以看到新添加的字段：

mysql>desc  escore.t_user_0;

再从库不会马上有(有延迟)，需要等待一段时间。

知道在从库中：desc  t_user_0;看到更新的字段，整个操作成功完成。

由于这次需要在多个表中添加相同的字段，可以使用批量操作(存在风险)

#for i in `seq 0 9`

>do

>echo “pt-online-schema-change --user=mysqldba --password=PEFD89PgtMg0ats7KUDeh --host=127.0.0.1 --alter=\"add COLUMN update_time datetime\" D=escore,t=t_user_$i --execute --nocheck-replication-filters” >> batch.sh

>done

#for i in {a..f}

>do

>echo "pt-online-schema-change --user=mysqldba --password=PEFD89PgtMg0ats7KUDeh --host=127.0.0.1 --alter=\"add COLUMN update_time datetime\" D=escore,t=t_user_$i --execute --nocheck-replication-filters"  >> batch.sh

>done

查看没有错误后，执行batch.sh