Mysql数据库日志

Mysql物理文件与数据库日志

数据库6种日志

慢查询日志：log-slow-queries记录所有超过long_query_time时间的SQL语句，

二进制日志：记录任何引起数据变化的操作，用于备份和还原。默认存放在数据目录中，在刷新和服务重启时会滚动二进制日志。

错误日志：MySQL服务启动和关闭过程中的信息以及其它错误和警告信息。默认在数据目录下

普通查询日志：用于记录select查询语句的日志。general_log、general_log_file 默认关闭，建议关闭。

事务日志：保证事务的一致性。

中继日志：从主服务器的二进制文件中复制的事件，并保存为二进制文件，格式和二进制日志一样

慢日志

MySQL 慢查询的相关参数解释

语法示例
show variables like '%log_output%';

set GLOBAL log_output = 'TABLE';


相关参数
slow_query_log    ：是否开启慢查询，1开启，0关闭
slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
long_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志。
log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。
log_output：日志存储方式。log_output='FILE'表示将日志存入文件，默认值是'FILE'。log_output='TABLE'表示将日志存入数据库，日志会写入到mysql.slow_log表中。

MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，
如：log_output='FILE,TABLE'。

日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。

二进制日志

1、相关配置

server-id = 1                                       # mysql5.7必须加，否则mysql服务启动报错
log-bin = /data/3306/tmp/binlog/mysql-bin           # 路径及命名，默认在data下
expire_logs_days = 10                               # 过期时间,二进制文件自动删除的天数,0代表不删除
max_binlog_size = 100M                              # 单个日志文件大小

2、通过show variables like ‘log_bin%’查看二进制日志设置

mysql> show variables like 'log_bin%';
+---------------------------------+---------------------------------------+
| Variable_name                   | Value                                 |
+---------------------------------+---------------------------------------+
| log_bin                         | ON                                    |
| log_bin_basename                | /data/3306/tmp/binlog/mysql-bin       |
| log_bin_index                   | /data/3306/tmp/binlog/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                   |
| log_bin_use_v1_row_events       | OFF                                   |
+---------------------------------+---------------------------------------+

3、查看当前服务器所有的二进制日志文件 show binary logs  /  show master logs

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       732 |
+------------------+-----------+

4、查看当前二进制日志状态  show master status

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      732 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

5、使用二进制日志恢复数据库

如果开启了二进制日志，出现了数据丢失，可以通过二进制日志恢复数据库，语法如下

mysqlbinlog [option] filename | mysql -u user -p passwd

option的参数主要有两个 --start-datetime  --stop-datetime 和 start-position  --stop-position 
前者指定恢复的时间点，后者指定恢复的位置（位置指的是二进制文件中 # at 580  580就是位置）。
原理就是把记录的语句重新执行了一次，如果恢复了两次。会产生重复数据。

mysqlbinlog --start-position="291" --stop-position="439" /data/3306/tmp/binlog/mysql-bin.000001 | mysql -uroot -p111111

 注意，要找到插入更新的语句所在的时间点或位置。如果恢复的语句包含只有delete，会报错1032错误。

6、二进制日志的三种模式

二进制日志三种格式：STATEMENT，ROW，MIXED，由参数binlog_format控制

1、STATEMENT模式（SBR）
每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况（如非确定函数）下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

2、ROW模式（RBR）
不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。

3、 MIXED模式（MBR）
以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式