percona-toolkit大表操作DDL使用

阅读量：4924 次

发布时间：2019-06-11

本文共 28485 字，大约阅读时间需要 94 分钟。

1. 系统与安装数据库

1 [root@zhang ~]# cat /etc/redhat-release  # 也可以使用其他版本  2 CentOS Linux release 7.4.1708 (Core) 3 [root@zhang ~]# yum install -y mariadb mariadb-server  # CentOS7的mysql数据库为mariadb  4 ……………… 5 [root@zhang ~]# systemctl enable mariadb.service   # 开机自启动mariadb  6 Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service. 7 [root@zhang ~]# systemctl start mariadb.service # 启动mariadb  8 [root@zhang ~]# systemctl status mariadb.service # 查看mariadb服务状态  9 ● mariadb.service - MariaDB database server10    Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)11    Active: active (running) since Wed 2018-05-23 17:13:35 CST; 6s ago12   Process: 1755 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)13   Process: 1675 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)14 ………………

2. 数据库准备工作

2.1. 数据库字符集修改

2.1.1. 数据库版本信息

MariaDB [(none)]> select version();+----------------+| version()      |+----------------+| 5.5.56-MariaDB |+----------------+1 row in set (0.00 sec)

2.1.2. 支持哪些字符集

1 MariaDB [(none)]>  show  CHARACTER SET;  2 ## 字符集        描述                            默认校对规则     最大长度   3 +----------+-----------------------------+---------------------+--------+ 4 | Charset  | Description                 | Default collation   | Maxlen | 5 +----------+-----------------------------+---------------------+--------+ 6 | big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 | 7 | dec8     | DEC West European           | dec8_swedish_ci     |      1 | 8 | cp850    | DOS West European           | cp850_general_ci    |      1 | 9 | hp8      | HP West European            | hp8_english_ci      |      1 |10 | koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |11 | latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |12 | latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |13 | swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |14 | ascii    | US ASCII                    | ascii_general_ci    |      1 |15 | ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |16 | sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |17 | hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |18 | tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 |19 | euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 |20 | koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |21 | gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |22 | greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |23 | cp1250   | Windows Central European    | cp1250_general_ci   |      1 |24 | gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |25 | latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |26 | armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 |27 | utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |28 | ucs2     | UCS-2 Unicode               | ucs2_general_ci     |      2 |29 | cp866    | DOS Russian                 | cp866_general_ci    |      1 |30 | keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 |31 | macce    | Mac Central European        | macce_general_ci    |      1 |32 | macroman | Mac West European           | macroman_general_ci |      1 |33 | cp852    | DOS Central European        | cp852_general_ci    |      1 |34 | latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 |35 | utf8mb4  | UTF-8 Unicode               | utf8mb4_general_ci  |      4 |36 | cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 |37 | utf16    | UTF-16 Unicode              | utf16_general_ci    |      4 |38 | cp1256   | Windows Arabic              | cp1256_general_ci   |      1 |39 | cp1257   | Windows Baltic              | cp1257_general_ci   |      1 |40 | utf32    | UTF-32 Unicode              | utf32_general_ci    |      4 |41 | binary   | Binary pseudo charset       | binary              |      1 |42 | geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 |43 | cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 |44 | eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |45 +----------+-----------------------------+---------------------+--------+46 39 rows in set (0.00 sec)

2.1.3. 当前数据库默认字符集

MariaDB [(none)]> show variables like '%character_set%';+--------------------------+----------------------------+| Variable_name            | Value                      |+--------------------------+----------------------------+| character_set_client     | utf8                       |  ## 客户端来源数据使用的字符集| character_set_connection | utf8                       |  ## 连接层字符集| character_set_database   | latin1                     |  ## 当前选中数据库的默认字符集| character_set_filesystem | binary                     || character_set_results    | utf8                       |  ## 查询结果返回字符集| character_set_server     | latin1                     |  ## 默认的内部操作字符集【服务端（数据库）字符】| character_set_system     | utf8                       |  ## 系统元数据(字段名等)字符集【Linux系统字符集】| character_sets_dir       | /usr/share/mysql/charsets/ |+--------------------------+----------------------------+8 rows in set (0.00 sec)

