percona之資料庫開發工具介紹

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

整理自網路