Cloud computing learning materials, courseware, msyql knowledge point explanation

In this article, we will explain the difference between now (), sysdate () and curdate () in msyql:

SELECT date_sub (date_sub (date_format (now (),'% y-%m-%d'), interval extract (day from now ())-1 day), interval 1 month)

Select date_sub ('2019-06-01 minute interval 1 month)

The first day of last month

Select date_sub (date_sub (date_format (now (),'% ymi% mmure% d'), interval extract (day from now ()) day), interval 0 month)

The last day of last month

Select date_sub (date_sub (date_format (now (),'% ymi% mmure% d'), interval extract (day from now ())-1 day), interval 0 month)

Select date_format (now (),'% Y-%m-01')

The first day of the month

Mysql log management

Error log error log troubleshooting / var/log/mysqld.log [enabled by default]

Bin log binary log backup incremental backup DDL DML DCL

Relay log Relay Log replication receives replication master

Slow log slow query log tuning query time exceeds the specified value

Error Log

Log-error=/var/log/mysqld.log

Binary Log (for backing up and restoring data)

Generate binlog logs:

Log-bin=/var/log/mysql-bin/slave2

Serve-id=2

Mkdir / var/log/mysql-bin/slave2

# chmod mysql.mysql / var/log/mysql-bin/slave

# systemctl restart mysqld

Restarting mysqld will truncate the old log and create a new log.

Refreshing the log will truncate the old log and create a new log.

Mysql > flush logs

Delete all binlog (disabled)

Mysql > reset master

Delete part of the log

Mysql > PURGE BINARY LOGS TO 'mysql-bin.010'

Mysql > PURGE BINARY LOGS BEFORE '2016-04-02 22 purl 46purl 26'

Suspend the binlog logging feature (valid for the current session only)

Mysql > SET SQL_LOG_BIN=0

Mysql > SET SQL_LOG_BIN=1

Read the binlog log:

Mysqlbinlog mysql.000002

Press datetime to read:

Mysqlbinlog mysql.000002-start-datetime= "2018-12-05 10:02:56" mysqlbinlog mysql.000002-- stop-datetime= "2018-12-05 11:02:54" mysqlbinlog mysql.000002-- start-datetime= "2018-12-05 10:02:56"-- stop-datetime= "2018-12-05 11:02:54"

Press position to read:

Mysqlbinlog mysql.000002-start-position=260mysqlbinlog mysql.000002-stop-position=260mysqlbinlog mysql.000002-start-position=260-stop-position=930

View binlong logs with encryption

Mysqlbinlog-output=decode-rows-v log file

Recover data according to binlog:

Recover data based on a point in time

Mysqlbinlog-- start-datetime='2014-11-25 11 11 stop-datetime='2014 56 tiger-bin.000001 54'--stop-datetime='2014-11-25 11 11 V 57 V 41 'tiger-bin.000001 | mysql-u root-p1

Restore data based on location point

Mysqlbinlog-- start-position 106--stop-position 527tiger-bin.000001 | mysql-u root-p1

Refresh the bin-log log:

# mysqladmin flush-logs

Remove binlog encryption:

Transaction_isolation=repeatable-read

Binlog_format=mixed

Slow query:

Slow_query_log=1

Slow_query_log_file=/var/log/mysql-slow/slow.log

Long_query_time=3

Mkdir / var/log/mysql-slow/chown mysql.mysql / var/log/mysql-slow/systemctl restart mysqld

View slow query log

Test: BENCHMARK (count,expr)

SELECT BENCHMARK (50000000)