Python

Python

python实现mysql ping 获取c到s端网络延时探讨

pythonAionsky 发表了文章 • 0 个评论 • 408 次浏览 • 2018-02-05 02:14 • 来自相关话题

#!/usr/bin/env python # -*- coding:utf-8 -*-
import mysql.connector 
import time 
''' 
目的:监控app层穿透proxy层访问数据层的延时。
该脚本主要实现,mysql client 到 mysql server 的网络延时监控。
select 1 有查询消耗时间,在压力大的情况下并不精确; 
ping 本身为用于诊断 server是否存活;
两种方法都是利用 发起命令前后 时间差 得出网络延时; 
两种检测方法,可混合使用,通过两个指标差值计算 或单一指标的步差值 再算一定时段均值,可较为合理的反映出MySQL通讯网络是否正常。 
该脚本只实现功能,未做进一步优化。 
单向延时*500,双向延时*1000,不知道这个理解对不对。 
不知道整个MySQL ping 的思路是不是对的。
'''
config = { 
 'host': '127.0.0.1', 
 'user': 'root',
 'password': 'root', 
 'port': 3306, 
 'database': 'test', 
 'charset': 'utf8' } 
try: 
 cnn = mysql.connector.connect(**config) 
except mysql.connector.Error as e: 
 print('connect fails!{}'.format(e)) cursor = cnn.cursor() 
try: 
 #方法一,使用select 语句进行查询,包含查询消耗时间,当数据库压力大时,查询时间可能会变长,可能因为本身数据库压力大导致,但可以变相反应出数据库当前压力。 
 sql_query = 'select 1;' 
 #命令发起前获取时间戳 
 time1 = time.time() 
 cursor.execute(sql_query) 
 result_1 = cursor.fetchall() 
 print result_1 
 #方法2,使用 ping,mysql client发起一次对server ping 请求。本身用于探测server是否正常。
 cnn.ping(cnn) 
 #命令执行完毕后获取时间戳 
 time2 = time.time() 
 #通过发起命令前后来的时间差值来,来确定网络延时。单向延时*500,双向延时*1000 
 time_result = str(float((time2 - time1))*500) + "ms" 
 print time_result 
 
except mysql.connector.Error as e: 
 print('query error!{}'.format(e)) 
finally: 
 cursor.close() 
 cnn.close() 查看全部
#!/usr/bin/env python # -*- coding:utf-8 -*-
import mysql.connector 
import time 
''' 
目的:监控app层穿透proxy层访问数据层的延时。
该脚本主要实现,mysql client 到 mysql server 的网络延时监控。
select 1 有查询消耗时间,在压力大的情况下并不精确; 
ping 本身为用于诊断 server是否存活;
两种方法都是利用 发起命令前后 时间差 得出网络延时; 
两种检测方法,可混合使用,通过两个指标差值计算 或单一指标的步差值 再算一定时段均值,可较为合理的反映出MySQL通讯网络是否正常。 
该脚本只实现功能,未做进一步优化。 
单向延时*500,双向延时*1000,不知道这个理解对不对。 
不知道整个MySQL ping 的思路是不是对的。
'''
config = { 
 'host': '127.0.0.1', 
 'user': 'root',
 'password': 'root', 
 'port': 3306, 
 'database': 'test', 
 'charset': 'utf8' } 
try: 
 cnn = mysql.connector.connect(**config) 
except mysql.connector.Error as e: 
 print('connect fails!{}'.format(e)) cursor = cnn.cursor() 
try: 
 #方法一,使用select 语句进行查询,包含查询消耗时间,当数据库压力大时,查询时间可能会变长,可能因为本身数据库压力大导致,但可以变相反应出数据库当前压力。 
 sql_query = 'select 1;' 
 #命令发起前获取时间戳 
 time1 = time.time() 
 cursor.execute(sql_query) 
 result_1 = cursor.fetchall() 
 print result_1 
 #方法2,使用 ping,mysql client发起一次对server ping 请求。本身用于探测server是否正常。
 cnn.ping(cnn) 
 #命令执行完毕后获取时间戳 
 time2 = time.time() 
 #通过发起命令前后来的时间差值来,来确定网络延时。单向延时*500,双向延时*1000 
 time_result = str(float((time2 - time1))*500) + "ms" 
 print time_result 
 
except mysql.connector.Error as e: 
 print('query error!{}'.format(e)) 
finally: 
 cursor.close() 
 cnn.close()

python脚本统计 MySQL 库中表的个数

pythonA128_huanggr 发表了文章 • 0 个评论 • 542 次浏览 • 2017-09-27 17:20 • 来自相关话题

#!/usr/bin/python

import os,sys,pymysql

mysqlbase = '/usr/local/mysql/bin/'

if  len(sys.argv)  == 1:
    print('format: python + Script_name + user + password + ip')
elif sys.argv[1] == '-h':
    print('format: python + Script_name + user + password + ip')
else:
    user = sys.argv[1]
    passw = sys.argv[2]
    ip = sys.argv[3]
    dbnam=os.popen(mysqlbase+'mysql -h'+ip+' -u'+user+' -p'+passw+'   -h'+ip+' -e "show databases"|grep -vE "mysql|Database|information_schema|performance_schema|sys"').read()
#    dbnam=os.popen(mysqlbase+'mysql -h'+ip+' -u'+user+' -p'+passw+'  -S /tmp/mysql3306.sock -h'+ip+' -e "show databases"|grep -vE "mysql|Database|information_schema|performance_schema|sys"').read()
    DBNAME = dbnam.replace('\n',',').rstrip(',').split(',')
    for dbname in DBNAME:
        lists =
        db=pymysql.connect(ip,user,passw,dbname)
        cursor=db.cursor()
        cursor.execute("show tables")
        data=cursor.fetchall()
        for tabname in data:
            lists.append(tabname)
        print(dbname,(len(data)))
        db.close()
 
# python connmysql.py root 123456 192.168.6.218
('cr_debug', 6)
('hh', 3)
('lts', 15)
('toprankdb', 496)
('tr', 1)
('zabbix', 127)
#每天学习一点点,日积月累。 查看全部
#!/usr/bin/python

import os,sys,pymysql

mysqlbase = '/usr/local/mysql/bin/'

if  len(sys.argv)  == 1:
    print('format: python + Script_name + user + password + ip')
elif sys.argv[1] == '-h':
    print('format: python + Script_name + user + password + ip')
else:
    user = sys.argv[1]
    passw = sys.argv[2]
    ip = sys.argv[3]
    dbnam=os.popen(mysqlbase+'mysql -h'+ip+' -u'+user+' -p'+passw+'   -h'+ip+' -e "show databases"|grep -vE "mysql|Database|information_schema|performance_schema|sys"').read()
#    dbnam=os.popen(mysqlbase+'mysql -h'+ip+' -u'+user+' -p'+passw+'  -S /tmp/mysql3306.sock -h'+ip+' -e "show databases"|grep -vE "mysql|Database|information_schema|performance_schema|sys"').read()
    DBNAME = dbnam.replace('\n',',').rstrip(',').split(',')
    for dbname in DBNAME:
        lists =
        db=pymysql.connect(ip,user,passw,dbname)
        cursor=db.cursor()
        cursor.execute("show tables")
        data=cursor.fetchall()
        for tabname in data:
            lists.append(tabname)
        print(dbname,(len(data)))
        db.close()
 
# python connmysql.py root 123456 192.168.6.218
('cr_debug', 6)
('hh', 3)
('lts', 15)
('toprankdb', 496)
('tr', 1)
('zabbix', 127)
#每天学习一点点,日积月累。

MHA 故障库恢复到集群 python脚本

pythonA128_huanggr 发表了文章 • 0 个评论 • 578 次浏览 • 2017-08-04 17:47 • 来自相关话题

#!/usr/bin/env python
#! _*_ coding:utf-8 _*_

import os,re,time

