目录

引言

一、日志管理

1、日志位置

2、常用的日志

错误日志

通用查询日志

二进制日志(binlog)

中继日志

慢查询日志

3、配置文件

4、查询配置

二、数据库备份

1、数据备份的重要性

2、备份类型

物理备份

物理备分类

逻辑备份

逻辑备份分类

3、逻辑备份比较

4、逻辑备份的策略

5、常用的备份

三、备份数据库实操

1、备份

2、还原

3、定时任务备份

4、增量备份与恢复

5、恢复实操

一般恢复

基于位置恢复

基于时间点恢复

总结

---

引言

其实日志和备份放在一起，大家一点也不会以外吧，做为一个运维工程师来说，数据安全是至关重要的，那我们平时做的最多的一件事就是保存日志和数据备份了，它们都可以在我们丢失数据的时候一定程度上减少我们的损失

一、日志管理

1、日志位置

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

2、常用的日志

错误日志

用来记录当MySQL启动、停止或运行时发生的错误信息，默认已开启
log-error=/usr/local/mysql/data/mysql_error.log     （指定日志的保存位置和文件名）

通用查询日志

用来记录MySQL的所有连接和语句，默认是关闭的
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log

二进制日志(binlog)

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

中继日志

一般情况下它在Mysql主从同步（复制）、读写分离集群的从节点开启。主节点一般不需要这个日志

慢查询日志

用来记录所有执行时间超过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秒）

3、配置文件

vim /etc/my.cnflog-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=5systemctl restart mysqld

4、查询配置

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;         #在数据库中设置开启慢查询的方法PS:variables 表示变量 like 表示模糊查询xxx% 以xxx为开头的字段
%xxx 以xxx为结尾的字段
%xxx% 只要出现xxx字段的都会显示出来
xxx   精准查询#二进制日志开启后，重启mysql 会在目录中查看到二进制日志
cd /usr/local/mysql/data
ls
mysql-bin.000001		#开启二进制日志时会产生一个索引文件及一个索引列表

二、数据库备份

1、数据备份的重要性

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

2、备份类型

物理备份

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

物理备分类

冷备份 (脱机备份) ：是在关闭数据库的时候进行的（tar）
热备份 (联机备份) ：数据库处于运行状态，依赖于数据库的日志文件（mysqlhotcopy mysqlbackup）
温备份 ：数据库锁定表格(不可写入但可读)的状态下进行备份操作（mysqldump）

逻辑备份

逻辑备份是对数据库逻辑组件的备份.表示为逻辑数据库结构
这种类型的备份适用于可以编辑数据值或表结构   

逻辑备份分类

完全备份：每次对数据进行完整备份，即对整个数据库、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础完全备份的备份与恢复操作都非常简单方便，但是数据存在大量的重复并且会占用大量的磁盘空间，备份的时间也很长

差异备份：备份那些自从上次完全备份之后被修改过的所有文件，备份的时间节点是从上次完整备份起，备份数据量会越来越大。恢复数据时只需要恢复上次的完全备份与最佳的一次差异备份

增量备份：只有那些在上次完全备份或者增量备份后被修改的文件才会被备份以上次完整备份或上次增量备份的时间为时间点，仅备份期间内的数据变化，因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从上一次的完整备份开始到最后一次增量备份之间的所有增量依次恢复，如中间某次的备份数据损坏，将导致数据的丢失，每次增量备份都是在备份在上一次完成全备份

3、逻辑备份比较

备份方式	完全备份	差异备份	增量备份
第一次备份	表1、表2	表1、表2	表1、表2
第二次备份	表1、表2、表3	表3	表3
第三次备份	表1、表2、表3、表4	表3、表4	表4

完全备份：每次都进行完全备份，会导致备份文件占用空间巨大，并且有大量的重复数据，恢复时，直接使用完全备份的文件即可

差异备份：每次差异备份，都会备份上一次完全备份之后的数据，可能会出现重复数据。恢复时，先恢复完全备份的数据，再恢复差异备份的数据

增量备份：每次增量备份都是备份在上一次完全备份或者增量备份之后的数据，不会出现重复数据的情况，也不会占用额外的磁盘空间，恢复数据，需要按照次序恢复完全备份和增量备份的数据

4、逻辑备份的策略

全备份+增加备份一周一次的全备，全备的时间需要在不提供业务的时间区间进行 PM 10点 AM 5:00之间进行全备增量：3天/2天/1天一次增量备份

5、常用的备份

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

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

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

第三方工具备份，免费的MySQL 热备份软件 Percona XtraBackup mysqlbackup

三、备份数据库实操

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_2021-10-24.tar.xz -C /usr/local/mysql/data/
cd /usr/local/mysql/data
mv /usr/local/mysql/data/* ./

完全备份一个或多个完整的库

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

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

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

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

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

 查看备份文件

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

2、还原

使用source恢复数据库的步骤

 使用mysqldump恢复数据库的步骤

 3、定时任务备份

0 1* * 6 /usr/local/mysql/bin/mysqldump -uroot -ly test > /opt/ly_test_$(date +%Y%m%d).sql ;/usr/local/mysql/bin/mysqladmin -u root -p flush-logs

4、增量备份与恢复

一般恢复

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

基于位置恢复

数据库在某一时间点可能既有错误的操作也有正确的操作
可以基于精准的位置跳过错误的操作
发生错误节点之前的一个节点，上一次正确操作的位置点停止

基于时间点恢复

跳过某个发生错误的时间点实现数据恢复
在错误时间点停止，在下一个正确时间点开始

5、恢复实操

一般恢复

(1)、模拟丢失更改的数据的恢复步骤（直接使用恢复即可）
① 备份ly库中test1表
mysqldump -uroot -p123123 ly test1 > /opt/ly_test11.sql
② 删除ly库中test1表
drop table ky11.test1;
③ 恢复test1表
mysql -uroot -p ky11 < info-2021-08-31.sql #查看日志文件
[root@mysql data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002

基于位置恢复

在此文件中，创建了库，表，内容

 

drop table ly.test;
或者
truncate table ly.test;

查看二进制断点 

 

mysqlbinlog --no-defaults --start-position='开始点' --stop-position='结束点' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p      #恢复从位置为*开始到位置为*
为止

基于时间点恢复

mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' --stop-datetime='年-月-日小时:分钟:秒' 二进制日志 | mysql -u 用户名 -p 密码

总结

完全备份
物理冷备份 ―关闭Mysqld 服务，tar命令打包 data目录，恢复就是解压tar包逻辑备份mysqldump -u-p--datebases 库1 库2 > xxx.sql
mysqldump -u-p--all-datebases > Xxx.sql
mysqldump -u-p库1表1表2 >XXx.sql
完全恢复
mysql-u-p<xxx.sql恢复库
mysql-u-p库名<XXx.sql恢复表
增量备份
1、开启二进制日志，设置二进制日志格式MIXED
2、进行一次完全备份，可每周备份一次，通过crontab -e
3、使用mysqladm -uroot -p flush-logs刷新分割出二进制日志，由于刷新之前的数据操作都记录在老的二进制日志里，所以需要把老的二进制日志复制到一个安全的目录中保管
4、可以通过mysqlbinlog --no-defaults --base64-output=decode-rows -v二进制日志文件名查看日志内容
5、可以通过mysqlbinlog --no-defaults二进制日志文件名l mysql -uroot -p恢复丢失的数据

实验报错说明：在我做位置恢复的时候，出现了一些情况，现在我们分别说一下

第一： 在二进制文件中，我们一定要确定一下，我们做了那些操作，比如我在第一次报错的时候，因为我在02文件中做了，创建库，创建表，添加数据操作，所以我在前面只做了drop table test；删表和内容操作，但是我恢复的时候，恢复了截至到2154截点的所有内容，故我只需要删库，即可

第二：第二次是我删除了表和数据，但是我直接恢复2154后的操作，所以我这里没有建库或者建表，这里我只需要补充一下缺失的部分，她就可以恢复了

所以我们要了解drop和truncate，delete删除表时候对表产生的影响，在这里特别是表结构，直接影响到我们是否需要重新创表