之前有一篇转载的文章，这里再做一个总结:
1.创建对应的低权限用户,给予select,reload,lock tables即可。
|
1 |
grant select,reload,lock tables on *.* to 'hotcopyer'@'localhost' identified by '123456'; |
2.修改/etc/mysql/my.cnf或者/etc/mysql/conf.d/ (我选择后者，这一步是可选的，写了这里在后面是使用mysqlhotcopy的时候就不需要输入密码了)
[mysqlhotcopy]
interactive-timeout
user=hotcopyer
password=123456
port=3306
3.编写热备份脚本
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
#!/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 #</p> #每个月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 |
4.写入Cron
00 03 * * * /usr/local/bin/mysqlback.bash