Mysql DBA Advanced Operations Learning Note-mysql log files in the database

1. Error log

(1) Description of error log

Error log: Log the error message encountered by the MySQL service process mysqld during Auto/close or travel

(2) Adjustment of error log

Check the error log inside the MY.CNF configuration file Log-error

[[email protected] ~]# sed -n ‘78,80p‘ /data/3306/my.cnf [mysqld_safe]log-error=/data/3306/mysql_zbf3306.errpid-file=/data/3306/mysqld.pid

2. Query log

(1) Introduction of inquiry log

Query log: Can be divided into two categories

General Query log: Logs client connection information and SQL statement information executed.

Slow query log (slow query log): Record execution time exceeds the specified value. (long_query_time) of SQL statements.

(2) General query log adjustment ( general query log )

Normal query log is not important, in the work is not open

A. Let's log in to MySQL and see if General log is open.

mysql> show variables like ‘%general_log%‘;+------------------+---------------------------+| Variable_name| Value |+------------------+---------------------------+| general_log  | OFF|| general_log_file | /data/3306/data/mysql.log |+------------------+---------------------------+2 rows in set (0.00 sec)

Through the inquiry we know general_log that no open, below we will general_log open

B. Open General_log Online

mysql> set global  general_log=ON;Query OK, 0 rows affected (0.00 sec)

C. See if General_log is turned on

mysql> show variables like ‘%general_log%‘;+------------------+---------------------------+| Variable_name| Value |+------------------+---------------------------+| general_log  | ON|| general_log_file | /data/3306/data/mysql.log |+------------------+---------------------------+2 rows in set (0.00 sec)[[email protected] ~]# ll /data/3306/data/mysql.* -rw-rw----. 1 mysql mysql 363 2月  13 01:02 /data/3306/data/mysql.log

D. Test General_log effect

General Query log: Log client connection information and execute SQL statement information: let's verify
Create a library

mysql> create database nn;Query OK, 1 row affected (0.01 sec)

Next we look at the Mysql.log normal query log

[[email protected] ~]# cat /data/3306/data/mysql.log /usr/local/mysql/bin/mysqld, Version: 5.5.32-log (Source distribution). started with:Tcp port: 3306  Unix socket: /data/3306/mysql.sockTime Id CommandArgument180213  1:11:05    25 Query create database nn

We're looking at the statement that I just created the library.

(3) Slow query adjustment (important, optimize SQL statements to do incremental backup master-slave synchronization is required)

[[email protected] ~]# sed -n "33,35p" /data/3306/my.cnf long_query_time = 1#log-slow-queries = /data/3306/slow.log#log_queries_not_using_indexes

long_query_timeParameter how long to record slow query, log-slow-queries the location of the slow query, the log_queries_not_using_indexes parameter is not using the index statement log inside the log.

3. Binary logs (binary log)

(1) Introduction to binary logs

Binary logs (binary log): Records information about the data being modified.

(2) Binary log adjustment

[[email protected] ~]# egrep "\[mysqld]|log-bin|log-sla" /data/3306/my.cnf [mysqld]log-bin = /data/3306/mysql-binlog-slave-updates

Log_bin is a record binlog

Sql_log_bin temporarily does not record Binlog

4.binlog logs in three modes 4.1 Statement level mode

Each SQL that modifies the data is recorded in the Binlog of Master, and the SQL process parses the same SQL that was executed by the original master side slave the copy sing Woo.
Advantages: The advantages of stament level first is to solve the disadvantage of the row level, do not need to record each row of data changes, reduce bin-log log volume, save IO, improve performance. Because he only needs to record the details of the statements executed on master, and the context in which the statements are executed.
Cons: Because he is a record execution statement, so in order to let these statements in the slave side can also be executed correctly, then he must also record each statement at the time of execution of some relevant information, that is, contextual information, To ensure that all statements are executed at the slave end and that the same statements are made at the time of execution on the master side. In addition, because MySQL is now developing relatively fast, a lot of new features continue to join, so that the replication of MySQL encountered a large challenge, natural replication involves more complex content, bugs will be more prone to appear. At the statement level, there are a number of things that have been found to cause MySQL replication problems, mainly when modifying the data when using some specific functions or functions, such as: Sleep () function in some versions can not be copied correctly, The last_insert_id () function is used in stored procedures, which may be inconsistent IDs on slave and master, and so on, because the row level is recorded on a per-row basis, so there is no similar problem.

4.2 Row Level mode

The log is recorded in the form of each row of data being modified, and then the same data is modified on the slave side.
Advantage: In row level mode, Binlog can not record the execution of the SQL statement context-sensitive information, only need to record that one record has been modified, modify what, so the log content of row level will be very clear record of each row of data modification details, Very easy to understand. There are no stored procedures, or function, and trigger calls and triggers that cannot be copied correctly in certain situations. He can solve the problem that statement level mode can't solve.
Disadvantage: In row level mode, all executed statements are recorded in each row when logged, which may result in a large amount of log content, such as an UPDATE statement: Update student set Name= ' Nishishei ' where name= ' Linzhongniao ', after execution, the log is not the UPDATE statement but the change of each record updated by this statement, so that many of the records have been updated many events. Naturally, the storage of Bin-log logs can be very large.

4.3 Mixed mode

