韦德国际_韦德国际1946官方网站_韦德国际1946手机版
做最好的网站

MySQL备份脚本及异地备份,MySQL备份脚本

日期:2019-05-22编辑作者:韦德国际1946手机版

[Python]代码

先是个本子:

  由于在Server200三中搭建了WAMP服务器,在那之中,MySQL为第一数据库服务器,为了不用手动备份,特地参谋英特网写了1个自动备份脚本。

一.异地备份的话,必要做ssh免密登入

#!/usr/bin/env python
#encoding=utf-8 

#Name    : mysql_backup.py
#Author  : Chaous Nie
#Mail    : chaous@sar4.com
#Site    : http://sar4.com
#Time    : 2011-08-20
#For     : Backup mysql databases on windows
#Version : 0.1.2 Beta
#Steps   :
# 1.backup databases match  filter
# 2.gzip backup
# 3.md5sum backup
# 4.delete backup before $keeptime days ago
# 5.if isftp==1 then upload backup to ftp server

import os, sys , re, string, gzip, md5, getpass
from ftplib import FTP
from datetime import datetime,timedelta

reload(sys)
sys.setdefaultencoding('utf-8')

#mysql related
backup_path = '''e:\\backup\'''
dbhost = "localhost"
dbuser = "backup"
dbpass = "080808"
dumpoptions = '''--default_character-set=utf8 --triggers --routines --hex-blob --master-data=2 --single-transaction --flush-logs'''
filter = re.compile('^test(.*)$')
keeptime = (7)

#ftp related
isftp = 1
ftphost = '10.10.10.10'
ftpuser = 'user'
ftppass = 'passwd'
ftpport = '21'
ftppath ='''/ftp'''

nowdate = datetime.now().strftime('%Y%m%d')

def find_db():
    if os.path.isdir('''%s''' % (backup_path)):
        pass
    else:
        os.mkdir('''%s''' % (backup_path))

    find_command = '''mysql -h%s -u%s -p%s -e"show databases" > %sfind_db.listn ''' % (dbhost, dbuser, dbpass, backup_path) 

    a, b = os.popen2('cmd')
    a.write(find_command)
    a.close()
    b.read()
    b.close()

def sum(*files):
    sts = 0
    if files and isinstance(files[-1], file):
        files = files[-1], files[:-1]
    if len(files) == 1 and not isinstance(files[0], str):
        files = files[0]
    for f in files:
        if isinstance(f, str):
            if f == '-':
                sts = printsumfp(sys.stdin, '<stdin>') or sts
            else:
                sts = printsum(f) or sts
        else:
            sts = sum(f) or sts
    return sts

def printsum(filename):
    try:
        fp = open(filename, 'rb')
    except IOError, msg:
        sys.stderr.write('%s: Can't open: %sn' % (filename, msg))
        return 1
    sts = printsumfp(fp, filename)
    fp.close()
    return sts

def printsumfp(fp, filename):
    m = md5.new()
    try:
        while 1:
            data = fp.read(8096)
            if not data:
                break
            m.update(data)
    except IOError, msg:
        sys.stderr.write('%s: I/O error: %sn' % (filename, msg))
        return 1
    return ('%s  %s' % (m.hexdigest(), filename))

def gzip_db(db):
    dbname=db
    gzip_in = open('%s%s%s_%s.sql' % (backup_path, dbname, dbname, nowdate) , 'rb')
    gzip_out = gzip.open('%s/%s/%s_%s.sql.gz' % (backup_path, dbname, dbname, nowdate) , 'wb')
    gzip_out.writelines(gzip_in)
    gzip_out.close()
    gzip_in.close()
    os.remove('%s%s%s_%s.sql' % (backup_path, dbname, dbname, nowdate))

def backup_db(db):
    dbname=db
    if os.path.isdir('''%s%s''' % (backup_path, dbname)):
        pass
    else:
        os.mkdir('''%s%s''' % (backup_path, dbname))
    backup_command = '''mysqldump -h%s -u%s -p%s %s %s > %s%s%s_%s.sqln''' % (dbhost, dbuser, dbpass, dumpoptions, dbname, backup_path, dbname, dbname, nowdate)
    print '''mysqldump -h%s -u%s -p%s %s %s > %s%s%s_%s.sql''' % (dbhost, dbuser, dbpass, dumpoptions, dbname, backup_path, dbname, dbname, nowdate)
    a, b = os.popen2('cmd')
    a.write(backup_command)
    a.close()
    b.read()
    b.close() 

def ftp_backup(db):
    dbname=db
    localgzip = '''%s%s%s_%s.sql.gz''' % (backup_path, dbname, dbname, nowdate)
    localmd5 = '''%s%s%s_%s.sql.gz.MD5''' % (backup_path, dbname, dbname, nowdate)

    try:
        ftp =FTP()
        ftp.connect(ftphost,ftpport)
        ftp.login(ftpuser,ftppass)
        print "Welcome:",ftp.getwelcome()
    except Exception,e:
        print e
    else:
        try:
            ftp.cwd('''%s/%s''' %(ftppath,dbname))
        except Exception,e:
            print e
            print '''mkdir %s/%s''' %(ftppath,dbname)
            ftp.mkd('''%s/%s''' %(ftppath,dbname))
            ftp.cwd('''%s/%s''' %(ftppath,dbname))

        fd = open(localgzip,'rb')
        ftp.storbinary('STOR %s' % os.path.basename(localgzip),fd)
        fd.close()

        fd = open(localmd5,'rb')
        ftp.storbinary('STOR %s' % os.path.basename(localmd5),fd)
        fd.close()

        ftp.retrlines('LIST')

        ftp.quit()

def del_backup(db):
    dbname=db
    now = datetime.now()
    old = now - timedelta(keeptime)
    olddate = old.strftime('%Y%m%d')
    exist=os.path.exists('%s%s%s_%s.sql.gz' % (backup_path, dbname, dbname, olddate))
    if exist :
        os.remove('%s%s%s_%s.sql.gz' % (backup_path, dbname, dbname, olddate))
        print '''delete %s_%s.sql.gz''' % (dbname,olddate)
    exist=os.path.exists('%s%s%s_%s.sql.gz.MD5' % (backup_path, dbname, dbname, olddate))
    if exist :
        os.remove('%s%s%s_%s.sql.gz.MD5' % (backup_path, dbname, dbname, olddate))
        print '''delete %s_%s.sql.gz.MD5''' % (dbname,olddate)

def md5sum(gzipfile,db):
    md5info = sum(gzipfile)
    print md5info
    md5file = open('%s.MD5' % (gzipfile) , 'w')
    md5file.write(md5info)
    md5file.close()

def main():
    find_db()

    dbs=open(backup_path 'find_db.list','r')
    for line in dbs.readlines():
        line = line.strip('n')
        db = filter.match(line)
        if db:
            db = db.group(0)
            backup_db(db)
            gzip_db(db)
            gzipfile = '''%s%s%s_%s.sql.gz''' % (backup_path, db, db, nowdate)
            md5sum(gzipfile,db)
            print '''dumped database %s as %s%s%s_%s.sql.gz''' % (db,backup_path, db, db, nowdate)
            del_backup(db)
    dbs.close()

    if isftp == 1:
        dbs=open(backup_path 'find_db.list','r')
        for line in dbs.readlines():
            line = line.strip('n')
            db = filter.match(line)
            if db:
                db = db.group(0)
                ftp_backup(db)
        dbs.close()

    os.remove(backup_path 'find_db.list')

if __name__ == '__main__':
    main()

#!/bin/bash

  

在MySQL服务器上操作

#MYSQL备份脚本

  由于采纳Wamp格局搭建的MySQL在net中并从未生出功效,暗中同意安装在C盘根目录下,我们供给将它安装到net中:

ssh-keygen  

ssh-copy-id root@192.168.1.4

#Date:2017/08/17

.../mysql/bin/mysqld --install

2. MySQL全备脚本,能够备份三个数据库,单独文件夹,删除10天前的备份,拷贝到其余服务器(scp增量,不会覆盖掉原先文件)

BAK_MySQL备份脚本及异地备份,MySQL备份脚本。DIR=/data/backup/`date %Y%m%d`

  当然你也得以不用安装,能够采用这种措施来关闭和运行它:.../mysql/bin/mysqladmin -u root -p shutdown/start 

#!/bin/bash

#Mysql dabase information

#主机

find /aebiz/soft/databackup/* -type f -mtime 10  -name "*.gz" -exec rm {} ;

db_host="localhost"

#数据库帐号

db_user="root"

#数据库密码

db_passwd="123456"

#mysql安装目录

MYSQL="/aebiz/soft/mysql"

#mysql命令目录

MYSQLDUMP="/aebiz/soft/mysql/bin/mysqldump"

#Path information

#寄存备份文件的门径

BACKUP_DB="/aebiz/soft/databackup"

LogFile=$BACKUP_DB"/bak.log"

#Time information

#时间

time=`date "%Y-%m-%d-%H-%M-%S"`

day=`date "%d"`

month=`date "%Y-%m"`

weekday=`date "%u"`

#Path enable write

if [ ! -w "$BACKUP_DB" ]; then

chmod -R 700 $BACKUP_DB

fi

#echo "**********************************host info****************************************"

#echo "db_host:"$db_host

#echo "db_user:"$db_user

#echo "db_passwd:"$db_passwd

#echo "MYSQL:"$MYSQL

#echo "MYSQLDUMP:"$MYSQLDUMP

#echo "databaseName:"$databaseName

#echo "BACKUP_DB:"$BACKUP_DB

#echo "**********************************************************************************"

#Mysql Backup

Date=`date %Y%m%d`

Begin=`date "%Y-%m-%d %H:%M:%S"`

#echo "start backup database:"$databaseName"  "$Begin

#databases

#供给备份的数据库,倘使有多个数据库,请用,分隔

databaseList="ceshishop,ceshiydys,testshop0711,testydys0711"

for databaseName in `echo "$databaseList" | sed 's/,/n/g'`

do 

    echo $databaseName

#fileName

#扭转备份文件名

fileName=$databaseName"-"$time".sql"

#变化备份文件完整路线

BACKUP_DBPATH=$BACKUP_DB"/"$databaseName

#echo "***********************************database Info***********************************************"

#echo "BACKUP_DBPATH:"$BACKUP_DBPATH

#echo "fileName:"$fileName

#echo "**********************************************************************************"

if [ ! -d "$BACKUP_DBPATH" ]; then

mkdir "$BACKUP_DBPATH"

fi

#备份mysql

$MYSQLDUMP -u $db_user -p$db_passwd -h $db_host $databaseName > $BACKUP_DBPATH/$fileName

#打包.sql文件

cd $BACKUP_DBPATH && tar -czf $fileName.tar.gz $fileName && rm -rf $fileName && chmod go-rwx $fileName.tar.gz

Last=`date "%Y-%m-%d %H:%M:%S"`

#echo "end backup database:"$databaseName"  "$Last

#出口日志

echo start:$Begin end:$Last $fileName succ >> $LogFile

done

scp -r  /aebiz/soft/databackup root@192.168.1.4:/aebiz/soft

MYSQLDB=数据库名

 

3.Mysql服务器设置crontab定期职务

MYSQLUSER=用户

现实的本子如下:

每隔八日的黎明(英文名:lí míng)三点,实施备份

MYSQLPWD=密码

 @echo off
 echo.
 echo MySQL数据库备份脚本(JX_Xpresshare)
 echo ##############################
 echo.
 echo 备份日期:
		

本文由韦德国际发布于韦德国际1946手机版,转载请注明出处:MySQL备份脚本及异地备份,MySQL备份脚本

关键词: mysql 随笔 备份 脚本 Windows

活动创立外键

relationship_create_grt.py 若一个实体的某字段指向另一实体的主键,此字段即为外键。被针对的实体叫作主表,担负在针...

详细>>

Nginx的Rewrite正则表明式,mysql正则表明式相称完整

mysql的正则表达式中 [[::]], [[::]] 意味着单词的左右侧际,如下示例: 由于要rewrite1个地址从 /mag/xx/xxx/ - /m/xxx 但原先...

详细>>

ignored的化解办法,MySQL不可能重启报错Warning

日前FreeBSD机器上的mysql服务重启、登陆mysql、使用mysqldump备份数据库时报以下错误: 标题浅析 MySQL无法重启报错Warn...

详细>>

怎么着将查询的行与列数据实行转置,从询问获

语法如下: 正文实例讲述了PHP使用mysql_fetch_row查询获得数据行列表的秘籍。分享给我们供我们参谋。具体深入分析如...

详细>>