Mysql 日志管理、备份与恢复
一、Mysql 日志管理
MySQL 的日志默认保存位置为 /usr/local/mysql/data
在配置文件中设置MySQL日志文件路径
vim /etc/my.cnf
[mysqld]
...
日志开启有2种方式：
一是通过配置文件开启日志；二是通过命令开启日志
通过命令修改开启的日志是临时的，关闭或重启服务后就会关闭
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
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秒 systemctl restart mysqld
或
mysql -u root -p #在数据库中设置开启慢查询的方法
set global slow_query_log=ON;
systemctl restart mysqld
二、查看日志状态
1、查看通用查询日志是否开启
mysql -u root -p show variables like 'general%';
2、查看二进制日志是否开启
show variables like 'log_bin%';
3、查看慢查询日志功能是否开启
show variables like '%slow%';
（1）查看慢查询时间设置
show variables like 'long_query_time';
（2）在数据库中设置开启慢查询的方法
set global slow_query_log=ON; #该方法重启服务失效
MySQL备份与恢复
一、数据备份的重要性
- 数据备份的重要性备份的主要目的是灾难恢复，
- 在生产环境中，数据的安全性至关重要
- 任何数据的丢失都可能产生严重的后果
- 造成数据丢失的原因
- 程序错误、
- 人为操作错误、
- 运算错误品、
- 磁盘故障、
- 灾难（如火灾、地震）和盗窃等。
二、数据备份的分类
1、从物理与逻辑的角度，备份可分为
（1）物理备份∶
对数据库操作系统的物理文件（如数据文件、日志文件等）的备份
物理备份方法：
- 冷备份（脱机备份）∶是在关闭数据库的时候进行的，将数据库数据文件进行备份
- 热备份（联机备份）∶数据库处于运行状态，依赖于数据库的日志文件进行备份
- 温备份∶数据库锁定表格（不可写入但可读）的状态下进行备份操作
（2）逻辑备份∶
对数据库逻辑组件（如∶表等数据库对象）的备份
2、从数据库的备份策略角度，备份可分为
（1）完全备份：
每次对数据库进行完整的备份
完全备份是对整个数据库、数据库结构和文件结构的备份。
保存的是备份完成时刻的数据库。
是差异备份与增量备份的基础。
备份时：每次都会进行完整的备份，会导致文件占用空间巨大，并且有大量重复的数据，
恢复时：直接使用完全备份的文件即可
优缺点：完全备份备份与恢复操作简单方便；但数据存在大量的重复，占用大量备份空间，备份与恢复时间长。
（2）差异备份：
备份自从上次完全备份之后被修改过的文件
备份时：每次差异备份，都会备份上一次完全备份之后的数据，可能出现备份重复的数据，导致占用额外的磁盘空间，
恢复时：需要先恢复完全备份的数据，再恢复差异备份的数据
（3）增量备份：
只有在上次完全备份或者增量备份后被修改的文件才会被备份
备份时：每次增量备份都是备份上一次完全备份或者增量备份后的数据，不会出现备份重复数据的情况，也不会占用额外的磁盘空间，
恢复时：恢复数据需要按照次序恢复完全备份和增量备份的数据
三、常见的备份方法
1、物理冷备（完全备份）
备份时数据库处于关闭状态，使用tar命令直接打包数据库文件夹（/data目录）
直接替换现有的MySQL目录即可
备份速度快，恢复也是最简单的
2、专用备份工具mysqldump或mysqlhotcopy
mysqldump常用的逻辑备份工具
MySQL自带的备份工具，可实现对MySQL的备份
可以将指定的库、表导出为SQL脚本
使用命令mysql导入备份的数据
mysqldump缺点：备份有重复数据，备份恢复时间较长
mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表
3、启用二进制日志进行增量备份
进行增量备份，需要刷新二进制日志，记录表数据中SQL语句，记录上一次备份后增加/变化的文件或者内容
4、第三方工具备份
免费的MySQL热备份软件Percona XtraBackup
四、MySQL完全备份与恢复
准备工作：
mysql -u root -p create database SCHOOL; use SCHOOL; create table if not exists CLASS1 ( id int(4) not null auto_increment, name varchar(10), sex char(10), hobby varchar(50),primary key (id)); insert into CLASS1 values(1,'user1','male','running'); insert into CLASS1 values(2,'user2','female','singing');
Mysql 完全备份
InnoDB存储引擎的数据库在磁盘上存储成三个文件：db.opt(表属性文件)、表名.frm(表结构文件)、表名.ibd(表数据文件)。
1、物理冷备份与恢复
systemctl stop mysqld
yum -y install xz
（1）压缩备份
tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/
（2）模式删除删除数据库SCHOOL
drop database SCHOOL;
（3）解压恢复
tar Jxvf /opt/mysql_all_2021-09-01.tar.xz -C /usr/local/mysql/data systemctl restart mysql
2、mysqldump 备份与恢复
（1）完全备份一个或多个完整的库（包括其中所有的表）
mysqldump -u root -p[密码] --databases 库名1 [库名2] … > /备份路径/备份文件名.sql
#导出的就是数据库脚本文件
例： mysqldump -uroot -pabc123 --databases SCHOOL > /opt/SCHOOL.sql mysqldump -uroot -pabc123 --databases mysql SCHOOL > /opt/mysql-SCHOOL.sql
（2）完全备份 Mysql 服务器中所有的库
mysqldump -u root -p[密码] --all-databases > /备份路径/备份文件名.sql
例：
mysqldump -u root -pabc123 --all-databases > /opt/all.sql
（3）完全备份指定库中的部分表
mysqldump -u root -p[密码] 库名 [表名1] [表名2] … > /备份路径/备份文件名.sq
例：
mysqldump -uroot -pabc123 SCHOOL CLASS1 > /opt/SCHOOL_CLASS1.sql #使用“-d”选项，说明只保存数据库的表结构 #不使用“-d”选项，说明表数据也进行备份
（4）查看备份文件
grep -v "^--" /opt/backup/AAA_test.sql | grep -v "^/" | grep -v "^$" #反匹配多余项仅剩下SQL语句
grep -v "^--" /opt/SCHOOL_CLASS1.sql | grep -v "^/" | grep -v "^$"
完全备份恢复
1、恢复数据库
mysql -uroot -pabc123 -e 'drop database SCHOOL;'
#“-e”选项，用于指定连接 MySQL 后执行的命令，命令执行完后自动退
mysql -uroot -pabc123 -e 'SHOW DATABASES;' mysql -uroot -pabc123 < /opt/SCHOOL.sql mysql -uroot -pabc123 -e 'SHOW DATABASES;'
2、恢复数据表
当备份文件中只包含表的备份，而不包含创建的库的语句时，执行导入操作时必须指定库名，且目标库必须存在。
mysql -uroot -pabc123 -e 'drop table SCHOOL.CLASS1;' mysql -uroot -pabc123 -e 'show tables from SCHOOL;' mysql -uroot -pabc123 SCHOOL < /opt/SCHOOL_CLASS1.sql mysql -uroot -pabc123 -e 'show tables from SCHOOL;'
五、MySQL 增量备份与恢复
MySQL 增量备份
使用 mysqldump 进行完全备份存在的问题
- 备份数据中有重复数据
- 备份时间与恢复时间过长
是自上一次备份后增加/变化的文件或者内容
特点：
- 没有重复数据，备份量不大，时间短
- 恢复需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份逐个反推恢复
MySQL没有提供直接的增量备份方法
可通过MySQL提供的二进制日志间接实现增量备份
- 因为存储都是每个增量期间输入的SQL语句，相当于把增量保存的SQL语句内容再次输入了一遍
MySQL二进制日志对备份的意义
（1）二进制日志保存了所有更新或者可能更新数据库的操作
（2）二进制日志在启动MySQL服务器后开始记录，并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件
（3）只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份
（4）可定期对文件进行完全备份，期间进行增量备份
1、开启二进制日志功能
vim /etc/my.cnf [mysqld] log-bin=mysql-bin binlog_format = MIXED server-id = 1
#二进制日志(binlog)有3种不同的记录格式：STATEMENT(基于SQL语句)、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMEN
systemctl restart mysqld
ls -l /usr/local/mysql/data/mysql-bin.*
2、可每周对数据库或表进行完全备份
mysqldump -uroot -pabc123 SCHOOL CLASS1 > /opt/SCHOOL_CLASS1_$(date +%F).sql mysqldump -uroot -pabc123 --all-databases SCHOOL > /opt/SCHOOL_$(date +%F).sql
或
crontab -e #使用计划性任务
例： 每周日早上8:30执行 对数据库和表进行完全备份
30 8 * * 7 mysqldump -uroot -pabc123 SCHOOL CLASS1 > /opt/SCHOOL_CLASS1_$(date +%F).sql
30 8 * * 7 mysqldump -uroot -pabc123 --all-databases SCHOOL > /opt/SCHOOL_$(date +%F).sq
3、可每天进行增量备份操作，生成新的二进制日志文件(例如 mysql-bin.000007)
mysqladmin -uroot -pabc123 flush-logs
4、插入新数据，以模拟数据的增加或变更
mysql -uroot -pabc123 use SCHOOL; insert into CLASS1 values(3,'user3','male','game'); insert into CLASS1 values(4,'user4','female','reading');
5、再次生成新的二进制日志文件(例如 mysql-bin.000008)
mysqladmin -uroot -pabc123 flush-logs
6、查看二进制日志文件的内容
cp /usr/local/mysql/data/mysql-bin.000007 /opt/ mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000007
#- -base64-output=decode-rows：使用64位编码机制去解码并按行读取
#-v：显示详细内
#210903 0:40:52 server id 1 end_log_pos 219 CRC32 0xf8876c8b Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 219 #210903 0:40:52 server id 1 end_log_pos 302 CRC32 0x610c7b11 Query thread_id=8 exec_time=0 error_code=0 SET TIMESTAMP=1630600852/*!*/; SET @@session.pseudo_thread_id=8/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1437073414/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 302 #210903 0:40:52 server id 1 end_log_pos 430 CRC32 0xb7c3a667 Query thread_id=8 exec_time=0 error_code=0 use `SCHOOL`/*!*/; SET TIMESTAMP=1630600852/*!*/; insert into CLASS1 values(3,'user3','male','game') /*!*/; # at 430 #210903 0:40:52 server id 1 end_log_pos 461 CRC32 0x920e1d1f Xid = 444 COMMIT/*!*/; # at 461 #210903 0:41:06 server id 1 end_log_pos 526 CRC32 0xc6008698 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 526 #210903 0:41:06 server id 1 end_log_pos 609 CRC32 0x3c472ed9 Query thread_id=8 exec_time=0 error_code=0 SET TIMESTAMP=1630600866/*!*/; BEGIN /*!*/; # at 609 #210903 0:41:06 server id 1 end_log_pos 742 CRC32 0xe0e96fff Query thread_id=8 exec_time=0 error_code=0 SET TIMESTAMP=1630600866/*!*/; insert into CLASS1 values(4,'user4','female','reading') /*!*/; # at 742 #210903 0:41:06 server id 1 end_log_pos 773 CRC32 0x91af0ed7 Xid = 445 COMMIT/*!*/; # at 773 #210903 0:43:25 server id 1 end_log_pos 820 CRC32 0x2e0bea4d Rotate to mysql-bin.000008 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*/;
MySQL 增量备份恢复
1、一般恢复
（1）模拟丢失更改的数据的恢复步骤
mysql -uroot -pabc123 use SCHOOL; delete from CLASS1 where id=3; delete from CLASS1 where id=4; select * from CLASS1; quit mysqlbinlog --no-defaults /opt/mysql-bin.000007 | mysql -uroot -pabc123 mysql -uroot -pabc123 -e "select * from SCHOOL.CLASS1;"
（2）模拟丢失所有数据的恢复步骤( 基本上与'一般恢复一样'，注意日志日期)
mysql -uroot -pabc123 use SCHOOL; drop table CLASS1; quit mysql -uroot -pabc123 SCHOOL < /opt/SCHOOL_CLASS1_2021-09-03.sql mysqlbinlog --no-defaults /opt/mysql-bin.000007 | mysql -uroot -pabc123 mysql -uroot -pabc123 -e "select * from SCHOOL.CLASS1;"
2、断点恢复
和之前一样使用64位编码机制去解码并按行读取二进制文件000007的详细内容
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000007
[root@localhost opt]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000007 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #210903 0:36:39 server id 1 end_log_pos 123 CRC32 0x23db3798 Start: binlog v 4, server v 5.7.20-log created 210903 0:36:39 # at 123 #210903 0:36:39 server id 1 end_log_pos 154 CRC32 0xf8c41748 Previous-GTIDs # [empty] # at 154 #210903 0:40:52 server id 1 end_log_pos 219 CRC32 0xf8876c8b Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 219 #210903 0:40:52 server id 1 end_log_pos 302 CRC32 0x610c7b11 Query thread_id=8 exec_time=0 error_code=0 SET TIMESTAMP=1630600852/*!*/; SET @@session.pseudo_thread_id=8/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1437073414/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 302 #210903 0:40:52 server id 1 end_log_pos 430 CRC32 0xb7c3a667 Query thread_id=8 exec_time=0 error_code=0 use `SCHOOL`/*!*/; SET TIMESTAMP=1630600852/*!*/; insert into CLASS1 values(3,'user3','male','game') /*!*/; # at 430 #210903 0:40:52 server id 1 end_log_pos 461 CRC32 0x920e1d1f Xid = 444 COMMIT/*!*/; # at 461 #210903 0:41:06 server id 1 end_log_pos 526 CRC32 0xc6008698 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 526 #210903 0:41:06 server id 1 end_log_pos 609 CRC32 0x3c472ed9 Query thread_id=8 exec_time=0 error_code=0 SET TIMESTAMP=1630600866/*!*/; BEGIN /*!*/; # at 609 #210903 0:41:06 server id 1 end_log_pos 742 CRC32 0xe0e96fff Query thread_id=8 exec_time=0 error_code=0 SET TIMESTAMP=1630600866/*!*/; insert into CLASS1 values(4,'user4','female','reading') /*!*/; # at 742 #210903 0:41:06 server id 1 end_log_pos 773 CRC32 0x91af0ed7 Xid = 445 COMMIT/*!*/; # at 773 #210903 0:43:25 server id 1 end_log_pos 820 CRC32 0x2e0bea4d Rotate to mysql-bin.000008 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*/;
（1）基于位置恢复
#仅恢复到操作 ID 为“609”之前的数据，即不恢复“user4”的数据
mysqlbinlog --no-defaults --stop-position='609' /opt/mysql-bin.000007 | mysql -uroot -p
例：
mysql -uroot -pabc123 -e "select * from SCHOOL.CLASS1;" mysql -uroot -pabc123 -e "truncate table SCHOOL.CLASS1;" mysql -uroot -pabc123 -e "select * from SCHOOL.CLASS1;" mysqlbinlog --no-defaults --stop-position='609' /opt/mysql-bin.000007 | mysql -uroot -p mysql -uroot -pabc123 -e "select * from SCHOOL.CLASS1;"
#仅恢复“user4”第4条的数据，跳过“user3”的数据恢复，609之后仅存在第四条数据记录
mysql -uroot -pabc123 -e "select * from SCHOOL.CLASS1;" mysqlbinlog --no-defaults --start-position='609' /opt/mysql-bin.000007 | mysql -uroot -pabc123 mysql -uroot -pabc123 -e "select * from SCHOOL.CLASS1;"
（2）基于时间点恢复
#仅恢复到 0:39:13 之前的数据，即不恢复“user4”的数据
例：
#清空表CLASS1,方便实验 mysql -uroot -pabc123 -e "truncate table SCHOOL.CLASS1;" mysql -uroot -pabc123 -e "select * from SCHOOL.CLASS1;" mysqlbinlog --no-defaults --stop-datetime='2021-09-03 0:41:06' /opt/mysql-bin.000007 |mysql -uroot -pabc123 mysql -uroot -pabc123 -e "select * from SCHOOL.CLASS1;"
#仅恢复“user4”的数据，跳过“user3”的数据恢复（基本一致）
mysql -uroot -pabc123 -e "truncate table SCHOOL.CLASS1;"
mysql -uroot -pabc123 -e "select * from SCHOOL.CLASS1;"
mysqlbinlog --no-defaults --start-datetime='2021-09-03 0:41:06' /opt/mysql-bin.000007 |mysql -uroot -p
mysql -uroot -pabc123 -e "select * from SCHOOL.CLASS1;"
