percona之数据库开发工具介绍

最新推荐文章于 2022-09-06 20:45:18 发布

cpseh68048

最新推荐文章于 2022-09-06 20:45:18 发布

阅读量210

点赞数

1. pt-duplicate-key-checker

功能介绍：

检查表中重复或多余的索引和外键，将重复的索引和外键都列出来，并可以生成了删除重复索引的语句。

使用说明

shell> pt-duplicate-key-checker [OPTIONS] [DSN]
详情可参考
shell> pt-duplicate-key-checker --help

--all-structs         比较不同索引结构(BTREE, HASH, etc)
--[no]clustered       主键列上添加辅助索引是否重复，默认重复
--databases=h     -d  指定数据库
--engines=h       -e  指定存储引擎
--ignore-order        忽略索引字段顺序，KEY(a,b) 与 KEY(b,a)重复
--key-types=s         检查类型f:外键；k：索引；fk：两者都检查(默认)
--[no]sql             重复索引是否生成删除语句，默认删除
--tables=h        -t  指定检查的表，用逗号间隔

使用示例

shell> pt-duplicate-key-checker --host localhost --user=root --ask-pass --database=test
Enter password: 
# ########################################################################
# test.t                                                                  
# ########################################################################

# idx_id is a duplicate of PRIMARY
# Key definitions:
#   KEY `idx_id` (`id`)
#   PRIMARY KEY (`id`),
# Column types:
#      `id` int(11) not null default '0'
# To remove this duplicate index, execute:
ALTER TABLE `test`.`t` DROP INDEX `idx_id`;

# Uniqueness of uq_id ignored because PRIMARY is a duplicate constraint
# uq_id is a duplicate of PRIMARY
# Key definitions:
#   UNIQUE KEY `uq_id` (`id`),
#   PRIMARY KEY (`id`),
# Column types:
#      `id` int(11) not null default '0'
# To remove this duplicate index, execute:
ALTER TABLE `test`.`t` DROP INDEX `uq_id`;

# ########################################################################
# Summary of indexes                                                      
# ########################################################################

# Size Duplicate Indexes   8
# Total Duplicate Indexes  2
# Total Indexes            8

2. pt-show-grants

功能介绍：

规范化并打印mysql权限，让复制、对比用户权限以及版本控制更高效

使用说明

shell> pt-show-grants [OPTIONS] [DSN]
详情可参考
shell> pt-show-grants --help


--database=s      -D  指定连接数据库
--drop                在用户列表添加DROP USER语句
--flush               在列表后添加FLUSH PRIVILEGES语句
--ignore=a            忽略用户列表，逗号间隔
--only=a              仅显示指定用户的权限
--revoke              在GRANT语句之后添加REVOKE语句
--separate            单独显示每个权限的GRANT 或 REVOKE 语句

使用示例

shell> pt-show-grants --drop --flush --revoke --only=svoid --host=localhost --user=root --ask-pass
Enter password: 
-- Grants dumped by pt-show-grants
-- Dumped from server Localhost via UNIX socket, MySQL 5.6.19-log at 2015-03-27 16:24:46
-- Revoke statements for 'svoid'@'%'
REVOKE PROCESS, REPLICATION SLAVE, SELECT, SUPER ON *.* FROM 'svoid'@'%';
DROP USER 'svoid'@'%';
DELETE FROM `mysql`.`user` WHERE `User`='svoid' AND `Host`='%';
-- Grants for 'svoid'@'%'
GRANT PROCESS, REPLICATION SLAVE, SELECT, SUPER ON *.* TO 'svoid'@'%' IDENTIFIED BY PASSWORD '*050376F3855A67F5E2C6514FD3130B31006C1276';
-- Revoke statements for 'svoid'@'localhost'
REVOKE PROCESS, REPLICATION SLAVE, SELECT, SUPER ON *.* FROM 'svoid'@'localhost';
DROP USER 'svoid'@'localhost';
DELETE FROM `mysql`.`user` WHERE `User`='svoid' AND `Host`='localhost';
-- Grants for 'svoid'@'localhost'
GRANT PROCESS, REPLICATION SLAVE, SELECT, SUPER ON *.* TO 'svoid'@'localhost' IDENTIFIED BY PASSWORD '*050376F3855A67F5E2C6514FD3130B31006C1276';
FLUSH PRIVILEGES;

3. pt-online-schema-change

功能介绍：

更改表结构操作的时候不用锁定表

工作原理：

创建一个和需要要执行Alter操作的表一样的空表结构，执行表结构修改，然后从原表中copy原始数据到表结构修改后的表，当数据copy完成以后就会将原表移走，用新表代替原表，默认动作是将原表drop掉。在copy数据的过程中，任何在原表的更新操作都会更新到新表，因为这个工具在会在原表上创建触发器，触发器会将在原表上更新的内容更新到新表。如果表中已经定义了触发器这个工具就不能工作了。

使用说明

shell> pt-online-schema-change [OPTIONS] DSN
详情可参考
shell> pt-online-schema-change --help


--alter=s                        修改表结构不指定ALTER TABLE关键字
--check-slave-lag=s              中断数据复制直到复制延迟比--max-lag指定值小
--chunk-index=s                  指定分块表的索引
--chunk-size=z                   每个块复制指定的行数，默认为1000
--chunk-time=f                   动态调整块大小，每次数据复制消耗指定值，默认为0.5
--critical-load=A                复制每块之后检查SHOW GLOBAL STATUS状态，如果状态变量比阈值高将退出（默认Threads_running=50）
--[no]drop-new-table             如果拷贝原表失败是否删除新表，默认删除
--[no]drop-old-table             重命名后是否删除原表，默认删除
--[no]drop-triggers              删除旧表的触发器
--dry-run                        创建修改新表，但不创建触发器、不拷贝数据、也不替换原表
--execute                        确认执行更新操作，注意：如果不加这个参数，这个工具会在执行一些检查后退出。
--max-lag=m                      中断数据复制直到所有备库延迟小于此值，默认为1s(s,m,h,d 默认为s)
--max-load=A                     复制每块之后检查SHOW GLOBAL STATUS状态，如果状态变量比阈值高将中断（默认Threads_running=25）

