Coding & Life

求知若饥，虚心若愚

0%

MySQL全量、增量备份与恢复

Posted on Edited on

数据安全重要性无需多言，本文讲解如何进行MySQL的备份与恢复

全量备份

逻辑备份

可以使用mysqldump直接备份所有库或者某一个库或者某一个库中的某个表

备份所有库

1
mysqldump -uroot -p --lock-all-tables --all-databases > all.sql

备份某一个库(test库)

1
mysqldump -uroot -p --lock-all-tables --databases test > test.sql

备份某一个库(test)中某一张(hy_express)表

1
mysqldump -uroot -p --lock-all-tables test hy_express > test.press.sql

--lock-all-tables保证在导出过程中整个mysql实例加全局读锁，操作线上数据库谨慎添加该参数

增量备份

增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加或者被修改的文件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件

增量备份是使用bin-log日志进行备份的，所以需要开启bin-log日志

开启bin-log

1
2
3
4
5
[mysqld]
log_bin=/var/mysql/mysql-bin
server-id=1
max_binlog_size=100m
binlog_format=row
  • log_bin日志路径，注意此路径必须mysql用户拥有读写权限
  • server-id服务唯一标识，可以随机填写，但不要重复
  • max_binlog_size单文件大小，超过此设置，生成新的文件。同时当MySQL数据库重启时，也会生成新的日志文件，文件序号递增
  • binlog_format设置记录模式。

binlog的一些常用操作

1
mysql> show master logs; #查看数据库所有日志文件

1
mysql> show binlog events in 'mysql-bin.000015'; #查看某个binlog文件信息

1
mysql> flush logs; #将缓存中的日志写磁盘，保存到当前binlog文件中，并产生一个新的binlog文件
1
mysql> flush logs; reset master; #删除所有binlog，并重新开始记录

实例

准备全量数据

1
2
3
4
mysql> create database t1;
mysql> use t1;
mysql> create table full(c1 int(10), c2 varchar(20)) engine=innodb;
mysql> insert into full values(1, 'full1'), (2, 'full2');

全量数据备份

  1. 备份前，数据库加读锁，防止数据在备份时写入

    1
    mysql> flush tables with read lock;

  2. 将binlog刷盘，写入当前binlog(mysql-bin.000001)，再生成一个新的binlog

    1
    mysql> flush logs;

  3. 全量备份

    1
    [root] mysqldump -uroot -p --lock-all-tables --databases t1 > t1.sql

  4. 解除读锁

    1
    mysql> unlock tables;

至此，全量备份结束。将全量数据文件t1.sql保存即可。数据库再有新的数据更新，会记录在mysql-bin.000002文件中

准备增量数据

1
2
3
4
mysql> create database t2;
mysql> use t2;
mysql> create table increment(c1 int(10), c2 varchar(20)) engine=innodb;
mysql> insert into increment values(3, 'increment1'), (4, 'increment2');

将第一份增量数据进行备份

  1. 备份前，数据库加读锁，防止数据在备份时写入

    1
    mysql> flush tables with read lock;

  2. 将binlog刷盘，写入当前binlog(mysql-bin.000002)，再生成一个新的binlog

    1
    mysql> flush logs;

  3. 将增备文件(mysql-bin.000002)直接复制保存即可

  4. 解除读锁

    1
    mysql> unlock tables;

准备第二份增量数据

1
2
mysql> use t2;
mysql> insert into increment values(5, 'increment3'), (6, 'increment4');

将第二份增量数据备份

步骤同上一步，生成mysql-bin.000003文件，复制保存

模拟故障

删除t1和t2库

1
2
mysql> drop database t1;
mysql> drop database t2;

数据恢复

还原全量备份数据

1
mysql -uroot -p < t1.sql;

此时，查看数据，恢复成功

还原增量备份数据

1
mysqlbinlog mysql-bin.000002 | mysql -uroot -p

查看结果，恢复成功

还原第二份增量

方法同上，查看数据

至此数据全部还原成功！

mysqlbinlog按照时间点和位置点恢复

按位置点恢复

1
mysqlbinlog --start-position=500 --stop-position=716 mysql-bin.000002 | mysql -uroot -p

按时间点恢复

1
mysqlbinlog --start-datetime='2022-07-29 15:45:59'  --stop-datetime='2022-07-29 15:46:24' mysql-bin.000002 | mysql -uroot -p

生产中建议定期使用全量备份，然后搭配增量备份的方式来保证数据的安全性。附上全量备份脚本，在进行全量备份后对binlog进行刷盘，这样方便搭配恢复。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#!/bin/bash
#获取当前时间
date_now=$(date "+%Y%m%d-%H%M%S")
backUpFolder=/root/backup/full
username="root"
password="xxx"
db_name="test"
#日志记录文件
logFile=/root/backup/full_bak.log
#定义备份文件名
fileName="${db_name}_${date_now}.sql"
#定义备份文件目录
backUpFileName="${backUpFolder}/${fileName}"
echo "starting backup mysql ${db_name} at ${date_now}." >> $logFile
/usr/bin/mysqldump -u${username} -p${password}  --lock-all-tables --flush-logs --databases ${db_name} > ${backUpFileName}
#进入到备份文件目录
cd ${backUpFolder}
#压缩备份文件
tar zcvf ${fileName}.tar.gz ${fileName}

date_end=$(date "+%Y%m%d-%H%M%S")
echo "finish backup mysql database ${db_name} at ${date_end}. filename is ${backUpFileName}" >> $logFile
echo "" >> $logFile