1. Error log

• Record errors encountered by mysql service process mysqld during start/close or run

[root@mysql ~]# vi /data/3306/my.conf
[mysqld_safe]
log-error=/data/3306/mysql_qb3306.err
mysql> show variables like "%log_error%";
+---------------+-----------------------------+
| Variable_name | Value                       |
+---------------+-----------------------------+
| log_error     | /data/3306/mysql_qb3306.err |
+---------------+-----------------------------+
1 row in set (0.00 sec)

2. General Query Log:

• Record client connection information and sql statement information executed

mysql> set global general_log = ON ;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "%general_log%";
+------------------+---------------------------+
| Variable_name    | Value                     |
+------------------+---------------------------+
| general_log      | ON                        |
| general_log_file | /data/3306/data/mysql.log |
+------------------+---------------------------+

3. Slow Query Log

• Record execution time exceeding specified value (long_query_time) to get sql statement

[root@mysql ~]# vi /data/3306/my.conf 
[mysqld]
long_query_time = 1 #Record in more than a second
log-slow-queries = /data/3306/slow.log #Location of log files recorded
log_queries_not_using_indexes #No indexed records are recorded in the log
mysql> show variables like "%long_q%";
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

mysql> show variables like "%log_slow%"; 
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| log_slow_queries | ON    |
+------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like "%log_que%";
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+
1 row in set (0.00 sec)

4. Binary Logging

• Relevant information about recording data being modified

[root@mysql ~]# vi /data/3306/my.conf
[mysqld]
log-bin = /data/3306/mysql-bin
mysql> show variables like "%log_bin%"; 
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | ON    |#Whether to open bin-log
| sql_log_bin                     | ON    |#Whether to record temporary statements, such as when using bi-log to restore data
+---------------------------------+-------+ Open this parameter if you don't want to be recorded.
3 rows in set (0.00 sec)

Three modes of 4.1 bin-log

• a) Row Level row pattern

  • The log records the form in which each row of data is modified, and then modifies the same data on the slave side.
  • Advantages: In row level mode, the context-related information of the executed sql statement can not be recorded in bin-log, but only the one that has been modified. So rowlevel's log content clearly records the details of each row's data modification. There will be no problems with stored procedures or function s under certain circumstances, as well as the inability of trigger calls and triggers to be replicated correctly.
  • Disadvantage: row level, when all executed statements are recorded in the log, they will be recorded with the modification of each row record, which will produce a lot of log content.

• b) Statement Level (default)

  • Each sql that modifies the data is recorded in the master's bin-log. When slave replicates, the sql process resolves to the same sql as the original master to execute again
  • Advantages: The first advantage of statement level is that it solves the disadvantage of row level. It does not need to record the changes of each row, reduce the amount of bin-log log, save IO and improve performance, because it only needs to lock the details of the execution statement on Master and the context information of the execution statement.
  • Disadvantage: Since only statements are recorded, many situations have been found under statement level that may cause problems in replication of MySQL, mainly when certain functions or functions are used to modify data.

• c) Mixed automatic mode

  • In Mixed mode, MySQL differentiates log formats for records based on each specific sql statement executed, that is, between Statement and Row. If an sql statement is indeed a statement that modifies data, such as update or delete, then changes in all rows are recorded.

• d) The difference between line mode and sentence mode

  • Statement pattern:
    1 million records
    Just one delete * from test; one million records can be deleted
  • row mode
    1 million records
    Record 1 million deletion commands

4.2 Configuration Method

default

mysql> show variables like "%binlog_for%";
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

Configuration File Modification Method

[root@mysql ~]# vi /data/3306/my.conf 
[mysqld]
log-bin = /data/3306/mysql-bin
#binlog_format = "STATEMENT"
#binlog_format = "ROW"
binlog_format = "MIXED"

On-line Modification Effective Method

mysql> set session binlog_format = "ROW";###Current User Effective
Query OK, 0 rows affected (0.00 sec)

mysql> set global binlog_format = "ROW"; ###All users are valid and need to log in again after modification    
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "%binlog_for%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)