Shulou(Shulou.com)06/01 Report--

Title index

Log classification

Log operation

Log decomposition

Log classification

In the database system, logs are mainly divided into six categories, as follows:

1. Query log: mainly records daily queries

2. Slow query log: mainly record statements that take longer than the set numerical time, which is convenient for DBA operators to query.

3. Error log: it is mainly used to record error events, which is convenient for DBA operators to operate and maintain the database.

4. Binary log: it is mainly used to record the operation of sql statements on the database, which is convenient for DBA operators to audit, backup and restore.

5. Relay log: mainly used for database master and standby in the design of database cluster architecture.

6. Transaction log: mainly used to prevent the recovery of things when the database server is interrupted abnormally.

Log operation

1. Query log, which can be recorded in the file system or in the table of its own database. The specific recording method can be viewed or changed through all variables. By default, it is only recorded in the file. It is recommended to turn off the query log.

MariaDB [(none)] > show global variables like 'general_log%' +-+-+ | Variable_name | Value | +-+-+ | general_log | ON | | general_log_file | nginxser02.log | # relative directory Relative to the directory of data file installation +-+-+ 2 rows in set (0.00 sec) MariaDB [(none)] > show global variables like 'log_output' +-+-+ | Variable_name | Value | +-+-+ | log_output | FILE | # value can be file | table | none+-+-+1 row in set (0.00 sec)

two。 Slow query log, the execution time exceeds the log of the specified query. Slow query may be unreasonable in database design or the table is locked for non-design reasons. Specific verification or modification can be based on the following example

# configure the configuration file MariaDB [(none)] > show global variables like 'long_query_time' if you want to modify the length of time for slow query and take effect permanently +-+-+ | Variable_name | Value | +-+-+ | long_query_time | 10.000000 | +-+-+ 1 row in set (0.00 sec) MariaDB [(none)] > show global variables like 'slow_query%' +-+-+ | Variable_name | Value | +-+-+ | slow_query_log | OFF | | slow_query_log_file | nginxser02-slow.log | +-+-+ 2 rows in set (0.00 sec) # slow log recording policy setting MariaDB [(none)] > show global variables like 'log_slow%' | +- -+ | Variable_name | Value | + -+ | log_slow_filter | admin Filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table Tmp_table_on_disk | | log_slow_queries | OFF | | log_slow_rate_limit | 1 | | log_slow_verbosity | | +-- -+ 4 rows in set (0.00 sec)

3. The error log records all error messages in the lifecycle of all databases, as follows

MariaDB [(none)] > show global variables like 'log_error%' +-+-+ | Variable_name | Value | +-+-+ | log_error | / var / log/mariadb/mariadb.log | +-+-+ 1 row in set (0.00 sec) # set error warning message 1: record. 0: do not record MariaDB [(none)] > show global variables like 'log_war%'. +-+-+ | Variable_name | Value | +-+-+ | log_warnings | 1 | +-+-+ 1 row in set (0.00 sec)

4. Binary logs, files are also binary files, database recovery can be used, etc., which is turned off by default and can only be modified in the configuration file, where the variable sql_log_bin is the switch for recording binary logs, while log-bin is the path for recording binary files. You can use absolute paths or relative paths based on database installation paths. Max_binlog_size is the default maximum size of a single binary file, and defaults to 1G. If you exceed this size, the log will be scrolled. Of course, it will also be scrolled when the service is restarted. The default value of sync_bin_log is 0, and the logs in memory will not be synchronized to disk in real time. If the server is powered off abnormally, the log will be lost and data will be lost. Whether to enable this function is set according to the architecture settings, as follows

# binary file recording is enabled by default, but the recording location of the binary file MariaDB [(none)] > show global variables like 'log_bin' is not specified +-+-+ | Variable_name | Value | +-+-+ | log_bin | OFF | +-+-+ 1 row in set (0.00 sec) MariaDB [(none)] > show global variables like 'sql_log_bin' +-+-+ | Variable_name | Value | +-+-+ | sql_log_bin | ON | +-+-+ 1 row in set (0.00 sec) MariaDB [(none)] > show global variables like 'sync_binlog' +-+-+ | Variable_name | Value | +-+-+ | sync_binlog | 0 | +-+-+ 1 row in set (0.00 sec)

