MySQL数据库的备份-恢复-日志_备份mysql数据库

一、备份：

1.数据备份的重要性：

• 备份的主要目的是灾难恢复。

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

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

2.造成数据丢失的原因：

• 程序错误
• 人为操作错误
• 运算错误
• 磁盘故障
• 灾难（如火灾、地震）和盗窃

3.数据库备份的分类：

• 从物理与逻辑的角度，备份可分为物理备份和逻辑备份。
• 从数据库的备份策略角度，备份可分为完全备份、差异备份和增量备份。

3.1数据库备份可以分为：

1. 物理备份：

对数据库操作系统的物理文件（如数据文件，日志文件等）的备份

物理备份的方法：

• 冷备份：是在关闭数据库的时候进行（使用tar）
• 热备份：数据库处于运行状态，依赖于数据库的日志文件（在数据库中使用：mysqldump备份）
• 温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作

 2.逻辑备份：

逻辑备份是对数据库逻辑组件的备份。

表示为逻辑数据库结构 这种类型的备份适用于可以编辑数据值或表结构。

逻辑备份的方法：

• 完全备份:每次对数据库进行完整的备份
• 差异备份:备份自从上次完全备份之后被修改过的文件
• 增量备份:只有在上次完全备份或者增量备份后被修改的文件才会被备份

3.2 从数据库的备份策略角度，备份可分为：

1.完全备份：

每次都进行完全备份，会导致备份文件占用巨大的空间，并且有大量的重复数据。

恢复时，直接把文件导入进去即可

2.差异备份：

上次差异备份，都会被备份上一次完全备份之后的数据，可能会出现备份重复数据，导致占用大额外的磁盘空间。

恢复时，先恢复完全备份，在导入差异备份数据

3.增量备份：

每次增量备份数据都是备份在上一次完全备份或增量备份之后的数据，不会出现备份重复数据，也不会占用额外的磁盘空间

恢复时，需要完全恢复，在做增量恢复，按照次序恢复

4.常见的备份方法：

1.物理冷备（完全备份）：

• 备份时数据库处于关闭状态，直接打包数据库文件
• 备份速度快，恢复时也是最简单的

2.专用备份工具mydump或mysqlhotcopy （完全备份，逻辑备份）：

• mysqldump常用的逻辑备份工具 （导出为sql脚本）

• mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表

• 启用二进制日志进行增量备份 （增量备份）

• 进行增量备份，需要刷新二进制日志

3.第三方工具备份：

• 免费的MySQL热备份软件Percona XtraBackup（阿里云的工具：dts，支持热迁移）

 二、MySQL完全备份：

1.完全备份介绍：

• 完全备份是对整个数据库、数据库结构和文件结构的备份
• 保存的是备份完成时刻的数据库
• 是差异备份与增量备份的基础

2.完全备份的优缺点：

优点：

• 备份与恢复操作简单方便

缺点：

• 数据存在大量的重复
• 占用大量的备份空间
• 备份与恢复时间长

3.完全备份的方法：

3.1 物理冷备份与恢复：

1. 关闭MySQL数据库
2. 使用 tar 命令直接打包数据库文件夹
3. 直接替换现有MySQL目录即可

3.2 mysqldump备份与恢复：

1. MySQL自带的备份工具，可方便实现对MySQL的备份
2. 可以将指定的库、表导出为SQL脚本
3. 使用命令mysq|导入备份的数据

4.完全备份的方法与恢复操作：

4.1 物理冷备份与恢复：

先关闭数据库方便库外操作：

备份：

恢复：

4.2 mysqldump 备份与恢复：

备份：

4.2.1  完全备份一个或多个完整的库（包括其中所有的表）：

mysqldump -u root -p[密码] --databases 库名1 [库名2] ... > /备份路径/备份文件名.sql    
 
#导出的就是数据库脚本文件

4.2.2 完全备份MySQL服务器中所有的库：

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

 查看：

4.2.3 完全备份指定库中的部份表:

mysqldump -u root -p[密码] [-d] 库名 [表名1] [表名2] ... > /备份路径/备份文件名.sql
 
#使用 -d 选项，说明只保存数据库的表结构
#不使用 -d 选项，说明表数据也进行备份

恢复：

4.2.4 使用 source 命令恢复数据：

删除表：

在MySQL内查看： 

#恢复数据
mysql> source /data/kgc.sql;

查看： 4

4.2.5 重定向导入备份文件：

查看：

三、MySQL 增量备份与恢复：

 1.增量备份的介绍：

1.1 增量备份产生的原因 ：

• 使用 mysqldump 进行完全备份存在的问题
  • 备份数据中有重复数据
  • 备份时间与恢复时间过长

