mysql必了解的日志和备份

目录

前言

一、日志

1.日志类型与作用

（1）redo 重做日志

（2）undo 回滚日志

（3）errorlog 错误日志

（4）slow query log 慢查询日志

（5）bin log 二进制日志 

（6）relay log 中继日志

（7）general log 普通日志

（8）配置文件

（9）注意点 

二、备份

1.为什么要备份

2.备份类型

（1）完全备份，部分备份

（2）完全备份、增量备份、差异备份

（3）冷、温、热备份

（4）物理和逻辑备份

3实际操作

（1）物理冷备份与恢复

（2）mysqldump备份与恢复（温备份）

（3）增量备份与恢复

（4）断点恢复

三、总结

---

前言

       随着信息技术的不断发展，企业对信息系统的依赖性越来越高，数据库作为信息系统的核心担当着重要的角色。数据库备份实在数据丢失的情况下，能及时恢复重要数据防止数据丢失的一种重要手段。

一、日志

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

1.日志类型与作用

（1）redo 重做日志

达到事务一致性（每次重启会重做）

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

（2）undo 回滚日志

作用：保证数据的原子性，记录事务发生之前的一个版本，用于回滚

（3）errorlog 错误日志

作用：Mysql本身启动，停止，运行期间发生的错误信息

（4）slow query log 慢查询日志

作用：记录执行时间过长的sql，时间阈值（10s）可以配置，只记录执行成功 另一个作用：在于提醒优化

（5）bin log 二进制日志 

作用：用于主从复制，实现主从同步 记录的内容是：数据库中执行的sql语句

（6）relay log 中继日志

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

（7）general log 普通日志

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

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;         在数据库中设置开启慢查询的方法

（8）配置文件

[root@localhost ~]# vim /etc/my.cnf

（9）注意点 

xxx（字段）
xxx% 以xxx为开头的字段
%xxx 以xxx为结尾的字段
%xxx% 只要出现xxx字段的都会显示出来
xxx   精准查询

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

cd /usr/local/mysql/data
ls

开启二进制日志时会产生一个索引文件及一个索引列表

例如mysql-bin.000001        

索引文件：记录更新语句

查看二进制日志内容

mysql> show binlog events in ' 二进制日志文件' ;

索引文件刷新方式

1、重启mysql的时候会更新索引文件，用于记录新的更新语句

2、刷新二进制日志

mysql-bin.index：

二进制日志文件的索引

二、备份

1.为什么要备份

灾难恢复：硬件故障、软件故障、自然灾害、黑客攻击、误操作测试等数据丢失场景

2.备份类型

（1）完全备份，部分备份

完全备份：整个数据集

部分备份：只备份数据子集，如部分库或表

（2）完全备份、增量备份、差异备份

增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂

差异备份：差异数据库备份，记录自上次完全数据库备份之后数据库的更改的数据库备份。

（3）冷、温、热备份

冷备：读、写操作均不可进行，数据库停止服务

温备：读操作可执行；但写操作不可执行

热备：读、写操作均可执行

MyISAM：温备，不支持热备

InnoDB：都支持 

（4）物理和逻辑备份

物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快

逻辑备份：从数据库中“导出”数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度

3实际操作

环境准备

[root@localhost ~]# mysql -u root -p'123123'；

mysql> create database kgc;        创建数据库

mysql> use kgc;

mysql> create table ky15(id int(4) not null auto_increment,name varchar(10) not null,age char(10) not null,hobby varchar(50),primary key (id));      创建表

mysql> insert into ky15 values(1,'naruto',20,'lasingan');   添加表内容

mysql> insert into ky15 values(2,'sasuki',21,'qidoluyi');    添加表内容

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

（1）物理冷备份与恢复

[root@localhost ~]# systemctl stop mysqld

•  压缩备份

[root@localhost ~]# cd /usr/local/mysql/
[root@localhost mysql]# mkdir /backup
[root@localhost mysql]# tar zcf /backup/mysql_all_$(date +%F).tar.gz /usr/local/mysql/data

• 模拟数据丢失 

进入数据库

mysql> show databases;

mysql> drop database kgc;

mysql> show databases;

• 解压恢复 

[root@localhost mysql]# mv data/ data.bak/
[root@localhost backup]# tar zxf mysql_all_2021-11-29.tar.gz   解压
[root@localhost mysql]# cp -r data/ /usr/local/mysql/
[root@localhost mysql]# systemctl restart mysqld.service （这里会报错是因为权限问题）
[root@localhost mysql]# chown -R mysql.mysql ./data        赋权
[root@localhost mysql]# systemctl restart mysqld.service    重启服务

我们进数据库发现数据恢复

（2）mysqldump备份与恢复

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

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

导出的就是数据库脚本文件

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

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

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

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

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

使用“-d”选项，说明只保存数据库的表结构

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

查看备份文件

cat /backup/wb1.sql |grep -v '^/'|grep -v '^$'|grep -v '^-'

mysql 完全恢复

基于kgc数据库操作

1.模拟删库

mysql> drop database kgc；

2.恢复

mysql -u root -p123123 < /backup/wb1.sql

进入数据库查看恢复情况

（3）增量备份与恢复

首先修改配置文件启用二进制日志
[root@localhost ~]# vim /etc/my.cnf

重启服务

[root@localhost ~]#systemctl restart mysqld.service

进入数据库

[root@localhost data]# mysql -u root  -p‘数据库密码’ 

添加表内容 

mysql> insert into info values(1,'naruto',17,'木叶','螺旋丸');

mysql> insert into info values(2,'sasuki',30,'宇智波','天照');

查看是否生成了新的二进制日志 

数据库中插入新的文件

mysql> use ky15

进入数据库 ky15

mysql> insert into info values(3,'sakula',17,'春野','百豪');

mysql> insert into info values(4,'kakaxi',30,'旗木','雷切');

mysql> select * from info；

查看二进制日志文件

还有一种方法也可以 

 [root@localhost ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/二进制日志

模拟删库 

mysql> drop database ky15;

恢复表

mysql> create database ky15;

[root@localhost backup]# cd /backup
[root@localhost backup]# mysql -u root ky15 < ky15_info.sql

恢复增量备份

[root@localhost backup]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000006 |mysql -u root

（4）断点恢复

原数据表

• 基于位置恢复

删除库中的数据

mysql> delete from info where id=3;

mysql> delete from info where id=4;

基于位置恢复

1.查看二进制文件

mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000006 >/backup/mysqlbin.log

2.选取位置，进行恢复

[root@localhost backup]#mysqlbinlog --no-defaults --stop-position='1715‘’ /usr/local/mysql/data/mysql-bin.000006 |mysql -u root  

3.查看看效果

基于时间点恢复

格式只将position 改为datetime 时间日期 格式  年-月-日 时：分：秒

[root@localhost backup]#mysqlbinlog --no-defaults --start-datetime='2021-12-1 16:31:14' /usr/local/mysql/data/mysql-bin.000002 |mysql -ur oot 

找回

三、总结

本章主要介绍了MySQL的日志种类以及其作用，备份的种类以及如何进行备份相关的操作。本章的难点在于如何区分增备，完备，差备之间的区别，希望大家反复研读，理解他们之间的差别，下期我们再见啦！