70.MySQL PT工具包之-pt-online-schema-change

已于 2023-02-27 10:49:22 修改
阅读量439 收藏 2
点赞数
分类专栏： MySQL数据库运维 文章标签： mysql 数据库
于 2023-02-27 10:21:04 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43346403/article/details/129236654
版权

1.工具介绍
正常情况下在线 ALTER 一个表增加一个字段、一个索引的话MySQL 会锁表 
使用pt-online-schema-change 在线更新表,不停止业务使用的在线更新表结构：在线 DDL 操作改变表结构并且不阻塞.

2.给表添加表结构。
(1)添加字段前。
mysql> desc  t2; 
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  | MUL | NULL    |       |
| name  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

(2)添加字段。
[root@mysql1 test]# pt-online-schema-change --user=root --host=192.168.1.51 --port=3306 --password=rootroot --execute --alter "ADD COLUMN member VARCHAR(20)" D=test,t=t2 --no-check-replication-filters
Found 1 slaves:
mysql2 -> 192.168.1.58:3306
Will check slave lag on:
mysql2 -> 192.168.1.58:3306
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 `test`.`t2`...
Creating new table...
Created new table test._t2_new OK.
Altering new table...
Altered `test`.`_t2_new` OK.
2023-02-28T10:08:52 Creating triggers...
2023-02-28T10:08:52 Created triggers OK.
2023-02-28T10:08:52 Copying approximately 3 rows...
2023-02-28T10:08:52 Copied rows OK.
2023-02-28T10:08:52 Analyzing new table...
2023-02-28T10:08:52 Swapping tables...
2023-02-28T10:08:52 Swapped original and new tables OK.
2023-02-28T10:08:52 Dropping old table...
2023-02-28T10:08:52 Dropped old table `test`.`_t2_old` OK.
2023-02-28T10:08:52 Dropping triggers...
2023-02-28T10:08:52 Dropped triggers OK.
Successfully altered `test`.`t2`.
mysql> DESC t2;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id     | int(11)      | YES  | UNI | NULL    |       |
| name   | varchar(100) | YES  |     | NULL    |       |
| member | varchar(20)  | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
发现已经添加成功。


(3)更改列 
pt-online-schema-change --user=root --host=192.168.1.51 --port=3306 --password=rootroot --execute --alter "MODIFY COLUMN member VARCHAR(30)" D=test,t=t2 --no-check-replication-filters
[root@mysql1 test]# pt-online-schema-change --user=root --host=192.168.1.51 --port=3306 --password=rootroot --execute --alter "MODIFY COLUMN member VARCHAR(30)" D=test,t=t2 --no-check-replication-filters
Found 1 slaves:
mysql2 -> 192.168.1.58:3306
Will check slave lag on:
mysql2 -> 192.168.1.58:3306
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 `test`.`t2`...
Creating new table...
Created new table test._t2_new OK.
Altering new table...
Altered `test`.`_t2_new` OK.
2023-02-28T10:13:41 Creating triggers...
2023-02-28T10:13:41 Created triggers OK.
2023-02-28T10:13:41 Copying approximately 3 rows...
2023-02-28T10:13:41 Copied rows OK.
2023-02-28T10:13:41 Analyzing new table...
2023-02-28T10:13:41 Swapping tables...
2023-02-28T10:13:41 Swapped original and new tables OK.
2023-02-28T10:13:41 Dropping old table...
2023-02-28T10:13:41 Dropped old table `test`.`_t2_old` OK.
2023-02-28T10:13:41 Dropping triggers...
2023-02-28T10:13:41 Dropped triggers OK.
Successfully altered `test`.`t2`.

mysql> DESC t2;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id     | int(11)      | YES  | UNI | NULL    |       |
| name   | varchar(100) | YES  |     | NULL    |       |
| member | varchar(30)  | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

