一.MySQL日志簡介
二.錯誤日志
作用：
記錄mysql資料庫的一般狀態資訊及報錯資訊，是我們對于資料庫常規報錯處理的常用日志，
默認位置：
$MYSQL_HOME/data/
開啟方式:（MySQL安裝完后默認開啟)
#編輯組態檔
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
log_error=/application/mysql/data/$hostname.err
#查看方式
mysql> show variables like 'log_error';
三.一般查詢日志
作用：
記錄mysql所有執行成功的SQL陳述句資訊，可以做審計用，但是我們很少開啟，
默認位置：
$MYSQL_HOME/data/
開啟方式:（MySQL安裝完之后默認不開啟）
#編輯組態檔
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
general_log=on
general_log_file=/application/mysql/data/$hostnamel.log
#查看方式
mysql> show variables like '%gen%';
四.二進制日志
作用：
記錄已提交的DML事務陳述句，并拆分為多個事件（event）來進行記錄
記錄所有DDL、DCL等陳述句
總之，二進制日志會記錄所有對資料庫發生修改的操作
默認是否開啟: 關閉
默認位置:$datadir
默認名字:mysql-bin
二進制日志模式:
statement：陳述句模式，上圖中將update陳述句進行記錄（默認模式），
row：行模式，即資料行的變化程序，上圖中Age=19修改成Age=20的程序事件，
mixed：以上兩者的混合模式，
企業推薦使用row模式
優缺點:
statement模式：
優點：簡單明了，容易被看懂，就是sql陳述句，記錄時不需要太多的磁盤空間，
缺點：記錄不夠嚴謹，
row模式：(記錄sql陳述句的變化程序)
優點：記錄更加嚴謹，
缺點：有可能會需要更多的磁盤空間，不太容易被讀懂，
binlog的作用:
1）如果我擁有資料庫搭建開始所有的二進制日志，那么我可以把資料恢復到任意時刻
2）資料的備份恢復
3）資料的復制
二進制日志的管理操作實戰
開啟方式
[root@db01 data]# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format=row
注意:在mysql5.7中開啟binlog必須要加上server-id，
[root@db01 data]# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin #也可以絕對路徑
binlog_format=row #日志模式
server_id=1 #mysql5.7中必須要加
二進制日志的操作
#物理查看
[root@db01 data]# ll /application/mysql/data/
-rw-rw---- 1 mysql mysql 285 Mar 6 2017 mysql-bin.000001
#命令列查看
mysql> show binary logs;
mysql> show master status;
#查看binlog事件
mysql> show binlog events in 'mysql-bin.000007';
事件介紹
1）在binlog中最小的記錄單元為event
2）一個事務會被拆分成多個事件（event）
事件（event）特性
1）每個event都有一個開始位置（start position）和結束位置（stop position），
2）所謂的位置就是event對整個二進制的檔案的相對位置，
3）對于一個二進制日志中，前120個position是檔案格式資訊預留空間，
4）MySQL第一個記錄的事件，都是從120開始的，
row模式下二進制日志分析及資料恢復
#查看binlog資訊
mysql> show master status;
#創建一個binlog庫
mysql> create database binlog;
#使用binlog庫
mysql> use binlog
#創建binglog_table表
mysql> create table binlog_table(id int);
#查看binlog資訊
mysql> show master status;
#插入資料1
mysql> insert into binlog_table values(1);
#查看binlog資訊
mysql> show master status;
#提交
mysql> commit;
#查看binlog資訊
mysql> show master status;
#插入資料2
mysql> insert into binlog_table values(2);
#插入資料3mysql> insert into binlog_table values(3);
#查看binlog資訊
mysql> show master status;
#提交
mysql> commit;
#洗掉資料1
mysql> delete from binlog_table where id=1;
#查看binlog資訊
mysql> show master status;
#提交
mysql> commit;
#更改資料2為22
mysql> update binlog_table set id=22 where id=2;
#查看binlog
mysql> show master status;
#提交
mysql> commit;
#查看binlog資訊
mysql> show master status;
#查看資料
mysql> select * from binlog_table;
#刪表
mysql> drop table binlog_table;
#刪庫
mysql> drop database binlog;
恢復資料到delete之前
#查看binlog事件
mysql> show binlog events in 'mysql-bin.000013';
#使用mysqlbinlog來查看
[root@db01 data]# mysqlbinlog /application/mysql/data/mysql-bin.000013
[root@db01 data]# mysqlbinlog /application/mysql/data/mysql-bin.000013|grep -v SET
[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000013
### UPDATE `binlog`.`binlog_table`
### WHERE
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
### SET
### @1=22 /* INT meta=0 nullable=1 is_null=0 */
#分析
update binlog.binlog_table
set
@1=22 --------->@1表示binlog_table中的第一列,集合表結構就是id=22
where
@1=2 --------->@1表示binlog_table中的第一列,集合表結構就是id=2
#結果
update binlog.binlog_table set id=22 where id=2;
#截取二進制日志
查看二進制日志后，發現delete陳述句開始位置是858
[root@db01 data]# mysqlbinlog --start-position=120 --stop-position=858 /application/mysql/data/mysql-bin.000013
#臨時關閉binlog
mysql> set sql_log_bin=0;
#執行sql檔案
mysql> source /tmp/binlog.sql
#查看洗掉的庫
mysql> show databases;
#進binlog庫
mysql> use binlog
#查看洗掉的表
mysql> show tables;
#查看表中內容
mysql> select * from binlog_table;
思考，存在問題：
資料庫或表被誤洗掉的是很久之前創建的（一年前）
如果基于binlog全量恢復，成本很高
1）可以用備份恢復+短時間內二進制日志，恢復到故障之前
2）非官方方法，binlog2sql，binlog取反，類似于Oracle的flushback
3）延時從庫如果同一時間內和故障庫無關的資料庫都有操作，在截取binlog時都會被截取到
想一個辦法過濾出來？
1）grep？
其他過濾方案？
1）-d 引數接庫名
模擬資料
#為了讓大家更清晰看到新的操作
#重繪一個新的binlog
mysql> flush logs;
#創建db1、db2兩個庫
mysql> create database db1;
mysql> create database db2;
#庫db1操作
mysql> use db1
#創建t1表
mysql> create table t1(id int);
#插入5條資料
mysql> insert into t1 values(1),(2),(3),(4),(5);
#提交
mysql> commit;
#庫db2操作
mysql> use db2
#創建t2表
mysql> create table t2(id int);
#插入3條資料
mysql> insert into t2 values(1),(2),(3);
#提交
mysql> commit;
#查看binlog事件
mysql> show binlog events in 'mysql-bin.000014';
#查看db1的操作
[root@db01 data]# mysqlbinlog -d db1 --base64-output=decode-rows -vvv /application/mysql/data/mysql-bin.000014
洗掉、重繪binlog
重繪binlog日志
1）flush logs;
2）重啟資料庫時會重繪
3）二進制日志上限（max_binlog_size 1G上限）
洗掉二進制日志
1）原則
在存盤能力范圍內，能多保留則多保留
基于上一次全備前的可以選擇洗掉
洗掉方式
- 1.根據存在時間洗掉日志
#臨時生效
SET GLOBAL expire_logs_days = 7;
#永久生效
[root@db01 data]# vim /etc/my.cnf
[mysqld]
expire_logs_days = 7
- 2.使用purge命令洗掉
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
purge binary logs before now() - interval 3 day;
- 3.根據檔案名洗掉
PURGE BINARY LOGS TO 'mysql-bin.000010'; #之前的 洗掉rinary
purge binary logs to 'mysql-bin.000010';
- 4.使用reset master(洗掉所有重新開始,相當于重置)
mysql> reset master;
五.慢查詢日志
作用：
1）是將mysql服務器中影響資料庫性能的相關SQL陳述句記錄到日志檔案
2）通過對這些特殊的SQL陳述句分析，改進以達到提高資料庫性能的目的
默認位置：
$MYSQL_HOME/data/$hostname-slow.log\
開啟方式（默認沒有開啟）：
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
#指定是否開啟慢查詢日志
slow_query_log = 1
#指定慢日志檔案存放位置（默認在data）
slow_query_log_file=/application/mysql/data/slow.log
#設定慢查詢的閥值(默認10s)
long_query_time=0.05
#不使用索引的慢查詢日志是否記錄到索引
log_queries_not_using_indexes
#查詢檢查回傳少于該引數指定行的SQL不被記錄到慢查詢日志
min_examined_row_limit=100（雞肋）
注意:組態檔寫對,否則重啟不成功
模擬慢查詢陳述句
#進入world庫
mysql> use world
#查看表
mysql> show tables
#將city表中所有內容加到t1表中
mysql> create table t1 select * from city;
#查看t1的表結構
mysql> desc t1;
#將t1表所有內容插入到t1表中（多插入幾次）
mysql> insert into t1 select * from t1;
mysql> insert into t1 select * from t1;
mysql> insert into t1 select * from t1;
mysql> insert into t1 select * from t1;
#提交
mysql> commit;
#洗掉t1表中id>2000的資料
mysql> delete from t1 where id>2000;
#查看慢日志
[root@db01 ~]# cat /application/mysql/data/mysql-db01
使用mysqldumpslow命令來分析慢查詢日志
#輸出記錄次數最多的10條SQL陳述句?$PATH/mysqldumpslow -s c -t 10 /database/mysql/slow-log
引數說明:
-s:
是表示按照何種方式排序，c、t、l、r分別是按照記錄次數、時間、查詢時間、回傳的記錄數來排序，ac、at、al、ar，表示相應的倒敘；
-t:
是top n的意思，即為回傳前面多少條的資料；
-g:
后邊可以寫一個正則匹配模式，大小寫不敏感的；
例：
#得到回傳記錄集最多的10個查詢
$PATH/mysqldumpslow -s r -t 10 /database/mysql/slow-log?#得到按照時間排序的前10條里面含有左連接的查詢陳述句?$PATH/mysqldumpslow -s t -t 10 -g “left join”/database/mysql/slow-log
show process
第三方推薦（擴展）：
yum install -y percona-toolkit-3.0.11-1.el6.x86_64.rpm
使用percona公司提供的pt-query-digest工具分析慢查詢日志
[root@mysql-db01 ~]# pt-query-digest /application/mysql/data/mysql-db01-slow.log
有能力的可以做成可視化界面：
Anemometer基于pt-query-digest將MySQL慢查詢可視化
https://www.percona.com/downloads/percona-toolkit/LATEST/ 慢日志分析工具下載
https://github.com/box/Anemometer 可視化代碼下載
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/107775.html
標籤：MySQL
上一篇：MYSQL思維導圖
下一篇：MySQL第四課