MySQL的日志与备份

---

一.日志

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

1.日志类型

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

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

2.undo回滚日志

作用:保证数据的原子性，记录事务发生之前的一个版本，用于回滚，innodb事务可重复读和读取已提交隔离级别就是通过mvcc+undo实现

3.errorlog错误日志

作用:Mysql本身启动，停止，运行期间发生的错误信息，默认已开启

指定日志的保存位置和文件名

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

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

4.通用查询日志

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

general_log=ON

general_log_file=/usr/local/mysql/data/mysql_general.log

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

5. slow query log慢查询日志

作用:记录执行时间过长的sql，时间阈值(10s）可以配置，只记录执行成功另一个作用:在于提醒优化。默认是关闭的

slow_query_log=ON

slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log

long_query_time=5

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

6.bin log二进制日志

作用:用于主从复制，实现主从同步记录的内容是:数据库中执行的sql语句。默认是关闭的

log-bin=mysql-bin

或log_bin=mysql-bin

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

每次重启服务就会生成一个二进制日志。

每次重启都是保留当前重启前的数据命令操作。

7.relay log 中继日志

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

8.general log 普通日志

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

2.查看日志状态

xxx%：表示匹配以xxx开头

%xxx：表示匹配以xxx结尾

%xxx%：表示含有xxx都匹配

xxx：表示精确匹配

1.查看通用查询日志是否开启

2.查看二进制日志是否开启

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

4.查询慢查询时间设置

5.设置慢查询的方法（临时）

(root@localhost) [(none)]> set global slow_query_log=ON;

二.MySQL备份与恢复

1.数据备份的重要性

• 在生产环境中，数据的安全性至关重要
• 任何数据的丢失都可能产生严重的后果

2.造成数据丢失的原因

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

3.物理和逻辑的角度分为

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

• 物理备份方法
  • 冷备份（脱机备份）：是在关闭数据库的时候进行的
  • 热备份（脱机备份）：数据库处于运行状态，依赖于数据库的日志文件
  • 温备份：数据库锁定表格（不可写入但不可读）的状态进行备份操作
• 逻辑备份：对数据库逻辑组件（如：表等数据库对象）的备份

4.从数据库的备份策略角度分为

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

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

优点：备份与恢复操作简单方便
缺点：数据存在大量的重复、占用大量的备份空间及备份与恢复时间长

• 差异备份：备份自从上次完全备份之后被修改过的文件
• 增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份

5.常见的备份方法

• 物理冷备
• 专业备份工具mysqldump或mysqlhotcopy
• 启用二进制日志进行增量备份
  • 进行增量备份，需要刷新二进制日志
• 第三方工具备份
• 热备份软件Percona XtraBackup

6.完全备份

1.创建表，添加表内容

(root@localhost) [oyyy]> create table oyyy(id int,name varchar(20),age int,address varchar(40),hobby varchar(50));

(root@localhost) [oyyy]> insert into oyyy values(1,‘aaa’,18,‘nj’,‘game’);

(root@localhost) [oyyy]> insert into oyyy values(2,‘bbb’,19,‘bj’,‘music’);

2.冷备份

[root@localhost ~]# systemctl stop mysqld.service 
[root@localhost mysql]# mkdir /backup
[root@localhost mysql]# tar zcf /backup/mysql_all_$(date +%F).tar.gz /usr/local/mysql/data
[root@localhost mysql]# systemctl start mysqld.service 
##删掉数据库oyyy
[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 
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.
##此时重启服务报错是因为属主和属组不对
[root@localhost mysql]# chown -R mysql.mysql ./data
[root@localhost mysql]# systemctl restart mysqld.service 
##此时再查看数据库时oyyy库就恢复了

在数据库中

3. mysqldump备份

当你用mysqldump命令备份还原数据库时,你会发现会有提示信息,此时呢数据库也是没有被导出的.

此时就需要修改配置文件/etc/my.cnf,在client下添加主机用户和密码

[root@localhost mysql]# mysqldump -uroot -p123123 --databases oyyy > /backup/oyyy.sql
此时输入mysqldump备份虽然还是会报错,但是已经备份成功了

或者还可以使用以下方法备份

[root@localhost mysql]# mysqldump --defaults-extra-file=/etc/my.cnf oyyy >/backup/oyyy1.sql

备份全部数据库

[root@localhost mysql]# mysqldump -uroot -p123123 --databases > /backup/all.sql

备份数据库 数据表

[root@localhost mysql]# mysqldump -uroot -p123123 oyyy oyyy > /backup/oyyy_oyyy.sql

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

[root@localhost mysql]# mysqldump -uroot -p123123 -d oyyy oyyy > /backup/oyyy_oyyy-d.sql

查看备份文件

[root@localhost backup]# cat oyyy_oyyy.sql |grep -v ‘^/’|grep -v ‘^$’|grep -v ‘^-’

4. mysqldump恢复

备份恢复1

先将oyyy数据库删掉

然后将之前备份的文件导进去

[root@localhost backup]# mysql -u root -p123123 < /backup/oyyy.sql

此时oyyy数据库就又恢复了

将数据库oyyy删掉之后再创建一个oyyy数据库

将之前备份的文件导进去

[root@localhost backup]# mysql -u root -p123123 oyyy< /backup/oyyy_oyyy.sql

要指定数据库

此时oyyy数据库和数据表就又恢复了

备份恢复3

将表oyyy删掉

将之前备份的文件导进去

[root@localhost backup]# mysql -u root -p123123 oyyy< /backup/oyyy_oyyy-d.sql

要指定数据库

此时oyyy数据表就又恢复了,但是内容为空了

7.增量备份与恢复

1.一般恢复

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

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

（STATMENT）基于SQL语句

（ROW）基于行

（MIXED）混合模式

默认格式为STATMENT

日志格式	STATEMENT（基于SQL语句，默认）	ROW（基于行）	MIXED（混合模式）
说明	每一条涉及到被修改的 sql 都会记录在 binlog 中	只记录变动的记录，不记录 sql 的上下文	一般语句使用 STATEMENT，函数使用 ROW
缺点	日志量过大，如函数、主从复制等构架记录日志时会出现问题	如果遇到 updata…set…where true，那么日志数据量就会很大	推荐使用

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

log-bin=mysql-bin
binlog_format=MIXED

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

向数据表中添加id=3的数据

[root@localhost data]# mysqladmin -u root -p123123 flush-logs

进行刷新，生成新的二进制文件相当于重启mysql服务

mysql-bin-000003就是插入id=3的数据内容

向数据表中删除id=3的数据

[root@localhost data]# mysqladmin -u root -p123123 flush-logs

mysql-bin-000004就是删除id=3的数据内容

[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000003

查看二进制日志文件

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

恢复数据插入id=3的数据

此时查看数据表，就有id=3的数据内容了

2.基于位置恢复

向数据表中删除id=3的数据

[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000003

查看二进制日志文件

所以298是插入id=3的数据内容的起始位置

[root@localhost data]# mysqlbinlog --no-defaults --start-position=‘298’ /usr/local/mysql/data/mysql-bin.000003 |mysql -u root -p123123

根据位置恢复数据插入id=3的数据

此时查看数据表，就有id=3的数据内容了

3.基于时间点

向数据表中删除id=3的数据

[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000003

查看二进制日志文件

所以21 11 30 14:57:59是插入id=3的数据内容的起始时间

[root@localhost data]# mysqlbinlog --no-defaults --start-datetime=‘2021-11-30 14:57:59’ /usr/local/mysql/data/mysql-bin.000003 |mysql -u root -p123123

根据时间恢复数据插入id=3的数据

此时查看数据表，就有id=3的数据内容了