系列教程
MySQL系列之開篇 MySQL關(guān)系型數(shù)據(jù)庫基礎(chǔ)概念
MySQL系列之一 MariaDB-server安裝
MySQL系列之二 多實例配置
MySQL系列之三 基礎(chǔ)篇
MySQL系列之四 SQL語法
MySQL系列之五 視圖、存儲函數(shù)、存儲過程、觸發(fā)器
MySQL系列之六 用戶與授權(quán)
MySQL系列之七 MySQL存儲引擎
MySQL系列之八 MySQL服務(wù)器變量
MySQL系列之九 mysql查詢緩存及索引
MySQL系列之十 MySQL事務(wù)隔離實現(xiàn)并發(fā)控制
MySQL系列之十一 日志記錄
MySQL系列之十二 備份與恢復(fù)
MySQL系列之十三 MySQL的復(fù)制
MySQL系列之十四 MySQL的高可用實現(xiàn)
MySQL系列之十五 MySQL常用配置和性能壓力測試
一、SQL命令歷史
~/.mysql_history
記錄了在mysql中執(zhí)行的命令歷史
二、事務(wù)日志
transaction log:事務(wù)型存儲引擎自行管理和使用
在一個事務(wù)提交后還沒有存到磁盤的情況下會記錄到事務(wù)日志,這個時候如果系統(tǒng)斷電,再次開機后會自動將已提交的事務(wù)重做(redo log)寫入磁盤;如果事務(wù)還沒有提交的情況下斷電重啟后會執(zhí)行撤銷操作(undo log)
相關(guān)變量
MariaDB [school]> SHOW VARIABLES LIKE 'innodb_log%';
+---------------------------+------------+
| Variable_name | Value |
+---------------------------+------------+
| innodb_log_block_size | 512 | -- 塊大小
| innodb_log_buffer_size | 8388608 | -- 緩存大小
| innodb_log_file_size | 1073741824 | -- 每個日志文件大小
| innodb_log_files_in_group | 3 | -- 日志組成員個數(shù),也就是有幾個文件
| innodb_log_group_home_dir | ./ | -- 事務(wù)文件路徑,相對于數(shù)據(jù)目錄
+---------------------------+------------+
innodb_log_file_size默認大小為5M,innodb_log_files_in_group個數(shù)默認2個;強烈建議調(diào)大這倆個值;
如果事務(wù)操作的數(shù)據(jù)大于事務(wù)日志文件總大小,在執(zhí)行ROLLBACK時:可以撤銷,但是數(shù)據(jù)文件會被占用,需要使用OPTIMIZE TABLE命令來釋放數(shù)據(jù)空間;
使用TRUNCATE刪除表記錄可以縮小占用空間,DELETE命令不會釋放磁盤空間,如果需要釋放需要使用OPTIMIZE命令從新整理碎片來釋放空間。
注意:修改innodb_log_file_size時需要先刪除原來的日志文件
三、錯誤日志
錯誤日志中記錄項目:
- mysqld啟動和關(guān)閉過程中輸出的事件信息
- mysqld運行中產(chǎn)生的錯誤信息
- event scheduler運行一個event時產(chǎn)生的日志信息
- 在主從復(fù)制架構(gòu)中的從服務(wù)器上啟動從服務(wù)器線程時產(chǎn)生的信息
相關(guān)變量
MariaDB [school]> SHOW GLOBAL VARIABLES LIKE 'log_error'; #錯誤日志文件的路徑
+---------------+------------------------------+
| Variable_name | Value |
+---------------+------------------------------+
| log_error | /var/log/mariadb/mariadb.log |
+---------------+------------------------------+
log_warnings=1|0
默認值1(是):是否記錄警告信息至錯誤日志文件
四、查詢?nèi)罩?/h2>
記錄用戶的操作日志,一般不建議啟用
MariaDB [school]> SHOW VARIABLES LIKE 'general_log%';
+------------------+-------------+
| Variable_name | Value |
+------------------+-------------+
| general_log | OFF | -- 開啟關(guān)閉通用日志,默認關(guān)閉
| general_log_file | centos7.log | -- 通用日志文件的文件名,/var/lib/mysql/HOSTNAME.log
+------------------+-------------+
MariaDB [school]> SHOW VARIABLES LIKE 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE | -- 以什么方式存放日志,(TABLE|FILE|NONE)
+---------------+-------+
mysql.general_log:表存放查詢?nèi)罩?,前提是以表方式存放?/p>
五、慢查詢?nèi)罩?/h2>
記錄執(zhí)行查詢時長超出指定時長的操作
1、慢查詢相關(guān)變量
MariaDB [school]> SHOW VARIABLES LIKE 'slow_query%';
+---------------------+------------------+
| Variable_name | Value |
+---------------------+------------------+
| slow_query_log | OFF | -- 開啟或關(guān)閉慢查詢?nèi)罩?
| slow_query_log_file | centos7-slow.log | -- 慢查詢?nèi)罩疚募?
+---------------------+------------------+
MariaDB [school]> SHOW VARIABLES LIKE 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 | -- 慢查詢的閥值,單位秒,查詢時間超過10則記錄到慢查詢?nèi)罩?
+-----------------+-----------+
MariaDB [school]> SHOW VARIABLES LIKE 'log_slow%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| log_slow_rate_limit | 1 | -- 多少次查詢才記錄,mariadb特有
| log_slow_verbosity | | -- 記錄內(nèi)容的詳細程度(Query_plan,explain)
+---------------------+-------+
MariaDB [school]> SHOW VARIABLES LIKE 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF | -- 記錄沒有利用索引查詢,而使用全文掃描的語句;默認關(guān)閉,建議開啟
+-------------------------------+-------+
log_slow_filter
:根據(jù)查詢結(jié)果過濾
- admin
- filesort
- filesort_on_disk
- full_join
- full_scan
- query_cache
- query_cache_miss
- tmp_table
- tmp_table_on_disk
2、補充:利用 profiling
跟蹤查詢語句所用的詳細時間
MariaDB [school]> SHOW VARIABLES LIKE 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | OFF | -- 開啟或關(guān)閉,默認關(guān)閉
+---------------+-------+
MariaDB [school]> SET profiling=ON; #開啟查詢語句跟蹤功能
MariaDB [school]> SHOW profiles; #查詢語句執(zhí)行的時間列表
+----------+------------+------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------------------------------------+
| 1 | 0.00024497 | SELECT * FROM students |
| 2 | 0.00038528 | SELECT stuid,name,age FROM students WHERE stuid = 2 |
+----------+------------+------------------------------------------------------------+
MariaDB [school]> SHOW profile FOR query 2; #查詢指定編號的SQL語句的詳細執(zhí)行過程
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000035 |
| Opening tables | 0.000003 |
| After opening tables | 0.000006 |
| query end | 0.000003 |
| closing tables | 0.000002 |
| freeing items | 0.000011 |
| updating status | 0.000006 |
| cleaning up | 0.000001 |
+----------------------+----------+
六、二進制日志
記錄已提交事務(wù)導(dǎo)致數(shù)據(jù)改變或潛在導(dǎo)致數(shù)據(jù)改變的SQL語句,通過“重放”日志文件中的事件來生成數(shù)據(jù)副本,不依賴存儲引擎類型。
開啟二進制日志,默認是關(guān)閉的,二進制日志和數(shù)據(jù)分開存放
開啟記錄二進制文件的功能:在my.cnf的[mysqld]下加入log_bin[=/path/somefile]
默認二進制日志在數(shù)據(jù)庫目錄下,mariadb-bin.000001
為二進制日志數(shù)據(jù)文件,mariadb-bin.index
為二進制日志索引文件
1、記錄二進制日志的三種方式:
- 基于語句的記錄方式,每一條改變數(shù)據(jù)的語句記錄為一條語句,節(jié)省空間,系統(tǒng)默認為此模式,但是不推薦使用,會有隱藏的風(fēng)險
- 基于行的記錄方式,每一行的改變都會將使其改變的語句記錄為一條語句,日志量會很大,但是對數(shù)據(jù)的安全保護非常高
- 混合模式:mixed, 讓系統(tǒng)自行判定該基于哪種方式進行
MariaDB [(none)]> SHOW VARIABLES LIKE 'binlog_format';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT | -- 記錄方式,系統(tǒng)默認基于語句方式
+---------------+-----------+
SET binlog_format='ROW|STATEMENT|MIXED'; -- 修改二進制日志記錄方式
2、二進制日志的相關(guān)變量
MariaDB [(none)]> SHOW MASTER|BINARY LOGS; -- 查看mariadb自行管理使用中的二進制日志文件列表
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000002 | 290 |
| mariadb-bin.000003 | 264 |
| mariadb-bin.000004 | 529038 |
| mariadb-bin.000005 | 245 |
+--------------------+-----------+
MariaDB [(none)]> SHOW MASTER STATUS; -- 查看使用中的二進制日志文件
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000005 | 245 | | |
+--------------------+----------+--------------+------------------+
MariaDB [(none)]> SHOW BINLOG EVENTS IN 'mariadb-bin.000004' FROM 1 LIMIT 2,3\G -- 查看二進制文件中的指定內(nèi)容
MariaDB [(none)]> SHOW VARIABLES LIKE 'sql_log_bin'; -- 是否記錄二進制日志,默認ON
MariaDB [(none)]> SHOW VARIABLES LIKE 'log_bin'; -- 指定文件位置;默認OFF,表示不啟用二進制日志功能,上述兩項都開啟才可
MariaDB [(none)]> SHOW VARIABLES LIKE 'max_binlog_size';
+-----------------+------------+
| Variable_name | Value |
+-----------------+------------+
| max_binlog_size | 1073741824 | -- 單個二進制日志文件的最大體積,到達最大值會自動滾動,默認為1G
+-----------------+------------+
MariaDB [(none)]> SHOW VARIABLES LIKE 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog | 0 | -- 設(shè)定是否啟動二進制日志即時同步磁盤功能,默認0,由操作系統(tǒng)負責同步日志到磁盤
+---------------+-------+
MariaDB [(none)]> SHOW VARIABLES LIKE 'expire_logs_days';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 0 | -- 二進制日志可以自動刪除的天數(shù)。 默認為0,即不自動刪除
+------------------+-------+
3、mysqlbinlog命令
- 二進制日志的客戶端命令工具
- --start-position=# 指定開始位置
- --stop-position=# 指定結(jié)束位置
- --start-datetime=(YYYY-MM-DD hh:mm:ss) 指定開始時間
- --stop-datetime=(YYYY-MM-DD hh:mm:ss) 指定結(jié)束時間
- --base64-output=decode-row
- -v |-vv |-vvv |-vvvv 顯示詳細信息
[root@centos7 mysql]# mysqlbinlog --start-position=528864 --stop-position=529019 mariadb-bin.000004 --base64-output=decode-row -v
# at 528864
#180611 20:59:46 server id 1 end_log_pos 528992 Query thread_id=29 exec_time=0 error_code=0
use `school`/*!*/;
SET TIMESTAMP=1528721986/*!*/;
INSERT students(StuID,Name,Age,Gender) VALUES (26,'Tom',22,'M') #改變數(shù)據(jù)的SQL語句
/*!*/;
事件發(fā)生的日期和時間:180611 20:59:46
事件發(fā)生的服務(wù)器標識:server id 1
事件的結(jié)束位置:end_log_pos 528992
事件的類型:Query
事件發(fā)生時所在服務(wù)器執(zhí)行此事件的線程的ID:thread_id=29
語句的時間戳與將其寫入二進制文件中的時間差:exec_time=0
錯誤代碼:error_code=0
事件內(nèi)容:
GTID:Global Transaction ID,mysql5.6以mariadb10以上版本專屬屬性:GTID
使用mysqlbinlog mariadb-bin.000001 > file.sql
命令重定向到SQL文件中,再使用mysql -uroot -p file.sql
命令可以直接導(dǎo)入數(shù)據(jù),實現(xiàn)了備份還原的功能
4、二進制日志的管理
MariaDB [(none)]> PURGE BINARY LOGS TO 'mariadb.000002'; -- 刪除到02,注意:02不刪除
MariaDB [(none)]> PURGE BINARY LOGS BEFORE '2018-01-23'; -- 刪除2018-01-23之前的日志
MariaDB [(none)]> PURGE BINARY LOGS BEFORE '2017-03-22 09:25:30';
MariaDB [(none)]> RESET MASTER; -- 刪除所有二進制日志,index文件重新記數(shù)
MariaDB [(none)]> FLUSH LOGS; -- 手動觸發(fā)日志滾動
七、中繼日志
relay log
:主從復(fù)制架構(gòu)中,從服務(wù)器用于保存從主服務(wù)器的二進制日志中讀取到的事件
總結(jié)
本篇文章就到這里了,希望可以給你帶來一些幫助,也希望您能夠多多關(guān)注腳本之家的更多內(nèi)容!
您可能感興趣的文章:- MySQL啟用慢查詢?nèi)罩居涗浄椒?/li>
- mysql 開啟慢查詢 如何打開mysql的慢查詢?nèi)罩居涗?/li>
- MySQL日志設(shè)置及查看方法
- MySQL日志文件詳解