通过python脚本备份mysql数据库
方式1:直接用通过os模块调用mysqldump执行备份
创建脚本
vim mysql_data_bakup.py
编辑内容
#!/usr/bin/python3
# -*- coding: utf-8 -*-
import os
import time
import sys
# 设置变量
db_host = 'localhost'
db_user = 'root'
# 密码取执行时的第一个参数
db_user_passwd = sys.argv[1]
db_port = '3306'
db_name_list = ["数据库1名称","数据库2名称"]
data_path = '/mysqldata/'
date_time = time.strftime('%Y%m%d-%H%M%S')
# 备份函数
def mysql_bakup():
# 按db_name循环
for db_name in db_name_list:
# 拼接备份文件名
file_name = db_name + '_' + date_time + '.sql'
# 备份命令(如果mysqldump没有配置环境变量,则需要写完整的路径)
sql = '/usr/local/mysql/bin/mysqldump -h ' + db_host + ' -P' + db_port + ' -u' + db_user + ' -p' + db_user_passwd + ' --default-character-set=utf8 ' + db_name + ' > ' + os.path.join(data_path,file_name)
# 执行备份命令
os.system(sql)
# 等待几秒
time.sleep(5)
return
# 检查函数
def check_path():
# 判断路径是否存在
if os.path.exists(data_path):
# 清除3天之前的记录
cle = 'find ' + data_path + ' -mtime +3 -name "*.sql" -exec rm -Rf {} \;'
# 执行清除命令
os.system(cle)
# 调用备份函数
mysql_bakup()
else:
# 路径不存在则创建
os.mkdir(data_path)
# 调用备份函数
mysql_bakup()
return
if __name__ == '__main__':
check_path()
方式2:通过pymysql模块连接数据库导出表
安装pymysql模块
pip install pymysql
如果使用的是Python3可能需要使用pip3
pip3 install pymysql
创建脚本
vim mysql_table_bakup.py
编辑内容
#!/usr/bin/python3
# -*- coding: utf-8 -*-
import pymysql
import time
# 数据库连接配置
config = {
'host': 'localhost',
'port': 3306,
'user': 'root',
'password': '密码',
'database': 'mysql',
'charset': 'utf8mb4',
'cursorclass': pymysql.cursors.DictCursor
}
# 创建数据库连接
connection = pymysql.connect(**config)
try:
# 创建一个游标
with connection.cursor() as cursor:
# 查询所有表名
cursor.execute("SHOW TABLES")
# 获取结果集中的所有行
tables = cursor.fetchall()
# 轮询取出每行,mysql7.5中获取到表名是个字典(例如{‘Tables_in_mysql’:'user'})
for value in tables:
# 从config中取出数据库名称
database = config['database']
# 编辑字典的键
table_name_key = 'Tables_in_' + database
# 通过键取出值
table_name = value[table_name_key]
# 设置数据备份路径
data_path = '/mysqldata/'
# 获取当前时间
date_time = time.strftime('%Y%m%d-%H%M%S')
# 编辑备份文件名称
file_name = data_path + table_name + '_' + date_time + '.csv'
# 导出每张表的数据到指定位置(需要有导出表的数据库权限和写入文件的系统权限)
sql = 'select * into outfile ' +file_name + ' from '+ table_name + ';'
# 执行sql语句
cursor.execute(sql)
# 提交sql
connection.commit()
finally:
# 关闭数据库连接
connection.close()
备注
(1)关于两种方式的对比:
方式1是直接调用mysqldump工具,mysqldump工具的优点是应用简单,可以备份远程的库,导出的sql文件可以直接运行,数据恢复或迁移比较方便。缺点是mysqldump运行时会锁表,在此期间会限制数据的写入和修改,不太适合大规模或实时数据备份。
方式2是使用pymsql模块直接连接数据库执来行sql语句导出表,优点是无需调用系统命令,而且能够处理比较复杂的逻辑,可以实时备份。缺点同样是需要复杂的逻辑,并且是按表导出而不是整库导出(如果需要导出表结构则需要在此基础上再增加其他代码)。
如果在pymysql里面再调用mysqldump,同样无法避免锁表的问题,但是可以根据实际需求读取数据库的内容,并根据取得的内容来进行复杂的逻辑判断。
(2)关于try…finally…
try
和``finally语句块用于异常处理,
try块包含可能会引发异常的代码,
finally块包含无论是否发生异常都会执行的代码。
finally块主要用于清理资源,如关闭文件、断开数据库连接等,
finally`块将在try块执行完毕后执行,无论是否发生异常。
(3)关于with…as…
with
语句是用于简化资源管理的代码,确保资源在使用后能够被正确释放或关闭(即使处理文件时发发生了异常),as
关键字用于在 with
语句中给上下文管理器返回的对象指定一个别名,从而可以在 with
语句块内部使用这个别名来访问和操作资源。
(4)关于os.path
os.path
是路径管理模块,常用函数包括:
os.path.abspath(path)
:返回绝对路径,无论输入的是相对路径还是已有的绝对路径。os.path.basename(path)
:从路径中提取文件名或目录名。os.path.dirname(path)
:返回路径的父目录部分。os.path.exists(path)
:检测路径对应的文件或目录是否存在。os.path.lexists(path)
:类似于exists,但即使路径是一个失效的符号链接也会返回True。os.path.ismount(path)
:判断路径是否为挂载点。os.path.join(*paths)
:将多个路径片段合并成一个完整的路径。os.path.normcase(path)
:将路径转换为系统特定的标准大小写形式。
(5)关于报错:
unindent does not match any outer indentation level
原因:代码中存在不一致的缩进问题。这种错误通常发生在以下几种情况:混合使用空格和制表符、不正确的减少缩进、意外增加缩进等。
(6)关于报错:
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
原因:MySQL服务器启用了–secure-file-priv选项,这个选项限制了MySQL可以执行文件操作的目录。如果配置指定了文件目录,则只能在这个目录下执行文件的导入导出,如果结果为NULL,则说明没有设置这个选项,需要添加设置。
查询–secure-file-priv选项配置
SHOW VARIABLES LIKE 'secure_file_priv';
如果是没有配置,则在数据库的配置文件my.cnf中取消secure-file-priv的注释并设置为空,然后重启数据库。
修改内容如下:
[mysqld]
secure-file-priv=
(7)关于报错:
ERROR 1 (HY000): Can’t create/write to file ‘/var/1.csv’ (OS errno 13 - Permission denied)
原因:当前用户在操作系统上没有在这个位置创建文件的权限。
修改内容如下:
chmod +777 /mysqldata/
setenforce 0
yunxi p.deng 2024 06 22