▪ 数据备份的重要性
▪ 在生产环境中,数据的安全性至关重要
▪ 任何数据的丢失都可能产生严重的后果
▪ 造成数据丢失的原因
程序错误
人为操作错误
运算错误
磁盘故障
灾难(如火灾、地震)和盗窃
▪ 数据库备份的分类
▪ 从物理与逻辑的角度,备份可分为
物理备份:对数据库操作系统的物理文件(如数据文件、日志文件等)的备份
▪ 物理备份方法
冷备份(脱机备份):是在关闭数据库的时候进行的
热备份(联机备份):数据库处于运行状态,依赖于数据库的日志文件
温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作的
▪ 逻辑备份:对数据库逻辑组件(如:表等数据库对象)的备份
从逻辑库的备份策略角度,备份可分为
完全备份:每次对数据库进行完整的备份
差异备份:备份自从上次完全备份之后被修改过的文件
增量备份:只有在上次完全备份或者增量备份后被修改的文件才会被备份
▪ 常见的备份方法
▪ 物理冷备
备份时数据库处于关闭状态,直接打包数据库文件
备份速度快,恢复时也是最简单的
▪ 专用备份工具mydump或mysqlhotcopy
mysqldump常用的逻辑备份工具
mysqlhotcopy仅拥有备份MyLSAM和ARCHIVE表
▪ 启用二进制日志进行增量备份
进行增量备份,需要刷新二进制日志
▪ 第三方工具备份
免费的MySQL热备份软禁Percona XtraBackup
MySQL备份与还原
一:数据库完全备份操作
1:部署实验环境
(1)创建实验所需的库和表
create database auth;
use auth;
create table users (user_name char(16) not null,user_passwd char(48) default '');
insert into users values('zhangsan',password('123456'));
insert into users values('lisi',password('123456'));
1:物理冷备份与恢复
(1)备份数据库
[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# mkdir /backup
[root@localhost ~]# cd /usr/local/
[root@localhost local]# tar zcvf /backup/mysql_$(date +%F).tar.gz mysql/
[root@localhost local]# cd /backup/
[root@localhost backup]# ls
mysql_2023-06-08.tar.gz
(2)模拟故障
[root@localhost backup]# systemctl start mysqld
[root@localhost backup]# mysql -u root -p -e 'drop database auth;'
[root@localhost backup]# mysql -u root -p -e 'select * from auth.users;'
(3)恢复数据库
[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# cd /backup/
[root@localhost backup]# tar zxvf mysql_2023-06-08.tar.gz
[root@localhost backup]# cd /usr/local/
[root@localhost local]# mv mysql/ mysql.bak
[root@localhost local]# cd /backup/
[root@localhost backup]# mv mysql /usr/local/
[root@localhost backup]# systemctl start mysqld
[root@localhost backup]# mysql -u root -p -e 'select * from auth.users;'
2:mysqldump备份与恢复
在服务启动状态下使用
(1)导出数据
[root@localhost ~]# mysqldump -u root -p auth users > /opt/auth-users.sql \\导出数据表
[root@localhost ~]# mysqldump -u root -p auth > /opt/auth.sql \\导出数据库
[root@localhost ~]# mysqldump -u root -p --opt --all-databases >/opt/all-data.sql ##备份所有库
注释:
--opt:优化执行速度
(2)导入表
如果已经存在数据库,可以将表导入到任何一个现有的数据库
[root@localhost ~]# mysql -u root -p -e 'create database test;'
[root@localhost ~]# mysql -u root -p test < /opt/auth-users.sql ##导入到其他库
[root@localhost ~]# mysql -u root -p auth < /opt/auth-users.sql ##导入到原始所在的库
[root@localhost ~]# mysql -u root -p -e 'show tables from test;'
(3)导入库
如果备份了完整的数据库,可以在mysql中没有该库信息的情况下直接导入该库
[root@localhost ~]# mysql -u root -p -e 'drop database auth;'
[root@localhost ~]# mysql -u root -p -e 'show databases;'
[root@localhost ~]# mysql -u root -p -e 'create database auth;'
[root@localhost ~]# mysql -u root -p auth
[root@localhost ~]# mysql -u root -p -e 'show databases;'
[root@localhost ~]# mysql -uroot -p -e 'select * from auth.users;'
(4)全库还原
保证至少已经创建了这些库
[root@localhost ~]# mysql -u root -p
二:增量备份与恢复
增量备份(incremental backup)是备份的一个类型,是指在一次完全备份或上一次增量备份后,以后每次的备份只需备份与前一次相比增加或者被修改的文件。
1:启用二进制日志功能
(1)修改主配置文件
[root@localhost ~]# vim /etc/my.cnf
在[mysqld]模块中添加
log-bin=mysql-bin
server-id=1
binlog_format = MIXED
[root@localhost ~]# systemctl restart mysqld
备注:
开启mysql事务日志的方法二:
在my.cnf中增加server-id字段
使用系统命令行执行mysqld --user=mysql --log-bin=/usr/local/mysql/data/文件前缀
然后重新启动mysql服务。
(2)生成新的日志文件
[root@localhost ~]# cd /usr/local/mysql/data/
[root@localhost data]# mysqladmin -u root -p flush-logs
(3)查看日志内容
mysqlbinlog --no-defaults mysql-bin.000002
注意:如果sql语句被加密了,可以使用以下指令
mysqlbinlog --no-defaults --base64-output=DECODE-ROWS mysql-bin.000002
2:向表添加数据(多添加一些)
mysql> use auth;
为了验证效果,逐个添加新的数据,并生成一个日志文件
use auth;
insert into users values('aaa','123456');
insert into users values('bbb','123456');
insert into users values('ccc','123456');
insert into users values('ddd','123456');
insert into users values('eee','123456');
insert into users values('fff','123456');
[root@localhost data]# mysqladmin -u root -p flush-logs
mysqlbinlog --no-defaults --base64-output=DECODE-ROWS mysql-bin.000002
3:模拟误操作将auth.users表删掉
[root@localhost data]# mysql -u root -p -e 'drop table auth.users;'
[root@localhost data]# mysql -u root -p -e 'select * from auth.users;' ##查看表,已经不存在
4:常规恢复操作
(1)先恢复完全备份
[root@localhost data]# mysql -u root -p auth
[root@localhost data]# mysql -u root -p -e 'select * from auth.users;' ##查看表,已经存在,但数据不全
(2)再恢复增量备份
[root@localhost mysql]# mysqlbinlog --no-defaults --base64-output=DECODE-ROWS mysql-bin.000002
注意:要先查看日志内容,确定是需要恢复的数据
[root@localhost mysql]# mysqlbinlog --no-defaults mysql-bin.000002 | mysql -u root -p
[root@localhost mysql]# mysql -u root -p -e 'select * from auth.users;' ##查看表的内容
注释:
--no-defaults:不读取任何选项文件,其后需要直接跟日志文件或开始停止位置
5:基于位置的恢复
(1)模拟误操作
[root@localhost mysql]# mysql -uroot -p -e 'drop table auth.users;'
[root@localhost mysql]# mysql -u root -p -e 'select * from auth.users;' ##没有了数据
(2)恢复完全备份
[root@localhost mysql]# mysql -u root -p auth < /opt/auth.sql
(3)查看日志内容
[root@localhost mysql]# mysqlbinlog --no-defaults mysql-bin.000002
从日志中分析哪些数据还没有恢复,找出想要恢复到哪个数据的操作ID
[root@localhost mysql]# mysqlbinlog --no-defaults --stop-position='521' mysql-bin.000002 | mysql -u root -p
(4)恢复到指定的地方
[root@localhost mysql]# mysqlbinlog --no-defaults --stop-position='521' mysql-bin.000002 | mysql -u root -p ##恢复到521之前的一个数据,不包含521
[root@localhost mysql]# mysql -u root -p -e 'select * from auth.users;'
(5)从指定的位置向后开始恢复
[root@localhost mysql]# mysqlbinlog --no-defaults --start-position='521' mysql-bin.000002 | mysql -u root -p ##从521之后开始恢复,包含521
(6)恢复指定范围内的数据
[root@localhost mysql]# mysqlbinlog --no-defaults --start-position='521' --stop-position='729' mysql-bin.000002 | mysql -u root -p
6:基于时间点恢复
(1)恢复到指定的时间
[root@localhost mysql]# mysqlbinlog --no-defaults --stop-datetime='2019-03-08 21:01:55' mysql-bin.000002 | mysql -u root -p
(2)从指定的时间开始恢复
[root@localhost mysql]# mysqlbinlog --no-defaults --start-datetime='2019-03-08 21:01:53' mysql-bin.000002 | mysql -u root -p
(3)恢复指定时间范围内的数据
[root@localhost mysql]# mysqlbinlog --no-defaults --start-datetime='2019-03-08 21:01:53' --stop-datetime='2019-03-08 21:01:55' mysql-bin.000002 | mysql -u root -p