-
MySQL日志及执行计划
MySQL日志和监控
1 Binlog日志
1.1 Binlog日志说明
Binlog日志包含描述数据库修改的语句，如create table、update等数据变更的语句，不会记录类似select、show等不修改数据的语句 。
Binlog日志的作用主要有以下2个：- 复制：主库的变更先写入Binlog日志，然后传到从库进行回放。
- 恢复：当误操作后，可以先把全量备份的Binlog日志导入到某个新的实例中，然后通过全量备份时间点到误操作中间的Binlog日志解析出所有事务，并在新实例中执行这些事务，达到恢复到误操作前的状态。
1.2 Binlog日志操作
1.2.1 开启Binlog日志功能
如果要开启Binlog日志，就需要在mysql配置文件的[mysqld]模块中加上如下语句，该配置表示Binlog日志的存放路径为/data/mysql/logs/，文件名为mysql-bin后接Binlog日志的序列号
log-bin="/data/mysql/logs/mysql-bin"- 1
1.2.2 关闭Binlog日志功能
如果要关闭Binlog日志，就需要在mysql配置文件中加上如下语句
skip_log_bin- 1
或者
disable_log_bin- 1
如果只是想关闭当前会话的Binlog日志，则可以通过以下命令动态的修改
set sql_log_bin = 'OFF';- 1
1.2.3 设置Binlog日志格式
Binlog日志可以设置为以下3种格式。
日志格式 说明 优点 缺点 statement 记录的是SQL语句，不需要 记录每行数据的变化 日志量少，节约IO，性能高 在主从复制中可能会导致主从数据不一致，如果使用了不确定 函数，类似UUID()函数等 row 记录的是每行数据修改前后的值 主从数据基本一致，支持闪回 日志量多 mixed 以上两种格式的混合 日志量少，节约IO，性能高，解决了statement格式部分数据不一致的情况 不支持闪回，部分高可用架构 不支持该格式，不方便将数据同步到其他类型的数据库 如果要设置Binlog日志格式，可以在mysql配置文件的[mysqld]模块中加上如下语句
binlog_format=row- 1
除了在配置文件中配置外，还可以通过以下命令动态的修改Binlog日志格式
-- 全局级别修改 set global binlog_format = 'row'; -- 会话级别修改 set session binlog_format = 'statement';- 1
- 2
- 3
- 4
- 5
1.2.4 列表Binlog日志文件
通过以下命令可以查看MySQL实例当前记录了哪些Binlog日志文件
show binary logs;- 1
命令示例：
以下命令示例显示，当前只有一个名称为mysql-bin.000001的Binlog日志文件，且最新的位点为988，并且该Binlog日志文件未加密。mysql> show binary logs; +------------------+-----------+-----------+ | Log_name | File_size | Encrypted | +------------------+-----------+-----------+ | mysql-bin.000001 | 988 | No | +------------------+-----------+-----------+ 1 row in set (0.00 sec) mysql>- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
1.2.5 查看Binlog日志事件
查看Binlog日志事件之前，需要先通过以下命令查看当前Binlog日志的位点
show master status\G;- 1
然后再通过以下命令查看从某个具体位点之后的Binlog日志事件
-- 从mysql-bin.000001文件中查看792位点开始的5条Binlog日志事件 show binlog events in 'mysql-bin.000001' from 792 limit 5\G;- 1
- 2
命令示例：
以下命令示例中，首先查看到当前Binlog日志的位点为792，然后创建了一张名为t_test的数据表，然后再查到当前Binlog日志的位点为988，最后再查看Binlog日志文件mysql-bin.000001中从位点792开始的所有的记录。mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 792 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> mysql> create table t_test(id int); Query OK, 0 rows affected (0.01 sec) mysql> mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 988 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> mysql> show binlog events in 'mysql-bin.000001' from 792\G; *************************** 1. row *************************** Log_name: mysql-bin.000001 Pos: 792 Event_type: Anonymous_Gtid Server_id: 1 End_log_pos: 869 Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS' *************************** 2. row *************************** Log_name: mysql-bin.000001 Pos: 869 Event_type: Query Server_id: 1 End_log_pos: 988 Info: use `member`; create table t_test(id int) /* xid=47 */ 2 rows in set (0.00 sec) mysql>- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
命令示例：
以下命令示例中，首先查看到当前Binlog日志的位点为1389，然后往t_member表中插入一行数据，然后再查到当前Binlog日志的位点为1740，最后再查看Binlog日志文件mysql-bin.000001中从位点1389开始的所有的记录。mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 1389 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> insert into `t_member` values (1, '202209040001', '李小明', '18934528794', '23423423@qq.com'); Query OK, 1 row affected (0.00 sec) mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 1740 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> show binlog events in 'mysql-bin.000001' from 1389\G; *************************** 1. row *************************** Log_name: mysql-bin.000001 Pos: 1389 Event_type: Anonymous_Gtid Server_id: 1 End_log_pos: 1468 Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS' *************************** 2. row *************************** Log_name: mysql-bin.000001 Pos: 1468 Event_type: Query Server_id: 1 End_log_pos: 1545 Info: BEGIN *************************** 3. row *************************** Log_name: mysql-bin.000001 Pos: 1545 Event_type: Table_map Server_id: 1 End_log_pos: 1618 Info: table_id: 93 (member.t_member) *************************** 4. row *************************** Log_name: mysql-bin.000001 Pos: 1618 Event_type: Write_rows Server_id: 1 End_log_pos: 1709 Info: table_id: 93 flags: STMT_END_F *************************** 5. row *************************** Log_name: mysql-bin.000001 Pos: 1709 Event_type: Xid Server_id: 1 End_log_pos: 1740 Info: COMMIT /* xid=100 */ 5 rows in set (0.00 sec) mysql>- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
命令示例：
以下命令示例中，首先查看到当前Binlog日志的位点为1740，然后往t_member表中插入一行数据，然后再查到当前Binlog日志的位点为2091，最后再查看Binlog日志文件mysql-bin.000001中从位点1740开始的所有的记录。mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 1740 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> insert into `t_member` values (2, '202209040002', '张聪', '18934358790', '22342323423@qq.com'); Query OK, 1 row affected (0.00 sec) mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 2091 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> show binlog events in 'mysql-bin.000001' from 1740\G; *************************** 1. row *************************** Log_name: mysql-bin.000001 Pos: 1740 Event_type: Anonymous_Gtid Server_id: 1 End_log_pos: 1819 Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS' *************************** 2. row *************************** Log_name: mysql-bin.000001 Pos: 1819 Event_type: Query Server_id: 1 End_log_pos: 1896 Info: BEGIN *************************** 3. row *************************** Log_name: mysql-bin.000001 Pos: 1896 Event_type: Table_map Server_id: 1 End_log_pos: 1969 Info: table_id: 93 (member.t_member) *************************** 4. row *************************** Log_name: mysql-bin.000001 Pos: 1969 Event_type: Write_rows Server_id: 1 End_log_pos: 2060 Info: table_id: 93 flags: STMT_END_F *************************** 5. row *************************** Log_name: mysql-bin.000001 Pos: 2060 Event_type: Xid Server_id: 1 End_log_pos: 2091 Info: COMMIT /* xid=106 */ 5 rows in set (0.00 sec) mysql>- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
1.2.6 解析Binlog日志文件
Binlog日志文件不能直接查看，需要通过mysql自带的mysqlbinlog工具解析，该工具在mysql安装目录下的bin目录中，CMD命令格式如下
-- start-position表示起始位点，-vv表示显示详细信息 mysqlbinlog --start-position=792 mysql-bin.000001 -vv > /data/000001.log -- 指明Binlog日志文件 mysqlbinlog --start-position=792 /data/mysql/logs/mysql-bin.000001 -vv > /data/000001.log- 1
- 2
- 3
- 4
- 5
解析文件示例：
以下文件内容即是上述Binlog日志文件解析出来的内容，为了方便阅读，在其中加了一些空行。从中可以看出，位点792到位点988是创建数据表t_test相关的记录，位点988到位点1350是清空数据表t_member相关的记录，位点1389到位点1709是往数据表t_member中插入第一行数据相关的记录，位点1740到位点2060是信数据表t_membet中插入第二行数据相关的记录。# The proper term is pseudo_replica_mode, but we use this compatibility alias # to make the statement usable on server versions 8.0.24 and older. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 157 #220904 11:50:17 server id 1 end_log_pos 126 CRC32 0x539502fe Start: binlog v 4, server v 8.0.30 created 220904 11:50:17 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' eSAUYw8BAAAAegAAAH4AAAABAAQAOC4wLjMwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAB5IBRjEwANAAgAAAAABAAEAAAAYgAEGggAAAAICAgCAAAACgoKKioAEjQA CigAAf4ClVM= '/*!*/; # at 792 #220904 14:53:51 server id 1 end_log_pos 869 CRC32 0xe120d65f Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=no original_committed_timestamp=1662274431171412 immediate_commit_timestamp=1662274431171412 transaction_length=196 # original_commit_timestamp=1662274431171412 (2022-09-04 14:53:51.171412 中国标准时间) # immediate_commit_timestamp=1662274431171412 (2022-09-04 14:53:51.171412 中国标准时间) /*!80001 SET @@session.original_commit_timestamp=1662274431171412*//*!*/; /*!80014 SET @@session.original_server_version=80030*//*!*/; /*!80014 SET @@session.immediate_server_version=80030*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 869 #220904 14:53:51 server id 1 end_log_pos 988 CRC32 0xf767b626 Query thread_id=9 exec_time=0 error_code=0 Xid = 47 use `member`/*!*/; SET TIMESTAMP=1662274431/*!*/; SET @@session.pseudo_thread_id=9/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1075838976/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C gbk *//*!*/; SET @@session.character_set_client=28,@@session.collation_connection=28,@@session.collation_server=255/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; /*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/; /*!80013 SET @@session.sql_require_primary_key=0*//*!*/; create table t_test(id int) /*!*/; # at 988 #220904 16:05:38 server id 1 end_log_pos 1067 CRC32 0xbe62b3c5 Anonymous_GTID last_committed=4 sequence_number=5 rbr_only=yes original_committed_timestamp=1662278738601091 immediate_commit_timestamp=1662278738601091 transaction_length=401 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; # original_commit_timestamp=1662278738601091 (2022-09-04 16:05:38.601091 中国标准时间) # immediate_commit_timestamp=1662278738601091 (2022-09-04 16:05:38.601091 中国标准时间) /*!80001 SET @@session.original_commit_timestamp=1662278738601091*//*!*/; /*!80014 SET @@session.original_server_version=80030*//*!*/; /*!80014 SET @@session.immediate_server_version=80030*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 1067 #220904 16:05:38 server id 1 end_log_pos 1144 CRC32 0xfb8e01e8 Query thread_id=10 exec_time=0 error_code=0 SET TIMESTAMP=1662278738/*!*/; /*!\C utf8mb4 *//*!*/; SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/; BEGIN /*!*/; # at 1144 #220904 16:05:38 server id 1 end_log_pos 1217 CRC32 0xb7559cc7 Table_map: `member`.`t_member` mapped to number 93 # at 1217 #220904 16:05:38 server id 1 end_log_pos 1358 CRC32 0xed8395cd Delete_rows: table id 93 flags: STMT_END_F BINLOG ' UlwUYxMBAAAASQAAAMEEAAAAAF0AAAAAAAEABm1lbWJlcgAIdF9tZW1iZXIABQMPDw8PCEAAgABA ALQAAAEBAAID/P8Ax5xVtw== UlwUYyABAAAAjQAAAE4FAAAAAF0AAAAAAAEAAgAF/wABAAAADDIwMjIwOTA0MDAwMQnmnY7lsI/m mI4LMTg5NDIzNTA3NjgNMzYxMjg5QHFxLmNvbQACAAAADDIwMjIwOTA0MDAwMgbnjovlvLoLMTg2 NzI5NzIyMzMONzY1OTIxNEBxcS5jb23NlYPt '/*!*/; ### DELETE FROM `member`.`t_member` ### WHERE ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2='202209040001' /* VARSTRING(64) meta=64 nullable=0 is_null=0 */ ### @3='鏉庡皬鏄? /* VARSTRING(128) meta=128 nullable=0 is_null=0 */ ### @4='18942350768' /* VARSTRING(64) meta=64 nullable=0 is_null=0 */ ### @5='361289@qq.com' /* VARSTRING(180) meta=180 nullable=0 is_null=0 */ ### DELETE FROM `member`.`t_member` ### WHERE ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ ### @2='202209040002' /* VARSTRING(64) meta=64 nullable=0 is_null=0 */ ### @3='鐜嬪己' /* VARSTRING(128) meta=128 nullable=0 is_null=0 */ ### @4='18672972233' /* VARSTRING(64) meta=64 nullable=0 is_null=0 */ ### @5='7659214@qq.com' /* VARSTRING(180) meta=180 nullable=0 is_null=0 */ # at 1358 #220904 16:05:38 server id 1 end_log_pos 1389 CRC32 0xb4968661 Xid = 80 COMMIT/*!*/; # at 1389 #220904 16:08:33 server id 1 end_log_pos 1468 CRC32 0xf1ae0a22 Anonymous_GTID last_committed=5 sequence_number=6 rbr_only=yes original_committed_timestamp=1662278913848935 immediate_commit_timestamp=1662278913848935 transaction_length=351 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; # original_commit_timestamp=1662278913848935 (2022-09-04 16:08:33.848935 中国标准时间) # immediate_commit_timestamp=1662278913848935 (2022-09-04 16:08:33.848935 中国标准时间) /*!80001 SET @@session.original_commit_timestamp=1662278913848935*//*!*/; /*!80014 SET @@session.original_server_version=80030*//*!*/; /*!80014 SET @@session.immediate_server_version=80030*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 1468 #220904 16:08:33 server id 1 end_log_pos 1545 CRC32 0xe21f6cd9 Query thread_id=9 exec_time=0 error_code=0 SET TIMESTAMP=1662278913/*!*/; /*!\C gbk *//*!*/; SET @@session.character_set_client=28,@@session.collation_connection=28,@@session.collation_server=255/*!*/; BEGIN /*!*/; # at 1545 #220904 16:08:33 server id 1 end_log_pos 1618 CRC32 0xce452aa2 Table_map: `member`.`t_member` mapped to number 93 # at 1618 #220904 16:08:33 server id 1 end_log_pos 1709 CRC32 0x107dc471 Write_rows: table id 93 flags: STMT_END_F BINLOG ' AV0UYxMBAAAASQAAAFIGAAAAAF0AAAAAAAEABm1lbWJlcgAIdF9tZW1iZXIABQMPDw8PCEAAgABA ALQAAAEBAAID/P8AoipFzg== AV0UYx4BAAAAWwAAAK0GAAAAAF0AAAAAAAEAAgAF/wABAAAADDIwMjIwOTA0MDAwMQnmnY7lsI/m mI4LMTg5MzQ1Mjg3OTQPMjM0MjM0MjNAcXEuY29tccR9EA== '/*!*/; ### INSERT INTO `member`.`t_member` ### SET ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2='202209040001' /* VARSTRING(64) meta=64 nullable=0 is_null=0 */ ### @3='鏉庡皬鏄? /* VARSTRING(128) meta=128 nullable=0 is_null=0 */ ### @4='18934528794' /* VARSTRING(64) meta=64 nullable=0 is_null=0 */ ### @5='23423423@qq.com' /* VARSTRING(180) meta=180 nullable=0 is_null=0 */ # at 1709 #220904 16:08:33 server id 1 end_log_pos 1740 CRC32 0x739d4a70 Xid = 100 COMMIT/*!*/; # at 1740 #220904 16:14:11 server id 1 end_log_pos 1819 CRC32 0xc662d5c5 Anonymous_GTID last_committed=6 sequence_number=7 rbr_only=yes original_committed_timestamp=1662279251667631 immediate_commit_timestamp=1662279251667631 transaction_length=351 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; # original_commit_timestamp=1662279251667631 (2022-09-04 16:14:11.667631 中国标准时间) # immediate_commit_timestamp=1662279251667631 (2022-09-04 16:14:11.667631 中国标准时间) /*!80001 SET @@session.original_commit_timestamp=1662279251667631*//*!*/; /*!80014 SET @@session.original_server_version=80030*//*!*/; /*!80014 SET @@session.immediate_server_version=80030*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 1819 #220904 16:14:11 server id 1 end_log_pos 1896 CRC32 0x51736bd1 Query thread_id=9 exec_time=0 error_code=0 SET TIMESTAMP=1662279251/*!*/; BEGIN /*!*/; # at 1896 #220904 16:14:11 server id 1 end_log_pos 1969 CRC32 0xc0cfa458 Table_map: `member`.`t_member` mapped to number 93 # at 1969 #220904 16:14:11 server id 1 end_log_pos 2060 CRC32 0x5f4545f8 Write_rows: table id 93 flags: STMT_END_F BINLOG ' U14UYxMBAAAASQAAALEHAAAAAF0AAAAAAAEABm1lbWJlcgAIdF9tZW1iZXIABQMPDw8PCEAAgABA ALQAAAEBAAID/P8AWKTPwA== U14UYx4BAAAAWwAAAAwIAAAAAF0AAAAAAAEAAgAF/wACAAAADDIwMjIwOTA0MDAwMgblvKDogaoL MTg5MzQzNTg3OTASMjIzNDIzMjM0MjNAcXEuY29t+EVFXw== '/*!*/; ### INSERT INTO `member`.`t_member` ### SET ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ ### @2='202209040002' /* VARSTRING(64) meta=64 nullable=0 is_null=0 */ ### @3='寮犺仾' /* VARSTRING(128) meta=128 nullable=0 is_null=0 */ ### @4='18934358790' /* VARSTRING(64) meta=64 nullable=0 is_null=0 */ ### @5='22342323423@qq.com' /* VARSTRING(180) meta=180 nullable=0 is_null=0 */ # at 2060 #220904 16:14:11 server id 1 end_log_pos 2091 CRC32 0x250a45c3 Xid = 106 COMMIT/*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
1.2.7 加密Binlog日志文件
从MySQL8.0.14开始，可以对Binlog日志文件进行加密，从而保护敏感数据。可以通过在配置文件的[mysqld]中加上如下语句开启Binlog日志文件加密
early-plugin-load=keyring_file.so keyring_file_data=/data/mysql/keyring binlog_encryption=on- 1
- 2
- 3
加密后的Binlog日志文件，在使用mysqlbinlog工具解析时，需要使用用户密码才能成功解析
-- read-from-remote-server参数表示从远程MySQL服务中读取Binlog日志文件，而不是读取本地日志文件 mysqlbinlog --read-from-remote-server -uroot -pxxx --start-positon=792 mysql-bin.000002 -vv >/data/00002.log- 1
- 2
1.2.8 清除Binlog日志文件
如果MySQL实例的Binlog日志文件增长较快，会占用大量的磁盘空间，可以使用以下命令来删除无用的Binlog日志文件
-- 删除指定Binlog日志文件之前的文件，这里不包含mysql-bin.00002文件 purge binary logs to 'mysql-bin.000002'; -- 删除指定时间之前的Binlog日志文件 purge binary logs before '2022-09-04 00:00:00';- 1
- 2
- 3
- 4
- 5
也可以通过以下参数指定Binlog日志文件的过期时间，如果Binlog日志文件超过了所设置的过期时间，则会自动删除
-- 设置Binlog日志文件过期时间为129600秒 set global binlog_expire_logs_seconds = 1296000; -- 查看Binlog日志文件过期时间 show global variables like 'binlog_expire_logs_seconds'; -- 需要执行flush logs语句才能删除之前的Binlog日志文件 flush logs;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
1.2.9 刷盘Binlog日志文件
Binlog日志同步到磁盘文件的频率是由sync_binlog参数控制的，该参数大致有以下几种配置
配置 说明 sync_binlog = 0 禁用MySQL服务将Binlog日志同步到磁盘的功能，是由操作系统控制Binlog的刷盘。
在这种情况下，性能比较好，但是当操作系统崩溃时可能会丢失部分事务sync_binlog = 1 每个事务都会同步到磁盘。这是最安全的设置，但是磁盘写入的次数的增加 可能会导致性能下降 sync_binlog = N 每N个事务同步一次到磁盘。当操作系统崩溃时，服务器提交的事务可能没有被刷新到Binlog日志中，
此时可能会丢失部分事务，虽然设置比较大的值可以提高性能，但是数据丢失的风险也会增加2 General日志
2.1 General日志说明
Binlog日志只会记录修改类型的SQL语句，而不会记录查询类型的SQL语句，而General日志会记录执行的每一条SQL语句。
2.2 General日志操作
2.2.1 开启General日志功能
如果要开启General日志，就需要在mysql配置文件的[mysqld]模块中加上如下语句，该配置表示General日志的存放路径为/data/mysql/logs/，文件名为mysql-general.log
general_log=ON general_log_file="/data/mysql/logs/mysql-general.log"- 1
- 2
或者通过以下命令开启General日志
set global general_log = 'ON'; set global general_log_file = '/data/mysql/logs/mysql-general.log';- 1
- 2
然后通过以下命令查看是否开启General日志
show global variables like 'general_log%';- 1
2.2.2 设置General日志存储
可以通过log_output参数控制General日志的存储方式，该参数大概有几下几种配置，其中TABLE和FILE可以同时使用。
配置 说明 TABLE 将记录保存在数据表mysql.general_log FILE 将记录保存到日志文件 NONE 禁用日志记录 可以在mysql配置文件的[mysqld]模块中加上如下语句设置General日志存储方式
-- 仅保存到文件中 log-output=FILE -- 仅保存到数据表中 log-output=TABLE -- 同时保存到文件中和数据表中 log-output=FILE,TABLE- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
或者通过以下命令设置General日志存储方式
set global log_output = 'FILE'; set global log_output = 'FILE,TABLE';- 1
- 2
- 3
然后通过以下命令查看设置的General日志存储方式
show global variables like 'log_output';- 1
2.2.3 查看General日志文件
命令示例：
以下命令示例中，首先是设置General日志输出方式为FILE，然后是查询和修改数据表t_member表中的数据。mysql> set global log_output = 'FILE'; Query OK, 0 rows affected (0.00 sec) mysql> show global variables like 'log_output'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_output | FILE | +---------------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> select * from t_member; +----+--------------+-------------+-------------+--------------------+ | id | member_no | member_name | phone | email | +----+--------------+-------------+-------------+--------------------+ | 1 | 202209040001 | 李小明 | 18934528794 | 23423423@qq.com | | 2 | 202209040002 | 张聪 | 18934358790 | 22342323423@qq.com | +----+--------------+-------------+-------------+--------------------+ 2 rows in set (0.00 sec) mysql> update t_member set member_name ='张小聪' where id = 2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from t_member; +----+--------------+-------------+-------------+--------------------+ | id | member_no | member_name | phone | email | +----+--------------+-------------+-------------+--------------------+ | 1 | 202209040001 | 李小明 | 18934528794 | 23423423@qq.com | | 2 | 202209040002 | 张小聪 | 18934358790 | 22342323423@qq.com | +----+--------------+-------------+-------------+--------------------+ 2 rows in set (0.00 sec) mysql>- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
上述命令示例的General日志如下所示：
Time Id Command Argument 2022-09-04T09:54:13.142636Z 9 Query set global log_output = 'FILE' 2022-09-04T09:54:32.027627Z 9 Query show global variables like 'log_output' 2022-09-04T09:54:48.457546Z 9 Query select * from t_member 2022-09-04T09:55:14.759138Z 9 Query update t_member set member_name ='张小聪' where id = 2 2022-09-04T09:55:24.169857Z 9 Query select * from t_member- 1
- 2
- 3
- 4
- 5
- 6
3 Slow日志
3.1 Slow日志说明
Slow日志可以用于查找执行时间比较长的查询，当优化数据库时，Slow日志往往是需要重点关注的日志文件。
3.2 Slow日志操作
3.2.1 开启Slow日志功能
如果要开启Slow日志，就需要在mysql配置文件的[mysqld]模块中加上如下语句，该配置表示Slow日志的存放路径为/data/mysql/logs/，文件名为mysql-slow.log，慢查询的时间为2秒
-- 开启Slow日志功能 slow_query_log=ON -- 设置Slow日志文件 slow_query_log_file="/data/mysql/logs/mysql-slow.log" -- 设置慢查询的时间为2秒(默认为10秒) long_query_time=2- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
或者通过以下命令开启Slow日志
-- 开启Slow日志功能 set global slow_query_log = 'ON'; -- 设置Slow日志文件 set global slow_query_log_file = '/data/mysql/logs/mysql-slow.log'; -- 设置慢查询的时间为2秒(默认为10秒)，以下2条SQL都需要执行 set long_query_time = 2; set global long_query_time = 2;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
然后通过以下命令查看是否开启Slow日志
show global variables like '%slow_query_log%'; show global variables like '%long_query_time%';- 1
- 2
- 3
3.2.2 设置Slow日志存储
可以通过log_output参数控制Slow日志的存储方式，该参数大概有几下几种配置，其中TABLE和FILE可以同时使用。
配置 说明 TABLE 将记录保存在数据表mysql.slow_log FILE 将记录保存到日志文件 NONE 禁用日志记录 可以在mysql配置文件的[mysqld]模块中加上如下语句设置Slow日志存储方式
-- 仅保存到文件中 log-output=FILE -- 仅保存到数据表中 log-output=TABLE -- 同时保存到文件中和数据表中 log-output=FILE,TABLE- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
或者通过以下命令设置Slow日志存储方式
set global log_output = 'FILE'; set global log_output = 'FILE,TABLE';- 1
- 2
- 3
然后通过以下命令查看设置的Slow日志存储方式
show global variables like 'log_output';- 1
3.2.3 查看Slow日志文件
命令示例：
以下命令示例中，首先是开启Slow日志，并设置慢查询时间为2秒，输出方式为FILE和TABLE，然后执行了3条耗时分别为1秒、2秒、3秒的查询。mysql> set global slow_query_log = 'ON'; Query OK, 0 rows affected (0.00 sec) mysql> show global variables like 'slow_query_log%'; +---------------------+----------------+ | Variable_name | Value | +---------------------+----------------+ | slow_query_log | ON | | slow_query_log_file | mysql-slow.log | +---------------------+----------------+ 2 rows in set, 1 warning (0.00 sec) mysql> set long_query_time = 2; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'long_query_time'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 2.000000 | +-----------------+----------+ 1 row in set, 1 warning (0.00 sec) mysql> set global long_query_time = 2; Query OK, 0 rows affected (0.00 sec) mysql> show global variables like 'long_query_time'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 2.000000 | +-----------------+----------+ 1 row in set, 1 warning (0.00 sec) mysql> set global log_output = 'FILE,TABLE'; Query OK, 0 rows affected (0.00 sec) mysql> show global variables like 'log_output'; +---------------+------------+ | Variable_name | Value | +---------------+------------+ | log_output | FILE,TABLE | +---------------+------------+ 1 row in set, 1 warning (0.00 sec) mysql> select sleep(1); +----------+ | sleep(1) | +----------+ | 0 | +----------+ 1 row in set (1.00 sec) mysql> select sleep(2); +----------+ | sleep(2) | +----------+ | 0 | +----------+ 1 row in set (2.00 sec) mysql> select sleep(3); +----------+ | sleep(3) | +----------+ | 0 | +----------+ 1 row in set (3.00 sec) mysql>- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
由于上述命令设置Slow日志同时保存在文件和数据表中，所以文件和数据表中的慢查询记录分别如下所示。
Time Id Command Argument # Time: 2022-09-04T10:58:26.820916Z # User@Host: root[root] @ localhost [::1] Id: 9 # Query_time: 2.000291 Lock_time: 0.000002 Rows_sent: 1 Rows_examined: 1 SET timestamp=1662289104; select sleep(2); # Time: 2022-09-04T10:58:36.095343Z # User@Host: root[root] @ localhost [::1] Id: 9 # Query_time: 3.000333 Lock_time: 0.000001 Rows_sent: 1 Rows_examined: 1 SET timestamp=1662289113; select sleep(3);- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
mysql> select * from mysql.slow_log\G; *************************** 1. row *************************** start_time: 2022-09-04 18:58:26.820916 user_host: root[root] @ localhost [::1] query_time: 00:00:02.000291 lock_time: 00:00:00.000002 rows_sent: 1 rows_examined: 1 db: member last_insert_id: 0 insert_id: 0 server_id: 1 sql_text: 0x73656C65637420736C656570283229 thread_id: 9 *************************** 2. row *************************** start_time: 2022-09-04 18:58:36.095343 user_host: root[root] @ localhost [::1] query_time: 00:00:03.000333 lock_time: 00:00:00.000001 rows_sent: 1 rows_examined: 1 db: member last_insert_id: 0 insert_id: 0 server_id: 1 sql_text: 0x73656C65637420736C656570283329 thread_id: 9 2 rows in set (0.00 sec) mysql>- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
4 Error日志
4.1 Error日志说明
MySQL的Error日志不仅包含错误信息，还包含启动和关闭的一些记录，在很多情况下，定位问题第一时间应该查看Error日志。
4.2 Error日志操作
4.2.1 配置Error日志文件
可以在mysql配置文件的[mysqld]模块中加上如下语句来设置Error日志的路径和文件名，该配置表示Error日志的存放路径为/data/mysql/logs/，文件名为mysql.err
log-error="/data/mysql/logs/mysql.err"- 1
然后通过以下命令查看Error日志配置
show global variables like 'log_error';- 1
4.2.2 切割Error日志文件
有时Error日志文件可能会比较 大，这就需要重新生成一个新的，此时可以使用下面的命令来实现
-- 切割Error文件 mv mysql.err mysql.err_20220904 -- flush-logs表示清除Error日志 mysqladmin -uroot -pxxx flush-logs- 1
- 2
- 3
- 4
- 5
5 MySQL监控
5.1 系统相关
MySQL系统相关的监控有以下几个方面：
- 系统CPU负载
- 系统内存交换
- 系统文件描述符
- 系统网络流量
- 磁盘利用率
- 磁盘IOPS
5.2 状态相关
5.2.1 MySQL的进程状态
获取MySQL的启动时间和状态名Uption的命令如下：
show global status like 'Uptime';- 1
或者在在MySQL中执行如下命令：
select 1;- 1
5.2.2 QPS
Queries是MySQL启动之后的总查询次数，查询命令如下：
show global status like 'Queries';- 1
假设第一次获取的Queries的值是Q1，60秒之后获取的Queries的值为Q2，那么QPS的计算方式如下：
QPS = (Q2 - Q1) / 60- 1
5.2.3 TPS
一般建议使用GTID的增长率来计算TPS。在计算TPS时会使用两个值，即Com_commit的值和Com_rollback的值，实际上这两个值是不准确的，需要显示执行commit命令，Com_commit的值才会增加。
5.2.4 CURD的数量
前面介绍了QPS和TPS的计算方式，如果要计算增、删、改、查具体的值，则可以使用下面的这些些亦是，具体的计算方式可以参考QPS的计算方式：
- 增：Com_insert
- 删：Com_delete
- 改：Com_update
- 查：Com_select
5.2.5 慢查询数量
以下命令可以查询启动MySQL服务后慢查询的总数量，同样可以通过类似计算QPS的方式计算每分钟慢查询的数量，vkjj超过5条就告警。
show global status like 'Slow_queries'- 1
5.2.6 日志缓冲区是否够用
可以通过监控innodb_log_waits参数的值来判断日志缓冲区是否够用，该状态值表示日志缓冲区过小，导致需要等待日志刷新才能继续的次数。当innodb_log_waits参数的值过大时，表示InnoDB log buffer不够用，因此需要对innodb_log_waits参数进行监控。
5.2.7 临时表
要以监控以下两个参数的值
- Created_tmp_disk_tables：执行语句时创建的内部磁盘临时表的数量
- Created_tmp_tables：执行语句时创建的内部临时表的数量
5.3 锁相关
5.3.1 表锁情况
table_locks_waited参数表示表锁等待的次数，因此可以通过获取该参数的值来确定表锁情况：
show global status like 'table_locks_waited';- 1
5.3.2 等待行锁的数量
可以通过innodb_row_lock_current_waits参数获取InnoDB正在等待行锁的数量：
show global status like 'innodb_row_lock_current_waits';- 1
5.3.3 行锁发生次数
可以通过innodb_row_lock_waits参数获取InnoDB行锁发生次数：
show global status like 'innodb_row_lock_waits';- 1
5.3.4 行锁总耗时
可以通过innodb_row_lock_time参数获取InnoDB行锁总耗时：
show global status like 'innodb_row_lock_time';- 1
5.3.5 行锁平均耗时
可以通过innodb_row_lock_time_avg参数获取InnoDB行锁平均耗时：
show global status like 'innodb_row_lock_time_avg';- 1
5.3.6 行锁最大耗时
可以通过innodb_row_lock_time_max参数获取InnoDB行锁最久耗时：
show global status like 'innodb_row_lock_time_max';- 1
5.4 连接相关
5.4.1 连接使用率
计算连接使用率，需要使用threads_connected参数和max_connections参数，获取这两个参数的命令如下：
show global status like 'threads_connected'; show global status like 'max_connections';- 1
- 2
连接使用率的计算公式如下：
connect_used_ratio = threads_connected / max_connections- 1
5.4.2 活跃连接
可以通过Threads_running参数获取活跃连接：
show global status like 'Threads_running';- 1
5.4.3 客户端异常中断数
可以通过Aborted_clients参数获取客户端被异常中断的次数，增加监控可以发现应用程序的一些异常。
show global status like 'Aborted_clients';- 1
5.5 复制相关
5.5.1 I/O线程和SQL线程
在正常情况下，执行show slave status语句，Slave_IO_Running和Slave_SQL_Running都为Yes，如果其中一个不为Yes，则需要有告警机制。
show slave status- 1
5.5.2 主从延迟
执行show slave status 语句可以获取Seconds_Behind_Master的值，一般都为0，如果Seconds_Behind_Master的值比较大，如大于300秒，则需要触发告警。
show slave status- 1
5.5.3 从库read only
在一般情况下同，从库都需要设置为read only，如果不是read only，则需要触发告警，查询命令如下：
show global variables like 'read_only';- 1
5.6 参数相关
5.6.1 log_bin参数
log_bin参数表示是否开启Binlog，在一般情况下，线上环境需要开启，如果某个实例的log_bin参数不为on，需要触发告警，提醒用户修改。
5.6.2 sync_binlog参数
为了保证数据库安全，一般建议将sync_binlog参数设置为1，表示每个事务的Binlog都会同步到磁盘，如果某个实例的sync_binlog参数的值不为1，需要触发告警，提醒用户修改。
5.6.3 binlog_expire_logs_seconds参数
对于线上的MySQL，需要合理配置binlog_expire_logs_seconds参数，参数值设置得太小可能会导致MySQL误操作后不能完整恢复，因此需要对这个参数的值进行监控。
5.6.4 innodb_flush_log_at_trx_commit参数
为了保证数据库安全，一般建议将innodb_flush_log_at_trx_commit参数设置为1，表示在每次提交事务时，日志缓冲区都会写入日志文件中，并在日志文件上执行磁新操作，如果某个实例的innodb_flush_log_trx_commit参数的值不为1，需要触发告警，提醒用户修改。
5.7 业务相关
5.7.1 主键自增值的使用率
一般建议主键是自增的，但是不管使用int类型还是使用bigint类型，都是有上限的，所以需要关注主键自增值的使用率。获取自增值auto_increment的SQL语句如下：
select auto_increment from tables where table_schema = '数据库名' and table_name = '表名';- 1
如果主键为unsigned int类型，则主键值的上限为 2 ^ 32 -1，因此，主键自增值的使用率的计算方式为： auto_increment / 2 ^ 32 -1
如果主键为unsigned bigint类型，则主值的上限为 2 ^ 48 -1，因此，主键自增值的使用率的计算方式为： auto_increment / 2 ^ 48 -1
5.7.2 表数据量
表数据量过多，可能会导致查询很慢，并且维护成功也会很高，因此，可以在表数据量达到某个阀值时触发对应的告警。获取表数据量的SQL语句如下：
select table_rows from information_schema.tables where table_schema = '数据库名' and table_name = '表名';- 1
-
相关阅读:
C语言学生宿舍水电费信息管理系统
【新版】系统架构设计师 - 软件架构设计＜轻量级架构＞
VUE3照本宣科——路由与状态管理器
leetCode 5. 最长回文子串 动态规划 + 优化空间 / 中心扩展法 + 双指针
分析25个主要DeFi协议的路线图 预见DeFi未来的七大趋势
java Collections基本概念和常用方法
从理解路由到实现一套Router（路由）
JS-String篇(速记用法大汇总)
API接口在电商商品数据获取中的应用
Java Base64 加密与解密
- 原文地址：https://blog.csdn.net/weixin_44516305/article/details/126687672
