mysql备份与恢复

备份分为增量备份和全量备份

一般我们设置每天一次增量备份和7天一次全量备份。如果是误删恢复的话，则需要找出binlog点，根据之前的备份使用“全备+binlog”恢复的方法恢复数据。

一、增量备份

1、登录mysql查看binlog状态

show variables like ‘%log_bin%’;

2、如果没打开，首先要开启binlog，在my.cnf中配置后重启mysql

[mysqld]  
log-bin=mysql-bin 
server-id = 1(单个节点id)
expire_logs_days = 10(表示此日志保存时间为10天)

或是免重启： set global expire_logs_days=10; 

说明：Binlog日志包括两类文件；第一个是二进制索引文件(后缀名为.index),第二个为日志文件(后缀名为.00000*),记录数据库所有的DDL和DML(除了查询语句select)语句事件

 

binlog有关sql语句：

• show master logs; 查看所有binlog日志文件列表
• show master status; 查看最后一个binlog日志的编号名称及其最后一个操作事件pos结束点的值
• Flush logs  刷新日志，此刻开始产生一个新编号的binlog文件（每当mysqld服务重启时，会自动执行刷新binlog日志命令，mysqldump备份数据时加-F选项也会刷新binlog日志）
• reset master 清空所有binlog日志命令:

二、增量备份恢复

通过pos点恢复：

1、查询出pos点位置（下面注有查询方法）

2、执行增量恢复命令：mysqlbinlog  --stop-position=435 --database=testdb  mysql-bin.000006 | mysql -uroot -p密码 -v testdb （这种是直接恢复到数据库中，下面也能用管道符直接恢复。 ）

通过时间恢复：

1、执行命令binlog转换成sql语句：mysqlbinlog mysql-bin.000012 --database=testdb --start-datetime='2020-07-20 00:00:01' --stop-datetime='2020-07-24 23:59:59' > testdb.sql

2、执行sql语句（这种是先导出sql语句再恢复到数据库中 ）

参数解释：

• database 为要恢复的数据库名称
• -start-datetime 恢复的起始时间
• -stop-datetime 恢复的结束时间
• --start-position 恢复的起始pos点
• --stop-position 恢复的结束pos点

注：binlog内容有两种查看方法

1、使用mysqlbinlog命令执行：mysqlbinlog mysql_bin.000001 

2、登录mysql后使用：show binlog events in ‘mysql-bin.000003’ from 406 limit 2,4\G;(查询mysql-bin.000003内容，从pos点406开始查询，中间跳过2行，查询4条数据,\G为了美观)

 

三、全量备份

脚本如下:backup.sql

#删除一个月前的备份
find /mnt/database_backup -mtime +30 -type f -name 'all*.sql.gz' -exec rm -rf {} \;
datetime=$(date "+%y%m%d-%H:%M:%S")
#备份数据库
/usr/local/mysql/bin/mysqldump --opt -R --single-transaction  -utestdb_gd -p123456 --databases testdb testdb2  | gzip > /mnt/database_backup/testdb_all$datetime.sql.gz

定时任务：crontab -e

0 0 * * 1 /mnt/database_backup/backup.sh

 

四、全量备份恢复

mysql -uroot -p123456 < /root/testdb.sql