MySQL数据库日志、备份与恢复

发布时间：2023-05-23 22:33:54 所属栏目：MySql教程来源：网络

导读： 文章目录
前言
数据传输、数据存储、数据交换、软件故障、硬盘坏道等情况容易产生数据故障。如果没有采取数据备份和数据恢复手段与措施，就会导致数据的丢失。没有数据库的备份，就没有数据

文章目录

前言

数据传输、数据存储、数据交换、软件故障、硬盘坏道等情况容易产生数据故障。如果没有采取数据备份和数据恢复手段与措施，就会导致数据的丢失。没有数据库的备份，就没有数据库的恢复，企业应当把企业数据备份的工作列为一项不可忽视的系统工作，为其选择相应的备份设备和技术，进行经济可靠的数据备份，从而避免损失。

一、日志管理数据的重要性造成数据丢失的原因

1、程序错误

2、人为操作错误

3、运算错误

4、磁盘故障

5、灾难 (如火灾、地震) 和盗窃

日志分类 1、重做日志（redo）：达到事务一致性（每次重启会重做）

作用：确保日志的持久性，防止在发生故障，脏页未写入磁盘。重启数据库会进行redo log执行重做，达到事务一致性

2、回滚日志（undo）

作用：保证数据的原子性，记录事务发生之前的一个版本，用于回滚，innodb事务可重复读和读取已提交

隔离级别就是通过mvcc+undo实现

3、错误日志（errorlog）

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

log-error=/usr/local/mysql/data/mysql_error.log

4、慢查询日志（slow query log）

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

slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5 单位时间为s，缺省时为10s

慢查询日志也可用来查询哪些搜索的字段超时，可以选择是否需要增加索引，加快查询速度

5、二进制日志（bin log）

作用：用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启

log_bin=mysql-bin
或 log-bin=mysql-bin

6、中继日志（relay log）

作用：用于数据库主从同步，将主库发来的bin log保存在本地，然后从库进行回放

7、普通日志（general log）

作用：记录数据库的操作明细，默认关闭，开启后会降低数据库性能

配置文件

vim /etc/my.cnf

#错误日志
log-error=/usr/local/mysql/data/mysql_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

#配置文件添加完后需要重启MySQL

systemctl restart mysql

在这里插入图片描述

show variables like 'general%';    #查询通用日志是否开启
show variables like 'log_bin%';    #查询二进制日志是否开启
show variables like '%slow%';     #查询慢查询日志功能是否开启
show variables like 'long%';        #查询慢查询日志超时时间
set global slow_query_log=ON;  #在数据库中设置开启慢查询的方法

PS：variables 表示变量 like 表示模糊查询

xxx% 以xxx为开头的字段

%xxx 以xxx为结尾的字段

%xxx% 只要出现xxx字段的都会显示出来

xxx 精准查询

在这里插入图片描述

二进制日志开启后，重启mysql会在目录中查看到二进制文件

在这里插入图片描述

二、数据库备份的分类 1、物理备份

数据库备份可以分为物理备份和逻辑备份。

物理备份是对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。这种类型的备份适用于在出现问题的时候需要快速恢复的大型重要数据库。

物理备份又可以成为冷备份（脱机备份）、热备份（连接备份）和温备份

2、逻辑备份

逻辑备份是对数据库逻辑组件的备份，表示为逻辑数据库结构

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

从数据库的备份策略角度来看，备份又可分为完全备份、差异备份和增量备份

完全备份差异备份增量备份对比备份方式完全备份差异备份增量备份

完全备份时的状态

表1、表2

第1次添加内容

创建表3

备份内容

表1、表2、表3

表3

第2次添加内容

创建表4

备份内容

表1、表2、表3、表4

表3、表4

常见备份方法 1、物理冷备 2、专用备份工具 3、启用二进制日志进行增量备份 4、第三方工具备份四、MySQL完全备份 1、概念 2、特点

优点

数据库完全备份分类 1、物理冷备份与恢复（tar）

关闭MySQL数据库

使用tar命令直接打包数据库文件夹

直接替换现有MySQL目录即可

2、mysqldump备份与恢复

MySQL自带的备份工具，可方便实现对MySQL的备份

可以将指定的库、表导出为SQL脚本

