MySQL使用mysqldump备份及还原

MySQL可以使用mysqldump进行数据的逻辑备份，配合开启bin log日志可以实现数据的全量恢复及增量恢复

　　MySQL版本查看

　　修改配置文件记录bin log日志

1 2 3 4 5 6 7 8 9

[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终端查看日志模式

1	show variables like "binlog_format";

　　查看bin log日志的POS值

1	show master status;

　　制定备份脚本每日定时备份，把该脚本运行定时任务每日凌晨全备并且在备份是时候记录bin log的POS位置值

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

#!/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行左右）

　　使用全备进行全量恢复，恢复前需关闭前端应用已避免数据混乱

1	mysql -uroot -p123456 -h127.0.0.1 < 20190117.sql

　　使用bin log进行增量恢复，为测试恢复效果在master主机上面新建数据库及表

1 2 3 4 5

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值

1	mysqlbinlog mysql-bin.001446 --start-position=194 > 20190117binlog.sql

　　还原

1	mysql -uroot -p123456 -h127.0.0.1 < 20190117binlog.sql

　　查看是否还原

　　全量及增量备份恢复成功

 　　PS：如果想针对单库进行增量恢复可以在mysqbinlog到处的时候加参数-d 例如

1	mysqlbinlog -d test mysql-bin.001446