percona-toolkit简介

Percona-toolkit起源于管理mysql最的工具Maatkit和Aspersa tools，但是Maatkit不再维护，全部合并到percona-toolkit中。Percona Toolkit是一套用于管理MySQL和系统任务的命令行工具，主要包括：

验证主节点和复制数据之间的一致性

有效归档记录行

查找重复的索引

总结MySQL服务器

分析来自日志和tcpdump的查询

出现问题时收集重要的系统信息

在线修改表格结构

这里主要介绍在线修改表结构的功能。

在mysql数据库的操作和维护中，我们总是对数据表进行ddl更改，修改和添加字段或索引。对于mysql来说，ddl显然是一个让所有MySQL dba批判的函数，因为在MySQL中，当对表执行ddl时，表会被锁定。当表很小的时候，比如小于1w，对前端影响不大，遇到几千万的表，会影响前端应用对表的写操作。5.1之前，非常费时费力。5.1以后，随着Plugin innodb的出现，在线索引提高了很多，但也受到影响(时间缩短)；但是，5.6可以避免上述情况。目前，InnoDB引擎通过以下步骤进行DDL:

根据原表(origin _ table)的表结构和DDL语句，创建一个不可见的临时表(tmp_table)

向原始表添加写锁，以阻止所有更新操作(插入、删除、更新等)。)

执行插入到tmp _ table从原始_ table中选择*

重命名original_table和tmp_table，最后删除original_table

释放写锁定。

我们可以看到InnoDB执行DDL时，原始表只能读不能写。因此，perconal引入了一个名为pt-online-schema-change的工具，其特点是在修改过程中不阻塞读写。

注意：mysql版本视图：mysql -V V。

补充：Oracle从9i版开始提供在线表重定义功能。通过调用DBMS _ REDEFINITION包，可以在修改表结构的同时允许DML操作。

pt-在线-模式-变更的工作原理

如果有外键，根据alter-foreign-key-method参数的值，检查与外键相关的表，并进行相应的设置。如果不使用-alter-foreign-key-方法指定特定的值，该工具将不会执行

创建一个新的空表，其命名规则为_原始表名_新

根据alter语句更新新表的表结构；

Create trigger，用于记录复制数据后继续修改源数据表中数据的操作，并在复制数据后执行这些操作，以确保数据不会丢失。如果在表中定义了触发器，此工具将不起作用。

将数据从源数据表复制到新表。

修改外键相关子表，根据修改后的数据修改外键相关子表。

rename源数据表为旧表，新表重命名为源表名，通过RENAME TABLE同时处理两个表，实现原子操作。(RENAME TABLE dbteamdb . user TO dbteamdb。_user_old，dbteamdb。_user_new TO dbteamdb.user)

删除旧表并删除触发器。

pt的使用-在线-模式-更改

pt-在线-模式-更改[OPTIONS] DSN

选项参数描述：

-用户：

-u，连接的用户名

-密码：

-p，连接密码

-数据库：

-D，连接的数据库

港口

-P，用于连接数据库的端口

-主机：

-h，连接的主机地址

-插座：

-S，用于连接的套接字文件

-询问-通过

隐式输入密码以连接到MySQL

-字符集

指定修改后的字符集

-默认值-文件

-F，读取配置文件

-改变：

结构变更语句，不需要alter table关键字。您可以指定多个更改，用逗号分隔。以下情况需要注意：

不能用RENAME重命名表。

不能通过在添加列之前将其删除来重命名列，并且数据不会被复制到新列中。

如果添加的列不为空并且没有默认值，该工具将失败。也就是说，它不会为您设置默认值，并且必须显示名称。

要删除外键(删除外键约束名)，需要指定名称约束名，而不是原始约束名。

例如，约束` fk _ foo `外键(` foo _ id `)引用` bar ` (` foo _ id `)，您需要指定：-alter' drop外键_ fk _ foo '

-alter-外键-方法

如何将外键引用到新表。具有外键约束的表需要特殊处理，以确保它们可以应用于新表。重命名表时，外键关系将被带到重命名的表中。

这个工具有两种方法，可以自动查找子表，修改约束关系。

自动：选择重建约束和删除交换之一。

Rebuild_constraints:在添加外键约束之前，使用ALTER TABLE语句删除它们。如果子表很大，会造成长期阻塞。

Drop_swap:执行FOREIGN_KEY_CHECKS=0，禁止外键约束，删除原表，然后重命名新表。这种方法速度快，不会造成堵塞，但有风险：

