MySQL的日志管理

于 2018-07-03 15:23:27 发布
阅读量383 收藏
点赞数
CC 4.0 BY-SA版权
分类专栏： ……【mysql】 MySQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/miner_k/article/details/80870672

错误日志：记录错误信息

包含内容

  • 服务器启动和关闭过程中的信息
  • 服务器运行过程中的错误信息
  • 事务调度器运行一个事务时产生的信息
  • 在从服务器上启动从服务器进程是产生的信息
错误日志相关变量值含义
log_error./miner-k.err存储在数据目录中，并以当前主机名.err
log_warnings1警告信息是否记录到错误日志中

一般查询日志：保留select查询记录

日志相关变量值含义
general_logOFF一般查询设置为关闭状态
general_log_file/mydata/data/miner-k.log一般查询日志的存储位置，可以将日志信息存储到表中
log_outputfile(文件)|table|none可以设置日志的输出结果为文件、表、不保存日志。

慢查询日志：超过指定查询时间的语句

涉及的变量值含义
long_query_time10.000000定义记录慢日志的查询语句大于10s
slow_query_logOFF是否启动慢查询日志
slow_query_log_file/mydata/data/miner-k-slow.log慢日志的记录位置
log_outputfile(文件)|table|none可以设置日志的输出结果为文件、表、不保存日志。
log_slow_queriesOFF慢日志查询是否开启和slow_query_log 的设置同步

二进制日志：记录任何可能引起数据库变化的操作。

MySQL复制、及时点还原。

二进制日志的格式：

  • 基于语句：statement
  • 基于行：row
  • 混合方式：mixed

二进制日志的保存：
重启mysqld会产生新的二进制日志
当二进制日志足够大时会产生新的日志。

及时点还原：如果过数据库挂掉，需要恢复数据，首先是通过全量备份恢复，全量备份到数据库挂掉之间的时间段需要使用二进制日志回复。

二进制日志事件：二进制中记录的每一个信息或者是每条语句
产生时间，相对位置，

二进制日志文件的滚动

  1. 重启mysqld

  2. flush logs；

    mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000008 |      367 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.07 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000009 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

二进制文件的类型

二进制日志文件的类型：索引文件、二进制日志文件

[root@miner-k data]# ls /mydata/data/mysql-bin.
mysql-bin.000001  mysql-bin.000003  mysql-bin.000005  mysql-bin.000007  mysql-bin.index   
mysql-bin.000002  mysql-bin.000004  mysql-bin.000006  mysql-bin.000008

这些都是mysql的二进制文件，mysql-bin.index是所有二进制文件的索引。

查看当前使用的二进制文件

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000008 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.03 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000008 |       410 |
| mysql-bin.000009 |       107 |
+------------------+-----------+
2 rows in set (0.00 sec)

查询binlog日志中的具体内容（事件）

