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

﹏ヽ暗。殇╰゛Y 2022-06-14 06:22 200阅读 0赞

MySQL在线ddl会阻塞所有insert，可以使用percona-toolkit中的pt-online-schema-change工具来对表进行online。

D=tpcctest 数据库名

t=customer 表名

\[root@localhost bin\]\# **./pt-online-schema-change -u root -h127.0.0.1 -proot  --alter='add column col int ' --execute D=tpcctest,t=customer **

No slaves found.  See --recursion-method if host localhost.localdomain has slaves.  
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.  
Operation, tries, wait:  
  copy\_rows, 10, 0.25  
  create\_triggers, 10, 1  
  drop\_triggers, 10, 1  
  swap\_tables, 10, 1  
  update\_foreign\_keys, 10, 1  
Altering \`tpcctest\`.\`customer\`...  
Creating new table...  
Created new table tpcctest.\_customer\_new OK.  
Altering new table...  
Altered \`tpcctest\`.\`\_customer\_new\` OK.  
2017-06-12T01:05:03 Creating triggers...  
2017-06-12T01:05:03 Created triggers OK.  
2017-06-12T01:05:03 Copying approximately 57629 rows...  
2017-06-12T01:05:08 Copied rows OK.  
2017-06-12T01:05:08 Swapping tables...  
2017-06-12T01:05:08 Swapped original and new tables OK.  
2017-06-12T01:05:08 Dropping old table...  
2017-06-12T01:05:08 Dropped old table \`tpcctest\`.\`\_customer\_old\` OK.  
2017-06-12T01:05:08 Dropping triggers...  
2017-06-12T01:05:08 Dropped triggers OK.

Successfully altered \`tpcctest\`.\`customer\`.

**online的操作步骤是：**

1、创建一个和原来表结构一样的临时表并ddl;  
2、将原表数据写入到临时表中（通过insert ...select方式）,并且在原表上创建触发器，如果原表有数据写入，通过触发器方式将新增的数据写入临时表中(前提该表之前没有触发器);  
3、写入完后，重命名临时表和原表名称;  
4、删除原表。

注：表一定要有主键。

mysql> create table t2(id int primary key,name int);

mysql> show create table t2;                
\+-------+---------------------------------  
| Table | Create Table                      
\+-------+---------------------------------  
| t2    | CREATE TABLE \`t2\` (               
  \`id\` int(11) NOT NULL,                    
  \`name\` int(11) DEFAULT NULL,              
  PRIMARY KEY (\`id\`)                        
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |      
\+-------+------------------------------

mysql> insert into t2 values(1,1);  
Query OK, 1 row affected (0.00 sec)  
mysql> insert into t2 values(2,2);  
Query OK, 1 row affected (0.01 sec)

\[root@localhost bin\]\# **./pt-online-schema-change --user=root -proot  --host=localhost --socket=/opt/mysql/mysqldata/3306/mysql.sock --alter "add INDEX idx\_t2\_id(id)" D=test,t=t2  --execute**  
No slaves found.  See --recursion-method if host localhost.localdomain has slaves.  
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.  
Operation, tries, wait:  
  copy\_rows, 10, 0.25  
  create\_triggers, 10, 1  
  drop\_triggers, 10, 1  
  swap\_tables, 10, 1  
  update\_foreign\_keys, 10, 1  
Altering \`test\`.\`t2\`...  
Creating new table...  
Created new table test.\_t2\_new OK.  
Altering new table...  
Altered \`test\`.\`\_t2\_new\` OK.  
2017-06-20T01:39:24 Creating triggers...  
2017-06-20T01:39:24 Created triggers OK.  
2017-06-20T01:39:24 Copying approximately 2 rows...  
2017-06-20T01:39:24 Copied rows OK.  
2017-06-20T01:39:24 Swapping tables...  
2017-06-20T01:39:24 Swapped original and new tables OK.  
2017-06-20T01:39:24 Dropping old table...  
2017-06-20T01:39:24 Dropped old table \`test\`.\`\_t2\_old\` OK.  
2017-06-20T01:39:24 Dropping triggers...  
2017-06-20T01:39:24 Dropped triggers OK.  
Successfully altered \`test\`.\`t2\`.

mysql> show create table t2;               
\+-------+--------------------------------  
| Table | Create Table                     
\+-------+--------------------------------  
| t2    | CREATE TABLE \`t2\` (              
  \`id\` int(11) NOT NULL,                   
  \`name\` int(11) DEFAULT NULL,             
  PRIMARY KEY (\`id\`),                      
  KEY \`idx\_t2\_id\` (\`id\`)                   
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |     
\+-------+--------------------------------