pt-ost python封装

gh-ost.py


#!/bin/env python
# -*- encoding: utf-8 -*-
#----------------------------------------------
# Purpose:     gh-ost
# Created:     2018-06-16
#----------------------------------------------

import MySQLdb
import re
import sys
import time
import subprocess
import os
from optparse import OptionParser

def calc_time(func):
    def _deco(*args, **kwargs):
        begin_time = time.time()
        func(*args, **kwargs)
        cost_time = time.time() - begin_time
        print 'cost time: %ss' % round(cost_time,2)
    return _deco

def get_table_count(conn,dbname,tbname):
    query  = ''' SELECT count(*) FROM %s.%s ''' %(dbname,tbname)
    cursor = conn.cursor()
    cursor.execute(query)
    row_nums = cursor.fetchone()
    cursor.close()
    conn.close() 
    return row_nums

def online_ddl(conn,ddl_cmd):
    cursor = conn.cursor()
    cursor.execute(ddl_cmd)
    conn.commit()
    cursor.close()
    conn.close()  

#@calc_time
def run_cmd(cmd):
    p = subprocess.Popen(cmd, shell=True)
    return p,p.pid

def drop_ghost_table(conn,ghost_name_list):
    try:
        cursor = conn.cursor()
        query  = ''' DROP TABLE IF EXISTS %s; ''' %(ghost_name_list)
        cursor.execute(query)
        conn.commit()
        cursor.close()
        conn.close()
    except Exception,e:
        print e

if __name__ == "__main__":
    parser = OptionParser()
    parser.add_option("-P", "--Port", help="Port for search", dest="port")
    parser.add_option("-D", "--Dbname", help="the Dbname to use", dest="dbname")
    parser.add_option("-T", "--Table", help="the Table to use", dest="tablename")

    (options, args) = parser.parse_args()

    if not options.port:
        print 'params port need to apply'
        exit()

    if not options.dbname:
        print 'params dbname need to apply'
        exit()

    if not options.tablename:
        print 'params tablename need to apply'
        exit()

    gh_ost_socket   = '/tmp/gh-ost.%s.%s.sock' %(options.dbname,options.tablename)
    #终止标志
    panic_flag      = '/tmp/gh-ost.panic.%s.%s.flag' %(options.dbname,options.tablename)
    # postpone_flag   =  '/tmp/gh-ost.postpone.%s.%s.flag' %(options.dbname,options.tablename)
    #暂停标志
    throttle_flag   = '/tmp/gh-ost.throttle.%s.%s' %(options.dbname,options.tablename)
#    socket = '/data/%s/tmp/mysql.sock' %(options.port)
    socket = '/var/run/mysqld/mysqld.sock'

    

    get_conn = MySQLdb.connect(host='192.168.163.131', port=int(options.port), user='root', passwd='root', db=options.dbname, unix_socket=socket,charset='utf8')
    conn     = MySQLdb.connect(host='192.168.163.131', port=int(options.port), user='root', passwd='root', db=options.dbname, unix_socket=socket,charset='utf8')
    
    (table_count,) = get_table_count(get_conn,options.dbname,options.tablename)
    print ("\033[0;32m%s\033[0m" % "表的数量:%s" %table_count)

    DDL_CMD    = raw_input('Enter DDL CMD   : ').replace('`','')

    gh_command_list = re.split('[ ]+',DDL_CMD)
    if gh_command_list[0].upper() == 'CHANGE' and gh_command_list[1] != gh_command_list[2]:
        print ("\033[0;31m%s\033[0m" % "renamed columns' data will be lost,pt-osc exit...")
        exit()

    if table_count <= 10000:
        ddl = ''' ALTER TABLE %s %s ''' %(options.tablename,DDL_CMD)
        print ("\033[0;36m%s\033[0m" %ddl)
        print ("\033[0;32m%s\033[0m" % "online ddl ...")
        online_ddl(conn,ddl)
        print ("\033[0;32m%s\033[0m" % "执行完成 ...")
        exit()

    else:
        MAX_LOAD   = raw_input('Enter Max Threads_running【25】 : ')
        if not MAX_LOAD:
            Threads_running = 25 
        else:
            try:
                Threads_running = int(MAX_LOAD)
            except ValueError:
                print ("\033[0;31m%s\033[0m" % "输入类型错误,退出...")
                exit()

        CHUNK_SIZE = raw_input('Enter Max chunk-size【1000】    : ')
        if not CHUNK_SIZE:
            chunk_size = 1000
        else:
            try:
                chunk_size = int(CHUNK_SIZE)
            except ValueError:
                print ("\033[0;31m%s\033[0m" % "输入类型错误,退出...")
                exit()

        print ("\033[0;32m%s\033[0m" % "gh-ost ddl ...")
        #--postpone-cut-over-flag-file=%s 
        gh_command = '''/usr/bin/gh-ost --user="root" --password="root" --host=192.168.163.131 --port=%s --database="%s" --table="%s" --allow-on-master  --max-load='Threads_running=%d' --chunk-size=%d --serve-socket-file=%s --panic-flag-file=%s --throttle-additional-flag-file=%s --alter="%s"  --execute ''' %(options.port,options.dbname,options.tablename,Threads_running,chunk_size,gh_ost_socket,panic_flag,throttle_flag,DDL_CMD)
        print ("\033[0;36m%s\033[0m" %gh_command)

    
        child,pid = run_cmd(gh_command)
        print ("\033[0;31mgh-ost's PID:%s\033[0m" %pid)
        print ("\033[0;33m创建:【touch %s】文件,暂停DDL ...\033[0m" %throttle_flag)
        try:
            child.wait()
        except:
            child.terminate()
            #clean
            ghost_name_list = '_%s_ghc,_%s_gho'  %(options.tablename,options.tablename)
            drop_ghost_table(conn,ghost_name_list)
            if os.path.exists(gh_ost_socket):
                os.system('rm -r %s' %gh_ost_socket)
                print ("\033[0;32m%s\033[0m" % "清理完成 ...")
                exit()
            print ("\033[0;32m%s\033[0m" % "清理完成 ...")
            exit()
        finally :
            pass

 

posted @   cdrcsy  阅读(7)  评论(0编辑  收藏  举报
点击右上角即可分享
微信分享提示