MySQL Learning record (25)--mysql log

First, the theory:
1. Error log:
A. Records information about when the mysqld starts and stops and when the error occurs, which can be viewed first when a database failure causes it to fail to start.
B. Use--log-error to specify where mysqld save the error log file
2. Binary log:
A.statement: The statements are all recorded. Pros: Log records are legible, log volumes are low, impact on I/O is small, and disadvantages: In some cases, slave log replication will fail
B.row: Records each row of changes to the log instead of logging the SQL statement. Advantages: Record the details of each row of data changes, there will be no replication in some cases, the disadvantage: large log volume, large impact on I/O
C.mixed: The default log format for MySQL at this time. Use as much of the advantages of the last two models as possible to avoid their drawbacks
D. Binlog_format can be logged at the global and session levels to ensure that replication from the library works
3. Log reads:
A.mysqlbinlog Tools
4. Deletion of logs:
A.reset Master. You can delete all the Binlog logs
B.purge master logs to ' mysql-bin.* ', all logs before the * number will be deleted
C.purge master logs before ' Yyyy-mm-dd hh24:mi:ss ', removes all logs before the specified date
d.--expire_logs_days=#: Set the number of days that the log expires
5. Other options:
A.--binlog-do-db=db_name: Update only db_name database records into binary logs without updating other databases
B.--binlog-ignore-db=db_name: Ignore db_name database logging into binary log
C.--innodb-safe-binlog: Used in conjunction with--sync-binlog=n (each write N log synchronous disk), making transactions in logs more secure
D.sql_log_bin=0: Clients with super privileges can disable writing their own operations to binary records by setting this value. But it could lead to inconsistent master and slave data.
6. Log reads: Query log records are formatted as plain text, so they can be read directly
7. Slow Query log:
A. Log of all SQL statements that have all time exceeded long_query_time and scan record count not less than in_examined_row_limit
B. By default, statements that manage statements and queries that do not use indexes are not logged to the slow query log
C.--SLOW_QUERY_LOG specifies the state of the slow query,--show_query_log_file specifies the path of the slow query output,--log-out specifies how to output a slow query (output to the table is only accurate to the second, output to the file can be accurate to microseconds)
8. Log reads:
A. Query the value of Long_query_time: Show variables like ' long% ';
B.set long_query_time: Setting the value of the Long_query_time
C.more localhost-slow.log: View the value of the slow query log
9. Other related tools for viewing logs:
A.mysqlsla: Viewing logs

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

Second, Practice:

mysql> use test; Reading table information for completion of table and column namesyou can turn off this feature to get a quicker startup W Ith-adatabase changedmysql> CREATE TABLE emp (ID int (one), Info varchar ()) engine = Innno DB 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> exit[email protected]:~/downloads/mysql$ mysql-uroot-p123 Welcome to T  He MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 98Server version:5.5.44-log Source distributioncopyright (c), Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> mysql> show Global variables like '%log% '; +-----------------------------------------+---------------------------------------+ | variable_name | Value |+-----------------------------------------+---------------------------------------+ | Back_log | 50 | | Binlog_cache_size | 32768 | | Binlog_direct_non_transactional_updates | OFF | | Binlog_format | STATEMENT | | Binlog_stmt_cache_size | 32768 | | Expire_logs_days | 14 | | General_log | OFF | | General_log_file | /usr/local/mysql/data/ubuntu.log | | Innodb_flush_log_at_trx_commit |           2                          || Innodb_locks_unsafe_for_binlog | 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 |              On                      || Log_slave_updates | OFF | | log_slow_queries | On | | log_warnings | 1 | | Max_binlog_cache_size | 18446744073709547520 | | Max_binlog_size | 1073741824 | | Max_binlog_stmt_cache_size | 18446744073709547520 | | Max_relay_log_size | 0 | |                                       Relay_log | ||                                       Relay_log_index | || Relay_log_info_file | Relay-log.info | | Relay_log_purge | On | | Relay_log_recovery |                 OFF                  || Relay_log_space_limit | 0 | | Slow_query_log | On | | Slow_query_log_file | /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 |+-----------------------------------------+---------------------------------------+ $ rows in Set (0.02 sec), ctrl-c--exit! aborted[email protected]:~/downloads/mysql$ mysqld--verbose--help | Grep-a 1 ' Default options ' 151106 15:37:14 [Warning] option ' table_definition_cache ': unsigned value of 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 C reate test file/usr/local/mysql/data/ubuntu.lower-test151106 15:37:14 [Warning] One can only use the--user switch if run Ning as Rootmysqld:file '/usr/local/mysql/data/mysql-bin.index ' not Found (errcode:13) Default options is 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 [E Rror] aborting[email protected]:~/downloads/mysql$ mysqld--verbose--help | Grep-a 1 ' Default options ' 151106 15:37:32 [Warning] option ' table_definition_cache ': Unsigned value adjusted to 40015 1106 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 C reate Test file/usr/local/mysql/data/ubuntu.lower-test151106 15:37:32 [Warning] One can only use the--user switch if running as Rootmysqld:fi  Le '/usr/local/mysql/data/mysql-bin.index ' not Found (errcode:13) Default options is 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 on the MySQL native [email protected]:~/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 Custome R C on p.payment_id = c.customer_id ORDER by c.customer_id;+----------+|    COUNT (*) |+----------+| 16049 |+----------+1 row in Set (0.07 sec) mysql> ctrl-c--exit! aborted[email protected]:/usr/local/mysql/data$ sudo tail./mysql-slow.log [sudo] password for ABC: # time:151106 2 3:40:39# [email protected]: root[root] @ localhost []# query_time:0.047216 lock_time:0.000099 rows_sent:1 Rows_e Xamined:16650set timestamp=1446882039;select Count (*) from payment p left joins customer C on p.payment_id = C.customer_ id;# time:151106 23:41:02# [email protected]: root[root] @ localhost []# query_time:0.075027 lock_time:0.000083 R Ows_sent:1 Rows_examined:32699set timestamp=1446882062;select Count (*) from payment p left join customer C on P.payme nt_id = c.customer_id ORDER by C.customer_id; 

Copyright Notice: This blog original article welcome reprint, please reprint friend best note source, thank you.

MySQL Learning record (25)--mysql log