56,679
社区成员
发帖
与我相关
我的任务
分享请问关于mysql数据库查询日志的问题
求教各大神，mysql数据库本身是否自带操作日志，可以记录数据库里各表中数据的变更情况呢？例如什么时候哪个表里的数据有了变更？有这样的日志吗？怎样查看呢？
...全文
请发表友善的回复…
发表回复
LongRui888 2015-09-08
- 打赏
- 举报
我的是windows里的，其实在linux还简单。。。
其实就是 先切换到 mysqlbinlog的这个程序的目录下，然后 mysqlbinlog --start-position=4 --stop-position=3000 "C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql.000001" >c:\test.txt 是关键。
position是日志的位置，一个是开始，一个是结束，然后后面那个 "C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql.000001" 是日志文件的位置和路径，最后 把输出内容重定向到 c:\test.txt
小白619 2015-09-08
- 打赏
- 举报
C:\Program Files\MySQL\MySQL Server 5.6\bin>mysqlbinlog --start-position=4 --stop-position=3000 "C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql.000001" >c:\test.txt
看不懂啊LongRui888 2015-09-08
- 打赏
- 举报
最后，登录到 mysql里，执行如下的命令：
mysql> source c:\\test.txt
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Charset changed
Query OK, 0 rows affected (0.00 sec)
LongRui888 2015-09-08
- 打赏
- 举报
mysql.000001日志文件怎么打开啊？只能用语句查看内容吗？[/quote]
C:\Program Files\MySQL\MySQL Server 5.6\bin>mysqlbinlog --start-position=4 --stop-position=3000 "C:\ProgramData\MySQL\MySQL Server 5.6\data\mysql.000001" >c:\test.txt
C:\Program Files\MySQL\MySQL Server 5.6\bin>more c:\test.txt
/*!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
#150907 18:24:38 server id 123456789 end_log_pos 120 CRC32 0x6cc46a1b Start: b
inlog v 4, server v 5.6.25-log created 150907 18:24:38 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
5mXtVQ8VzVsHdAAAAHgAAAABAAQANS42LjI1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADmZe1VEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAARtq
xGw=
'/*!*/;
# at 120
#150907 18:37:38 server id 123456789 end_log_pos 201 CRC32 0x5fad937b Query
thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1441622258/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.uniq
ue_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1344274432/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/
;
/*!\C gbk *//*!*/;
SET @@session.character_set_client=28,@@session.collation_connection=28,@@sessio
n.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 201
# at 233
#150907 18:37:38 server id 123456789 end_log_pos 233 CRC32 0x0a9d7cab Intvar
SET INSERT_ID=4/*!*/;
#150907 18:37:38 server id 123456789 end_log_pos 350 CRC32 0x3ad17a20 Query
-- More (25%) --
小白619 2015-09-08
- 打赏
- 举报
mysql.000001日志文件怎么打开啊？只能用语句查看内容吗？
rick-he 2015-09-07
- 打赏
- 举报
binlog日志
http://bbs.csdn.net/topics/391820759
ACMAIN_CHM 2015-09-07
- 打赏
- 举报
.
LongRui888 2015-09-07
- 打赏
- 举报
首先你要启用binlog，先看看：
mysql> show variables like 'log_bin%';
+---------------------------------+--------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------------------------+
| log_bin | ON |
| log_bin_basename | C:\ProgramData\MySQL\MySQL Server 5.6\Data\mysql |
| log_bin_index | C:\ProgramData\MySQL\MySQL Server 5.6\Data\mysql.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+--------------------------------------------------------+
5 rows in set (0.01 sec)
如果没有启用，可以配置文件 my.inf 加入：
log-bin = mysql
然后重启mysql，就可以了。
然后，我们可以插入数据：
mysql> use world
Database changed
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
| t1 |
| tb |
| tb1 |
| test |
| test1 |
+-----------------+
8 rows in set (0.08 sec)
mysql> select * from t1 ;
+----+------+------+
| id | name | age |
+----+------+------+
| 3 | aa | 30 |
+----+------+------+
1 row in set (0.01 sec)
这里插入数据：
mysql> insert into t1(name,age) values('bb',100);
Query OK, 1 row affected (0.04 sec)
mysql> insert into t1(name,age) values('cc',300);
Query OK, 1 row affected (0.05 sec)
然后，找到binlog的日志文件 mysql.000001：
mysql> show master status;
+--------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| mysql.000001 | 642 | | | |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.05 sec)
然后，就可以查看日志：
mysql> show binlog events in 'mysql.000001';
+--------------+-----+-------------+-----------+-------------+--------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+--------------+-----+-------------+-----------+-------------+--------------------------------------------------------+
| mysql.000001 | 4 | Format_desc | 123456789 | 120 | Server ver: 5.6.25-log, Binlog ver: 4 |
| mysql.000001 | 120 | Query | 123456789 | 201 | BEGIN |
| mysql.000001 | 201 | Intvar | 123456789 | 233 | INSERT_ID=4 |
| mysql.000001 | 233 | Query | 123456789 | 350 | use `world`; insert into t1(name,age) values('bb',100) |
| mysql.000001 | 350 | Xid | 123456789 | 381 | COMMIT /* xid=15 */ |
| mysql.000001 | 381 | Query | 123456789 | 462 | BEGIN |
| mysql.000001 | 462 | Intvar | 123456789 | 494 | INSERT_ID=6 |
| mysql.000001 | 494 | Query | 123456789 | 611 | use `world`; insert into t1(name,age) values('cc',300) |
| mysql.000001 | 611 | Xid | 123456789 | 642 | COMMIT /* xid=17 */ |
+--------------+-----+-------------+-----------+-------------+--------------------------------------------------------+
9 rows in set (0.02 sec)
