首页 > 代码库 > MySQL之日志管理(一)
MySQL之日志管理(一)
MySQL的日志有以下六种:
错误日志:服务器启动、关闭、运行中产生的错误信息、及event(事件调度)运行一个事件时产生的信息、及从服务器上启动和关闭从服务器进程时产生的信息。
一般查询日志:general_log、general_log_file.
慢查询日志:查询时间超过指定的查询时间的记录
二进制日志:任何引起或者可能引起数据变化的操作都要记录进二进制日志(DDL、DML、DCL语句);二进制日志又叫做几十点还原,可在server崩溃后将数据还原到崩溃的那一刻。
中继日志:中继日志就是从服务器复制的主服务器上的二进制日志,其格式一样,在从服务器上会根据中级日志的记录重新执行一遍,进行数据备份
事务日志:只有支持事务的存储引擎(如innodb)才有事务日志,事务日志保证了数据的ACID,并将随机I/O转化为顺序I/O。
mysql> show global variables like ‘%log%‘; 查询与日志有关的变量
错误日志文件参数:
log_error: /mydata/data/localhost.localdomain.err(数据目录下当前主机名+.err)
log_warnings
一般查询日志参数:
general_log
general_log_file: /mydata/data/localhost.log
log_output={table|file|none} table则日志全记录入表,file则日志全记录进文件,none不记录
man查询日志参数:
long_query_time=# 超过long_query_time的查询都要记录,默认为10s
slow_query_log={on|off} 启用或关闭记录慢查询日志,启用不代表记录
slow_query_log_file=/path 慢查询日志保存目录
log_slow_queries={yes|no} 慢查询日志是否启用,启用不代表记录
>set global slow_query_log=1;启用慢查询日志记录
二进制日志的格式:
1.基于语句: statement
2.基于行: row
3.混合方式: mixed
二进制日志事件一般要记录两个内容:
1.记录这个事件产生的时间(starttime)
2.这个事件在整个事件中的相对位置
让二进制日志文件不要和数据存放在同一个磁盘上,当数据丢失或者崩溃时,可以用备份 + 二进制日志的方式将数据还原到数据崩溃的那一刻
[root@node1 ~]# cd /mydata/data/
[root@node1 data]# ls
master-bin.000002 master-bin.000006 master-bin.000010
master-bin.000003 master-bin.000007 master-bin.000011
master-bin.000004 master-bin.000008 master-bin.000012
master-bin.000005 master-bin.000009 master-bin.000013
mysql> show master status; 查看当前使用的日志文件时哪个
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000021 | 673 | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.14 sec)
当前使用的是master-bin.00002
事件相对于整个事件的位置为673
mysql> show binlog events in ‘master-bin.000021‘; 查看二进制日志文件
+-------------------+-----+-------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------------+-----+-------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------+
| master-bin.000021 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.34-log, Binlog ver: 4
| master-bin.000021 | 120 | Query | 1 | 207 | BEGIN |
| master-bin.000021 | 207 | Query | 1 | 344 | use `students`; insert into test1 (cid,name,sid) values (4,‘faded‘,‘D‘)
| master-bin.000021 | 344 | Xid | 1 | 375 | COMMIT /* xid=40 */ |
| master-bin.000021 | 375 | Query | 1 | 549 | use `mysql`; CREATE USER ‘faker‘@‘127.0.0.1‘ IDENTIFIED BY PASSWORD ‘*23AE809DDACAF96AF0FD78ED04B6A265E05AA257‘ |
| master-bin.000021 | 549 | Query | 1 | 673 | use `mysql`; rename user faker@‘127.0.0.1‘ to sky@‘127.0.0.1‘
+-------------------+-----+-------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------+
6 rows in set (0.02 sec)
Pos 事件的起始位置
Event_type 事件类型
Server_id 服务器id号
End_log_pos 事件结束位置
Info 文件头及进行的操作
mysql> show binlog events in ‘master-bin.000021‘ from 344; 从304起始位置查看
+-------------------+-----+------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------------+-----+------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------+
| master-bin.000021 | 344 | Xid | 1 | 375 | COMMIT /* xid=40 */ |
| master-bin.000021 | 375 | Query | 1 | 549 | use `mysql`; CREATE USER ‘faker‘@‘127.0.0.1‘ IDENTIFIED BY PASSWORD ‘*23AE809DDACAF96AF0FD78ED04B6A265E05AA257‘ |
| master-bin.000021 | 549 | Query | 1 | 673 | use `mysql`; rename user faker@‘127.0.0.1‘ to sky@‘127.0.0.1‘ |
+-------------------+-----+------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
除此之外还可以用mysqlbinlog命令查看日志文件(二进制格式的二进制文件不能用cat查看)
mysqlbinlog
--start-datetime
--stop-datetime
--start-position
--stop-position
[root@node1 data]# mysqlbinlog master-bin.000021
[root@node1 data]# mysqlbinlog --start-position=207 --stop-position=375 master-bin.000021
[root@node1 data]# mysqlbinlog --start-datetime=‘17-02-13 9:06:21‘ master-bin.000021
还可以将其导入一个文件中,然后再将该文件导入另外一个数据库中,就可以按照二进制日志重新执行一遍
[root@node1 data]# mysqlbinlog --start-datetime=‘17-02-13 9:06:21‘ master-bin.000021 >/root/my.sql
mysql> flush logs; 手动进行日志滚动
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000022 | 120 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
如果我已经将某时刻之前的数据进行了备份,应该删除该时刻之前的二进制日志,从而节省空间
mysql> purge binary logs to ‘master-bin.0000010‘; 将‘master-bin.0000010‘之前的日志全删除
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000022 | 120 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binary logs; 查看当前拥有的二进制日志
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| master-bin.000001 | 69435 |
| master-bin.000002 | 1371047 |
| master-bin.000003 | 143 |
| master-bin.000004 | 143 |
| master-bin.000005 | 120 |
| master-bin.000006 | 143 |
| master-bin.000007 | 4836454 |
| master-bin.000008 | 21561 |
| master-bin.000009 | 143 |
| master-bin.000010 | 143 |
| master-bin.000011 | 120 |
| master-bin.000012 | 120 |
| master-bin.000013 | 120 |
| master-bin.000014 | 143 |
| master-bin.000015 | 120 |
| master-bin.000016 | 143 |
| master-bin.000017 | 120 |
| master-bin.000018 | 818 |
| master-bin.000019 | 2220 |
| master-bin.000020 | 4692 |
| master-bin.000021 | 721 |
| master-bin.000022 | 120 |
+-------------------+-----------+
本文出自 “11097124” 博客,请务必保留此出处http://11107124.blog.51cto.com/11097124/1897457
MySQL之日志管理(一)