mysql 學習記錄（二十五）--mysql日誌

標籤：最佳化   io   資料庫   mysql   日誌   

一、理論：
1.錯誤記錄檔：
a.記錄了mysqld啟動和停止時以及出錯時的相關資訊，當資料庫出現故障導致無法啟動時可以先查看此資訊。
b.可用--log-error來指定mysqld儲存錯誤記錄檔檔案的位置
2.二進位日誌：
a.statement:記錄的都是語句。優點：日誌記錄清晰易讀、日誌量少、對I/O影響較小，缺點：在某些情況下slave的日誌複製會出錯
b.row:將每一行的變更記錄到日誌中，而不是記錄sql語句。優點：記錄每一行的資料變化細節，不會出現某些情況下無法複製的情況，缺點：日誌量大，對I/O影響較大
c.mixed:目前mysql的預設日誌格式。儘可能對上兩種模式的優點加以利用而避開它們的缺點
d.可以在global和session層級對binlog_format進行日誌格式的操作，確保從庫的複製能夠正常進行
3.日誌的讀取：
a.mysqlbinlog工具
4.日誌的刪除：
a.reset master.可以刪除所有的binlog日誌
b.purge master logs to ‘mysql-bin.*‘，將刪除*編號前的所有日誌
c.purge master logs before ‘yyyy-mm-dd hh24:mi:ss‘,將刪除日期為指定日期之前的所有日誌
d.--expire_logs_days=#：設定日誌的到期天數
5.其他選項：
a.--binlog-do-db=db_name:僅更新db_name資料庫記錄到二進位日誌中而不更新其他資料庫
b.--binlog-ignore-db=db_name:忽略db_name資料庫記錄到二進位日誌中
c.--innodb-safe-binlog:與--sync-binlog=N(每寫N次日誌同步磁碟)一起配合使用，使得事務在日誌中的記錄更加安全
d.sql_log_bin=0:具有super許可權的用戶端可以通過設定此值使得禁止將自己的操作寫入二進位記錄。但有可能會導致主從資料不一致
6.日誌的讀取：查詢日誌記錄的格式是純文字，所以可以直接進行讀取
7.慢查詢日誌：
a.記錄了所有時間超過long_query_time的設定值並且掃描記錄數不小於in_examined_row_limit的所有sql語句的日誌 
b.預設情況下，管理語句和不使用索引進行查詢的語句不會記錄到慢查詢日誌
c.--slow_query_log指定慢查詢的狀態，--show_query_log_file指定慢查詢輸出的路徑，--log-out指定輸出慢查詢的方式（輸出到表則只能精確到秒，輸出到檔案則能精確到微秒）
8.日誌的讀取：
a.查詢long_query_time的值:show variables like ‘long%‘;
b.set long_query_time：設定long_query_time的值
c.more localhost-slow.log:查看慢查詢日誌的值
9.其他查看日誌的相關工具：
a.mysqlsla:查看日誌

b.sqlprofi,mysql-expain-slow-log,mysqllogfilter：分析日誌

二、實踐：

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> exit[email protected]:~/Downloads/mysql$ mysql -uroot -p123 Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 98Server version: 5.5.44-log Source distributionCopyright (c) 2000, 2015, 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' 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                                     |+-----------------------------------------+---------------------------------------+41 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 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] Aborting[email protected]:~/Downloads/mysql$ mysqld --verbose --help | grep -A 1 'Default options'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//以下檔案就是mysql的本機上的設定檔[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 customer 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 23:40:39# [email protected]: root[root] @ localhost []# Query_time: 0.047216  Lock_time: 0.000099 Rows_sent: 1  Rows_examined: 16650SET timestamp=1446882039;select count(*) from payment p  left join 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 Rows_sent: 1  Rows_examined: 32699SET timestamp=1446882062;select count(*) from payment p  left join customer c  on p.payment_id = c.customer_id order by c.customer_id;

著作權聲明：本部落格原創文章歡迎轉載，請轉載的朋友最好註明出處，謝謝大家。

mysql 學習記錄（二十五）--mysql日誌