Mysql learning record (25) -- mysql Log _ MySQL

I. theory: 1. error log: a records information related to mysqld start and stop and error. you can view this information when the database fails and cannot be started. B. use -- log-error to specify the location where mysqld saves the error log file. 2. binary log: astate 1. theory:
1. error log:
A. records information about mysqld startup and stop and error. you can view this information when the database fails and cannot start.
B. You can use -- log-error to specify the location where mysqld stores error log files.
2. binary log:
A. statement: all records are statements. Advantage: The log record is clear and easy to read, the log volume is small, and has little impact on I/O. disadvantage: in some cases, slave log replication may fail.
B. row: records the changes of each row in the log, instead of the SQL statement. Advantage: record the details of data changes in each row, so that data cannot be copied in some cases. disadvantage: the large log volume has a great impact on I/O.
C. mixed: the default log format of mysql. Try to use the advantages of the two models to avoid their shortcomings.
D. The binlog_format operation can be performed at the global and session levels to ensure normal replication of slave databases.
3. log reading:
A. mysqlbinlog tool
4. Log deletion:
A. reset master. all binlog logs can be deleted.
B. purge master logs to 'MySQL-bin. * 'will delete all logs before *.
C. purge master logs before 'yyyy-mm-dd hh24: mi: SS' will delete all logs before the specified date
D. -- expire_logs_days = #: Set the log expiration days.
5. other options:
A. -- binlog-do-db = db_name: only the db_name database record is updated to the binary log without updating other databases.
B. -- binlog-ignore-db = db_name: ignore the binary log recorded in the db_name database.
C. -- innodb-safe-binlog
D. SQL _log_bin = 0: a client with super permission can disable the write of its own operations to binary records by setting this value. However, the master/slave data may be inconsistent.
6. log reading: The Log Query format is plain text, so you can directly read it.
7. slow query log:
A. logs of all SQL statements whose time exceeds the long_query_time value and whose number of scan records is not less than in_examined_row_limit
B. by default, management statements and statements that do not use indexes for queries do not record slow query logs.
C. -- slow_query_log specifies the slow query status, -- show_query_log_file specifies the slow query output path, -- log-out specifies the slow query output method (output to the table can only be accurate to seconds, output to files can be accurate to microseconds)
8. log reading:
A. query the long_query_time value: show variables like 'Long % ';
B. set long_query_time: set the value of long_query_time.
C. more localhost-slow.log: View slow query log values
9. other tools for viewing logs:
A. mysqlsla: View Logs

B. sqlprofi, mysql-expain-slow-log, and mysqllogfilter: analyze logs.

II. practice:

