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

1.1. Error log file (error log)

The default path is the file ending with error.log in the data directory.

View the file location:

Show variables like'% og_error%'

Pay attention to warnings information

Characteristics of TIMESTAMP types

1.2. Binary log file (binary log)

Record all DML statements after submission. To record select and show, you need to enable the full log feature.

The main functions of binary logs are:

L complete the master-slave replication function. Binlog records all modified data on the master server and sends them to the slave server.

L carries on the recovery operation, through the binlog, uses the mysqlbinlog command, realizes the recovery based on the time point and the location.

The configuration parameter log-bin=ON starts the binary log. If there is no named file name, the default mysql-binlog.xxxx is the binlog file name, which is stored in the data directory by default.

[mysql] > show variables like'% log_bin%'

+-+

| | Variable_name | Value |

+-+

| | log_bin | ON |

| | log_bin_basename | / data/mysql/mysql-binlog |

| | log_bin_index | / data/mysql/mysql-binlog.index |

| | log_bin_trust_function_creators | OFF |

| | log_bin_use_v1_row_events | OFF |

| | sql_log_bin | ON |

+-+

6 rows in set (0.00 sec)

View the current binlog file and value size

[mysql] > show binary logs

+-+ +

| | Log_name | File_size |

+-+ +

| | mysql-binlog.000001 | 177 | |

| | mysql-binlog.000002 | 177 | |

| | mysql-binlog.000003 | 1844 | |

| | mysql-binlog.000004 | 369 | |

| | mysql-binlog.000005 | 592 | |

| | mysql-binlog.000006 | 1065 | |

| | mysql-binlog.000007 | 615 |

| | mysql-binlog.000008 | 217 | |

| | mysql-binlog.000009 | 194 | |

+-+ +

9 rows in set (0.00 sec)

View mysql current log and status

[mysql] > show master status

+-- +

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-- +

| | mysql-binlog.000009 | 194 | 9c21d663-e09d-11e8-b535-000c29899e07:1-13 | |

+-- +

1 row in set (0.00 sec)

The mysql-bin.index file in the data directory is the index file of the binary log, which is used to record the sequence number of the resulting binary log.

Introduction of binlog parameters

Max_binlog_size: set the maximum value of a single binlog. Default is 1G. If the production environment controls the generation time to be less than 5 minutes, it is recommended to adjust it to 256MB.

Binlog_cache_size: all uncommitted transactions are recorded in a cache, and when waiting for the transaction to commit, the binary log in the cache is written directly to binlog. Each new session defaults to 32K; each thread starts a transaction and automatically allocates a cache. If the buffer is too small, temporary disk files will be used. It can be judged by the following command.

[mysql] > show global status like'% binlog_cache%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Binlog_cache_disk_use | 0 | |

| | Binlog_cache_use | 0 | |

+-+ +

Mainly depends on these two worthy of use; the production environment is generally 4MB.

Binlog_format stands for binary file format, there are three kinds of statement,row,mixed.

L statement: operation-based SQL statements are recorded in binlog, abbreviated as the default format before SBR,5.1, to save IO, but sometimes master-slave data is inconsistent and discarded.

L mixed: mixed row and statement formats are not recommended.

L row: record the content before and after the change based on the change record of the line, referred to as RBR. Do not record SQL statement context information, only record data changes, in any case can be copied, but generate a large number of logs. Recommended.

Add the binlog_rows_query_log_events parameter after mysql5.6, and you can see the complete sql statement in row mode. 5.7 off by default.

[mysql] > show variables like'% binlog_rows%'

+-+ +

| | Variable_name | Value |

+-+ +

| | binlog_rows_query_log_events | OFF |

+-+ +

Sync_binlog: determines the refresh mechanism of binlog.

Expire_logs_days: binlog expiration time (days)

Binlog-do-db or binlog-ignore-db: indicates which libraries are written or ignored. Default is empty.

Log_slave_updates: when configuring the architecture of m > S1 > S2, you need to set it to 1 on S1 before S1 can synchronize S2.

Binlog_checksum: check the write to binlog. Default is crc32.

Binlog_row_imag determines the record scope, and the default full records all records; minimal records only the information to modify the column; and noblob records all fields except blog and text.

Mysqlbinlog command to view the binlog demonstration

L View current binlog format

[mysql] > show variables like'% binlog_format%'

+-+ +

| | Variable_name | Value |

+-+ +

| | binlog_format | ROW |

+-+ +

L create test table t