1.短时间内删除原表，重命名新表，表不存在，程序会返回错误。

2.如果重命名表时出现错误，则无法回滚，因为原始表已被删除。

无：类似于‘drop _ swap’，但不删除原表，外键关系会随着重命名转移到旧表。

-[不]检查-改变

默认是，解析。用-dry-run-print运行，检查是否有问题(更改列，删除主键)。

-更大滞后

默认值为1s。在每个块拷贝完成后，将检查所有复制的从属块的延迟。如果延迟大于该值，请停止复制数据，直到所有从设备的延迟小于该值，并使用秒落后主设备。如果任何延迟超过该选项的值，工具将休眠-检查-间隔指定的时间，并再次检查。如果从停止，它将一直等待，直到同步从开始，并且延迟小于此值。如果指定了- check-slave-lag-lag，则该工具仅检查该服务器的延迟，而不是所有服务器的延迟。

-检查-从属-滞后

指定从库的DSN连接地址。如果从库超过- max-lag参数设置的值，操作将暂停。

递归方法

默认值是show processlist，发现从机的方法也可以是host，但是需要在从机上指定report_host，通过show slave hosts找到，可以指定none，避免检查从机。

方法用途

=============================

进程列表显示进程列表

hosts SHOW SLAVE HOSTS

dsn=表中的dsn

没有人找不到奴隶

指定none表示您不关心。

-检查间隔

默认值为1。-更大滞后检查睡眠时间。

-[不]检查计划

默认是。检查查询执行计划的安全性。

-[否]检查-复制-过滤器

默认是。如果该工具在服务器选项中检测到任何与复制相关的筛选器，例如指定binlog_ignore_db和replicate_do_db。如果找到这样的过滤器，该工具将报告错误并退出。因为如果更新的表存在于主机上而不存在于从机上，会导致复制失败。使用不检查-不检查-复制-过滤器选项禁用检查。

-[不]交换表

默认是。用新表替换原表，除非您禁止--不--删除旧表。

-[否]下拉触发器

默认情况下是，删除原始表上的触发器。-no-no-drop-触发器将被强制打开-no-drop-old-table，也就是说，如果触发器未被删除，原始表将被强制不被删除。

-新表名

由复制创建的新表的名称，默认为%T_new。

-[否]删除新表

默认是。如果复制组织表失败，请删除新表。

-[不]旧桌子

默认是。重命名复制的数据后，删除原始表。如有错误，保留原表。

-更大负载

默认值为Threads_running=25。复制每个块后，它将检查显示全局状态的内容，并检查索引是否超过指定的阈值。如果超过，先暂停。在这里，您可以指定用逗号分隔的多个条件。每个条件的格式为：状态指示器=MAX_VALUE或状态指示器：MAX_VALUE。如果未指定更大值，工具将只将其设置为当前值的120%。

临界负荷

默认值为Threads_running=50。用法基本类似于- max-load。如果未指定更大值，工具将仅将其用作当前值的200%。如果超过指定值，工具将直接退出，而不是暂停。

-默认-引擎

默认情况下，新表与原始表是同一个存储引擎，因此如果原始表使用InnoDB，新表将使用InnoDB。在某些涉及复制的情况下，主存储引擎和从存储引擎可能不同。默认情况下，使用此选项将使用默认存储引擎。

-设置-vars

设置MySQL变量，用逗号分隔多个变量。该工具默认设置为：wait _ time out=10000 innodb _ lock _ wait _ time out=1 lock _ wait _ time out=60

-块大小限制

当要复制的块比设定的块大得多时，它将不会被复制。默认值为4.0。对于没有主键或索引的表，块大小是不确定的。

-大块时间

在块时间执行期间，动态调整块大小以适应服务器性能的变化。将此参数设置为0或指定块大小会禁止动态调整。

-块大小

指定块大小，默认为1000行，可以添加k，M，G m，G后缀。块大小应该尽可能与块时间相匹配。如果明确指定了此选项，每个块将指定行数。

-[不]检查计划

默认是。为了安全起见，请检查查询的执行计划。默认情况下，该工具将在执行查询之前进行解释，以便一次性获取少量数据。如果是一个不好的解释，那么一次就会得到大量的数据。该工具将多次执行EXPALIN。如果解释结果不同，那么这个查询将被认为是不安全的。

-统计

打印出内部事件的数量，就可以看到插入复制数据的数量。

-试运行

