Chinaunix首页 | 论坛 | 博客
  • 博客访问: 344993
  • 博文数量: 87
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 727
  • 用 户 组: 普通用户
  • 注册时间: 2014-11-27 15:56
个人简介

开心了, 就笑;不开心了,就过会儿再笑。。。。

文章分类

全部博文(87)

文章存档

2017年(16)

2016年(28)

2015年(33)

2014年(10)

我的朋友

分类: Mysql/postgreSQL

2014-12-11 11:03:31

本文描述的是企业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

阅读(1869) | 评论(0) | 转发(0) |
0

上一篇:五险一金

下一篇:MySQL修改表结构

给主人留下些什么吧!~~