#注 请把故障库启动后再执行此脚本
#1.获取mha日志中change语句
stmt = os.popen("ssh exsddb3 'cat /var/log/masterha/app1/app1.log'|grep 'All other slaves'|grep \"`date +%d|cut -d'0' -f2` \"|cut -d':' -f4|awk '{sub(/^[ \t]+/, \"\")};1'").read().strip('\n')

ddd = 'CHANGE MASTER TO MASTER_HOST='
ip = re.findall(r'\d+.\d+.\d+.\d+',stmt)[0]
userf = 'replication'
passwf = 'replication'
logfile = re.findall(r"MASTER_LOG_FILE='(.*)', MASTER_LOG",stmt)[0]
logpos = re.findall(r"MASTER_LOG_POS=(.*), MASTER_USER=",stmt)[0]
user = 'root'
passw = 'abc123'
mysqlbase = '/usr/local/mysql/bin/'

#2.转换成标准change语句
change = "%s'%s',MASTER_PASSWORD='%s',MASTER_PORT=%s,MASTER_LOG_FILE='%s',MASTER_LOG_POS=%s,MASTER_USER='%s'" %(ddd,ip,passwf,'3306',logfile,logpos,userf)
status = os.popen("%smysql -u'%s' -p'%s' -e 'show slave status\G'" %(mysqlbase,user,passw)).read()
print(' ')

#3.在故意库中change到新主库,并启动slave,查看salve状态
os.system("%smysql -u'%s' -p'%s' -e '%s'" %(mysqlbase,user,passw,change))
os.system("%smysql -u'%s' -p'%s' -e 'start slave'" %(mysqlbase,user,passw))
os.system("%smysql -u'%s' -p'%s' -e 'show slave status\G'" %(mysqlbase,user,passw))

#4.在manager节点上检查MHA复制状态
os.system("ssh exsddb3 '/usr/bin/masterha_check_repl --global_conf=/etc/masterha/masterha_default.conf --conf=/etc/masterha/app1.conf' >/tmp/check_repl")

print(' ')
print(' ')
ifok = os.popen("cat /tmp/check_repl |grep 'MySQL Replication Health is OK.'").read().strip('\n')
#5.判断MHA集群是否OK,若OK就启动manager进程对MHA进行监控,然后查看MHA集群状态
if ifok == 'MySQL Replication Health is OK.':
    print(' ')
    os.system("ssh exsddb3 'sh /sh/mha_start.sh'")
    time.sleep(2)
    os.system("ssh exsddb3 '/usr/bin/masterha_check_status --global_conf=/etc/masterha/masterha_default.conf --conf=/etc/masterha/app1.conf'")
else:
    print(' ')
    print('MySQL Replication Health is not OK!')

 

#@_@ 欢迎交流!联系人 广西宾阳 阿桂 qq149951292 查看全部
#!/usr/bin/env python
#! _*_ coding:utf-8 _*_

import os,re,time

#注 请把故障库启动后再执行此脚本
#1.获取mha日志中change语句
stmt = os.popen("ssh exsddb3 'cat /var/log/masterha/app1/app1.log'|grep 'All other slaves'|grep \"`date +%d|cut -d'0' -f2` \"|cut -d':' -f4|awk '{sub(/^[ \t]+/, \"\")};1'").read().strip('\n')

ddd = 'CHANGE MASTER TO MASTER_HOST='
ip = re.findall(r'\d+.\d+.\d+.\d+',stmt)[0]
userf = 'replication'
passwf = 'replication'
logfile = re.findall(r"MASTER_LOG_FILE='(.*)', MASTER_LOG",stmt)[0]
logpos = re.findall(r"MASTER_LOG_POS=(.*), MASTER_USER=",stmt)[0]
user = 'root'
passw = 'abc123'
mysqlbase = '/usr/local/mysql/bin/'

#2.转换成标准change语句
change = "%s'%s',MASTER_PASSWORD='%s',MASTER_PORT=%s,MASTER_LOG_FILE='%s',MASTER_LOG_POS=%s,MASTER_USER='%s'" %(ddd,ip,passwf,'3306',logfile,logpos,userf)
status = os.popen("%smysql -u'%s' -p'%s' -e 'show slave status\G'" %(mysqlbase,user,passw)).read()
print(' ')

#3.在故意库中change到新主库,并启动slave,查看salve状态
os.system("%smysql -u'%s' -p'%s' -e '%s'" %(mysqlbase,user,passw,change))
os.system("%smysql -u'%s' -p'%s' -e 'start slave'" %(mysqlbase,user,passw))
os.system("%smysql -u'%s' -p'%s' -e 'show slave status\G'" %(mysqlbase,user,passw))

#4.在manager节点上检查MHA复制状态
os.system("ssh exsddb3 '/usr/bin/masterha_check_repl --global_conf=/etc/masterha/masterha_default.conf --conf=/etc/masterha/app1.conf' >/tmp/check_repl")

print(' ')
print(' ')
ifok = os.popen("cat /tmp/check_repl |grep 'MySQL Replication Health is OK.'").read().strip('\n')
#5.判断MHA集群是否OK,若OK就启动manager进程对MHA进行监控,然后查看MHA集群状态
if ifok == 'MySQL Replication Health is OK.':
    print(' ')
    os.system("ssh exsddb3 'sh /sh/mha_start.sh'")
    time.sleep(2)
    os.system("ssh exsddb3 '/usr/bin/masterha_check_status --global_conf=/etc/masterha/masterha_default.conf --conf=/etc/masterha/app1.conf'")
else:
    print(' ')
    print('MySQL Replication Health is not OK!')

 

#@_@ 欢迎交流!联系人 广西宾阳 阿桂 qq149951292

MySQL 整库单表备份 for python脚本

pythonA128_huanggr 发表了文章 • 0 个评论 • 630 次浏览 • 2017-07-26 14:43 • 来自相关话题

#!/usr/bin/python
#! _*_ coding:utf-8 _*_

from datetime import date, datetime, timedelta
import os,time

user = 'root'
passw = '123456'
ip = '192.168.1.229'
dir = '/data2/backup/3308/'
mysqlbase = '/usr/local/mysql/bin/'
port='3308'

if  os.path.isdir(dir):
    print("dir ok")
else:    
    os.makedirs(dir)
dbnam=os.popen(mysqlbase+'mysql  -u'+user+' -p'+passw+' -h'+ip+' -P'+port+' -e "show databases"|grep -vE "mysql|Database|information_schema|performance_schema|sys"').read()
DBNAME = dbnam.replace('\n',',').rstrip(',').split(',')
os.chdir(dir)
for dbname in DBNAME:
    ad = os.popen(mysqlbase+'mysql -h'+ip+' -u'+user+' -p'+passw+' -P'+port+' -e "use ' + dbname + ';show tables "|grep -v Tables_in').read()
    ad1=ad.replace('\n',',').rstrip(',').split(',')
    for tabname in ad1:
        os.system(mysqlbase+'mysqldump   -h'+ip+' -u'+user+' -p'+passw+' -P'+port+'  --set-gtid-purged=off  --single-transaction  -R  '+dbname+' ' + tabname+' > '+dir+ '/'+dbname + '.'+ tabname +'.sql')
    dat = time.strftime('%Y%m%d_%H%M',time.localtime(time.time()))
    os.system('cd '+dir+';tar zcvf '+dat+dbname+'.tar.gz *sql; rm -f *.sql')
    os.system('find /data2/backup/3306/* -mtime +3 -name "2017*.tar.gz" -exec rm -fr {} \;')

 
 
设置计划任务每小时备份一次方便恢复单表:
0 */1 * * *    /root/python/mysqldump_table.py >/dev/null 2>&1
 
欢迎各位同学一起交流。
  查看全部
#!/usr/bin/python
#! _*_ coding:utf-8 _*_

from datetime import date, datetime, timedelta
import os,time

user = 'root'
passw = '123456'
ip = '192.168.1.229'
dir = '/data2/backup/3308/'
mysqlbase = '/usr/local/mysql/bin/'
port='3308'

