MySQL备份与恢复脚本

1、检查并把mysqldump，mysql命令是否在/usr/bin下面，没有则链接进去，

 因为定时器不会加载其他目录的执行文件，以及环境变量，

如果不想配置，则需要在后面的备份脚本中写命令的全路径，否则备份不成功。

所以经常同一个用户手动执行成功，配置定时器执行就不成功，也是这个原因。

   查找路径  whereis mysqldump 

   sudo ln -s /usr/local/mysql/bin/mysqldump /usr/bin/

   sudo ln -s /usr/local/mysql/bin/mysql /usr/bin/

2、安装python mysql驱动

如果是python2
yum install MySQL-python.x86_64

如果是python3

pip3 install PyMySQL

3、备份脚本代码(/data/scripts/backup_or_restore_db.py)

# -*- coding: utf-8 -*-
import os
import sys
import datetime
import shutil
import traceback
import os
import sys
if sys.version_info.major == 3:
    import pymysql as MySQLdb
    MySQLdb.version_info = (1, 4, 13, "final", 0)
    MySQLdb.install_as_MySQLdb()
elif sys.version_info.major == 2:
    import MySQLdb

else:
    raise Exception("error python version")


USER = "test"
HOST = "127.0.0.1"
PASSWD = "test123456"
BACKUP_DAYS = 5
BACKUP_DIR  = "/data/data/backup"

if os.name != "posix":
    BACKUP_DIR = "E://backup"

class BackUpDb(object):
    def __init__(self,host,user,passwd):
        self.host = host
        self.user = user
        self.passwd = passwd
        self.db_connect()

    def db_connect(self):#连接数据库
        self.connect = MySQLdb.connect(
            host = self.host,
            user = self.user,
            passwd = self.passwd
            )


    def set_autocommit(self, status):
        sql = "SET autocommit=%d" % status
        self.db_query(sql)

    def db_close(self):
        self.connect.commit()    #innodb表需要commit
        self.connect.close()

    def db_query(self, sql, result=0):
        db_cursor = self.connect.cursor()
        mod_num = db_cursor.execute(sql)
        ret = db_cursor.fetchall()
        return ret
 
def backup_db():
    conn = BackUpDb(HOST,USER,PASSWD)
    exclude_dbs = [
            "information_schema",
            "mysql",
            "performance_schema",
            "test",
            "sys",
    ]
    sql = "show databases;"
    query_result = conn.db_query(sql)
    conn.db_close()
    
    dbs = []
    for db_name in query_result:
        if db_name[0] not in exclude_dbs:
            dbs.append(db_name[0])
    
    for db in dbs:
        print ('start backupdb:%s'%db)
        backup_file_name = "%s_%s_backup.sql"%(db,datetime.datetime.now().strftime("%Y%m%d%H%M%S"))
        d_dir = os.path.join(BACKUP_DIR,datetime.datetime.now().strftime("%Y%m%d"))
        if not os.path.exists(d_dir):
            try:
                os.makedirs(d_dir)
            except:
                traceback.print_exc()
        
        backup_file_name = os.path.join(d_dir,backup_file_name)
        
        command = "mysqldump --default-character-set=utf8 -u%(user)s -p%(pwd)s -B %(db)s -h %(host)s >%(f)s"%{
            "user":USER,
            "db":db,
            "host":HOST,
            "f":backup_file_name,
            "pwd":PASSWD,
        }
        print(command)
        os.system(command)
        os.system("gzip %s"%backup_file_name)
        
        print ('end backupdb:%s\n'%db)
    
    print('check and delete backups')
    if os.path.exists(BACKUP_DIR):
        count_dirs = 0
        all_backup_day_dirs = os.listdir(BACKUP_DIR)
        all_backup_day_dirs.sort()
        for f in all_backup_day_dirs:
            if os.path.isdir(os.path.join(BACKUP_DIR,f)):
                try:
                    d = datetime.datetime.strptime(f,"%Y%m%d")
                    count_dirs += 1
                except:
                    traceback.print_exc()
        
        #删除多余操作的文件备份
        if count_dirs>BACKUP_DAYS:
            for i  in range(count_dirs - BACKUP_DAYS):
                try:
                    last_d = (datetime.date.today() - datetime.timedelta(days=BACKUP_DAYS)).strftime("%Y%m%d")
                    if all_backup_day_dirs[i] < last_d:
                        d = os.path.join(BACKUP_DIR,all_backup_day_dirs[i])
                        print ("last_d='%s',delete dir ='%s'"%(last_d,d))
                        shutil.rmtree(d)
                except:
                    traceback.print_exc()
    

def restore_db():
    files = os.listdir(".")
    for f in files:
        if os.path.isfile(f) and f.endswith("_backup.sql"):
            db_name = os.path.split(f)[1][:-11]
            print ('start restore:%s'%db_name)
            command = u"mysql --default-character-set=utf8 -h %(host)s -u%(user)s -p%(pwd)s < %(f)s"%{
                "f":f,
                "user":USER,
                "pwd":PASSWD,
                "host":HOST,
            }
            print (command)
            os.system(command)
            print ('end restore:%s\n'%db_name)
    
    
if __name__ == "__main__":
    u"""
        备份的时候
        1、FLUSH TABLES WITH READ LOCK;
        2、执行脚本
        3、unlock
        4、/sbin/chkconfig --level 345 mysqld off
    """
    if len(sys.argv)<2: 
        raise Exception("command like this python backup_db backup|restore")
    
    command = sys.argv[1]
    if command not in ["backup","restore"]:
        raise Exception("command like this python backup_db backup|restore")
    
    if command == "backup":
        backup_db()
        
   

4、配置定时器

30  1  * * * flock -xn  /tmp/backup_db.lock  -c '/usr/bin/python /data/scripts/backup_or_restore_db.py backup'

 5、查看备份情况

windows:
E://backup
linux:
/data/data/backup/