一、MySQL日志管理
1、MySQL日志文件
- 常用的日志文件(在/etc/my.cnf中[mysqld]客户端配置中修改)
- MySQL的默认日志保存位置为/usr/local/mysql/data
[mysqld]
错误日志
用于记录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
慢查询日志
用来记录所有执行时间超过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
二、查看日志状态命令
1、查看通用查询日志是否开启
mysql -u root -p
show variables like 'general%';
2、查看二进制日志是否开启
show variables like 'log_bin%';
3、查看慢查询日功能是否开启
show variables like '%slow%';
查看慢查询时间设置
show variables like 'long_query_time';
在数据库中设置开启慢查询的方法(临时)
set global slow_query_log=ON;
该方法重启服务失效
三、备份的重要性
- 在企业中数据的价值至关重要,数据保障了企业业务的正常运行。因此,数据的安全性及数据的可靠性是运维的重中之重,任何数据的丢失都可能对企业产生严重的后果。
1、造成数据丢失的原因有如下几种
- 程序错误
- 人为操作错误
- 运算错误
- 磁盘故障
- 灾难(如火灾、地震)和盗窃
四、备份类型
1、从物理与逻辑的角度分类
数据库备份可以分为物理备份和逻辑备份。物理备份是对数据库操作系统的物理文件
(如数据文件、日志文件等)的备份。这种类型的备份适用于在出现问题时需要快速恢复的大型重要数据库。物理备份又可以分为
冷备份(脱机备份)、热备份(联机备份)和温备份。
-
冷备份:在数据库关闭状态下进行备份操作。(tar)
-
热备份:在数据库处于运行状态时进行备份操作,该备份方法依赖数据库的日志文件。(mysqldump)
-
温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作。
-
逻辑备份是对数据库逻辑组件(如表等数据库对象)的备份,表示为逻辑数据库结构(CREATE DATABASB,CREATBTABLE语句)和内容(INSERT语句或分隔文本文件)的信息。这种类型的备份适用于可以编辑数据值或表结构较小的数据量,或者在不同的机器体系结构上重新创建数据
2、从数据库的备份策略角度分类
从数据库的备份策略角度,数据库的备份可分为完全备份、差异备份和增量备份(面试点)
- 完全备份:每次对数据进行完整的备份,即对整个数据库、数据库结构和文件结构的备份,保存的是备份完成时刻的数据库,是差异备份与增量备份的基础。完全备份的备份与恢复操作都非常简单方便,但是数据存在大量的重复,并且会占用大量的磁盘空间,备份的时间也很长。
- 差异备份:备份那些自从上次完全备份之后被修改过的所有文件,备份的时间节点是从上次完整备份起,备份数据量会越来越大。恢复数据时,只需恢复上次的完全备份与最近的一次差异备份。
- 增量备份:只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。以上次完整备份或上次增量备份的时间为时间点,仅备份这之间的数据变化,因而备份的数据量小,占用空间小,备份速度快。但恢复时,需要从上一次的完整备份开始到最后一次增量备份之的所有增量依次恢复,如中间某次的备份数据损坏,将导致数据的丢失。
3、备份方法
数据库的备份可以采用很多种方式,如直接打包数据库文件(物理冷备份)、专用备份工具(mysqldump)、二进制日志增量备份、第三方工具备份等。
- 物理冷备份
物理冷备份时需要在数据库处于关闭状态下,能够较好地保证数据库的完整性。物理冷备份一般用于非核心业务,这类业务一般都允许中断,物理冷备份的特点就是速度快,恢复时也是最为简单的。通常通过直接打包数据库文件夹(/usr/local/mysql/data)来实现备份 - 专用备份工具mysqldump或mysqlhotcopy
mysqldump程序和mysqlhotcopy都可以做备份。
mysqldump是客户端常用逻辑备份程序,能够产生一组被执行以后再现原始数据库对象定义和表数据的sgr语句。它可以转储一个到多个MysQI数据库,对其进行备份或传输到远程sQI服务器。mysqldump更为通用,
因为它可以备份各种表。
mysqlhotcopy 仅适用于某些存储引擎。 - 通过启用二进制日志进行增量备份MySQL
支持增量备份,进行增量备份时必须启用二进制日志。二进制日志文件为用户提供复制,对执行备份点后进行的数据库更改所需的信息进行恢复。如果进行增量备份(包含自上次完全备份或增量备份以来发生的数据修改),需要刷新二进制日志。 - 通过第三方工具备份 Percona XtraBackup是一个免费的 MySgL热备份软件,支持在线热备份Innodb 和xtraDB,也可以支持MySQL表备份,不过 MyISAM表的备份要在表锁的情况下进行。
五、数据库冷备份与恢复及完全备份与恢复的基本命令
1、物理冷备份与恢复
systemctl stop mysqld
yum -y install xz (一种压缩工具,详单与gzip,bz2)
压缩备份
tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/
解压恢复
tar Jxvf /opt/mysql_all_2021-04-14.tar.xz -C /usr/local/mysql/data
systemctl restart mysql
2、mysqldump 备份与恢复
完全备份一个或多个完整的库(包括其中所有的表)
mysqldump -u root -p[密码] --databases 库名1 [库名2] … > /备份路径/备份文件名.sql #导出的就是数据库脚本文件
例:
mysqldump -uroot -proot --databases hj > /opt/hj.sql
完全备份 MySQL 服务器中所有的库
mysqldump -u root -p[密码] --all-databases > /备份路径/备份文件名.sql
例:
mysqldump -uroot -proot --all-databases > /opt/all_databases.sql
完全备份指定库中的部分表
mysqldump -u root -p[密码] 库名 [表名1] [表名2] … > /备份路径/备份文件名.sql
例:
mysqldump -uroot -proot hj info2 > /opt/hj_info2.sql
#使用“-d”选项,说明只保存数据库的表结构
mysqldump -u root -p[密码] -d 库名 [表名1] [表名2] … > /备份路径/备份文件名.sql
#不使用“-d”选项,说明表数据也进行备份
3、查看备份文件
cat /opt/备份的文件 |grep -v "^--" | grep -v "^/" | grep -v "^$"
cat /opt/hj_member5.sql |grep -v "^--" |grep -v "^/" |grep -v "^$"
grep -v "^--" /opt/hj_member5.sql |grep -v "^/" |grep -v "^$"
cat /opt/hj_member5.sql |egrep -v "^--|^/|^$"
4、MySQL 完全恢复
恢复数据库
#“-e”选项,用于指定连接 MySQL 后执行的命令,命令执行完后自动退出
mysql -uroot -proot -e "drop database hj;"
mysql -uroot -proot -e "show databases;"
mysql -uroot -proot < /opt/hj.sql
mysql -uroot -proot -e "show databases;"
恢复数据表
当备份文件中只包含表的备份,而不包含创建的库的语句时,执行导入操作时必须指定库名,且目标库必须存在。
mysql -uroot -proot -e "drop table hj.member5;"
mysql -uroot -proot -e "show tables from hj;"
mysql -uroot -proot hj < /opt/hj_member5.sql
mysql -uroot -proot -e "show tables from hj;"
在表中操作恢复
source /opt/school_test.sql
六、MySQL增量备份与恢复
1、二进制日志(binlog)有三种不同的记录格式
STATEMENT(基于SQL语句)
binlog format=STATEMENT默认
每一条涉及到被修改的sql都会记录在binlog中。
缺点:日志量过大,如sleep()函数, last_insert_id()>,以及user-definedfunctions(udf)、主从复制等架构记录日志时会出问题会出现问题
ROW(基于行)
shell binlog format=ROw
只记录变动的记录,不记录sql的上下文环境。
缺点:如果遇到updata … set … where true那么就binlog的数据量就变大
MIXED(混合模式)
binlog format=M工XED推荐使用
一般的语句使用statement,函数使用ROW方式存取。
2、MySQL增量备份
vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id = 1
binlog_format = MIXED #指定二进制日志(binlog)的记录格式为 MIXED
#二进制日志(binlog)有3种不同的记录格式:STATEMENT(基于SQL语句)、ROW(基于行)、MIXED(混合模式),默认格式是STATEMENT
systemctl restart mysqld.service
ls -l /usr/local/mysql/data/mysql-bin.*
可每周对数据库或表进行完全备份(增量备份是基于完全备份,所以这里我们直接备份数据库)
#完全备份
mysqldump -u root -p hj aa > /opt/back/hj_aa_$(date +%F).sql
mysqldump -u root -p hj > /opt/back/hj_$(date +%F).sql
#使用crontab -e 计划性任务来执行;每周1凌晨2点对表test和school库进行完全备份
0 2 * * 1 mysqldump -u root -p hj aa > /opt/back/hj_aa_$(date +%F).sql
0 2 * * 1 mysqldump -u root -p hj > /opt/back/hj_$(date +%F).sql
可每天进行增量备份操作,生成新的二进制日志文件(例如 mysql-bin.000002)
mysqladmin -u root -p flush-logs
插入新数据,以模拟数据的增加或变更
mysql -uroot -proot
use hj
select * from aa;
insert into aa values (4,'xry','女');
insert into aa values (5,'wc','男');
再次生成新的二进制日志文件(例如 mysql-bin.000003)
mysqladmin -u root -p flush-logs
#上面我们往test表中插入数据的操作会保存到mysql-bin.000002文件中,之后数据库数据再发生变化则保存在mysql-bin.000003文件中
查看二进制日志文件的内容
cp /usr/local/mysql/data/mysql-bin.000002 /opt/back
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/back/mysql-bin.000002
#--base64-output=decode-rows:使用64位编码机制去解码并按行读取
#-v:显示详细内容
可以放到其他目录查看也可以在本地查看
3、MySQL 增量恢复
一般恢复
模拟丢失更改的数据的恢复步骤
delete from aa where id=4;
delete from aa where id=5;
select *from aa;
断点恢复
mysqladmin -uroot -proot flush-logs
进行日志刷新
查看我们刚才操作保存的日志000005二进制日志文件
mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000005
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#221102 23:03:24 server id 1 end_log_pos 123 CRC32 0xb3a8831c Start: binlog v 4, server v 5.7.20-log created 221102 23:03:24
# at 123
#221102 23:03:24 server id 1 end_log_pos 154 CRC32 0xd97fa0c0 Previous-GTIDs
# [empty]
# at 154
#221102 23:06:52 server id 1 end_log_pos 219 CRC32 0x96b843d3 Anonymous_GTID last_committed=0sequence_number=1 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#221102 23:06:52 server id 1 end_log_pos 345 CRC32 0xd563edb2 Query thread_id=3 exec_time=0 error_code=0
use `hj`/*!*/;
SET TIMESTAMP=1667401612/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
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/*!*/;
create table bb (id int(5),name varchar(10), age int(5))
/*!*/;
# at 345
#221102 23:08:55 server id 1 end_log_pos 410 CRC32 0x219b0f5e Anonymous_GTID last_committed=1sequence_number=2 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 410
#221102 23:08:55 server id 1 end_log_pos 485 CRC32 0x23acff03 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1667401735/*!*/;
BEGIN
/*!*/;
# at 485
#221102 23:08:55 server id 1 end_log_pos 594 CRC32 0xfc0eb909 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1667401735/*!*/;
insert into bb values (1,'张三','21')
/*!*/;
# at 594
#221102 23:08:55 server id 1 end_log_pos 625 CRC32 0xdfea14a7 Xid = 60
COMMIT/*!*/;
# at 625
#221102 23:09:09 server id 1 end_log_pos 690 CRC32 0xd8ff1432 Anonymous_GTID last_committed=2sequence_number=3 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 690
#221102 23:09:09 server id 1 end_log_pos 765 CRC32 0xe75b78d9 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1667401749/*!*/;
BEGIN
/*!*/;
# at 765
#221102 23:09:09 server id 1 end_log_pos 874 CRC32 0x439f5c15 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1667401749/*!*/;
insert into bb values (2,'李四','22')
/*!*/;
# at 874
#221102 23:09:09 server id 1 end_log_pos 905 CRC32 0x17ff12fa Xid = 61
COMMIT/*!*/;
# at 905
#221102 23:09:26 server id 1 end_log_pos 970 CRC32 0x774529ed Anonymous_GTID last_committed=3sequence_number=4 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 970
#221102 23:09:26 server id 1 end_log_pos 1045 CRC32 0x568ef43b Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1667401766/*!*/;
BEGIN
/*!*/;
# at 1045
#221102 23:09:26 server id 1 end_log_pos 1154 CRC32 0xf38bd87b Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1667401766/*!*/;
insert into bb values (4,'王五','23')
/*!*/;
# at 1154
#221102 23:09:26 server id 1 end_log_pos 1185 CRC32 0x4b37a593 Xid = 62
COMMIT/*!*/;
# at 1185
#221102 23:13:43 server id 1 end_log_pos 1250 CRC32 0xd911c6e9 Anonymous_GTID last_committed=4 sequence_number=5 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1250
#221102 23:13:43 server id 1 end_log_pos 1325 CRC32 0x6f2b835d Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1667402023/*!*/;
BEGIN
/*!*/;
# at 1325
#221102 23:13:43 server id 1 end_log_pos 1421 CRC32 0xbd6906dd Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1667402023/*!*/;
delete from bb where id =2
/*!*/;
# at 1421
#221102 23:13:43 server id 1 end_log_pos 1452 CRC32 0x11103239 Xid = 64
COMMIT/*!*/;
# at 1452
#221102 23:14:22 server id 1 end_log_pos 1517 CRC32 0x23a127b4 Anonymous_GTID last_committed=5 sequence_number=6 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1517
#221102 23:14:22 server id 1 end_log_pos 1592 CRC32 0xfdc9f2cb Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1667402062/*!*/;
BEGIN
/*!*/;
# at 1592
#221102 23:14:22 server id 1 end_log_pos 1699 CRC32 0x421cb5ff Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1667402062/*!*/;
insert into bb values (3,'赵六',25)
/*!*/;
# at 1699
#221102 23:14:22 server id 1 end_log_pos 1730 CRC32 0x9572b4b4 Xid = 65
COMMIT/*!*/;
# at 1730
#221102 23:18:03 server id 1 end_log_pos 1777 CRC32 0x054f617d Rotate to mysql-bin.000006 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*/;
基于位置恢复
这边我们模拟删除id=2的操作为误删除 我们进行恢复
恢复到操作ID为‘1325’之前的数据,就是不执行删除id=2的操作
mysqlbinlog --no-defaults --stop-position='1325' /usr/local/mysql/data/mysql-bin.000005 | mysql -uroot -proot
意思就是跳过我们删除的操作其余的都恢复
mysqlbinlog --no-defaults --start-position='4' /usr/local/mysql/data/mysql-bin.000005 | mysql -uroot -proot
操作前首先要删除需要备份的表
--stop-position是到哪个节点停止
--stop-position='1325'是到1325这个节点停止,可以恢复我们开始插入的id=1、2、4的数据。
这样就到删除2的数据之前就停止。
select *from bb;
--start-position是从哪个节点开始恢复
--start-position='1592'是从节点1592这个节点开始恢复,可以恢复插入的id=3的数据。
select *from bb;
基于时间点恢复
删除hj库中的bb表,先基于完整备份的恢复,在基于时间点恢复
#仅恢复到221102 23:13:43之前的数据,就是不执行删除id=2的操作直接跳过
mysqlbinlog --no-defaults --stop-datetime='2022-11-02 23:13:43' /usr/local/mysql/data/mysql-bin.000005 | mysql -uroot -proot
#跳过221102 23:14:22的数据恢复之后的数据
mysqlbinlog --no-defaults --start-datetime='2022-11-02 23:14:22' /usr/local/mysql/data/mysql-bin.000005 | mysql -uroot -proot
总结:断点恢复
如果恢复某条SQL语句之前的所有数据,就stop在这个语句的位置节点或者时间点
如果恢复某条SQ语句以及之后的所有数据,就从这个语句的位置节点或者时间点start