if  os.path.isdir(dir):
    print("dir ok")
else:    
    os.makedirs(dir)
dbnam=os.popen(mysqlbase+'mysql  -u'+user+' -p'+passw+' -h'+ip+' -P'+port+' -e "show databases"|grep -vE "mysql|Database|information_schema|performance_schema|sys"').read()
DBNAME = dbnam.replace('\n',',').rstrip(',').split(',')
os.chdir(dir)
for dbname in DBNAME:
    ad = os.popen(mysqlbase+'mysql -h'+ip+' -u'+user+' -p'+passw+' -P'+port+' -e "use ' + dbname + ';show tables "|grep -v Tables_in').read()
    ad1=ad.replace('\n',',').rstrip(',').split(',')
    for tabname in ad1:
        os.system(mysqlbase+'mysqldump   -h'+ip+' -u'+user+' -p'+passw+' -P'+port+'  --set-gtid-purged=off  --single-transaction  -R  '+dbname+' ' + tabname+' > '+dir+ '/'+dbname + '.'+ tabname +'.sql')
    dat = time.strftime('%Y%m%d_%H%M',time.localtime(time.time()))
    os.system('cd '+dir+';tar zcvf '+dat+dbname+'.tar.gz *sql; rm -f *.sql')
    os.system('find /data2/backup/3306/* -mtime +3 -name "2017*.tar.gz" -exec rm -fr {} \;')

 
 
设置计划任务每小时备份一次方便恢复单表:
0 */1 * * *    /root/python/mysqldump_table.py >/dev/null 2>&1
 
欢迎各位同学一起交流。
 

check_gtid_sync.py用于检查从库接收的GTID是否执行完成

MySQLkeung 发表了文章 • 0 个评论 • 563 次浏览 • 2017-01-10 01:07 • 来自相关话题

check_gtid_sync.py参考用法
python check_gtid_sync.py --host="172.16.60.60“ --port=3306 --user="monitor" --password="monitor4lepus"

脚本内容如下#!/usr/bin/env python
#coding: utf-8

import re
import sys
import argparse
import pymysql.cursors

parser = argparse.ArgumentParser()

class CheckMySQL(object):
def __init__(self, host=None, port=None, user=None, passwd=None):
self.dbhost = host
self.dbport = port
self.dbuser = user
self.dbpassword = passwd

