docker上定期备份mysql数据库

docker备份数据指令脚本python脚本shell脚本

docker备份数据指令

container_name：容器名称，此处也可填容器ID。

db_user：数据库账号。

db_password：数据库密码。

database：要备份的数据库。

file_path：备份出来的文件名。

docker exec -it {container_name} mysqldump -u{db_user} -p{db_password} {database} > {file_path}

脚本

python脚本

1.能指定要备份的数据库

2.将备份出来的SQL文件打包成压缩文件，并以一定的规范来命名，比如：mysqlname_-01-20-20.zip，mysqlname是前缀，-01-20-20代表是01月20日20点的时候备份的

3.定期删除5个小时或10个小时之前的备份文件

#!/usr/bin/env python# encoding: utf-8import datetimeimport osimport shutilimport subprocessimport timeimport zipfile# 数据库用户名db_user = "root"# 数据库密码db_password = "123456"# 备份目录backup_dir = "/var/test_backup"# backup_prefix和backup_suffix分别为备份文件的前缀和后缀，如test_backup_-09-19-11则代表该文件是在9月19日的11点时备份的backup_prefix = "test_backup"backup_suffix = "%Y-%m-%d-%H"# 备份数据库列表backup_databases = ["test1","test2",]# 容器名container_name = "mysql-test"# 过期小时，定期删除5个小时前的备份文件expire_hour = 5# 获取备份文件名def get_backup_filename():t = time.strftime(backup_suffix, time.localtime())return "%s_%s" % (backup_prefix, t)def get_backup_path():return "%s%s%s" % (backup_dir, os.sep, get_backup_filename())# 获取过期时间戳def get_expire_time():t = datetime.datetime.now() - datetime.timedelta(hours=expire_hour)return int(time.mktime(t.timetuple()))def create_dir(dir_path):# 如果目录存在则退出if os.path.exists(dir_path):returnos.mkdir(dir_path)cmd_template = "docker exec -it {container_name} mysqldump -u{db_user} -p{db_password} {database} > {file_path}"# 备份指定数据库def backup_database(backup_path, database):file_path = os.sep.join([backup_path, "%s.sql" % database])d = {"container_name": container_name,"db_user": db_user,"db_password": db_password,"database": database,"file_path": file_path,}cmd = cmd_template.format(**d)subprocess.call(cmd, shell=True)def zip_dir(dir_path):file_path = '.'.join([dir_path, "zip"])if os.path.exists(file_path):os.remove(file_path)z = zipfile.ZipFile(file_path, 'w', zipfile.ZIP_DEFLATED)for root, directories, files in os.walk(dir_path):fpath = root.replace(dir_path, '')fpath = fpath and fpath + os.sep or ''for filename in files:z.write(os.path.join(root, filename), fpath + filename)z.close()# 备份数据库def backup():backup_path = get_backup_path()try:create_dir(backup_path)for database in backup_databases:backup_database(backup_path, database)zip_dir(backup_path)finally:shutil.rmtree(backup_path)# 清理过期备份文件def clean():expire_time = get_expire_time()for root, directories, files in os.walk(backup_dir):for file in files:if not file.startswith(backup_prefix):continueif not file.endswith(".zip"):continuefile_path = os.sep.join([root, file])t = os.path.getctime(file_path)if t < expire_time:os.remove(file_path)if __name__ == "__main__":try:backup()finally:clean()

shell脚本

#!/bin/bash# 设置mysql的登录用户名和密码(根据实际情况填写)mysql_user="root"mysql_password="root"mysql_host="localhost"mysql_port="3306"mysql_charset="utf8mb4"# 备份文件存放地址(根据实际情况填写)backup_location=/usr/local# 是否删除过期数据expire_backup_delete="ON"expire_days=7backup_time=`date +%Y%m%d%H%M`backup_dir=$backup_locationwelcome_msg="Welcome to use MySQL backup tools!"# 备份指定数据库中数据(此处假设数据库是mysql_backup_test)docker exec -it mysql mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -B test1 > $backup_dir/mysql_backup_test-$backup_time.sql# 删除过期数据if [ "$expire_backup_delete" == "ON" -a "$backup_location" != "" ];then`find $backup_location/ -type f -mtime +$expire_days | xargs rm -rf`echo "Expired backup data delete complete!"fi