发布于2019-10-29 16:02 阅读(1474) 评论(0) 点赞(2) 收藏(3)
主要使用到的模块:pymysql、xlwt、paramiko、os 。
废话不多说,直接贴代码。
- # conding=utf-8
- # Version:python3.7
- # Tools:Pycharm 2019.2.1
- __date__ = "2019/10/26 上午9:29"
- __author__ = "HANHAN"
-
- import pymysql
- import xlwt
- import paramiko
- import os, datetime
-
- sql = """select VM_DOMAIN_NAME as 虚拟机名, avg(CPU_AVERAGE_RATE) as cpu利用率平均值 ,AVG(CPU_MAX) as cpu利用率峰值,avg(MEMORY_AVERAGE_RATE) as 内存利用率平均值,AVG(MEMORY_MAX ) 内存利用率峰值 FROM
- (SELECT
- TBL_DOMAIN_HOUR_STAT.ID,
- TBL_DOMAIN_HOUR_STAT.CLUSTER_ID,
- TBL_HOST.`NAME` AS CVK_HOSTNAME,
- TBL_DOMAIN.TITLE AS VM_DOMAIN_NAME,
- TBL_DOMAIN_HOUR_STAT.TIME,
- DATE_FORMAT(TBL_DOMAIN_HOUR_STAT.TIME,'%H:%i') AS Hour_Stamp,
- DATE_FORMAT(TBL_DOMAIN_HOUR_STAT.TIME,'%Y-%m-%d %H:%i') AS Time_Stamp,
- TBL_DOMAIN_HOUR_STAT.CPU AS CPU_AVERAGE_RATE,
- TBL_DOMAIN_HOUR_STAT.CPU_MAX,
- TBL_DOMAIN_HOUR_STAT.CPU_MIN,
- TBL_DOMAIN_HOUR_STAT.MEMORY AS MEMORY_AVERAGE_RATE,
- TBL_DOMAIN_HOUR_STAT.MEMORY_MAX,
- TBL_DOMAIN_HOUR_STAT.MEMORY_MIN
- FROM
- TBL_DOMAIN_HOUR_STAT ,
- TBL_DOMAIN ,
- TBL_HOST
- WHERE
- TBL_DOMAIN.CASTOOLS_STATUS ='1' AND
- TBL_DOMAIN_HOUR_STAT.DOMAIN_ID = TBL_DOMAIN.ID AND
- TBL_DOMAIN_HOUR_STAT.HOST_ID = TBL_HOST.ID AND
- TBL_DOMAIN_HOUR_STAT.TIME >= '2019-09-01 00' AND
- TBL_DOMAIN_HOUR_STAT.TIME <= '2019-09-30 23'
- ORDER BY
- VM_DOMAIN_NAME ASC,
- TBL_DOMAIN_HOUR_STAT.TIME ASC) as total
- GROUP BY VM_DOMAIN_NAME;"""
- host_ip = '10.12.60.50'
- remote_path = '/tmp/vservice.sql'
- local_path = '/tmp/vservice.sql'
- cvm = 'root'
- cvmpassword = 'xxxxx'
-
-
- def remote_ssh():
- client = paramiko.SSHClient()
- client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
- client.connect(hostname=host_ip, port=22, username=cvm, password=cvmpassword)
- print("开始dump数据库请等待。。。")
- stdin, stdout, stderr = client.exec_command(
- '''rm -rf /tmp/vservice.sql*;mysqldump --extended-insert -uroot -p1q2w3e vservice > /tmp/vservice.sql''')
- print(stdout.read().decode('utf-8'))
- client.close()
- print("dump完成")
-
-
- def remote_scp():
- print("开始拷贝远程文件到本地")
- t = paramiko.Transport((host_ip, 22))
- t.connect(username=cvm, password=cvmpassword)
- sftp = paramiko.SFTPClient.from_transport(t)
- src = remote_path
- des = local_path
- sftp.get(src, des)
- t.close()
-
-
- def select_sql():
- conn = pymysql.connect(host='10.12.60.235', port=3306, user='root', passwd='xxxx', db='vservice', charset='utf8')
- cursor = conn.cursor()
- count = cursor.execute(sql)
- # 打印出总共条数
- print(count)
- # 重置游标位置,指定游标位置从最初开始
- cursor.scroll(0, mode='absolute')
- # 查询所有结果
- results = cursor.fetchall()
- # 获取mysql里面的数据字段名称
- fields = cursor.description
- workbook = xlwt.Workbook()
- sheet = workbook.add_sheet('test', cell_overwrite_ok=True)
- # 字段信息
- for field in range(0, len(fields)):
- sheet.write(0, field, fields[field][0])
- # 获取并写入数据库字段信息
- row = 1
- col = 0
- for row in range(1, len(results) + 1):
- for col in range(0, len(fields)):
- sheet.write(row, col, '%s' % results[row - 1][col])
- workbook.save('/tmp/虚拟机性能周报.xls')
- print('导出结束!总共导出:%d 条数据!' % count)
- print("数据保存在/tmp目录下,请自行下载")
- conn.close()
-
- def recov_db():
- print("开始恢复数据库,请耐心等待")
- conn = pymysql.connect(host='10.12.60.235', port=3306, user='root', passwd='xxxx', db='mysql', charset='utf8')
- cursor = conn.cursor()
- count1 = cursor.execute("drop database if exists vservice;")
- count3 = cursor.execute("create database if not exists vservice;")
- count4 = cursor.execute("use vservice;")
- conn.close()
- os.system("""mysql -uroot -pxxx, --one-database vservice < /tmp/vservice.sql""")
- print("数据库恢复完毕!")
-
- def main():
- # 远程登陆dump文件
- remote_ssh()
- # 拷贝文件到本地
- remote_scp()
- # 开始执行恢复数据库
- recov_db()
- # 开始输出表格
- select_sql()
-
-
- if __name__ == '__main__':
- main()
写的不好,请多指教。谢谢!
作者:放羊人
链接:https://www.pythonheidong.com/blog/article/147536/40d7e4ae269e5cf425b4/
来源:python黑洞网
任何形式的转载都请注明出处,如有侵权 一经发现 必将追究其法律责任
昵称:
评论内容:(最多支持255个字符)
---无人问津也好,技不如人也罢,你都要试着安静下来,去做自己该做的事,而不是让内心的烦躁、焦虑,坏掉你本来就不多的热情和定力
Copyright © 2018-2021 python黑洞网 All Rights Reserved 版权所有,并保留所有权利。 京ICP备18063182号-1
投诉与举报,广告合作请联系vgs_info@163.com或QQ3083709327
免责声明:网站文章均由用户上传,仅供读者学习交流使用,禁止用做商业用途。若文章涉及色情,反动,侵权等违法信息,请向我们举报,一经核实我们会立即删除!