devbox
Gausst松鼠会
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

Python 基于docker部署的Mysql备份查询脚本

作者：Gausst松鼠会 | 2024-04-22 08:05:49

赞

踩

Python 基于docker部署的Mysql备份查询脚本

前言

此环境是基于docker部署的mysql，docker部署mysql可以参考如下链接：

docker 部署服务案例-CSDN博客

颜色块文件

  1. root@bogon:~ 2024-04-18 16:34:23# cat DefaultColor.py
  2. #########################################################################
  3. #    File Name: DefaultColor.py
  4. #    Author: eight
  5. #    Mail: 18847097110@163.com 
  6. #    Created Time: Thu 11 Apr 2024 10:25:31 PM CST
  7. #########################################################################
  8. #!/usr/bin/env python
  9. # -*- coding: utf-8 -*-
  10. class Color:
  11.         END = '\033[0m'  # normal
  12.         BOLD = '\033[1m'  # bold
  13.         RED = '\033[1;91m'  # red
  14.         GREEN = '\033[1;92m'  # green
  15.         ORANGE = '\033[1;93m'  # orange
  16.         BLUE = '\033[1;94m'  # blue
  17.         PURPLE = '\033[1;95m'  # purple
  18.         UNDERLINE = '\033[4m'  # underline
  19.         CYAN = '\033[1;96m'  # cyan
  20.         GREY = '\033[1;97m'  # gray
  21.         BR = '\033[1;97;41m'  # background red
  22.         BG = '\033[1;97;42m'  # background green
  23.         BY = '\033[1;97;43m'  # background yellow

备份代码

  1.  
  2. #########################################################################
  3. #    File Name: query_mysql.py
  4. #    Author: eight
  5. #    Mail: 18847097110@163.com 
  6. #    Created Time: Thu 18 Apr 2024 11:12:26 AM CST
  7. #########################################################################
  8. #!/usr/bin/env python
  9. # -*- coding: utf-8 -*-
  10. import pymysql
  11. import configparser
  12. import os
  13. from datetime import datetime
  14. import DefaultColor
  15. import subprocess
  16.  
  17.  
  18. # 创建备份目录
  19. def mkdir():
  20.         dir_path = os.getcwd()
  21.         print("--------------------------------------------------------")
  22.         print("当前脚本工作目录：", dir_path)
  23.         mysql_backup_dir = "/opt/mysql_backup"
  24.         print("--------------------------------------------------------")
  25.         if not os.path.exists(mysql_backup_dir):
  26.                 os.makedirs(mysql_backup_dir)
  27.         else:
  28.                 return f"数据库备份目录 '{mysql_backup_dir}' 已经存在，跳过创建"
  29.         return "数据库备份目录：" + DefaultColor.Color.GREEN + mysql_backup_dir + DefaultColor.Color.END + " 创建成功"
  30.  
  31.  
  32.  
  33. # 备份数据库 
  34. def backup(mysql_backup_dir):
  35.         config = configparser.ConfigParser()
  36.         config.read("config.ini")
  37.         # 获取配置信息
  38.         host = config.get("MYSQL", "MYSQL_HOST")
  39.         username = config.get("MYSQL", "MYSQL_USER")
  40.         password = config.get("MYSQL", "MYSQL_PASSWORD")
  41.         port = config.get("MYSQL", "MYSQL_PORT")
  42.         db = config.get("MYSQL", "MYSQL_DB")
  43.         current_datetime = datetime.now()
  44.         formatted_datetime = current_datetime.strftime("%Y-%m-%d_%H-%M-%S")
  45.         try:
  46.                 command = f'docker exec -it mysql-container /bin/bash -c "mysqldump -h {host} -u {username} -p{password} -P {port} -- {db} > {mysql_backup_dir}-{db}-{formatted_datetime}.sql"'
  47.                 subprocess.run(command, shell=True, check=True)
  48.                 
  49.         except subprocess.CalledProcessError as e:
  50.                 print("Database backup failed:", e)
  51.         return f"数据库：{db}备份完成: {mysql_backup_dir}-{db}-{formatted_datetime}.sql"
  52.  
  53.  
  54.  
  55. if __name__ == '__main__':
  56.         backup_dir = mkdir()
  57.         print(backup_dir)
  58.         backup = backup("/opt/mysql_backup")
  59.         print(backup)

