mysql在線更改表可用工具 pt-online-schema-change?更改，或者用gh-ost更改。pt-online-schema-change?在原表創建索引，跟蹤新插入的數據。gh-ost則根據日志應用到更改后的表。

下面把pt-online-schema-change封裝為腳本化

#!/usr/bin/python #coding:utf8# 使用方法 例如 python mysql_osc.py -s 192.168.x.x -p 3306 -d test -t test -a 'add column `name16` varchar(200)' import sys import os import commands import argparse
##自定義mysql鏈接類 from mysqlmodel import mysqlconn general_user = cf.get('mysqlinfo','user') general_passwd = cf.get('mysqlinfo','passwd')def osc(server_ip,server_port,dbname,tabname,ddlstmt):try:conn=mysqlconn(server_ip,server_port)sql1="SELECT COUNT(1) as dbcount FROM information_schema.tables WHERE TABLE_SCHEMA not in ('mysql','performance_schema','information_schema','sys','test') and TABLE_SCHEMA='%s' and TABLE_NAME='%s';" % (dbname,tabname)sql2="select COUNT(1) as inxcount from information_schema.STATISTICS where TABLE_SCHEMA='%s' and TABLE_NAME='%s' AND NON_UNIQUE=0 ;" % (dbname,tabname)sql3="show slave status"check_table=conn.myquery(sql1)check_index=conn.myquery(sql2)check_slave=conn.myquery(sql3)if len(check_slave)!=0:print "%s:%s 不是主節點,更改失敗" % (server_ip,server_port)sys.exit()if check_table[0]["dbcount"]!=1:print "表名 %s.%s 不存在" % (dbname,tabname)sys.exit()if check_index[0]["inxcount"]<1:print "表名 %s.%s 不存在唯一索引" % (dbname,tabname)sys.exit()except Exception as error:print "error: %s " % (error)print "mysql 鏈接錯誤"sys.exit()try:cmd = """/usr/local/toolkit/bin/pt-online-schema-change p='%s',u=%s,A=utf8mb4,h='%s',P=%s,D=%s,t=%s --alter='%s' --execute """ % (general_passwd,general_user,server_ip,server_port,dbname,tabname,ddlstmt)#print cmdstatus=commands.getstatusoutput(cmd)mystatus=status[1]if status[1].find("Successfully altered")>0 and status[0]==0:mystatus="Successfully altered"return mystatusexcept Exception as error:print "error: %s " % (error)return "執行失敗" if __name__ == '__main__':#參數:parser = argparse.ArgumentParser(description='請輸入 -s ip , -p 端口 ,-d 數據庫名稱,-t 表名, -a 更改語句')parser.add_argument('-s','--host', type=str,required=True,help="數據庫地址")parser.add_argument('-p','--port', type=int,required=True,help="數據庫端口")parser.add_argument('-d','--db', type=str,required=True,help="數據庫名")parser.add_argument('-t','--table', type=str,required=True,help="表名")parser.add_argument('-a','--ddlstmt', type=str,required=True,help="更改語句")args = parser.parse_args()server_ip = args.hostserver_port = args.portdbname = args.dbtabname = args.tableddlstmt = args.ddlstmtif server_ip=="" or server_port=="" or dbname=="" or tabname=="" or ddlstmt=="":print "輸入完整信息"sys.exit()status=osc(server_ip,server_port,dbname,tabname,ddlstmt)print status

?

轉載于:https://www.cnblogs.com/vansky/p/9396277.html

總結

以上是生活随笔為你收集整理的pt-online-schema-change 脚本化的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。