Shulou(Shulou.com)06/01 Report--

MySQL has several different log files that can help you find out what's going on inside mysqld:

log file

Type of information entered in file

Error log (-log-err)

Log problems when starting, running, or stopping mysqld.

Query log (-log)

Record client connections established and statements executed.

Update log (-log-update)

Notes statements that change data. Disapprove of the use of this journal.

Binary log (-log-bin)

Record all statements that change data. It is also used for replication.

slow logs (-log-slow-queries)

Records all queries that take longer than long_query_time seconds to execute or queries that do not use an index.

In the mysql installation directory, open my.cnf, add the above parameters, save and restart the mysql service.

For example:

log-error= d:/mysql/log/log-error.log

#log=......

#log-slow-queries=......

#log-update=......

#log-bin=......

Above only open the error log, to open other logs to remove the front "#" and set the log format and directory.

Let's focus on binary logs.

Binary contains all the information available in the update log in a more efficient format and in a transaction-safe manner.

Contains information about the execution time of each statement that updates the database. It does not contain statements that do not modify any data.

Here are some of the operations associated with binlog.

1. Whether logging is enabled

ON is enabled

mysql> show variables like 'log_bin';

+---------------+-------+

| Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+

2. View binary logs 2.1. View a list of all binary logs mysql> show binary logs; +------------+---------+| Log_name | File_size | +---------------+-----------+ | binlog.000001 | 2036 || binlog.000002 | 143 || binlog.000003 | 143 || binlog.000004 | 143 || binlog.000005 | 143 || binlog.000006 | 120 | +---------------+-----------+

2.2 View binary log status information in use

mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000006 | 120 | | | | +---------------+----------+--------------+------------------+-------------------+

mysql> show variables like 'max_binlog_size';+---------------+---------+| Variable_name | Value | +-----------------+------------+ | max_binlog_size | 1073741824 | +-----------------+------------+

4. Delete binlog logs There are several ways to delete binlog logs:

4.1 Set the expire_logs_days parameter expire_logs_days parameter indicates the number of days binary logs are automatically deleted. The default value is 0, which means "no automatic deletion." mysql> show variables like 'expire_logs_days'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | expire_logs_days | 0 |+--------------------+--------+ The expire_logs_days parameter value can be modified in the following ways. mysql> set global expire_logs_days=5; Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'expire_logs_days'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | expire_logs_days | 5 | +------------------+-------+

4.2 Delete all binlogs Use RESET MASTER; to delete all binlogs. mysql> RESET MASTER;

4.3 PURGE MASTER LOGS PURGE {MASTER}| BINARY} LOGS TO 'log_name' PURGE {MASTER |BINARY} LOGS BEFORE 'date' is used to delete all binary logs listed in the log index before the specified log or date. These logs are also removed from the manifest recorded in the log index file, so that the given log becomes the first. For example: PURGE MASTER LOGS TO 'binlog.00003'; PURGE MASTER LOGS BEFORE '2016-04-02 22:46: 26'; The date argument of the BEFORE variable can be of the format 'Y-MM-DD hh:mm: ss'. MASTER and BINARY are synonyms.

5. View the contents of binary files

To view the contents of a binary file use mysqlbinlog [root@mysql1 log]# mysqlbinlog binlog.000001| more /*! 40019 SET @@session.max_insert_delayed_threads=0*/; /*! 50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #160426 13:44:42 server id 1 end_log_pos 120 Start: binlog v 4, server v 5.6.30-log created 160426 13:44:42 at startup ROLLBACK/*!*/; BINLOG ' SgAfVw8BAAAAdAAAAHgAAAAAAAQANS42LjMwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAABKAB9XEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAZhk qys= '/*!*/; # at 120 。。。。。。。。。。。。。。。。。。。。。。。。。。。----Log inside you can see the changes you make to the database operations, such as the following is my log inside one of the operations. use `test2`/*!*/; SET TIMESTAMP=1461649838/*!*/; insert into t values(1,'2016-04-27')# /*!*/;。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.