錯(cuò)誤日志(log_error)
--記錄啟動(dòng)關(guān)閉日常運(yùn)行過程中,狀態(tài)信息,警告,錯(cuò)誤
默認(rèn)就是開啟的: /數(shù)據(jù)路徑下/hostname.err 手工設(shè)定: Master [(none)]>select @@log_error; vim /etc/my.cnf log_error=/var/log/MySQL.log log_timestamps=system 重啟生效 show variables like 'log_error';
默認(rèn)位置：
DATADIR/hostname.err
配置方式
vim .etc/my.cnf
log_error=/data/mysql/data/mysql.log
查看：
mysql> select @@log_error;
+----------------------+
| @@log_error |
+----------------------+
| /data/3307/mysql.log |
+----------------------+
1 row in set (0.00 sec)
binlog(binary logs):二進(jìn)制日志 *****
(1)備份恢復(fù)必須依賴二進(jìn)制日志
(2)主從環(huán)境必須依賴二進(jìn)制日志
binlog配置 (5.7必須加server_id)
注意：MySQL默認(rèn)是沒有開啟二進(jìn)制日志的。 基礎(chǔ)參數(shù)查看: 開關(guān): [(none)]>select @@log_bin; 日志路徑及名字 [(none)]>select @@log_bin_basename; 服務(wù)ID號(hào): [(none)]>select @@server_id; 二進(jìn)制日志格式: [(none)]>select @@binlog_format; 雙一標(biāo)準(zhǔn)之二: [(none)]>select @@sync_binlog;
log_error=
/data/mysql/data/mysql.log
log_bin=
/data/binlog/3306/mysql-bin #/data/binlog/3306/ ：目錄 mysql-bin：文件名
[root@localhost 3306]# pwd
/data/binlog/3306
[root@localhost 3306]# ll
總用量 8
-rw-r----- 1 mysql mysql 154 9月 4 13:51 mysql-bin.000001
-rw-r----- 1 mysql mysql 35 9月 4 13:51 mysql-bin.index
查看配置
mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------------+
| log_bin | ON |
| log_bin_basename | /data/binlog/3306/mysql-bin |
| log_bin_index | /data/binlog/3306/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-----------------------------------+
6 rows in set (0.00 sec)
binlog記錄了什么?
-----binlog是SQL層的功能。記錄的是變更SQL語句，不記錄查詢語句。
記錄SQL語句種類
DDL ：原封不動(dòng)的記錄當(dāng)前DDL(statement語句方式)。
DCL ：原封不動(dòng)的記錄當(dāng)前DCL(statement語句方式)。
DML ：只記錄已經(jīng)提交的事務(wù)DML
DML三種記錄方式binlog_format（binlog的記錄格式）參數(shù)影響
（1）statement（5.6默認(rèn)）SBR(statement based replication) ：語句模式原封不動(dòng)的記錄當(dāng)前DML。
（2）ROW(5.7 默認(rèn)值)RBR(ROW based replication) ：記錄數(shù)據(jù)行的變化(用戶看不懂，需要工具分析)
（3）mixed（混合）MBR(mixed based replication)模式 ：以上兩種模式的混合
mysql> show variables like 'bnlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)
面試題
SBR與RBR模式的對(duì)比
STATEMENT：可讀性較高，日志量少，但是不夠嚴(yán)謹(jǐn)
ROW ：可讀性很低，日志量大，足夠嚴(yán)謹(jǐn)
update t1 set xxx=xxx where id>1000 ? -->一共500w行，row模式怎么記錄的日志
為什么row模式嚴(yán)謹(jǐn)？
id name intime
insert into t1 values(1,'zs',now())
我們建議使用：row記錄模式
event（事件）是什么?
事件的簡介
二進(jìn)制日志的最小記錄單元
對(duì)于DDL,DCL,一個(gè)語句就是一個(gè)event
對(duì)于DML語句來講:只記錄已提交的事務(wù)。
例如以下列子,就被分為了4個(gè)event
begin; 120 - 340
DML1 340 - 460
DML2 460 - 550
commit; 550 - 760
event的組成
三部分構(gòu)成:
(1) 事件的開始標(biāo)識(shí)
(2) 事件內(nèi)容
(3) 事件的結(jié)束標(biāo)識(shí)
Position:
開始標(biāo)識(shí): at 194
結(jié)束標(biāo)識(shí): end_log_pos 254
194? 254?
某個(gè)事件在binlog中的相對(duì)位置號(hào)
位置號(hào)的作用是什么？
為了方便我們截取事件
查看一共多少個(gè)binlog
Master [(none)]>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.01 sec)
Master [(none)]>flush logs;
Query OK, 0 rows affected (0.03 sec)
Master [(none)]>flush logs;
Query OK, 0 rows affected (0.01 sec)
Master [(none)]>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 201 |
| mysql-bin.000002 | 201 |
| mysql-bin.000003 | 154 |
+------------------+-----------+
3 rows in set (0.00 sec)
Master [(none)]>
通過執(zhí)行flush logs命令之后，再次查詢binary log信息，發(fā)現(xiàn)已經(jīng)使用了一個(gè)新的bin log文件了。
flush logs命令的作用就是關(guān)閉當(dāng)前使用的binary log，然后打開一個(gè)新的binary log文件，binlog文件的序號(hào)遞增加1
查看mysql正在使用的日志文件
Master [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
Master [(none)]>
file：當(dāng)前MySQL正在使用的文件名
Position：最后一個(gè)事件的結(jié)束位置號(hào)
日志內(nèi)容查看
event查看
Master [binlog]>show binlog events in 'mysql-bin.000003';
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
| mysql-bin.000003 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| mysql-bin.000003 | 123 | Previous_gtids | 6 | 154 | |
| mysql-bin.000003 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 219 | Query | 6 | 319 | create database binlog |
| mysql-bin.000003 | 319 | Anonymous_Gtid | 6 | 384 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 384 | Query | 6 | 486 | use `binlog`; create table t1 (id int) |
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
Log_name：binlog文件名
Pos：開始的position *****
Event_type：事件類型
Format_desc：格式描述，每一個(gè)日志文件的第一個(gè)事件，多用戶沒有意義，MySQL識(shí)別binlog必要信息
Server_id：mysql服務(wù)號(hào)標(biāo)識(shí)
End_log_pos：事件的結(jié)束位置號(hào) *****
Info：事件內(nèi)容*****
補(bǔ)充:
SHOW BINLOG EVENTS
[IN 'log_name']
[FROM pos]
[LIMIT [offset,] row_count]
[root@db01 binlog]# mysql -e "show binlog events in 'mysql-bin.000004'" |grep drop
binlog文件內(nèi)容詳細(xì)查看
mysqlbinlog /data/mysql/mysql-bin.000006
mysqlbinlog --base64-output=decode-rows -vvv /data/binlog/mysql-bin.000003
mysqlbinlog -d binlog /data/binlog/mysql-bin.000003
[root@db01 binlog]# mysqlbinlog --start-datetime='2019-05-06 17:00:00' --stop-datetime='2019-05-06 17:01:00' /data/binlog/mysql-bin.000004
基于Position號(hào)進(jìn)行日志截取
核心就是找截取的起點(diǎn)和終點(diǎn)
--start-position=321
--stop-position=513
mysqlbinlog --start-position=219 --stop-position=1347 /data/binlog/mysql-bin.000003 >/tmp/bin.sql
案例: 使用binlog日志進(jìn)行數(shù)據(jù)恢復(fù)
模擬:
1.
[(none)]>create database binlog charset utf8;
2.
[(none)]>use binlog;
[binlog]>create table t1(id int);
3.
[binlog]>insert into t1 values(1);
[binlog]>commit;
[binlog]>insert into t1 values(2);
[binlog]>commit;
[binlog]>insert into t1 values(3);
[binlog]>commit;
4.
[binlog]>drop database binlog;
恢復(fù):
[(none)]>show master status ;
[(none)]>show binlog events in 'mysql-bin.000004';
[root@db01 binlog]# mysqlbinlog --start-position=1227 --stop-position=2342 /data/binlog/mysql-bin.000004 >/tmp/bin.sql
[(none)]>set sql_Log_bin=0;#設(shè)為0后，在Master數(shù)據(jù)庫上執(zhí)行的語句臨時(shí)都不記錄binlog，退出窗口可以恢復(fù)
[(none)]>source /tmp/bin.sql
-----上述通過pos位置恢復(fù) 需要關(guān)閉gitd----
經(jīng)過測試發(fā)現(xiàn) 開起來gitd，通過pos位置無法恢復(fù)數(shù)據(jù)
mysqlbinlog --start-position=219 --stop-position=1012 /data/binlog/3306/mysql-bin.000002 >/tmp/bin.sql
binlog日志的GTID新特性
GTID 介紹
5.6 版本新加的特性,5.7中做了加強(qiáng)
5.6 中不開啟,沒有這個(gè)功能.
5.7 中的GTID,即使不開也會(huì)有自動(dòng)生成
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
GTID(Global Transaction ID)
是對(duì)于一個(gè)已提交事務(wù)的編號(hào)，并且是一個(gè)全局唯一的編號(hào)。
它的官方定義如下：GTID = source_id ：transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:29
重要參數(shù)介紹：
vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true
systemctl restart mysqld
Master [(none)]>create database gtid charset utf8;
Query OK, 1 row affected (0.01 sec)
Master [(none)]>show master status ;
+------------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000004 | 326 | | | dff98809-55c3-11e9-a58b-000c2928f5dd:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)
Master [(none)]>use gtid
Database changed
Master [gtid]>create table t1 (id int);
Query OK, 0 rows affected (0.01 sec)
Master [gtid]>show master status ;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000004 | 489 | | | dff98809-55c3-11e9-a58b-000c2928f5dd:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
Master [gtid]>create table t2 (id int);
Query OK, 0 rows affected (0.01 sec)
Master [gtid]>create table t3 (id int);
Query OK, 0 rows affected (0.02 sec)
Master [gtid]>show master status ;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000004 | 815 | | | dff98809-55c3-11e9-a58b-000c2928f5dd:1-4 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
Master [gtid]>begin;
Query OK, 0 rows affected (0.00 sec)
Master [gtid]>insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)
Master [gtid]>commit;
Query OK, 0 rows affected (0.00 sec)
Master [gtid]>show master status ;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000004 | 1068 | | | dff98809-55c3-11e9-a58b-000c2928f5dd:1-5 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
Master [gtid]>begin;
Query OK, 0 rows affected (0.00 sec)
Master [gtid]>insert into t2 values(1);
Query OK, 1 row affected (0.00 sec)
Master [gtid]>commit;
Query OK, 0 rows affected (0.01 sec)
Master [gtid]>show master status ;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000004 | 1321 | | | dff98809-55c3-11e9-a58b-000c2928f5dd:1-6 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
基于GTID進(jìn)行查看binlog
具備GTID后,截取查看某些事務(wù)日志:
--include-gtids
--exclude-gtids
mysqlbinlog --include-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:1-6' --exclude-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:4' /data/binlog/mysql-bin.000004
跳過多個(gè)事務(wù)id
mysqlbinlog --include-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:1-6' --exclude-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:4,dff98809-55c3-11e9-a58b-000c2928f5dd:5' /data/binlog/mysql-bin.000004
GTID的冪等性
開啟GTID后,MySQL恢復(fù)Binlog時(shí),重復(fù)GTID的事務(wù)不會(huì)再執(zhí)行了
就想恢復(fù)?怎么辦?
--skip-gtids
mysqlbinlog --skip-gtids --include-gtids='3ca79ab5-3e4d-11e9-a709-000c293b577e:4' /data/binlog/mysql-bin.000004 > /tmp/binlog.sql
set sql_log_bin=0;
source /tmp/binlog.sql
set sql_log_bin=1;
使用二進(jìn)制日志恢復(fù)數(shù)據(jù)案例
故障環(huán)境介紹
創(chuàng)建了一個(gè)庫 db, 導(dǎo)入了表t1 ,t1表中錄入了很多數(shù)據(jù)
一個(gè)開發(fā)人員,drop database db;
沒有備份,日志都在.怎么恢復(fù)?
思路:找到建庫語句到刪庫之前所有的日志,進(jìn)行恢復(fù).(開啟了GTID模式)
故障案例模擬:
(0) drop database if exists db ;
(1) create database db charset utf8;
(2) use db;
(3) create table t1 (id int);
(4) insert into t1 values(1),(2),(3);
(5) insert into t1 values(4),(5),(6);
(6) commit
(7) update t1 set id=30 where id=3;
(8) commit;
(9) delete from t1 where id=4;
(10)commit;
(11)insert into t1 values(7),(8),(9);
(12)commit;
(13)drop database db;
========================
drop database if exists db ;
create database db charset utf8;
use db;
create table t1 (id int);
insert into t1 values(1),(2),(3);
insert into t1 values(4),(5),(6);
commit;
update t1 set id=30 where id=3;
commit;
delete from t1 where id=4;
commit;
insert into t1 values(7),(8),(9);
commit;
drop database db;
=======
運(yùn)行以上語句，模擬故障場景
需求：將數(shù)據(jù)庫恢復(fù)到以下狀態(tài)（提示第9步和第13步是誤操作，其他都是正常操作）
恢復(fù)過程(無GTID時(shí)的恢復(fù))
1，查看當(dāng)前使用的 binlog文件
oldguo [db]>show master status ;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 | 1873 | | | |
+------------------+----------+--------------+------------------+-------------------+
2.查看事件：
第一段：
| mysql-bin.000006 | 813 | Query | 1 | 907 | use `db`; create table t1 (id int) |
| mysql-bin.000006 | 907 | Query | 1 | 977 | BEGIN |
| mysql-bin.000006 | 977 | Table_map | 1 | 1020 | table_id: 77 (db.t1) |
| mysql-bin.000006 | 1020 | Write_rows | 1 | 1070 | table_id: 77 flags: STMT_END_F |
| mysql-bin.000006 | 1070 | Table_map | 1 | 1113 | table_id: 77 (db.t1) |
| mysql-bin.000006 | 1113 | Write_rows | 1 | 1163 | table_id: 77 flags: STMT_END_F |
| mysql-bin.000006 | 1163 | Xid | 1 | 1194 | COMMIT /* xid=74 */ |
| mysql-bin.000006 | 1194 | Query | 1 | 1264 | BEGIN |
| mysql-bin.000006 | 1264 | Table_map | 1 | 1307 | table_id: 77 (db.t1) |
| mysql-bin.000006 | 1307 | Update_rows | 1 | 1353 | table_id: 77 flags: STMT_END_F |
| mysql-bin.000006 | 1353 | Xid | 1 | 1384 | COMMIT /* xid=77 */
mysqlbinlog --start-position=813 --stop-position=1384 /data/mysql/mysql-bin.000006 >/tmp/bin1.sql
第二段：
| mysql-bin.000006 | 1568 | Query | 1 | 1638 | BEGIN |
| mysql-bin.000006 | 1638 | Table_map | 1 | 1681 | table_id: 77 (db.t1) |
| mysql-bin.000006 | 1681 | Write_rows | 1 | 1731 | table_id: 77 flags: STMT_END_F |
| mysql-bin.000006 | 1731 | Xid | 1 | 1762 | COMMIT /* xid=81 */
mysqlbinlog --start-position=1568 --stop-position=1762 /data/mysql/mysql-bin.000006 >/tmp/bin2.sql
3，恢復(fù)
set sql_log_bin=0;
source /tmp/bin1.sql
source /tmp/bin2.sql
set sql_log_bin=1;
oldguo [db]>select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 30 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
有GTID的恢復(fù):
mysqlbinlog --skip-gtids --include-gtids='3ca79ab5-3e4d-11e9-a709-000c293b577e:7-12' mysql-bin.000004> /tmp/bin.sql
恢復(fù)
set sql_log_bin=0;
source /tmp/bin.sql
二進(jìn)制日志其他操作
自動(dòng)清理日志
show variables like '%expire%';
expire_logs_days 0
自動(dòng)清理時(shí)間,是要按照全備周期+1
set global expire_logs_days=8;
永久生效:修改配置文件
vim my.cnf
expire_logs_days=15;
企業(yè)建議,至少保留兩個(gè)全備周期+1的binlog
手工清理（危險(xiǎn)）
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
PURGE BINARY LOGS TO 'mysql-bin.000010';
注意:不要手工 rm binlog文件
1. my.cnf binlog關(guān)閉掉,啟動(dòng)數(shù)據(jù)庫
2.把數(shù)據(jù)庫關(guān)閉,開啟binlog,啟動(dòng)數(shù)據(jù)庫
刪除所有binlog,并從000001開始重新記錄日志
reset master; 主從關(guān)系中，主庫執(zhí)行此操作，主從環(huán)境必崩
日志是怎么滾動(dòng)
flush logs;
重啟mysql也會(huì)自動(dòng)滾動(dòng)一個(gè)新的
日志文件達(dá)到1G大小(max_binlog_size)
| max_binlog_size | 1073741824
備份時(shí),加入?yún)?shù)也可以自動(dòng)滾動(dòng)
slow_log 慢日志
記錄慢SQL語句的日志,定位低效SQL語句的工具日志
mysql> show variables like '%slow%';
+---------------------------+-------------------------------------+
| Variable_name | Value |
+---------------------------+-------------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /data/mysql/data/localhost-slow.log |
+---------------------------+-------------------------------------+
5 rows in set (0.00 sec)
開啟慢日志(默認(rèn)沒開啟)
開關(guān):
slow_query_log=1
文件位置及名字
slow_query_log_file=/data/mysql/slow.log
設(shè)定慢查詢時(shí)間:
long_query_time=0.1
沒走索引的語句也記錄:
log_queries_not_using_indexes #不走索引
vim /etc/my.cnf
slow_query_log=1
slow_query_log_file=/data/mysql/slow.log
long_query_time=0.1
log_queries_not_using_indexes
systemctl restart mysqld
mysqldumpslow 分析慢日志
mysqldumpslow -s c -t 10 /data/mysql/slow.log
# 第三方工具(自己擴(kuò)展)
https://www.percona.com/downloads/percona-toolkit/LATEST/
yum install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-Digest-MD5
toolkit工具包中的命令:
./pt-query-diagest /data/mysql/slow.log
Anemometer基于pt-query-digest將MySQL慢查詢可視化
常用的參數(shù)
-s：是表示按照何種方式排序
|
參數(shù)值 |
說明 |
|
c |
訪問計(jì)數(shù) |
|
l |
鎖定時(shí)間 |
|
r |
返回記錄 |
|
t |
查詢時(shí)間 |
|
al |
平均鎖定時(shí)間 |
|
ar |
平均返回記錄數(shù) |
|
at |
平均查詢時(shí)間 |
-t：是top n的意思，即為返回前面多少條的數(shù)據(jù)
-g：后邊可以寫一個(gè)正則匹配模式，大小寫不敏感的
Mysql慢查詢?nèi)罩痉治黾皃ercona-toolkit的使用
相關(guān)文獻(xiàn)：
https://blog.csdn.NET/qq_41600067/article/details/112952251
https://naibawu.com/1250.html
https://www.cnblogs.com/yaomaomao/p/12672283.html
本文來自博客園，作者：孫龍-程序員，轉(zhuǎn)載請(qǐng)注明原文鏈接：
https://www.cnblogs.com/sunlong88/p/16655005.html
