perconatoolkit的安装与使用

database

一、percona-toolkit的安装请参考:https://blog.csdn.net/yuanyk1222/article/details/100066788

二、下面来说说此工具的一些用法:

pt-online-schema-change

功能可以在线整理表结构,收集碎片,给大表添加字段和索引。避免出现锁表导致阻塞读写的操作。不过针对 MySQL 5.7 版本,就可以不需要使用这个命令,直接在线 online DDL 就可以了。

Mysql数据库中有如下表:

mysql> desc t;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id | int(11) | NO | PRI | NULL | |

| k | int(11) | NO | MUL | NULL | |

| b | varchar(10) | YES | | NULL | |

| name | varchar(16) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

如果我们想在线增加一个字段,可以执行如下命令:

[root@i-vvwtw5ne pt-kill]# pt-online-schema-change --user=root --password=Falsesoul1207 --host=192.192.18.34 --alter="add column c int after b" D=test,t=t --execute

Found 1 slaves:

i-s70m0mun

Will check slave lag on:

i-s70m0mun

*******************************************************************

Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client

is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER

possibly with SSL_ca_file|SSL_ca_path for verification.

If you really don"t want to verify the certificate and keep the

connection open to Man-In-The-Middle attacks please set

SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.

*******************************************************************

at /usr/bin/pt-online-schema-change line 6576.

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 `test`.`t`...

Creating new table...

Created new table test._t_new OK.

Altering new table...

Altered `test`.`_t_new` OK.

2019-12-08T00:17:36 Creating triggers...

2019-12-08T00:17:36 Created triggers OK.

2019-12-08T00:17:36 Copying approximately 1 rows...

2019-12-08T00:17:36 Copied rows OK.

2019-12-08T00:17:36 Swapping tables...

2019-12-08T00:17:36 Swapped original and new tables OK.

2019-12-08T00:17:36 Dropping old table...

2019-12-08T00:17:36 Dropped old table `test`.`_t_old` OK.

2019-12-08T00:17:36 Dropping triggers...

2019-12-08T00:17:36 Dropped triggers OK.

Successfully altered `test`.`t`.

再查看原来的表结构,发再增加字段成功,在b字段后面成功增加了一个字段c:

mysql> desc t;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id | int(11) | NO | PRI | NULL | |

| k | int(11) | NO | MUL | NULL | |

| b | varchar(10) | YES | | NULL | |

| c | int(11) | YES | | NULL | |

| name | varchar(16) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

重点说下pt-kill用法:

show  PROCESSLIST结果如下:

 print执行时间超过10s的查询:

[root@i-vvwtw5ne ~]# pt-kill -h192.192.18.34 -P3306 -uroot -pFalsesoul1207  --busy-time=10 --print 

# 2019-12-08T19:53:06 KILL 67432267 (Query 33 sec) do sleep(60000)

kill执行时间超过11s的查询:

[root@i-vvwtw5ne ~]# pt-kill -h192.192.18.34 -P3306 -uroot -pFalsesoul1207  --busy-time=10 --kill

每隔10s执行kill所有的“Command”条件为“Query”的查询:

[root@i-vvwtw5ne ~]# pt-kill -h192.192.18.34 -P3306 -uroot -pFalsesoul1207  --busy-time=10 --match-command=Query --victims=all --interval=10 --kill

print所以“State”条件为“User sleep”的查询:

[root@i-vvwtw5ne ~]# pt-kill -h192.192.18.34 -P3306 -uroot -pFalsesoul1207  --busy-time=10 --match-state="User sleep" --victims=all --interval=10 --print

# 2019-12-08T19:59:34 KILL 67432969 (Query 12 sec) do sleep(6000000)

每10s打印一次数据库为test state=User sleep的sql语句

[root@i-vvwtw5ne ~]# pt-kill -h192.192.18.34 -P3306 -uroot -pFalsesoul1207 --match-db="test"  --busy-time=10 --match-state="User sleep" --victims=all --interval=10 --print

 

以上是 perconatoolkit的安装与使用 的全部内容, 来源链接: utcz.com/z/531741.html

回到顶部