发现已经修改成功。
(3)删除列 
[root@mysql1 test]# pt-online-schema-change --user=root --host=192.168.1.51 --port=3306 --password=rootroot --execute --alter "DROP COLUMN member " D=test,t=t2 --no-check-replication-filters
Found 1 slaves:
mysql2 -> 192.168.1.58:3306
Will check slave lag on:
mysql2 -> 192.168.1.58:3306
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 `test`.`t2`...
Creating new table...
Created new table test._t2_new OK.
Altering new table...
Altered `test`.`_t2_new` OK.
2023-02-28T10:15:58 Creating triggers...
2023-02-28T10:15:58 Created triggers OK.
2023-02-28T10:15:58 Copying approximately 3 rows...
2023-02-28T10:15:58 Copied rows OK.
2023-02-28T10:15:58 Analyzing new table...
2023-02-28T10:15:58 Swapping tables...
2023-02-28T10:15:58 Swapped original and new tables OK.
2023-02-28T10:15:58 Dropping old table...
2023-02-28T10:15:58 Dropped old table `test`.`_t2_old` OK.
2023-02-28T10:15:58 Dropping triggers...
2023-02-28T10:15:58 Dropped triggers OK.
Successfully altered `test`.`t2`.
mysql> DESC t2;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  | UNI | NULL    |       |
| name  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
删除完成。

3.pt-online-schema-change 其他用法。
(1)增加字段 content 
CREATE TABLE `test_pt` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

pt-online-schema-change --user=root --password=rootroot --host=192.168.1.51 \
--alter "ADD COLUMN content text" D=test,t=test_pt --no-check-replication-filters \
--alter-foreign-keys-method=auto --recursion-method=none --print --execute

