Linux下Mysql全量备份脚本如下:
#!/bin/bash
DATE=`date +%Y-%m-%d`
MYSQL_U="root"
MYSQL_P=xxxxxx
#MYSQL_H=`ifconfig eth0 | grep "inet addr" |awk 'BEGIN {FS=":"} {print $2}'|awk '{print $1}'`
MYSQL_H="localhost"
BAKDIR=/opt/mysqlbak
#创建备份目录
[ ! -d ${BAKDIR} ] && mkdir -p ${BAKDIR}
#备份mysql数据库
ALL_DB=`mysql -u${MYSQL_U} -h${MYSQL_H} -p${MYSQL_P} -Bse 'show databases'`
for DB in ${ALL_DB}
do
mysqldump -u${MYSQL_U} -h${MYSQL_H} -p${MYSQL_P} ${DB} --default-character-set=utf8 -opt -Q -R --skip-lock-tables --events --ignore-table=mysql.events | bzip2 >${BAKDIR}/${DB}-${DATE}.tar.gz
done
#--default-character-set=utf8 设置备份出的默认字符集为utf8
#--skip-lock-tables //不用此参数,备份库information_schema会报错"Got error: 1044: Access denied for user 'root'@'localhost' to database 'information_schema' when using LOCK TABLES"
#--events --ignore-table=mysql.events //不添加此选项,执行脚本时会警告"Warning: Skipping the data of table mysql.event. Specify the --events option explicitly"
#删除备份文件中7天前的文件
cd ${BAKDIR} && find ./ -name "*.tar.gz" -mtime +7 -type f | xargs rm -rf
#至此mysql全量备份脚本编写完成
转载于:https://blog.51cto.com/jekin/1680181