def sysbench_run_job(param):
# ips,threads,times,numberoftest,interval
sbjob = param[0]
dbhosts= sbjob.dbhosts
sbhost = sbjob.sbhost
threads=eval(sbjob.threads)
times=int(sbjob.times)
numberoftest=int(sbjob.numberoftest)
interval=int(sbjob.interval)
f = open("job1.txt","w")
x = PrettyTable(["ip", "threads", "times", "No.", "tps","qps","avgLatency","%%user","%%nice","%%system","%%iowait","%%steal","%%idle"])
for ip in dbhosts.keys():
for t in threads:
for i in xrange(1,numberoftest + 1):
LOG('GREEN','Perform %dth/%s test(ip=%s,threads=%d,times=%d)'%(i,numberoftest,ip,t,times))
param=(dbhosts[ip],sbhost,t,times)
result = sysbench_run(param)
print result
row= [ip,t,times,i] + list(result)
print row
x.add_row(row)
f.write(str(row))
LOG('GREEN','Sleep %d seconds'%(interval))
time.sleep(interval)
print x
f.close()
def run_commander(param):
hosts = param[0]
cmd = param[1]
for h in hosts.values():
LOG('GREEN', '=' * 60)
LOG('GREEN', 'Target Host:%s' % (h.ip))
rssh = ssh(h.ip, user=h.user, passwd=h.passwd)
rssh.ssh_run(cmd)
def change_root_passwd(param):
hosts = param[0]
new_pwd = param[1]
for h in hosts.values():
print h
LOG('GREEN', 'Target Host:%s' % (h.ip))
rssh = ssh(h.ip, h.user, passwd=h.passwd)
# 修改root密码*
LOG('GREEN', 'Set the root user password')
rssh.ssh_run("echo '%s'|sudo passwd --stdin root" % (new_pwd))
def uninstall_mysql(hosts):
for hostinfo in hosts:
LOG('GREEN','Target Host:%s'%(hostinfo[0]))
rssh = ssh(hostinfo,ROOT_USER,passwd=ROOT_USER_PASSWORD)
# 关闭mysql数据库
rssh.ssh_run("pkill -9 mysql")
# 卸载MySQL软件
for pkg in mysqlInstallPkgs:
LOG('GREEN','UnInstall MySQL Package: %s'%pkg)
rtn = rssh.ssh_run('rpm -e %s'%pkg[:-4])
# 删除数据文件和配置文件
rssh.ssh_run("rm -rf /opt/huawei/db")
rssh.ssh_run("rm /etc/my.cnf")
# 删除mysql安装文件
rssh.ssh_run("rm -rf /root/source")
def start_slave_process(param):
dbhosts = param[0]
for h in dbhosts.values():
cnx = mysql.connector.connect(user=h.user, password=h.passwd,host=h.ip,database='test')
cur = cnx.cursor()
query = "show slave status"
cur.execute(query)
for (f1) in cur:
Slave_IO_Running = f1[10]
Slave_SQL_Running = f1[11]
if Slave_IO_Running == "No" and Slave_SQL_Running == "No":
# 启动MySQL的slave进程
LOG('GREEN','Start MySQL Slave Process:%s'%(h.ip))
cur.execute("start slave")
elif Slave_IO_Running != "Yes" and Slave_SQL_Running != "Yes":
# 重新启动MySQL的slave进程
LOG('GREEN','Restart MySQL Slave Process:%s'%(h.ip))
cur.execute("stop slave")
cur.execute("start slave")
def fixEnv(hosts):
for hostinfo in hosts:
LOG('GREEN', 'Target Host:%s' % (hostinfo[0]))
rssh = ssh(hostinfo, ROOT_USER, passwd=ROOT_USER_PASSWORD)
# 检查安装环境是否具备
# 判断/opt/huawei目录是否挂载
LOG('GREEN', 'Check if mount point /opt/huawei is exists')
rtn = rssh.ssh_run(testMountCmd)
if int(rtn.split(_mess_part)[1]) < 1:
# 挂载磁盘/opt/huawei
LOG('GREEN', 'Begin to Create Mount Point:/opt/huawei')
rssh.ssh_run("mkfs.ext4 /dev/nvme1n1")
rssh.ssh_run("mkdir -p /opt/huawei")
rssh.ssh_run("mount /dev/nvme1n1 /opt/huawei")
# 安装os相关包
for pkg in osDependencePkgs:
LOG('GREEN', 'Install OS Dependence Package:%s' % (pkg))
rssh.ssh_run('yum -y install %s' % (pkg))
def backup_mysql(hosts):
for hostinfo in hosts:
ip = hostinfo[0]
role = "M" if hostinfo[1] == "" else "S"
LOG('GREEN','='*60)
LOG('GREEN','Target Host:%s'%(ip))
rssh = ssh(hostinfo,user=ROOT_USER,passwd=ROOT_USER_PASSWORD)
# 停止mysql数据库
LOG('GREEN','Stop MySQL service')
rssh.ssh_run("service mysql stop")
# 物理拷贝文件
LOG('GREEN','Backup MySQL Datafile')
todir = time.strftime('/opt/huawei/db%Y%m%d_%H%M')
rssh.ssh_run("cp /opt/huawei/db %s -R"%(todir))
# 重启mysql数据库
LOG('GREEN','Restart MySQL service')
rssh.ssh_run("service mysql start")
# 启动MySQL内部的slave进程
if role == "S":
LOG('GREEN','Restart slave process')
rssh.ssh_run("mysql -uroot -proot -e 'start slave'")
def set_root_passwd(hosts):
for hostinfo in hosts:
ip = hostinfo[0]
LOG('GREEN', '=' * 60)
LOG('GREEN', 'Target Host:%s' % (ip))
rssh = ssh(hostinfo, USER_NAME, pem=USER_PEM)
# 修改root密码*
LOG('GREEN', 'Set the root user password')
rssh.ssh_run("echo '%s'|sudo passwd --stdin root" %
(ROOT_USER_PASSWORD))
# 修改ssh配置文件
LOG('GREEN', 'Modify ssh configuration file:/etc/ssh/sshd_config')
rssh.ssh_run(
"sudo sed -i 's/PasswordAuthentication no/PasswordAuthentication yes/g' %s"
% ("/etc/ssh/sshd_config"))
# 重启sshd服务
LOG('GREEN', 'Restart sshd service')
rssh.ssh_run("sudo service sshd restart")
# 本地ssh登录测试
LOG('GREEN', 'Run a connection test using root user')
rssh = ssh(hostinfo, user=ROOT_USER, passwd=ROOT_USER_PASSWORD)
rssh.ssh_run("echo")
def cpu_metric(fname):
with open(fname) as f:
lines = f.readlines()
lastline = lines[len(lines) - 1]
if lastline.startswith("Average:"):
fields = lastline.strip().split()
user= fields[2]
nice= fields[3]
system= fields[4]
iowait= fields[5]
steal= fields[6]
idle= fields[7]
return user,nice,system,iowait,steal,idle
else:
LOG('RED','%s content is incomplete or corrupt'%(fname))
return ""
def checkEnvReady(param):
hosts = param[0]
pkgs = param[1]
for h in hosts.values():
print h
LOG('GREEN', 'Target Host:%s' % (h.ip))
rssh = ssh(h.ip, h.user, passwd=h.passwd)
# 检查安装环境是否具备
# 1.检查目录是否挂载
LOG('GREEN', 'Check whether mount point /opt/huawei is exists')
rtn = rssh.ssh_run(testMountCmd)
if int(rtn.split(_mess_part)[1]) < 1:
LOG('RED', 'Failure')
else:
LOG('GREEN', "OK")
# 2.检查相关包是否安装
for pkg in pkgs:
LOG('GREEN',
'Check whether OS Dependence Package is installed:%s' % (pkg))
rtn = rssh.ssh_run('rpm -qa|grep %s|wc -l' % (pkg))
if int(rtn.split(_mess_part)[1]) < 1:
LOG('RED', 'Failure')
else:
LOG('GREEN', "OK")
def sysbench_run(param):
dbhost = param[0]
sbhost = param[1]
threads = param[2]
times = param[3]
ip = dbhost.ip
uuid = time.strftime('%m%d_%H%M')
sarlog = '/opt/huawei/%s_%d_%d_%s_sar.log'%(ip,threads,times,uuid)
sblog = '/opt/huawei/%s_%d_%d_%s_sysbench.log'%(ip,threads,times,uuid)
local_sarlog = '/opt/huawei/%s/'%(uuid)
local_sblog = ''
LOG('GREEN','='*60)
LOG('GREEN','Target MySQL Host:%s'%(ip))
rssh = ssh(dbhost.ip,user=dbhost.user,passwd=dbhost.passwd)
# 启动sar监控命令
LOG('GREEN','Start sar to collect cpu statistic data')
cmd = "sar 1 %s >%s &"%(times,sarlog)
rssh.ssh_run(cmd)
# 执行sysbench测试命令
LOG('GREEN','Start sysbench')
rssh_sb = ssh(sbhost.ip,user=sbhost.user,passwd=sbhost.passwd)
cmd = "sysbench /root/sysbench/src/lua/oltp_read_write.lua --mysql-host=%s \
--mysql-port=3306 --mysql-db=test --mysql-user=root --mysql-password=root --table_size=10000000 --tables=10 --threads=%s --time=%s --report-interval=10 --db-driver=mysql run > %s "%(ip,threads,times,sblog)
print cmd
rssh_sb.ssh_run(cmd)
print(time.strftime('%m%d_%H%M%S'))
time.sleep(10)
# 收集sysbench日志
LOG('GREEN','Download sysbench log file')
rssh_sb.sftp_download_file(sblog,sblog)
# 收集sar日志
LOG('GREEN','Download sar log file')
rssh.sftp_download_file(sarlog,sarlog)
return sysbench_metric(sblog) + cpu_metric(sarlog)
def install_mysql(hosts):
for hostinfo in hosts:
ip = hostinfo[0]
ser_id = ip[ip.rfind('.')+1:]
LOG('GREEN','='*60)
LOG('GREEN','Target Host:%s'%(ip))
rssh = ssh(hostinfo,ROOT_USER,passwd=ROOT_USER_PASSWORD)
# 创建work目录
LOG('GREEN','1/9: Create MySQL Directory')
rssh.ssh_run('mkdir -p /root/source')
# 上传mysql文件
LOG('GREEN','2/9: Upload MySQL RPM Install Package')
rssh.sftp_upload_file('/root/source/MySQL-server-5.6.38-1.el7.x86_64.rpm','/root/source/MySQL-server-5.6.38-1.el7.x86_64.rpm')
rssh.sftp_upload_file('/root/source/MySQL-client-5.6.38-1.el7.x86_64.rpm','/root/source/MySQL-client-5.6.38-1.el7.x86_64.rpm')
# 上传my.cnf文件
LOG('GREEN','3/9: Upload MySQL Configure File:/etc/my.cnf')
rssh.sftp_upload_file('/root/my.cnf','/etc/my.cnf')
# 修改my.cnf文件中的server-id
rssh.ssh_run("sudo sed -i 's/18224/%s/g' %s"%(ip[ip.rfind('.')+1:],'/etc/my.cnf'))
# 安装MySQL软件
for pkg in mysqlInstallPkgs:
LOG('GREEN','4/9: Install MySQL Package: %s'%pkg)
rtn = rssh.ssh_run('rpm -ivh %s%s'%(SoftDir,pkg))
# 创建目录和授权
LOG('GREEN','5/9: Create MySQL Data Dir: /opt/huawei/db')
rssh.ssh_run("mkdir -p /opt/huawei/db/data")
rssh.ssh_run("mkdir -p /opt/huawei/db/mysql_temp")
rssh.ssh_run("chown mysql:mysql /opt/huawei/db -R")
rssh.ssh_run("chmod 777 /opt/huawei/db -R")
rssh.ssh_run("chown mysql:mysql /etc/my.cnf")
# 执行建库脚本:
LOG('GREEN','6/9: Run Install DB Script：/usr/bin/mysql_install_db')
rssh.ssh_run("/usr/bin/mysql_install_db --user=mysql")
# 启动mysql服务
LOG('GREEN','7/9: start mysql service')
rtn = rssh.ssh_run('service mysql start')
# 设置root密码
LOG('GREEN','8/9: Set the password of root user')
rtn = rssh.ssh_run('/usr/bin/mysqladmin -u root password "root"')
# 创建root账号
# grant all on *.* to 'root'@'%' identified by 'root';
# flush privileges;
LOG('GREEN','9/9: Add new root user account')
print('mysql -uroot -proot -e "%s"'%("grant all on *.* to 'root'@'%' identified by 'root';flush privileges;"))
rtn = rssh.ssh_run('mysql -uroot -proot -e "%s"'%("grant all on *.* to 'root'@'%' identified by 'root';flush privileges;"))
print(rtn)
