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=""

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= --max-allowed-packet= --max-allowed-packet= --skip-opt "${Db_Name}" "${Table_Name}">"${Bak_Data_Dir_}""${Db_Name}"_"${Table_Name}"."${Date_Time}".sql.dump

}

function Pirnt_Log(){

    Results=`tail - "${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() NOT NULL DEFAULT '' COMMENT '用户名';

" --execute >>"${Bak_logs_Dir}"pt_"${Date_Time}".log

}

function main(){

    Data_table_dump

    Pirnt_Log

    if [ $? -eq  ]; then

        Pt_Online_Change_Schema

    else

        exit

    fi

}

main

# end

##########################################################################################

# 准备测试表

mysql> show create table test_article\G;

*************************** . row ***************************

       Table: test_article

Create Table: CREATE TABLE `test_article` (

  `id` int() unsigned NOT NULL AUTO_INCREMENT,

  `title` varchar() NOT NULL DEFAULT '' COMMENT '文章标题',

  `content` longtext NOT NULL COMMENT '资讯内容',

  `source` text NOT NULL COMMENT '来源',

  `seo_keyword` varchar() NOT NULL DEFAULT '' COMMENT '搜索引擎敏感搜索关键词',

  `seo_discrible` varchar() NOT NULL DEFAULT '' COMMENT '搜索引擎敏感描述字符串',

  `seo_title` varchar() NOT NULL DEFAULT '' COMMENT '搜索引擎敏感标题词',

  `top_column_id` int() NOT NULL DEFAULT '' COMMENT '栏目表中一级栏目id',

  `second_column_id` int() NOT NULL DEFAULT '' COMMENT '栏目表中二级栏目id',

  `img_url` varchar() NOT NULL DEFAULT '' COMMENT '缩略图url',

  `sort_value` int() NOT NULL DEFAULT '' COMMENT '排序权重,越大越靠前',

  `public_uid` int() NOT NULL DEFAULT '' COMMENT '发布人的uid',

  `state` tinyint() NOT NULL DEFAULT '' COMMENT '0已删除,1发布,2未发布',

  `reading_times` int() NOT NULL DEFAULT '' COMMENT '浏览总量',

  `tag` varchar() NOT NULL DEFAULT '' COMMENT '标签id,json字符串数组',

  `related_recommend` varchar() 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= DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

 row in set (0.00 sec)

ERROR:

No query specified

-- 、添加字段: alter table test_article add column name varchar() NOT NULL DEFAULT '' COMMENT '用户名';

-- 、删除字段: alter table test_article drop column name ;

-- 、添加普通索引: alter table test_article  add index idx_name(name);

-- 、删除普通索引: alter table test_article  drop index idx_name;

-- 、添加唯一索引: alter table test_article  add unique index idx_name(name);

-- 、删除唯一索引: alter table test_article  drop index idx_name;

-- 、修改字段长度：alter table test_article modify  name varchar() NOT NULL DEFAULT '' COMMENT '用户名或者昵称';

-- 、修改字段名称: alter table test_article change  name user_name varchar() 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

-rw-r--r--  root root  Aug  : dbtest_test_article..sql.dump

logs:

total

-rw-r--r--  root root    Aug  : dump_20190816143119.log

-rw-r--r--  root root  Aug  : pt_20190816143119.log

[root@fudao_db_cluster_003 pt_dump]# cat logs/dump_20190816143119.log

 /data/backup/pt_dump/data/dbtest_test_article..sql.dump  Dump completed.

[root@fudao_db_cluster_003 pt_dump]# cat logs/pt_20190816143119.log

Found  slaves:

fudao_db_cluster_003 -> 10.192.30.60:

Will check slave lag on:

fudao_db_cluster_003 -> 10.192.30.60:

Operation, tries, wait:

  analyze_table, ,

  copy_rows, , 0.25

  create_triggers, ,

  drop_triggers, ,

  swap_tables, ,

  update_foreign_keys, ,

Altering `dbtest`.`test_article`...

Creating new table...

Created new table dbtest._test_article_new OK.

Altering new table...

Altered `dbtest`.`_test_article_new` OK.

--16T14:: Creating triggers...

--16T14:: Created triggers OK.

--16T14:: Copying approximately  rows...

--16T14:: Copied rows OK.

--16T14:: Analyzing new table...

--16T14:: Swapping tables...

--16T14:: Swapped original and new tables OK.

--16T14:: Dropping old table...

--16T14:: Dropped old table `dbtest`.`_test_article_old` OK.

--16T14:: Dropping triggers...

--16T14:: 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  slaves:

fudao_db_cluster_003 -> 10.192.30.60:

Will check slave lag on:

fudao_db_cluster_003 -> 10.192.30.60:

Operation, tries, wait:

  analyze_table, ,

  copy_rows, , 0.25

  create_triggers, ,

  drop_triggers, ,

  swap_tables, ,

  update_foreign_keys, ,

Altering `dbtest`.`test_article`...

Creating new table...

Created new table dbtest._test_article_new OK.

Altering new table...

Altered `dbtest`.`_test_article_new` OK.

--16T14:: Creating triggers...

--16T14:: Created triggers OK.

--16T14:: Copying approximately  rows...

--16T14:: Copied rows OK.

--16T14:: Analyzing new table...

--16T14:: Swapping tables...

--16T14:: Swapped original and new tables OK.

--16T14:: Dropping old table...

--16T14:: Dropped old table `dbtest`.`_test_article_old` OK.

--16T14:: Dropping triggers...

--16T14:: Dropped triggers OK.

Successfully altered `dbtest`.`test_article`.

[root@fudao_db_cluster_003 pt_dump]#

巴特西

pt-online-schema-change的用法

pt-online-schema-change的用法

最新文章

热门文章