В дополнение к скриптуPython: скрипт инкрементального или полного бекапа файлов — второй, который выполняет полное копирование баз данных.
В целях «совместимости» с первым скриптом — данные так же хранятся в отдельных daily и weekly директориях, хотя в отличии от файлов тут не выполняется инкрементальный бекап — базы небольшие, а после сжатия bzip2 совсем маленькие:
# ls -l /home/setevoy/backups/setevoy/daily/ total 16 drwxr-xr-x 2 root root 4096 Oct 13 23:15 2014-10-13-files drwxr-xr-x 2 root root 4096 Oct 14 04:00 2014-10-14-files drwxr-xr-x 2 root root 4096 Oct 15 17:49 2014-10-15-database drwxr-xr-x 2 root root 4096 Oct 15 08:44 2014-10-15-files
Скрипт выполняет «обход» по каждой базе, к которой есть доступ у пользователя (за исключением баз, перечисленных в переменной EXCLUDE):
# ls -lh /home/setevoy/backups/setevoy/weekly/2014-10-15-database/ total 3.1M -rw-r--r-- 1 root root 805 Oct 15 18:02 autocomtestdb.sql.bz2 -rw-r--r-- 1 root root 417 Oct 15 18:02 mysqlslap.sql.bz2 -rw-r--r-- 1 root root 4.9K Oct 15 18:02 old_money_db1.sql.bz2 -rw-r--r-- 1 root root 3.0M Oct 15 18:02 setevoy_forum.sql.bz2 -rw-r--r-- 1 root root 5.1K Oct 15 18:02 setevoy_money_db1.sql.bz2 -rw-r--r-- 1 root root 89K Oct 15 18:02 setevoyorgua_db1.sql.bz2 -rw-r--r-- 1 root root 843 Oct 15 18:02 setevoy_test.sql.bz2 -rw-r--r-- 1 root root 1.8K Oct 15 18:02 themega_chars.sql.bz2
MariaDB [(none)]> select user, db from mysql.db; +-------------+-------------------+ | user | db | +-------------+-------------------+ | setevoy | setevoy_test | | setevoy | autocomtestdb | ... | vexim | vexim | | worlddesign | worlddesign_db1 | | zabbix | zabbix | +-------------+-------------------+ 16 rows in set (0.00 sec)
К примеру, база RTFM занимает:
# du -sh /var/lib/mysql/rtfm_db1/ 47M /var/lib/mysql/rtfm_db1/
А её бекап после сжатия:
# ls -lh ../backups/rtfm/daily/2014-10-16-database/ total 3.2M -rw-r--r-- 1 root root 3.2M Oct 16 03:00 rtfm_db1.sql.bz2
Так же, тут нет функции для удаления старых каталогов с данными — этим занимается функция clear_old_dirs() из скрипта files_backup.py:
clear_old_dirs(user, day, count, host, bkptype)
Deletes old directories. Depending on [count] — can store 7
(for daily) or 4 (for weekly) copies, including today’s archive.
Поэтому, скрипт запускается по cron-у перед выполнением бекапа файлов:
# crontab -l ... 0 03 * * * /home/setevoy/opt/mysql_backup.py 0 04 * * * /home/setevoy/opt/files_backup.py
Лог выглядит так:
mysql_backup.py - INFO [2014-10-15 18:29:47,028] Backup started at: 2014-10-15 18:29:47 mysql_backup.py - INFO [2014-10-15 18:29:47,041] Today is Wed, will use backuptype daily. mysql_backup.py - INFO [2014-10-15 18:29:47,042] Directory /home/setevoy/backups/setevoy/daily/2014-10-15-database/ created mysql_backup.py - INFO [2014-10-15 18:29:47,055] Database backup complete: database setevoy_test saved to /home/setevoy/backups/setevoy/daily/2014-10-15-database/setevoy_test.sql.bz2 mysql_backup.py - INFO [2014-10-15 18:29:47,060] Database mysqlslap in exclude list, skip ... mysql_backup.py - INFO [2014-10-15 18:29:47,068] Backup /home/setevoy/backups/hudeem/daily/2014-10-15-database/hudeem_db.sql.bz2 already present, skip .... mysql_backup.py - INFO [2014-10-15 18:29:57,337] Email sent from ['[email protected]'] to ['[email protected]']. mysql_backup.py - INFO [2014-10-15 18:29:57,337] Backup finished at: 2014-10-15 18:29:57
И сам скрипт:
#!/usr/bin/env python
'''
Create full backup every user's database.
Creation date: 15.10.2014
Last modify date: 16.10.2014
'''
import MySQLdb
import socket
import os
import logging
import time
import subprocess
import sys
import smtplib
import mimetypes
import email
import email.mime.application
import socket
BACKDIR = '/home/setevoy/backups/'
LOGPATH = '/var/log/database_backup.log'
DBROOT = 'root'
_DBROOTPW = 'PasswordHere'
DBHOST = 'localhost'
# exclude databases list - will not be added to backup
EXCLUDE = 'mysqlslap'
HOST = socket.gethostname()
logging.basicConfig(format = '%(filename)s - %(levelname)-3s [%(asctime)s] %(message)s ', filename=LOGPATH, level=logging.DEBUG)
def dir_bkptype(day):
'''On Sunday - create full weekly backup with 'full_backup()';
other days - incremental backup with inc_backup()';
also used in 'back_dir_create()' to create BACKDIR/weekly or daily;
also used in 'clear_old_dirs()' for deleting old directories.'''
if day == 'Mon':
bkptype = 'weekly'
else:
bkptype = 'daily'
logging.info('Today is %s, will use backuptype %s.' % (day, bkptype))
return(bkptype)
def back_dir_create(user, bkptype):
'''Creates new directory for each day;
depending on data recieved from 'dir_bkptype()'
can create 'weekly' or 'daily' directory.'''
dirname = os.path.join(BACKDIR, user, bkptype, '%s-database/' % time.strftime('%Y-%m-%d'))
if not os.path.exists(dirname):
os.makedirs(dirname)
logging.info('Directory %s created' % dirname)
return(dirname)
def mysql_connect(dbroot, dbhost, dbrootpw):
'''Calls in 'run_backup()' with [DBROOT], [_DBROOTPW] and [DBHOST]
in arguments.
Creates object [cursor] for use in select_user_dbs()'''
db = MySQLdb.connect(dbhost, dbroot, dbrootpw)
cursor = db.cursor()
return(cursor)
def select_user_dbs(cursor):
'''Calls with [cursor] from 'mysql_connect()';
returns two lists like:
for users - ['setevoy', 'setevoy', 'hudeem', [...] 'vexim', 'worlddesign', 'zabbix']
and their databases: ['setevoy_test', 'autocomtestdb', 'hudeem_db', [...] 'vexim', 'worlddesign_db1', 'zabbix']'''
users_list = []
database_list = []
cursor.execute('select user, db from mysql.db')
for line in cursor.fetchall():
users_list.append(line[0])
database_list.append(line[1])
return(users_list, database_list)
cursor.close()
def mysql_dump(dbroot, dbrootpw, database, archname):
'''Calls in 'run_backup()' to create dump'''
subprocess.call('mysqldump -u %s -p%s %s | bzip2 > %s' % (dbroot, dbrootpw, database, archname), shell=True)
logging.info('Database backup complete: database %s saved to %s' % (database, archname))
def sendmail():
'''Send email report to [to] with used disk space information.'''
sender = '[email protected]'
to = ['[email protected]']
msg = email.mime.Multipart.MIMEMultipart()
msg['Subject'] = ('Database backup report')
msg['From'] = 'Backup Manager <[email protected]>'
msg['To'] = 'root <[email protected]>'
body = email.mime.Text.MIMEText("""
Databases backup on %s finished.
""" % (HOST))
msg.attach(body)
smtpconnect = smtplib.SMTP('localhost:25')
#smtpconnect.set_debuglevel(1)
smtpconnect.sendmail(sender, to, msg.as_string())
smtpconnect.quit()
logging.info('Email sent from ['%s'] to %s.' % (sender, to))
def run_backup():
'''Main function.
cursor - creates 'cursor' object from 'mysql_connect()' first;
user, database - receives lists (users_list[] and database_list[]) from 'select_user_dbs()';
bkptype - determines backup type (weekly, daily) from 'dir_bkptype()'.
For each user looks for it's databases, creates directory,
and if there is no backup ('archname') yet - calls 'mysql_dump()' function.'''
cursor = mysql_connect(DBROOT, DBHOST, _DBROOTPW)
user, database = select_user_dbs(cursor)
bkptype = dir_bkptype(curday)
curday = time.strftime('%a')
for user, database in zip(user, database):
if database not in EXCLUDE:
backup_dir = back_dir_create(user, bkptype)
if backup_dir:
archname = (backup_dir + database + '.sql.bz2')
if not os.path.isfile(archname):
mysql_dump(DBROOT, _DBROOTPW, database, archname)
else:
logging.info('Backup %s already present, skip' % archname)
else:
logging.info('Database %s in exclude list, skip' % database)
sendmail()
if __name__ == '__main__':
starttime = time.strftime('%Y-%m-%d %H:%M:%S')
logging.info('Backup started at: %s' % starttime)
run_backup()
finishtime = time.strftime('%Y-%m-%d %H:%M:%S')
logging.info('Backup finished at: %s' % finishtime)
