paper/mysql_backup.py

#!/usr/bin/python
import os
import tarfile
import smtplib
from email.header import Header
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from collections import namedtuple
from datetime import datetime

"""
MySQL dumper tool
used for backup mysql automatically(which could be done by cron).

Aims:
1. support multiple database
2. support dump to local basically and push to remote server/send an email
"""

""" Config Section """
db_t = namedtuple('db_t', ['host', 'port', 'db', 'username', 'password'])
smtp_t = namedtuple('smtp_t', ['server', 'sender', 'username', 'password',
                               'to', 'title'])

# mysql config
mylist = [
    db_t('localhost',   # host
         3306,          # port
         'twongd',      # db
         'twongd',      # username
         'twongd'),     # password
]

# email config
email = smtp_t('smtp.163.com',          # smtp server
               'yyk882002@163.com',     # from
               'yyk882002@163.com',     # username
               'initialize',            # password
               'wedtrav@foxmail.com',   # to
               'twongd backup')         # title

# local config
saveto = '/tmp/'
""" /Config Section """


def dump():
    """
     return False or saved filename
    """
    files = []
    for my in mylist:
        filename = my.db + datetime.now().strftime('_%Y_%m_%d_%H_%M_%S.sql')
        dest = os.path.join(saveto, filename)
        cmd = 'mysqldump -h {} -u {} --password={} {} > {}'.format(
            my.host, my.username, my.password, my.db, dest
        )
        if os.system(cmd):
            break
        files.append(dest)
    return files


def create_targz(source_files, dest_file):
    try:
        with tarfile.open(dest_file, 'w:gz') as tar:
            for file in source_files:
                tar.add(file)
        return True
    except Exception as e:
        print(e)
        return False


def send_mail(sqlfile):
    """
    return bool
    """
    msg = MIMEMultipart()
    msg['From'] = email.sender
    msg['To'] = email.to
    msg['Subject'] = Header(email.title, 'utf-8')
    msg.attach(MIMEText('daily backup', 'plain', 'utf-8'))
    att = MIMEText(str(open(sqlfile, 'rb').read()), 'base64', 'utf-8')
    att['Content-Type'] = 'application/octet-stream'
    name = os.path.basename(sqlfile)
    att['Content-Disposition'] = 'attachment; filename={}'.format(name)
    msg.attach(att)

    smtp_obj = smtplib.SMTP_SSL()
    smtp_obj.connect(email.server)
    smtp_obj.login(email.username, email.password)
    smtp_obj.sendmail(email.sender, email.to, msg.as_string())
    smtp_obj.quit()


def main():
    # check config
    if not mylist:
        print('no any mysql configurations')
        return
    # dump to sql files
    sqlfiles = dump()
    if not sqlfiles:
        print('ERROR: dump failed')
        return
    # compress all sql files
    tmp_targz = datetime.now().strftime('/tmp/%H_%m_%s_%H:%M_%S.tar.gz')
    if not create_targz(sqlfiles, tmp_targz):
        print('compress .sql failed')
        return
    # send email
    send_mail(tmp_targz)
    # remove sql files (keep .tar.gz on dist)
    _ = [os.remove(sql) for sql in sqlfiles]


if __name__ == '__main__':
    main()