首页 > 代码库 > MySQL利用xtrabackup和binlog 备份根据时间点恢复
MySQL利用xtrabackup和binlog 备份根据时间点恢复
#!/bin/bash
#定义变量
MYSQL_HOST=192.168.56.21
MYSQL_USERNAME=root
MYSQL_PORT=3306
MYSQL_PASSPWD=oracle
MYSQL_SOCK=/data/mysql/3306/tmp/3306.sock
MYSQL_CNF=/data/mysql/3306/my3306.cnf
MYSQL_DATADIR=`cat ${MYSQL_CNF}|grep datadir|awk -F "=" ‘{print $2}‘ `
#MYSQL_BACKUPDIR=$1
#MYSQL_DATATIME=$2
MYSQL_BACKUPDIR=/data/backup/2017-04-26_01-03-49
MYSQL_DATATIME="2017-04-26 01:05:22"
MYSQL_POSDIR=${MYSQL_BACKUPDIR}/xtrabackup_info
#获取binlog position
MYSQL_POS=`cat ${MYSQL_POSDIR} |grep position |awk -F "," ‘{print $2}‘| awk -F " " ‘{print $2}‘ `
#恢复全备
innobackupex --defaults-file=${MYSQL_CNF} --user=${MYSQL_USERNAME} --password=${MYSQL_PASSPWD} --copy-back ${MYSQL_BACKUPDIR}
修改数据目录datadir 权限
chown -R mysql:mysql ${MYSQL_DATADIR}
#启动MySQL
mysqld --defaults-file=${MYSQL_CNF} &
#利用mysqlbinlog基因时间点恢复
mysqlbinlog --start-position=${MYSQL_POS} --stop-datetime="${MYSQL_DATATIME}" |mysql -S ${MYSQL_SOCK} -u${MYSQL_USERNAME} -p${MYSQL_PASSPWD}
######################## 下面的内容请忽略
#全备
# innobackupex --defaults-file=/data/mysql/3306/my3306.cnf --user=root --password=oracle /data/backup
mysqlbinlog --start-position=120 --stop-datetime=‘2017-04-26 1:05:22‘ /data/mysql/3306/logs/mysql-bin.000043 /data/mysql/3306/logs/mysql-bin.000044 /data/mysql/3306/logs/mysql-bin.000045 /data/mysql/3306/logs/mysql-bin.000046 | mysql -S /data/mysql/3306/tmp/3306.sock -uroot -poracle
mysqlbinlog --start-position=${MYSQL_POS} --stop-datetime="${MYSQL_DATATIME}" /data/mysql/3306/logs/mysql-bin.000043 /data/mysql/3306/logs/mysql-bin.000044 /data/mysql/3306/logs/mysql-bin.000045 /data/mysql/3306/logs/mysql-bin.000046 |mysql -S ${MYSQL_SOCK} -u${MYSQL_USERNAME} -p${MYSQL_PASSPWD}
rm -rf /data/mysql/3306/data/*
pkill mysql
MySQL利用xtrabackup和binlog 备份根据时间点恢复