创建和修改新表，但不创建触发器、复制数据和替换原始表。它并不真正执行，所以您可以看到生成的执行语句，并知道它的执行步骤和细节。-试运行和- execute必须指定一个，它们是互斥的。它最适合印刷。

-执行

如果您确定要修改该表，请指定此参数。真正的执行力。-试运行和- execute必须指定一个，它们是互斥的。

-打印

将SQL语句打印到标准输出。指定此选项允许您查看工具执行的语句，这最适合于模拟运行。

-进步

复制数据时打印进度报告，由两部分组成：部分是百分比，第二部分是时间。

安静

-q，不要输出信息标准。

例1

1、建立测试表：

CREATE TABLE `online_table `(

` id` int(11)主键，

` name` varchar(10) DEFAULT NULL，

` age` int(11) DEFAULT NULL

)engine=innodb默认字符集utf8

2.--实际上不执行试运行：

一个

pt-online-schema-change-user=root-password=123456-host=192 . 168 . 200 . 25-alter ' ADD COLUMN content text ' D=test，t=online_table - print -试运行

3.--执行真正的执行：

一个

$ pt-online-schema-change-user=root-password=123456-host=192 . 168 . 200 . 25-alter ' ADD COLUMN content text ' D=test，t=online_table - print - execute

查看表格结构：

desc在线_表格；

- - - - - -

|字段|类型|空|键|默认|额外

- - - - - -

| id | int(11)| NO | PRI | NULL | auto _ increment

| name | varchar(10) | YES | | NULL | |

|年龄| int(11) | YES | | NULL | |

|内容|文本|是| |空| |

- - - - - -

4行一组(0.00秒)

注意：以上执行可能不正常：

设置innodb_lock_wait_timeout : DBD :3360 my SQL : db do failed ed :时出错变量' innodb _ lock _ wait _ time out '是只读变量[对于语句“SET SESSION innodb _ lock _ wait _ time out=1”]。innodb_lock_wait_timeout的当前值为50。如果变量是只读的(不是动态的)，请指定set-vars innodb _ lock _ wait _ time out=50以避免此警告，否则请手动设置变量并重新启动MySQL。

根据提示(innodb_lock_wait_timeout为静态参数)，add-set-varsinodb _ lock _ wait _ time out=50:

一个

pt-online-schema-change-user=root-passwor=root-host=localhost-alter ' ADD COLUMN content text ' D=test，t=user-set-vars innodb _ lock _ wait _ time out=50-print-execute

示例2(主从)

desc tmp _ test；

- - - - - -

|字段|类型|空|键|默认|额外

- - - - - -

| id | int(11)| NO | PRI | NULL | auto _ increment

| name | varchar(10) | YES | | NULL | |

- - - - - -

1、添加字段：

一

2

$ pt-online-schema-change-user=zjy-host=192。168 .200 .111-alter ' ADD COLUMN content text ' D=CRM _ production，t=tmp _ test-ask-pass-print-execute

输入关系型数据库密码：

报错，因为该工具在检测到服务器选项中有任何复制相关的筛选会退出，需要指定：无检查复制过滤器

这些主机上设置了复制过滤器：

数据库2

replicate_do_db=crm_production

请阅读检查-复制-过滤器文档，了解如何解决此问题at/usr/local/bin/pt-联机-模式-更改第8015行第2行。

2、加上参数：无检查复制过滤器

一

$ pt-online-schema-change-user=zjy-host=192。168 .200 .111-alter ' ADD COLUMN content text ' D=CRM _ production，t=tmp _ test-ask-pass-no-check-replication-filters-print-execute

3、除了添加列，也可以修改列，删除列，对于更改列则需要指定：无检查更改

实例三（外键)

1、测试表：

一

2

3

创建表tt(id int not null auto_increment，名称varchar(10)，主键(id))引擎=innodb默认字符集utf8

创建表xx(id int不为空自动增量，tt标识不为null，名称varchar(10)，主键(id))发动机=innodb默认字符集utf8

更改表xx添加外键fk_xx_tt_id(tt_id)引用TT(身份证)；

2、添加字段：

一

$ pt-online-schema-change-user=root-password=123456-host=192。168 .200 .25-alter ' ADD COLUMN content text ' D=AAA，t=TT-无-检查-复制-筛选-打印-执行

执行错误退出，提示需要指定：更改-外键-方法参数来操作有外键的表。要是没有外键而加了参数的话会出现：

没有外键引用AAA。xx；忽略更改-外键-方法。

3、使用更改-外键-方法

