mysql 8.0 自动备份_基于mysql8.0的数据库备份脚本

import pandas as pd,os,math,gc,osfrom sqlalchemy import create_engine# mysqlhost = 'localhost'user = 'root'password = '123456'db = 'source_macro'port = 3306charset = 'utf8'engine = create_engine('mysql+pymysql://{user}:{password}@{host}:{port}/{db}?charset={charset}'

.format(user = user,

host = host,

password = password,

db = db,

port = port,

charset = charset),

pool_size = 30,max_overflow = 0)# 创建直接调用数据库属性CONN = engine.connect()def read_table_list():

'''

读取mysql中所有库名与表名:return: DF

'''

return pd.read_sql('SELECT

`TABLE_SCHEMA`,`TABLE_NAME`,`TABLE_COMMENT` FROM

`information_schema`.`TABLES` WHERE `TABLE_SCHEMA` NOT IN

("information_schema","mysql","performance_schema","sys","source_regdb")',CONN)def create_folder(folder_dir,folder_name):

'''

创建文件夹:param folder_dir: 文件夹路径:param folder_name: 文件夹名称:return: None

'''

if not(os.path.exists(folder_dir + '/' + folder_name)):

os.makedirs(folder_dir + '/' + folder_name)

print(folder_dir + '/' + folder_name + '创建成功')

else:print(folder_dir + '/' + folder_name + '已经存在')def backup_table(schema,table,folder_dir,file_type = 'csv'):

'''

备份数据:param schema: 数据库名:param table: 表名:param folder_dir: 文件夹路径:param file_type: 备份数据格式类型(csv,txt,xlsx)三种:return: None

'''

temp_table = pd.read_sql('select * from {}.{}'.format(schema,table),CONN)

if file_type == 'csv':

temp_table.to_csv(folder_dir + '/' + schema + '/' + table + '.csv',index=False)

elif file_type == 'txt':

temp_table.to_csv(folder_dir + '/' + schema + '/' + table + '.txt', index=False)

elif file_type == 'xlsx':

temp_table.to_excel(folder_dir + '/' + schema + '/' + table + '.xlsx', index=False)

gc.collect()

print(table + ' 数据备份完成')def backup_construction(schema,table,folder_dir):

'''

备份结构:param schema: 数据库名:param table: 表名:param folder_dir: 文件夹路径:return: None

'''

temp_construction = pd.read_sql('SHOW CREATE TABLE {}.{}'.format(schema,table),CONN)['Create Table'].values[0]

construction_file = open(folder_dir + '/' + schema + '/' + table + '.sql','w')

construction_file.write(temp_construction)

construction_file.close()

print(table + ' 结构备份完成')def backup_proc(schema,folder_dir):

# schema = 'support_technology'

# folder_dir = r'\\10.253.114.71\备份文件\函数备份'

proc_list = pd.read_sql('SELECT ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE

FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA =

"{}";'.format(schema), CONN)

if proc_list.empty:return None

for index,row in proc_list.iterrows():

temp_proc = pd.read_sql('show create {proc_type}

{db}.`{proc_name}`;'.format(db=row[0], proc_name=row[1], proc_type=row[2]),

CONN)['Create {}'.format(row[2].title())][0]

proc_file = open(folder_dir + '/' + schema + '/' + row[1] + '.sql','w')

proc_file.write(temp_proc)

proc_file.close()def main(schema,table,folder_dir,file_type = 'csv'):

'''

主函数:param schema: 数据库名:param table: 表名:param folder_dir: 文件夹路径:param file_type: 备份数据格式类型(csv,txt,xlsx)三种:return: None

'''

backup_table(schema, table, folder_dir,file_type)

backup_construction(schema, table, folder_dir)if __name__ == '__main__':

table_list = read_table_list()

folder_dir =r'\\10.253.114.71\备份文件\数据备份'

folder_dir_1 = r'\\10.253.114.71\备份文件\结构备份'

folder_dir_2 = r'\\10.253.114.71\备份文件\函数备份'

for i in set(list(table_list['TABLE_SCHEMA'])):

create_folder(folder_dir,i)

create_folder(folder_dir_1, i)

create_folder(folder_dir_2, i)

# table_list.apply(lambda

x:main(x[0],x[1],folder_dir),axis = 1) # 数据与结构备份到同一文件夹内

table_list.apply(lambda x: backup_table(x[0], x[1], folder_dir), axis=1) # 数据与结构备份到不同文件夹内

table_list.apply(lambda x: backup_construction(x[0], x[1], folder_dir_1), axis=1) # 数据与结构备份到不同文件夹内

table_list.apply(lambda x: backup_proc(x[0], folder_dir_2), axis=1)