#!/bin/bash #created by yangqiqi 2017-08-24 #创建备份用户 #grant select,lock tables,reload,super,file,show view on *.* to 'mysqlbackup'@'localhost' identified by 'my_password'; #grant execute on *.* to 'mysqlbackup'@'localhost' identified by 'my_password'; 授予调用存储过程的权限 ##flush privileges; USERNAME=mysqlbackup #备份的用户名 PASSWORD=my_password #备份的密码 HOST=localhost #备份主机 DATE=`date +%Y-%m-%d` #用来做备份文件名字的一部分 OLDDATE=`date +%Y-%m-%d -d '-10 days'` #本地保存天数 #指定命令所用的全路径 MYSQL=/app/mysql5.5/bin/mysql MYSQLDUMP=/app/mysql5.5/bin/mysqldump MYSQLADMIN=/app/mysql5.5/bin/mysqladmin #创建备份的目录和文件 BACKDIR=/data/backup/db [ -d ${BACKDIR} ] || mkdir -p ${BACKDIR} [ -d ${BACKDIR}/${DATE} ] || mkdir ${BACKDIR}/${DATE} [ ! -d ${BACKDIR}/${OLDDATE} ] || rm -rf ${BACKDIR}/${OLDDATE} #保存10天 多余的删除最前边的 #开始备份 for循环想要备份的数据库 MYSQLDUMP_LIST=`mysql -uroot -p'123456' -e "show databases"| grep -Evi "database|infor|perfor"` for DBNAME in ${MYSQLDUMP_LIST} ##使用for依次罗列需要备份的数据库 do ${MYSQLDUMP} -B -F --master-data=2 --single-transaction -u${USERNAME} -p${PASSWORD} ${DBNAME} | gzip > ${BACKDIR}/${DATE}/${DBNAME}-backup-${DATE}.sql.gz /bin/sleep 2 logger "${DBNAME} has been backup successful - $DATE" done 旧版本的备份脚本
#!/bin/bash #created by qiqi 2018-5-4 ####################### 用户说明 不写入脚本里############### MYSQL 备份用户权限 需要以下权限: SELECT, RELOAD, SHOW DATABASES, LOCK TABLES, REPLICATION CLIENT, EVENT 权限列表 1.SELECT 读取 2.SHOW DATABASES 允许访问完整的数据库列表 4.LOCK TABLES 允许锁定表 5.RELOAD 允许载入和刷新服务器缓存 6.REPLICATION CLIENT 允许用户询问从属服务器或主服务器的地址 7.EVENT 允许执行事务 mysql> GRANT SELECT,RELOAD,SHOW DATABASES,LOCK TABLES,EVENT,REPLICATION CLIENT ON *.* TO 'back_user'@'192.168.0.203' IDENTIFIED BY '123456'; mysql> FLUSH PRIVILEGES; ###################################################################### USERNAME=mysqlbackup #备份的用户名 PASSWORD=xxxxx #备份的密码 HOST=1.1.1.1 #备份主机 DATE=`date +%Y-%m-%d` #用来做备份文件名字的一部分 OLDDATE=`date +%Y-%m-%d -d '-3 days'` #本地保存天数 #指定命令所用的全路径 MYSQL=/data/mysql/bin/mysql MYSQLDUMP=/data/mysql/bin/mysqldump MYSQLADMIN=/data/mysql/bin/mysqladmin #创建备份的目录和文件 BACKDIR=/data/backup/mysqldb [ -d ${BACKDIR} ] || mkdir -p ${BACKDIR} [ -d ${BACKDIR}/${DATE} ] || mkdir ${BACKDIR}/${DATE} [ ! -d ${BACKDIR}/${OLDDATE} ] || rm -rf ${BACKDIR}/${OLDDATE} #保存5天 多余的删除最前边的 #开始备份 for循环想要备份的数据库 MYSQLDUMP_LIST=$(${MYSQL} -u${USERNAME} -p${PASSWORD} -h${HOST} --socket=/data/mysql3306/mysql.sock -e "show databases"| grep -Evi "database|infor|perfor|mysql") for DBNAME in ${MYSQLDUMP_LIST} ##使用for依次罗列需要备份的数据库名字 do $MYSQLDUMP -u${USERNAME} -p${PASSWORD} -h${HOST} --single-transaction --master-data=2 --set-gtid-purged=off -F --triggers --routines --events --socket=/data/mysql3306/mysql.sock $DBNAME | gzip > ${BACKDIR}/${DATE}/${DBNAME}-${DATE}.sql.gz logger "${DBNAME} has been backup successful - $DATE" done
脚本注意点:
MYSQLDUMP_LIST=`mysql -uroot -p'123456' -e "show databases"| grep -Evi "database|infor|perfor"` 过滤掉不需要备份的数据库,这里需要注意,过滤完成
后拿到的库名是自己需要备份的库名,如果自建的库有的也叫infor开头或者perfor的可能会被过滤掉,按照个人情况调整。
每天的备份样式如下:
[root@xxx db]# ls 2017-08-25 [root@xxx db]# cd 2017-08-25/ [root@xxx 2017-08-25]# ls mysql-backup-2017-08-25.sql.gz test2-backup-2017-08-25.sql.gz test-backup-2017-08-25.sql.gz test1-backup-2017-08-25.sql.gz test3-backup-2017-08-25.sql.gz
logger解释
logger 是一个shell 命令接口,可以通过该接口使用Syslog的系统日志模块,还可以从命令行直接向系统日志文件写入一行信息。 日志的级别 日志的级别分为七级,从紧急程度由高到底: emerg 系统已经不可用,级别为紧急 alert 警报,需要立即处理和解决 crit 既将发生,得需要预防。事件就要发生 warnig 警告 err 错误信息,普通的错误信息 notice 提醒信息,很重要的信息 info 通知信息,属于一般信息 debug 这是调试类信息
上边的脚本备份完毕后 就可以在 看到每个数据库备份的信息 logger的作用
[root@xxx 2017-08-25]# tail -f /var/log/messages Aug 25 09:30:06 xxx root: test has been backup successful - 2017-08-25 Aug 25 09:30:11 xxx root: test1 has been backup successful - 2017-08-25 Aug 25 09:30:16 xxx root: test2 has been backup successful - 2017-08-25 Aug 25 09:30:21 xxx root: test3 has been backup successful - 2017-08-25 Aug 25 09:40:02 xxx root: mysql has been backup successful - 2017-08-25 Aug 25 09:40:07 xxx root: test has been backup successful - 2017-08-25 Aug 25 09:40:12 xxx root: test1 has been backup successful - 2017-08-25 Aug 25 09:40:17 xxx root: test2 has been backup successful - 2017-08-25 Aug 25 09:40:22 xxx root: test3 has been backup successful - 2017-08-25
调用存储过程时报了下面的错误
ERROR 1370 (42000): execute command denied to user backupAccount@'localhost' for routine 'databaseName.spName'
ERROR 1370 (42000): execute command denied to user backupAccount@'localhost' for routine 'databaseName.spName'
解决方法:
grant execute on *.* to 'mysqlbackup'@'localhost' identified by 'mysql_ritto';
数据库备份完毕后,可能有 需要把备份的文件传输到一个专门用来放备份文件的服务器上 我习惯用rsync
方法如下:
需求:
每天凌晨1点把 A服务器上/data/backup/db/下的数据备份文件放到 B服务器里的/data/backup/db_192.168.1.11/目录下
不是累加 是增量更新 (--delete)
A服务器:
数据源服务器
打开防火墙 关闭seLinux
B服务器:
把公钥文件里的id_rsa.pub内容复制到 A服务器里的authorized_keys
文件中准备脚本文件
采用的是 拉push的政策在B服务器上将需要的数据源从A服务器上拉下来
数据源服务器
yum install rsync
-A INPUT -p tcp -m state --state NEW -m tcp --dport 873 -j ACCEPT
B服务器:
ssh-keygen
文件中准备脚本文件
#!/bin/bash usr/bin/rsync -avz --delete -e "ssh -p 4396" root@192.168.1.11:/data/backup/db /data/backup/db_192.168.1.11/ logger "Successful backup file transfer - $DATE"