def connect(self):
try:
conn = pymysql.connect(self.dbhost, self.dbuser, self.dbpassword, port=self.dbport, charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
conn.commit()
except Exception, e:
print(e)
return 1
return cursor

def isGTID_sync(self):
try:
with self.connect() as cursor:
cursor.execute("show slave status")
result = cursor.fetchone()
cursor.close()
retd_gtid = int(result.get("Retrieved_Gtid_Set").split("-")[-1])
exec_gtid = int(result.get("Executed_Gtid_Set").split("-")[-1])
if retd_gtid == exec_gtid:
result = "OK"
elif retd_gtid > exec_gtid:
result = "Gtid_Set_Behind_Master: %s" % (retd_gtid - exec_gtid)
except Exception, e:
print(e)
return "ERROR"
return result


if __name__== "__main__":
parser.add_argument("--host", type=str, help="mysql server host")
parser.add_argument("--port", type=int, help="mysql server port")
parser.add_argument("--user", type=str, help="mysql server user")
parser.add_argument("--password", type=str, help="mysql server passwd")
args = parser.parse_args()
if not args.host or not args.port or not args.user or not args.password:
print parser.format_usage()
sys.exit(1)

host=args.host
port=args.port
user=args.user
passwd=args.password
check=CheckMySQL(host,port,user,passwd)
isSync = check.isGTID_sync()
print isSync 查看全部
check_gtid_sync.py参考用法
python check_gtid_sync.py --host="172.16.60.60“ --port=3306 --user="monitor" --password="monitor4lepus"

脚本内容如下
#!/usr/bin/env python
#coding: utf-8

import re
import sys
import argparse
import pymysql.cursors

parser = argparse.ArgumentParser()

class CheckMySQL(object):
def __init__(self, host=None, port=None, user=None, passwd=None):
self.dbhost = host
self.dbport = port
self.dbuser = user
self.dbpassword = passwd

def connect(self):
try:
conn = pymysql.connect(self.dbhost, self.dbuser, self.dbpassword, port=self.dbport, charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
conn.commit()
except Exception, e:
print(e)
return 1
return cursor

def isGTID_sync(self):
try:
with self.connect() as cursor:
cursor.execute("show slave status")
result = cursor.fetchone()
cursor.close()
retd_gtid = int(result.get("Retrieved_Gtid_Set").split("-")[-1])
exec_gtid = int(result.get("Executed_Gtid_Set").split("-")[-1])
if retd_gtid == exec_gtid:
result = "OK"
elif retd_gtid > exec_gtid:
result = "Gtid_Set_Behind_Master: %s" % (retd_gtid - exec_gtid)
except Exception, e:
print(e)
return "ERROR"
return result


if __name__== "__main__":
parser.add_argument("--host", type=str, help="mysql server host")
parser.add_argument("--port", type=int, help="mysql server port")
parser.add_argument("--user", type=str, help="mysql server user")
parser.add_argument("--password", type=str, help="mysql server passwd")
args = parser.parse_args()
if not args.host or not args.port or not args.user or not args.password:
print parser.format_usage()
sys.exit(1)

host=args.host
port=args.port
user=args.user
passwd=args.password
check=CheckMySQL(host,port,user,passwd)
isSync = check.isGTID_sync()
print isSync

python 实现 mysql start | stop | restar | status 便捷管理命令

MySQLglon 发表了文章 • 1 个评论 • 713 次浏览 • 2016-05-16 17:27 • 来自相关话题

脚本还比较傻瓜,要求 MySQL 规范化部署。
basedir = /usr/local/mysql
datadir = /data/mysql/appname/data
conf_path: /data/mysql/appname/my%port.cnf
socket:/tmp/mysql%port.cnf
 
使用:
./mysqlCtrl {start|stop|restart|enter}
./mysqlCtrl -i mysql3316 -h 192.168.1.1 -u glon -p xxx -P3306 -o {start|stop|restart|enter}
 
#!/usr/bin/env python
# -*- coding: UTF-8 -*-
# Author:glon
# date:2016-5-16 17:47:10

import sys
import os
import time
import getopt
import subprocess

app='mysql3316'
dbhost='localhost'
dbport=3316
dbuser='root'
dbpassword='123456'
operations = ['start','stop','restart','status','enter']

def usage():
print '''usage:
-i: app name.
-h: database host.
-P: databaee port
-u: database user
-p: database password
-o: database operation.{start|stop|restart|status|enter}

eg:
1) python mysqlCtrl.py -i mysql3316 -h 192.168.1.1 -u glon -p xxx -P3306 -o start
2) python mysqlCtrl.py start'''

def execCmd(cmd):
res = subprocess.Popen(cmd,stdout = subprocess.PIPE,shell = True)
return res.stdout.read()

def chkMySQL():
chk_mysql_process_cmd="ps aux | grep mysqld | grep %s | grep -v grep | wc -l" % dbport
chk_mysql_port_cmd ="netstat -tunlp | grep \":%s\" | wc -l" % dbport

mysql_process_num = execCmd(chk_mysql_process_cmd)
mysql_port_num = execCmd(chk_mysql_process_cmd)

return int(mysql_process_num and mysql_port_num)

def getOpts():
db_cfg = {}
opts, args = getopt.getopt(sys.argv[1:], 'i:h:P:u:p:o:', ['help'])
ops = dict(opts)
db_cfg['instance'] = ops['-i'] if ops.has_key('-i') else app
db_cfg['host'] = ops['-h'] if ops.has_key('-h') else dbhost
db_cfg['user'] = ops['-u'] if ops.has_key('-u') else dbuser
db_cfg['pwd'] = ops['-p'] if ops.has_key('-p') else dbpassword
db_cfg['port'] = ops['-P'] if ops.has_key('-P') else dbport

db_cfg['opt'] = None
if len(args) > 0:
for arg in args:
if operations.count(arg):
db_cfg['opt'] = arg
break

if ops.has_key('-o'):
db_cfg['opt'] = ops['-o']

return db_cfg

def startMySQL(dbconf):
if not chkMySQL():
cmd = "sudo /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/%s/my%s.cnf &" % (dbconf['instance'],dbconf['port'])
subprocess.Popen(cmd,stderr = subprocess.PIPE,shell=True)
print ">>> Start MySQL \033[32m [ OK ] \033[0m" if chkMySQL() > 0 else '>>> Start MySQL \033[31m [ FAIL ] \033[0m'
else:
print ">>> MySQL is already \033[32m [ EXIST ] \033[0m"

def stopMySQL(dbconf):
if not chkMySQL():
print ">>> Stop MySQL \033[32m [ OK ] \033[0m"
else:
cmd = "sudo /usr/local/mysql/bin/mysqladmin -S /tmp/mysql%s.sock shutdown -u%s -p%s" % (dbconf['port'],dbconf['user'],dbconf['pwd'])
subprocess.Popen(cmd,stderr = subprocess.PIPE,shell=True)
time.sleep(5)
print ">>> Stop MySQL \033[32m [ OK ] \033[0m" if chkMySQL() == 0 else '>>> Stop MySQL \033[31m [ FAIL ] \033[0m'

def enterMySQL(dbconf):
if chkMySQL():
cmd = "/usr/local/mysql/bin/mysql -S /tmp/mysql%s.sock -u%s -p%s" % (dbconf['port'],dbconf['user'],dbconf['pwd'])
os.system(cmd)
else:
print ">>> MySQL is \033[31m [ NOT EXIST ] \033[0m"

if __name__== "__main__":
try:
db_conf = getOpts()
if db_conf['opt'] == 'start':
print '>>> starting MySQL on Port %s ...' % db_conf['port']
startMySQL(db_conf)
elif db_conf['opt'] == 'stop':
print '>>> stopping MySQL on Port %s ...' % db_conf['port']
stopMySQL(db_conf)
elif db_conf['opt'] == 'restart':
print '>>> restarting MySQL on Port %s...' % db_conf['port']
stopMySQL(db_conf)
startMySQL(db_conf)
elif db_conf['opt'] == 'status':
print '>>> MySQL is \033[32m [ RUNNING ] \033[0m on Port %s' % db_conf['port'] if chkMySQL() > 0 else '>>> MySQL is \033[31m [ NOT ON SERVICE ] \033[0m on Port %s' % db_conf['port']
elif db_conf['opt'] == 'enter':
enterMySQL(db_conf)
else:
print '>>> Nothing to do, plz input the right operation ...'
usage()


except getopt.GetoptError, err:
print str(err)
usage()
sys.exit(2) 查看全部
脚本还比较傻瓜,要求 MySQL 规范化部署。
basedir = /usr/local/mysql
datadir = /data/mysql/appname/data
conf_path: /data/mysql/appname/my%port.cnf
socket:/tmp/mysql%port.cnf
 
使用:
./mysqlCtrl {start|stop|restart|enter}
./mysqlCtrl -i mysql3316 -h 192.168.1.1 -u glon -p xxx -P3306 -o {start|stop|restart|enter}
 
#!/usr/bin/env python
# -*- coding: UTF-8 -*-
# Author:glon
# date:2016-5-16 17:47:10

import sys
import os
import time
import getopt
import subprocess

app='mysql3316'
dbhost='localhost'
dbport=3316
dbuser='root'
dbpassword='123456'
operations = ['start','stop','restart','status','enter']

def usage():
print '''usage:
-i: app name.
-h: database host.
-P: databaee port
-u: database user
-p: database password
-o: database operation.{start|stop|restart|status|enter}

eg:
1) python mysqlCtrl.py -i mysql3316 -h 192.168.1.1 -u glon -p xxx -P3306 -o start
2) python mysqlCtrl.py start'''

def execCmd(cmd):
res = subprocess.Popen(cmd,stdout = subprocess.PIPE,shell = True)
return res.stdout.read()

def chkMySQL():
chk_mysql_process_cmd="ps aux | grep mysqld | grep %s | grep -v grep | wc -l" % dbport
chk_mysql_port_cmd ="netstat -tunlp | grep \":%s\" | wc -l" % dbport

mysql_process_num = execCmd(chk_mysql_process_cmd)
mysql_port_num = execCmd(chk_mysql_process_cmd)

return int(mysql_process_num and mysql_port_num)

def getOpts():
db_cfg = {}
opts, args = getopt.getopt(sys.argv[1:], 'i:h:P:u:p:o:', ['help'])
ops = dict(opts)
db_cfg['instance'] = ops['-i'] if ops.has_key('-i') else app
db_cfg['host'] = ops['-h'] if ops.has_key('-h') else dbhost
db_cfg['user'] = ops['-u'] if ops.has_key('-u') else dbuser
db_cfg['pwd'] = ops['-p'] if ops.has_key('-p') else dbpassword
db_cfg['port'] = ops['-P'] if ops.has_key('-P') else dbport

db_cfg['opt'] = None
if len(args) > 0:
for arg in args:
if operations.count(arg):
db_cfg['opt'] = arg
break

if ops.has_key('-o'):
db_cfg['opt'] = ops['-o']

return db_cfg

def startMySQL(dbconf):
if not chkMySQL():
cmd = "sudo /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/%s/my%s.cnf &" % (dbconf['instance'],dbconf['port'])
subprocess.Popen(cmd,stderr = subprocess.PIPE,shell=True)
print ">>> Start MySQL \033[32m [ OK ] \033[0m" if chkMySQL() > 0 else '>>> Start MySQL \033[31m [ FAIL ] \033[0m'
else:
print ">>> MySQL is already \033[32m [ EXIST ] \033[0m"

def stopMySQL(dbconf):
if not chkMySQL():
print ">>> Stop MySQL \033[32m [ OK ] \033[0m"
else:
cmd = "sudo /usr/local/mysql/bin/mysqladmin -S /tmp/mysql%s.sock shutdown -u%s -p%s" % (dbconf['port'],dbconf['user'],dbconf['pwd'])
subprocess.Popen(cmd,stderr = subprocess.PIPE,shell=True)
time.sleep(5)
print ">>> Stop MySQL \033[32m [ OK ] \033[0m" if chkMySQL() == 0 else '>>> Stop MySQL \033[31m [ FAIL ] \033[0m'

def enterMySQL(dbconf):
if chkMySQL():
cmd = "/usr/local/mysql/bin/mysql -S /tmp/mysql%s.sock -u%s -p%s" % (dbconf['port'],dbconf['user'],dbconf['pwd'])
os.system(cmd)
else:
print ">>> MySQL is \033[31m [ NOT EXIST ] \033[0m"

if __name__== "__main__":
try:
db_conf = getOpts()
if db_conf['opt'] == 'start':
print '>>> starting MySQL on Port %s ...' % db_conf['port']
startMySQL(db_conf)
elif db_conf['opt'] == 'stop':
print '>>> stopping MySQL on Port %s ...' % db_conf['port']
stopMySQL(db_conf)
elif db_conf['opt'] == 'restart':
print '>>> restarting MySQL on Port %s...' % db_conf['port']
stopMySQL(db_conf)
startMySQL(db_conf)
elif db_conf['opt'] == 'status':
print '>>> MySQL is \033[32m [ RUNNING ] \033[0m on Port %s' % db_conf['port'] if chkMySQL() > 0 else '>>> MySQL is \033[31m [ NOT ON SERVICE ] \033[0m on Port %s' % db_conf['port']
elif db_conf['opt'] == 'enter':
enterMySQL(db_conf)
else:
print '>>> Nothing to do, plz input the right operation ...'
usage()


except getopt.GetoptError, err:
print str(err)
usage()
sys.exit(2)

python实现mysql ping 获取c到s端网络延时探讨

pythonAionsky 发表了文章 • 0 个评论 • 408 次浏览 • 2018-02-05 02:14 • 来自相关话题

#!/usr/bin/env python # -*- coding:utf-8 -*-
import mysql.connector 
import time 
''' 
目的:监控app层穿透proxy层访问数据层的延时。
该脚本主要实现,mysql client 到 mysql server 的网络延时监控。
select 1 有查询消耗时间,在压力大的情况下并不精确; 
ping 本身为用于诊断 server是否存活;
两种方法都是利用 发起命令前后 时间差 得出网络延时; 
两种检测方法,可混合使用,通过两个指标差值计算 或单一指标的步差值 再算一定时段均值,可较为合理的反映出MySQL通讯网络是否正常。 
该脚本只实现功能,未做进一步优化。 
单向延时*500,双向延时*1000,不知道这个理解对不对。 
不知道整个MySQL ping 的思路是不是对的。
'''
config = { 
 'host': '127.0.0.1', 
 'user': 'root',
 'password': 'root', 
 'port': 3306, 
 'database': 'test', 
 'charset': 'utf8' } 
try: 
 cnn = mysql.connector.connect(**config) 
except mysql.connector.Error as e: 
 print('connect fails!{}'.format(e)) cursor = cnn.cursor() 
try: 
 #方法一,使用select 语句进行查询,包含查询消耗时间,当数据库压力大时,查询时间可能会变长,可能因为本身数据库压力大导致,但可以变相反应出数据库当前压力。 
 sql_query = 'select 1;' 
 #命令发起前获取时间戳 
 time1 = time.time() 
 cursor.execute(sql_query) 
 result_1 = cursor.fetchall() 
 print result_1 
 #方法2,使用 ping,mysql client发起一次对server ping 请求。本身用于探测server是否正常。
 cnn.ping(cnn) 
 #命令执行完毕后获取时间戳 
 time2 = time.time() 
 #通过发起命令前后来的时间差值来,来确定网络延时。单向延时*500,双向延时*1000 
 time_result = str(float((time2 - time1))*500) + "ms" 
 print time_result 
 
except mysql.connector.Error as e: 
 print('query error!{}'.format(e)) 
finally: 
 cursor.close() 
 cnn.close() 查看全部
#!/usr/bin/env python # -*- coding:utf-8 -*-
import mysql.connector 
import time 
''' 
目的:监控app层穿透proxy层访问数据层的延时。
该脚本主要实现,mysql client 到 mysql server 的网络延时监控。
select 1 有查询消耗时间,在压力大的情况下并不精确; 
ping 本身为用于诊断 server是否存活;
两种方法都是利用 发起命令前后 时间差 得出网络延时; 
两种检测方法,可混合使用,通过两个指标差值计算 或单一指标的步差值 再算一定时段均值,可较为合理的反映出MySQL通讯网络是否正常。 
该脚本只实现功能,未做进一步优化。 
单向延时*500,双向延时*1000,不知道这个理解对不对。 
不知道整个MySQL ping 的思路是不是对的。
'''
config = { 
 'host': '127.0.0.1', 
 'user': 'root',
 'password': 'root', 
 'port': 3306, 
 'database': 'test', 
 'charset': 'utf8' } 
try: 
 cnn = mysql.connector.connect(**config) 
except mysql.connector.Error as e: 
 print('connect fails!{}'.format(e)) cursor = cnn.cursor() 
try: 
 #方法一,使用select 语句进行查询,包含查询消耗时间,当数据库压力大时,查询时间可能会变长,可能因为本身数据库压力大导致,但可以变相反应出数据库当前压力。 
 sql_query = 'select 1;' 
 #命令发起前获取时间戳 
 time1 = time.time() 
 cursor.execute(sql_query) 
 result_1 = cursor.fetchall() 
 print result_1 
 #方法2,使用 ping,mysql client发起一次对server ping 请求。本身用于探测server是否正常。
 cnn.ping(cnn) 
 #命令执行完毕后获取时间戳 
 time2 = time.time() 
 #通过发起命令前后来的时间差值来,来确定网络延时。单向延时*500,双向延时*1000 
 time_result = str(float((time2 - time1))*500) + "ms" 
 print time_result 
 
except mysql.connector.Error as e: 
 print('query error!{}'.format(e)) 
finally: 
 cursor.close() 
 cnn.close()

python脚本统计 MySQL 库中表的个数

pythonA128_huanggr 发表了文章 • 0 个评论 • 542 次浏览 • 2017-09-27 17:20 • 来自相关话题

#!/usr/bin/python

import os,sys,pymysql

mysqlbase = '/usr/local/mysql/bin/'

if  len(sys.argv)  == 1:
    print('format: python + Script_name + user + password + ip')
elif sys.argv[1] == '-h':
    print('format: python + Script_name + user + password + ip')
else:
    user = sys.argv[1]
    passw = sys.argv[2]
    ip = sys.argv[3]
    dbnam=os.popen(mysqlbase+'mysql -h'+ip+' -u'+user+' -p'+passw+'   -h'+ip+' -e "show databases"|grep -vE "mysql|Database|information_schema|performance_schema|sys"').read()
#    dbnam=os.popen(mysqlbase+'mysql -h'+ip+' -u'+user+' -p'+passw+'  -S /tmp/mysql3306.sock -h'+ip+' -e "show databases"|grep -vE "mysql|Database|information_schema|performance_schema|sys"').read()
    DBNAME = dbnam.replace('\n',',').rstrip(',').split(',')
    for dbname in DBNAME:
        lists =
        db=pymysql.connect(ip,user,passw,dbname)
        cursor=db.cursor()
        cursor.execute("show tables")
        data=cursor.fetchall()
        for tabname in data:
            lists.append(tabname)
        print(dbname,(len(data)))
        db.close()
 
# python connmysql.py root 123456 192.168.6.218
('cr_debug', 6)
('hh', 3)
('lts', 15)
('toprankdb', 496)
('tr', 1)
('zabbix', 127)
#每天学习一点点,日积月累。 查看全部
#!/usr/bin/python

import os,sys,pymysql

mysqlbase = '/usr/local/mysql/bin/'

if  len(sys.argv)  == 1:
    print('format: python + Script_name + user + password + ip')
elif sys.argv[1] == '-h':
    print('format: python + Script_name + user + password + ip')
else:
    user = sys.argv[1]
    passw = sys.argv[2]
    ip = sys.argv[3]
    dbnam=os.popen(mysqlbase+'mysql -h'+ip+' -u'+user+' -p'+passw+'   -h'+ip+' -e "show databases"|grep -vE "mysql|Database|information_schema|performance_schema|sys"').read()
#    dbnam=os.popen(mysqlbase+'mysql -h'+ip+' -u'+user+' -p'+passw+'  -S /tmp/mysql3306.sock -h'+ip+' -e "show databases"|grep -vE "mysql|Database|information_schema|performance_schema|sys"').read()
    DBNAME = dbnam.replace('\n',',').rstrip(',').split(',')
    for dbname in DBNAME:
        lists =
        db=pymysql.connect(ip,user,passw,dbname)
        cursor=db.cursor()
        cursor.execute("show tables")
        data=cursor.fetchall()
        for tabname in data:
            lists.append(tabname)
        print(dbname,(len(data)))
        db.close()
 
# python connmysql.py root 123456 192.168.6.218
('cr_debug', 6)
('hh', 3)
('lts', 15)
('toprankdb', 496)
('tr', 1)
('zabbix', 127)
#每天学习一点点,日积月累。

MHA 故障库恢复到集群 python脚本

pythonA128_huanggr 发表了文章 • 0 个评论 • 578 次浏览 • 2017-08-04 17:47 • 来自相关话题

#!/usr/bin/env python
#! _*_ coding:utf-8 _*_

import os,re,time

#注 请把故障库启动后再执行此脚本
#1.获取mha日志中change语句
stmt = os.popen("ssh exsddb3 'cat /var/log/masterha/app1/app1.log'|grep 'All other slaves'|grep \"`date +%d|cut -d'0' -f2` \"|cut -d':' -f4|awk '{sub(/^[ \t]+/, \"\")};1'").read().strip('\n')

ddd = 'CHANGE MASTER TO MASTER_HOST='
ip = re.findall(r'\d+.\d+.\d+.\d+',stmt)[0]
userf = 'replication'
passwf = 'replication'
logfile = re.findall(r"MASTER_LOG_FILE='(.*)', MASTER_LOG",stmt)[0]
logpos = re.findall(r"MASTER_LOG_POS=(.*), MASTER_USER=",stmt)[0]
user = 'root'
passw = 'abc123'
mysqlbase = '/usr/local/mysql/bin/'

#2.转换成标准change语句
change = "%s'%s',MASTER_PASSWORD='%s',MASTER_PORT=%s,MASTER_LOG_FILE='%s',MASTER_LOG_POS=%s,MASTER_USER='%s'" %(ddd,ip,passwf,'3306',logfile,logpos,userf)
status = os.popen("%smysql -u'%s' -p'%s' -e 'show slave status\G'" %(mysqlbase,user,passw)).read()
print(' ')

#3.在故意库中change到新主库,并启动slave,查看salve状态
os.system("%smysql -u'%s' -p'%s' -e '%s'" %(mysqlbase,user,passw,change))
os.system("%smysql -u'%s' -p'%s' -e 'start slave'" %(mysqlbase,user,passw))
os.system("%smysql -u'%s' -p'%s' -e 'show slave status\G'" %(mysqlbase,user,passw))

#4.在manager节点上检查MHA复制状态
os.system("ssh exsddb3 '/usr/bin/masterha_check_repl --global_conf=/etc/masterha/masterha_default.conf --conf=/etc/masterha/app1.conf' >/tmp/check_repl")

print(' ')
print(' ')
ifok = os.popen("cat /tmp/check_repl |grep 'MySQL Replication Health is OK.'").read().strip('\n')
#5.判断MHA集群是否OK,若OK就启动manager进程对MHA进行监控,然后查看MHA集群状态
if ifok == 'MySQL Replication Health is OK.':
    print(' ')
    os.system("ssh exsddb3 'sh /sh/mha_start.sh'")
    time.sleep(2)
    os.system("ssh exsddb3 '/usr/bin/masterha_check_status --global_conf=/etc/masterha/masterha_default.conf --conf=/etc/masterha/app1.conf'")
else:
    print(' ')
    print('MySQL Replication Health is not OK!')

 

#@_@ 欢迎交流!联系人 广西宾阳 阿桂 qq149951292 查看全部
#!/usr/bin/env python
#! _*_ coding:utf-8 _*_

import os,re,time

#注 请把故障库启动后再执行此脚本
#1.获取mha日志中change语句
stmt = os.popen("ssh exsddb3 'cat /var/log/masterha/app1/app1.log'|grep 'All other slaves'|grep \"`date +%d|cut -d'0' -f2` \"|cut -d':' -f4|awk '{sub(/^[ \t]+/, \"\")};1'").read().strip('\n')

ddd = 'CHANGE MASTER TO MASTER_HOST='
ip = re.findall(r'\d+.\d+.\d+.\d+',stmt)[0]
userf = 'replication'
passwf = 'replication'
logfile = re.findall(r"MASTER_LOG_FILE='(.*)', MASTER_LOG",stmt)[0]
logpos = re.findall(r"MASTER_LOG_POS=(.*), MASTER_USER=",stmt)[0]
user = 'root'
passw = 'abc123'
mysqlbase = '/usr/local/mysql/bin/'

#2.转换成标准change语句
change = "%s'%s',MASTER_PASSWORD='%s',MASTER_PORT=%s,MASTER_LOG_FILE='%s',MASTER_LOG_POS=%s,MASTER_USER='%s'" %(ddd,ip,passwf,'3306',logfile,logpos,userf)
status = os.popen("%smysql -u'%s' -p'%s' -e 'show slave status\G'" %(mysqlbase,user,passw)).read()
print(' ')

#3.在故意库中change到新主库,并启动slave,查看salve状态
os.system("%smysql -u'%s' -p'%s' -e '%s'" %(mysqlbase,user,passw,change))
os.system("%smysql -u'%s' -p'%s' -e 'start slave'" %(mysqlbase,user,passw))
os.system("%smysql -u'%s' -p'%s' -e 'show slave status\G'" %(mysqlbase,user,passw))

#4.在manager节点上检查MHA复制状态
os.system("ssh exsddb3 '/usr/bin/masterha_check_repl --global_conf=/etc/masterha/masterha_default.conf --conf=/etc/masterha/app1.conf' >/tmp/check_repl")

print(' ')
print(' ')
ifok = os.popen("cat /tmp/check_repl |grep 'MySQL Replication Health is OK.'").read().strip('\n')
#5.判断MHA集群是否OK,若OK就启动manager进程对MHA进行监控,然后查看MHA集群状态
if ifok == 'MySQL Replication Health is OK.':
    print(' ')
    os.system("ssh exsddb3 'sh /sh/mha_start.sh'")
    time.sleep(2)
    os.system("ssh exsddb3 '/usr/bin/masterha_check_status --global_conf=/etc/masterha/masterha_default.conf --conf=/etc/masterha/app1.conf'")
else:
    print(' ')
    print('MySQL Replication Health is not OK!')

 

#@_@ 欢迎交流!联系人 广西宾阳 阿桂 qq149951292

MySQL 整库单表备份 for python脚本

pythonA128_huanggr 发表了文章 • 0 个评论 • 630 次浏览 • 2017-07-26 14:43 • 来自相关话题

#!/usr/bin/python
#! _*_ coding:utf-8 _*_

from datetime import date, datetime, timedelta
import os,time

user = 'root'
passw = '123456'
ip = '192.168.1.229'
dir = '/data2/backup/3308/'
mysqlbase = '/usr/local/mysql/bin/'
port='3308'

if  os.path.isdir(dir):
    print("dir ok")
else:    
    os.makedirs(dir)
dbnam=os.popen(mysqlbase+'mysql  -u'+user+' -p'+passw+' -h'+ip+' -P'+port+' -e "show databases"|grep -vE "mysql|Database|information_schema|performance_schema|sys"').read()
DBNAME = dbnam.replace('\n',',').rstrip(',').split(',')
os.chdir(dir)
for dbname in DBNAME:
    ad = os.popen(mysqlbase+'mysql -h'+ip+' -u'+user+' -p'+passw+' -P'+port+' -e "use ' + dbname + ';show tables "|grep -v Tables_in').read()
    ad1=ad.replace('\n',',').rstrip(',').split(',')
    for tabname in ad1:
        os.system(mysqlbase+'mysqldump   -h'+ip+' -u'+user+' -p'+passw+' -P'+port+'  --set-gtid-purged=off  --single-transaction  -R  '+dbname+' ' + tabname+' > '+dir+ '/'+dbname + '.'+ tabname +'.sql')
    dat = time.strftime('%Y%m%d_%H%M',time.localtime(time.time()))
    os.system('cd '+dir+';tar zcvf '+dat+dbname+'.tar.gz *sql; rm -f *.sql')
    os.system('find /data2/backup/3306/* -mtime +3 -name "2017*.tar.gz" -exec rm -fr {} \;')

 
 
设置计划任务每小时备份一次方便恢复单表:
0 */1 * * *    /root/python/mysqldump_table.py >/dev/null 2>&1
 
欢迎各位同学一起交流。
  查看全部
#!/usr/bin/python
#! _*_ coding:utf-8 _*_

from datetime import date, datetime, timedelta
import os,time

user = 'root'
passw = '123456'
ip = '192.168.1.229'
dir = '/data2/backup/3308/'
mysqlbase = '/usr/local/mysql/bin/'
port='3308'

if  os.path.isdir(dir):
    print("dir ok")
else:    
    os.makedirs(dir)
dbnam=os.popen(mysqlbase+'mysql  -u'+user+' -p'+passw+' -h'+ip+' -P'+port+' -e "show databases"|grep -vE "mysql|Database|information_schema|performance_schema|sys"').read()
DBNAME = dbnam.replace('\n',',').rstrip(',').split(',')
os.chdir(dir)
for dbname in DBNAME:
    ad = os.popen(mysqlbase+'mysql -h'+ip+' -u'+user+' -p'+passw+' -P'+port+' -e "use ' + dbname + ';show tables "|grep -v Tables_in').read()
    ad1=ad.replace('\n',',').rstrip(',').split(',')
    for tabname in ad1:
        os.system(mysqlbase+'mysqldump   -h'+ip+' -u'+user+' -p'+passw+' -P'+port+'  --set-gtid-purged=off  --single-transaction  -R  '+dbname+' ' + tabname+' > '+dir+ '/'+dbname + '.'+ tabname +'.sql')
    dat = time.strftime('%Y%m%d_%H%M',time.localtime(time.time()))
    os.system('cd '+dir+';tar zcvf '+dat+dbname+'.tar.gz *sql; rm -f *.sql')
    os.system('find /data2/backup/3306/* -mtime +3 -name "2017*.tar.gz" -exec rm -fr {} \;')

 
 
设置计划任务每小时备份一次方便恢复单表:
0 */1 * * *    /root/python/mysqldump_table.py >/dev/null 2>&1
 
欢迎各位同学一起交流。
 

check_gtid_sync.py用于检查从库接收的GTID是否执行完成

MySQLkeung 发表了文章 • 0 个评论 • 563 次浏览 • 2017-01-10 01:07 • 来自相关话题

check_gtid_sync.py参考用法
python check_gtid_sync.py --host="172.16.60.60“ --port=3306 --user="monitor" --password="monitor4lepus"

脚本内容如下#!/usr/bin/env python
#coding: utf-8

import re
import sys
import argparse
import pymysql.cursors

parser = argparse.ArgumentParser()

class CheckMySQL(object):
def __init__(self, host=None, port=None, user=None, passwd=None):
self.dbhost = host
self.dbport = port
self.dbuser = user
self.dbpassword = passwd

def connect(self):
try:
conn = pymysql.connect(self.dbhost, self.dbuser, self.dbpassword, port=self.dbport, charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
conn.commit()
except Exception, e:
print(e)
return 1
return cursor

def isGTID_sync(self):
try:
with self.connect() as cursor:
cursor.execute("show slave status")
result = cursor.fetchone()
cursor.close()
retd_gtid = int(result.get("Retrieved_Gtid_Set").split("-")[-1])
exec_gtid = int(result.get("Executed_Gtid_Set").split("-")[-1])
if retd_gtid == exec_gtid:
result = "OK"
elif retd_gtid > exec_gtid:
result = "Gtid_Set_Behind_Master: %s" % (retd_gtid - exec_gtid)
except Exception, e:
print(e)
return "ERROR"
return result


if __name__== "__main__":
parser.add_argument("--host", type=str, help="mysql server host")
parser.add_argument("--port", type=int, help="mysql server port")
parser.add_argument("--user", type=str, help="mysql server user")
parser.add_argument("--password", type=str, help="mysql server passwd")
args = parser.parse_args()
if not args.host or not args.port or not args.user or not args.password:
print parser.format_usage()
sys.exit(1)

host=args.host
port=args.port
user=args.user
passwd=args.password
check=CheckMySQL(host,port,user,passwd)
isSync = check.isGTID_sync()
print isSync 查看全部
check_gtid_sync.py参考用法
python check_gtid_sync.py --host="172.16.60.60“ --port=3306 --user="monitor" --password="monitor4lepus"

脚本内容如下
#!/usr/bin/env python
#coding: utf-8

import re
import sys
import argparse
import pymysql.cursors

parser = argparse.ArgumentParser()

class CheckMySQL(object):
def __init__(self, host=None, port=None, user=None, passwd=None):
self.dbhost = host
self.dbport = port
self.dbuser = user
self.dbpassword = passwd

def connect(self):
try:
conn = pymysql.connect(self.dbhost, self.dbuser, self.dbpassword, port=self.dbport, charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
conn.commit()
except Exception, e:
print(e)
return 1
return cursor

def isGTID_sync(self):
try:
with self.connect() as cursor:
cursor.execute("show slave status")
result = cursor.fetchone()
cursor.close()
retd_gtid = int(result.get("Retrieved_Gtid_Set").split("-")[-1])
exec_gtid = int(result.get("Executed_Gtid_Set").split("-")[-1])
if retd_gtid == exec_gtid:
result = "OK"
elif retd_gtid > exec_gtid:
result = "Gtid_Set_Behind_Master: %s" % (retd_gtid - exec_gtid)
except Exception, e:
print(e)
return "ERROR"
return result


if __name__== "__main__":
parser.add_argument("--host", type=str, help="mysql server host")
parser.add_argument("--port", type=int, help="mysql server port")
parser.add_argument("--user", type=str, help="mysql server user")
parser.add_argument("--password", type=str, help="mysql server passwd")
args = parser.parse_args()
if not args.host or not args.port or not args.user or not args.password:
print parser.format_usage()
sys.exit(1)

host=args.host
port=args.port
user=args.user
passwd=args.password
check=CheckMySQL(host,port,user,passwd)
isSync = check.isGTID_sync()
print isSync

python 实现 mysql start | stop | restar | status 便捷管理命令

MySQLglon 发表了文章 • 1 个评论 • 713 次浏览 • 2016-05-16 17:27 • 来自相关话题

脚本还比较傻瓜,要求 MySQL 规范化部署。
basedir = /usr/local/mysql
datadir = /data/mysql/appname/data
conf_path: /data/mysql/appname/my%port.cnf
socket:/tmp/mysql%port.cnf
 
使用:
./mysqlCtrl {start|stop|restart|enter}
./mysqlCtrl -i mysql3316 -h 192.168.1.1 -u glon -p xxx -P3306 -o {start|stop|restart|enter}
 
#!/usr/bin/env python
# -*- coding: UTF-8 -*-
# Author:glon
# date:2016-5-16 17:47:10

import sys
import os
import time
import getopt
import subprocess

app='mysql3316'
dbhost='localhost'
dbport=3316
dbuser='root'
dbpassword='123456'
operations = ['start','stop','restart','status','enter']

def usage():
print '''usage:
-i: app name.
-h: database host.
-P: databaee port
-u: database user
-p: database password
-o: database operation.{start|stop|restart|status|enter}

eg:
1) python mysqlCtrl.py -i mysql3316 -h 192.168.1.1 -u glon -p xxx -P3306 -o start
2) python mysqlCtrl.py start'''

def execCmd(cmd):
res = subprocess.Popen(cmd,stdout = subprocess.PIPE,shell = True)
return res.stdout.read()

def chkMySQL():
chk_mysql_process_cmd="ps aux | grep mysqld | grep %s | grep -v grep | wc -l" % dbport
chk_mysql_port_cmd ="netstat -tunlp | grep \":%s\" | wc -l" % dbport

mysql_process_num = execCmd(chk_mysql_process_cmd)
mysql_port_num = execCmd(chk_mysql_process_cmd)

return int(mysql_process_num and mysql_port_num)

def getOpts():
db_cfg = {}
opts, args = getopt.getopt(sys.argv[1:], 'i:h:P:u:p:o:', ['help'])
ops = dict(opts)
db_cfg['instance'] = ops['-i'] if ops.has_key('-i') else app
db_cfg['host'] = ops['-h'] if ops.has_key('-h') else dbhost
db_cfg['user'] = ops['-u'] if ops.has_key('-u') else dbuser
db_cfg['pwd'] = ops['-p'] if ops.has_key('-p') else dbpassword
db_cfg['port'] = ops['-P'] if ops.has_key('-P') else dbport

db_cfg['opt'] = None
if len(args) > 0:
for arg in args:
if operations.count(arg):
db_cfg['opt'] = arg
break

if ops.has_key('-o'):
db_cfg['opt'] = ops['-o']

return db_cfg

def startMySQL(dbconf):
if not chkMySQL():
cmd = "sudo /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/%s/my%s.cnf &" % (dbconf['instance'],dbconf['port'])
subprocess.Popen(cmd,stderr = subprocess.PIPE,shell=True)
print ">>> Start MySQL \033[32m [ OK ] \033[0m" if chkMySQL() > 0 else '>>> Start MySQL \033[31m [ FAIL ] \033[0m'
else:
print ">>> MySQL is already \033[32m [ EXIST ] \033[0m"

def stopMySQL(dbconf):
if not chkMySQL():
print ">>> Stop MySQL \033[32m [ OK ] \033[0m"
else:
cmd = "sudo /usr/local/mysql/bin/mysqladmin -S /tmp/mysql%s.sock shutdown -u%s -p%s" % (dbconf['port'],dbconf['user'],dbconf['pwd'])
subprocess.Popen(cmd,stderr = subprocess.PIPE,shell=True)
time.sleep(5)
print ">>> Stop MySQL \033[32m [ OK ] \033[0m" if chkMySQL() == 0 else '>>> Stop MySQL \033[31m [ FAIL ] \033[0m'

def enterMySQL(dbconf):
if chkMySQL():
cmd = "/usr/local/mysql/bin/mysql -S /tmp/mysql%s.sock -u%s -p%s" % (dbconf['port'],dbconf['user'],dbconf['pwd'])
os.system(cmd)
else:
print ">>> MySQL is \033[31m [ NOT EXIST ] \033[0m"

if __name__== "__main__":
try:
db_conf = getOpts()
if db_conf['opt'] == 'start':
print '>>> starting MySQL on Port %s ...' % db_conf['port']
startMySQL(db_conf)
elif db_conf['opt'] == 'stop':
print '>>> stopping MySQL on Port %s ...' % db_conf['port']
stopMySQL(db_conf)
elif db_conf['opt'] == 'restart':
print '>>> restarting MySQL on Port %s...' % db_conf['port']
stopMySQL(db_conf)
startMySQL(db_conf)
elif db_conf['opt'] == 'status':
print '>>> MySQL is \033[32m [ RUNNING ] \033[0m on Port %s' % db_conf['port'] if chkMySQL() > 0 else '>>> MySQL is \033[31m [ NOT ON SERVICE ] \033[0m on Port %s' % db_conf['port']
elif db_conf['opt'] == 'enter':
enterMySQL(db_conf)
else:
print '>>> Nothing to do, plz input the right operation ...'
usage()


except getopt.GetoptError, err:
print str(err)
usage()
sys.exit(2) 查看全部
脚本还比较傻瓜,要求 MySQL 规范化部署。
basedir = /usr/local/mysql
datadir = /data/mysql/appname/data
conf_path: /data/mysql/appname/my%port.cnf
socket:/tmp/mysql%port.cnf
 
使用:
./mysqlCtrl {start|stop|restart|enter}
./mysqlCtrl -i mysql3316 -h 192.168.1.1 -u glon -p xxx -P3306 -o {start|stop|restart|enter}
 
#!/usr/bin/env python
# -*- coding: UTF-8 -*-
# Author:glon
# date:2016-5-16 17:47:10

import sys
import os
import time
import getopt
import subprocess

app='mysql3316'
dbhost='localhost'
dbport=3316
dbuser='root'
dbpassword='123456'
operations = ['start','stop','restart','status','enter']

def usage():
print '''usage:
-i: app name.
-h: database host.
-P: databaee port
-u: database user
-p: database password
-o: database operation.{start|stop|restart|status|enter}

eg:
1) python mysqlCtrl.py -i mysql3316 -h 192.168.1.1 -u glon -p xxx -P3306 -o start
2) python mysqlCtrl.py start'''

def execCmd(cmd):
res = subprocess.Popen(cmd,stdout = subprocess.PIPE,shell = True)
return res.stdout.read()

def chkMySQL():
chk_mysql_process_cmd="ps aux | grep mysqld | grep %s | grep -v grep | wc -l" % dbport
chk_mysql_port_cmd ="netstat -tunlp | grep \":%s\" | wc -l" % dbport

mysql_process_num = execCmd(chk_mysql_process_cmd)
mysql_port_num = execCmd(chk_mysql_process_cmd)

return int(mysql_process_num and mysql_port_num)

def getOpts():
db_cfg = {}
opts, args = getopt.getopt(sys.argv[1:], 'i:h:P:u:p:o:', ['help'])
ops = dict(opts)
db_cfg['instance'] = ops['-i'] if ops.has_key('-i') else app
db_cfg['host'] = ops['-h'] if ops.has_key('-h') else dbhost
db_cfg['user'] = ops['-u'] if ops.has_key('-u') else dbuser
db_cfg['pwd'] = ops['-p'] if ops.has_key('-p') else dbpassword
db_cfg['port'] = ops['-P'] if ops.has_key('-P') else dbport

db_cfg['opt'] = None
if len(args) > 0:
for arg in args:
if operations.count(arg):
db_cfg['opt'] = arg
break

if ops.has_key('-o'):
db_cfg['opt'] = ops['-o']

return db_cfg

def startMySQL(dbconf):
if not chkMySQL():
cmd = "sudo /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/%s/my%s.cnf &" % (dbconf['instance'],dbconf['port'])
subprocess.Popen(cmd,stderr = subprocess.PIPE,shell=True)
print ">>> Start MySQL \033[32m [ OK ] \033[0m" if chkMySQL() > 0 else '>>> Start MySQL \033[31m [ FAIL ] \033[0m'
else:
print ">>> MySQL is already \033[32m [ EXIST ] \033[0m"

def stopMySQL(dbconf):
if not chkMySQL():
print ">>> Stop MySQL \033[32m [ OK ] \033[0m"
else:
cmd = "sudo /usr/local/mysql/bin/mysqladmin -S /tmp/mysql%s.sock shutdown -u%s -p%s" % (dbconf['port'],dbconf['user'],dbconf['pwd'])
subprocess.Popen(cmd,stderr = subprocess.PIPE,shell=True)
time.sleep(5)
print ">>> Stop MySQL \033[32m [ OK ] \033[0m" if chkMySQL() == 0 else '>>> Stop MySQL \033[31m [ FAIL ] \033[0m'

def enterMySQL(dbconf):
if chkMySQL():
cmd = "/usr/local/mysql/bin/mysql -S /tmp/mysql%s.sock -u%s -p%s" % (dbconf['port'],dbconf['user'],dbconf['pwd'])
os.system(cmd)
else:
print ">>> MySQL is \033[31m [ NOT EXIST ] \033[0m"

if __name__== "__main__":
try:
db_conf = getOpts()
if db_conf['opt'] == 'start':
print '>>> starting MySQL on Port %s ...' % db_conf['port']
startMySQL(db_conf)
elif db_conf['opt'] == 'stop':
print '>>> stopping MySQL on Port %s ...' % db_conf['port']
stopMySQL(db_conf)
elif db_conf['opt'] == 'restart':
print '>>> restarting MySQL on Port %s...' % db_conf['port']
stopMySQL(db_conf)
startMySQL(db_conf)
elif db_conf['opt'] == 'status':
print '>>> MySQL is \033[32m [ RUNNING ] \033[0m on Port %s' % db_conf['port'] if chkMySQL() > 0 else '>>> MySQL is \033[31m [ NOT ON SERVICE ] \033[0m on Port %s' % db_conf['port']
elif db_conf['opt'] == 'enter':
enterMySQL(db_conf)
else:
print '>>> Nothing to do, plz input the right operation ...'
usage()


except getopt.GetoptError, err:
print str(err)
usage()
sys.exit(2)
Python技术