No slaves found.  See --recursion-method if host mysql1 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
No foreign keys reference `test`.`test_pt`; ignoring --alter-foreign-keys-method.
Altering `test`.`test_pt`...
Creating new table...
CREATE TABLE `test`.`_test_pt_new` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Created new table test._test_pt_new OK.
Altering new table...
ALTER TABLE `test`.`_test_pt_new` ADD COLUMN content text
Altered `test`.`_test_pt_new` OK.
2023-02-28T10:26:54 Creating triggers...
2023-02-28T10:26:54 Created triggers OK.
2023-02-28T10:26:54 Copying approximately 1 rows...
INSERT LOW_PRIORITY IGNORE INTO `test`.`_test_pt_new` (`id`, `name`, `age`) SELECT `id`, `name`, `age` FROM `test`.`test_pt` LOCK IN SHARE MODE /*pt-online-schema-change 81063 copy table*/
2023-02-28T10:26:54 Copied rows OK.
2023-02-28T10:26:54 Analyzing new table...
2023-02-28T10:26:54 Swapping tables...
RENAME TABLE `test`.`test_pt` TO `test`.`_test_pt_old`, `test`.`_test_pt_new` TO `test`.`test_pt`
2023-02-28T10:26:54 Swapped original and new tables OK.
2023-02-28T10:26:54 Dropping old table...
DROP TABLE IF EXISTS `test`.`_test_pt_old`
2023-02-28T10:26:54 Dropped old table `test`.`_test_pt_old` OK.
2023-02-28T10:26:54 Dropping triggers...
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_test_pt_del`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_test_pt_upd`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_test_pt_ins`
2023-02-28T10:26:54 Dropped triggers OK.
Successfully altered `test`.`test_pt`.
mysql> desc test_pt;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(10) | YES  |     | NULL    |                |
| age     | int(11)     | YES  |     | NULL    |                |
| content | text        | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

(2)删除字段 
pt-online-schema-change --user=root --password=rootroot --host=192.168.1.51 \
--alter "DROP COLUMN content " D=test,t=test_pt --no-check-replication-filters \
--alter-foreign-keys-method=auto --recursion-method=none --quiet --execute

No foreign keys reference `test`.`test_pt`; ignoring --alter-foreign-keys-method
mysql> desc test_pt;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
删除成功。

(3)修改字段 
pt-online-schema-change --user=root --password=rootroot --host=192.168.1.51 \
--alter "MODIFY COLUMN age TINYINT NOT NULL DEFAULT 0" D=test,t=test_pt \
--no-check-replication-filters --alter-foreign-keys-method=auto \
--recursion-method=none --quiet --execute

No foreign keys reference `test`.`test_pt`; ignoring --alter-foreign-keys-method

修改前 
mysql> desc test_pt;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

修改后：
mysql>  desc test_pt;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | NULL    |                |
| age   | tinyint(4)  | NO   |     | 0       |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

(4)字段改名称：将age改成address;
pt-online-schema-change --user=root --password=rootroot --host=192.168.1.51 \
--alter "CHANGE COLUMN age address varchar(30)" D=test,t=test_pt --no-check-alter \
--no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none \
--quiet --execute 

No foreign keys reference `test`.`test_pt`; ignoring --alter-foreign-keys-method

修改前：

mysql>  desc test_pt;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | NULL    |                |
| age   | tinyint(4)  | NO   |     | 0       |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
修改后： 
mysql> desc test_pt;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(10) | YES  |     | NULL    |                |
| address | varchar(30) | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

(5)增加索引,在address列上添加索引
pt-online-schema-change --user=root --password=rootroot --host=192.168.1.51 \
--alter "ADD INDEX idx_address(address)" D=test,t=test_pt --no-check-alter \
--no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none \
--print --execute
No slaves found.  See --recursion-method if host mysql1 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
No foreign keys reference `test`.`test_pt`; ignoring --alter-foreign-keys-method.
Altering `test`.`test_pt`...
Creating new table...
CREATE TABLE `test`.`_test_pt_new` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `address` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Created new table test._test_pt_new OK.
Altering new table...
ALTER TABLE `test`.`_test_pt_new` ADD INDEX idx_address(address)
Altered `test`.`_test_pt_new` OK.
2023-02-28T10:39:41 Creating triggers...
2023-02-28T10:39:41 Created triggers OK.
2023-02-28T10:39:41 Copying approximately 1 rows...
INSERT LOW_PRIORITY IGNORE INTO `test`.`_test_pt_new` (`id`, `name`, `address`) SELECT `id`, `name`, `address` FROM `test`.`test_pt` LOCK IN SHARE MODE /*pt-online-schema-change 81730 copy table*/
2023-02-28T10:39:41 Copied rows OK.
2023-02-28T10:39:41 Analyzing new table...
2023-02-28T10:39:41 Swapping tables...
RENAME TABLE `test`.`test_pt` TO `test`.`_test_pt_old`, `test`.`_test_pt_new` TO `test`.`test_pt`
2023-02-28T10:39:41 Swapped original and new tables OK.
2023-02-28T10:39:41 Dropping old table...
DROP TABLE IF EXISTS `test`.`_test_pt_old`
2023-02-28T10:39:41 Dropped old table `test`.`_test_pt_old` OK.
2023-02-28T10:39:41 Dropping triggers...
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_test_pt_del`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_test_pt_upd`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_test_pt_ins`
2023-02-28T10:39:41 Dropped triggers OK.
Successfully altered `test`.`test_pt`.

修改前:
mysql> show indexes from  test_pt;
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test_pt |          0 | PRIMARY     |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

3 rows in set (0.00 sec)
修改后： 
mysql> show indexes from  test_pt;
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test_pt |          0 | PRIMARY     |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| test_pt |          1 | idx_address |            1 | address     | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

