Percona-toolkit Online Add delete MySQL index, field (not locked table)

1. Installation Configuration

Yum install perl-dbd-mysql perl-time-hires perl-io-socket-ssl perl-dbi perl-extutils-cbuilder perl-ExtUtils-MakeMaker -y Cd/root/soft tar zxvf percona-toolkit_2.2.11.tar.gz cd percona-toolkit-2.2.11 perl makefile.pl make make Install

2.pt-online-schema-change tool Use

#查看操作的数据表结构

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/54/94/wKioL1SGv67j6-wJAAF_MMumV7I425.jpg "title=" 001. JPG "alt=" wkiol1sgv67j6-wjaaf_mmumv7i425.jpg "/>

2.1 Add fields online, do not lock the table

Pt-online-schema-change-uroot-hlocalhost-p123-s/tmp/mysql.sock--alter= ' Add column name char (4) '--execute D=test,t= User

#-s If there is sock need to add sock path, name denotes a character field name to be added, D represents the test database name, T represents the user table name

#执行结果如下:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/54/94/wKioL1SGwHrQOoX7AAFRA6Q1O2w119.jpg "title=" 002. JPG "alt=" wkiol1sgwhrqoox7aafra6q1o2w119.jpg "/>

2.2 Add index online, do not lock the table

Pt-online-schema-change-uroot-hlocalhost-p123-s/tmp/mysql.sock--alter= ' Add index user_id (u_id,g_id) '--execute D= Test,t=user

#创建一个组合索引user_id (u_id,g_id)

#执行结果如下:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/54/94/wKioL1SGwQTy2UW9AAFPdHiNtr4400.jpg "title=" 003. JPG "alt=" wkiol1sgwqty2uw9aafpdhintr4400.jpg "/>

2.3 Online Delete index, do not lock table

Pt-online-schema-change-uroot-hlocalhost-p123-s/tmp/mysql.sock--alter= ' drop index user_id '--execute D=test,t=user

#-s If there is sock need to add sock path, user_id represents the name of the index to be deleted, D represents the test database name, T represents the user table name

#执行结果如下:

650) this.width=650; "Src=" http://s3.51cto.com/wyfs02/M00/54/96/wKiom1SGwL2hx2EvAAE0TzAgn78491.jpg " Title= "004.jpg" alt= "Wkiom1sgwl2hx2evaae0tzagn78491.jpg"/>

2.4 Delete fields online, do not lock the table

Pt-online-schema-change-uroot-hlocalhost-p123-s/tmp/mysql.sock--alter= ' drop column name '--execute D=test,t=user

#-s If there is sock need to add sock path, name denotes a field name to be added, D represents the test database name, T represents the user table name

#执行结果如下:

650) this.width=650; "Src=" http://s3.51cto.com/wyfs02/M02/54/94/wKioL1SGwZfzN0EZAAEgxdSAc5c665.jpg " Title= "005.jpg" alt= "Wkiol1sgwzfzn0ezaaegxdsac5c665.jpg"/>

Note: The operation of the table must have a primary key otherwise will be an error, this tool is suitable for MyISAM and InnoDB engine, the disadvantage is that the execution time is slow, but does not lock the table, if you want to perform several operations at the same time, the direct comma separation is good.

This article is from the "Chengdu @ Ah-like" blog, please be sure to keep this source http://azhuang.blog.51cto.com/9176790/1588019

Percona-toolkit Online Add delete MySQL index, field (not locked table)