# 引入包
import os, shutil, ftplib, zipfile, datetime;

# mysqldump命令路径
mysqldump = 'mysqldump';

# mysql配置
user = 'xmsb';
pwd = '123456';

# 要备份的库名.表名
databases = ['database1', 'database2'];
tables = ['database.table1', 'database.table2'];

# FTP服务器配置
host = '127.0.0.1';
username = 'xmsb';
password = '123456';
cd = '/home/xmsb/';

# 获取当前时间
now = datetime.datetime.now().strftime('%Y-%m-%d_%H%M%S');

# 创建文件夹
path = os.getcwd() + os.sep + 'history';
if os.path.isdir(path) == False:
    os.mkdir(path);

mPath = path + os.sep + str(now);
os.mkdir(mPath);

dPath = mPath + os.sep + 'databases';
os.mkdir(dPath);

tPath = mPath + os.sep + 'tables';
os.mkdir(tPath);

# 执行备份
cmd = mysqldump + ' -u' + user + ' -p' + pwd + ' ';

for i in databases:
    os.system(cmd + i + ' > ' + dPath + os.sep + i + '.sql');
for i in tables:
    os.system(cmd + i.replace('.', ' ') + ' > ' + tPath + os.sep + i.replace('.', '_') + '.sql');
    
# 打包文件
fileName = 'history' + os.sep + now + '.zip';
zip = zipfile.ZipFile(fileName, 'w', zipfile.ZIP_DEFLATED, allowZip64 = True);
for root, dir, files in os.walk(mPath):
    root_ = os.path.relpath(root, mPath);
    for file in files:
        tmpPath = os.path.join(root, file);
        tmpPath_ = os.path.join(root_, file);
        zip.write(tmpPath, tmpPath_);
        
zip.close();

# 连接ftp服务器并上传文件
ftp = ftplib.FTP();
ftp.set_pasv(False);
ftp.connect(host, 21);
ftp.login(username, password);
ftp.cwd(cd);
ftp.storbinary('STOR '+ now + '.zip', open(fileName, 'rb'), 1024);
ftp.close();

# 删除临时文件夹
shutil.rmtree(mPath);