Linux O & M learning notes-MySQL Log learning and learning notes-mysql

Linux O & M learning notes-MySQL Log learning and learning notes-mysql

I. Error Log: Error Log

1. Introduction

An error log is an error message that records the MySQL service process mysqld during startup, shutdown, or running. The error log function is enabled by default. In addition, error logs cannot be disabled. By default, error logs are stored in mysql database data files. The error log file is usually named hostname. err. Hostname indicates the Host Name of the server.

The information recorded in the error log can be defined through log-error and log-warnings. log-error indicates whether to enable the error log function and the storage location of the error log, log-warnings defines whether to include warning information in the error log.

2. log-error parameter configuration

Vi/data/3306/my. cnf

[Mysqld_safe]

Log-error =/data/3306/mysql_oldboy3306.err

Ii. Query Log: Query Log

1. Introduction

(1) General Query Log)

Records client connection information and executed SQL statement information. By default, log query is disabled. Because the query log contains all user operations, including addition, deletion, query, modification, and other information, a large amount of information will be generated in an environment with a large number of concurrent operations, resulting in unnecessary disk IO, will affect the performance of mysql. We recommend that you do not enable log query for the purpose of debugging the database.

(2) Slow Query Log (Slow Query Log)

Slow query logs are used to record query statements whose execution time exceeds the specified time (Long_Query_Time. Through slow query logs, you can find out which query statements have low execution efficiency for optimization. It is generally recommended to enable it, which has little impact on the server performance, but can record the query statements that have been executed on the mysql server for a long time. This helps us locate performance problems.

(3) view log-related parameters

Show variables like '% _ log % ';

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

| Variable_name | Value |

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

| Back_log | 600 |

| Bin log_cache_size | 1048576 |

| Binlog_direct_non_transactional_updates | OFF |

| Binlog_format | STATEMENT |

| Binlog_stmt_cache_size | 1, 32768 |

| Expire_logs_days | 7 |

| General_log | OFF |

| General_log_file |/data/3306/data/3306.log |

| Innodb_flush_log_at_trx_commit | 2 |

| Innodb_locks_unsafe_for_binlog | OFF |

| Innodb_log_buffer_size | 2097152 |

| Innodb_log_file_size | 4194304 |

| Innodb_log_files_in_group | 3 |

| Innodb_log_group_home_dir |./|

| Innodb_mirrored_log_groups | 1 |

| Max_binlog_cache_size | 1048576 |

| Max_binlog_size | 2097152 |

| Max_binlog_stmt_cache_size | 1, 18446744073709547520 |

| Max_relay_log_size | 0 |

| Relay_log |/data/3306/relay-bin |

| Relay_log_index |

| Relay_log_info_file |/data/3306/relay-log.info |

| Relay_log_purge | ON |

| Relay_log_recovery | OFF |

| Relay_log_space_limit | 0 |

| Slow_query_log | OFF |

| Slow_query_log_file |/data/3306/data/3306-slow.log |

| SQL _log_bin | ON |

| SQL _log_off | OFF |

| Sync_binlog | 0 |

| Sync_relay_log | 0 |

| Sync_relay_log_info | 0 |

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

2. Set common query logs

(1) enable common query logs

Set global general_log = ON;

(2) check whether the parameters take effect

Show variables like '% general_log % ';

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

| Variable_name | Value |

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

| General_log | ON |

| General_log_file |/data/3306/data/3306.log |

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

(3) view log Content

A. Execute SQL statements

Select * frommysql. user;

Create databaseddd;

B. view log Content

Cat/data/3306/data/3306.log

/Application/mysql-5.5.32/bin/mysqld, Version: 5.5.32-log (Source distribution). started:

Tcp port: 3306 Unix socket:/data/3306/mysql. sock

Time Id Command Argument

17042513: 38: 20 9 Query show variables like '% general_log %'

17042513: 41: 55 9 Query select * from mysql. user

17042513: 45: 16 9 Query create database ddd

17042513: 45: 17 9 Quit

17042513: 45: 21 10 Connect root @ localhost on

10 Query select @ version_comment limit 1

(4) Disable common query logs

Set global general_log = OFF;

3. Set slow query logs

(1) Add slow query parameters in the configuration file my. cnf

Vi/data/3306/my. cnf

[Mysqld_safe]

Long_query_time = 1

Log-slow-queries =/data/3306/slow. log

Log_queries_not_using_indexes

(2) view parameters

Show variableslike 'lo % ';

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

| Variable_name | Value |

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

| Local_infile | ON |

| Lock_wait_timeout | 31536000 |

| Locked_in_memory | OFF |

| Log | OFF |

| Log_bin | ON |

| Log_bin_trust_function_creators | OFF |

| Log_error |/data/3306/mysql_oldboy3306.err |

| Log_output | FILE |

| Log_queries_not_using_indexes | OFF |

| Log_slave_updates | OFF |

| Log_slow_queries | OFF |

| Log_warnings | 1 |

| Long_query_time | 1.000000 |

| Low_priority_updates | OFF |

| Lower_case_file_system | OFF |

| Lower_case_table_names | 1 |

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

Iii. Binary Log: Binary Log

1. Introduction

Records the modified information of the data. The location and file name are specified by the log-bin parameter.

2. View binlog Parameters

Show variables like '% log_bin ';

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

| Variable_name | Value |

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

| Log_bin | ON | # record binlog

| SQL _log_bin | ON | # Temporarily logs of SQL statements are not recorded in binlog

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

3. Three Levels of binlog

(1) Statement Level (SQL Statement Level, default)

A. Role

Each SQL statement that modifies the data is recorded in the binlog.

B. Advantages

You do not need to record the changes of each row, which reduces the binlog volume, saves IO, and improves performance. (The performance and log volume can be reduced compared to the row, which depends on the application's SQL condition. The log volume generated by modifying the same record normally or inserting the row format is smaller than the log volume generated by Statement, however, considering the conditional update operations, as well as the entire table deletion and alter TABLE operations, the ROW format will generate a large number of logs, therefore, when considering whether to use the ROW format log, it should be based on the actual situation of the application, the amount of logs generated will increase, and the IO performance problems .)

C. Disadvantages

Because only execution statements are recorded, in order that these statements can run correctly on slave, some related information of each statement must be recorded during execution, to ensure that all the statements can get the same results as the execution on the master end in slave. In addition, mysql replication, such as some specific function functions, slave can be consistent with the master, there will be many related problems (such as sleep () function, last_insert_id (), and user-defined functions (udf) problems ).

The statements using the following functions cannot be copied:

* LOAD_FILE ()

* UUID ()

* USER ()

* FOUND_ROWS ()

* SYSDATE () (unless the -- sysdate-is-now option is enabled at startup)

At the same time, INSERT... SELECT will generate more row-level locks than RBR.

(2) Row Level (Row-Level mode)

A. Role

The context information of the SQL statement is not recorded. Only the record that is saved is modified.

B. Advantages

Binlog does not record the context-related information of the executed SQL statement. You only need to record what the record is modified. Therefore, rowlevel logs Clearly record the details of each row of data modification. In addition, there will be no problems in certain situations where stored procedures, functions, trigger calls and triggers cannot be correctly copied.

C. Disadvantages

When all the executed statements are recorded in the log, they are recorded with modifications recorded in each line. This may produce a large amount of log content, such as an update statement, if you modify multiple records, each modification to the binlog will be recorded, resulting in a large amount of binlog logs. Especially when you execute statements such as alter table, because of the table structure modification, if each record changes, each record in the table is recorded in the log.

(3) Mixed Level (Mixed mode, officially recommended)

The preceding two levels are used in combination. For general statement modification, binlog is saved in statment format. For example, if statement cannot complete master-slave replication, binlog is saved in row format, mySQL will differentiate the log format of the Record Based on each specific SQL Statement executed, that is, select one between Statement and Row. the row level mode of the new MySQL squadron is also optimized. Not all modifications are recorded by row level, as if the table structure is changed, it will be recorded in statement mode. For statements that modify data such as update or delete, changes to all rows are recorded.

4. Adjust the binlog log mode.

(1) Setting Method

A. Method 1: modify the configuration file my. cnf

Vi/data/3306/my. cnf

Binlog_format = "STATEMENT"

# Binlog_format = "ROW"

# Binlog_format = "MIXED"

B. Method 2: online modification

(I) take effect at runtime

Set session binlog_format = 'Statement ';

Set session binlog_format = 'row ';

Set session binlog_format = 'mixed ';

(Ii) Global effectiveness

Set global binlog_format = 'Statement ';

Set global binlog_format = 'row ';

Set global binlog_format = 'mixed ';

(2) view the current binlog log mode (STATEMENT by default)

Show variables like '% binlog_format % ';

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

| Variable_name | Value |

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

| Binlog_format | STATEMENT |

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

(3) Online setting to Row Level Mode

A. Set

Set global binlog_format = 'row ';

B. Check the modified results and find no changes

Show variables like '% binlog_format % ';

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

| Variable_name | Value |

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

| Binlog_format | STATEMENT |

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

C. Exit and re-enter

Quit;

Mysql-uroot-p '000000'-S/data/123456/mysql. sock

D. query again

Show variables like '% binlog_format % ';

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

| Variable_name | Value |

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

| Binlog_format | ROW |

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

(4) view the Row Level Effect

A. Refresh the log and switch to binlog.

Flush logs;

B. Update table data

Update student set name = 'cc' where id = 3;

C. View binlog content

Mysqlbinlog -- base64-output = decode-rows-v mysql-bin.000005

# At 224

#170425 19:46:00... Table_map: 'test'. 'student 'mapped to number 35

#170425 19:46:00... Update_rows: table id 35 flags: STMT_END_F

### UPDATE 'test'. 'student'

### WHERE

###@ 1 = 3

###@ 2 = 'C'

### SET

###@ 1 = 3

###@ 2 = 'cc'

# At 269

#170425 19: 46: 00 server id 1 end_log_pos 296 Xid = 68

COMMIT /*! */;

D. view the ROW Level content in binlog, which must be correctly displayed using -- base64-output = decode-rows-v