# -*- 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()