Open the path specified by the binary file (relative to the path of the database installation), and record it in the file mysql-bin file. The specific configuration is as follows

[root@centos7 ~] # vim / etc/ my.cnf [mysqld] z log-bin=mysql-bin # format for logging, which is based on statement, raw, or a hybrid format of binlog_format=mixed

Log in to the database again for verification

MariaDB [(none)] > show global variables like 'log_bin';+-+-+ | Variable_name | Value | +-+-+ | log_bin | ON | +-+-+ 1 row in set (0.00 sec) MariaDB [(none)] > help show Name: 'SHOW'Description:SHOW has many forms that provide information about databases, tables,columns, or status information about the server. This section describesthose following:SHOW AUTHORSSHOW {BINARY | MASTER} LOGSSHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count] MariaDB [(none)] > show binary logs # View binary logs in use +-+-+ | Log_name | File_size | +-+-+ | mysql-bin.000001 | 264 | | mysql-bin.000002 | 245 | +- -+-+ 2 rows in set (0.00 sec) MariaDB [(none)] > show binlog events in 'mysql-bin.000002' +-+-- + | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-+- -- + | mysql-bin.000002 | 4 | Format_desc | 1 | 245 | Server ver: 5.5.52-MariaDB Binlog ver: 4 | +-+-- + 1 row in set (0. 00 sec) # View the index file of the binary file Check the number of scrolling log files through the index file [root@nginxser02 ~] # cat / var/lib/mysql/mysql-bin.index. / mysql-bin.000001./mysql-bin.000002# look at the binary file [root@nginxser02 ~] # mysqlbinlog / varmp SESSION.PSEUDO_SLAVE_MODE=1*/ through a special command. / *! 40019 SET @ @ session.max.insertinserted delayedthreaded threads @ @ session.Maxially inserted delayedThreadsThread @ @ session.Maxonomic insertingdelayedthreadsThreads @ @ session.max.insertingdelayedthreads @ @ session.maxMaxinsertingdelayedthreadsThreads @ @ session.maxMaxinsertingdelayedThreads @ @ session.max.insertingdelayedthreads @ @ session.maxMaxinsertingdelayedthreaded threads @ @ session.max.insertingdelayedthreaded threads @ @ session.

5. Relay log, which is used by the accomplice server to hold events read from the binary log of the primary server

6. The working principle of transaction log is that at the beginning of things, a buffer is divided in memory to cache transaction logs, and in addition, synchronization of transaction logs fails when the grouping of things in disk files in memory is full, so a group of files is created on disk, and a group of files defaults to 2 files. It is convenient for buffer to synchronize the log to disk files, and the transaction logs in disk files are synchronized to the database system again. Prevent things from being lost abnormally.

Figure 1-1 Log structure diagram of things

By default, the transaction log is configured with a buffer of 8m, a file size of 5m, 2 files in each group, and the transaction log storage path is the root of the relative path, as shown below:

MariaDB [(none)] > show global variables like 'innodb_log%' +-- +-+ | Variable_name | Value | +-+-+ | innodb_log_block_size | 512 | | innodb_log_buffer_size | 8388608 | | innodb_log_file _ size | 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir |. / | +-- +-+ 5 rows in set (0.00 sec)

View log files in which things are synchronized on disk

[root@nginxser02 mysql] # ls-lh / var/lib/mysql/ib_logfile*-rw-rw---- 1 mysql mysql 5.0M Nov 30 05:06 / var/lib/mysql/ib_logfile0-rw-rw---- 1 mysql mysql 5.0M Nov 8 22:56 / var/lib/mysql/ib_logfile1

In addition, the thing log has redo log and undo log,redo, that is, the thing is completed and saved in the thing log, but it is not synchronized to the data system, so in order to synchronize into the database system, the thing log must be redo,undo that is the unfinished thing and saved in the thing log. At this time, things need to be rolled back and the previous things will be undo.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.