資料庫學習之十：mysql日志管理

十、mysql日志管理

課程大綱

1、MySQL日志型別簡介

2、MySQL錯誤日志配置及查看

3、MySQL binlog介紹及管理實戰

4、MySQL 慢查詢日志設定及管理實戰

1、日志的型別簡介

mysql> show variables like '%log_error%'；在組態檔中指定錯誤日志位置，

mysql> show variables like '%gen%'; 一般日志查詢

二進制日志，記錄修改記錄，

2、MySQL錯誤日志配置及查看

錯誤日志：
配置方法:
[mysqld]
log-error=/data/mysql/mysql.log
查看配置方式：
mysql> show variables like '%log%error%';
作用：
記錄mysql資料庫的一般狀態資訊及報錯資訊，是我們對于資料庫常規報錯處理的常用日志，
一般查詢日志：
配置方法:
[mysqld]
general_log=on
general_log_file=/data/mysql/server2.log
查看配置方式：
show variables like '%gen%';
作用：
記錄mysql所有執行成功的SQL陳述句資訊，可以做審計用，但是我們很少開啟，

3、MySQL binlog介紹及管理實戰

1、二進制日志都記錄了什么？
已提交的資料記錄，以event的形式記錄到二進制檔案中
2、二進制記錄格式有哪些？
row：行模式，即資料行的變化程序，如將Age=19修改成Age=20的程序事件，(一般都是應用這個模式)
statement：陳述句模式，如將update陳述句進行記錄，
mixed：以上兩者的混合模式，
3、三總模式有什么優缺點？
4、binlog的作用
備份恢復、復制

二進制日志管理：

1、開啟二進制日志
set sql_log_bin=0    -->在會話級別修改為臨時關閉
vi /etc/my.cnf
log-bin=/data/mysql/mysql-bin  -->在全域打開binlog

2、設定二進制日志記錄格式（建議是ROW）：
組態檔中修改：
binlog-format=ROW
命令列修改
mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';    
3、查看binlog設定
show variables like '%binlog%';

查詢二進制日志方法：
mysql> show variables like 'binlog';
mysql> show binary logs;
mysql> show master status; 最近一次的，當前正在使用的二進制日志
mysql> show binlog events in 'mysql-bin.000014';
可以通過linux命令列中行程查詢：
[root@centos6-kvm3 mysql]# pwd
/application/mysql/data/mysql
[root@centos6-kvm3 data]# mysqlbinlog mysql-bin.000014 #真正二進制日志內容
詳細的查詢：日志內容分析
[root@centos6-kvm3 data]# mysqlbinlog --help | more
[root@centos6-kvm3 data]# mysqlbinlog --base64-output=decode-rows -v mysql-bin.000014
根據需求截取二進制日志：
[root@centos6-kvm3 data]# mysqlbinlog --start-position=1077  --stop-position=1119 mysql-bin.000014
----
二進制日志
-----
mkdir /data/binlog -p
chown -R mysql.mysql /data/binlog

vim /etc/my.cnf
log-bin=/data/binlog
binlog_format=row
sync_binlog=1

show binary logs;
show binlog events in 'my-bin.000002'
show master status;
截取獲取二進制日志，進行恢復：
mysqlbinlog  --base64-output=decode-rows -v my-bin.000002  #日志內容分析
mysqlbinlog --start-position=120 --stop-position=721   my-bin.000002 #截取二進制日志
mysqlbinlog --start-position=340 --stop-position=721   my-bin.000002 >/tmp/binlog.sql
source /tmp/binlog.sql
二進制日志管理：
重繪二進制日志
flush logs
截取二進制日志
mysqlbinlog --start-position=  --stop-position=    >a.sql 
問題：
1、什么是事件？
2、什么是position	?	

1、通過截取binlog恢復損壞資料
mysqbinlog --start-position=340 --stop-position=721 my-bin.000002 >/tmp/binlog.sql
2、二進制日志翻轉實作閃回資料（擴展）
source /tmp/binlog.sql

洗掉二進制：
默認情況下，不會洗掉舊的日志檔案，
根據存在時間洗掉日志：
SET GLOBAL expire_logs_days = 7;
…或者…
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
根據檔案名洗掉日志：
PURGE BINARY LOGS TO 'mysql-bin.000010';
reset master;#重啟啟用一個二進制檔案，

4、慢日志管理：

功能：
slow-log，記錄所有條件內的慢的SQL陳述句
優化的一種工具日志，幫我們定位問題，
是將mysql服務器中影響資料庫性能的相關SQL陳述句記錄到日志檔案
通過對這些特殊的SQL陳述句分析，改進以達到提高資料庫性能的目的，

慢日志設定：
long_query_time    ：   設定慢查詢的閥值，超出次設定值的SQL即被記錄到慢查詢日志，預設值為10s
slow_query_log      ：  指定是否開啟慢查詢日志
slow_query_log_file ：  指定慢日志檔案存放位置，可以為空，系統會給一個預設的檔案host_name-slow.log
min_examined_row_limit：查詢檢查回傳少于該引數指定行的SQL不被記錄到慢查詢日志
log_queries_not_using_indexes: 不使用索引的慢查詢日志是否記錄到索引

配置例子，添加在組態檔中:
slow_query_log=1 
slow_query_log_file=/data/slow/slow.log
long_query_time=0.5
log_queries_not_using_indexes
	
處理慢日志：
mysqldumpslow命令

mysqldumpslow -s c -t 10 /data/slow/slow.log
mysqldumpslow -s at -t 10 /data/slow/slow.log

這會輸出記錄次數最多的10條SQL陳述句，
其中：
-s
是表示按照何種方式排序
c、t、l、r分別是按照記錄次數、時間、查詢時間、回傳的記錄數來排序，
ac、at、al、ar，表示相應的倒敘，
-t
是top n的意思，即為回傳前面多少條的資料；

----------自己擴展工具
pt-query-diagest    percona-toolkit
mysqlsla

標籤：MySQL

上一篇：資料庫學習之九：mysql 存盤引擎

下一篇：資料庫學習之十一：mysql 備份恢復