MySQL数据库之——日志和数据库内容的备份与恢复（妈妈再也不用担心数据丢失了）

一、日志管理

1.1 日志路径

MySQL的日志默认保存 /usr/local/mysql/data

1.2 修改配置文件

vim /etc/my.cnf

[mysqld]
...

#错误日志路径
log-error=/usr/local/mysql/data/mysq1_error.log							#指定日志的保存位置和文件名
#通用查询日志
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
#二进制日志
log-bin=mysql-bin
#慢查询日子
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5														#设置超过5秒执行的语句被记录，缺省时为10秒

进入MySQL查看

mysql -u root -p

• show variables like ‘general%’;

  • 查看通用查询日志是否开启
    

• show variables like ‘log_bin%’;

  • 查看二进制日志是否开启
    

• show variables like ‘%slow%’;

  • 查看慢查询日志功能是否开启
    

• show variables like ‘long_query_time’;

  • 查看慢查询时间设置
    

• set global slow_query_log=ON;

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

二、数据库备份的分类

2.1 概述

备份的主要目的为灾难恢复，在生产环境中，数据的安全性至关重要，任何数据的丢失都可能产生严重的后果

2.1 分类

从物理和逻辑的角度分为两类

物理备份

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

• 物理备份方法
  • 冷备份（脱机备份）：关闭数据库进行
  • 热备份（联机备份）：数据运行时，依赖于数据库的日志文件
  • 温备份：数据库锁定表格的状态下进行备份

逻辑备份

对数据库逻辑组件（表等数据库对象）的备份

从数据库的备份策略

完全备份：每次对数据库进行完整的备份

差异备份：备份自从上次完全备份后被修改过的文件

增量备份：只有在上次完全备份或增量备份后被修改的文件才会备份

2.2 常见的方法

物理冷备

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

专用工具

• mysqldump 常用的逻辑备份工具
• mysqlhotcopy 仅拥有备份myisam 和 archive 表

二进制日志增量备份

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

三、完全备份和恢复

3.1 概述

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

优点

备份和恢复操作简单

缺点

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

3.2 结构

• InnoDB 存储引擎的数据库在磁盘上存储成三个文件
  • ad.opt（表属性文件）
  • 表名.frm（表结构文件）
  • 表名.ibd（表数据文件）

物理冷备份与恢复

# 关闭服务
systemctl stop mysqld.service

# 安装第三种压缩解压缩工具 XZ
yum -y install xz

# 压缩数据库文件
tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/

# 解压缩恢复至原目录
tar Jxvf /opt/mysql_all_2021-06-25.tar.xz -C /usr/local/mysql/data

3.3 mysqldump 备份

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

• mysqldump -u root -p 密码 --databases 库名1 （有需要就库名2） > /备份路径/备份文件名.sql
  • 导出的文件为数据库脚本文件

mysqldump -u root -p --databases dcc > /opt/dcc.sql

完全备份MySQL中所有的库

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

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

完全备份指定库中的选定表

mysqldump -u root -p 库名 表名 > /备份路径/备份文件名.sql

mysqldump -u root -p dcc cc01 > /opt/cc01.sql

注：-d 选项可以只保存数据库的表结构，不加-d 则对表数据也备份

查看备份文件

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

3.4 完全恢复

恢复数据库

mysql -u root -p -e 'drop database dcc;'

mysql -u root -p -e 'show databases;'

-e 选项表示连接MySQL后执行的命令，命令执行完自动退出

mysql -u root -p < /opt/dcc.sql

恢复数据表

当备份文件只包含表的备份，不包含创建的库的语句时，执行导入操作必须指定库名，并且这个库要存在

# 导入cc01表
mysqldump -u root -p dcc < /opt/cc01.sql

# 查看
mysql -u root -p -e 'show tables from dcc;'
Enter password: 
+---------------+
| Tables_in_dcc |
+---------------+
| cc01          |
+---------------+

四、MySQL增量备份与恢复

4.1 增量备份

开启二进制日志功能

vim /etc/my.cnf

#二进制日志
log-bin=mysql-bin
binlog_format = MIXED			# 可选可不选，指定二进制日志的记录格式为 MIXED
server-id = 1

二进制日志（binlog）有三种不同的记录格式

STATEMENT（基于SQL语句）（默认格式）
ROW（基于行）
MIXED（混合模式）

• systemctl restart mysqld.service
• ls -l /usr/local/mysql/data/mysql-bin.*

每周对数据库或表进行完全备份

mysqldump -u root -p dcc cc01 > /opt/dcc_cc01_$(date +%F).sql

mysqldump -u root -p --all-databases dcc > /opt/dcc_$(date +%F).sql

每天进行增量备份操作，生成新的二进制日志文件

• mysqladmin -u root -P flush-logs

自行插入新数据，以模拟数据的增加或变更

use dcc;

insert into cc01 values(7,'杨记','桃酥');

再次生成新的二进制日志文件

mysqladmin -u root -P flush- logs

#之前的步骤4的数据库操作会保存到mysq1-bin.000002文件中，之后数据库数据再发生变化则保存在mysql-bin.000003文件中

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

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

mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002
#--base64-output=decode - rows:使用64位编码机制去解码并按行读取
#-V:显示详细内容

4.2 增量恢复

一般恢复

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

# 删除记录
use dcc;
delete from cc01 where id=3;

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

模拟丢失所有数据的恢复步骤

use dcc;
drop table cc01;
mysql -u root -P dcc < /opt/dcc_cc01_2021-06-25.sql
mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p 

断点恢复

mysqlbinlog --no-defaults --stop-position='2971' /opt/mysql-bin.000002 | mysql -u root -p

mysqlbinlog --no-defaults --stop-position='2021-06-25 22:14:20' /opt/mysql-bin.000002 | mysql -u root -p