MySQL服务日志介绍及增量恢复命令

mysqlbinlog命令介绍

mysql -->解析mysql的binlog日志

mysql的binlog日志是什么

在数据目录下以mysql-bin开头的文件

如何开启binlog日志

打开mysql 的配置文件my.cnf
在mysqld配置项下面加上log_bin=mysql_bin

[root@192 data]# cat /etc/my.cnf 
[mysqld]
user=mysql
basedir=/aplication/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=6
port=3306
log_bin=mysql_bin
[mysql]
socket=/tmp/mysql.sock

mysql的binlog日志作用是什么

用来记录mysql内部增删改查等对mysql数据库有更新的内容的记录

使用mysqlbinlog语句查看mysql-bin开头的文件

[root@192 data]# mysqlbinlog mysql_bin.000001 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#211205 20:53:17 server id 6  end_log_pos 123 CRC32 0x0f67b3e4 	Start: binlog v 4, server v 5.7.26-log created 211205 20:53:17 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
PbasYQ8GAAAAdwAAAHsAAAABAAQANS43LjI2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAA9tqxhEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AeSzZw8=
'/*!*/;
# at 123
#211205 20:53:17 server id 6  end_log_pos 154 CRC32 0xe177a737 	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*/;

#指定数据库
mysqlbinlog -d world mysql_bin.000001;
参数：
-d:指定只看某一个数据库的操作日志

#指定位置点恢复
mysqlbinlog mysql_bin.000001 --start-position=4 --stop-position=123 -r world.sql
参数：
--start-position/--stop-position:在日志从从at 4开始到at 123结束的所有语句

#指定时间恢复
mysqlbinlog mysql_bin.000001 --start-datetime='2021-12-05 13:00:00' --stop-datetime='2021-12-05 13:14:00' -r world.sql
参数：
--start-datetime/--stop-datetime:从2021-12-05 13:00:00开始到2021-12-05 13:14:00结束的所有语句

mysqldump中- -master-data

作用：

例如凌晨1点备份，但是早上5点数据丢失，1点-5点之间的数据只能从mysqlbinlog中获取，- -master-data的作用就是找到1点那个时间点开始的内容

MySQL服务错误日志

• error log：记录MySQL服务进程mysqld在启动/关闭或运行过程中遇到的错误信息
• 查询日志：
  普通查询日志：记录客户端连接信息和执行的SQL语句信息；
  慢查询日志：记录执行时间超出指定值的SQL语句；
• 二进制日志：记录数据被修改的相关信息；

如何开启MySQL错误日志

在 /etc/my.cnf中指定log-error=/var/log/mysql/error.log

[root@test01 ~]# cat /etc/my.cnf 
[mysqld]
user=mysql
basedir=/aplication/mysql
datadir=/data01/mysql/data
socket=/tmp/mysql.sock
server_id=6
port=3306
log_bin=mysql_bin
log-error=error.log
[mysql]
socket=/tmp/mysql.sock

MySQL服务二进制日志的三种工作模式

Statement Level模式

每一条修改数据的sql会记录到master的bin-log中，slave在复制的时候sql进程会解析成和原来master端执行的相同的sql再来执行
优点：
不需要记录每一行数据的变化，减少bin-log日志量，节约IO，提高性能。
缺点：
MySQL发现比较快，很多新功能不断加入，会造成MySQL复制问题

Row Level模式

日志中会记录成每一行数据被修改的形式，然后再slave端再对相同的数据进行修改
优点：
记录下每一行数据修改的细节
缺点：
bin-log日志量会很大

Mixed模式

就是前两种牧师的结合，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式。

MySQL服务二进制日志的三种工作模式配置方法

运行时在线修改

SET SESSION binlog_format = 'STATEMENT'
SET SESSION binlog_format = 'ROW'
SET SESSION binlog_format = 'MIXED'

全局生效

SET GLOBAL binlog_format = 'STATEMENT'
SET GLOBAL binlog_format = 'ROW'
SET GLOBAL binlog_format = 'MIXED'

[root@test01 ~]# mysql -uroot -p1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.26-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL binlog_format = 'MIXED'
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
[root@test01 ~]# mysql -uroot -p1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.26-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.00 sec)

mysql>