Mysql> use test; reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql> create table emp (-> id int (11 ), -> info varchar (20)->) engine = innnodb charset = utf8; Query OK, 0 rows affected, 2 warnings (0.05 sec) mysql> insert into emp values (1, 'z1 '); Query OK, 1 row affected (0.00 sec) mysql> insert into emp Values (1, 'z2'); Query OK, 1 row affected (0.00 sec) mysql> exitabc @ ubuntu :~ /Downloads/mysql $ mysql-uroot-p123 Welcome to the MySQL monitor. commands end with; or \ g. your MySQL connection id is 98 Server version: 5.5.44-log Source distributionCopyright (c) 2000,201 5, Oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. other names may be trademarks of their respectiveowners. type 'help; 'or' \ H' fo R help. type '\ C' to clear the current input statement. mysql> show global variables like '% log % '; + Metrics + | Variable_name | Value | + Metrics + | back_log | 50 | binlog_cache_size | 32768 | binlog_direct_non_transactional_updates | OFF | binlog_f Ormat | STATEMENT | binlog_stmt_cache_size | 32768 | expire_logs_days | 14 | general_log | OFF | general_log_file |/usr/local/mysql/data/ubuntu. log | bytes | 2 | bytes | OFF | innodb_log_buffer_size | 8388608 | innodb_log_file_size | 67108864 | innodb_log_files_in_group | 2 | innodb_log_group_home_dir |. /| innodb_mirrored_log_groups | 1 | log | OFF | log_bin | ON | log_bin_trust_function_creators | OFF | log_error |/usr/local/mysql/data/mysql-error.log | log_output | FILE | log_queries_not_using_indexes | | log_slave_updates | OFF | log_slow_queries | ON | log_warnings | 1 | max_binlog_cache_size | 18446744073709547520 | max_binlog_size | 1073741824 | metrics | 18446744073709547520 | max_rel Ay_log_size | 0 | relay_log | relay_log_index | metrics | relay-log.info | relay_log_purge | ON | relay_log_recovery | OFF | metrics | 0 | slow_query_log | ON | metrics | /usr/local/mysql/data/mysql-slow.log | SQL _log_bin | ON | SQL _log_off | OFF | sync_binlog | 1 | sync_relay_log | 0 | sync_relay_log_info | 0 | + Metrics ------------------------------- ---------- + ------------------------------------- + 41 rows in set (0.02 sec)-> Ctrl-C -- exit! Abortedabc @ ubuntu :~ /Downloads/mysql $ mysqld -- verbose -- help | grep-A 1 'default option' 151106 15:37:14 [Warning] option 'Table _ definition_cache ': unsigned value 100 adjusted to 400151106 15:37:14 [Note] mysqld (mysqld 5.5.44-log) starting as process 76330... 151106 15:37:14 [Warning] Can't create test file/usr/local/mysql/data/ubuntu. lower-test151106 15:37:14 [Warning] Can't create test file/usr/local/mysql/data /Ubuntu. lower-test151106 15:37:14 [Warning] One can only use the -- user switch if running as rootmysqld: File '/usr/local/mysql/data/mysql-bin.index' not found (Errcode: 13) default options are read from the following files in the given order:/etc/my. cnf/etc/mysql/my. cnf/usr/local/mysql/etc/my. cnf ~ /. My. cnf 151106 15:37:14 [ERROR] Abortingabc @ ubuntu :~ /Downloads/mysql $ mysqld -- verbose -- help | grep-A 1 'default option' 151106 15:37:32 [Warning] option 'Table _ definition_cache ': unsigned value 100 adjusted to 400151106 15:37:32 [Note] mysqld (mysqld 5.5.44-log) starting as process 76335... 151106 15:37:32 [Warning] Can't create test file/usr/local/mysql/data/ubuntu. lower-test151106 15:37:32 [Warning] Can't create test file/usr/local/mysql/data /Ubuntu. lower-test151106 15:37:32 [Warning] One can only use the -- user switch if running as rootmysqld: File '/usr/local/mysql/data/mysql-bin.index' not found (Errcode: 13) default options are read from the following files in the given order:/etc/my. cnf/etc/mysql/my. cnf/usr/local/mysql/etc/my. cnf ~ // My. cnf 151106 15:37:32 [ERROR] Aborting // the following file is the configuration file abc @ ubuntu on the mysql machine :~ /Downloads/mysql $ vi/usr/local/mysql/etc/my. cnf mysql> select count (*) from payment p left join customer c on p. payment_id = c. customer_id; + ---------- + | count (*) | + ---------- + | 16049 | + ---------- + 1 row in set (0.06 sec) mysql> show variables like 'Long % '; + ----------------- + ---------- + | Variable_name | Value | + ----------------- + ---------- + | long_query_time | 2.000000 | + ----------------- + ---------- + 1 Row in set (0.00 sec) mysql> set long_query_time = 0.05; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'Long % '; + ----------------- + ---------- + | Variable_name | Value | + ----------------- + ---------- + | long_query_time | 0.050000 | + ----------------- + -------- + 1 row in set (0.00 sec) mysql> select count (*) from payment p left join customer c on p. payment_id = c. customer_id order by c. cuz Tomer_id; + ---------- + | count (*) | + ---------- + | 16049 | + ---------- + 1 row in set (0.07 sec) mysql> Ctrl-C -- exit! Abortedabc @ ubuntu:/usr/local/mysql/data $ sudo tail. /mysql-slow.log [sudo] password for abc: # Time: 151106 23:40:39 # User @ Host: root [root] @ localhost [] # Query_time: 0.047216 Lock_time: 0.000099 Rows_sent: 1 Rows_examined: 16650 SET timestamp = 1446882039; select count (*) from payment p left join customer c on p. payment_id = c. customer_id; # Time: 151106 23:41:02 # User @ Host: root [root] @ localhost [] # Query_time: 0.075027 Lock_time: 0.000083 Rows_sent: 1 Rows_examined: 32699 SET timestamp = 1446882062; select count (*) from payment p left join customer c on p. payment_id = c. customer_id order by c. customer_id;