配置部署mysqlhotcopy热备工具总结

之前有一篇转载的文章,这里再做一个总结:

1.创建对应的低权限用户,给予select,reload,lock tables即可。

[sql]grant select,reload,lock tables on *.* to 'hotcopyer'@'localhost' identified by '123456';[/sql]

2.修改/etc/mysql/my.cnf或者/etc/mysql/conf.d/ (我选择后者,这一步是可选的,写了这里在后面是使用mysqlhotcopy的时候就不需要输入密码了)

[mysqlhotcopy]
interactive-timeout
user=hotcopyer
password=123456
port=3306

3.编写热备份脚本
[bash]
#!/bin/bash
# Name:mysqlbackup.bash
# PS:MySQL DataBase Backup,Use mysqlhotcopy script.
# Write by:ihipop At Gmail Dot Com
# Last Modify:2011-2-10
#
# 定义变量
scriptsDir=pwd
datadir=/var/lib/mysql/
tmpBackupDir=/tmp/mysqlback
backupDir=/root/Dropbox/MySQL
mysqlUser=hotcopyer
mysqlPWD=123456
#mysqlhost=127.0.0.1
dblist=ls -p $datadir | grep / | tr -d /
eMail=××××××@qq.com
copy=/usr/bin/mysqlhotcopy
dump=/usr/bin/mysqldump
#

#每个月1号同时备份SQL
[ date +%d == 10 ] && needsql=1

#创建文件夹
if [[ -e $tmpBackupDir ]]; then
rm -rf $tmpBackupDir/*
else
mkdir -p $tmpBackupDir
fi

if [[ ! -e $backupDir ]];then
mkdir -p $backupDir
#else
# continue
#rm -rf $backupDir/*
fi

if [[ -s $backupDir/mysqlbackup.log ]]; then
cat /dev/null >$backupDir/mysqlbackup.log
fi
#

for databases in $dblist
do
if [[ $databases == "" ]]; then
continue
else
if [ -n $mysqlPWD ] ;then
$copy --user=$mysqlUser --password=$mysqlPWD -q "$databases" $tmpBackupDir
else
$copy -q "$databases" $tmpBackupDir
fi
[ "$needsql" == "1" ] && $dump --user=$mysqlUser --password=$mysqlPWD "$databases" >$tmpBackupDir/${databases}.sql
dateTime=date "+%Y.%m.%d %H:%M:%S"
echo "$dateTime Database:$databases backup success!" >> $backupDir/mysqlbackup.log
fi
done

# 压缩数据
date=date +%Y-%m-%d-%H
cd $tmpBackupDir
tar jcvf $backupDir/mysql-$date.tar.gz ./ >/dev/null

#邮寄结果
if [[ -s $backupDir/mysqlbackup.log ]]; then
cat $backupDir/mysqlbackup.log | mail -s "MySQL Backup" $eMail
fi

#删除临时文件

rm -rf $tmpBackupDir
[/bash]
4.写入Cron

00 03 * * * /usr/local/bin/mysqlback.bash

Author Info :
  • From:配置部署mysqlhotcopy热备工具总结
  • URL:https://blog.ihipop.info/2011/02/2082.html
  • Please Reserve This Link,Thanks!
  • 发表评论

    您的电子邮箱地址不会被公开。 必填项已用*标注