1.2 增量备份是什么：

• 是自上一次备份后增加/变化的文件或者内容

1.3 增量备份的特点：

• 没有重复数据，备份量不大，时间短
• 恢复需要上次完全备份及完全备份之后所有的增量备份才 能恢复，而且要对所有增量备份进行逐个反推恢复

1.4 增量备份的过程 ：

• MySQL没有提供直接的增量备份方法
• 可通过MySQL提供的二进制日志间接实现增量备份

MySQL二进制日志对备份的意义：

• 二进制日志保存了所有更新或者可能更新数据库的操作
• 二进制日志在启动MySQL服务器后开始记录，并在文件达到 max_binlog_size 所设置的大小或者接收到 flush logs 命令后重新创建新的日志文件
• 只需定时执行 flush logs 方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份

2.增量备份的方式：

• 一般恢复

  • 将所有备份的二进制日志内容全部恢复

• 基于位置恢复

  • 数据库在某一时间点可能既有错误的操作也有正确的操作
  • 可以基于精准的位置跳过错误的操作

• 基于时间点恢复

  • 跳过某个发生错误的时间点实现数据恢复

四、MySQL日志管理：

• MySQL 的日志默认保存位置为 /usr/local/mysql/data 
• MySQL 的日志配置文件为 /etc/my.cnf

1.日志的分类：

1.1 错误日志：

用来记录当MySQL启动、停止或运行时发生的错误信息，默认已开启

vim /etc/my.cnf
log-error=/home/mysql/mysql_error.log   ##指定日志的保存位置				

1.2 通用查询日志：

用来记录mysql的所有连接和语句，默认是关闭的

vim /etc/my.cnf
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log   

1.3 二进制日志：

用来记录所有当MySQL启动、停止或运行时发送的错误信息，默认是关闭的

vim /etc/my.cnf
log-bin=mysql-bin				
或
log_bin=mysql-bin
 #使用相对路径，则文件存储在默认目录/usr/local/mysql/data/中

1.4 慢查询日志：

用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，以便于优化，默认是关闭的

vim /etc/my.cnf
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5	   ##慢查询时间，设置超过5秒执行的语句被记录	

2.查看日志的状态：

2.1查看通用查询日志是否开启:

show variables like 'general%';		

 2.2 查看二进制日志是否开启:

show variables like 'log_bin%';

2.3 查看慢查询日功能是否开启:

show variables like '%slow%';	

2.4 查看慢查询时间设置:

show variables like 'long_query_time';

2.5 在数据库中设置开启慢查询的方法:

set global slow_query_log=ON;
##在数据库中设置开启慢查询的方法，即以修改变量值的方式开启。但重启服务后会失效。

五、增量备份的实际操作：

1.二进制日志功能：

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

• STATEMENT (基于SQL语句)：

每一条涉及到被修改的sql 都会记录在binlog中

• ROW(基于行)：

只记录变动的记录，不记录sql的上下文环境

• MIXED(混合模式)

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

mysql> show variables like 'general%';

默认格式是STATEMENT

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

2.查看二进制文件内容：

[root@localhost data]#mysqlbinlog --no-defaults /data/mysql-bin.000001

mysqlbinlog --no-defaults  /opt/mysql-bin.000001
mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/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 /usr/local/mysql/data/mysql-bin.000001 > /opt/mysql-bin.000001
 
1、at ：开始的位置点
 
2、end_log_pos：结束的位置
 
3、时间戳： 210712 11:50:30
 
4、SQL语句

 

3.一般恢复：

备份：

删除ky35 

恢复：

查看：

4.基于位置点恢复：

可每天进行增量备份操作，生成新的二进制日志文件(例如:mysql-bin.000004)
 
mysqladmin -u root -p flush-logs ### 刷新

基于位置点恢复
#仅恢复到操作 ID 为'1023'之前的数据，即恢复1 2的数据
mysqlbinlog --no-defaults --stop-position='1023' mysql-bin.000001|mysql -uroot -p123
 
 
#恢复3数据  起始操作ID 为'1576',截止id为 '1975'
mysqlbinlog --no-defaults --start-position='2947' --stop-position='3237' mysql-bin.000002|mysql -uroot -p123
 
#跳过第4条数据，恢复第5条数据，直接从第5条开始到最后
mysqlbinlog --no-defaults --start-position='1771' mysql-bin.000002|mysql -uroot -p123

插入数据： 

回到1315之前：

查看：

 

 回到1204之后：

 

回到1204到1456之间：

5.基于时间点恢复：

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

 

恢复了第三条数据：