pt-online-schema-change安装和使用(同时验证shardingsphere-proxy上的操作)
环境:
OS:Centos 7
1.下载
192.168.1.14
cd /soft
wget https://downloads.percona.com/downloads/percona-toolkit/3.7.0/binary/tarball/percona-toolkit-3.7.0_x86_64.tar.gz
2.安装
cd /soft
tar -xvf percona-toolkit-3.7.0_x86_64.tar.gz
mv percona-toolkit-3.7.0 /opt/
[root@es bin]# /opt/percona-toolkit-3.7.0/bin/pt-online-schema-change --version
pt-online-schema-change 3.7.0
3.链接真实的实例添加字段
干跑
/opt/percona-toolkit-3.7.0/bin/pt-online-schema-change -h 192.168.1.113 -uroot -p"hxl3040" --charset='utf8mb4' --recursion-method=none --alter='add column name1 varchar(32) DEFAULT NULL COMMENT "名称1",ADD COLUMN name2 varchar(32) DEFAULT NULL COMMENT "名称2"' D=db_test,t=t_order_hxl --dry-run
真正执行
/opt/percona-toolkit-3.7.0/bin/pt-online-schema-change -h 192.168.1.113 -uroot -p"hxl3040" --charset='utf8mb4' --recursion-method=none --alter='add column name1 varchar(32) DEFAULT NULL COMMENT "名称1",ADD COLUMN name2 varchar(32) DEFAULT NULL COMMENT "名称2"' D=db_test,t=t_order_hxl --execute
4.通过shardingsphere-proxy添加字段
[root@es bin]# /opt/percona-toolkit-3.7.0/bin/pt-online-schema-change -h 192.168.1.113 -uroot -p"123456" -P3307 --charset='utf8mb4' --recursion-method=none --alter='add column name1 varchar(32) DEFAULT NULL COMMENT "名称1",ADD COLUMN name2 varchar(32) DEFAULT NULL COMMENT "名称2"' D=sharding_db_test,t=t_order --dry-run
Error setting innodb_lock_wait_timeout: DBD::mysql::db do failed: Unknown system variable 'innodb_lock_wait_timeout' [for Statement "SET SESSION innodb_lock_wait_timeout=1"]. The current value for innodb_lock_wait_timeout is 120. If the variable is read only (not dynamic), specify --set-vars innodb_lock_wait_timeout=120 to avoid this warning, else manually set the variable and restart MySQL.
Error setting innodb_lock_wait_timeout: DBD::mysql::db do failed: Unknown system variable 'innodb_lock_wait_timeout' [for Statement "SET SESSION innodb_lock_wait_timeout=1"]. The current value for innodb_lock_wait_timeout is 120. If the variable is read only (not dynamic), specify --set-vars innodb_lock_wait_timeout=120 to avoid this warning, else manually set the variable and restart MySQL.
DBD::mysql::db selectall_arrayref failed: Unknown database 'sharding_db_test' [for Statement "SHOW TRIGGERS FROM `sharding_db_test` LIKE 't\_order'"] at /opt/percona-toolkit-3.7.0/bin/pt-online-schema-change line 11573.
结论:pt-osc是无法走shardingsphere-proxy加字段的
解决办法:
链接真实的实例,对每个表进行修改,然后刷新shardingsphere-proxy元数据
/opt/percona-toolkit-3.7.0/bin/pt-online-schema-change -h 192.168.1.113 -uroot -p"hxl3040" --charset='utf8mb4' --recursion-method=none --alter='add column name1 varchar(32) DEFAULT NULL COMMENT "名称1",ADD COLUMN name2 varchar(32) DEFAULT NULL COMMENT "名称2"' D=db_test,t=t_order_0 --execute
/opt/percona-toolkit-3.7.0/bin/pt-online-schema-change -h 192.168.1.113 -uroot -p"hxl3040" --charset='utf8mb4' --recursion-method=none --alter='add column name1 varchar(32) DEFAULT NULL COMMENT "名称1",ADD COLUMN name2 varchar(32) DEFAULT NULL COMMENT "名称2"' D=db_test,t=t_order_1 --execute
/opt/percona-toolkit-3.7.0/bin/pt-online-schema-change -h 192.168.1.113 -uroot -p"hxl3040" --charset='utf8mb4' --recursion-method=none --alter='add column name1 varchar(32) DEFAULT NULL COMMENT "名称1",ADD COLUMN name2 varchar(32) DEFAULT NULL COMMENT "名称2"' D=db_test,t=t_order_2 --execute
/opt/percona-toolkit-3.7.0/bin/pt-online-schema-change -h 192.168.1.113 -uroot -p"hxl3040" --charset='utf8mb4' --recursion-method=none --alter='add column name1 varchar(32) DEFAULT NULL COMMENT "名称1",ADD COLUMN name2 varchar(32) DEFAULT NULL COMMENT "名称2"' D=db_test,t=t_order_3 --execute
/opt/percona-toolkit-3.7.0/bin/pt-online-schema-change -h 192.168.1.113 -uroot -p"hxl3040" --charset='utf8mb4' --recursion-method=none --alter='add column name1 varchar(32) DEFAULT NULL COMMENT "名称1",ADD COLUMN name2 varchar(32) DEFAULT NULL COMMENT "名称2"' D=db_test,t=t_order_4 --execute
/opt/percona-toolkit-3.7.0/bin/pt-online-schema-change -h 192.168.1.113 -uroot -p"hxl3040" --charset='utf8mb4' --recursion-method=none --alter='add column name1 varchar(32) DEFAULT NULL COMMENT "名称1",ADD COLUMN name2 varchar(32) DEFAULT NULL COMMENT "名称2"' D=db_test,t=t_order_5 --execute
/opt/percona-toolkit-3.7.0/bin/pt-online-schema-change -h 192.168.1.113 -uroot -p"hxl3040" --charset='utf8mb4' --recursion-method=none --alter='add column name1 varchar(32) DEFAULT NULL COMMENT "名称1",ADD COLUMN name2 varchar(32) DEFAULT NULL COMMENT "名称2"' D=db_test,t=t_order_6 --execute
/opt/percona-toolkit-3.7.0/bin/pt-online-schema-change -h 192.168.1.113 -uroot -p"hxl3040" --charset='utf8mb4' --recursion-method=none --alter='add column name1 varchar(32) DEFAULT NULL COMMENT "名称1",ADD COLUMN name2 varchar(32) DEFAULT NULL COMMENT "名称2"' D=db_test,t=t_order_7 --execute
/opt/percona-toolkit-3.7.0/bin/pt-online-schema-change -h 192.168.1.113 -uroot -p"hxl3040" --charset='utf8mb4' --recursion-method=none --alter='add column name1 varchar(32) DEFAULT NULL COMMENT "名称1",ADD COLUMN name2 varchar(32) DEFAULT NULL COMMENT "名称2"' D=db_test,t=t_order_8 --execute
/opt/percona-toolkit-3.7.0/bin/pt-online-schema-change -h 192.168.1.113 -uroot -p"hxl3040" --charset='utf8mb4' --recursion-method=none --alter='add column name1 varchar(32) DEFAULT NULL COMMENT "名称1",ADD COLUMN name2 varchar(32) DEFAULT NULL COMMENT "名称2"' D=db_test,t=t_order_9 --execute
刷新shardingsphere-proxy元数据
/home/mysql57/bin/mysql -h 192.168.1.113 -uroot -p123456 -P3307 -c
mysql> use sharding_db_test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------------------+
| Tables_in_sharding_db_test |
+----------------------------+
| t_order |
+----------------------------+
1 row in set (0.00 sec)
mysql> show create table t_order;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_order | CREATE TABLE `t_order` (
`order_id` bigint(20) NOT NULL,
`order_name` varchar(100) DEFAULT NULL,
`total_money` varchar(100) DEFAULT NULL,
`user_id` bigint(20) NOT NULL,
`name1` varchar(32) DEFAULT NULL COMMENT '名称1',
`name2` varchar(32) DEFAULT NULL COMMENT '名称2',
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
发现自动感知到了.
但是查询*,却没有相应的字段
mysql> select * from t_order;
+----------+------------+-------------+---------+
| order_id | order_name | total_money | user_id |
+----------+------------+-------------+---------+
| 10 | name10 | 1000 | 10 |
| 1 | name1 | 100 | 1 |
| 2 | name2 | 200 | 2 |
| 3 | name3 | 300 | 3 |
| 4 | name4 | 400 | 4 |
| 5 | name5 | 500 | 5 |
| 6 | name6 | 600 | 6 |
| 7 | name7 | 700 | 7 |
| 8 | name8 | 800 | 8 |
| 9 | name9 | 900 | 9 |
+----------+------------+-------------+---------+
10 rows in set (0.02 sec)
mysql> select name1,name2 from t_order;
ERROR 10006 (42S02): Unknown column 'name1' in 'field list'.
mysql> REFRESH TABLE METADATA;
Query OK, 0 rows affected (0.34 sec)
mysql> select * from t_order;
+----------+------------+-------------+---------+-------+-------+
| order_id | order_name | total_money | user_id | name1 | name2 |
+----------+------------+-------------+---------+-------+-------+
| 10 | name10 | 1000 | 10 | NULL | NULL |
| 1 | name1 | 100 | 1 | NULL | NULL |
| 2 | name2 | 200 | 2 | NULL | NULL |
| 3 | name3 | 300 | 3 | NULL | NULL |
| 4 | name4 | 400 | 4 | NULL | NULL |
| 5 | name5 | 500 | 5 | NULL | NULL |
| 6 | name6 | 600 | 6 | NULL | NULL |
| 7 | name7 | 700 | 7 | NULL | NULL |
| 8 | name8 | 800 | 8 | NULL | NULL |
| 9 | name9 | 900 | 9 | NULL | NULL |
+----------+------------+-------------+---------+-------+-------+
10 rows in set (0.01 sec)
mysql> select name1,name2 from t_order;
+-------+-------+
| name1 | name2 |
+-------+-------+
| NULL | NULL |
| NULL | NULL |
| NULL | NULL |
| NULL | NULL |
| NULL | NULL |
| NULL | NULL |
| NULL | NULL |
| NULL | NULL |
| NULL | NULL |
| NULL | NULL |
+-------+-------+
10 rows in set (0.00 sec)
5.通过shardingsphere-proxy添加索引
/opt/percona-toolkit-3.7.0/bin/pt-online-schema-change -h 192.168.1.113 -uroot -p"hxl3040" --charset='utf8mb4' --recursion-method=none --alter='ADD INDEX idx_user_id(user_id),ADD INDEX idx_name1(name1)' D=db_test,t=t_order_0 --execute
/opt/percona-toolkit-3.7.0/bin/pt-online-schema-change -h 192.168.1.113 -uroot -p"hxl3040" --charset='utf8mb4' --recursion-method=none --alter='ADD INDEX idx_user_id(user_id),ADD INDEX idx_name1(name1)' D=db_test,t=t_order_1 --execute
/opt/percona-toolkit-3.7.0/bin/pt-online-schema-change -h 192.168.1.113 -uroot -p"hxl3040" --charset='utf8mb4' --recursion-method=none --alter='ADD INDEX idx_user_id(user_id),ADD INDEX idx_name1(name1)' D=db_test,t=t_order_2 --execute
/opt/percona-toolkit-3.7.0/bin/pt-online-schema-change -h 192.168.1.113 -uroot -p"hxl3040" --charset='utf8mb4' --recursion-method=none --alter='ADD INDEX idx_user_id(user_id),ADD INDEX idx_name1(name1)' D=db_test,t=t_order_3 --execute
/opt/percona-toolkit-3.7.0/bin/pt-online-schema-change -h 192.168.1.113 -uroot -p"hxl3040" --charset='utf8mb4' --recursion-method=none --alter='ADD INDEX idx_user_id(user_id),ADD INDEX idx_name1(name1)' D=db_test,t=t_order_4 --execute
/opt/percona-toolkit-3.7.0/bin/pt-online-schema-change -h 192.168.1.113 -uroot -p"hxl3040" --charset='utf8mb4' --recursion-method=none --alter='ADD INDEX idx_user_id(user_id),ADD INDEX idx_name1(name1)' D=db_test,t=t_order_5 --execute
/opt/percona-toolkit-3.7.0/bin/pt-online-schema-change -h 192.168.1.113 -uroot -p"hxl3040" --charset='utf8mb4' --recursion-method=none --alter='ADD INDEX idx_user_id(user_id),ADD INDEX idx_name1(name1)' D=db_test,t=t_order_6 --execute
/opt/percona-toolkit-3.7.0/bin/pt-online-schema-change -h 192.168.1.113 -uroot -p"hxl3040" --charset='utf8mb4' --recursion-method=none --alter='ADD INDEX idx_user_id(user_id),ADD INDEX idx_name1(name1)' D=db_test,t=t_order_7 --execute
/opt/percona-toolkit-3.7.0/bin/pt-online-schema-change -h 192.168.1.113 -uroot -p"hxl3040" --charset='utf8mb4' --recursion-method=none --alter='ADD INDEX idx_user_id(user_id),ADD INDEX idx_name1(name1)' D=db_test,t=t_order_8 --execute
/opt/percona-toolkit-3.7.0/bin/pt-online-schema-change -h 192.168.1.113 -uroot -p"hxl3040" --charset='utf8mb4' --recursion-method=none --alter='ADD INDEX idx_user_id(user_id),ADD INDEX idx_name1(name1)' D=db_test,t=t_order_9 --execute
登录查看
/home/mysql57/bin/mysql -h 192.168.1.113 -uroot -p123456 -P3307 -c
mysql> use sharding_db_test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------------------+
| Tables_in_sharding_db_test |
+----------------------------+
| t_order |
+----------------------------+
1 row in set (0.00 sec)
mysql> show create table t_order;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_order | CREATE TABLE `t_order` (
`order_id` bigint(20) NOT NULL,
`order_name` varchar(100) DEFAULT NULL,
`total_money` varchar(100) DEFAULT NULL,
`user_id` bigint(20) NOT NULL,
`name1` varchar(32) DEFAULT NULL COMMENT '名称1',
`name2` varchar(32) DEFAULT NULL COMMENT '名称2',
PRIMARY KEY (`order_id`),
KEY `idx_user_id` (`user_id`),
KEY `idx_name1` (`name1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
发现通过过来了,确保起见,可以刷新下元数据
mysql> REFRESH TABLE METADATA;
发现无法删除索引
mysql> alter table t_order drop index idx_name1;
ERROR 1091 (42000): Can't DROP 'idx_name1_t_order_0'; check that column/key exists
原因是通过刚才我们不是通过shardingsphere-proxy创建的索引,通过shardingsphere-proxy创建的索引命名规则是:索引名称_分表名,如:idx_name1_t_order_0
5.通过shardingsphere-proxy删除索引
/opt/percona-toolkit-3.7.0/bin/pt-online-schema-change -h 192.168.1.113 -uroot -p"hxl3040" --charset='utf8mb4' --recursion-method=none --alter='drop index idx_name1' D=db_test,t=t_order_0 --execute
/opt/percona-toolkit-3.7.0/bin/pt-online-schema-change -h 192.168.1.113 -uroot -p"hxl3040" --charset='utf8mb4' --recursion-method=none --alter='drop index idx_name1' D=db_test,t=t_order_1 --execute
/opt/percona-toolkit-3.7.0/bin/pt-online-schema-change -h 192.168.1.113 -uroot -p"hxl3040" --charset='utf8mb4' --recursion-method=none --alter='drop index idx_name1' D=db_test,t=t_order_2 --execute
/opt/percona-toolkit-3.7.0/bin/pt-online-schema-change -h 192.168.1.113 -uroot -p"hxl3040" --charset='utf8mb4' --recursion-method=none --alter='drop index idx_name1' D=db_test,t=t_order_3 --execute
/opt/percona-toolkit-3.7.0/bin/pt-online-schema-change -h 192.168.1.113 -uroot -p"hxl3040" --charset='utf8mb4' --recursion-method=none --alter='drop index idx_name1' D=db_test,t=t_order_4 --execute
/opt/percona-toolkit-3.7.0/bin/pt-online-schema-change -h 192.168.1.113 -uroot -p"hxl3040" --charset='utf8mb4' --recursion-method=none --alter='drop index idx_name1' D=db_test,t=t_order_5 --execute
/opt/percona-toolkit-3.7.0/bin/pt-online-schema-change -h 192.168.1.113 -uroot -p"hxl3040" --charset='utf8mb4' --recursion-method=none --alter='drop index idx_name1' D=db_test,t=t_order_6 --execute
/opt/percona-toolkit-3.7.0/bin/pt-online-schema-change -h 192.168.1.113 -uroot -p"hxl3040" --charset='utf8mb4' --recursion-method=none --alter='drop index idx_name1' D=db_test,t=t_order_7 --execute
/opt/percona-toolkit-3.7.0/bin/pt-online-schema-change -h 192.168.1.113 -uroot -p"hxl3040" --charset='utf8mb4' --recursion-method=none --alter='drop index idx_name1' D=db_test,t=t_order_8 --execute
/opt/percona-toolkit-3.7.0/bin/pt-online-schema-change -h 192.168.1.113 -uroot -p"hxl3040" --charset='utf8mb4' --recursion-method=none --alter='drop index idx_name1' D=db_test,t=t_order_9 --execute
通过shardingsphere-proxy查看,发现自动刷新了元数据
mysql> show create table t_order;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_order | CREATE TABLE `t_order` (
`order_id` bigint(20) NOT NULL,
`order_name` varchar(100) DEFAULT NULL,
`total_money` varchar(100) DEFAULT NULL,
`user_id` bigint(20) NOT NULL,
`name1` varchar(32) DEFAULT NULL COMMENT '名称1',
`name2` varchar(32) DEFAULT NULL COMMENT '名称2',
PRIMARY KEY (`order_id`),
KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
浙公网安备 33010602011771号