(五)进入MySQL 【备份与还原】

一、据备份的重要性

1、备份的主要目的是灾难恢复

2、在生产环境中，数据的安全性至关重要

3、任何数据的丢失都可能产生严重的后果

4、造成数据丢失的原因 程序错误 人为操作错误 运算错误 磁盘故障 灾难 (如火灾、地震) 和盗窃

二、数据库备份类型

差异备份

当第一次完全备份后，后面每次修改都会备份，且基于第一次完全备份后为基准

完全备份  完全备份  +修改1     完全备份  +修改1+ 修改1+修改2

企业几乎不用，不稳定

三、实际操作

create database hhhhh;

use hhhhh;

create table if not exists info1(
id int(4) not  null auto_increment,
name varchar(10) not null,
age char(10) not null,
hobby varchar(50),
primary key(id));

show tables;

前置准备

3.1、物理冷备份与恢复

#先关闭mysql的运行
systemctl stop mysqld;
yum -y install xz

#tar备份#或者他的下面的库
tar cJvf /opt/mysql_all_$(date +%F).tar.xz  /usr/local/mysql/data

删除数据实验

mv /usr/local/mysql/data/* /opt

可以开一下mysql

再进入

恢复

 #解压恢复
tar xJvf  /opt/mysql_all_你的日期.tar.xz -C /usr/local/mysql
cd /usr/local/mysql/
mv usr/local/mysql/data/*   data/

开启再次查看

systemctl start mysqld.service

补充，在 tar 命令中，小写 j 和大写 J 是有区别的，它们分别用于处理不同类型的压缩文件。

• 小写 j (-j)：这个选项告诉 tar 命令使用 bzip2 程序来压缩或解压缩文件。当你想要处理 .tar.bz2 或 .tbz2 这类使用 bzip2 压缩的归档文件时，应该使用 -j 选项。

• 大写 J (-J)：这个选项则是用于处理 .tar.xz 这类使用 xz 压缩的归档文件

3.2、mysqldump 备份与恢复（温备份）

use hhhhh;
create table info2 (id int,name char(10),age int,sex char(4));
insert into info2 values(1,'user',11,'男');
insert into info2 values(2,'user',12,'女');

(1)、完全备份一个或多个完整的库 (包括其中所有的表)

mysqldump -u root -p[密码] --databases 库名1 [库名2] ... > /备份路径/备份文件名.sql

#备份一个hhh库 结构和数据   有--databases代表连同库一起备份，可以多个库一起

mysqldump -u root -pabc123 --databases  hhhhh >/opt/hhh.sql 

(2)、完全备份 MySQL 服务器中所有的库

mysqldump -u root -p[密码] --all-databases > /备份路径/备份文件名.sql

例: mysqldump -u root -p --all-databases  > /opt/all.sql

主要就是   --all-databases

(3)、完全备份指定库中的部分表

mysqldump -u root -p[密码] 库名 [表名1] [表名2] ... > /备份路径/备份文件名.sql

例:

mysqldump -u root -pabc123   hhhhh info1 info2 > /opt/hhhhh_info1.sql

#使用“-d”选项，说明只保存数据库的表结构，只做为一个表结构模板

#不使用“-d"选项，说明表数据也进行备份

(4)查看备份文件

grep -v "^--" /opt/hhhhh_info1.sql | grep -v "^/" | grep -v "^$"

恢复如下，Mysql 完全恢复

使用mysqldump导出的文件，可使用导入的方法恢复

• source命令
• mysql命令恢复

使用source恢复数据库的步骤

#模拟删除 -e是后面跟mysql语法

mysql -u root -pabc123 -e 'drop table hhhhh.info1';

进入mysql

mysql -u root -pabc123

use hhhhh；

查看少了一张表

show tables；

source /opt/hhhhh_info1.sql

select * from info1;

使用mysql恢复数据库的步骤

大同小异恢复如下

mysql -u root -pabc123  hhhhh< /opt/hhhhh_02.sql

.在生产环境中，可以使用Shell脚本自动实现定时备份（时间频率需要确认）

0 1 * * 6 /usr/local/mysql/bin/mysqldump -uroot -pabc123 库 info1 > ./库_infol_$(date +%Y%m%d).sql ;

/usr/local/mysql/bin/mysqladmin -u root -p flush-logs

四、MySQL数据库增量恢复

4.1、增量恢复的三种

1.一般恢复           将所有备份的二进制日志内容全部恢复

2.基于位置恢复    数据库在某一时间点可能既有错误的操作也有正确的操作 可以基于精准的位置跳过错误的操作 发生错误节点之前的一个节点，上一次正确操作的位置点停止

3.基于时间点恢复 跳过某个发生错误的时间点实现数据恢复 在错误时间点停止，在下一个正确时间点开始

vim /etc/my.cnf

[mysqld]
#打开二进制日志
log-bin=mysql-bin
#可选，指定二进制日志(binlog)的记录格式为MIXED（混合输入）
binlog_format = MIXED
server-id = 1

systemctl restart mysqld

重启就会产生二进制日志

4.2、二进制日志有3种不同的记录格式

#二进制日志(binlog)有3种不同的记录格式:

• 混合模式（Mixed）：推荐使用的模式，结合了基于语句和基于行的日志的优点。
• 基于行（ROWS）：记录数据行的变化，适用于大量数据变更的场景。
• 基于SQL（默认）：记录引起数据变化的SQL语句。

默认格式是STATEMENT，常用是MIXED

① STATEMENT（基于SQL语句）: 每一条涉及到被修改的sql 都会记录在binlog中

缺点：日志量过大，如sleep()函数，last_insert_id()>，以及user-defined fuctions（udf）、 主从复制等架构记录日志时会出现问题

总结：增删改查通过sql语句来实现记录，如果用高并发可能会出错，可能时间差异或者延迟，可能不是我们 想想的恢复可能你先删除或者在修改，可能会倒过来。准确率底

② ROW(基于行) 只记录变动的记录，不记录sql的上下文环境

缺点:如果遇到update......set....where true 那么binlog的数据量会越来越大

总结：update、delete以多行数据起作用，来用行记录下来， 只记录变动的记录，不记录sql的上下文环境， 比如sql语句记录一行，但是ROW就可能记录10行，但是准确性高，高并发的时候由于操作量，性能变低 比较 大所以记录都记下来，

③ MIXED 推荐使用

一般的语句使用statement，函数使用ROW方式存储。

4.3、查看二进制日志文件的内容

cp /usr/local/mysql/data/mysql-bin.000001 /opt

① mysqlbinlog --no-defaults /opt/mysql-bin.000001

或

mysqlbinlog  --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000001

也可以查重定向到文件内方便查看

一些解释

#--base64-output=decode-rows：使用64位编码机制去解码（decode）并按行读取（rows）

#-v: 显示详细内容

#--no-defaults : 默认字符集（不加会报UTF-8的错误）

PS: 可以将解码后的文件导出为txt格式，方便查阅 mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002 > /opt/mysql-bin.000002

例如，statement模式记录量较少，但有可能会因为没有记录下所有细节而产生问题；

ROW模式可以记录下所 有细节，但是记录量可能会非常大。所以在实际使用中需要根据情况选择适合的模式

二进制日志中需要关注的部分

1、at ：开始的位置点

2、end_log_pos：结束的位置

3、时间戳：240902 22:28:21

4、SQL语句

4.4、增量备份

1、增量备份时基于完全备份的（还是开始需要完全备份的），所以我们直接完全备份数据库

mysqldump -uroot -p hhhhh info1 > /opt/hhhhh_info1_$(date +%F).sql

mysqldump -uroot -p123123 hhhhh> /opt/hhhhh_all_$(date +%F).sql

2、然后可每天进行增量备份操作，生成新的二进制日志文件(例如:mysql-bin.000002)也就是

刷新并关闭当前的二进制日志（binary log）文件，并立即开启一个新的日志文件

mysqladmin -u root -p flush-log

3、一些操作  增删改查

4、再次生成新的二进制日志文件(例如:mysql-bin.000003)

mysqladmin -u root -p flush-logs

4.5、增量恢复

(1)、一般恢复

1、模拟丢失更改的数据的恢复步骤

直接使用恢复即可

比如不小心删除xxx库中test1表

mysql -uroot -p xxx库 test1< test1_info.sql

2、丢失所有数据的恢复步骤

mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p

(2)、基于位置恢复

比如操作test1表插入3,4,5发现4，不需要，需要跳过

1、刷新二进制日志并删除test1表，确认位置点，

mysqladmin -uroot -pabc123  flush-logs

drop table 库.test1;

找基点

  #恢复从位置为400开始到位置为623为止

mysqlbinlog --no-defaults --start-position='400' --stop-position='623' /opt/mysql-bin.000002 | mysql -uroot -p  

(3)、基于时间点恢复

mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' --stop datetime='年-月-日小时:分钟:秒' 二进制日志 | mysql -u 用户名 -p 密码

如果恢复某条SQL语之前的所有数据，就stop在这个语句的位置节点或者时间点   到..结束

如果恢复某条SQL语句以及之后的所有数据，就从这个语句的位置节点或者时间点start   从..开始

五、总结

mysql备份与恢复:

备份的类型: 1、物理备份、逻辑备份 物理备份:冷备、温备、热备

1）冷备: tar，需要关闭mysql服务，或者确保mysql服务在进行tar备份时，没有客户端的写入操作 2）温备: mysqldump，这个是mysql自带的备份工具，特性，逐表备份，每备份一张表时，会先drop 删除，然后重新create 创建表结构，然后再锁表（仅锁定写的操作，但可读），进行insert语句的备 份，备份完成后，进行unlock解锁,然后继续备份下一个

3）热备: xtrabackup mysqlhotcopy等热备工具（第三方)，在mysql正常运行时，进行备份、

---

2、逻辑备份:

1）全量备份: tar、 mysqldump -u -p --all-databases

2）增量备份:主要使用bin-log 二进制日志来完成备份，同时每个增量备份的文件，可以通 过:mysqladmin -u -p flush-logs来刷新生成新的增备的日志文件，同时可以结合crontab完成自动刷新

PS:再进行基于二进制文件的增备恢复时，有必要的话，需要先恢复完备的数据，再逐个恢复增备的数 据，直到恢复至我们需要恢复的数据为止

3）差异备份:需要选择保存日志的格式为ROW（基于行）的备份方式日志保存的类型:

1)混合模式Mixed(建议使用) 2)基于行ROWS 3)基于sql (默认) mysql 恢复的方式:

1) 基于冷备(tar）形式: tar zxvf解压打包的/usr/local/mysql/data数据下的内容

2）基于自带的温备工具:使用mysgldump进行备份后，

可使用两种方式进行恢复

① : mysql -u -p 库名< /opt/mysql_all.sql(备份文件的路径)

② mysql -u -p -e 'source /opt/mysql_all.sql'直接使用source进行恢复

3）基于日志: bin-log，首先开启二进制日志的配置log-bin=mysql-bin log_format=MiXED, 然后在/usr/local/mysql/data MysQL工作目录下，会生成mysql-bin.00000x的二进制日志文件 接着使用mysqlbinlog --no-defaults 二进制日志路径│ mysql -u -p

基于完整的二进制日志文件进行恢复

基于位置点和时间点进行恢复:使用mysqlbinlog --no-defaults --base64-output=decode-rows -v二进 制日志文件路径，查看正确操作和需要跳过的错误操作的at (position位置点）和datetime（时间点）来 进行恢复