MySQL备份、恢复与日志管理

一、MySQL日志管理

1、MySQL日志默认保存位置

/usr/local/mysql/data

2、设置日志

进入vim /etc/my.cnf进行添加

①错误日志

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

log-error=/usr/local/mysql/data/mysql_error.log #日志保存的路径

②通用查询日志

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

general_log=ON

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

③二进制日志

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

log-bin=mysql-bin

或者

log_bin=mysql-bin

④慢查询日志

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

s1ow_query_log=ON

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

long_query_time=10 #设置超过10秒执行的语句被记录，缺省时为10秒

systemctl restart mysqld #添加完成以后需要重启服务

3、查询日志是否开启

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、在生产环境中，数据的安全性至关重要
3、任何数据的丢失都可能产生严重的后果
4、造成数据丢失的原因
程序错误
人为操作错误
运算错误
磁盘故障
灾难 (如火灾、地震) 和盗窃

三、数据库备份分类

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

①物理备份

对数据库操作系统的物理文件(如数据文件、日志文件等)的备份
物理备份方法
冷备份 (脱机备份) ：是在关闭数据库的时候进行的
热备份 (联机备份) ：数据库处于运行状态，依赖于数据库的日志文件
温备份 ：数据库锁定表格(不可写入但可读)的状态下进行备份操作

②逻辑备份

对数据库逻辑组件(如:表等数据库对象)的备份

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

①完全备份：每次对数据库进行完整的备份
②差异备份：备份自从上次完全备份之后被修改过的文件
③增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份

3、常见的备份方法

①物理冷备

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

②专用备份工具 mydump 或 mysqlhotcopy

mysqldump 常用的逻辑备份工具
mysqlhotcopy 仅拥有备份 MyISAM 和 ARCHIVE 表

③启用二进制日志进行增量备份

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

④第三方工具备份

免费的MySQL 热备份软件 Percona XtraBackup

四、MySQL完全备份

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

1、完全备份的优缺点

①优点

备份与恢复操作简单方便

②缺点

数据存在大量的重复
占用大量的备份空间
备份与恢复时间长

2、数据库完全备份分类

①物理冷备份与恢复

关闭MySQL数据库
使用tar命令直接打包数据库文件夹
直接替换现有MySQL目录即可

②mysqldump备份与恢复

MySQL自带的备份工具，可方便实现对MySQL的备份
可以将指定的库、表导出为SQL 脚本
使用命令ysq|导入备份的数据

五、MySQL备份、恢复相关命令

1、物理备份数据库

systemctl stop mysql #关闭数据库

yum install -y XZ #安装压缩工具

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

需要恢复时解压即可

tar Jxvf /opt/mysql_all_2021-01-26.tar.xz -C /usr/local/mysql/data/

2、数据库备份

①完全备份一个或多个完整的数据库

mysqldump -u -root -p(密码) --databases 库名1 库名2 … > /opt/mysql-FYF.sql #后面跟库名即可

②备份所有的库

mysqldump -u root -p(密码) --all-databases >/opt/mysql-all.sql

③备份表

mysqldump -u root -p(密码) [-d] 库名 表名1 表名2 … >/opt/mysql-FYF-LOL.sql #-d说明表示只备份表结构，不加-d会连同数据一起备份

④查看备份文件

grep -v “^–” /opt/gcc_info1.sql | grep -v “^/” | grep -v “^$” #-v反选的意思

3、恢复数据库

①恢复数据库

mysql -u root -P -e ‘drop database FYF;’ #-e 用于指定连接MySQL后执行命令，并执行完自动退出

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

②恢复表

mysql -u root -p123456 -e ‘drop table FYF.LOL;’

mysql -u root -p123456 FYF < /opt/mysql-FYF-LOL.sql

4、增量备份

①开启二进制日志功能

[mysqld]
log-bin=mysql-bin
binlog_format = MIXED #可选，指定二进制日志(binlog)的记录格式为MIXED
server-id = 1

#二进制日志(binlog)有3种不同的记录格式: STATEMENT (基于SQL语句)、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENT

systemctl start mysqld #配置完成后需要重启服务

②对每周数据进行备份

mysql -u root -p123456 库名 表名 >/opt/mysql-库名-表名(date +%F).sql

mysql -u -root -p123456 --all-databases 库名 >/opt/mysql-库名(date %F).sql

③进行每天的增量备份

mysqladmin -u root -p flush-logs #每天刷新一下，会自动生成一份

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

cd /usr/local/mysql/data

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

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

#-v: 显示详细内容

5、增量恢复

①一般恢复

mysqlbinlog --no-defaults mysql-bin.000002 | mysql -u root -p

当你删完东西以后，如果想恢复，可以选择上上个时间点进行恢复

②恢复全部数据

mysql -u root -p gcc < /opt/gcc_info1_2020-11-22.sql #这个是根据保存的表进行恢复

mysqlbinlog --no-defaults / opt/mysql-bin.000002 | mysql -u root -p #这个是根据每人保存的二进制数据进行恢复

③断点恢复-基于位置的恢复

#仅恢复到操作 位置为“64005"之前的数据

mysqlbinlog --no-defaults --stop-position=‘64005’ mysql-bin.000013 | mysql -u root -p123456

#只恢复5不恢复4

mysqlbinlog --no-defaults --stop-position=‘64005’ mysql-bin.000013 | mysql -u root -p123456

mysqlbinlog --no-defaults --start-position=‘64286’ mysql-bin.000013 | mysql -u root -p123456

④断点恢复-基于时间的恢复

mysqlbinlog [–no-defaults] --start-datetime=‘年-月-日 小时:分钟:秒’ --stop-datetime=‘年-月-日小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码
总结：
如果恢复某条SQL语之前的所有数据，就stop在这个语句的位置节点或者时间点
如果恢复某条SQL语句以及之后的所有数据，就从这个语句的位置节点或者时间点start