mysql日志

最新推荐文章于 2025-09-13 23:06:41 发布

转载最新推荐文章于 2025-09-13 23:06:41 发布 · 52 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/goudingcheng/blog/597104

文章标签：

#python #数据库

2019独角兽企业重金招聘Python工程师标准>>>

mysqlbinlog日志的作用用来记录mysql增删改对数据库跟新的操作
mysql错误日志
查询日志
普通查询
genaral_log
general_log_file
mysql> show variables like 'ge%';
+------------------+---------------------------+
| Variable_name    | Value                     |
+------------------+---------------------------+
| general_log      | ON                        |
| general_log_file | /data/3307/mysql-3307.log |
+------------------+---------------------------+
2 rows in set (0.34 sec)

160102 2:50:13        3 Query   start slave
            4 Connect Out   root@192.168.88.172:3306
160102 2:50:49        3 Query   show slave status
160102 2:50:57        3 Query   show slave status
160102 2:53:37        5 Query   CREATE database tt01
160102 2:53:45        3 Query   show databases
160102 2:54:30        3 Query   mysql> CREATE database tt01
160102 3:44:03        3 Query   Query OK, 1 row affected (0.00 sec)

show precesslist
160102 3:44:14        3 Query   show processlist
160102 22:28:17        3 Query   show variables like 'ge%'

慢查询--记录执行时间超出指定值的sql语句
long_query_time=1
long-slow-queries=/data/3306/
log_queries_not using_indexes
log_bin
sql_log_bin零时不记录binlog
errorlog
和pid都放在my.cnf里mysqlsafe
bin_log三种模式》5.1
statemenmt模式，每一条修改数据的sql都会记录到master的binlog里slave在复制的时候回
解析成和原来master端执行过的相同sql
row level模式
日志会记录成每一行数据被修改的形式，不记录上下文，只记录被修改的行；日志很大
mixed模式
mysql> show variables like '%format%';
+--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| binlog_format            | MIXED             |
| date_format              | %Y-%m-%d          |
| datetime_format          | %Y-%m-%d %H:%i:%s |
| default_week_format      | 0                 |
| innodb_file_format       | Antelope          |
| innodb_file_format_check | ON                |
| innodb_file_format_max   | Antelope          |
| time_format              | %H:%i:%s          |
+--------------------------+-------------------+
8 rows in set (0.00 sec)
mysql> set session binlog_format='ROW';
Query OK, 0 rows affected (0.03 sec)

mysql> show variables like '%format%';
+--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| binlog_format            | ROW               |
| date_format              | %Y-%m-%d          |
| datetime_format          | %Y-%m-%d %H:%i:%s |
| default_week_format      | 0                 |
| innodb_file_format       | Antelope          |
| innodb_file_format_check | ON                |
| innodb_file_format_max   | Antelope          |
| time_format              | %H:%i:%s          |
+--------------------------+-------------------+
8 rows in set (0.00 sec)
mysqlbinlog --base64-output=decode-rows -v mysql-bin.000016