美文网首页
Mysql日志.

Mysql日志.

作者: ALuckyLive | 来源:发表于2017-09-12 18:10 被阅读0次
    日志:
    查询日志: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;

    相关文章

      网友评论

          本文标题:Mysql日志.

          本文链接:https://www.haomeiwen.com/subject/mrxhsxtx.html