(6)删除索引：
pt-online-schema-change --user=root --password=rootroot --host=192.168.1.51 \
--alter "DROP INDEX idx_address" D=test,t=test_pt --no-check-alter \
--no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none \
--print --execute
No slaves found.  See --recursion-method if host mysql1 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
No foreign keys reference `test`.`test_pt`; ignoring --alter-foreign-keys-method.
Altering `test`.`test_pt`...
Creating new table...
CREATE TABLE `test`.`_test_pt_new` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `address` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_address` (`address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Created new table test._test_pt_new OK.
Altering new table...
ALTER TABLE `test`.`_test_pt_new` DROP INDEX idx_address
Altered `test`.`_test_pt_new` OK.
2023-02-28T10:42:43 Creating triggers...
2023-02-28T10:42:43 Created triggers OK.
2023-02-28T10:42:43 Copying approximately 1 rows...
INSERT LOW_PRIORITY IGNORE INTO `test`.`_test_pt_new` (`id`, `name`, `address`) SELECT `id`, `name`, `address` FROM `test`.`test_pt` LOCK IN SHARE MODE /*pt-online-schema-change 81884 copy table*/
2023-02-28T10:42:43 Copied rows OK.
2023-02-28T10:42:43 Analyzing new table...
2023-02-28T10:42:43 Swapping tables...
RENAME TABLE `test`.`test_pt` TO `test`.`_test_pt_old`, `test`.`_test_pt_new` TO `test`.`test_pt`
2023-02-28T10:42:43 Swapped original and new tables OK.
2023-02-28T10:42:43 Dropping old table...
DROP TABLE IF EXISTS `test`.`_test_pt_old`
2023-02-28T10:42:43 Dropped old table `test`.`_test_pt_old` OK.
2023-02-28T10:42:43 Dropping triggers...
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_test_pt_del`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_test_pt_upd`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_test_pt_ins`
2023-02-28T10:42:43 Dropped triggers OK.
Successfully altered `test`.`test_pt`.

删除前：
mysql> show indexes from  test_pt;
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test_pt |          0 | PRIMARY     |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| test_pt |          1 | idx_address |            1 | address     | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
删除后：
mysql> show indexes from  test_pt;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test_pt |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)


4.总结 
pt-online-schema-change 可以很方便的实现在线对表增加列,但是有个限制条件就是表必须有主键或者唯一索引,
否则添加失败。有主从的环境需要指定: --no-check-replication-filters.
pt-online-schema-change:
1.添加列
2.删除字段
3.修改字段
4.改字段的名称
5.创建索引
6.删除索引
 

戒掉贪嗔痴(薛双奇)
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
离开：Percona的Active Directory迁移的pt-online-schema-change运行程序
02-04
标题中的“离开：Percona的Active Directory迁移的pt-online-schema-change运行程序”指的是Percona工具集中的一个功能，pt-online-schema-change，它用于在MySQL环境中在线更改表结构，而不会导致长时间的服务中断...
mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
07-29
同时，监控MySQL的性能，定期备份，以及使用工具如pt-online-schema-change进行在线DDL修改，都是日常运维中不可或缺的部分。 总之，MySQL 5.7.31在Linux上的部署不仅提供了强大的数据库服务，而且通过优化的特性...
pt-online-schema-change
weixin_44352521的博客
09-13 4513
通过测试的方式查看其原理： 安装percona-toolkit： # wget https://www.percona.com/downloads/percona-toolkit/3.2.1/binary/tarball/percona-toolkit-3.2.1_x86_64.tar.gz # tar xf percona-toolkit-3.2.1_x86_64.tar.gz # cd percona-toolkit-3.2.1 # perl Makefile.PL # make # make test
pt-online-schema-change使用详解
eagle89的专栏
01-09 5095
pt-online-schema-change
MySQL管理工具Percona Toolkit（pt）的32个命令作用介绍
最新发布
学亮编程手记
05-26 254
Percona Toolkit简称pt工具，是Percona公司开发用于管理的工具，功能包括检查主从复制的数据一致性、检查重复索引、定位IO占用高的表文件、在线DDL等，DBA熟悉掌握后将极大提高工作效率。
【MySQL工具】pt-online-schema-change 详细介绍
DBA之路的博客
12-20 2035
无锁表修改表结构工具。
mysql工具percona-toolkit之安装
jc_benben的专栏
10-19 2062
MySQL数据库是轻量级、开源数据库的佼佼者，其功能和管理，健壮性与Oracle相比还是有相当的差距。因此有很多功能强大第三方的衍生产品， 如percona-toolkit，XtraBackup等等。percona-toolkit是一组高级命令行工具的集合，可以查看当前服务的摘要信息，磁盘检测，分析慢查询日志， 查找重复索引，实现表同步等等, 1,下载 可以访问http://www.per
Percona Toolkit mysql辅助利器
weixin_33847182的博客
01-19 266
1 PT介绍 Percona Toolkit简称pt工具—PT-Tools，是Percona公司开发用于管理MySQL的工具，功能包括检查主从复制的数据一致性、检查重复索引、定位IO占用高的表文件、在线DDL等，DBA熟悉掌握后将极大提高工作效率。 2 PT 安装 下载地址 ：https://www.percona.com/downloads/percona-toolkit/ 下载完...
mysql-8.0.21-el7-x86-64.tar.gz
03-23
对于生产环境，还应考虑使用如`mysqldump`进行备份，`pt-online-schema-change`进行在线表结构变更，以及`percona-toolkit`等性能优化工具。同时，理解并正确配置MySQL的系统变量和配置文件`my.cnf`对于优化性能至关...
percona-toolkit-3.0.13_x86_64.tar.gz
08-21
1. `pt-online-schema-change`：在不锁定表的情况下进行结构修改，使得在生产环境中更新表结构成为可能，避免了长时间的服务中断。 2. `pt-table-checksum`和`pt-table-sync`：用于跨服务器验证数据的一致性并同步...
perconna-2018最新工具包
10-11
1. **pt-online-schema-change**：这是一个用于在线修改数据库表结构的工具，可以在不锁定表的情况下进行添加、删除或修改列，以及重命名表等操作。这对于大型生产环境中的数据库来说尤其重要，因为它避免了长时间的...
pt-online-schema-change的原理解析与应用说明
dbtg45121的博客
04-24 593
如果转载，请注明博文来源：www.cnblogs.com/xinysu/ ，版权归 博客园 苏家小萝卜 所有。望各位支持！ PERCONA提供了若干管理维护MySQL的小工具，集成在 PERCONA Toolkit工具中，有慢查询分析、主从差异对比、主从差异修复及在线表结构修改等工具，个人觉得挺好用的。本文简单介绍 pt-o...
MySQL 运维管理工具--pt(Percona-toolkit)
只是甲的博客
09-08 1261
文章目录一.检查和安装与Perl相关的模块二.下载和安装percona toolkit的包2.1 下载和安装percona toolkit的包2.2 安装percona-toolkit工具包2.3 percona-toolkit工具介绍2.3.1 pt-archiver(归档表)2.3.2 pt-online-schema-change(在线修改表结构)2.3.3 pt-table-checksum2.3.4 pt-table-sync2.3.5 pt-deadlock-logger(死锁检测)2.3.6
69.MySQL-PT工具包之-pt-duplicate-key-checker
weixin_43346403的博客
02-27 243
pt-duplicate-key-checker
MySQL慢查询分析工具pt-query-digest
weixin_42338534的博客
08-30 1621
pt-query-digest是用于分析mysql慢查询的一个工具，它可以分析binlog、General log、slowlog，也可以通过SHOWPROCESSLIST或者通过tcpdump抓取的MySQL协议数据来进行分析。可以把分析结果输出到文件中，分析过程是先对查询语句的条件进行参数化，然后对参数化以后的查询进行分组统计，统计出各查询的执行时间、次数、占比等，可以借助分析结果找出问题进行优化。
pt-online-schema-change VS oak-online-alter-table
weixin_33674437的博客
06-10 164
前言 在上篇文章中提到了MySQL 5.6 Online DDL，如果是MySQL 5.5的版本在DDL方面是要付出代价的，虽然已经有了Fast index Creation，但是在添加字段还是会锁表的，而且在添加删除辅助索引是会加S锁，也就是无法进行写操作。所以，这里就有相关工具的出现，那就是pt-online-schema-change和oak-online-alter-table，都实现了...
MYSQL大表利用PT工具归档
linuxplayer
07-12 700
MYSQL大表利用PT工具归档

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值