Mysql Log System, mysql Log

Mysql Log System, mysql Log
Logs

Mysql Log classification:

• Server logs
• • Records special events during the process startup and running to help you analyze problems encountered by the mysql service.
  • Capture specific SQL statements as needed to track business SQL statements with potential performance problems.
• Transaction log -- Record Content not record status
• • Record all changes to data by the application
  • Available for data recovery
  • It can be used for data synchronization between instances.

Server Log: Transaction Log: Server Error Log:

• Records important messages during instance startup and running
• Configuration parameters
• • Log_error =/data/mysql_data/node-1/mysqld. log
• Not all error messages
• If the mysqld process cannot be started normally, first view the Error Log

Mysql> show global variables like "log_error"; + --------------- + ------------------------ + | Variable_name | Value | + --------------- + logs + | log_error |/var/log/mysql/error. log | + --------------- + ------------------------ + 1 row in set (0.00 sec) # tail-f error. log monitors this file in real time;

Tail-f error. log monitors logs in this file in real time; slow query logs

• Records SQL statements whose execution time exceeds a certain threshold
• Configuration parameters
• • Slow_query_log = 1 ----- enable or disable
  • Slow_query_log_file =/data/mysql_data/node-1/mysql-slow.log -- location
  • Long_query_time = 5 ----- threshold time
• SQL statement used to analyze potential performance problems in the system

# Check whether slow logs are enabled and the Log Path

mysql> show global variables like '%slow%';+---------------------+----------------------------------+| Variable_name       | Value                            |+---------------------+----------------------------------+| log_slow_queries    | OFF                              || slow_launch_time    | 2                                || slow_query_log      | OFF                              || slow_query_log_file | /var/lib/mysql/itcast01-slow.log |+---------------------+----------------------------------+4 rows in set (0.00 sec)

# Enable slow query logs

mysql> set global slow_query_log =1;Query OK, 0 rows affected (0.00 sec) mysql> show global variables like '%slow%';+---------------------+----------------------------------+| Variable_name       | Value                            |+---------------------+----------------------------------+| log_slow_queries    | ON                               || slow_launch_time    | 2                                || slow_query_log      | ON                               || slow_query_log_file | /var/lib/mysql/itcast01-slow.log |+---------------------+----------------------------------+4 rows in set (0.00 sec)

 

# Threshold for viewing slow query logs

mysql> show global variables like 'long_query_time';+-----------------+-----------+| Variable_name   | Value     |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+

 

Set the threshold, usually 5 seconds;

mysql> set global  long_query_time = 1;Query OK, 0 rows affected (0.00 sec) mysql> show global variables like 'long_query_time';+-----------------+----------+| Variable_name   | Value    |+-----------------+----------+| long_query_time | 1.000000 |+-----------------+----------+1 row in set (0.00 sec)

 

Comprehensive log query

• If enabled, all SQL statements in the system are recorded.
• Configuration parameters
• • General_log = 1
  • General_log_file =/data/mysql_data/node-1/mysql-gen.log
• Occasionally used to help analyze system problems and affect performance

 

mysql> show global variables like '%general%';+------------------+-----------------------------+| Variable_name    | Value                       |+------------------+-----------------------------+| general_log      | OFF                         || general_log_file | /var/lib/mysql/itcast01.log |+------------------+-----------------------------+2 rows in set (0.00 sec)

Query log output and file Switching

• Log output parameters
• • Log_output = {file | table | none}
• If the log file is too large, you can regularly truncate and switch the new file.
• • Flush logs;

mysql> show global variables like '%log_output%';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_output    | FILE  |+---------------+-------+1 row in set (0.00 sec)

 

Storage engine transaction log-only records data changes;

• Some storage engines have redo logs)
• For example, innodb, TokuDB, and other WAL (Write Ahead Log) storage engines
• Logs are persisted with the transaction commit to ensure that no data is lost during exception recovery.
• Log sequential write performance is good

Innodb Transaction Log reuse mechanism

• Innodb Transaction logs are repeatedly reused using two sets of Files

Scenario 1:

 

　　

 

Scenario 2:

 

Therefore, we recommend that you set Increase the size of ib_logfile; This is conducive to concurrency; binary log binlog

• Binlog (binary log)
• Data RecordSQL statement for data changesOrData logic changes
• Mysql service layer record, irrelevant storage engine
• The main functions of binlog:
• • Recover data based on backup
  • Master-slave Database Synchronization
  • Mining and analysis SQL statements

Enable binlog

• Parameters
• • Log_bin = c:/tmp/mylog/mysql-bin (if the static parameter is set to non-0, binlog is enabled; if it is set to 1, the directory is in the mysql data directory)
  • SQL _log_bin = 1 (mysql selects whether the session records binlog)
  • Sync_binlog = 1 (mysql, binlog persistence mode; 0, not automatically refreshed to the disk, but fl when dirty data is refreshed; 1, each refresh to the disk; 100, it is automatically refreshed to the disk for every 100 entries)

 

mysql> show global variables like 'log_bin';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_bin       | OFF   |+---------------+-------+1 row in set (0.00 sec)

 

Binlog Management

• Main Parameters
• • Max_binlog_size = 100 MB
  • Expire_logs_days = 7 --------- how many days of binlog files are automatically saved
• Binlog always generates new files and will not be reused
• Manual cleaning of binlog
• • Purge binary logs to 'mysql-bin.000009'
  • Purge binary logs before '2017-06-01 22:45:34'

View binlog content

• Log)
• • Show binlog events in 'mysql-bin.000011'
  • Show binlog events in 'mysql-bin.000011 'from 60 (position) limit 3;
• Mysqlbinlog Tool
• • Mysqlbinlog c:/tmp/mylog/mysql-bin.000001 -- start-datetime | -- stop -- datetime -- start-position | -- stop-position

Binlog format

• Main Parameters
• • Binlog_format = {row | satement | mixed}
• View the binlog content in row Mode
• • Mysqlbinlog -- base64-output = decode-rows-v c:/tmp/mylog/mysql-bin.000001

 

show binary logs;show binlog events in 'mysql-bin.000001'

 

Binlog only records statements that change data, but has limitations: If uuid () is used, different values are generated each time. To insert data, binlog, insert into tab values (uuid () is recorded. At this time, through playback, binlog cannot obtain the original data, because each uuid () produces different values. Therefore, mysql allows you to use another format to record binlog and only change data values. You can set binlog_format to row, binlog_format to satement, and record specific operation statements. binlog_format is set MixedMysql automatically determines whether to use row or satement; Summary  

• Service Log:
• • Log_error
  • • Records important messages during instance startup and running,
    • The content is not all error messages,
    • Mysqld cannot be started. First, view the error log;
  • Slow query logs;
  • Comprehensive log query
  • • All SQL statements in the system are recorded,
    • Occasionally used to help analyze system problems and affect performance;
• Log output:
• • Log_output = file, table, none;
  • If the log file is too large, you can regularly truncate and switch the new file to flush logs;
• Transaction log:
• • Redo log
  • • Only record data changes
    • Innodb Transaction Log reuse mechanism, ib_logfile conference is conducive to writing multiple businesses;
  • Bin log
  • • Records the SQL statements or data changes that cause data changes,
    • At the mysql service layer,
    • Mainly used:
    • • Recover data based on backup,
      • Master-slave Database Synchronization,
      • Mining and analysis SQL;
    • Main parameters:
    • • Row only records data changes,
      • SQL statement that records data changes,
      • Mix max values;