使用示例

shell> pt-online-schema-change --user=root --ask-pass --host=localhost --alter="drop index idx_username" D=db_test,t=users --dry-run
Enter MySQL password: 
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
Starting a dry run.  `db_test`.`users` will not be altered.  Specify --execute instead of --dry-run to alter the table.
Creating new table...
Created new table db_test._users_new OK.
Altering new table...
Altered `db_test`.`_users_new` OK.
Not creating triggers because this is a dry run.
Not copying rows because this is a dry run.
Not swapping tables because this is a dry run.
Not dropping old table because this is a dry run.
Not dropping triggers because this is a dry run.
2015-03-27T17:59:52 Dropping new table...
2015-03-27T17:59:52 Dropped new table OK.
Dry run complete.  `db_test`.`users` was not altered.

shell> pt-online-schema-change --user=root --ask-pass --host=localhost --alter="drop index idx_username" D=db_test,t=users --execute
 pt-online-schema-change --user=root --ask-pass --host=localhost --alter="drop index idx_username" D=db_test,t=users --execute
Enter MySQL password: 
Found 1 slaves:
  rac2
Will check slave lag on:
  rac2
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 `db_test`.`users`...
Creating new table...
Created new table db_test._users_new OK.
Waiting forever for new table `db_test`.`_users_new` to replicate to rac2...
Waiting for rac2:   0% 00:00 remain
Waiting for rac2:   0% 00:00 remain
Waiting for rac2:   0% 00:00 remain
Waiting for rac2:   0% 00:00 remain
Waiting for rac2:   0% 00:00 remain
Waiting for rac2:   0% 00:00 remain    #发现备库SQL 线程挂掉，主库会一直等待，重启备库SQL线程
Altering new table...
Altered `db_test`.`_users_new` OK.
2015-03-27T18:08:51 Creating triggers...
2015-03-27T18:08:51 Created triggers OK.
2015-03-27T18:08:51 Copying approximately 99 rows...
2015-03-27T18:08:51 Copied rows OK.
2015-03-27T18:08:51 Swapping tables...
2015-03-27T18:08:51 Swapped original and new tables OK.
2015-03-27T18:08:51 Dropping old table...
2015-03-27T18:08:51 Dropped old table `db_test`.`_users_old` OK.
2015-03-27T18:08:51 Dropping triggers...
2015-03-27T18:08:51 Dropped triggers OK.
Successfully altered `db_test`.`users`.

删除主键操作
shell> pt-online-schema-change --user=root --ask-pass --host=localhost --alter="drop primary key" D=db_test,t=users --execute
Enter MySQL password: 
Found 1 slaves:
  rac2
Will check slave lag on:
  rac2
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 `db_test`.`users`...
--alter contains 'DROP PRIMARY KEY'.  Dropping and altering the primary key can be dangerous, especially if the original table does not have other unique indexes.
The tool should handle this correctly, but you should test it first and carefully examine the triggers which rely on the PRIMARY KEY or a unique index.  Specify --no-check-alter to disable this check and perform the --alter.
`db_test`.`users` was not altered.
--check-alter failed.

删除字段
shell> pt-online-schema-change --user=root --ask-pass --host=localhost --alter="drop column password" D=db_test,t=users --execute
Enter MySQL password: 
Found 1 slaves:
  rac2
Will check slave lag on:
  rac2
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 `db_test`.`users`...
Creating new table...
Created new table db_test._users_new OK.
Waiting forever for new table `db_test`.`_users_new` to replicate to rac2...
Waiting for rac2:   0% 00:00 remain
Altering new table...
Altered `db_test`.`_users_new` OK.
2015-03-27T18:19:17 Creating triggers...
2015-03-27T18:19:17 Created triggers OK.
2015-03-27T18:19:17 Copying approximately 99660 rows...
Replica lag is 31 seconds on rac2.  Waiting.
Replica lag is 60 seconds on rac2.  Waiting.
Copying `db_test`.`users`:  13% 06:40 remain
Replica lag is 32 seconds on rac2.  Waiting.
2015-03-27T18:21:20 Copied rows OK.
2015-03-27T18:21:20 Swapping tables...
2015-03-27T18:21:20 Swapped original and new tables OK.
2015-03-27T18:21:20 Dropping old table...
2015-03-27T18:21:20 Dropped old table `db_test`.`_users_old` OK.
2015-03-27T18:21:20 Dropping triggers...
2015-03-27T18:21:20 Dropped triggers OK.
Successfully altered `db_test`.`users`.

mysql> show tables;
+-------------------+
| Tables_in_db_test |
+-------------------+
| _users_new        |    #创建临时表
| t                 |
| tt                |
| user              |
| users             |
+-------------------+
5 rows in set (0.00 sec)

同时插入数据未锁表
mysql> insert into users values(100000,'111','test');
Query OK, 1 row affected (0.01 sec)


添加字段
shell> pt-online-schema-change --user=root --ask-pass --host=localhost --alter="add column password char(41)" D=db_test,t=users --execute

修改存储引擎
shell> pt-online-schema-change --user=root --ask-pass --host=localhost --alter="ENGINE=InnoDB" D=db_test,t=users --execute