为什么要用日志？
你和女朋友吵架了，每次都指出你的错误，而你每次都不改，然后女朋友就不搭理你了，所以为了哄女朋友，你就把你的错误写在笔记本上，记录为什么会犯错误，原因是什么，怎么去避免等。然后吸取教训。
作用: 记录数据库启动以来，状态、警告、报错。诊断数据库报错问题
SQL语句查询日志位置
mysql> select @@log_error; +-------------+ | @@log_error | +-------------+ | ./db01.err | +-------------+ 1 row in set (0.00 sec) mysql> select @@datadir; +------------------+ | @@datadir | +------------------+ | /data/3306/data/ | +------------------+ 1 row in set (0.00 sec)
对错误日志自定义文件目录
[root@db01 ~]# mkdir -p /data/3306/logs [root@db01 ~]# chown -R mysql.mysql /data/* [root@db01 ~]# vim /etc/my.cnf [mysqld] #添加： log_error=/data/3306/logs/mysql.err
重启数据库，啊偶报错了，
# 重启报错 [root@db01 ~]# /etc/init.d/mysqld restart ++++++++++++++++++++++ Shutting down MySQL.. SUCCESS! Starting MySQL.2020-05-09T06:51:36.457401Z mysqld_safe error: log-error set to '/data/3306/logs/mysql.err', however file don't exists. Create writable for user 'mysql'. ERROR! The server quit without updating PID file (/data/3306/data/db01.pid).
报错了要学会看error 上面说的是那一个文件没有存在 让我们手动touch一个
[root@db01 ~]# touch /data/3306/logs/mysql.err [root@db01 ~]# chown -R mysql.mysql /data/* # 重新启动 [root@db01 ~]# /etc/init.d/mysqld restart
查看日志
模拟吵架
[root@db01 ~]# chown -R root.root /data/3306/data/ibdata1 [root@db01 ~]# /etc/init.d/mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL.. ERROR! The server quit without updating PID file (/data/3306/data/db01.pid). [root@db01 ~]#
哄女朋友
egrep '\[ERROR\]' /data/3306/logs/mysql.err 2020-05-09T06:56:09.226056Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
记录错误
cp mysql.err mysql.err_`date +%F` echo > mysql.err
二进制日志（binlog）
数据恢复
主从复制
记录的内容介绍
记录修改类操作(逻辑日志，类似于SQL记录)。 DML：insert update delete DDL: create drop alter trucate DCL: grant revoke
配置方法
基础参数查询： mysql> select @@log_bin; mysql> select @@log_bin_basename; mysql> select @@server_id; 设置基础参数： vim /etc/my.cnf server_id=6 # 主机ID，在主从复制会使用 log_bin=/data/3306/logs/mysql-bin # 开关+文件路径+文件名前缀 最终格式：mysql-bin.000001 # 重启生效 [root@db01 logs]# /etc/init.d/mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL.. SUCCESS!
binlog内容的记录格式
事件（event）的方式记录 最小的记录单元 每个事件： (1)事件描述 ：事件戳、server_id、加密方式、开始的位置（start_pos）、结束位置点（end_pos） (2)事件内容 : 修改类的操作：SQL 语句 或者 数据行变化。 # 重点关注： 开始的位置（start_pos） ：1000字节 结束位置点（end_pos） ： 事件内容
二进制日志事件内容格式
mysql> select @@binlog_format; +-----------------+ | @@binlog_format | +-----------------+ | ROW | +-----------------+ 作用： 对于DDL、DCL语句，直接将SQL本身记录到binlog中 对于DML ：insert、update、delete 受到binlog_format参数控制。 SBR ：Statement : 语句模式。之前版本，默认模式 RBR ：ROW ：行记录模式。5.7以后，默认模式 MBR ：miexd : 混合模式。 SBR、RBR区别： update t1 set name='zhangsan' where id<100; SBR: 记录SQL本身 。RBR: 100个数据行的变化。 SBR日志量少，RBR日志量大。 SBR记录不够准确，RBR记录够准确。
binlog 的应用
日志文件情况查询
# 查看所有的日志文件信息 mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 154 | +------------------+-----------+ # 刷新一个新的日志 mysql> flush logs; mysql> flush logs; mysql> flush logs; mysql> flush logs; mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 201 | | mysql-bin.000002 | 201 | | mysql-bin.000003 | 201 | | mysql-bin.000004 | 201 | | mysql-bin.000005 | 154 | +------------------+-----------+ # 当前数据库使用的二进制日志 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000005 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
内容查询
# 数据模拟 mysql> create database ku charset utf8mb4; mysql> use ku Database changed mysql> create table biao (id int); mysql> insert into biao values(1); mysql> commit; 查看日志事件 mysql> show binlog events in 'mysql-bin.000005'; +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | mysql-bin.000005 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.28-log, Binlog ver: 4 | | mysql-bin.000005 | 123 | Previous_gtids | 6 | 154 | | | mysql-bin.000005 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000005 | 219 | Query | 6 | 323 | create database ku charset utf8mb4 | | mysql-bin.000005 | 323 | Anonymous_Gtid | 6 | 388 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000005 | 388 | Query | 6 | 484 | use `ku`; create table biao (id int) | | mysql-bin.000005 | 484 | Anonymous_Gtid | 6 | 549 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000005 | 549 | Query | 6 | 619 | BEGIN | | mysql-bin.000005 | 619 | Table_map | 6 | 664 | table_id: 108 (ku.biao) | | mysql-bin.000005 | 664 | Write_rows | 6 | 704 | table_id: 108 flags: STMT_END_F | | mysql-bin.000005 | 704 | Xid | 6 | 735 | COMMIT /* xid=24 */ | +------------------+-----+----------------+-----------+-------------+---------------------------------------+
查看日志内容
[root@db01 logs]# mysqlbinlog /data/3306/logs/mysql-bin.000005 # create table 日志内容 # at 388 #200509 15:49:59 server id 6 end_log_pos 484 CRC32 0x8b05dfaf Query thread_id=3 exec_time=0 error_code=0 use `ku`/*!*/; create table biao (id int) /*!*/; # at 484
insert 操作的日志内容
# at 549 #200509 15:50:36 server id 6 end_log_pos 619 CRC32 0x515eec96 Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1589010636/*!*/; BEGIN /*!*/; # at 619 #200509 15:50:36 server id 6 end_log_pos 664 CRC32 0xedfb82fb Table_map: `ku`.`biao` mapped to number 108 # at 664 #200509 15:50:36 server id 6 end_log_pos 704 CRC32 0x5762132e Write_rows: table id 108 flags: STMT_END_F BINLOG ' zGC2XhMGAAAALQAAAJgCAAAAAGwAAAAAAAEAAmt1AARiaWFvAAEDAAH7gvvt zGC2Xh4GAAAAKAAAAMACAAAAAGwAAAAAAAEAAgAB//4BAAAALhNiVw==' /*!*/; # at 704 #200509 15:50:40 server id 6 end_log_pos 735 CRC32 0xb06a212b Xid = 24 COMMIT/*!*/;
binlog日志截取及恢复演练
# 故障模拟 mysql> drop database ku; Query OK, 1 row affected (0.01 sec) # 需求恢复ku的所有数据到删库之前。 思路： 1. 截取从建库以来到删库之前的所有binlog。 ### 起点：建库的位置点（position）。 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000005 | 886 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> show binlog events in 'mysql-bin.000005'; | mysql-bin.000005 | 219 | Query | 6 | 323 | create database ku charset utf8mb4 ### 终点：删库的位置点（position）。 | mysql-bin.000005 | 800 | Query | 6 | 886 | drop database ku [root@db01 logs]# mysqlbinlog --start-position=219 --stop-position=800 /data/3306/logs/mysql-bin.000005 >/tmp/bin.sql 2. 将截取的日志进行回放。 mysql> set sql_log_bin=0; mysql> source /tmp/bin.sql; mysql> set sql_log_bin=1;
彩蛋：
思考一下：如果生产中会有什么痛点？
- 需要的日志在多个文件中，怎么截取？
# 场景模拟： mysql> show master status ; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000005 | 886 | | | | +------------------+----------+--------------+------------------+-------------------+ mysql> create database tongdian charset=utf8mb4; mysql> use tongdian mysql> create table t1 (id int); mysql> flush logs; mysql> show master status ; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000006 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ mysql> insert into t1 values(1),(2),(3); mysql> commit; mysql> flush logs; mysql> show master status ; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000007 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ mysql> create table t2(id int); mysql> insert into t2 values(1),(2),(3); mysql> commit; mysql> flush logs; mysql> show master status ; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000008 | 154 | | | | mysql> insert into t2 values(11),(22),(33); mysql> commit; mysql> drop database tongdian; # 恢复方法： 方法1：分段截取 --start-position --stop-position 方法2: 时间戳截取 1. 找起点 ：建库的时间戳 （1） 起点：postion 号 mysql> show binlog events in 'mysql-bin.000005'; | mysql-bin.000005 | 951 | Query | 6 | 1073 | create database tongdian charset=utf8mb4 | （2） 通过position 过滤时间戳 [root@db01 logs]# mysqlbinlog --start-position=951 --stop-position=1073 mysql-bin.000005 |grep -A 1 '^\#\ at\ 951' # at 951 #200509 17:11:23 server id 6 end_log_pos 1073 CRC32 0x220759ef Query thread_id=8 exec_time=0 error_code=0 2. 找终点 [root@db01 logs]# mysql -e "show binlog events in 'mysql-bin.000008'" +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | mysql-bin.000008 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.28-log, Binlog ver: 4 | | mysql-bin.000008 | 123 | Previous_gtids | 6 | 154 | | | mysql-bin.000008 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000008 | 219 | Query | 6 | 295 | BEGIN | | mysql-bin.000008 | 295 | Table_map | 6 | 344 | table_id: 112 (tongdian.t2) | | mysql-bin.000008 | 344 | Write_rows | 6 | 394 | table_id: 112 flags: STMT_END_F | | mysql-bin.000008 | 394 | Xid | 6 | 425 | COMMIT /* xid=114 */ | | mysql-bin.000008 | 425 | Anonymous_Gtid | 6 | 490 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000008 | 490 | Query | 6 | 594 | drop database tongdian | mysqlbinlog mysql-bin.000008 [root@db01 logs]# mysqlbinlog mysql-bin.000008 #200509 17:13:52 3. 截取日志 [root@db01 logs]# mysqlbinlog --start-datetime="2020-05-09 17:11:23" --stop-datetime="2020-05-09 17:14:01" mysql-bin.000005 mysql-bin.000006 mysql-bin.000007 mysql-bin.000008 >/tmp/data.sql # 登录数据库恢复 mysql> source /tmp/data.sql
binlog属于全局日志，日志中有其他库的操作，怎么排除掉？
mysqlbinlog -d oldboy mysql-bin.000008 > /tmp/bin.sql
binlog中100w个事件，怎么快速找到drop database的位置点？
mysql> pager grep "DROP" [root@db01 ~]# mysql -e "show binlog events in 'mysql-bin.000014'" |less [root@db01 ~]# mysql -e "show binlog events in 'mysql-bin.000014'" |grep
比如删除的库，建库是在2年前操作的。这种情况怎么办？
每天全备，binlog完好的。 可以使用 全备+binlog方式实现恢复数据故障之前。
基于GTID的binlog应用
GTID 全局事务ID。 对每个事务，进行单独编号。连续不断进行增长。 表示方式 server_uuid:N
配置
# 查看参数 mysql> show variables like '%GTID%'; # 设置参数 vim /etc/my.cnf gtid_mode=ON #开关 enforce_gtid_consistency=ON #强制GTID一致性 log_slave_updates=ON #强制从库更新binlog
模拟环境
mysql> create database gtdb charset utf8mb4; mysql> show master status ; +------------------+----------+--------------+------------------+----------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+----------------------------------------+ | mysql-bin.000016 | 329 | | | 202628e9-9265-11ea-b4a0-000c29248f69:1 | +------------------+----------+--------------+------------------+----------------------------------------+ mysql> use gtdb; mysql> create table t1(id int); mysql> show master status ; +------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------+ | mysql-bin.000016 | 491 | | | 202628e9-9265-11ea-b4a0-000c29248f69:1-2 | +------------------+----------+--------------+------------------+------------------------------------------+ mysql> begin; mysql> insert into t1 values(1); mysql> insert into t1 values(2); mysql> insert into t1 values(3); mysql> commit; mysql> show master status ; +------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------+ | mysql-bin.000016 | 914 | | | 202628e9-9265-11ea-b4a0-000c29248f69:1-3 | +------------------+----------+--------------+------------------+------------------------------------------+
通过GTID方式截取日志
mysqlbinlog --skip-gtids --include-gtids='202628e9-9265-11ea-b4a0-000c29248f69:1-3' /data/3306/logs/mysql-bin.000016 >/tmp/gt1.sql
日志滚动
# 命令触发mysql> flush logs; shell# mysqladmin flush-logsshell# mysql -e "flush logs"shell# mysqldump -F # 自动触发mysql> select @@max_binlog_size;+-------------------+| @@max_binlog_size |+-------------------+| 1073741824 |+-------------------+ 重启数据库，会触发刷新
日志删除方式
默认：不自动清理。直到空间写满。 # 配置自动清理 mysql> select @@expire_logs_days; 最少设置多少天合适？ 参考全备时间周期。 例如：全备周期是7天。可以保留8天。一般生产中保留两轮备份周期的日志，15天。 # 手工清理 Examples: PURGE BINARY LOGS TO 'mysql-bin.010'; PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26';
慢日志 SlOWLOG
#在线配置 mysql> select @@slow_query_log; # 开关 mysql> set global slow_query_log=1; # 在线改 mysql> select @@slow_query_log_file; # 文件位置。离线改。 mysql> select @@long_query_time; # 慢查询时间设定。 mysql> set global long_query_time=0.1; # 在线设置，最低微秒级别。 mysql> select @@log_queries_not_using_indexes #如果没走索引会被记录 mysql> set global log_queries_not_using_indexes=1; #在线设置 #永久生效： vim /etc/my.cnf slow_query_log=1 slow_query_log_file=/data/3306/logs/slow.log long_query_time=0.1 log_queries_not_using_indexes=1 #重启生效。