此代码如果是本地环境测试的话，逻辑是判断当前本地有没有存储备份数据的目录，没有则创建。

再利用subprocess执行mysqldump命令，按照日期生成.sql文件

效果

 因为是docker环境，所以判断当前目录是否存在，是判断的宿主机上的，所以此代码不是很严谨的代码，如果是本地部署的话，把mysqldump命令前面的  docker exec -it mysql-container /bin/bash -c  删除掉即可。

这样备份的脚本就实现了脚本预期效果：将数据库备份到指定的目录下，以日期区别sql文件

全量代码（读取配置和备份和查询）

  1. root@bogon:~ 2024-04-18 16:30:14# cat MysqlBackup.py 
  2. #########################################################################
  3. #    File Name: query_mysql.py
  4. #    Author: eight
  5. #    Mail: 18847097110@163.com 
  6. #    Created Time: Thu 18 Apr 2024 11:12:26 AM CST
  7. #########################################################################
  8. #!/usr/bin/env python
  9. # -*- coding: utf-8 -*-
  10. import pymysql
  11. import configparser
  12. import os
  13. from datetime import datetime
  14. import DefaultColor
  15. import subprocess
  16.  
  17. # 读取mysql配置文件
  18. def mysql_config():
  19.         config = configparser.ConfigParser()
  20.         config.read("config.ini")
  21.  
  22.         # 获取配置信息
  23.         host = config.get("MYSQL", "MYSQL_HOST")
  24.         username = config.get("MYSQL", "MYSQL_USER")
  25.         password = config.get("MYSQL", "MYSQL_PASSWORD")
  26.         port = config.get("MYSQL", "MYSQL_PORT")
  27.         db = config.get("MYSQL", "MYSQL_DB")
  28.         print("--------------------------------------------------------")
  29.         return f"主机：{host}\t用户名:{username}\t密码:{password}\t端口:{port}\t数据库:{db}"
  30.  
  31.  
  32. # 创建备份目录
  33. def mkdir():
  34.         dir_path = os.getcwd()
  35.         print("--------------------------------------------------------")
  36.         print("当前脚本工作目录：", dir_path)
  37.         mysql_backup_dir = "/opt/mysql_backup"
  38.         print("--------------------------------------------------------")
  39.         if not os.path.exists(mysql_backup_dir):
  40.                 os.makedirs(mysql_backup_dir)
  41.         else:
  42.                 return f"数据库备份目录 '{mysql_backup_dir}' 已经存在，跳过创建"
  43.         return "数据库备份目录：" + DefaultColor.Color.GREEN + mysql_backup_dir + DefaultColor.Color.END + " 创建成功"
  44.  
  45.  
  46. # 查询sql语句
  47. ##def select():
  48. #       config = configparser.ConfigParser()
  49. #       config.read("config.ini")
  50. #                    
  51. #       # 获取配置信息
  52. #       host = config.get("MYSQL", "MYSQL_HOST")
  53. #       username = config.get("MYSQL", "MYSQL_USER")
  54. #       password = config.get("MYSQL", "MYSQL_PASSWORD")
  55. #       port = config.get("MYSQL", "MYSQL_PORT")
  56. #       db = config.get("MYSQL", "MYSQL_DB")
  57. #       # 创建mysql连接信息
  58. #       conn = pymysql.connect(host=host, user=username, password=password, port=int(port), db=db)
  59. #       cur = conn.cursor()
  60. #       # 执行查询语句
  61. #       cur.execute("use docker;")
  62. #       cur.execute("select * from DockerImages;")
  63. #       print("查询到的数据是：")
  64. #       # 接收全部的返回结果行
  65. #       result = cur.fetchall()
  66. #       while True:
  67. #               resp = cur.fetchone()
  68. #               if resp is None:
  69. #                       break
  70. #       # 关闭查询游标
  71. #       cur.close()
  72. #       # 提交
  73. #       conn.commit()
  74. #       # 关闭链接，释放计算机资源
  75. #       conn.close()
  76. #       return result
  77.  
  78.  
  79. # 备份数据库 
  80. def backup(mysql_backup_dir):
  81.         config = configparser.ConfigParser()
  82.         config.read("config.ini")
  83.         # 获取配置信息
  84.         host = config.get("MYSQL", "MYSQL_HOST")
  85.         username = config.get("MYSQL", "MYSQL_USER")
  86.         password = config.get("MYSQL", "MYSQL_PASSWORD")
  87.         port = config.get("MYSQL", "MYSQL_PORT")
  88.         db = config.get("MYSQL", "MYSQL_DB")
  89.         current_datetime = datetime.now()
  90.         formatted_datetime = current_datetime.strftime("%Y-%m-%d_%H-%M-%S")
  91.         try:
  92.                 command = f'docker exec -it mysql-container /bin/bash -c "mysqldump -h {host} -u {username} -p{password} -P {port} -- {db} > {mysql_backup_dir}-{db}-{formatted_datetime}.sql"'
  93.                 subprocess.run(command, shell=True, check=True)
  94.                 
  95.         except subprocess.CalledProcessError as e:
  96.                 print("Database backup failed:", e)
  97.         return f"数据库：{db}备份完成: {mysql_backup_dir}-{db}-{formatted_datetime}.sql"
  98.  
  99.  
  100.  
  101. if __name__ == '__main__':
  102.         #mysql_config = mysql_config()
  103.         #print(mysql_config)
  104.         backup_dir = mkdir()
  105.         print(backup_dir)
  106.  
  107.         #sector = select()
  108.         # 循环打印所有数据，如果此代码是封装在函数内部，那么使用return的话，只会输出第一条数据，所以要在外部调用
  109.         #for row in sector:
  110.         #       print(row)
  111.         backup = backup("/opt/mysql_backup")
  112.         print(backup)
  113. root@bogon:~ 2024-04-18 16:30:18# 
  114. root@bogon:~ 2024-04-18 16:30:32# 
  115. root@bogon:~ 2024-04-18 16:30:32# cat MysqlBackup.py 
  116. #########################################################################
  117. #    File Name: query_mysql.py
  118. #    Author: eight
  119. #    Mail: 18847097110@163.com 
  120. #    Created Time: Thu 18 Apr 2024 11:12:26 AM CST
  121. #########################################################################
  122. #!/usr/bin/env python
  123. # -*- coding: utf-8 -*-
  124. import pymysql
  125. import configparser
  126. import os
  127. from datetime import datetime
  128. import DefaultColor
  129. import subprocess
  130.  
  131. # 读取mysql配置文件
  132. def mysql_config():
  133.         config = configparser.ConfigParser()
  134.         config.read("config.ini")
  135.  
  136.         # 获取配置信息
  137.         host = config.get("MYSQL", "MYSQL_HOST")
  138.         username = config.get("MYSQL", "MYSQL_USER")
  139.         password = config.get("MYSQL", "MYSQL_PASSWORD")
  140.         port = config.get("MYSQL", "MYSQL_PORT")
  141.         db = config.get("MYSQL", "MYSQL_DB")
  142.         print("--------------------------------------------------------")
  143.         return f"主机：{host}\t用户名:{username}\t密码:{password}\t端口:{port}\t数据库:{db}"
  144.  
  145.  
  146. # 创建备份目录
  147. def mkdir():
  148.         dir_path = os.getcwd()
  149.         print("--------------------------------------------------------")
  150.         print("当前脚本工作目录：", dir_path)
  151.         mysql_backup_dir = "/opt/mysql_backup"
  152.         print("--------------------------------------------------------")
  153.         if not os.path.exists(mysql_backup_dir):
  154.                 os.makedirs(mysql_backup_dir)
  155.         else:
  156.                 return f"数据库备份目录 '{mysql_backup_dir}' 已经存在，跳过创建"
  157.         return "数据库备份目录：" + DefaultColor.Color.GREEN + mysql_backup_dir + DefaultColor.Color.END + " 创建成功"
  158.  
  159.  
  160. # 查询sql语句
  161. def select():
  162.         config = configparser.ConfigParser()
  163.         config.read("config.ini")
  164.                      
  165.         # 获取配置信息
  166.         host = config.get("MYSQL", "MYSQL_HOST")
  167.         username = config.get("MYSQL", "MYSQL_USER")
  168.         password = config.get("MYSQL", "MYSQL_PASSWORD")
  169.         port = config.get("MYSQL", "MYSQL_PORT")
  170.         db = config.get("MYSQL", "MYSQL_DB")
  171.         # 创建mysql连接信息
  172.         conn = pymysql.connect(host=host, user=username, password=password, port=int(port), db=db)
  173.         cur = conn.cursor()
  174.         # 执行查询语句
  175.         cur.execute("use docker;")
  176.         cur.execute("select * from DockerImages;")
  177.         print("查询到的数据是：")
  178.         # 接收全部的返回结果行
  179.         result = cur.fetchall()
  180.         while True:
  181.                 resp = cur.fetchone()
  182.                 if resp is None:
  183.                         break
  184.         # 关闭查询游标
  185.         cur.close()
  186.         # 提交
  187.         conn.commit()
  188.         # 关闭链接，释放计算机资源
  189.         conn.close()
  190.         return result
  191.  
  192.  
  193. # 备份数据库 
  194. def backup(mysql_backup_dir):
  195.         config = configparser.ConfigParser()
  196.         config.read("config.ini")
  197.         # 获取配置信息
  198.         host = config.get("MYSQL", "MYSQL_HOST")
  199.         username = config.get("MYSQL", "MYSQL_USER")
  200.         password = config.get("MYSQL", "MYSQL_PASSWORD")
  201.         port = config.get("MYSQL", "MYSQL_PORT")
  202.         db = config.get("MYSQL", "MYSQL_DB")
  203.         current_datetime = datetime.now()
  204.         formatted_datetime = current_datetime.strftime("%Y-%m-%d_%H-%M-%S")
  205.         try:
  206.                 command = f'docker exec -it mysql-container /bin/bash -c "mysqldump -h {host} -u {username} -p{password} -P {port} -- {db} > {mysql_backup_dir}-{db}-{formatted_datetime}.sql"'
  207.                 subprocess.run(command, shell=True, check=True)
  208.                 
  209.         except subprocess.CalledProcessError as e:
  210.                 print("Database backup failed:", e)
  211.         return f"数据库：{db}备份完成: {mysql_backup_dir}-{db}-{formatted_datetime}.sql"
  212.  
  213.  
  214.  
  215. if __name__ == '__main__':
  216.         mysql_config = mysql_config()
  217.         print(mysql_config)
  218.         backup_dir = mkdir()
  219.         print(backup_dir)
  220.  
  221.         sector = select()
  222.         #循环打印所有数据，如果此代码是封装在函数内部，那么使用return的话，只会输出第一条数据，所以要在外部调用
  223.         for row in sector:
  224.                 print(row)
  225.         backup = backup("/opt/mysql_backup")
  226.         print(backup)

