MySQL使用mysqldump备份及还原

MySQL使⽤mysqldump备份及还原
MySQL可以使⽤mysqldump进⾏数据的逻辑备份，配合开启bin log⽇志可以实现数据的全量恢复及增量恢复
MySQL版本查看
修改配置⽂件记录bin log⽇志
[mysqld]
#bin log⽇志记录位置
log-bin=/opt/mysqldata/prodda3306data/mysql-bin
binlog_cache_size = 4M
max_binlog_cache_size = 256M
max_binlog_size = 32M
#⽇志模式为row ⽣产环境最好使⽤此⽇志模式
binlog_format = row
expire_logs_days = 7
登录MySQL终端查看⽇志模式
show variables like "binlog_format";
查看bin log⽇志的POS值
show master status;
制定备份脚本每⽇定时备份，把该脚本运⾏定时任务每⽇凌晨全备并且在备份是时候记录bin log的POS位置值
#!/bin/bash
source /etc/profile
#mysqldump to fully backup mysql data
port=$1
password='password'
if [ $# -ne 1 ];then
echo "Usage: sh all.sh (3306|3307)"
exit 1
fi
psocket=`ps -ef|grep $port|grep "socket"|awk -vRS="--socket" '{t=$0;}END{print "--socket"t}'|awk '{print $1}'`
if [ -f /root/.bash_profile ];then
source /root/.bash_profile
fi
#定义备份⽬录
BakDir=/opt/mysqlbak/full${port}
[ ! -d $BakDir ] && mkdir -p $BakDir
LogFile=$BakDir/bak.log
Date=`date +%Y%m%d`
Begin=`date +"%Y年%m⽉%d⽇ %H:%M:%S"`
cd $BakDir
DumpFile=$Date.sql
GZDumpFile=$Date.sql.tgz
mysqldump -uroot -p"$password" --all-databases --lock-all-tables --routines --triggers --events --master-data=2 --flush-logs $psocket --set-gtid-purged=OFF > $DumpFile tar zcvf $GZDumpFile $DumpFile
if [ -f $DumpFile ];then
rm -rf $DumpFile
fi
Last=`date +"%Y年%m⽉%d⽇ %H:%M:%S"`
echo 开始：$Begin 结束：$Last $GZDumpFile succ >> $LogFile
sleep 1
#删除超过10天的全备⽂件
find $BakDir -name "*.tgz" -mtime +10 -exec rm -rf {} \;
恢复
全备恢复，把备份⽂件解压缩，把解压后的⽂件打开查看POS值（在22⾏左右）
使⽤全备进⾏全量恢复，恢复前需关闭前端应⽤已避免数据混乱
mysql -uroot -p123456 -h127.0.0.1 < 20190117.sql
使⽤bin log进⾏增量恢复，为测试恢复效果在master主机上⾯新建数据库及表
create database test;
use test
create table test(id int);
insert into test values(1);
select * from test;
创建⼀个test库和test表，并且往表⾥⾯插⼊⼀条数据
把mysql-bin.001446及以后的⽇志（如果有的话）都拷贝下来
使⽤mysqlbinlog命令还原⽇志，对于001446需要指定start position值，其他001446以后的⽇志（如果有的话）还原不需要指定start position值
mysqlbinlog mysql-bin.001446 --start-position=194 > 20190117binlog.sql
还原
mysql -uroot -p123456 -h127.0.0.1 < 20190117binlog.sql
查看是否还原
全量及增量备份恢复成功
PS：如果想针对单库进⾏增量恢复可以在mysqbinlog到处的时候加参数-d 例如mysqlbinlog -d test mysql-bin.001446。