目录
MySQL日志管理
日志简介
错误日志
mysql> show variables like 'log_error';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| log_error | ./db01.err |
+---------------+------------+
## 错误日志日志名：
$HOSTNAME.err
## 错误日志存储位置：
$datadir 数据目录下
## 错误日志默认是否开启：
开启的
## 如何修改配置：
vim /etc/my.cnf
[mysqld]
log_error=/opt/1.txt
## 日志文件必须提前创建出来，并且针对mysql用户有写入权限
touch /opt/1.txt
chown -R mysql.mysql /opt/1.txt
一般查询日志
root@localhost [(none)] >show variables like '%general%';
+------------------+--------------------------+
| Variable_name | Value |
+------------------+--------------------------+
| general_log | OFF |
| general_log_file | /app/mysql/data/db04.log |
+------------------+--------------------------+
## 常规日志日志名：
$HOSTNAME.log
## 常规日志存储位置：
$datadir 数据目录下
## 常规日志默认是否开启：
关闭
## 如何修改配置：
vim /etc/my.cnf
[mysqld]
general_log=1
general_log_file=/opt/xxx.log
二进制日志 binlog
mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------+
| log_bin | ON |
+---------------------------------+---------------------------------+
## 常规的日志名
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
...
## 常规日志存储位置：
$datadir 数据目录下
## 常规日志默认是否开启
关闭
## 如何修改配置
### 开启binlog MySQL5.6
vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format=row
#log-bin=/opt/zls-bin
### 开启binlog MySQL5.7
vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server_id=1
binlog_format=row
binlog的工作模式
statement 语句模式
记录MySQL的SQL语句 DDL DML DCL
## MySQL5.6 默认语句模式
mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
# 优缺点
- 优点：
- 易读
- 占用磁盘空间小
- 缺点：
- 不严谨
row 行级模式
记录MySQL的SQL语句 DDL、DCL，DML记录每一行的变化过程
### ## MySQL5.7 默认行级模式
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
# 优缺点
- 优点：
- 严谨
- 缺点
- 不易读
- 占用磁盘空间大
mixed 混合模式
statement 和 row 的混合
一般运维不用
查看binlog
## 查看语句模式
mysqlbinlog binlog名字
[root@db01 data]# mysqlbinlog mysql-bin.000001
## 查看行级模式
[root@db03 ~]# mysqlbinlog --base64-output=decode-rows -vvv /app/mysql/data/mysql-bin.000013
# 查看当前的binlog
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000016 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
# 查看有哪些binlog，每个binlog的大小
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 622 |
| mysql-bin.000002 | 143 |
| mysql-bin.000003 | 387 |
| mysql-bin.000004 | 120 |
| mysql-bin.000005 | 120 |
| mysql-bin.000006 | 120 |
| mysql-bin.000007 | 120 |
| mysql-bin.000008 | 120 |
| mysql-bin.000009 | 120 |
| mysql-bin.000010 | 120 |
| mysql-bin.000011 | 120 |
| mysql-bin.000012 | 143 |
| mysql-bin.000013 | 143 |
| mysql-bin.000014 | 167 |
| mysql-bin.000015 | 2686 |
| mysql-bin.000016 | 120 |
+------------------+-----------+
# 查看binlog事件
mysql> show binlog events in 'mysql-bin.000015'\G
*************************** 1. row ***************************
Log_name: mysql-bin.000015
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 120
Info: Server ver: 5.6.50-log, Binlog ver: 4
*************************** 2. row ***************************
Log_name: mysql-bin.000015
Pos: 120
Event_type: Query
Server_id: 1
End_log_pos: 220
Info: create database binlog
*************************** 3. row ***************************
Log_name: mysql-bin.000015
Pos: 220
Event_type: Query
Server_id: 1
End_log_pos: 330
Info: use `binlog`; create table test_binlog(id int)
*************************** 4. row ***************************
Log_name: mysql-bin.000015
Pos: 330
Event_type: Query
Server_id: 1
End_log_pos: 404
Info: BEGIN
····等····
查看binlog内容和导出
mysqlbinlog --start-position=120 --stop-position=1035 mysql-bin.000007 > /tmp/test_binlog.sql
# -d db1 指定只查看db1库的操作
mysqlbinlog -d db1 --start-position=120 --stop-position=1035 mysql-bin.000007 > /tmp/test_binlog.sql
事件介绍
1）在binlog中最小的记录单元为event
2）一个事务会被拆分成多个事件（event）
## MySQL5.6 事件（event）特性
1）每个event都有一个开始位置（start position）和结束位置（stop position）。
2）所谓的位置就是event对整个二进制的文件的相对位置。
3）对于一个二进制日志中，前120个position是文件格式信息预留空间。
4）MySQL第一个记录的事件，都是从120开始的。
5）MySQL5.6空的binlog 143
## MySQL5.7 事件（event）特性
1）每个event都有一个开始位置（start position）和结束位置（stop position）。
2）所谓的位置就是event对整个二进制的文件的相对位置。
3）对于一个二进制日志中，前154个position是文件格式信息预留空间。
4）MySQL第一个记录的事件，都是从154开始的。
5）MySQL5.7空的binlog 177
使用binlog恢复数据案例
模拟数据
# 查看binlog信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000016 | 218 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 刷新一个新的binlog
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
# 查看binlog信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000017 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 创建一个binlog库
mysql> create database binlog;
Query OK, 1 row affected (0.00 sec)
# 使用binlog库
mysql> use binlog
Database changed
# 创建test_binlog表
mysql> create table test_binlog(id int);
Query OK, 0 rows affected (0.00 sec)
# 插入数据1
mysql> insert into test_binlog values(1);
Query OK, 1 row affected (0.00 sec)
# 提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 查看binlog信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000017 | 531 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 插入数据2
mysql> insert into test_binlog values(2);
Query OK, 1 row affected (0.00 sec)
# 插入数据3
mysql> insert into test_binlog values(3);
Query OK, 1 row affected (0.00 sec)
# 提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 查看binlog信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000017 | 933 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 查看test_binlog表内的内容
mysql> select * from test_binlog;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
# 更改数据1为10
mysql> update test_binlog set id=10 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 查看test_binlog表内的信息
mysql> select * from test_binlog;
+------+
| id |
+------+
| 10 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
# 查看binlog信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000017 | 1140 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 删除数据3
mysql> delete from test_binlog where id=3;
Query OK, 1 row affected (0.00 sec)
# 提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 查看test_binlog表内的信息
mysql> select * from test_binlog;
+------+
| id |
+------+
| 10 |
| 2 |
+------+
2 rows in set (0.00 sec)
# 查看binlog信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000017 | 1341 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 查看库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| baixiong |
| mysql |
| performance_schema |
| test |
| xx |
+--------------------+
模拟数据故障
# 删除test_binlog表
mysql> drop table test_binlog;
Query OK, 0 rows affected (0.00 sec)
# 删除binlog库
mysql> drop database binlog;
Query OK, 0 rows affected (0.00 sec)
# 查看binlog信息
mysql> show master status;
+------------------+----------+
| File | Position |
+------------------+----------+
| mysql-bin.000017 | 1558 |
+------------------+----------+
恢复数据
# 1.查看当前使用的是哪个binlog？
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000017 | 1558 | | | |
+------------------+----------+--------------+------------------+-------------------+
# 一般binlog写入的都为最后一个
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 622 |
| mysql-bin.000002 | 143 |
| mysql-bin.000003 | 387 |
| mysql-bin.000004 | 120 |
| mysql-bin.000005 | 120 |
| mysql-bin.000006 | 120 |
| mysql-bin.000007 | 120 |
| mysql-bin.000008 | 120 |
| mysql-bin.000009 | 120 |
| mysql-bin.000010 | 120 |
| mysql-bin.000011 | 120 |
| mysql-bin.000012 | 143 |
| mysql-bin.000013 | 143 |
| mysql-bin.000014 | 167 |
| mysql-bin.000015 | 2686 |
| mysql-bin.000016 | 265 |
| mysql-bin.000017 | 1558 |
+------------------+-----------+
# 2.使用mysqlbinlog命令查看binlog内容
[root@db01 ~]# cd /app/mysql/data/
[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000017
# 3.找到起点和结束的位置
120 和 1270
标签：binlog,log,管理,##,MySQL,120,mysql,日志
From： https://www.cnblogs.com/xiutai/p/17749316.html