数据库中直接查看
mysql> insert into student (name,cid,gender,age) values ('tomm',1,'F',30);
Query OK, 1 row affected (0.15 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000008 |      367 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000008';
+------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                                               |
+------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------------------------+
| mysql-bin.000008 |   4 | Format_desc |         1 |         107 | Server ver: 5.5.59-log, Binlog ver: 4                                              |
| mysql-bin.000008 | 107 | Query       |         1 |         179 | BEGIN                                                                              |
| mysql-bin.000008 | 179 | Intvar      |         1 |         207 | INSERT_ID=15                                                                       |
| mysql-bin.000008 | 207 | Query       |         1 |         340 | use `students`; insert into student (name,cid,gender,age) values ('tomm',1,'F',30) |
| mysql-bin.000008 | 340 | Xid         |         1 |         367 | COMMIT /* xid=15 */                                                                |
+------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------------------------+
5 rows in set (0.03 sec)

指定从某个位置开始

mysql> show binlog events in 'mysql-bin.000008' from 107;
+------------------+-----+------------+-----------+-------------+------------------------------------------------------------------------------------+
| Log_name         | Pos | Event_type | Server_id | End_log_pos | Info                                                                               |
+------------------+-----+------------+-----------+-------------+------------------------------------------------------------------------------------+
| mysql-bin.000008 | 107 | Query      |         1 |         179 | BEGIN                                                                              |
| mysql-bin.000008 | 179 | Intvar     |         1 |         207 | INSERT_ID=15                                                                       |
| mysql-bin.000008 | 207 | Query      |         1 |         340 | use `students`; insert into student (name,cid,gender,age) values ('tomm',1,'F',30) |
| mysql-bin.000008 | 340 | Xid        |         1 |         367 | COMMIT /* xid=15 */                                                                |
+------------------+-----+------------+-----------+-------------+------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
使用命令指定二进制日志

使用mysalbinlog命令查看二进制日志

[root@miner-k data]# mysqlbinlog mysql-bin.000008 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;





# at 4
#180630  1:11:58 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.59-log created 180630  1:11:58 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
Tjs3Ww8BAAAAZwAAAGsAAAABAAQANS41LjU5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABOOzdbEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;

上一段是文件头

# at 107    #站点位置
#180630  7:07:34 server id 1  end_log_pos 179 （结束站点）    Query   thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1530367654/*!*/;   #事件的发生时间
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;

–start-position=107 –stop-position=308 指定二进制日志站点的开始和结束位置

[root@miner-k data]# mysqlbinlog --start-position=107 --stop-position=308  mysql-bin.000008 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180630  1:11:58 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.59-log created 180630  1:11:58 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
Tjs3Ww8BAAAAZwAAAGsAAAABAAQANS41LjU5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABOOzdbEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#180630  7:07:34 server id 1  end_log_pos 179   Query   thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1530367654/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 179
#180630  7:07:34 server id 1  end_log_pos 207   Intvar
SET INSERT_ID=15/*!*/;
# at 207
#180630  7:07:34 server id 1  end_log_pos 340   Query   thread_id=3 exec_time=0 error_code=0
use `students`/*!*/;
SET TIMESTAMP=1530367654/*!*/;
insert into student (name,cid,gender,age) values ('tomm',1,'F',30)
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

–start-time=’2018-06-30 7:07:34’ 指定开始时间

[root@miner-k data]# mysqlbinlog --start-time='2018-06-30 7:07:34'  mysql-bin.000008 
mysqlbinlog: unknown variable 'start-time=2018-06-30 7:07:34'
[root@miner-k data]# mysqlbinlog --start-datetime='2018-06-30 7:07:34'  mysql-bin.000008 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180630  1:11:58 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.59-log created 180630  1:11:58 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
Tjs3Ww8BAAAAZwAAAGsAAAABAAQANS41LjU5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABOOzdbEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#180630  7:07:34 server id 1  end_log_pos 179   Query   thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1530367654/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 179
#180630  7:07:34 server id 1  end_log_pos 207   Intvar
SET INSERT_ID=15/*!*/;
# at 207
#180630  7:07:34 server id 1  end_log_pos 340   Query   thread_id=3 exec_time=0 error_code=0
use `students`/*!*/;
SET TIMESTAMP=1530367654/*!*/;
insert into student (name,cid,gender,age) values ('tomm',1,'F',30)
/*!*/;
# at 340
#180630  7:07:34 server id 1  end_log_pos 367   Xid = 15
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

注：通过该命令输出的结果重定向到一个*.sql文件中，可以直接使用.sql文件直接导入数据。

删除二进制日志文件

在数据库的存储位置的binlog日志都在，只是修改了mysql-bin.index中的内容。

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000008 |       410 |
| mysql-bin.000009 |       150 |
| mysql-bin.000010 |       150 |
| mysql-bin.000011 |       150 |
| mysql-bin.000012 |       150 |
| mysql-bin.000013 |       150 |
| mysql-bin.000014 |       150 |
| mysql-bin.000015 |       150 |
| mysql-bin.000016 |       150 |
| mysql-bin.000017 |       107 |
+------------------+-----------+
10 rows in set (0.00 sec)

mysql> purge binary logs to 'mysql-bin.000013';
Query OK, 0 rows affected (0.04 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000013 |       150 |
| mysql-bin.000014 |       150 |
| mysql-bin.000015 |       150 |
| mysql-bin.000016 |       150 |
| mysql-bin.000017 |       107 |
+------------------+-----------+
5 rows in set (0.00 sec)

二进制日志相关的变量

变量名称变量的值含义
binlog_formatMIXED二进制日志的格式
log_binON是不是记录二进制日志
binlog_cache_size32768二进制日志的缓存的大小
binlog_stmt_cache_size32768和事务相关的二进制日志的缓存大小（数据库语句缓存）
log_binON二进制日志是否开启，可以指定二进制日志的存储位置，如果使用默认on，表示二进制日志存储在mysql的数据目录中
sql_log_binON控制二进制日志是否记录进日志文件，如果mysql需要记录二进制日志需要开启log_bin和sql_log_bin 这两个变量的值。
sync_binlog0设置多久同步二进制日志到磁盘文件中，0表示不同步，任何正数值表示对二进制每多少次写操作同步一次，当autocommit=1，每条语句执行一次。如果设置为5，表示每5个写操作同步一次。
expire_logs_days0自动清除二进制日志，设置二进制日志的保存天数。

同步：数据库中语句执行之后，不在内存中存留，直接写在磁盘上
异步：将数据库中的语句或者执行结果，保存在内存中（缓存），达到一定的值之后。再写在磁盘上。

中继日志

中继日志：在从服务器上需要执行的二进制日志,并保存的日志文件。

事务日志

事务日志：保证事务的ACID（原子性、完整性、隔离性、永久性），将随机io转变为顺序IO

事务日志中记录事务的ID、影响的数据库、数据表、数据库中的原始数据和新数据， 如果事务中drop table删除表操作是是无法恢复的。事务可以rollback恢复是由于事务日志中有数据的修改记录。

二进制日志和事务日志的区别：
二进制日志可以用来重放，重新执行，但是无法直接恢复。
但是事务日志可以直接恢复。

变量名称值含义
innodb_flush_log_at_trx_commit1控制事务日志从缓存到日志文件中提交，1表示每事务同步，并执行磁盘flush操作。0表示每秒同步，并执行磁盘flush操作。2表示每事务同步，但是不执行磁盘flush操作，由系统决定磁盘flush。
innodb_log_buffer_size8388608内存缓存大小
innodb_log_file_size5242880日志文件大小
innodb_log_files_in_group2事务日志组中日志的个数，如果一个事务较大时，一个事务日志存放不了，需要两个事务日志在存储。
innodb_log_group_home_dir./事务日志的存放位置
innodb_mirrored_log_groups1指定事务日志组的个数，事务日志的备份

事务日志

[root@miner-k data]# pwd
/mydata/data
[root@miner-k data]# ll
total 28824
-rw-rw---- 1 mysql mysql 18874368 Jun 30 07:07 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Jul  1 14:03 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Jun 21 10:55 ib_logfile1

存储引擎

MySQL是插件式存储引擎，可以支持外来的其他的存储引擎。

MyISM：
不支持事务
不支持外键
表锁
B树索引、FULLTEXT索引、空间索引
表压缩

Innodb
支持事务
行级锁
B树索引、聚簇索引、自适应hash索引
使用表空间、raw磁盘设备（裸设备，没有文件系统）

MRG_MYISAM
将逻辑结构相同的表结合使用

CSV：文本文件的格式

ARCHIVE：数据归档
MEMORY：创建临时表 。
BLOCKHOLE：黑洞

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值