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)