mysql数据库运维脚本_mysql数据库备份与维护脚本

准备工作：

创建mysql备份账号：

GRANT SELECT, RELOAD, PROCESS, SUPER, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup'@'localhost' IDENTIFIED BY 'XXX';

FLUSH PRIVILEGES

1， 全备脚本(backup.sh)

#!/usr/bin/env sh

#

# full backup

################################################################################

################################################################################

BINLOG_HOME=/export/mysql/logs

BACKUP_HOME=/export/mysql_backup

BACKUP_DEST=$BACKUP_HOME/full

BACKUP_LOG=$BACKUP_HOME/log

################################################################################

# make one backup

innobackupex --defaults-file=/etc/mysql/my.cnf --user='backup' \

--password='XXX' --slave-info  --safe-slave-backup \

--compress --compress-threads=4 $BACKUP_DEST

2, 备份log ( backup_log.py)

#!/usr/bin/python

#

# log backup

import os

import os.path

import shutil

import time,  datetime

import re

binlog_home = '/export/mysql/logs/'

backup_home = '/export/mysql_backup/log'

basename = 'mysql-bin.[0-9]'

def copyFiles(sourceDir, targetDir):

if not os.access(sourceDir, os.F_OK):

print '%s does not exist' %(sourceDir)

return

if sourceDir.find(".svn") > 0:

return

for file in os.listdir(sourceDir):

sourceFile = os.path.join(sourceDir, file)

targetFile = os.path.join(targetDir, file)

if os.path.isfile(sourceFile) and re.compile(basename).match(file):

if not os.path.exists(targetDir):

os.makedirs(targetDir)

if not os.path.exists(targetFile) or (os.path.exists(targetFile) and (os.path.getsize(targetFile) != os.path.getsize(sourceFile))):

open(targetFile, "wb").write(open(sourceFile, "rb").read())

if  __name__ =="__main__":

print "Start binlog Copying..."

copyFiles(binlog_home, backup_home)

print "End Copying!"

3，清理历史文件(cleanoldfile.sh)

#!/bin/sh

#

#  clean up history backup data/log files

echo "begin cleaning..."

find /export/mysql_backup/full -type d -ctime +10 | xargs rm -rf

find /export/mysql_backup/log -type f -ctime +7 | xargs rm -rf

echo "end cleaning!"

4，crontab设置：

0 1 * * * /export/mysql_backup/cleanoldfile.sh

0 2 * * * /export/mysql_backup/backup.sh

*/30 * * * * /export/mysql_backup/backup_log.py