您当前的位置： 首页 > 学无止境 > 心得笔记 网站首页心得笔记
马哥linux运维学习笔记-MySQL系列之十二——MySQL日志管理
发布时间：2019-03-17 13:51:26编辑：雪饮阅读（）
在mysql控制台查看某二进制日志文件
mysql> show binlog events in 'mysql-bin.000009';
+------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------- ------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------- ------------+
| mysql-bin.000009 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.28-log, Binlog ver: 4 |
| mysql-bin.000009 | 107 | Query | 1 | 176 | BEGIN |
| mysql-bin.000009 | 176 | Query | 1 | 304 | use `mysql`; update user set password=password('dmj220807') where user='root' |
| mysql-bin.000009 | 304 | Query | 1 | 374 | COMMIT |
| mysql-bin.000009 | 374 | Query | 1 | 454 | use `mysql`; flush privileges |
| mysql-bin.000009 | 454 | Query | 1 | 521 | BEGIN |
| mysql-bin.000009 | 521 | Intvar | 1 | 549 | INSERT_ID=14 |
| mysql-bin.000009 | 549 | Query | 1 | 647 | use `db1`; insert into test(name) values('gjp') |
| mysql-bin.000009 | 647 | Xid | 1 | 674 | COMMIT /* xid=21 */ |
+------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------- ------------+
9 rows in set (0.00 sec)
通过mysqlbinlog查看某二进制日志文件
查看整个二进制文件
[root@localhost data]# mysqlbinlog mysql-bin.000005
以指定开始位置与结束位置来查看二进制文件
[root@localhost data]# mysqlbinlog --start-position=107 --stop-position=467 mysql-bin.000005
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190315 21:17:47 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.28-log created 190315 21:17:47 at startup
ROLLBACK/*!*/;
BINLOG '
+6WLXA8BAAAAZwAAAGsAAAAAAAQANS41LjI4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAD7pYtcEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#190315 21:29:51 server id 1 end_log_pos 232 Query thread_id=1 exec_time=0 error_code=0
use db1/*!*/;
SET TIMESTAMP=1552656591/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=0, @@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=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
DROP TABLE IF EXISTS `active_configs` /* generated by server */
/*!*/;
# at 232
#190315 21:29:51 server id 1 end_log_pos 467 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1552656591/*!*/;
CREATE TABLE `active_configs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`nums` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
以指定时间节点来查看二进制文件
[root@localhost data]# mysqlbinlog --start-datetime='2019-03-16 1:55:05' mysql-bin.000005
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190315 21:17:47 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.28-log created 190315 21:17:47 at startup
ROLLBACK/*!*/;
BINLOG '
+6WLXA8BAAAAZwAAAGsAAAAAAAQANS41LjI4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAD7pYtcEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 8109650
#190316 1:55:05 server id 1 end_log_pos 8109669 Stop
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
二进制日志的滚动
当mysql每次重启的时候会导致二进制日志文件的滚动
重启前
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000009 | 674 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
重启后
[root@localhost ~]# service mysqld restart
Shutting down MySQL. [ OK ]
Starting MySQL.. [ OK ]
[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000010 | 107 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> quit
也可以免重启直接使二进制日志文件滚动
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000010 | 107 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000011 | 107 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
通过mysql控制台查看当前二进制日志文件列表
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 1057 |
| mysql-bin.000002 | 874 |
| mysql-bin.000003 | 126 |
| mysql-bin.000004 | 2623 |
| mysql-bin.000005 | 8109669 |
| mysql-bin.000006 | 2114 |
| mysql-bin.000007 | 2765 |
| mysql-bin.000008 | 1756 |
| mysql-bin.000009 | 693 |
| mysql-bin.000010 | 150 |
| mysql-bin.000011 | 107 |
+------------------+-----------+
11 rows in set (0.00 sec)
通过mysql控制台删除二进制日志文件
mysql二进制文件不建议直接删除
删除mysql二进制文件
mysql> purge binary logs to 'mysql-bin.000005';
Query OK, 0 rows affected (0.01 sec)
这里是删除了文件序号为000005之前的二进制日志文件
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000005 | 8109669 |
| mysql-bin.000006 | 2114 |
| mysql-bin.000007 | 2765 |
| mysql-bin.000008 | 1756 |
| mysql-bin.000009 | 693 |
| mysql-bin.000010 | 150 |
| mysql-bin.000011 | 107 |
+------------------+-----------+
7 rows in set (0.00 sec)
关键字词：linux,mysql,日志
相关文章
- 马哥linux运维学习笔记-MySQL系列之九——MySQL事务和
- 马哥linux运维学习笔记-MySQL系列之八——多表查询、
- 马哥linux运维学习笔记-MySQL系列之七——单表查询、
- 马哥linux运维学习笔记-MySQL系列之六——MySQL管理表
- 马哥linux运维学习笔记-MySQL系列之五——MySQL数据类
- 马哥linux运维学习笔记-mysql系列之四mysql客户端工具
- 马哥linux运维学习笔记-mysql系列之三MySQL数据库基础
- 马哥linux运维学习笔记-配置使用vnc服务
- 马哥linux运维学习笔记-bash脚本编程之在bash脚本中使
- 马哥linux运维学习笔记-bash编程系列之数组