+关注
已关注

分类  

暂无分类

标签  

暂无标签

日期归档  

2019-04(2)

2019-06(1)

2019-07(4)

2019-08(91)

2019-09(106)

脚本小子-------使用python脚本完成mysql数据库备份、恢复、查询并生成excel

发布于2019-10-29 16:02     阅读(936)     评论(0)     点赞(2)     收藏(3)


主要使用到的模块:pymysql、xlwt、paramiko、os 。

废话不多说,直接贴代码。

  1. # conding=utf-8
  2. # Version:python3.7
  3. # Tools:Pycharm 2019.2.1
  4. __date__ = "2019/10/26 上午9:29"
  5. __author__ = "HANHAN"
  6. import pymysql
  7. import xlwt
  8. import paramiko
  9. import os, datetime
  10. 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
  11. (SELECT
  12. TBL_DOMAIN_HOUR_STAT.ID,
  13. TBL_DOMAIN_HOUR_STAT.CLUSTER_ID,
  14. TBL_HOST.`NAME` AS CVK_HOSTNAME,
  15. TBL_DOMAIN.TITLE AS VM_DOMAIN_NAME,
  16. TBL_DOMAIN_HOUR_STAT.TIME,
  17. DATE_FORMAT(TBL_DOMAIN_HOUR_STAT.TIME,'%H:%i') AS Hour_Stamp,
  18. DATE_FORMAT(TBL_DOMAIN_HOUR_STAT.TIME,'%Y-%m-%d %H:%i') AS Time_Stamp,
  19. TBL_DOMAIN_HOUR_STAT.CPU AS CPU_AVERAGE_RATE,
  20. TBL_DOMAIN_HOUR_STAT.CPU_MAX,
  21. TBL_DOMAIN_HOUR_STAT.CPU_MIN,
  22. TBL_DOMAIN_HOUR_STAT.MEMORY AS MEMORY_AVERAGE_RATE,
  23. TBL_DOMAIN_HOUR_STAT.MEMORY_MAX,
  24. TBL_DOMAIN_HOUR_STAT.MEMORY_MIN
  25. FROM
  26. TBL_DOMAIN_HOUR_STAT ,
  27. TBL_DOMAIN ,
  28. TBL_HOST
  29. WHERE
  30. TBL_DOMAIN.CASTOOLS_STATUS ='1' AND
  31. TBL_DOMAIN_HOUR_STAT.DOMAIN_ID = TBL_DOMAIN.ID AND
  32. TBL_DOMAIN_HOUR_STAT.HOST_ID = TBL_HOST.ID AND
  33. TBL_DOMAIN_HOUR_STAT.TIME >= '2019-09-01 00' AND
  34. TBL_DOMAIN_HOUR_STAT.TIME <= '2019-09-30 23'
  35. ORDER BY
  36. VM_DOMAIN_NAME ASC,
  37. TBL_DOMAIN_HOUR_STAT.TIME ASC) as total
  38. GROUP BY VM_DOMAIN_NAME;"""
  39. host_ip = '10.12.60.50'
  40. remote_path = '/tmp/vservice.sql'
  41. local_path = '/tmp/vservice.sql'
  42. cvm = 'root'
  43. cvmpassword = 'xxxxx'
  44. def remote_ssh():
  45. client = paramiko.SSHClient()
  46. client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
  47. client.connect(hostname=host_ip, port=22, username=cvm, password=cvmpassword)
  48. print("开始dump数据库请等待。。。")
  49. stdin, stdout, stderr = client.exec_command(
  50. '''rm -rf /tmp/vservice.sql*;mysqldump --extended-insert -uroot -p1q2w3e vservice > /tmp/vservice.sql''')
  51. print(stdout.read().decode('utf-8'))
  52. client.close()
  53. print("dump完成")
  54. def remote_scp():
  55. print("开始拷贝远程文件到本地")
  56. t = paramiko.Transport((host_ip, 22))
  57. t.connect(username=cvm, password=cvmpassword)
  58. sftp = paramiko.SFTPClient.from_transport(t)
  59. src = remote_path
  60. des = local_path
  61. sftp.get(src, des)
  62. t.close()
  63. def select_sql():
  64. conn = pymysql.connect(host='10.12.60.235', port=3306, user='root', passwd='xxxx', db='vservice', charset='utf8')
  65. cursor = conn.cursor()
  66. count = cursor.execute(sql)
  67. # 打印出总共条数
  68. print(count)
  69. # 重置游标位置,指定游标位置从最初开始
  70. cursor.scroll(0, mode='absolute')
  71. # 查询所有结果
  72. results = cursor.fetchall()
  73. # 获取mysql里面的数据字段名称
  74. fields = cursor.description
  75. workbook = xlwt.Workbook()
  76. sheet = workbook.add_sheet('test', cell_overwrite_ok=True)
  77. # 字段信息
  78. for field in range(0, len(fields)):
  79. sheet.write(0, field, fields[field][0])
  80. # 获取并写入数据库字段信息
  81. row = 1
  82. col = 0
  83. for row in range(1, len(results) + 1):
  84. for col in range(0, len(fields)):
  85. sheet.write(row, col, '%s' % results[row - 1][col])
  86. workbook.save('/tmp/虚拟机性能周报.xls')
  87. print('导出结束!总共导出:%d 条数据!' % count)
  88. print("数据保存在/tmp目录下,请自行下载")
  89. conn.close()
  90. def recov_db():
  91. print("开始恢复数据库,请耐心等待")
  92. conn = pymysql.connect(host='10.12.60.235', port=3306, user='root', passwd='xxxx', db='mysql', charset='utf8')
  93. cursor = conn.cursor()
  94. count1 = cursor.execute("drop database if exists vservice;")
  95. count3 = cursor.execute("create database if not exists vservice;")
  96. count4 = cursor.execute("use vservice;")
  97. conn.close()
  98. os.system("""mysql -uroot -pxxx, --one-database vservice < /tmp/vservice.sql""")
  99. print("数据库恢复完毕!")
  100. def main():
  101. # 远程登陆dump文件
  102. remote_ssh()
  103. # 拷贝文件到本地
  104. remote_scp()
  105. # 开始执行恢复数据库
  106. recov_db()
  107. # 开始输出表格
  108. select_sql()
  109. if __name__ == '__main__':
  110. main()

写的不好,请多指教。谢谢!



所属网站分类: 技术文章 > 博客

作者:放羊人

链接: https://www.pythonheidong.com/blog/article/147536/

来源: python黑洞网

任何形式的转载都请注明出处,如有侵权 一经发现 必将追究其法律责任

2 0
收藏该文
已收藏

评论内容:(最多支持255个字符)