CREATE TABLE `t` (

`id`int (11) NOT NULL auto_increment

`name` varchar (10) DEFAULT NULL

`city` varchar (10) DEFAULT NULL

PRIMARY KEY (`id`)

ENGINE=INNODB auto_increment=1

L insert data

Insert into t (name,city) values ('zs','bj'), (' zz','sh')

L update data

Update t set name=' zs1' where name='zs'

L copy binlog file

# cp mysql-binlog.000009 / tmp/mysqlbinlog9bk

L convert binlog

# / usr/local/mysql/bin/mysqlbinlog-- no-defaults-v-v-- base64-output=decode-rows / tmp/mysqlbinlog9bk > / tmp/binlog.txt

Parameter explanation:

-v you can see the specific implementation information

-- base64-output: convert binary log files to format

L look at the converted file, you can see the contents before and after the update.

# UPDATE `mysql`.`t`

# WHERE

# @ 1room1 / * INT meta=0 nullable=0 is_null=0 * /

# @ 2 roommates / * VARSTRING (40) meta=40 nullable=1 is_null=0 * /

# @ 3roombj' / * VARSTRING (40) meta=40 nullable=1 is_null=0 * /

# SET

# @ 1room1 / * INT meta=0 nullable=0 is_null=0 * /

# @ 2roomzs1' / * VARSTRING (40) meta=40 nullable=1 is_null=0 * /

# @ 3roombj' / * VARSTRING (40) meta=40 nullable=1 is_null=0 * /

1.3. Slow query log slow log

Record all sql statements that exceed the parameter long_query_time time. 5.7 defaults to 0.5.

View slow log location: [mysql] > show variables like'% slow_query_log_file%'

Tools for viewing slow log: percona-toolkit, refer to p84

Download address: https://www.percona.com/downloads/percona-toolkit/LATEST/

The sample version in the book is 3.0.3, and the latest version of the official website is 3.0.12. You can download the rpm package of the corresponding system, or the general version of linux-generic, and install the package 7.8m.

Decompress after download:

# tar-zxvf percona-toolkit-3.0.12_x86_64.tar.gz

Generate a slow SQL report with the following command:

# pwd

/ usr/local/percona-toolkit-3.0.12/bin

#. / pt-query-digest-- since=24th / data/mysql/slow.log > slowquery.log

Invalid-since value at. / pt-query-digest line 13813.

Install the rpm package with the same error; we'll study it later.

1.4. Full log general log

Sql that records all operations of the mysql database, including select and show, turned off by default.

View the general log configuration:

[mysql] > show variables like'% general%'

+-- +

| | Variable_name | Value |

+-- +

| | general_log | OFF |

| | general_log_file | / data/mysql/localhost.log |

+-- +

Set to take effect before restarting the database:

[mysql] > set global general_log='ON'

Log content:

[root@localhost mysql] # tail-f-n 100 localhost.log

/ usr/local/mysql/bin/mysqld, Version: 5.7.24-log (MySQL Community Server (GPL)). Started with:

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

Time Id Command Argument

2018-11-06T06:14:26.924983Z 67 Query commit

2018-11-06T06:14:29.037015Z 67 Query set global general_log='ON'

2018-11-06T06:14:32.715302Z 67 Query show variables like'% general%'

2018-11-06T06:15:04.075786Z 67 Quit

Log_output: a global dynamic variable that determines how all logs are saved. This parameter also determines how slow log is saved. Optional:

L File, default file storage, recommended

L table: stored in the newly created table general_ log table

L NONE: do not save

1.5. Audit log audit log

Database audit (referred to as DBAudit) can record database activities on the network in real time, conduct compliance management of fine-grained audit of database operations, warn against risky behaviors, and block attacks. Through the recording, analysis and reporting of users' access to the database, it is used to help users generate compliance reports and trace the source of accidents, and at the same time strengthen the network behavior records of internal and external databases and improve the security of data assets.

The audit function can only be used in the enterprise version. You can use the third-party open source plug-in libaudit_plugin.so to audit wa memory in mysql5.7 Community Edition. Reference p88

Official download address: https://bintray.com/mcafee/mysql-audit-plugin/

Github: https://github.com/studywellnow/mysql-audit

Version in the book: 1.1.4-725 the latest download version is 1.1.6-784

1.5.1. Mysql-audit-plugin installation mine stampede

L extract the plug-in package:

# unzip audit-plugin-mysql-5.7-1.1.6-784-linux-x86_64.zip

Copy the extracted plug-in to the plug-in directory of the mysqllib library:

[root@localhost lib] # pwd

/ orasoft/audit-plugin-mysql-5.7-1.1.6-784/lib

[root@localhost lib] # cp libaudit_plugin.so / usr/local/mysql/lib/plugin/

L database command line installation plug-in

INSTALL PLUGIN AUDIT SONANEM 'libaudit_plugin.so'

[mysql] > install plugin audit soname 'libaudit_plugin.so'

ERROR 1123 (HY000): Can't initialize function 'audit'; Plugin initialization function failed.

Check github, the current version supports mysql 5.7.19, while the current database version is 5.7.24, which doesn't seem to work.

Database log prompt: 2018-11-06T06:48:10.122754Z 5 [Note] McAfee Audit Plugin: Couldn't find proper THD offsets for: 5.7.24-log

According to the book record below, we will study it again when we are free.

L check whether the plug-in function is enabled

Show variables like'% audit%'

L enable plug-in function:

Set global audit_json_file=1

L View Log

Generate a mysql-audit.json audit log under the mysql data directory, which can look up the user name of the operation SQL, IP.

1.6. Relay log relay log

In master-slave replication, the slave server IO thread reads and records the binlog of the master server into the local file relay log, and then reads the application from the server SQL thread.

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.