一

$ pt-online-schema-change-user=root-password=123456-host=192。168 .200 .25-alter ' ADD COLUMN content text ' D=AAA，t=TT-不检查-复制-筛选器-alter-外键-方法-自动-打印-执行

注：对可靠性要求不高可以用汽车模式更新，要是可靠性要求高则需要用重建约束(_ r)模式。即：

一

-alter-foreign-key-method rebuild _ constraints

综合实例

1、测试表：

CREATE TABLE `tmp_test `(

` id` int(11)非空自动增量，

` name` varchar(10) DEFAULT NULL，

“年龄”int(11) DEFAULT NULL，

主键(` id `)

)发动机=Innodb DEFAULT CHARSET=ut F8

2、增加字段：

一

pt-online-schema-change-user=root-password=123456-host=192。168 .200 .25-alter ' ADD COLUMN content text ' D=AAA，t=tmp _ test-no-check-replication-filters-alter-外键-method=auto-recursion-method=none-print-execute

3、删除字段：

一

pt-online-schema-change-user=root-password=123456-host=192。168 .200 .25-alter ' DROP COLUMN content ' D=AAA，t=tmp _ test-no-check-replication-filters-alter-foreign-key-method=auto-recursion-method=none-quiet-execute

4、修改字段：

一

pt-online-schema-change-user=root-password=123456-host=192。168 .200 .25-alter ' MODIFY COLUMN age TINYNT NOT NULL DEFAULT 0 ' D=AAA，t=tmp _ test-no-check-replication-filters-alter-外键-method=自动递归-method=非安静-执行

5、改名：

一

pt-online-schema-CHANGE-user=root-password=123456-host=192。168 .200 .25-alter ' CHANGE COLUMN age address varchar(30)' D=AAA，t=tmp _ test-no-check-alter-no-check-replication-filters-alter-外键-method=自动递归-method=none-quiet-execut

6、索引：

一

pt-online-schema-change-user=root-password=123456-host=192 . 168 . 200 . 25-alter ' ADD INDEX idx _ address(address)' D=AAA，t=tmp _ test-no-check-alter-no-check-replication-filters-alter-foreign-key-method=auto-recursion-method=none-print-execute

7.删除索引：

一个

pt-online-schema-change-user=root-password=123456-host=192 . 168 . 200 . 25-alter ' DROP INDEX idx _ address ' D=AAA，t=tmp _ test-no-check-alter-no-check-replication-filters-alter-外键-method=auto-recursion-method=none-print-execute

8.其他：

1)不丢弃旧表：

在上述测试中，原始表被删除。如果原始表未被删除，no-drop-old-table将保留原始表(_test_binlog_old)。

一个

pt-online-schema-change-user=root-password=123456-host=192 . 168 . 200 . 25-alter ' ADD COLUMN a text ' D=AAA，t=test _ binlog-no-check-replication-filters-no-drop-old-table-print-execute

2)更大负载：

如果您向在线环境中添加了一个字段，但不想影响服务，可以使用参数：-更大负载来执行该工具。默认值为Threads_running=25，即当前运行的线程太多，数据复制会暂停，如果小于该值，数据会继续复制到新表中：

一个

pt-online-schema-change-user=root-password=123456-host=192 . 168 . 200 . 25-alter ' add INDEX idx _ address(address)' D=AAA，t=tmp _ test-no-check-alter-no-check-replication-filters-alter-外键-method=auto-recursion-method=none-max-load=Threads _ running=2-statistics-print-execute

暂停时，标准输出将具有：

暂停，因为线程运行=2 .当正在运行的线程数小于给定值时，数据将继续被复制，直到完成。

9.总结：

当通信量较大时，修改操作将在执行最终重命名操作之前等待没有数据修改。所以在修改表结构的时候，尽量选择业务比较空闲的时候，至少是修改表上数据的操作比较低的时候。

对外键表进行操作时，需要根据表的数据量和可靠性选择四种类型的外键操作。出于可靠性考虑，尽量使用rebuild_constraints类型，如果没有可靠性要求，可以使用auto类型。

由于可能存在一定的风险，建议在操作前备份数据表，这样可以使操作更加安全可靠。

使用该工具的前提是，被处理的表需要一个主键或索引。处理带外键的表时，需要添加参数-alter-foreign-key-method，可以根据情况设置值。如果是主从环境，不关心从机延迟，则需要添加参数recursion-method=none。当需要尽可能少地影响服务时，需要添加max-load参数。