mysql-Learning-5-20170511

mysql-Learning-5-20170511

MySQL Foundation II

Use of audit logs requires purchase of Enterprise Edition to support

5.7 Added DDL logs.

8.0 The atom is introduced in the DDL log

General_log is a regular log

The more important is slow log, error log, binary log

If configured, this configuration: Log_error=error.log
If you do not configure it, the default is to display the following way:
$datadir/' hostname '. Err cp $datadir/error.log/backup/error.log
Diff_err= ' Diff/backup/error.log $datadir/error.log '

Determine if there is growth if there is an increase in the situation that needs to be configured into Zabbix monitoring

General-log This is not so scary online, this is the sequential type of IO overhead, if open, it should not be a problem, probably accounted for the cost of MySQL 5%

Slow log can gradually slow down, it is recommended that if less than one second do not record

Long_query_time=1

Lock waiting time is not recorded in the longquerytime inside, this point to pay attention!!!

Just put

For slow log and general_log how to maintain? ****

Actions that can be removed once a day

MV slow.log/path/slow-' Date +%y-%m%-%d '

After the MV has gone, you need to do one of the following:

MYSQL-E "Flush Logs"

Slow_log, it is recommended to send the relevant personnel after daily collection.

Binlog

Need to set an expiration time, command:--expire-log-days is generally set to 7 days

Requires full preparation and Binlog

Relay_log:

From the main, and put it locally executed logs.

A transaction can not cross binary log, if set 128Mb but see the Binlog than 128Mb is also very normal, is because the transaction cannot cross Binlog record

Binlog many cases, restarting the database is slow

The shape of the binlog looks like this:

mysql-bin.000001 show Processlist; ID flush logs;

Row format
Statement VS Row
MIXED Mixed Format x protocol

Innodb Cluster

Use of binlog_do_db is not recommended

Query Binlog for some events:

Binary log: The first 4 section words are a magic number
"Admin" Wu Bingxi (82565387) 21:29:00
Bin
"Admin" Wu Bingxi (82565387) 21:29:35
Then there is an FDC Event
"Admin" Wu Bingxi (82565387) 21:29:49
The first 19 words are fixed again

Row format binary display

-V--base64-output=decode-rows

Purge binary logs before ' 2017-05-01 00:00:00 '; To remove the deletion before May 1

Official audit log for money purchase:

This is in XML format

You need to download a parser in XML format to view it, and Python to see

MySQL commands for daily use:

The SYS library is specifically explained by teacher Yip

++++++++++++++++++++++

"Bubbling" a589-Zhang Youhai-Beijing (250698918) 21:49:18
The teacher has questions: This general and Binlog can record SQL operations? What difference does it have in nature?
"Admin" Wu Bingxi (82565387) 21:50:37
Select, update, the wrong SQL statement connected to the MySQL output, etc. will be recorded in the general log Binlog only logs that log changes to the database will not record the SELECT statement, only the insert update, and so on ++++++++++++++ ++++++++

Now it is recommended to use row format

The focus can be seen in the following tables:

Tables Columns Processlist InnoDB

Restart = stop-start

Show open tables;

Show table status from zst like "u%";

Help show;

mysql-Learning-5-20170511