#!/usr/bin/env python
# coding: utf-8

# This file is backup script of MySQL in SAKURA's rental server.
#
# Backup is using the mysqldump command.
# Dump file is stored in directory specified.
# Dump files that are older than for the specified period will be removed.
# Can be run from the command line or cron.
#
# Remote backup is using sftp.
# If exists SSH private key, key is used for authentication
# Can be remote backup, by specify a remote server in argument.
#  Example) backupDatabase.py [USER@]SERVER[:PORT] [PASSWORD]
#  * If you not specify user, use login user .
#  * If you not specify port, use port 22.
#
# This script is required MySQLdb module.
# * If you want remote backup, required ssh module.

import os
import sys
import time
import datetime
import getpass
import glob
from subprocess import call
# External module
import MySQLdb

# Database connection infomation
HOST = 'mysqlXXX.db.sakura.ne.jp'
USER = getpass.getuser()
PASSWD = '******'

# Backup directory and backup command
BKDIR = os.path.expanduser('~/backup/mysql')
DTIME = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
TMPLT = '/usr/local/bin/mysqldump -c -Q -h{0} -p{1} {2} > {3}/{2}_{4}.sql'

# Retention period
DEL_DAY = 90

# Remote backup settings
remote_backup = False
if len(sys.argv) > 1:
    try:
        # External module
        import ssh
        rhost = sys.argv[1]
        ruser = getpass.getuser()
        rport = 22
        priv_key = os.path.expanduser('~/.ssh/id_rsa')
        # Remote backup directory
        RBKDIR = '/usr/local/backup'
        if rhost.find('@') >= 0:
            ruser, rhost = rhost.split('@')

        if rhost.find(':') >= 0:
            rhost, rport = rhost.split(':')
            rport = int(rport)

        # RSA Key passphrase or remote server password
        RPASS = '******'
        if len(sys.argv) > 2:
            RPASS = sys.argv[2]
        t = ssh.Transport((rhost, rport))
        if os.path.isfile(priv_key):
            key = ssh.RSAKey.from_private_key_file(priv_key, RPASS)
            t.connect(username=ruser, pkey=key)
        else:
            t.connect(username=ruser, password=RPASS)
        sftp = ssh.SFTPClient.from_transport(t)
        remote_backup = True
    except Exception as e:
        print(e)

# Get database list
conn = MySQLdb.connect(host=HOST, port=3306, user=USER, passwd=PASSWD)
cur = conn.cursor(MySQLdb.cursors.DictCursor)
cur.execute("SET NAMES utf8")
sql = "SHOW DATABASES LIKE '{0}%'".format(USER)
cur.execute(sql)
res = cur.fetchall()
dbs = [ d.values()[0] for d in res ]
cur.close()
conn.close()

# Database backup and remote backup
cmds = [ TMPLT.format(HOST, PASSWD, db, BKDIR, DTIME) for db in dbs ]
for cmd in cmds:
    file = cmd.split()[-1]
    retcode = call(cmd, shell=True)
    if remote_backup:
        sftp.put(file, os.path.join(RBKDIR, os.path.basename(file)))

# Delete
dtime = datetime.date.today() - datetime.timedelta(days=90)
epoc = time.mktime(dtime.timetuple())
files = { f: os.path.getctime(f) for f in glob.iglob(os.path.join(BKDIR, '*')) }
for file in files:
    if epoc > files[file]:
        os.unlink(file)

if remote_backup:
    t.close()