Mysql之pt-online-schema-change在线更新大表加索引

原创 2016年05月30日 15:06:28

由于目前生产环境中大表比较多,这里选择了一个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, 1
Altering `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 remain
2016-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千万级大表在线加索引

 create table tmp like paper_author; ALTER TABLE tmp ADD INDEX ( `PaperID` ) insert into tmp(o...
  • u013834131
  • u013834131
  • 2014年03月31日 23:41
  • 4194

mysql 5.6在线DDL建索引测试

基本信息: mysql版本: (product)root@localhost [(none)]> select @@version; +------------+ | @@version  | +--...
  • zengxuewen2045
  • zengxuewen2045
  • 2016年04月23日 16:12
  • 1093

pt-online-schema-change在线修改MYSQL表结构

pt-online-schema-chang为Percona Toolkit其中一个在线修改表结构的工具,其中原理为:创建临时表-》修改修改结构-》记录数据-》删掉原表-》重命名临时表 安装:wge...
  • u011078940
  • u011078940
  • 2017年02月22日 18:19
  • 453

mysql工具percona-toolkit之安装

MySQL数据库是轻量级、开源数据库的佼佼者,其功能和管理,健壮性与Oracle相比还是有相当的差距。因此有很多功能强大第三方的衍生产品, 如percona-toolkit,XtraBackup等等。...
  • jc_benben
  • jc_benben
  • 2016年10月19日 10:47
  • 994

[MySQL 工具] pt-mysql-summary使用分析mysq数据库

pt-mysql-summary:本地安装、然后远程收集MySQL Server的基本信息 在安装percona-toolkit的时候,这个组件也一起安装完成了:安装参考:http://blog.c...
  • mchdba
  • mchdba
  • 2013年11月07日 11:58
  • 4746

快速对Mysql添加索引的五个方法

1.添加PRIMARY KEY(主键索引) mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 2.添加UNIQ...
  • zhangketuan
  • zhangketuan
  • 2016年01月25日 14:26
  • 2193

XtraBackup,在使用innobackupex 中出现了错误: perl-DBD-MySQL 无法安装

注:部分内容转载 问题描述: 在使用innobackupex 中出现了错误   -bash-4.1$ innobackupex --defaults-file=./my_3304.cnf --use...
  • lvxiaoqing
  • lvxiaoqing
  • 2015年05月12日 15:00
  • 2061

Perl中DBI和DBD-mysql模块的安装

一、DBI的安装wget http://www.cpan.org/modules/by-module/DBD/DBI-1.604.tar.gztar -zxvf DBI-1.604.tar.gzcd ...
  • liuzhoulong
  • liuzhoulong
  • 2013年06月27日 11:26
  • 5041

Mysql5.6 借助工具pt-online-schema-change在线DDL(还有加索引)

MySQL在线ddl会阻塞所有insert,可以使用percona-toolkit中的pt-online-schema-change工具来对表进行online。 D=tpcctest 数据库名 t=c...
  • guogang83
  • guogang83
  • 2017年06月12日 16:16
  • 338

mysql 5.6在线DDL建索引测试

基本信息: mysql版本: (product)root@localhost [(none)]> select @@version; +------------+ | @@version  | +--...
  • zengxuewen2045
  • zengxuewen2045
  • 2016年04月23日 16:12
  • 1093
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Mysql之pt-online-schema-change在线更新大表加索引
举报原因:
原因补充:

(最多只允许输入30个字)