MySQL 整库单表备份 for python脚本

#!/usr/bin/python
#! _*_ coding:utf-8 _*_

from datetime import date, datetime, timedelta
import os,time

user = 'root'
passw = '123456'
ip = '192.168.1.229'
dir = '/data2/backup/3308/'
mysqlbase = '/usr/local/mysql/bin/'
port='3308'

if  os.path.isdir(dir):
    print("dir ok")
else:    
    os.makedirs(dir)
dbnam=os.popen(mysqlbase+'mysql  -u'+user+' -p'+passw+' -h'+ip+' -P'+port+' -e "show databases"|grep -vE "mysql|Database|information_schema|performance_schema|sys"').read()
DBNAME = dbnam.replace('\n',',').rstrip(',').split(',')
os.chdir(dir)
for dbname in DBNAME:
    ad = os.popen(mysqlbase+'mysql -h'+ip+' -u'+user+' -p'+passw+' -P'+port+' -e "use ' + dbname + ';show tables "|grep -v Tables_in').read()
    ad1=ad.replace('\n',',').rstrip(',').split(',')
    for tabname in ad1:
        os.system(mysqlbase+'mysqldump   -h'+ip+' -u'+user+' -p'+passw+' -P'+port+'  --set-gtid-purged=off  --single-transaction  -R  '+dbname+' ' + tabname+' > '+dir+ '/'+dbname + '.'+ tabname +'.sql')
    dat = time.strftime('%Y%m%d_%H%M',time.localtime(time.time()))
    os.system('cd '+dir+';tar zcvf '+dat+dbname+'.tar.gz *sql; rm -f *.sql')
    os.system('find /data2/backup/3306/* -mtime +3 -name "2017*.tar.gz" -exec rm -fr {} \;')

 
 
设置计划任务每小时备份一次方便恢复单表:
0 */1 * * *    /root/python/mysqldump_table.py >/dev/null 2>&1
 
欢迎各位同学一起交流。
 

0 个评论

要回复文章请先登录注册