使用命令mysql导入备份数据

操作

准备测试环境

use test;

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));

insert into info1 values(1,‘user1’,20,‘running’);

insert into info1 values(2,‘user2’,30,‘singing’);

在这里插入图片描述

Mysql完全备份与恢复

InnoDB 存储引擎的数据库在磁盘上存储成三个文件: db.opt(表属性文件)、表名.frm(表结构文件)、表名.ibd(表数据文件)。

1、物理冷备份与恢复

systemctl stop mysqld

yum -y install xz

在这里插入图片描述

#压缩备份

tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/

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

在这里插入图片描述

#解压恢复

tar Jxvf /opt/mysql_all_2022-09-19.tar.xz -C /usr/local/mysql/

cd /usr/local/mysql/data

systemctl start mysqld

在这里插入图片描述

mysqldump备份与恢复

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

#格式：

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

#示例：

mysqldump -u root -pabc123 --databases test > /opt/test.sql #备份一个 test 库

mysqldump -u root -p --databases mysql test > /opt/mysql-test.sql #备份 mysql 与 zone 两个库

在这里插入图片描述

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

#格式：

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

#示例:

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

在这里插入图片描述

完全备份指定库中的部分表

mysqldump -u root -p[密码] 库名 [表名1] [表名2] ... > /备份路径/备份文件名.sql
例:
mysqldump -u root -p [-d] test info1 > /opt/test_info1.sql
#使用 "-d" 选项，说明只保存数据库的表结构 
#不使用 "-d" 选项，说明表数据也进行备份
#做为一个表结构模板

在这里插入图片描述

查看备份文件

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

完全恢复方法一：使用source恢复数据库的步骤

登录到 MySQL 数据库

执行 source 备份 sql 脚本的路径

#模拟故障

use test;

delete from info1;

在这里插入图片描述

#恢复

source /opt/test.sql

在这里插入图片描述

方法二：使用mysql命令，无需登陆mysql数据库

使用 -e 选项用于指定连接mysql后执行的命令，删除数据表，执行完自动退出。执行 mysql 备份 sql 脚本的路径

mysql -uroot -pabc123 -e ‘drop database test;’

mysql -uroot -pabc123 -e ‘show databases;’

mysql -uroot -pabc123 < /opt/test.sql

mysql -uroot -pabc123 -e ‘show databases;’

在这里插入图片描述

注意：mysqldump严格来说属于温备份，会需要对表进行写入的锁定

在全量备份与恢复实验中，假设现有 test 库，需要注意的一点为：

① 当备份时加 --databases，表示针对于 test 库

备份命令：

mysqldump -uroot -p123456 --databases test > /opt/test_01.sql 备份库后

恢复命令过程为：

mysql -uroot -pabc123;

drop database test;

quit

mysql -uroot -pabc123 < /opt/test_01.sql

② 当备份时不加 --databases，表示针对 test 库下的所有表

备份命令：

mysqldump -uroot -pabc123 test > /opt/test_all.sql

恢复过程：

mysql -uroot -pabc123

drop database test;

create database test;

quit

mysql -uroot -pabc123 test < /opt/test_all.sql

主要原因在于两种方式的备份（前者会从 “create databases” 开始mysql备份，而后者则全是针对表格进行操作）

Mysql增量备份与恢复恢复的方式

一般恢复

基于位置恢复

基于时间点恢复

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

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

vim /etc/my.cnf

在这里插入图片描述

STATEMENT（基于SQL语句） ROW（基于行） MIXED 推荐使用

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

查看二进制文件内容

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

mysqlbinlog --no-defaults /opt/mysql-bin.000002

在这里插入图片描述

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

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

#-v: 显示详细内容

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

在这里插入图片描述

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

at ：开始的位置点

end_log_pos：结束的位置 position （位置点）

时间戳： 220918 21:38:25

SQL语句

增量备份过程（全备+增备）

使用mysqldump

mysqldump -uroot -pabc123 test info1 > /opt/test_info1_$(date +%F).sql
mysqldump -uroot -pabc123 test > /opt/test_all_$(date +%F).sql
 
mysqldump -uroot -pabc123 --all-databases > /opt/all_$(date +%F).sql

在这里插入图片描述