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</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