效果

优化

  1. #########################################################################
  2. #    File Name: query_mysql.py
  3. #    Author: eight
  4. #    Mail: 18847097110@163.com 
  5. #    Created Time: Thu 18 Apr 2024 11:12:26 AM CST
  6. #########################################################################
  7. #!/usr/bin/env python
  8. # -*- coding: utf-8 -*-
  9. import pymysql
  10. import configparser
  11. import os
  12. from datetime import datetime
  13. import DefaultColor
  14. import subprocess
  15.  
  16. # 读取mysql配置文件
  17. def mysql_config():
  18. 	config = configparser.ConfigParser()
  19. 	config.read("config.ini")
  20. 	# 获取配置信息
  21. 	host = config.get("MYSQL", "MYSQL_HOST")
  22. 	username = config.get("MYSQL", "MYSQL_USER")
  23. 	password = config.get("MYSQL", "MYSQL_PASSWORD")
  24. 	port = config.get("MYSQL", "MYSQL_PORT")
  25. 	db = config.get("MYSQL", "MYSQL_DB")
  26. 	print("--------------------------------------------------------")
  27. 	return {
  28. 			"host": host,
  29. 			"username": username,
  30. 			"password": password,
  31. 			"port": port,
  32. 			"db": db
  33. 			}
  34.  
  35. 	
  36. # 创建备份目录
  37. def mkdir():
  38. 	dir_path = os.getcwd()
  39. 	print("--------------------------------------------------------")
  40. 	print("当前脚本工作目录：", dir_path)
  41. 	mysql_backup_dir = "/opt/mysql_backup"
  42. 	print("--------------------------------------------------------")
  43. 	if not os.path.exists(mysql_backup_dir):
  44. 		os.makedirs(mysql_backup_dir)
  45. 	else:
  46. 		return f"数据库备份目录 '{mysql_backup_dir}' 已经存在，跳过创建"
  47. 	return "数据库备份目录：" + DefaultColor.Color.GREEN + mysql_backup_dir + DefaultColor.Color.END + " 创建成功"
  48.  
  49.  
  50. # 查询sql语句
  51. def select():
  52. 	mysql_connect_info = mysql_config()
  53. 	# 创建mysql连接信息 
  54. 	conn = pymysql.connect(host=mysql_connect_info["host"],
  55. 							user=mysql_connect_info["username"],
  56. 							password=mysql_connect_info["password"],
  57. 							port=int(mysql_connect_info["port"]),
  58. 							db=mysql_connect_info["db"])																													
  59. 	cur = conn.cursor()
  60. 	# 执行查询语句
  61. 	cur.execute("use docker;")
  62. 	cur.execute("select * from DockerImages;")
  63. 	print("查询到的数据是：")
  64. 	# 接收全部的返回结果行
  65. 	result = cur.fetchall()
  66. 	while True:
  67. 		resp = cur.fetchone()
  68. 		if resp is None:
  69. 			break
  70. 	# 关闭查询游标
  71. 	cur.close()
  72. 	# 提交
  73. 	conn.commit()
  74. 	# 关闭链接，释放计算机资源
  75. 	conn.close()
  76. 	return result
  77.  
  78.  
  79. # 备份数据库 
  80. def backup(mysql_backup_dir):
  81. 	# 获取配置信息
  82. 	current_datetime = datetime.now()
  83. 	formatted_datetime = current_datetime.strftime("%Y-%m-%d_%H-%M-%S")
  84. 	mysql_connect_info = mysql_config()
  85. 	try:
  86. 		command = f'docker exec -it mysql-container /bin/bash -c "mysqldump -h {mysql_connect_info["host"]} -u {mysql_connect_info["username"]} -p{mysql_connect_info["password"]} -P {mysql_connect_info["port"]} -- {mysql_connect_info["db"]} > {mysql_backup_dir}-{mysql_connect_info["db"]}-{formatted_datetime}.sql"'
  87. 		subprocess.run(command, shell=True, check=True)
  88. 		
  89. 	except subprocess.CalledProcessError as e:
  90. 		print("Database backup failed:", e)
  91. 	return f'数据库：{mysql_connect_info["db"]}备份完成: {mysql_backup_dir}-{mysql_connect_info["db"]}-{formatted_datetime}.sql'
  92.  
  93.  
  94.  
  95. if __name__ == '__main__':
  96. 	backup_dir = mkdir()
  97. 	print(backup_dir)
  98.  
  99. 	sector = select()
  100. 	#循环打印所有数据，如果此代码是封装在函数内部，那么使用return的话，只会输出第一条数据，所以要在外部调用
  101. 	for row in sector:
  102. 		print(row)
  103. 	backup = backup("/opt/mysql_backup")
  104. 	print(backup)

将配置文件信息以字典的方式返回

在查询函数时，使用相应的键来访问这些值

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Gausst松鼠会/article/detail/467616
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号