Mysql之pt-online-schema-change在线更新大表加索引
来源:互联网 发布:怎么挖淘宝客户的快递 编辑:程序博客网 时间:2024/06/06 20:24
由于目前生产环境中大表比较多,这里选择了一个600M的小表做一次在线测试。
环境:Ubuntu 12.04
mysql版本 :5.6.29-log
表引擎:Innodb
一、pt-online-schema-change介绍
percona 公司提供的一款在线更新表的工具,更新过程不会锁表,也就是说操作alter的过程不会阻塞写和读取。即使如此,建议大家操作前还是先做好表备份。(备份工具推荐mydumper,这里不细说)
参数说明:
pt-online-schema-change [OPTIONS] DSN
options 可以自行查看 help,DNS 为你要操作的数据库和表。这里有两个参数需要介绍一下:
–dry-run
这个参数不建立触发器,不拷贝数据,也不会替换原表。只是创建和更改新表。
–execute
这个参数的作用和前面工作原理的介绍的一样,会建立触发器,来保证最新变更的数据会影响至新表。注意:如果不加这个参数,这个工具会在执行一些检查后退出。
注:操作的表必须有主键;否则报错:Cannot chunk the original table `houyi`.`ga`: There is no good index and the table is oversized. at ./pt-online-schema-change line 5353.
二、工作原理
1. 创建需要执行alter操作的原表的一个临时表,然后在临时表中更改表结构。
2. 在原表中创建触发器(3个)三个触发器分别对应insert,update,delete操作
3. 从原表拷贝数据到临时表,拷贝过程中在原表进行的写操作都会更新到新建的临时表。
4. Rename 原表到old表中,在把临时表Rename为原表,最后将原表删除(可能不删除),将原表上所创建的触发器删除。
三、正式测试,在线加索引
表:
Rows: 1214314
Avg_row_length: 214
Data_length: 259866624
Max_data_length: 0
Index_length: 719224832
root@xxxx:/data/xxx# pt-online-schema-change -h xx.xx.xx.xx -P 3307 -u wulz -p lynn --charset=utf8 --alter='ADD INDEX `create_time` (`create_time`, `location`, `version`) USING BTREE' --execute D=lynn_test,t=test_table错误:Cannot connect to MySQL: Cannot connect to MySQL because the Perl DBI module is not installed or not found. Run 'perl -MDBI' to see the directories that Perl searches for DBI. If DBI is not installed, try: Debian/Ubuntu apt-get install libdbi-perl RHEL/CentOS yum install perl-DBI OpenSolaris pkg install pkg:/SUNWpmdbi
错误1的原因是 该工具也是perl语言写的,在连接mysql时候所依赖的库不存在。运行
apt-get install libdbi-perl
再次运行:
Cannot connect to MySQL: Cannot connect to MySQL because the Perl DBD::mysql module is not installed or not found. Run 'perl -MDBD::mysql' to see the directories that Perl searches for DBD::mysql. If DBD::mysql is not installed, try: Debian/Ubuntu apt-get install libdbd-mysql-perl RHEL/CentOS yum install perl-DBD-MySQL OpenSolaris pgk install pkg:/SUNWapu13dbd-mysql
错误2:仍然缺少依赖库,按提示安装,再次运行;
DBD::mysql::db selectall_arrayref failed: Access denied; you need (at least one of) the<span style="color:#ff0000;"> REPLICATION SLAVE privilege(s) </span>for this operation [for Statement "SHOW SLAVE HOSTS"] at /usr/local/bin/pt-online-schema-change line 4260.
这次报错提示的是 权限问题了。由于我给的我测试连接用户给的是指定库的 all privileges的权限。但是根据提示来看,少了个复制权限(show slave hosts)。
grant replication slave ON *.* TO 'wulz'@'xx.xx.xx.xx';
这些工作完成后都准备就绪了;
执行:
No slaves found. See --recursion-method if host s0137-gz has slaves.Not checking slave lag because no slaves were found and --check-slave-lag was not specified.Operation, tries, wait: analyze_table, 10, 1 copy_rows, 10, 0.25 create_triggers, 10, 1 drop_triggers, 10, 1 swap_tables, 10, 1 update_foreign_keys, 10, 1Altering `lynn_test`.`test_table`...Creating new table...Created new table lynn_test._test_table_new OK.Altering new table...Altered `lynn_test`.`_test_table_new` OK.2016-05-30T14:31:26 Creating triggers...2016-05-30T14:31:27 Created triggers OK.2016-05-30T14:31:27 Copying approximately 1119876 rows...Copying `lynn_test`.`test_table`: 64% 00:16 remain2016-05-30T14:32:21 Copied rows OK.2016-05-30T14:32:21 Analyzing new table...2016-05-30T14:32:21 Swapping tables...2016-05-30T14:32:21 Swapped original and new tables OK.2016-05-30T14:32:21 Dropping old table...2016-05-30T14:32:21 Dropped old table `lynn_test`.`_<span style="font-family: Arial, Helvetica, sans-serif;">test_table</span><span style="font-family: Arial, Helvetica, sans-serif;">_old` OK.</span>2016-05-30T14:32:21 Dropping triggers...2016-05-30T14:32:22 Dropped triggers OK.Successfully altered `lynn_test`.`test_table`.
操作过程:
新表生成;
完成后,最终只剩下一张表;
- Mysql之pt-online-schema-change在线更新大表加索引
- [MySQL] 表在线重定义 - pt-online-schema-change
- [MySQL]表在线重定义-pt-online-schema-change
- MySQL在线DDL工具pt-online-schema-change
- MySQL在线DDL工具pt-online-schema-change
- MySQL在线DDL工具pt-online-schema-change
- pt-online-schema-change在线修改MYSQL表结构
- MySQL在线DDL工具pt-online-schema-change
- 在线更改MySQL表结构工具pt-online-schema-change
- MySQL 大表在线DML神器--pt-online-schema-change
- MySQL 在线DDL工具 pt-online-schema-change
- 【MySQL】online ddl 工具之pt-online-schema-change
- MYSQL ONLINE DDL--PT-ONLINE-CHANGE-SCHEMA
- pt-online-schema-change
- Mysql5.6 借助工具pt-online-schema-change在线DDL(还有加索引)
- 利用pt-online-schema-change工具在线ddl操作
- percona-toolkit之pt-online-schema-change(在线更改表结构)
- pt-online-schema-change测试
- redis面试题总汇
- AndroidSlidingUpPanel简析
- Lab 相关 Stack overflow(数组分配过大) 解决方法
- PowerDesigner设计建造MySQL数据库(mysql 导入sql文件)
- 获取@font-face所需字体格式
- Mysql之pt-online-schema-change在线更新大表加索引
- spring-test-dbunit的配置和使用
- BZOJ 3224 替罪羊树
- AppCan移动平台开发常见问题解答
- UNIVERSAL IMAGE LOADER. PART 3---ImageLoader详解
- C++中的基类与派生类
- IOS 无障碍开发指导
- 当用jquery进行全选与反选操作时出现只能执行一次成功的情况时解决办法
- thrift rpc框架入门