Mixed mode is a mixture of the first two modes, in mixed mode, MySQL will be executed according to each specific SQL statement to the log form of treatment records, that is, the choice between statement and row one, think which mode is good to use which kind of record. The statement level in the new version is still the same as before, just record the executed statement. The new version of MySQL in the row level mode is also optimized, not many changes will be at the row level to record, want to encounter table structure changes in the statement mode to record, If the SQL statement is really a statement that modifies data such as update or delete, then all rows are changed.

5. Adjust the Binlog Log Mode method 5.1 methods modified in the configuration file

The following is the default mode for Mysql5.5, which is also our front-master copy

mysql> show variables like ‘%binlog_format%‘;+---------------+-----------+| Variable_name | Value |+---------------+-----------+| binlog_format | STATEMENT |+---------------+-----------+

The parameters modified in the configuration file are as follows

[[email protected] ~]# sed -n "39,42p" /data/3306/my.cnf log-bin = /data/3306/mysql-bin#binlog_format = "STATEMENT"#binlog_format = "ROW"binlog_format = "MIXED"

The official recommendation is to use Mixed mode

5.2 Online Modification Immediate effect method

(1) The runtime changes online, the current session is in effect

mysql> set session binlog_formant = ‘STATEMENT‘;mysql> set session binlog_formant = ‘ROW‘;mysql> set session binlog_formant = ‘MIXED‘;

(2) Global effective, all users are effective:

mysql> set global binlog_format = ‘ROW‘;mysql> set global binlog_format = ‘STATEMENT‘;mysql> set global binlog_format = ‘MIXED‘;

(3) Actual combat demo Global modification Binlog log mode and row principle demonstration

A. Modify to row mode

mysql> set global binlog_format = ‘ROW‘;Query OK, 0 rows affected (0.00 sec)

If the query default mode does not modify the highlight login, then login to view the default mode is modified

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

B. Refresh Binlog

In order to better demonstrate we refresh the Binlog, this time when we update the data will be in the newly generated Binlog log file inside the write.

[[email protected] 3306]# mysqladmin -uroot -p123456 -S /data/3306/mysql.sock flush-logs

C. Log in to MySQL to modify the data of the Lingzhongniao library

We have modified the data for the Name field in the student table in the Linzhongniao library to Linzhongniao2

mysql> update student set name=‘linzhongniao2‘;Query OK, 9 rows affected (0.00 sec)Rows matched: 9  Changed: 9  Warnings: 0mysql> select * from student;+----+---------------+| id | name  |+----+---------------+|  1 | linzhongniao2 ||  3 | linzhongniao2 ||  5 | linzhongniao2 ||  6 | linzhongniao2 ||  8 | linzhongniao2 || 10 | linzhongniao2 || 11 | linzhongniao2 || 13 | linzhongniao2 || 15 | linzhongniao2 |+----+---------------+9 rows in set (0.00 sec)

D. Viewing binlog log files

[[email protected] 3306]# mysqlbinlog--base64-output=decode-rows-v mysql-bin.000026 ... Omit ... #180214 0:52:30 server ID 1 end_log_pos 245 table_map: ' Linzhongniao '. ' Student ' mapped to number 33#180214 0:52:3    0 Server ID 1 end_log_pos 617 update_rows:table ID flags:stmt_end_f### Update ' linzhongniao '. ' Student ' # # # where###   @1=1### @2= ' Linzhongniao1 ' # # # set### @1=1### @2= ' Linzhongniao2 ' # # # UPDATE ' Linzhongniao '. ' Student ' # # # where### @1=3### @2= ' Linzhongniao1 ' # # # set### @1=3### @2= ' Linzhongniao2 ' # # # UPDATE ' Linzhongniao '. ' Student ' # # # where### @1= 5### @2= ' Linzhongniao1 ' # # # set### @1=5### @2= ' Linzhongniao2 ' # # # UPDATE ' Linzhongniao '. ' Student ' # # # where### @1=6##   # @2= ' Linzhongniao1 ' # # # set### @1=6### @2= ' Linzhongniao2 ' # # UPDATE ' Linzhongniao '. ' Student ' # # # where### @1=8### @2= ' Linzhongniao1 ' # # # set### @1=8### @2= ' Linzhongniao2 ' # # UPDATE ' Linzhongniao '. ' Student ' # # # where### @1=10### @ 2= ' Linzhongniao1 ' # # # set### @1=10### @2= ' LinzhongNiao2 ' # # # UPDATE ' Linzhongniao '. ' Student ' # # # where### @1=11### @2= ' Linzhongniao1 ' # # # set### @1=11### @2= ' LINZHONGN Iao2 ' # # # UPDATE ' Linzhongniao '. ' Student ' # # # where### @1=13### @2= ' Linzhongniao1 ' # # # set### @1=13### @2= ' Linzhongni Ao2 ' # # # UPDATE ' Linzhongniao '. ' Student ' # # # where### @1=15### @2= ' Linzhongniao1 ' # # # set### @1=15### @2= ' Linzhongnia O2 ' # at 617#180214 0:52:30 server ID 1 end_log_pos 644 Xid = 39commit/*!*/;D elimiter; # End of log file

We look at the above to see the row mode record each row of the modified content, modify what, this will cause the Bin-log log storage will be very large

Mysql DBA Advanced Operations Learning Note-mysql log files in the database