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_2024-06-27.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_2024-06-28.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</opt/auth.sql

[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 </opt/all-data.sql

二：增量备份与恢复

增量备份（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 </opt/auth.sql

[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