2.1.4. 修改字符集为utf8

1 [root@zhang ~]# vim /etc/my.cnf   2 [client] 3 default-character-set=utf8 4  5 [mysqld] 6 character-set-server=utf8 7 datadir=/var/lib/mysql 8 socket=/var/lib/mysql/mysql.sock 9 # Disabling symbolic-links is recommended to prevent assorted security risks10 symbolic-links=011 # Settings user and group are ignored when systemd is used.12 # If you need to run mysqld under a different user or group,13 # customize your systemd unit file for mariadb according to the14 # instructions in http://fedoraproject.org/wiki/Systemd15 16 [mysqld_safe]17 log-error=/var/log/mariadb/mariadb.log18 pid-file=/var/run/mariadb/mariadb.pid19 20 #21 # include all files from the config directory22 #23 !includedir /etc/my.cnf.d24 25 [root@zhang ~]# systemctl restart mariadb.service # 重启mariadb26 27 # 字符集查看 28 MariaDB [(none)]> show variables like '%character_set%';  29 +--------------------------+----------------------------+30 | Variable_name            | Value                      |31 +--------------------------+----------------------------+32 | character_set_client     | utf8                       |33 | character_set_connection | utf8                       |34 | character_set_database   | utf8                       |35 | character_set_filesystem | binary                     |36 | character_set_results    | utf8                       |37 | character_set_server     | utf8                       |38 | character_set_system     | utf8                       |39 | character_sets_dir       | /usr/share/mysql/charsets/ |40 +--------------------------+----------------------------+41 8 rows in set (0.00 sec)

2.2. 数据库建库、授权操作

2.2.1. 创建数据库

MariaDB [(none)]> create database zhangtest01; Query OK, 1 row affected (0.00 sec)MariaDB [(none)]> show create database zhangtest01; +-------------+----------------------------------------------------------------------+| Database    | Create Database                                                      |+-------------+----------------------------------------------------------------------+| zhangtest01 | CREATE DATABASE `zhangtest01` /*!40100 DEFAULT CHARACTER SET utf8 */ |+-------------+----------------------------------------------------------------------+1 row in set (0.00 sec)MariaDB [(none)]> show databases; +--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || test               || zhangtest01        |+--------------------+5 rows in set (0.00 sec)

2.2.2. 授权

1 MariaDB [(none)]> grant all on zhangtest01.* to zhang01@'%' identified by 'zhang01';  # 错误授权【大表操作时会失败】  2 Query OK, 0 rows affected (0.00 sec) 3  4 MariaDB [(none)]> grant all on *.* to zhang06@'%' identified by 'zhang06';  # 正确授权  5 Query OK, 0 rows affected (0.00 sec) 6  7 MariaDB [(none)]> flush privileges;  # 刷新权限  8 Query OK, 0 rows affected (0.00 sec) 9 10 MariaDB [(none)]> show grants for zhang01@'%' ;  11 +--------------------------------------------------------------------------------------------------------+12 | Grants for zhang01@%                                                                                   |13 +--------------------------------------------------------------------------------------------------------+14 | GRANT USAGE ON *.* TO 'zhang01'@'%' IDENTIFIED BY PASSWORD '*4D6E977808109CE3DEEDEDA4E3EA17CE0F9CC8C1' |15 | GRANT ALL PRIVILEGES ON `zhangtest01`.* TO 'zhang01'@'%'                                               |16 +--------------------------------------------------------------------------------------------------------+17 2 rows in set (0.00 sec)18 19 MariaDB [(none)]> show grants for zhang06@'%' ;  20 +-----------------------------------------------------------------------------------------------------------------+21 | Grants for zhang06@%                                                                                            |22 +-----------------------------------------------------------------------------------------------------------------+23 | GRANT ALL PRIVILEGES ON *.* TO 'zhang06'@'%' IDENTIFIED BY PASSWORD '*45D6EF2FFF78EB89123D0056C9AE2FC6BA6DA0E7' |24 +-----------------------------------------------------------------------------------------------------------------+25 1 row in set (0.00 sec)26 27 mysql> show variables like '%connect%';      28 +--------------------------+-----------------+29 | Variable_name            | Value           |30 +--------------------------+-----------------+31 | character_set_connection | utf8            |32 | collation_connection     | utf8_general_ci |33 | connect_timeout          | 10              |34 | extra_max_connections    | 1               |35 | init_connect             |                 |36 | max_connect_errors       | 10              |37 | max_connections          | 151             |38 | max_user_connections     | 0               |39 +--------------------------+-----------------+40 8 rows in set (0.00 sec)41 42 mysql> show status like '%connect%';    # 连接信息     43 +--------------------------+-------+44 | Variable_name            | Value |45 +--------------------------+-------+46 | Aborted_connects         | 11    |47 | Connections              | 36    |48 | Max_used_connections     | 9     |49 | Ssl_client_connects      | 0     |50 | Ssl_connect_renegotiates | 0     |51 | Ssl_finished_connects    | 0     |52 | Threads_connected        | 5     |53 +--------------------------+-------+54 7 rows in set (0.00 sec)

