Python production environment MySQL database Incremental backup script

Python production environment MySQL database Incremental backup script

A common method for MySQL databases is to use MySQLdump to export SQL for backup, but it is not suitable for databases with large data volumes. The speed and table lock are two serious problems. The previous article introduced the xtrabackup hot backup tool. The following script implements automatic Database Backup Based on xtrabackup.

Requirement Description:

Make a full backup of the database at every night. Incremental backup is performed hourly at the next day. Before each backup, move the last full backup and 23 incremental backups to the specified directory to keep the data for 7 days.

Ps: Don't ask me, why is the full backup performed at, and it cannot be better handled? Bingo: This is our business requirement. You know it. Don't ask me why. You don't need to use shell, or at least write subprocess. call to call system commands. I can only say, I'm happy, do you care? Haha!

#-*-Coding: UTF-8 -*-
#! /Usr/bin/python
#===================================================== ==================
# Author: gaochenchao-EMail: gccmx@163.com
# Last modified
# Filename: innobackup. py
# Description: backup mysql files, base percona xtrabackup
# Blog: gccmx.blog.51cto.com
#===================================================== ==================
Import datetime
Import subprocess
Import OS
Import sys
Import logging
Logging. basicConfig (level = logging. DEBUG,
Format = '% (asctime) s % (filename) s [line: % (lineno) d] % (levelname) s % (message) s ',
Datefmt = '% a, % d % B % Y % H: % M: % s ',
Filename = 'backup. log ',
Filemode = 'A ')
Backuser = 'bkuser'
Backpass = 'bk2015'
Basedir = '/mnt/backups'
Tomorrowdate = datetime. date. fromordinal (datetime. date. today (). toordinal () + 1). strftime ("% y % m % d ")
Todaydate = datetime. datetime. now (). strftime ("% y % m % d ")
Fullback_dir = "% s/% s" % (basedir, tomorrowdate)
Cuhour = datetime. datetime. now (). strftime ("% H ")
# Cuhour = sys. argv [1]
Increment_dir = '% s/% s' % (basedir, cuhour)
Increbase_dir =''
Stores = '/mnt/stores'

# Dump old backup data to the folder named after the current year, month, and day, for example, 150209-bak
Def storebefore ():
Suffix = datetime. datetime. now (). strftime ("% y % m % d ")
Storedir = "% s/% s-bak" % (stores, suffix)
If not OS. path. exists (storedir ):
Subprocess. call ("mkdir-p % s" % (storedir), shell = True)
Command = "cd % s & mv * % s" % (basedir, storedir)
Subprocess. call (command, shell = True)

# Delete backup data that exceeds 7 days in the dump directory
Def cleanstore ():
Command = "find % s-type d-mtime + 7 | xargs rm-fr" % stores
Subprocess. call (command, shell = True)

# Backup method: complete backup at every day, Incremental backup at the next day
Def backup ():
If not OS. path. exists (basedir ):
Subprocess. call ("mkdir-p % s" % basedir, shell = True)
Commandfull = "innobackupex -- user = % s -- password = % s -- no-timestamp % s" % (backuser, backpass, fullback_dir)
If cuhour = '23 ':
Storebefore ()
Subprocess. call ("rm-fr % s/*" % basedir, shell = True)
Subprocess. call (commandfull, shell = True)
Logging.info (commandfull)
Else:
If int (cuhour)-1> = 0:
Increbase_dir = '% s/% s' % (basedir, str (int (cuhour)-1 ))
Else:
Increbase_dir = "% s/% s" % (basedir, todaydate)
If not OS. path. exists (increbase_dir ):
Logging.info ('the last Incremental Backup Directory [% s] does not exist, stop executing '% increbase_dir)
Exit (0)
Commandinre = "innobackupex -- user = % s -- password = % s -- no-timestamp -- incremental % s -- incremental-basedir = % s" % (
Backuser, backpass, increment_dir, increbase_dir)
Subprocess. call (commandinre, shell = True)
Logging.info (commandincres)

If _ name _ = '_ main __':
Backup ()
Cleanstore ()

MySQL management-using XtraBackup for Hot Backup

MySQL open-source backup tool Xtrabackup backup deployment

MySQL Xtrabackup backup and recovery

Use XtraBackup to implement MySQL master-slave replication and quick deployment [master-slave table lock-free]

Install and use Xtrabackup from Percona to back up MySQL

XtraBackup details: click here
XtraBackup: click here

This article permanently updates the link address: