MySQL日志、备份与恢复
MySQL日志
MySQL的默认日志保存位置为/usr/local/mysql/data
通过命令修改开启的日志是临时的,关闭或重启服务后就会关闭
MySQL常用日志类型及开启
错误日志
通用查询日志
二进制日志(binlog)
慢查询日志
记得重启!
日志状态
查看通用查询日志是否开启
查看二进制日志是否开启
查看慢查询日功能是否开启
查看慢查询时间设置
在数据库中设置开启慢查询的方法
set global slow_query_log=ON;
该方法重启服务失效
MySQL备份与恢复
数据备份的重要性
备份的主要目的是灾难恢复,在生产环境中,数据的安全性至关重要,任何数据的丢失都可能产生严重的后果
数据库备份的分类
物理备份
对数据库操作系统的物理文件(如数据文件、日志文件等)的备份
物理备份方法:
冷备份(脱机备份):是在关闭数据库的时候进行的
热备份(联机备份):数据库处于运行状态,依赖于数据库的日志文件
温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作
逻辑备份
对数据库逻辑组件(如:表等数据库对象)的备份
从数据库的备份策略角度,备份可分为:
完全备份:每次对数据库进行完整的备份
完全备份是对整个数据库、数据库结构和文件结构的备份。
保存的是备份完成时刻的数据库。 是差异备份与增量备份的基础。 相当于基石。
差异备份:备份自从上次完全备份之后被修改过的文件
增量备份:只要在上次完全备份或者增量备份后被修改的文件才会被备份
常见的备份方法
物理冷备
备份时数据库处于关闭状态,直接打包数据库文件 备份速度快,恢复时也是最简单的 关闭MySQL数据库 使用tar命令直接打包数据库文件夹
直接替换现有的MySQL目录即可
备份工具mydump或者mysqlhotcopy
mysqldump常用的逻辑备份工具 MySQL自带的备份工具,可实现对MySQL的备份 可以将指定的库、表导出为SQL脚本
使用命令mysql导入备份的数据
mysqlhotcopy仅拥有备份myisam和archive表
启动二进制日志进行增量备份
进行增量备份,需要刷新二进制日志
第三方工具备份
免费的MySQL热备份软件Percona XtraBackup
MySQL完全备份与恢复
MySQL 完全备份
InnoDB存储引擎的数据库在磁盘上存储成三个文件:db.opt(表属性文件)、表名.frm(表结构文件)、表名.ibd(表数据文件)。
物理冷备份与恢复
systemctl stop mysqld
yum -y install xz
压缩备份
tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/
解压恢复
tar Jxvf /opt/mysql_all_2021-1-30.tar.xz -C /usr/local/mysql/data
systemctl restart mysql
mysqldump 备份与恢复
完全备份 MySQL 服务器中所有的库
完全备份指定库中的部分表
mysqldump -u root -p[密码] 库名 [表名1] [表名2] … > /备份路径/备份文件名.sql
mysqldump -uroot -p123123 wahaha wa1 > /opt/wahaha_wa1.sql
#使用“-d”选项,说明只保存数据库的表结构
#不使用“-d”选项,说明表数据也进行备份
查看备份文件
完全备份恢复
恢复数据库
“-e”选项,用于指定连接 MySQL 后执行的命令,命令执行完后自动退出
恢复数据表
MySQL 增量备份与恢复
MySQL 增量备份
开启二进制日志功能
可每周对数据库或表进行完全备份
每周备份的情况下需要进行计划性任务crontab -e 来创建计划完成
可每天进行增量备份操作,生成新的二进制日志文件
插入新数据,以模拟数据的增加或变更
再次生成新的二进制日志文件
刷新之后新生成的文件是现在使用的,之前的记录保存在上一个文件
查看二进制日志文件的内容
#- -base64-output=decode-rows:使用64位编码机制去解码并按行读取
#-v:显示详细内容
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#210419 16:49:49 server id 1 end_log_pos 123 CRC32 0xc68a66b3 Start: binlog v 4, server v 5.7.17-log created 210419 16:49:49
# Warning: this binlog is either in use or was not closed properly.
# at 123
#210419 16:49:49 server id 1 end_log_pos 154 CRC32 0x16b645ad Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
MySQL 增量备份恢复
一般恢复
#模拟丢失更改的数据的恢复步骤
基于位置恢复
BEGIN
/*!*/;
# at 290
#210419 16:48:14 server id 1 end_log_pos 345 CRC32 0x1cdfd3ce Table_map: `zzz`.`xxx1` mapped to number 223
# at 345
#210419 16:48:14 server id 1 end_log_pos 398 CRC32 0xcf4d8a75 Write_rows: table id 223 flags: STMT_END_F
### INSERT INTO `zzz`.`xxx1`
### SET
### @1=3
### @2='u3'
### @3='male'
### @4='game'
# at 398
#210419 16:48:14 server id 1 end_log_pos 429 CRC32 0xc59a0560 Xid = 999
COMMIT/*!*/;
# at 429
#210419 16:48:34 server id 1 end_log_pos 494 CRC32 0xd195265b Anonymous_GTID last_committed=1 sequence_number=2
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 494
#210419 16:48:34 server id 1 end_log_pos 565 CRC32 0x3afbeebe Query thread_id=17 exec_time=0 error_code=0
SET TIMESTAMP=1618822114/*!*/;
BEGIN
/*!*/;
# at 565
#210419 16:48:34 server id 1 end_log_pos 620 CRC32 0xc793bd14 Table_map: `zzz`.`xxx1` mapped to number 223
# at 620
#210419 16:48:34 server id 1 end_log_pos 678 CRC32 0x8c2d52cd Write_rows: table id 223 flags: STMT_END_F
### INSERT INTO `zzz`.`xxx1`
### SET
### @1=4
### @2='u4'
### @3='female'
### @4='reading'
# at 678
#210419 16:48:34 server id 1 end_log_pos 709 CRC32 0x01de0178 Xid = 1000
COMMIT/*!*/;
# at 709
#210419 16:49:49 server id 1 end_log_pos 756 CRC32 0x3c931c52 Rotate to mysql-bin.000004 pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
基于时间点恢复
与断点恢复相同,position改为datetime