2.3. 数据库建表、插入数据

2.3.1. 建表语句

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.3.2. 插入语句示例

INSERT INTO `zhangtest01`.`zhang_test` (`id`, `name`, `address`) VALUES ('0', 'test0', '中国XX省XX市0');

2.3.3. 对应的批量SQL语句脚本

[root@zhang database]# vim batch_insert.sh #!/bin/shfor i in `echo {
   1..2000000}`;do  echo "INSERT INTO zhangtest01.zhang_test (id, name, address) VALUES ('${i}', 'test${i}', '中国XX省XX市${i}'); "done

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

3. 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 # 帮助文档………………

4. 大表DDL操作

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

1 [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  # 语句 2 No slaves found.  See --recursion-method if host zhang has slaves. 3 Not checking slave lag because no slaves were found and --check-slave-lag was not specified. 4 Operation, tries, wait: 5   analyze_table, 10, 1 6   copy_rows, 10, 0.25 7   create_triggers, 10, 1 8   drop_triggers, 10, 1 9   swap_tables, 10, 110   update_foreign_keys, 10, 111 Altering `zhangtest01`.`zhang_test`...12 Creating new table...13 Created new table zhangtest01._zhang_test_new OK.14 Altering new table...15 Altered `zhangtest01`.`_zhang_test_new` OK.16 2018-05-23T13:02:13 Creating triggers...17 2018-05-23T13:02:13 Created triggers OK.18 2018-05-23T13:02:13 Copying approximately 2006480 rows...19 Copying `zhangtest01`.`zhang_test`:  74% 00:10 remain20 2018-05-23T13:02:21 Copied rows OK.21 2018-05-23T13:02:21 Swapping tables...22 2018-05-23T13:02:21 Swapped original and new tables OK.23 Not dropping old table because --no-drop-old-table was specified.24 2018-05-23T13:02:21 Dropping triggers...25 2018-05-23T13:02:21 Dropped triggers OK.26 Successfully altered `zhangtest01`.`zhang_test`.

4.1.1. 修改后的表结构

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;

4.1.2. 命令参数说明

--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    执行操作

4.2. 修改表字段

1 [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   2 [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   3 No slaves found.  See --recursion-method if host zhang has slaves. 4 Not checking slave lag because no slaves were found and --check-slave-lag was not specified. 5 Operation, tries, wait: 6   analyze_table, 10, 1 7   copy_rows, 10, 0.25 8   create_triggers, 10, 1 9   drop_triggers, 10, 110   swap_tables, 10, 111   update_foreign_keys, 10, 112 Altering `zhangtest01`.`zhang_test`...13 Creating new table...14 Created new table zhangtest01._zhang_test_new OK.15 Altering new table...16 Altered `zhangtest01`.`_zhang_test_new` OK.17 2018-05-23T22:11:15 Creating triggers...18 2018-05-23T22:11:15 Created triggers OK.19 2018-05-23T22:11:15 Copying approximately 2005915 rows...20 2018-05-23T22:11:22 Copied rows OK.21 2018-05-23T22:11:22 Swapping tables...22 2018-05-23T22:11:22 Swapped original and new tables OK.23 2018-05-23T22:11:22 Dropping old table...24 2018-05-23T22:11:22 Dropped old table `zhangtest01`.`__zhang_test_old` OK.25 2018-05-23T22:11:22 Dropping triggers...26 2018-05-23T22:11:22 Dropped triggers OK.27 Successfully altered `zhangtest01`.`zhang_test`.

4.3. 删除表字段

1 [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   2 No slaves found.  See --recursion-method if host zhang has slaves. 3 Not checking slave lag because no slaves were found and --check-slave-lag was not specified. 4 Operation, tries, wait: 5   analyze_table, 10, 1 6   copy_rows, 10, 0.25 7   create_triggers, 10, 1 8   drop_triggers, 10, 1 9   swap_tables, 10, 110   update_foreign_keys, 10, 111 Altering `zhangtest01`.`zhang_test`...12 Creating new table...13 Created new table zhangtest01._zhang_test_new OK.14 Altering new table...15 Altered `zhangtest01`.`_zhang_test_new` OK.16 2018-05-23T22:04:38 Creating triggers...17 2018-05-23T22:04:38 Created triggers OK.18 2018-05-23T22:04:38 Copying approximately 1996965 rows...19 2018-05-23T22:04:47 Copied rows OK.20 2018-05-23T22:04:47 Swapping tables...21 2018-05-23T22:04:47 Swapped original and new tables OK.22 2018-05-23T22:04:47 Dropping old table...23 2018-05-23T22:04:47 Dropped old table `zhangtest01`.`__zhang_test_old` OK.24 2018-05-23T22:04:47 Dropping triggers...25 2018-05-23T22:04:47 Dropped triggers OK.26 Successfully altered `zhangtest01`.`zhang_test`.

4.4. 添加表索引

1 [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)  2 No slaves found.  See --recursion-method if host zhang has slaves. 3 Not checking slave lag because no slaves were found and --check-slave-lag was not specified. 4 Operation, tries, wait: 5   analyze_table, 10, 1 6   copy_rows, 10, 0.25 7   create_triggers, 10, 1 8   drop_triggers, 10, 1 9   swap_tables, 10, 110   update_foreign_keys, 10, 111 Altering `zhangtest01`.`zhang_test`...12 Creating new table...13 Created new table zhangtest01._zhang_test_new OK.14 Altering new table...15 Altered `zhangtest01`.`_zhang_test_new` OK.16 2018-05-23T22:16:59 Creating triggers...17 2018-05-23T22:16:59 Created triggers OK.18 2018-05-23T22:16:59 Copying approximately 2013664 rows...19 2018-05-23T22:17:12 Copied rows OK.20 2018-05-23T22:17:12 Swapping tables...21 2018-05-23T22:17:12 Swapped original and new tables OK.22 2018-05-23T22:17:12 Dropping old table...23 2018-05-23T22:17:12 Dropped old table `zhangtest01`.`__zhang_test_old` OK.24 2018-05-23T22:17:12 Dropping triggers...25 2018-05-23T22:17:12 Dropped triggers OK.26 Successfully altered `zhangtest01`.`zhang_test`.

4.4.1. 修改后的表结构

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;

4.5. 删除表索引

1 [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  2 No slaves found.  See --recursion-method if host zhang has slaves. 3 Not checking slave lag because no slaves were found and --check-slave-lag was not specified. 4 Operation, tries, wait: 5   analyze_table, 10, 1 6   copy_rows, 10, 0.25 7   create_triggers, 10, 1 8   drop_triggers, 10, 1 9   swap_tables, 10, 110   update_foreign_keys, 10, 111 Altering `zhangtest01`.`zhang_test`...12 Creating new table...13 Created new table zhangtest01._zhang_test_new OK.14 Altering new table...15 Altered `zhangtest01`.`_zhang_test_new` OK.16 2018-05-23T22:19:31 Creating triggers...17 2018-05-23T22:19:31 Created triggers OK.18 2018-05-23T22:19:31 Copying approximately 2005445 rows...19 2018-05-23T22:19:38 Copied rows OK.20 2018-05-23T22:19:38 Swapping tables...21 2018-05-23T22:19:38 Swapped original and new tables OK.22 2018-05-23T22:19:38 Dropping old table...23 2018-05-23T22:19:38 Dropped old table `zhangtest01`.`__zhang_test_old` OK.24 2018-05-23T22:19:38 Dropping triggers...25 2018-05-23T22:19:38 Dropped triggers OK.26 Successfully altered `zhangtest01`.`zhang_test`.

4.6. 添加唯一索引

1 ##### 注意：确保字段中数据的唯一性，不然会丢失数据 2 [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)   3 No slaves found.  See --recursion-method if host zhang has slaves. 4 Not checking slave lag because no slaves were found and --check-slave-lag was not specified. 5 Operation, tries, wait: 6   analyze_table, 10, 1 7   copy_rows, 10, 0.25 8   create_triggers, 10, 1 9   drop_triggers, 10, 110   swap_tables, 10, 111   update_foreign_keys, 10, 112 Altering `zhangtest01`.`zhang_test`...13 Creating new table...14 Created new table zhangtest01._zhang_test_new OK.15 Altering new table...16 Altered `zhangtest01`.`_zhang_test_new` OK.17 2018-05-23T22:24:31 Creating triggers...18 2018-05-23T22:24:31 Created triggers OK.19 2018-05-23T22:24:31 Copying approximately 2005445 rows...20 Copying `zhangtest01`.`zhang_test`:  69% 00:13 remain21 2018-05-23T22:24:44 Copied rows OK.22 2018-05-23T22:24:44 Swapping tables...23 2018-05-23T22:24:44 Swapped original and new tables OK.24 2018-05-23T22:24:44 Dropping old table...25 2018-05-23T22:24:44 Dropped old table `zhangtest01`.`__zhang_test_old` OK.26 2018-05-23T22:24:44 Dropping triggers...27 2018-05-23T22:24:44 Dropped triggers OK.28 Successfully altered `zhangtest01`.`zhang_test`.

4.6.1. 修改后的表结构

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;

4.6.2. 命令参数说明

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

4.7. 删除唯一索引

1 ##### 与删除普通索引一样 2 [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 3 No slaves found.  See --recursion-method if host zhang has slaves. 4 Not checking slave lag because no slaves were found and --check-slave-lag was not specified. 5 Operation, tries, wait: 6   analyze_table, 10, 1 7   copy_rows, 10, 0.25 8   create_triggers, 10, 1 9   drop_triggers, 10, 110   swap_tables, 10, 111   update_foreign_keys, 10, 112 Altering `zhangtest01`.`zhang_test`...13 Creating new table...14 Created new table zhangtest01._zhang_test_new OK.15 Altering new table...16 Altered `zhangtest01`.`_zhang_test_new` OK.17 2018-05-23T22:35:12 Creating triggers...18 2018-05-23T22:35:12 Created triggers OK.19 2018-05-23T22:35:12 Copying approximately 2005445 rows...20 2018-05-23T22:35:19 Copied rows OK.21 2018-05-23T22:35:19 Swapping tables...22 2018-05-23T22:35:19 Swapped original and new tables OK.23 2018-05-23T22:35:19 Dropping old table...24 2018-05-23T22:35:19 Dropped old table `zhangtest01`.`__zhang_test_old` OK.25 2018-05-23T22:35:19 Dropping triggers...26 2018-05-23T22:35:19 Dropped triggers OK.27 Successfully altered `zhangtest01`.`zhang_test`.

4.8. 删除表主键

4.8.1. 重要说明

对主键修改的步骤：    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.

1 [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    2 No slaves found.  See --recursion-method if host zhang has slaves. 3 Not checking slave lag because no slaves were found and --check-slave-lag was not specified. 4 Operation, tries, wait: 5   analyze_table, 10, 1 6   copy_rows, 10, 0.25 7   create_triggers, 10, 1 8   drop_triggers, 10, 1 9   swap_tables, 10, 110   update_foreign_keys, 10, 111 Altering `zhangtest01`.`zhang_test`...12 Creating new table...13 Created new table zhangtest01._zhang_test_new OK.14 Altering new table...15 Altered `zhangtest01`.`_zhang_test_new` OK.16 2018-05-23T22:45:28 Creating triggers...17 2018-05-23T22:45:28 Created triggers OK.18 2018-05-23T22:45:28 Copying approximately 2005445 rows...19 2018-05-23T22:45:42 Copied rows OK.20 2018-05-23T22:45:42 Swapping tables...21 2018-05-23T22:45:42 Swapped original and new tables OK.22 2018-05-23T22:45:42 Dropping old table...23 2018-05-23T22:45:42 Dropped old table `zhangtest01`.`__zhang_test_old` OK.24 2018-05-23T22:45:42 Dropping triggers...25 2018-05-23T22:45:42 Dropped triggers OK.26 Successfully altered `zhangtest01`.`zhang_test`.

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

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;

4.8.3. 命令参数说明

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

4.9. 添加表主键

1 [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    2 No slaves found.  See --recursion-method if host zhang has slaves. 3 Not checking slave lag because no slaves were found and --check-slave-lag was not specified. 4 Operation, tries, wait: 5   analyze_table, 10, 1 6   copy_rows, 10, 0.25 7   create_triggers, 10, 1 8   drop_triggers, 10, 1 9   swap_tables, 10, 110   update_foreign_keys, 10, 111 Altering `zhangtest01`.`zhang_test`...12 Creating new table...13 Created new table zhangtest01._zhang_test_new OK.14 Altering new table...15 Altered `zhangtest01`.`_zhang_test_new` OK.16 2018-05-23T22:47:23 Creating triggers...17 2018-05-23T22:47:23 Created triggers OK.18 2018-05-23T22:47:23 Copying approximately 2227360 rows...19 2018-05-23T22:47:36 Copied rows OK.20 2018-05-23T22:47:36 Swapping tables...21 2018-05-23T22:47:36 Swapped original and new tables OK.22 2018-05-23T22:47:36 Dropping old table...23 2018-05-23T22:47:36 Dropped old table `zhangtest01`.`__zhang_test_old` OK.24 2018-05-23T22:47:36 Dropping triggers...25 2018-05-23T22:47:36 Dropped triggers OK.26 Successfully altered `zhangtest01`.`zhang_test`.

4.9.1. 修改后的表结构

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;

4.10. 多个操作合一

1 [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     2 No slaves found.  See --recursion-method if host zhang has slaves. 3 Not checking slave lag because no slaves were found and --check-slave-lag was not specified. 4 Operation, tries, wait: 5   analyze_table, 10, 1 6   copy_rows, 10, 0.25 7   create_triggers, 10, 1 8   drop_triggers, 10, 1 9   swap_tables, 10, 110   update_foreign_keys, 10, 111 Altering `zhangtest01`.`zhang_test`...12 Creating new table...13 Created new table zhangtest01._zhang_test_new OK.14 Altering new table...15 Altered `zhangtest01`.`_zhang_test_new` OK.16 2018-05-23T22:55:17 Creating triggers...17 2018-05-23T22:55:17 Created triggers OK.18 2018-05-23T22:55:17 Copying approximately 1990757 rows...19 2018-05-23T22:55:39 Copied rows OK.20 2018-05-23T22:55:39 Swapping tables...21 2018-05-23T22:55:39 Swapped original and new tables OK.22 2018-05-23T22:55:39 Dropping old table...23 2018-05-23T22:55:39 Dropped old table `zhangtest01`.`__zhang_test_old` OK.24 2018-05-23T22:55:39 Dropping triggers...25 2018-05-23T22:55:39 Dropped triggers OK.26 Successfully altered `zhangtest01`.`zhang_test`.

4.10.1. 修改前的表结构

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;

4.10.2. 修改后的表结构

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;