# -*- coding:utf-8 -*-

__author__ = 'colin'

#!/usr/bin/env python

#-*- coding:utf-8 -*-

import os,commands

import MySQLdb

import time

import subprocess

import multiprocessing

import paramiko

BKPATH='/data/DB_back'

NOTBK = ['information_schema','test','mysql','performance_schema']

TIME=time.strftime("%Y%m%d%H%M",time.localtime())

dblist = []

sendFile = []

conn=MySQLdb.connect(host='192.168.1.203',user='root',port=3306)

cur=conn.cursor()

cur.execute('show databases;')

result=cur.fetchall()

for line in result:

if line[0] in NOTBK:

line = line[0].strip(line[0])

dblist.append(line)

pass

cur.close()

conn.close()

'''

清掉之前的备份的文件

'''

for i in os.listdir(BKPATH):

if i.endswith('tar.gz'):

try:

os.remove("{0}{1}".format(BKPATH,i))

except NameError:pass

'''

备份文件

'''

def run(cmd):

#p=subprocess.Popen(cmd,shell=True,stdout=subprocess.PIPE,stderr=subprocess.PIPE)

res=commands.getstatusoutput(cmd)

pool = []

p = multiprocessing.Pool(processes=2)

for i in range(0,len(dblist)):

if dblist[i] != '':

cmd = '/usr/bin/mysqldump -h 192.168.1.203 -uroot %s --events|gzip > %s%s_%s.sql.gz' % (dblist[i][0], BKPATH, dblist[i][0],TIME)

sendFile.append("%s_%s.sql.gz" % (dblist[i][0],TIME))

#run(cmd)

pool.append(p.apply_async(run,(cmd, )))

p.close()

p.join()

"""

备份到其他服务器

"""

s = paramiko.SSHClient()

s.load_system_host_keys()

s.set_missing_host_key_policy(paramiko.AutoAddPolicy())

t = paramiko.Transport(('192.168.1.220',int(22)))

key = paramiko.RSAKey.from_private_key_file("/root/.ssh/id_rsa")

t.connect(username='root',pkey=key)

sftp = paramiko.SFTPClient.from_transport(t)

for i in range(0,len(sendFile)):

sftp.put("{0}{1}".format(BKPATH, sendFile[i]),"/data/mysql_backup_1.44/{0}".format(sendFile[i]))

t.close()