percona-toolkit 大表操作DDL使用

OpenInfo 2019-09-03

367

本文包含 percona-toolkit 对表字段的增删改操作；表索引的添加与删除；表主键的删除与添加；以及多个操作合并执行。

之前打算在开头也写数据库的安装与准备工作，如：在CentOS 7 上安装 MariaDB，数据库字符集的修改，建库与授权等等。但是想了下，这些东西虽然可以增加文章的完整性，用户也可以跟着从头到尾操作完，但是却和文章的主题有冲突，因此这部分就省略了。

如果想了解这部分，那么请参考如下博文：

http://www.zhangblog.com/2019/09/02/percona01/

部分注意事项在这里也说下：

1、本文涉及的数据库与表信息。

# 数据库名：zhangtest01
# 表信息如下：
CREATE TABLE `zhang_test` (
  `id` int(10) NOT NULL,
  `name` varchar(50) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、请按照如下正确授权，不然会操作失败。

MariaDB [(none)]> grant all on zhangtest01.* to zhang01@'%' identified by 'zhang01';  # 错误授权【大表操作时会失败】 
MariaDB [(none)]> grant all on *.* to zhang06@'%' identified by 'zhang06';  # 正确授权

3、批量生成插入语句脚本

[root@zhang database]# vim batch_insert.sh 
#!/bin/sh


for i in `echo {1..2000000}`;do
  echo "INSERT INTO zhangtest01.zhang_test (id, name, address) VALUES ('${i}', 'test${i}', '中国XX省XX市${i}'); "


done

执行脚本将插入数据放到一个文本中，之后导入数据库即可

percona-toolkit 安装

# 官网下载
[root@zhang tools]# wget https://www.percona.com/downloads/percona-toolkit/3.0.10/binary/redhat/7/x86_64/percona-toolkit-3.0.10-1.el7.x86_64.rpm  
………………
[root@zhang tools]# yum install -y percona-toolkit-3.0.10-1.el7.x86_64.rpm  
………………
[root@docker01 tools]# pt-online-schema-change --help # 帮助文档
………………

大表 DDL 操作

添加表字段【并保存原始表】

[root@docker01 tools]# pt-online-schema-change -h172.16.1.14 -uzhang06 -pzhang06 --nocheck-replication-filters --nodrop-old-table --charset=UTF8 --max-load="Threads_running=1000" --alter "add uuid varchar(100) NOT NULL DEFAULT '0' COMMENT 'UUID' after id" D=zhangtest01,t=zhang_test --execute  # 语句
No slaves found.  See --recursion-method if host zhang 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 `zhangtest01`.`zhang_test`...
Creating new table...
Created new table zhangtest01._zhang_test_new OK.
Altering new table...
Altered `zhangtest01`.`_zhang_test_new` OK.
2018-05-23T13:02:13 Creating triggers...
2018-05-23T13:02:13 Created triggers OK.
2018-05-23T13:02:13 Copying approximately 2006480 rows...
Copying `zhangtest01`.`zhang_test`:  74% 00:10 remain
2018-05-23T13:02:21 Copied rows OK.
2018-05-23T13:02:21 Swapping tables...
2018-05-23T13:02:21 Swapped original and new tables OK.
Not dropping old table because --no-drop-old-table was specified.
2018-05-23T13:02:21 Dropping triggers...
2018-05-23T13:02:21 Dropped triggers OK.
Successfully altered `zhangtest01`.`zhang_test`.

修改后的表结构

CREATE TABLE `zhang_test` (
  `id` int(10) NOT NULL,
  `uuid` varchar(100) NOT NULL DEFAULT '0' COMMENT 'UUID',
  `name` varchar(50) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

命令参数说明

--nocheck-replication-filters
不检查复制过滤器【有主从复制也照样执行】


--[no]drop-old-table
    操作完后是否删除原始表【默认TRUE】


--critical-load="Threads_running=1000"  # 终止拷贝【不优先使用】
    类似于--max-load，不同的是检测到超高负载时会直接中断OSC进程而不是暂停
--max-load="Threads_running=1000"        # 暂停拷贝【优先使用】
    默认如果检测到服务器负载过重会暂停操作
    检查每个块后显示全局状态，如果负载太高暂停（默认Threads_running＝25）


D=zhangtest01
    操作的哪个数据库


t=zhang_test
    操作哪张表


--execute
    执行操作

修改表字段

[root@docker01 ~]# pt-online-schema-change -h172.16.1.14 -uzhang06 -pzhang06 --nocheck-replication-filters --charset=UTF8 --max-load="Threads_running=1000" --alter "MODIFY uuid varchar(80)" D=zhangtest01,t=zhang_test --execute  
[root@docker01 ~]# pt-online-schema-change -h172.16.1.14 -uzhang06 -pzhang06 --nocheck-replication-filters --charset=UTF8 --max-load="Threads_running=1000" --alter "MODIFY uuid int(11)" D=zhangtest01,t=zhang_test --execute  
No slaves found.  See --recursion-method if host zhang 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 `zhangtest01`.`zhang_test`...
Creating new table...
Created new table zhangtest01._zhang_test_new OK.
Altering new table...
Altered `zhangtest01`.`_zhang_test_new` OK.
2018-05-23T22:11:15 Creating triggers...
2018-05-23T22:11:15 Created triggers OK.
2018-05-23T22:11:15 Copying approximately 2005915 rows...
2018-05-23T22:11:22 Copied rows OK.
2018-05-23T22:11:22 Swapping tables...
2018-05-23T22:11:22 Swapped original and new tables OK.
2018-05-23T22:11:22 Dropping old table...
2018-05-23T22:11:22 Dropped old table `zhangtest01`.`__zhang_test_old` OK.
2018-05-23T22:11:22 Dropping triggers...
2018-05-23T22:11:22 Dropped triggers OK.
Successfully altered `zhangtest01`.`zhang_test`.

删除表字段

[root@docker01 ~]# pt-online-schema-change -h172.16.1.14 -uzhang06 -pzhang06 --nocheck-replication-filters --charset=UTF8 --max-load="Threads_running=1000" --alter "DROP uuid" D=zhangtest01,t=zhang_test --execute  
No slaves found.  See --recursion-method if host zhang 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 `zhangtest01`.`zhang_test`...
Creating new table...
Created new table zhangtest01._zhang_test_new OK.
Altering new table...
Altered `zhangtest01`.`_zhang_test_new` OK.
2018-05-23T22:04:38 Creating triggers...
2018-05-23T22:04:38 Created triggers OK.
2018-05-23T22:04:38 Copying approximately 1996965 rows...
2018-05-23T22:04:47 Copied rows OK.
2018-05-23T22:04:47 Swapping tables...
2018-05-23T22:04:47 Swapped original and new tables OK.
2018-05-23T22:04:47 Dropping old table...
2018-05-23T22:04:47 Dropped old table `zhangtest01`.`__zhang_test_old` OK.
2018-05-23T22:04:47 Dropping triggers...
2018-05-23T22:04:47 Dropped triggers OK.
Successfully altered `zhangtest01`.`zhang_test`.

添加表索引

[root@docker01 ~]# pt-online-schema-change -h172.16.1.14 -uzhang06 -pzhang06 --nocheck-replication-filters --charset=UTF8 --max-load="Threads_running=1000" --alter "ADD INDEX index_name(name)" D=zhangtest01,t=zhang_test --execute   # ADD INDEX indexName(columnName) 
No slaves found.  See --recursion-method if host zhang 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 `zhangtest01`.`zhang_test`...
Creating new table...
Created new table zhangtest01._zhang_test_new OK.
Altering new table...
Altered `zhangtest01`.`_zhang_test_new` OK.
2018-05-23T22:16:59 Creating triggers...
2018-05-23T22:16:59 Created triggers OK.
2018-05-23T22:16:59 Copying approximately 2013664 rows...
2018-05-23T22:17:12 Copied rows OK.
2018-05-23T22:17:12 Swapping tables...
2018-05-23T22:17:12 Swapped original and new tables OK.
2018-05-23T22:17:12 Dropping old table...
2018-05-23T22:17:12 Dropped old table `zhangtest01`.`__zhang_test_old` OK.
2018-05-23T22:17:12 Dropping triggers...
2018-05-23T22:17:12 Dropped triggers OK.
Successfully altered `zhangtest01`.`zhang_test`.

修改后的表结构

CREATE TABLE `zhang_test` (
  `id` int(10) NOT NULL,
  `uuid` int(11) DEFAULT NULL,
  `name` varchar(50) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

删除表索引

[root@docker01 ~]# pt-online-schema-change -h172.16.1.14 -uzhang06 -pzhang06 --nocheck-replication-filters --charset=UTF8 --max-load="Threads_running=1000" --alter "DROP INDEX index_name" D=zhangtest01,t=zhang_test --execute  # DROP INDEX indexName 
No slaves found.  See --recursion-method if host zhang 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 `zhangtest01`.`zhang_test`...
Creating new table...
Created new table zhangtest01._zhang_test_new OK.
Altering new table...
Altered `zhangtest01`.`_zhang_test_new` OK.
2018-05-23T22:19:31 Creating triggers...
2018-05-23T22:19:31 Created triggers OK.
2018-05-23T22:19:31 Copying approximately 2005445 rows...
2018-05-23T22:19:38 Copied rows OK.
2018-05-23T22:19:38 Swapping tables...
2018-05-23T22:19:38 Swapped original and new tables OK.
2018-05-23T22:19:38 Dropping old table...
2018-05-23T22:19:38 Dropped old table `zhangtest01`.`__zhang_test_old` OK.
2018-05-23T22:19:38 Dropping triggers...
2018-05-23T22:19:38 Dropped triggers OK.
Successfully altered `zhangtest01`.`zhang_test`.

添加唯一索引

##### 注意：确保字段中数据的唯一性，不然会丢失数据
[root@docker01 ~]# pt-online-schema-change -h172.16.1.14 -uzhang06 -pzhang06 --nocheck-replication-filters --nocheck-unique-key-change --charset=UTF8 --max-load="Threads_running=1000" --alter "ADD UNIQUE uniq_index_name(name)" D=zhangtest01,t=zhang_test --execute  # ADD UNIQUE uniqueName(columnName)  
No slaves found.  See --recursion-method if host zhang 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 `zhangtest01`.`zhang_test`...
Creating new table...
Created new table zhangtest01._zhang_test_new OK.
Altering new table...
Altered `zhangtest01`.`_zhang_test_new` OK.
2018-05-23T22:24:31 Creating triggers...
2018-05-23T22:24:31 Created triggers OK.
2018-05-23T22:24:31 Copying approximately 2005445 rows...
Copying `zhangtest01`.`zhang_test`:  69% 00:13 remain
2018-05-23T22:24:44 Copied rows OK.
2018-05-23T22:24:44 Swapping tables...
2018-05-23T22:24:44 Swapped original and new tables OK.
2018-05-23T22:24:44 Dropping old table...
2018-05-23T22:24:44 Dropped old table `zhangtest01`.`__zhang_test_old` OK.
2018-05-23T22:24:44 Dropping triggers...
2018-05-23T22:24:44 Dropped triggers OK.
Successfully altered `zhangtest01`.`zhang_test`.

修改后的表结构

CREATE TABLE `zhang_test` (
  `id` int(10) NOT NULL,
  `uuid` int(11) DEFAULT NULL,
  `name` varchar(50) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

命令参数说明

--nocheck-unique-key-change
    添加该参数选项，才可以添加唯一索引

删除唯一索引

##### 与删除普通索引一样
[root@docker01 ~]# pt-online-schema-change -h172.16.1.14 -uzhang06 -pzhang06 --nocheck-replication-filters --charset=UTF8 --max-load="Threads_running=1000" --alter "DROP INDEX uniq_index_name" D=zhangtest01,t=zhang_test --execute   # DROP UNIQUE uniqueName
No slaves found.  See --recursion-method if host zhang 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 `zhangtest01`.`zhang_test`...
Creating new table...
Created new table zhangtest01._zhang_test_new OK.
Altering new table...
Altered `zhangtest01`.`_zhang_test_new` OK.
2018-05-23T22:35:12 Creating triggers...
2018-05-23T22:35:12 Created triggers OK.
2018-05-23T22:35:12 Copying approximately 2005445 rows...
2018-05-23T22:35:19 Copied rows OK.
2018-05-23T22:35:19 Swapping tables...
2018-05-23T22:35:19 Swapped original and new tables OK.
2018-05-23T22:35:19 Dropping old table...
2018-05-23T22:35:19 Dropped old table `zhangtest01`.`__zhang_test_old` OK.
2018-05-23T22:35:19 Dropping triggers...
2018-05-23T22:35:19 Dropped triggers OK.
Successfully altered `zhangtest01`.`zhang_test`.

删除表主键

重要说明

对主键修改的步骤：
    1、保证有一个唯一索引【如果没有那么就添加一个唯一索引】
    2、删除原主键
    3、添加新主键
    4、删除之前的唯一索引【可选】
原因如下：
The new table `zhangtest01`.`_zhang_test_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger.

[root@docker01 ~]# pt-online-schema-change -h172.16.1.14 -uzhang06 -pzhang06 --nocheck-replication-filters --nocheck-alter --charset=UTF8 --max-load="Threads_running=1000" --alter "DROP PRIMARY KEY" D=zhangtest01,t=zhang_test --execute   
No slaves found.  See --recursion-method if host zhang 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 `zhangtest01`.`zhang_test`...
Creating new table...
Created new table zhangtest01._zhang_test_new OK.
Altering new table...
Altered `zhangtest01`.`_zhang_test_new` OK.
2018-05-23T22:45:28 Creating triggers...
2018-05-23T22:45:28 Created triggers OK.
2018-05-23T22:45:28 Copying approximately 2005445 rows...
2018-05-23T22:45:42 Copied rows OK.
2018-05-23T22:45:42 Swapping tables...
2018-05-23T22:45:42 Swapped original and new tables OK.
2018-05-23T22:45:42 Dropping old table...
2018-05-23T22:45:42 Dropped old table `zhangtest01`.`__zhang_test_old` OK.
2018-05-23T22:45:42 Dropping triggers...
2018-05-23T22:45:42 Dropped triggers OK.
Successfully altered `zhangtest01`.`zhang_test`.

修改后的表结构【没有主键了，但有唯一索引】

CREATE TABLE `zhang_test` (
  `id` int(10) NOT NULL,
  `uuid` int(11) DEFAULT NULL,
  `name` varchar(50) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  UNIQUE KEY `uniq_index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

命令参数说明

--[no]check-alter
    解析-ALTER指定并尝试警告可能的意外行为（默认为“是”）
    如果没有改选项，修改会失败

添加表主键

[root@docker01 ~]# pt-online-schema-change -h172.16.1.14 -uzhang06 -pzhang06 --nocheck-replication-filters --nocheck-alter --charset=UTF8 --max-load="Threads_running=1000" --alter "ADD PRIMARY KEY (id)" D=zhangtest01,t=zhang_test --execute   
No slaves found.  See --recursion-method if host zhang 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 `zhangtest01`.`zhang_test`...
Creating new table...
Created new table zhangtest01._zhang_test_new OK.
Altering new table...
Altered `zhangtest01`.`_zhang_test_new` OK.
2018-05-23T22:47:23 Creating triggers...
2018-05-23T22:47:23 Created triggers OK.
2018-05-23T22:47:23 Copying approximately 2227360 rows...
2018-05-23T22:47:36 Copied rows OK.
2018-05-23T22:47:36 Swapping tables...
2018-05-23T22:47:36 Swapped original and new tables OK.
2018-05-23T22:47:36 Dropping old table...
2018-05-23T22:47:36 Dropped old table `zhangtest01`.`__zhang_test_old` OK.
2018-05-23T22:47:36 Dropping triggers...
2018-05-23T22:47:36 Dropped triggers OK.
Successfully altered `zhangtest01`.`zhang_test`.

修改后的表结构

CREATE TABLE `zhang_test` (
  `id` int(10) NOT NULL,
  `uuid` int(11) DEFAULT NULL,
  `name` varchar(50) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

多个操作合并

[root@docker01 ~]# pt-online-schema-change -h172.16.1.14 -uzhang06 -pzhang06 --nocheck-replication-filters --charset=UTF8 --max-load="Threads_running=1000" --alter "add last_name varchar(20) NOT NULL DEFAULT '' COMMENT '姓名' after name, ADD INDEX index_address(address), add birthday date COMMENT '生日'" D=zhangtest01,t=zhang_test --execute    
No slaves found.  See --recursion-method if host zhang 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 `zhangtest01`.`zhang_test`...
Creating new table...
Created new table zhangtest01._zhang_test_new OK.
Altering new table...
Altered `zhangtest01`.`_zhang_test_new` OK.
2018-05-23T22:55:17 Creating triggers...
2018-05-23T22:55:17 Created triggers OK.
2018-05-23T22:55:17 Copying approximately 1990757 rows...
2018-05-23T22:55:39 Copied rows OK.
2018-05-23T22:55:39 Swapping tables...
2018-05-23T22:55:39 Swapped original and new tables OK.
2018-05-23T22:55:39 Dropping old table...
2018-05-23T22:55:39 Dropped old table `zhangtest01`.`__zhang_test_old` OK.
2018-05-23T22:55:39 Dropping triggers...
2018-05-23T22:55:39 Dropped triggers OK.
Successfully altered `zhangtest01`.`zhang_test`.

修改前的表结构

CREATE TABLE `zhang_test` (
  `id` int(10) NOT NULL,
  `uuid` int(11) DEFAULT NULL,
  `name` varchar(50) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

修改后的表结构

CREATE TABLE `zhang_test` (
  `id` int(10) NOT NULL,
  `uuid` int(11) DEFAULT NULL,
  `name` varchar(50) NOT NULL,
  `last_name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
  `address` varchar(255) DEFAULT NULL,
  `birthday` date DEFAULT NULL COMMENT '生日',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_index_name` (`name`),
  KEY `index_address` (`address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

如果觉得不错就点个赞呗 (-^O^-)！

———END———

数据库

文章转载自OpenInfo，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

percona-toolkit 大表操作DDL使用

评论