MySQL-ログ管理
16987 ワード
1、エラーログ
1.1作用
MySQLの起動と作業中、ステータス、エラー、警告を記録します.
1.2設定方法
1>プロファイルの変更とMySQLの再起動
2>エラー・ログの表示
1.3エラー・ログの表示方法
[ERROR]の文脈に注目する.
2、バイナリログ(重要)
2.1作用
データ・リカバリに必要なログ.プライマリ・スレーブは依存するログをコピーします.
2.2設定方法
2.2.1プロファイルの変更
2.2.2ディレクトリの作成と承認
2.2.3データベースの再起動
2.3バイナリログには何が記録されていますか?
2.3.1導入
クエリー・クラスの文を除いて、すべてのデータベース変更クラスの文が記録されます.
2.3.2記録文の種類
DDL(データ定義言語):create、drop DCL(データ制御言語)DML(データ操作言語):insert、update、delete
2.3.3異なる文の記録形式説明
DDL、DCLは直接文(statement)で記録する.DML文には、SBR、RBR、MBRの3パターンがあります
面接問題:SBRとRBRの違いを説明しますか?
区別
SBR
RBR(デフォルト、推奨)
コンテンツの記録
SQL文
データ行の変更を記録
読みやすさ
強い
差
ログの量
小さい
大きい
ログの正確性
データエラー
エラーなし
2.3.4 binlog events(バイナリログイベント)
1>概要
バイナリ・ログ・コンテンツは、イベントを最小レコード・ユニットとします.DDLとDCLの場合、1つの文はイベントです.DML(標準のトランザクション文)の場合、コミットされたトランザクションのDML文のみが記録されます.
2>イベントの構成(ログを切り取るため)
2.3.4バイナリ・ログの基本表示
1>バイナリ・ログの構成情報の表示
2>バイナリ・ログの基本情報の表示(1)現在のMySQLのすべてのバイナリ・ログを印刷し、最後に使用したpositionを表示
(2)現在使用中のバイナリ・ログの表示
show binary logs; show master status;(共通)
3>バイナリ・ログのイベント情報の表示
2.4コンテンツの表示と切り取り
2.4.1コンテンツ表示コマンド
2.4.2ログの切り取り
================================データの準備
2>破壊する
3>データ復旧(1)始点と終点の確認
(2)ログの切り取り
(3)リカバリログ
2.5 gtidベースbinlog管理(拡張)
2.5.1 gtid(Global Transaction ID)とは?
グローバル一意のトランザクション番号.べき乗等化GtIDは2つの部分を含む:Server_uuid: Tx_id:
2.5.2構成
2.5.3 gtidベースのログの切り取り
DDLとDCLの操作はGTIDです.DMLの場合、完全なトランザクションはGTIDを与えたことです.
2.5.4 gtidベースのログの切り取り
2.5.5訓練
1>環境の準備
2>破壊する
3>始点と端末を探す(gtid)
4>ログの切り取り(参考のみ)
4>データの復元
2.6バイナリ・ログのその他の操作
2.6.1ログの自動クリーンアップ
2.6.2手動清掃
2.6.3 binlogのスクロール
3、スローログ(slow-log)
3.1概要
実行が遅い文を記録slowlogに記録します.機能は、最適化を支援するツールログです.ストレス性の遅い-------->show processlistでしばらく監視できる遅い-------->slow記録、統計が可能
3.2構成
3.3スロー文シミュレーション
3.4分析処理の遅い文
1.1作用
MySQLの起動と作業中、ステータス、エラー、警告を記録します.
1.2設定方法
1>プロファイルの変更とMySQLの再起動
--
vim /etc/my.cnf
log_error=/data/3306/data/mysql.log #
-- MySQL
/etc/init.d/mysqld restart
2>エラー・ログの表示
wenjuan[(none)]>select @@log_error;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 2
Current database: *** NONE ***
+---------------------------+
| @@log_error |
+---------------------------+
| /data/3306/data/mysql.log |
+---------------------------+
1 row in set (0.00 sec)
wenjuan[(none)]>
1.3エラー・ログの表示方法
[ERROR]の文脈に注目する.
2、バイナリログ(重要)
2.1作用
データ・リカバリに必要なログ.プライマリ・スレーブは依存するログをコピーします.
2.2設定方法
2.2.1プロファイルの変更
vim /etc/my.server_id
server_id=6
log_bin=/data/3306/binlog/mysql-bin
：
server_id 5.7
log_bin= /data/3306/binlog/ mysql-bin
2.2.2ディレクトリの作成と承認
[root@db01 /data/3306]# mkdir -p /data/3306/binlog/
[root@db01 /data/3306]# chown -R mysql.mysql /data/3306/*
2.2.3データベースの再起動
[root@db01 /data/3306]# /etc/init.d/mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS!
[root@db01 /data/3306]# ll binlog/
total 8
-rw-r----- 1 mysql mysql 768 Aug 14 20:02 mysql-bin.000001
-rw-r----- 1 mysql mysql 35 Aug 14 18:18 mysql-bin.index
[root@db01 /data/3306]#
：
mysql-bin
000001 MySQL ，
2.3バイナリログには何が記録されていますか?
2.3.1導入
クエリー・クラスの文を除いて、すべてのデータベース変更クラスの文が記録されます.
2.3.2記録文の種類
DDL(データ定義言語):create、drop DCL(データ制御言語)DML(データ操作言語):insert、update、delete
2.3.3異なる文の記録形式説明
DDL、DCLは直接文(statement)で記録する.DML文には、SBR、RBR、MBRの3パターンがあります
wenjuan[(none)]>select @@binlog_format;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 2
Current database: *** NONE ***
+-----------------+
| @@binlog_format |
+-----------------+
| ROW |
+-----------------+
1 row in set (0.00 sec)
wenjuan[(none)]>
：
statement---->SBR： ， SQL
row---------->RBR： （ ， ）
mixed-------->MBR：
面接問題:SBRとRBRの違いを説明しますか?
区別
SBR
RBR(デフォルト、推奨)
コンテンツの記録
SQL文
データ行の変更を記録
読みやすさ
強い
差
ログの量
小さい
大きい
ログの正確性
データエラー
エラーなし
2.3.4 binlog events(バイナリログイベント)
1>概要
バイナリ・ログ・コンテンツは、イベントを最小レコード・ユニットとします.DDLとDCLの場合、1つの文はイベントです.DML(標準のトランザクション文)の場合、コミットされたトランザクションのDML文のみが記録されます.
begin ; 1
a 2
b 3
commit; 4
2>イベントの構成(ログを切り取るため)
[root@db01 /data/3306/binlog]# mysqlbinlog mysql-bin.000001
# at 219 (position)
end_log_pos 319 (position)
#190814 18:46:35
create database oldboy
2.3.4バイナリ・ログの基本表示
1>バイナリ・ログの構成情報の表示
wenjuan[(none)]>show variables like '%log_bin%';
+---------------------------------+-----------------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------------+
| log_bin | ON |
| log_bin_basename | /data/3306/binlog/mysql-bin |
| log_bin_index | /data/3306/binlog/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)
wenjuan[(none)]>
：
log_bin
log_bin_basename
sql_log_bin
2>バイナリ・ログの基本情報の表示(1)現在のMySQLのすべてのバイナリ・ログを印刷し、最後に使用したpositionを表示
-----
wenjuan[(none)]>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)
wenjuan[(none)]>
(2)現在使用中のバイナリ・ログの表示
show binary logs; show master status;(共通)
wenjuan[(none)]>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)
wenjuan[(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
wenjuan[(none)]>
3>バイナリ・ログのイベント情報の表示
wenjuan[(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
wenjuan[(none)]>
wenjuan[(none)]>show binlog events in 'mysql-bin.000001';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids | 6 | 154 | |
| mysql-bin.000001 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 219 | Query | 6 | 322 | create database wwjtest |
| mysql-bin.000001 | 322 | Anonymous_Gtid | 6 | 387 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 387 | Query | 6 | 481 | create database heee |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
6 rows in set (0.00 sec)
2.4コンテンツの表示と切り取り
2.4.1コンテンツ表示コマンド
[root@db01 ~]# mysqlbinlog /data/3306/binlog/mysql-bin.000003
[root@db01 ~]# mysqlbinlog --base64-output=decode-rows -vvv /data/3306/binlog/mysql-bin.000003
： mysqlbinlog --help
2.4.2ログの切り取り
--start-position
--stop-position
:
mysqlbinlog --start-position=xxx --stop-position=xxx /data/3306/binlog/mysql-bin.000003>/data/bin.sql
================================データの準備
wenjuan[(none)]>create database binlog charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
wenjuan[(none)]>use binlog;
Database changed
wenjuan[binlog]>create table t1(id int) engine=innodb charset=utf8mb4;
Query OK, 0 rows affected (0.02 sec)
wenjuan[binlog]>
wenjuan[binlog]>insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
wenjuan[binlog]>insert into t1 values(11),(12),(13);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
wenjuan[binlog]>commit;
Query OK, 0 rows affected (0.01 sec)
wenjuan[binlog]>
wenjuan[binlog]>update t1 set id=10 where id>10;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
wenjuan[binlog]>commit;
Query OK, 0 rows affected (0.01 sec)
wenjuan[binlog]>
wenjuan[binlog]>select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 10 |
| 10 |
| 10 |
+------+
6 rows in set (0.00 sec)
wenjuan[binlog]>
2>破壊する
wenjuan[binlog]>drop database binlog;
Query OK, 1 row affected (0.00 sec)
wenjuan[(none)]>
3>データ復旧(1)始点と終点の確認
wenjuan[(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 1610 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
wenjuan[(none)]>
wenjuan[(none)]>show binlog events in 'mysql-bin.000003';
：
| mysql-bin.000003 | 488 | Query | 6 | 604 | create database binlog charset utf8mb4 |
：
| mysql-bin.000003 | 1512 | Query | 6 | 1610 | drop database binlog |
(2)ログの切り取り
[root@db01 ~]# mysqlbinlog --start-position=488 --stop-position=1512 /data/3306/binlog/mysql-bin.000003>/data/bin.sql
(3)リカバリログ
wenjuan[(none)]>set sql_log_bin=0; ## binlog
wenjuan[(none)]>source /data/bin.sql;
wenjuan[(none)]>set sql_log_bin=1; ## binlog
2.5 gtidベースbinlog管理(拡張)
2.5.1 gtid(Global Transaction ID)とは?
グローバル一意のトランザクション番号.べき乗等化GtIDは2つの部分を含む:Server_uuid: Tx_id:
2.5.2構成
wenjuan[(none)]>show variables like '%gtid%';
vim /etc/init.d/my.cnf
gtid_mode=on ----
enforce_gtid_consistency=true ---- GTID
log_slave_updates=1 ---- binlog， GTID
：/etc/init.d/mysqld restart
2.5.3 gtidベースのログの切り取り
DDLとDCLの操作はGTIDです.DMLの場合、完全なトランザクションはGTIDを与えたことです.
wenjuan[(none)]>wenjuan[(none)]>show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000005 | 489 | | | 936b9a3f-b75a-11e9-bd16-000c290143b9:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
wenjuan[(none)]>
wenjuan[(none)]>wenjuan[(none)]>show binlog events in 'mysql-bin.000005';
+------------------+-----+----------------+-----------+-------------+-------------------------
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info
+------------------+-----+----------------+-----------+-------------+-------------------------
| mysql-bin.000005 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.26-log,
| mysql-bin.000005 | 123 | Previous_gtids | 6 | 154 |
| mysql-bin.000005 | 154 | Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT=
| mysql-bin.000005 | 219 | Query | 6 | 308 | drop database dbtest
| mysql-bin.000005 | 308 | Gtid | 6 | 373 | SET @@SESSION.GTID_NEXT=
| mysql-bin.000005 | 373 | Query | 6 | 489 | create database dbtest c
+------------------+-----+----------------+-----------+-------------+-------------------------
6 rows in set (0.00 sec)
wenjuan[(none)]>
2.5.4 gtidベースのログの切り取り
--include-gtids= ----
--exclude-gtids= ------
--skip-gtids
1-3 :
[root@db01 ~]# mysqlbinlog --include-gtids='545fd699-be48-11e9-8f0a-000c2980e248:1-3' /data/binlog/mysql-bin.000009>/data/gtid.sql
1-10 gtid , 6 8 .
[root@db01 ~]# mysqlbinlog --include-gtids='545fd699-be48-11e9-8f0a-000c2980e248:1-10 --exclude-gtids='545fd699-be48-11e9-8f0a-000c2980e248:6,545fd699-be48-11e9-8f0a-000c2980e248:8' /data/binlog/mysql-bin.000009>/data/gtid.sql
2.5.5訓練
1>環境の準備
wenjuan[(none)]>create database gtid charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
wenjuan[(none)]>use gtid;
Database changed
wenjuan[gtid]>create table t1(id int) engine=innodb charset=utf8mb4;
Query OK, 0 rows affected (0.02 sec)
wenjuan[gtid]>insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0
wenjuan[gtid]>commit;
Query OK, 0 rows affected (0.00 sec)
wenjuan[gtid]>insert into t1 values(11),(12),(13);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
wenjuan[gtid]>commit;
Query OK, 0 rows affected (0.00 sec)
wenjuan[gtid]>select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 11 |
| 12 |
| 13 |
+------+
6 rows in set (0.00 sec)
wenjuan[gtid]>
2>破壊する
wenjuan[gtid]>drop database gtid;
Query OK, 1 row affected (0.01 sec)
wenjuan[(none)]>
3>始点と端末を探す(gtid)
wenjuan[(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000008 | 1244 | | | 936b9a3f-b75a-11e9-bd16-000c290143b9:1-11 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
wenjuan[(none)]>show binlog events in 'mysql-bin.000008';
| mysql-bin.000008 | 194 | Gtid | 6 | 259 | SET @@SESSION.GTID_NEXT= '936b9a3f-b75a-11e9-bd16-000c290143b9:7' |
| mysql-bin.000008 | 259 | Query | 6 | 369 | create database gtid charset utf8mb4 |
| mysql-bin.000008 | 1087 | Gtid | 6 | 1152 | SET @@SESSION.GTID_NEXT= '936b9a3f-b75a-11e9-bd16-000c290143b9:11' |
| mysql-bin.000008 | 1152 | Query | 6 | 1244 | drop database gtid |
4>ログの切り取り(参考のみ)
[root@db01 ~]# mysqlbinlog --skip-gtids --include-gtids='936b9a3f-b75a-11e9-bd16-000c290143b9:7-10' /data/3306/binlog/mysql-bin.000008>/data/gtid.sql
4>データの復元
wenjuan[(none)]>set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
wenjuan[(none)]>
wenjuan[(none)]>source /data/gtid.sql
2.6バイナリ・ログのその他の操作
2.6.1ログの自動クリーンアップ
show variables like '%expire%';
expire_logs_days 0
, +1
set global expire_logs_days=8;
:
my.cnf
expire_logs_days=15;
, +1 binlog
2.6.2手動清掃
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
PURGE BINARY LOGS TO 'mysql-bin.000009';
: rm binlog
\1. my.cnf binlog ,
2. , binlog,
binlog, 000001
*reset master; ， ，
2.6.3 binlogのスクロール
wenjuan[(none)]>flush logs;
select @@max_binlog_size;
，
3、スローログ(slow-log)
3.1概要
実行が遅い文を記録slowlogに記録します.機能は、最適化を支援するツールログです.ストレス性の遅い-------->show processlistでしばらく監視できる遅い-------->slow記録、統計が可能
3.2構成
wenjuan[(none)]>show variables like '%slow_query%';
wenjuan[(none)]>select @@long_query_time;
wenjuan[(none)]>show variables like '%log_queries_not_using_indexes%';
vim /etc/my.cnf
slow_query_log=1
slow_query_log_file=/data/3306/data/db01-slow.log
long_query_time=0.1 10
log_queries_not_using_indexes=1
mysql：
[root@db01 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS!
[root@db01 ~]#
3.3スロー文シミュレーション
set sql_log_bin=0;
source /tmp/t100w.sql;
set sql_log_bin=1;
3.4分析処理の遅い文
[root@db01 ~]# mysqldumpslow -s c -t 5 /data/3306/data/db01-slow.log
：
-t top
-s order