MySQL日志管理、备份与恢复

一。MySQL日志管理

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

在配置文件中设置MySQL日志文件路径

vim /etc/my.cnf

[mysqld]

（1）错误日志，用来记录当MySQL启动、停止或运行时发生的错误信息

log-error=/usr/local/mysql/data/mysql_error.log #指定日志的保存位置和文件名

（2）通用查询日志，用来记录MySQL的所有连接和语句，默认是关闭的 general_log=ON

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

（3）二进制日志（binlog），用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认开启

log-bin=mysql-bin #也可以log_bin=mysql-bin

#默认保存在mysql工作目录中的data目录

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

slow_query_log=ON

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

long_query_time=5 #设置超过5秒执行的语句被记录，不设置默认为10秒

或者

mysql -u root -p #在数据库中设置开启慢查询的方法

set global slow_query_log=ON;

systemctl restart mysqld #重启服务

查询日志文件是否开启

mysql -u root -p

show variables like 'general%'; #查看通用查询日志是否开启

show variables like 'log_bin%'; #查看二进制日志是否开启

show variables like '%slow%'; #查看慢查询日功能是否开启

show variables like'long_query_time'; #查看慢查询时间设置

二。数据备份的重要性

数据备份的重要性备份的主要目的是灾难恢复，在生产环境中，数据的安全性至关重要任何数据的丢失都可能产生严重的后果，造成数据丢失的原因可能是程序错误、人为操作错误、运算错误品、磁盘故障、灾难（如火灾、地震）和盗窃等。

三。数据库备份的分类

1. 从物理与逻辑的角度，备份可分为

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

物理备份方法

冷备份（脱机备份）∶是在关闭数据库的时候进行的，将数据库数据文件进行备份

热备份（联机备份）∶数据库处于运行状态，依赖于数据库的日志文件进行备份

温备份∶数据库锁定表格（不可写入但可读）的状态下进行备份操作

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

2. 从数据库的备份策略角度，备份可分为

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

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

恢复时：直接使用完全备份的文件即可

完全备份备份与恢复操作简单方便，但数据存在大量的重复，占用大量备份空间，备份与恢复时间长。

（2）差异备份∶备份自从上次完全备份之后被修改过的文件

备份时：对上一次完全备份之后的数据进行备份，但第二次差异备份依然会从上一次完全备份之后的数据进行备份，也会导致占用了额外的磁盘空间，

恢复时：需要先恢复完全备份的数据，再恢复差异备份的数据

（3）增量备份∶只有在上次完全备份或者增量备份后被修改的文件才会被备份

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

恢复时：比较繁琐，需要将完全备份数据先恢复在按增量备份的索引号顺序（1.2.3...）进行恢复（1.2.3..）

四. 常见的备份方法

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

备份时数据库处于关闭状态

使用tar命令直接打包数据库文件夹（/data目录）

直接替换现有的MySQL目录即可

（2）专用备份工具mysqldump或mysqlhotcopy

mysqldump常用的逻辑备份工具

#mysqldump缺点：备份有重复数据，备份恢复时间较长

mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表

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

进行增量备份，需要刷新二进制日志，记录表数据中SQL语句，记录上一次备份后增加/变化的文件或者内容

（4）第三方工具备份

免费的MySQL热备份软件Percona XtraBackup

五。物理冷备份与恢复

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

use AAA;

create table if not exists test (

id int (4) not null auto_increment primary key,

name varchar(10),

sex char(10),

hobby varchar (50));

insert into test values(1,'AAA','boy','running');

insert into test values(2, 'BBB','girl','singing');

物理冷备份与恢复

systemctl stop mysqld

yum -y install xz

#压缩备份

tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/ #J调用xz程序压缩文件

#解压恢复

systemctl stop mysqld

tar Jxvf /opt/mysql_all_2020-10-22.tar.xz -C /opt #解压到opt目录中

mv /usr/local/mysql/data /usr/local/mysql/data.bak

mv /opt/usr/local/mysql/data /usr/local/mysql #将之前的备份的文件移动过来

六。mysqldump备份与恢复

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

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

例∶

mysqldump -u root -p --databases AAA > /opt/backup/AAA.sql

mysqldump -u root -p --databases mysql AAA > /opt/backup/mysql-AAA.sql

（2）完全备份 MySOL服务器中所有的库

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

例∶

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

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

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

例∶

mysqldump -u root -p [-d] AAA test > /opt/backup/AAA_test.sql

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

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

（4）查看备份文件

grep -v "^--" /opt/backup/AAA_test.sql | grep -v "^/" | grep -v "^$" #反匹配多余项仅剩下SQL语句

（5）恢复数据库

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

#^-e"选项，用于指定连接 MySQL后执行的命令，命令执行完后自动退出

mysql -u root -p -e 'SHOW DATABASES;'

#重定向输入恢复数据库

mysql -u root -p < /opt/backup/AAA.sql

mysql -u root -p -e 'SHOW DATABASES; '

（6）恢复数据表

当备份文件中只包含表的备份，而不包含创建的库的语句时，执行导入操作时必须指定库名，且目标库必须存在。

mysqldump -u root -p AAA test > /opt/backup/AAA_test.sql

#删除表

mysql -u root -p -e 'drop table AAA.test;'

mysql -u root -p -e 'show tables from AAA;'

#对表进行恢复

mysql -u root -p AAA < /opt/backup/AAA_test.sql

mysql -u root -p -e 'show tables from AAA;'

七。MySQL增量备份

是自上一次备份后增加/变化的文件或者内容，没有重复数据，备份量小，时间短，但恢复起来较为繁琐，需要先恢复全局备份的数据，然后将保存的二进制文件按照索引顺序从小到大依次恢复，如果中途漏掉了某个增量备份文件，可能会出现报错，或者缺漏文件数据。

MySQL没有提供直接的增量备份方法，可通过MySQL提供的二进制日志间接实现增量备份，因为存储都都是每个增量期间输入的SQL语句，相对与把增量保存的SQL语句内容再次输入了一遍

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

（1）二进制日志保存了所有更新或者可能更新数据库的操作

（2）二进制日志在启动MySQL服务器后开始记录，并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件

（3）只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份

（4）可定期对文件进行完全备份，期间进行增量备份

MySQL增量备份步骤

1. 开启二进制日志功能

vim /etc/my.cnf

[mysqld]

log-bin=mysql-bin

binlog_format = MIXED #可选，指定二进制日志（binlog）的记录格式为MIXED

#二进制日志（binlog）有3种不同的记录格式∶STATEMENT（基于SQL语句，在高并发环境中一些新功能同步可能会出现错误）、ROW（基于行，精确度较高，但是可能会将一条SQL语句记录多行，需要占用更多的性能资源）、MIXED（混合模式，两种模式的结合较为适用），默认格式是STATEMENT

systemctl start mysqld

ls -l /usr/local/mysql/data/mysql-bin.*