6. mysql Log

1.error Log  

Error log, log the problem that occurs when the MySQL service starts, text file

[Mysqld_safe] #注意是写在这里

Log-error=/var/log/mysqld/mysql-error.log --mysqld folder to be present and chown mysql

The log cannot be turned off

2.access Log

query (Access) log, record all statements executed by the client , the text file

For frequently accessed systems, the performance impact is large, it is recommended to close

Showvariables like '%general_log% ';

[Mysqld]

General_log=on

General_log_file=/var/log/mysqld/mysql-access.log     ---- make some actions after connecting, and then check the close #注释这2行

3.bin Log       

Binary log, records all DDL, DML statements, does not remember Select, is also used for copying, non-text files

[Mysqld]

log_bin=/var/log/mysqld/mysql-bin--the log file prefix

Service Restarts 1 times, the log file is 1 new (such as mysql-bin.000002)

flushlogs; A new log file will also be added

Log file size exceeds max_binlog_size (in bytes, default 1GB), and a new log file is added

To display the log file:

Showmaster logs;

Showmaster status;

Show variables like "%log_bin%"

To view binary logs:

mysqlbinlogmysql-bin.000001

Mysqlbinlog mysql-bin.000001-d DS --Only the DS database is displayed

--start-datetime= "2012-10-10 9:10:0"--stop-datetime= "2012-10-10 9:30:59" --show only logs between this time period

--start-position= "123"--stop-position= "456"--the number after the at, the busy online system, the same time multi-person operation, that is, at the same time there are multiple DML, then you can use position to distinguish

if only the start of writing, then to the tail of the log, If write-only finishes, start at the beginning of the log

--------------------------------------------------------------------------------------------------------------- ----------------

periodic deletion of logs files are an important part of the work of maintaining MySQL

Method 1:

Reset Master;

Delete all log files, generate 1 new log files, and start numbering again from 000001

Method 2:

Purge master logs to ' mysql-bin.000006 ';

All log files before number 000006 are deleted

Method 3:

[Mysqld]

expire_logs_days =3 (days)--Restart the service, the file will be automatically deleted from the generation to 3 days after the default is 0 , do not delete

Some other options

[Mysqld]

Binlog_do_db=db1

BINLOG_DO_DB=DB2 --databases that are not explicitly specified will not be logged

Binlog_ignore_db=db1

BINLOG_IGNORE_DB=DB2 --databases that are not explicitly specified will be logged

set sql_log_bin = 0; --Disable logging of your own operations into the binary log

4.slow Log    

slow log, text file, record all statements with execution time exceeding long_query_time sec Adding and deleting changes will be remembered

Slow logs are helpful for us to find statements that have performance issues, and it is recommended to open and view the analysis frequently

[Mysqld]

long_query_time=2 (SEC)

--5.5

Log_slow_queries=/var/log/mysqld/mysql-slow.log

--5.6

Slow_query_log=on

Slow_query_log_file=/var/log/mysqld/mysql-slow.log

From for notes (Wiz)

6. mysql Log