Mysql日志.
2017-09-12 本文已影响0人
ALuckyLive
日志：
查询日志：general_log
慢查询日志：log_slow_queries
错误日志：log_error， log_warnings
二进制日志：binlog
中继日志：relay_log
事务日志：innodb_log
查看查询日状态，是否开启及日志输出到的文件路径位置；相对路径 note2.log
show global variables like 'general%' ;
+------------------+-----------+
| Variable_name | Value |
+------------------+-----------+
| general_log | OFF |
| general_log_file | note2.log |
+------------------+-----------+
日志输出到的位置FILE类型；
MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
1 row in set (0.00 sec)
开启查询日志功能；
MariaDB [mydb]> SET GLOBAL general_log=ON;
Query OK, 0 rows affected (0.00 sec)
查看日志文件类型；FILE
MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE 'log_output'
-> ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
1 row in set (0.00 sec)
查看查询日志是否开，与日志保存路径；
MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE 'general%'
-> ;
+------------------+-----------+
| Variable_name | Value |
+------------------+-----------+
| general_log | ON |
| general_log_file | note2.log |
+------------------+-----------+
2 rows in set (0.00 sec)
开启查询语句后；当查询会生成日志；
MariaDB [mydb]> SELECT * FROM mydb.students WHERE stuid=3;
+-------+-------+------+--------+----------------+
| stuid | name | age | gender | major |
+-------+-------+------+--------+----------------+
| 3 | XImen | 31 | M | kuihua baodian |
+-------+-------+------+--------+----------------+
1 row in set (0.01 sec)
查看生成的日志；
[root@note2 mysql]# tail note2.log
/usr/libexec/mysqld, Version: 5.5.52-MariaDB (MariaDB Server). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
170912 14:41:28 2 Query SHOW GLOBAL VARIABLES LIKE 'log_output'
170912 14:42:23 2 Query SHOW GLOBAL VARIABLES LIKE 'general%'
170912 14:53:42 2 Query SELECT * FROM mydb.students WHERE stuid=3
更改表的存放形式；table
MariaDB [mydb]> SET @@global.log_output='TABLE';
Query OK, 0 rows affected (0.00 sec)
查看查询日志的输出到的位置；
MariaDB [mydb]> SHOW GLOBAL VARIABLES LIKE 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | TABLE |
+---------------+-------+
1 row in set (0.00 sec)
查询生成的日志；
MariaDB [mydb]> SELECT * FROM mysql.general_log;
+----------------------------+---------------------------+-----------+-----------+--------------+-----------------------------------------+
| event_time | user_host | thread_id | server_id | command_type | argument |
+----------------------------+---------------------------+-----------+-----------+--------------+-----------------------------------------+
| 2017-09-12 15:14:57.591377 | root[root] @ localhost [] | 2 | 0 | Query | SELECT * FROM mysql.general_log |
| 2017-09-12 15:16:21.876954 | root[root] @ localhost [] | 2 | 0 | Query | SHOW GLOBAL VARIABLES LIKE 'log_output' |
| 2017-09-12 15:18:39.473597 | root[root] @ localhost [] | 2 | 0 | Query | SELECT * FROM mysql.general_log |
+----------------------------+---------------------------+-----------+-----------+--------------+-----------------------------------------+
3 rows in set (0.00 sec)
关闭查询日志功能；
MariaDB [mydb]> SET @@global.general_log=OFF
-> ;
Query OK, 0 rows affected (0.00 sec)
显示所有的数据库
MariaDB [mydb]> SHOW DATABASES
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
使用数据库
MariaDB [mydb]> USE mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
列出数据库列表
MariaDB [mysql]> SHOW TABLES
-> ;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set (0.00 sec)
查看慢查询日志的状态；
MariaDB [mysql]> SHOW GLOBAL VARIABLES LIKE '%slow%';
+---------------------+--------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------------------------------------------------------------------------------------+
| log_slow_filter | admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk |
| log_slow_queries | OFF |
| log_slow_rate_limit | 1 |
| log_slow_verbosity | |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | note2-slow.log |
+---------------------+--------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)
开启慢查询日志；
MariaDB [mysql]> SET GLOBAL slow_query_log=ON
-> ;
Query OK, 0 rows affected (0.00 sec)
开启后使用上条命令查看状态；
SHOW GLOBAL VARIABLES LIKE '%slow%';
想要将文件保存在文件中需修改文件类型；
MariaDB [mysql]> SET @@GLOBAL.log_output='FILE';
Query OK, 0 rows affected (0.00 sec)
关闭慢查询，查看状态；
MariaDB [mysql]> SET @@global.log_slow_queries=OFF;
Query OK, 0 rows affected, 1 warning (0.00 sec)
MariaDB [mysql]> SHOW GLOBAL VARIABLES LIKE '%slow%';
+---------------------+--------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------------------------------------------------------------------------------------+
| log_slow_filter | admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk |
| log_slow_queries | OFF |
| log_slow_rate_limit | 1 |
| log_slow_verbosity | |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | note2-slow.log |
+---------------------+--------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)
查询错误日志状态；
当在log_error对应位置有文件路径即表示一起用，没有文件历经为off时为关闭状态；
MariaDB [mysql]> SHOW GLOBAL VARIABLES LIKE '%error%';
+--------------------+------------------------------+
| Variable_name | Value |
+--------------------+------------------------------+
| log_error | /var/log/mariadb/mariadb.log |
| max_connect_errors | 10 |
| max_error_count | 64 |
| slave_skip_errors | OFF |
+--------------------+------------------------------+
4 rows in set (0.00 sec)
查看错误日志；
# tail /var/log/mariadb/mariadb.log
二进制文件；
基于时间戳而改变；
二进制日志文件；分为两种；
STATEMENT;语句---记录结果
ROW;行---详细
一般记录结果，节约空间；
MIXED;以上两种为混编方式；
查看使用那种类型记录日志；
MariaDB [mysql]> SHOW GLOBAL VARIABLES LIKE 'binlog_format';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
查看是否启用日志；
MariaDB [mysql]> SHOW MASTER LOGS;
ERROR 1381 (HY000): You are not using binary logging
没有启动二进制日志文件，需要编写配置文件；
vim /etc/my.cnf.d/server.cnf
mysql位置添加；
log_bin=master_log
并重启服务；
systemctl restart mariadb.service
重新连接，并查看二进制日志是否启用；
[root@note2 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%log%bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+
3 rows in set (0.00 sec)
启用后二进制文件，使用的那个文件；
MariaDB [(none)]> SHOW MASTER LOGS
-> ;
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| master_log.000001 | 245 |
+-------------------+-----------+
1 row in set (0.00 sec)
查看当前使用的二年进制文件；
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master_log.000001 | 245 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
需要滚动一次；查询二进制日志文件及当前使用的文件；
MariaDB [(none)]> flush logs;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master_log.000002 | 245 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> SHOW MASTER LOGS;
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| master_log.000001 | 289 |
| master_log.000002 | 245 |
+-------------------+-----------+
2 rows in set (0.00 sec)
当查看students表时，二进制日志文件不会改变；
删除表中的一行是发生改变；
#use mydb;
#SHOW * FROM students;
MariaDB [mydb]> SHOW MASTER SHATUS;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SHATUS' at line 1
MariaDB [mydb]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master_log.000002 | 245 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
当删除一行后发生改变；
MariaDB [mydb]> DELETE FROM students WHERE stuid=1986;
Query OK, 1 row affected (0.00 sec)
MariaDB [mydb]> SHOW MASTER STATUS
-> ;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master_log.000002 | 440 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
查看发生二进制的事件；
MariaDB [mydb]> SHOW BINLOG EVENTS;
+-------------------+-----+-------------+-----------+-------------+-------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------------+-----+-------------+-----------+-------------+-------------------------------------------+
| master_log.000001 | 4 | Format_desc | 1 | 245 | Server ver: 5.5.52-MariaDB, Binlog ver: 4 |
| master_log.000001 | 245 | Rotate | 1 | 289 | master_log.000002;pos=4 |
+-------------------+-----+-------------+-----------+-------------+-------------------------------------------+
2 rows in set (0.00 sec)
查看事件发生的内容；
MariaDB [mydb]> SHOW BINLOG EVENTS in 'master_log.000002';
+-------------------+-----+-------------+-----------+-------------+---------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------------+-----+-------------+-----------+-------------+---------------------------------------------------+
| master_log.000002 | 4 | Format_desc | 1 | 245 | Server ver: 5.5.52-MariaDB, Binlog ver: 4 |
| master_log.000002 | 245 | Query | 1 | 313 | BEGIN |
| master_log.000002 | 313 | Query | 1 | 413 | use `mydb`; DELETE FROM students WHERE stuid=1986 |
| master_log.000002 | 413 | Xid | 1 | 440 | COMMIT /* xid=24 */ |
+-------------------+-----+-------------+-----------+-------------+---------------------------------------------------+
4 rows in set (0.00 sec)
当测是目的操作不想被记录在我二进制日志文件当中时关掉session会话，不在记录到二进制日志；
MariaDB [mydb]> set @@session.sql_log_bin=OFF;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mydb]> DELETE FROM students WHERE stuid=2000;
Query OK, 1 row affected (0.00 sec)
MariaDB [mydb]> SHOW BINLOG EVENTS in 'master_log.000002';
+-------------------+-----+-------------+-----------+-------------+---------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------------+-----+-------------+-----------+-------------+---------------------------------------------------+
| master_log.000002 | 4 | Format_desc | 1 | 245 | Server ver: 5.5.52-MariaDB, Binlog ver: 4 |
| master_log.000002 | 245 | Query | 1 | 313 | BEGIN |
| master_log.000002 | 313 | Query | 1 | 413 | use `mydb`; DELETE FROM students WHERE stuid=1986 |
| master_log.000002 | 413 | Xid | 1 | 440 | COMMIT /* xid=24 */ |
+-------------------+-----+-------------+-----------+-------------+---------------------------------------------------+
4 rows in set (0.00 sec)
想要把上次删除的操作记录在二进制文件当中，可以开启会话，并在此仅删除操作；
MariaDB [mydb]> SET @@session.sql_log_bin=ON;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mydb]> DELETE FROM students WHERE stuid=2000:
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':' at line 1
MariaDB [mydb]> DELETE FROM students WHERE stuid=2000;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mydb]> SHOW BINLOG EVENTS in 'master_log.000002';
+-------------------+-----+-------------+-----------+-------------+---------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------------+-----+-------------+-----------+-------------+---------------------------------------------------+
| master_log.000002 | 4 | Format_desc | 1 | 245 | Server ver: 5.5.52-MariaDB, Binlog ver: 4 |
| master_log.000002 | 245 | Query | 1 | 313 | BEGIN |
| master_log.000002 | 313 | Query | 1 | 413 | use `mydb`; DELETE FROM students WHERE stuid=1986 |
| master_log.000002 | 413 | Xid | 1 | 440 | COMMIT /* xid=24 */ |
| master_log.000002 | 440 | Query | 1 | 508 | BEGIN |
| master_log.000002 | 508 | Query | 1 | 608 | use `mydb`; DELETE FROM students WHERE stuid=2000 |
| master_log.000002 | 608 | Query | 1 | 677 | COMMIT |
+-------------------+-----+-------------+-----------+-------------+---------------------------------------------------+
7 rows in set (0.00 sec)
查看二进制日志某个位置的文件；
MariaDB [mydb]> SHOW BINLOG EVENTS in 'master_log.000002' FROM 440;
+-------------------+-----+------------+-----------+-------------+---------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------------+-----+------------+-----------+-------------+---------------------------------------------------+
| master_log.000002 | 440 | Query | 1 | 508 | BEGIN |
| master_log.000002 | 508 | Query | 1 | 608 | use `mydb`; DELETE FROM students WHERE stuid=2000 |
| master_log.000002 | 608 | Query | 1 | 677 | COMMIT |
+-------------------+-----+------------+-----------+-------------+---------------------------------------------------+
3 rows in set (0.00 sec)
给定440位置后，只显示440后面的内容；
显示当前表中的第一行内容，进行限制显示；
MariaDB [mydb]> SHOW BINLOG EVENTS in 'master_log.000002' FROM 440 LIMIT 1;
+-------------------+-----+------------+-----------+-------------+-------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------------+-----+------------+-----------+-------------+-------+
| master_log.000002 | 440 | Query | 1 | 508 | BEGIN |
+-------------------+-----+------------+-----------+-------------+-------+
1 row in set (0.00 sec)
日志文件所在的位置；
[root@note2 mysql]# ls
aria_log.00000001 master_log.000001 mysql.sock
aria_log_control master_log.000002 note2.log
ibdata1 master_log.index note2-slow.log
ib_logfile0 mydb performance_schema
ib_logfile1 mysql test
命令行查看二进制文件内容，开头为头文件；
[root@note2 mysql]# mysqlbinlog master_log.000002
/*!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
#170912 16:55:14 server id 1 end_log_pos 245 Start: binlog v 4, server v 5.5.52-MariaDB created 170912 16:55:14
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
8qC3WQ8BAAAA8QAAAPUAAAABAAQANS41LjUyLU1hcmlhREIAbG9nAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAMoi+UA==
'/*!*/;
# at 245
#170912 17:07:26 server id 1 end_log_pos 313 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1505207246/*!*/;
mysqlbinlog 为客户端命令；
设置二进制日志文件大小；
max_binlog_size=1073731824;
