mysql .bak_mysql备份
可以使用percona-xtrabackup-2.0.7-552.rhel6.x86_64.rpm对mysql进行备份。
基本语法：
多实例备份：
语法解释：--user=数据库用户
--password=数据库密码
--socket=指定socket
--default-file=指定配置文件
最后面是存放位置
innobackupex --user=root --port=3306 --host=127.0.0.1 --defaults-file=/etc/my.cnf --defaults-group=mysqld3306 --stream=tar /usr/local/backup |gzip > 127.0.0.1_3306.tar.gz
全数据还原
/etc/init.d/mysqld stop //停掉mysql
mv /var/lib/mysql /var/lib/mysql_bak //数据目录备份
mkdir -p /var/lib/mysql //重建数据目录
--apply-log选项的命令是准备在一个备份上启动mysql服务
innobackupex --defaults-file=/etc/my.cnf --user=root --apply-log /home/tank/backup/2014-09-18_16-35-12
--copy-back 选项的命令从备份目录拷贝数据,索引,日志到my.cnf文件里规定的初始位置
innobackupex --defaults-file=/etc/my.cnf --user=root --copy-back /home/tank/backup/2014-09-18_16-35-12
chown -R mysql.mysql /var/lib/mysql //改变文件所属
/etc/init.d/mysqld stop //启动mysql
#!/bin/bash
# by jchen
# Mysql Backup for mysql 5.1.60
# 2013-12-02 14:00:00
# Version V1.1
###############################################
# 保留备份的天数
DAY=3
#等待n秒后再执行备份
wait=60
#是否开启增量备份默认0为开启1为关闭
open_increase_bak=1
#全备时间点
default_time=4
#设置不进行备份的时间点
exclude_time="0 5"
###############################################
#载入环境变量，否则无法找到/usr/local/mysql/bin/mysql路径
source /etc/profile
DBIP="127.0.0.1"
DBUSER="xxx"
DBPWD="root"
DATE=`date +%F_%H-%M`
Hour=`date '+%H'`
PORT=`netstat -tunlp|grep mysql |awk '{print $4}'|awk -F: '{print $2}'`
MYCNF="/etc/my.cnf"
LOG_FILE="/home/msbakscript/logs/xtrabackup.log"
BACKUP_PATH="/usr/local/hero_all_backup"
MYSQL_LOG="/home/msbakscript/logs/mysql.log"
INNOBACKUPEX="/usr/bin/innobackupex"
bak_log="/home/msbakscript/logs/mysqlbak.log"
MYSQL_PATH="/usr/local/mysql/var"
HOST=$(awk -F= '/IPADDR=/ {print $2}' /etc/sysconfig/network-scripts/ifcfg-eth1)
CHECKPOINT=$(awk '/to_lsn/ {print $3}' /usr/local/hero_all_backup/xtrabackup_checkpoints 2>/dev/null)
all_bak=("$HOST"_"$DATE")
increase_bak=("$HOST"_"$DATE""-increase")
if [ $# -eq 0 -o $# -eq 1 -o $# -eq 2 ];then
echo "OK" > /dev/null
else
echo "sh $0 allbakfile 或者 sh $0 allbakfile incbakfile"
exit 1
fi
[ ! -d ${BACKUP_PATH} ] && mkdir -p ${BACKUP_PATH}
[ ! -d /home/msbakscript/logs ] && mkdir -p /home/msbakscript/logs
if [ -z "`/sbin/pidof -s mysqld`" ];then
echo "mysqld is not running"
exit 1
fi
if [ `rpm -qa |grep -c xtrabackup-0.9-2.rhel4` -eq 1 ];then
rpm -e xtrabackup-0.9-2.rhel4
fi
if [ ! -f /usr/bin/innobackupex ];then
echo "xtrabackup is not installed"
rpm -i --nodeps http://122.228.194.133:8080/percona-xtrabackup-2.0.7-552.rhel5.x86_64.rpm
elif [ -f /usr/bin/innobackupex ] && [ `ps -ef|grep -v grep |grep -c /usr/bin/innobackupex` -ge 1 ];then
echo "xtrabackup process already exist."
exit 1
fi
complete_bak ()
{
cd ${BACKUP_PATH}
${INNOBACKUPEX} --user=${DBUSER} --password=${DBPWD} --port=${PORT} --host=${DBIP} --defaults-file=${MYCNF} --stream=tar ${BACKUP_PATH}/ 2>>${LOG_FILE} |gzip >${BACKUP_PATH}/${all_bak}_${P
ORT}.tar.gz
if [ $? -eq 0 ] && [ `tail -10 "${LOG_FILE}" | grep -ic "completed OK"` -eq 1 ];then
tar zxvfi ${BACKUP_PATH}/${all_bak}_${PORT}.tar.gz xtrabackup_checkpoints
echo "${all_bak}_${PORT}.tar.gz backup successed" >>${bak_log}
echo -en "ok\n${all_bak}_${PORT}.tar.gz" >${MYSQL_LOG}
sh /home/msbakscript/redis_backup.sh
chown nobody.nobody *
else
echo "${all_bak}_${PORT}.tar.gz backup failed" >>${bak_log}
echo -e "failure\n${all_bak}_${PORT}.tar.gz" >${MYSQL_LOG}
exit 1
fi
}
complete_bak
increase_bak ()
{
cd ${BACKUP_PATH}
if [ ! -f ${BACKUP_PATH}/xtrabackup_checkpoints -o -z ${BACKUP_PATH}/xtrabackup_checkpoints ];then
echo "xtrabackup_checkpoints does not exist" >>/{LOG_FILE}
complete_bak
exit 0
fi
${INNOBACKUPEX} --user=${DBUSER} --password=${DBPWD} --port=${PORT} --host=${DBIP} --defaults-file=${MYCNF} --no-timestamp --incremental --throttle=30 ${BACKUP_PATH}/${increase_bak}_${PORT
} --incremental-lsn=${CHECKPOINT} >>${LOG_FILE} 2>&1if [ $? -eq 0 ] && [ `tail -10 "${LOG_FILE}" | grep -ic "completed OK"` -eq 1 ];then
tar zcfi ${increase_bak}_${PORT}.tar.gz ${increase_bak}/
rm -rf ${increase_bak}_${PORT}
echo "${increase_bak}_${PORT}.tar.gz backup successed" >>${bak_log}
echo -e "ok\n${increase_bak}_${PORT}.tar.gz" >${MYSQL_LOG}
else
echo "${increase_bak}_${PORT} backup failed" >>${bak_log}
echo -e "failure\n${increase_bak}_${PORT}.tar.gz" >${MYSQL_LOG}
exit 1
fi
}
increase_bak
del_bakfile ()
{
for dbfile in `find "${BACKUP_PATH}/" -name "[0-9]*.tar.gz" -type f -mtime +${DAY}`; do
rm -f ${dbfile}
done
}
del_bakfile
bakmysql ()
{
for i in ${exclude_time[@]};do
[ "${Hour}" -eq "$i" ] && exit 0
done
if [ "${Hour}" -eq "${default_time}" ];then
complete_bak
elif [ "${Hour}" -ne "${default_time}" -a ${open_increase_bak} -eq 0 ];then
increase_bak
else
exit 0
fi
}
bakmysql
case $# in
0)
bakmysql
del_bakfile
;;
1)
if [ "$1" = "all" ];then
complete_bak
del_bakfile
elif [ "$1" = "inc" ];then
increase_bak
del_bakfile
else
echo "sh $0 all or sh $0 inc" && exit 0
fi
;;
esac
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！