pt-online-schema-change的用法
pt-online-schema-change的用法
环境：
10.192.30.53 主库
10.192.30.60 从库
mysql版本：8.0.17
为了方便操作，简单的写了如下的脚本。
#!/bin/bash # file_name: /usr/local/scripts/pt_change_online.sh . /etc/init.d/functions ########################################################################################## Date_Time=`date +%Y%m%d%H%M%S` Master_Host="10.192.30.53" Slave_Host="10.192.30.60" Port_Num="3306" User_Name="admin_m" Pass_word="rA75MQy*R*y@KO4z%LZe" Pt_Cmd="/usr/bin/pt-online-schema-change" MySQL_Dump_Cmd="/usr/bin/mysqldump" Bak_Data_Dir_="/data/backup/pt_dump/data/" Bak_logs_Dir="/data/backup/pt_dump/logs/" Db_Name="dbtest" Table_Name="test_article" Character_Name="utf8mb4" # sql操作中必须去掉反引号,其他ddl操作只需要替换"add column name varchar(64) NOT NULL DEFAULT '' COMMENT '用户名';" 即可，可以不同带上最后的分号 ########################################################################################## function Data_table_dump(){ "${MySQL_Dump_Cmd}" -h"${Slave_Host}" -P"${Port_Num}" -u"${User_Name}" -p"${Pass_word}" --default-character-set="${Character_Name}" --skip-tz-utc --routines --events --hex-blob --log-error="${Bak_logs_Dir}"dump_"${Date_Time}".log --set-gtid-purged=OFF --single-transaction --dump-slave=2 --max-allowed-packet=33554432 --skip-opt "${Db_Name}" "${Table_Name}">"${Bak_Data_Dir_}""${Db_Name}"_"${Table_Name}"."${Date_Time}".sql.dump } function Pirnt_Log(){ Results=`tail -1 "${Bak_Data_Dir_}""${Db_Name}"_"${Table_Name}"."${Date_Time}".sql.dump | awk '{print $2,$3}'|awk '{print $1,$2}'` if [ "${Results}"X == "Dump completed"X ]; then echo " "${Bak_Data_Dir_}""${Db_Name}"_"${Table_Name}"."${Date_Time}".sql.dump Dump completed. " >>"${Bak_logs_Dir}"dump_"${Date_Time}".log else echo " "${Bak_Data_Dir_}""${Db_Name}"_"${Table_Name}"."${Date_Time}".sql.dump Dump error! " >>"${Bak_logs_Dir}"dump_"${Date_Time}".log fi } function Pt_Online_Change_Schema(){ "${Pt_Cmd}" --host="${Master_Host}" --port="${Port_Num}" --user="${User_Name}" --password="${Pass_word}" --no-check-replication-filters --no-check-unique-key-change D="${Db_Name}",t="${Table_Name}" --charset="${Character_Name}" --no-check-alter --alter=" add column name varchar(64) NOT NULL DEFAULT '' COMMENT '用户名'; " --execute >>"${Bak_logs_Dir}"pt_"${Date_Time}".log } function main(){ Data_table_dump Pirnt_Log if [ $? -eq 0 ]; then Pt_Online_Change_Schema else exit fi } main # end ##########################################################################################
# 准备测试表 mysql> show create table test_article\G; *************************** 1. row *************************** Table: test_article Create Table: CREATE TABLE `test_article` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(64) NOT NULL DEFAULT '' COMMENT '文章标题', `content` longtext NOT NULL COMMENT '资讯内容', `source` text NOT NULL COMMENT '来源', `seo_keyword` varchar(128) NOT NULL DEFAULT '' COMMENT '搜索引擎敏感搜索关键词', `seo_discrible` varchar(255) NOT NULL DEFAULT '' COMMENT '搜索引擎敏感描述字符串', `seo_title` varchar(255) NOT NULL DEFAULT '' COMMENT '搜索引擎敏感标题词', `top_column_id` int(11) NOT NULL DEFAULT '0' COMMENT '栏目表中一级栏目id', `second_column_id` int(11) NOT NULL DEFAULT '0' COMMENT '栏目表中二级栏目id', `img_url` varchar(1024) NOT NULL DEFAULT '' COMMENT '缩略图url', `sort_value` int(11) NOT NULL DEFAULT '0' COMMENT '排序权重,越大越靠前', `public_uid` int(11) NOT NULL DEFAULT '0' COMMENT '发布人的uid', `state` tinyint(4) NOT NULL DEFAULT '1' COMMENT '0已删除,1发布,2未发布', `reading_times` int(11) NOT NULL DEFAULT '0' COMMENT '浏览总量', `tag` varchar(1024) NOT NULL DEFAULT '' COMMENT '标签id,json字符串数组', `related_recommend` varchar(255) NOT NULL DEFAULT '' COMMENT '相关推荐,json', `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) ERROR: No query specified
-- 1、添加字段: alter table test_article add column name varchar(64) NOT NULL DEFAULT '' COMMENT '用户名'; -- 2、删除字段: alter table test_article drop column name ; -- 3、添加普通索引: alter table test_article add index idx_name(name); -- 4、删除普通索引: alter table test_article drop index idx_name; -- 3、添加唯一索引: alter table test_article add unique index idx_name(name); -- 4、删除唯一索引: alter table test_article drop index idx_name; -- 5、修改字段长度：alter table test_article modify name varchar(128) NOT NULL DEFAULT '' COMMENT '用户名或者昵称'; -- 6、修改字段名称: alter table test_article change name user_name varchar(128) NOT NULL DEFAULT '' COMMENT '用户名';
[root@fudao_db_cluster_003 pt_dump]# sh /usr/local/scripts/pt_change_online.sh mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@fudao_db_cluster_003 pt_dump]# ll * data: total 664 -rw-r--r-- 1 root root 679612 Aug 16 14:31 dbtest_test_article.20190816143119.sql.dump logs: total 8 -rw-r--r-- 1 root root 89 Aug 16 14:31 dump_20190816143119.log -rw-r--r-- 1 root root 1010 Aug 16 14:31 pt_20190816143119.log [root@fudao_db_cluster_003 pt_dump]# cat logs/dump_20190816143119.log /data/backup/pt_dump/data/dbtest_test_article.20190816143119.sql.dump Dump completed. [root@fudao_db_cluster_003 pt_dump]# cat logs/pt_20190816143119.log Found 1 slaves: fudao_db_cluster_003 -> 10.192.30.60:3306 Will check slave lag on: fudao_db_cluster_003 -> 10.192.30.60: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 `dbtest`.`test_article`... Creating new table... Created new table dbtest._test_article_new OK. Altering new table... Altered `dbtest`.`_test_article_new` OK. 2019-08-16T14:31:22 Creating triggers... 2019-08-16T14:31:22 Created triggers OK. 2019-08-16T14:31:22 Copying approximately 36 rows... 2019-08-16T14:31:32 Copied rows OK. 2019-08-16T14:31:32 Analyzing new table... 2019-08-16T14:31:33 Swapping tables... 2019-08-16T14:31:33 Swapped original and new tables OK. 2019-08-16T14:31:33 Dropping old table... 2019-08-16T14:31:33 Dropped old table `dbtest`.`_test_article_old` OK. 2019-08-16T14:31:33 Dropping triggers... 2019-08-16T14:31:33 Dropped triggers OK. Successfully altered `dbtest`.`test_article`. [root@fudao_db_cluster_003 pt_dump]# # 删除字段的操作，只需要修改：/usr/local/scripts/pt_change_online.sh 例如： function Pt_Online_Change_Schema(){ "${Pt_Cmd}" --host="${Master_Host}" --port="${Port_Num}" --user="${User_Name}" --password="${Pass_word}" --no-check-replication-filters --no-check-unique-key-change D="${Db_Name}",t="${Table_Name}" --charset="${Character_Name}" --alter=" drop column name # 修改这一行，即--alter 和 --execute之间的内容，ddl操作类似。 " --execute >>"${Bak_logs_Dir}"pt_"${Date_Time}".log } [root@fudao_db_cluster_003 pt_dump]# cat logs/pt_20190816143538.log Found 1 slaves: fudao_db_cluster_003 -> 10.192.30.60:3306 Will check slave lag on: fudao_db_cluster_003 -> 10.192.30.60: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 `dbtest`.`test_article`... Creating new table... Created new table dbtest._test_article_new OK. Altering new table... Altered `dbtest`.`_test_article_new` OK. 2019-08-16T14:35:41 Creating triggers... 2019-08-16T14:35:41 Created triggers OK. 2019-08-16T14:35:41 Copying approximately 36 rows... 2019-08-16T14:35:53 Copied rows OK. 2019-08-16T14:35:53 Analyzing new table... 2019-08-16T14:35:54 Swapping tables... 2019-08-16T14:35:54 Swapped original and new tables OK. 2019-08-16T14:35:54 Dropping old table... 2019-08-16T14:35:54 Dropped old table `dbtest`.`_test_article_old` OK. 2019-08-16T14:35:54 Dropping triggers... 2019-08-16T14:35:54 Dropped triggers OK. Successfully altered `dbtest`.`test_article`. [root@fudao_db_cluster_003 pt_dump]#
