percona-toolkit大表操作DDL使用

发表于 | 分类于 | | 阅读次数:
字数统计: 4.1k | 阅读时长 ≈ 23

image

操作系统与安装数据库

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

数据库准备工作

数据库字符集修改

数据库版本信息

1
2
3
4
5
6
7
MariaDB [(none)]> select version();
+----------------+
| version()      |
+----------------+
| 5.5.56-MariaDB |
+----------------+
row in set (0.00 sec)

支持哪些字符集

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

当前数据库默认字符集

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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/ |
+--------------------------+----------------------------+
rows in set (0.00 sec)

修改字符集为utf8

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
[root@zhang ~]# vim /etc/my.cnf  
[client]
default-character-set=utf8

[mysqld]
character-set-server=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[root@zhang ~]# systemctl restart mariadb.service # 重启mariadb

# 字符集查看 
MariaDB [(none)]> show variables like '%character_set%';  
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

数据库建库、授权操作

创建数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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 */ |
+-------------+----------------------------------------------------------------------+
row in set (0.00 sec)

MariaDB [(none)]> show databases; 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| zhangtest01        |
+--------------------+
rows in set (0.00 sec)

授权

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
MariaDB [(none)]> grant all on zhangtest01.* to zhang01@'%' identified by 'zhang01';  # 错误授权【大表操作时会失败】 
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant all on *.* to zhang06@'%' identified by 'zhang06';  # 正确授权 
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;  # 刷新权限 
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show grants for zhang01@'%' ;  
+--------------------------------------------------------------------------------------------------------+
| Grants for zhang01@%                                                                                   |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhang01'@'%' IDENTIFIED BY PASSWORD '*4D6E977808109CE3DEEDEDA4E3EA17CE0F9CC8C1' |
| GRANT ALL PRIVILEGES ON `zhangtest01`.* TO 'zhang01'@'%'                                               |
+--------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> show grants for zhang06@'%' ;  
+-----------------------------------------------------------------------------------------------------------------+
| Grants for zhang06@%                                                                                            |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'zhang06'@'%' IDENTIFIED BY PASSWORD '*45D6EF2FFF78EB89123D0056C9AE2FC6BA6DA0E7' |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show variables like '%connect%';      
+--------------------------+-----------------+
| Variable_name            | Value           |
+--------------------------+-----------------+
| character_set_connection | utf8            |
| collation_connection     | utf8_general_ci |
| connect_timeout          | 10              |
| extra_max_connections    | 1               |
| init_connect             |                 |
| max_connect_errors       | 10              |
| max_connections          | 151             |
| max_user_connections     | 0               |
+--------------------------+-----------------+
8 rows in set (0.00 sec)

mysql> show status like '%connect%';    # 连接信息     
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Aborted_connects         | 11    |
| Connections              | 36    |
| Max_used_connections     | 9     |
| Ssl_client_connects      | 0     |
| Ssl_connect_renegotiates | 0     |
| Ssl_finished_connects    | 0     |
| Threads_connected        | 5     |
+--------------------------+-------+
7 rows in set (0.00 sec)

数据库建表、插入数据

建表语句

1
2
3
4
5
6
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;

插入语句示例

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

对应的批量SQL语句脚本

1
2
3
4
5
6
7
[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安装【可以在另外一台机器】

1
2
3
4
5
6
7
# 官网下载
[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操作

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
[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`.

修改后的表结构

1
2
3
4
5
6
7
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;

命令参数说明

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
--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
    执行操作

修改表字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
[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`.

删除表字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
[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`.

添加表索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
[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`.

修改后的表结构

1
2
3
4
5
6
7
8
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;

删除表索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
[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`.

添加唯一索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
##### 注意：确保字段中数据的唯一性，不然会丢失数据
[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`.

修改后的表结构

1
2
3
4
5
6
7
8
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;

命令参数说明

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

删除唯一索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
##### 与删除普通索引一样
[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
5
6
7
对主键修改的步骤：
    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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
[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`.

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

1
2
3
4
5
6
7
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;

命令参数说明

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

添加表主键

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
[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`.

修改后的表结构

1
2
3
4
5
6
7
8
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;

多个操作合并

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
[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`.

修改前的表结构

1
2
3
4
5
6
7
8
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;

修改后的表结构

1
2
3
4
5
6
7
8
9
10
11
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;

附录：

1、不停机不停服务，MYSQL可以这样修改亿级数据表结构

2、pt-online-schema-change解读

3、Mysql 查看连接数,状态 最大并发数(赞)

<-------------the end------------->
lightzhang wechat
欢迎扫一扫，订阅我的微信公众号！
坚持原创分享，你的支持就是我最大的动力！