Linux相关知识的第十八回合
文章目录
MySQL常见的存储引擎以及特点
概述
MySQL的常见的存储引擎
14:35:11 (root@(none)) [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+--------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+--------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | <null> | <null> | <null> |
+--------------------+---------+----------------------------------------------------------------+--------------+--------+------------+
9 rows in set
Time: 0.017s
目前基本上只用MyISAM、InnoDB
MyISAM与InnoDB的比较
MyISAM
MyIsam的存储文件有三个，后缀名分别是.frm、.MYD、MYI，其中.frm是表的定义文件，.MYD是数据文件，.MYI是索引文件。MyIsam只支持表锁，不支持事务。MyIsam由于有单独的索引文件，在读取数据方面的性能很高。Myisam是以堆结构进行组织数据，其表容易损坏
特点
不支持事务
表级锁定
读写相互阻塞，写入不能读，读时不能写
只缓存索引
不支持外键索引
不支持聚簇索引
读取数据较快，占用资源较少
不支持MVCC（多版本并发控制机制）高并发
崩溃恢复性较差
MySQL5.5前默认的数据库引擎
适用场景
只读（或者写较少）、表较少（可以接受长时间进行修复操作）
引擎文件
tb1_name.frm存放表的定义 file user*查看
tb1_name.MYD数据文件
tb1_name.MYI索引文件
实例
pwd
/data/mysql/data/hellodb
ll
total 360K
-rw-r----- 1 mysql mysql 61 Mar 2 15:29 db.opt
-rw-r----- 1 mysql mysql 8.5K Mar 2 15:29 classes.frm
-rw-r----- 1 mysql mysql 172 Mar 2 15:29 classes.MYD
-rw-r----- 1 mysql mysql 2.0K Mar 2 15:29 classes.MYI
15:40:24 (root@(none)) [hellodb]> show create table classes;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| classes | CREATE TABLE `classes` (\n `ClassID` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,\n `Class` varchar(100) DEFAULT NULL,\n `NumOfStu` smallint(5) unsigned DEFAULT NULL,\n PRIMARY KEY (`ClassID`)\n) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
Time: 0.009s
InnoDB
InnoDB的存储文件有两个，后缀名分别是.frm和.idb，其中 .frm是表的定义文件，而.idb是数据文件。InnoDB中存在表锁和行锁，不过行锁是在命中索引的情况下才会起作用。InnoDB支持事务，且支持四种隔离级别（读未提交、读已提交、可重复读、串行化），默认的为可重复读
特点
行级锁
支持事务，适合处理大量短期事务
读写阻塞与事务隔离级别相关
可缓存数据和索引
支持聚簇索引
崩溃恢复性好
支持MVCC高并发
从MySQL5.5后支持全文索引
从MySQL5.5开始为默认的数据库引擎
引擎文件
- 共享表空间：所有Innodb表的数据和索引都放在同一表空间中
ibdata1、ibdata2数据文件，存放在定义好的数据目录下（/path/to/path/datadir/）
tb1_name.frm表格式定义，存放在每个表对应的目录下（/path/to/path/datadir/dbname/）
- 独立表空间：
file-per-table表空间；每个表单独使用一个表空间存储表的数据和索引
tb1_name.frm表格式定义，存放在每个表对应的目录下（/path/to/path/datadir/dbname/）
tb1_name.ibd数据文件，存储数据和索引，与.frm文件存放在同级目录下（/path/to/path/datadir/dbname/）
采用File-Per-Table的优缺点如下：
优点： 可以方便回收删除表所占的磁盘空间。如果使用系统表空间的话，删除表后空闲空间只能被InnoDB数据使用。
TRUNCATE TABLE操作会更快。可以单独拷贝表空间数据到其他数据库(使用transportable tablespace特性)，可以更方便的观测每个表空间数据的大小缺点：
fsync操作需要作用的多个表空间文件，比只对系统表空间这一个文件进行fsync操作会多一些IO操作。此外，mysqld需要维护更多的文件描述符
查看是否启用file-per-table
16:08:45 (root@(none)) [(none)]> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set
Time: 0.010s
在MySQL5.7及MariaDB5.5以后，默认File-Per-Table为开启的
之前的版本想可以通过修改innodb_file_per_table=ON来启用，或者在配置文件中添加相关选项
vim /etc/my.cnf
##################################################################
[mysqld]
# 设置默认的存储引擎
default_storage_engine=InnoDB
# 使用此选项，每个表都有ibd文件，每个表都有独立的文件更好管理
innodb_file_per_table
##################################################################
# 重启数据库服务
systemctl restart mariadb
其他存储引擎
Performance_Schema: Performance_Schema数据库使用；performance_schema使用的存储引擎
Memory: 将所有数据存储在RAM中，以便在需要快速查找参考和其他类似数据的环境中进行快速访问。适用存放临时数据。引擎以前被称为HEAP引擎；information_schema使用的存储引擎
MRG_MyISAM: 使MySQL DBA或开发人员能够对一系列相同的MyISAM表进行逻辑分组，并将它们作为一个对象引用。适用于VLDB(Very Large Data Base)环境，如数据仓库
Archive: 为存储和检索大量很少参考的存档或安全审核信息，只支持SELECT和INSERT操作；支持行级锁和专用缓存区
Federated联合: 用于访问其它远程MySQL服务器一个代理，它通过创建一个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，而后完成数据存取，提供链接单独MySQL服务器的能力，以便从多个物理服务器创建一个逻辑数据库。非常适合分布式或数据集市环境
BDB: 可替代InnoDB的事务引擎，支持COMMIT、 ROLLBACK和其他事务特性
Cluster/NDB: MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性
CSV: CSV存储引擎使用逗号分隔值格式将数据存储在文本文件中。可以使用CSV引擎以CSV格式导入和导出其他软件和应用程序之间的数据交换
BLACKHOLE: 黑洞存储引擎接受但不存储数据，检索总是返回一个空集。该功能可用于分布式数据库设计，数据自动复制，但不是本地存储
example: “stub”引擎，它什么都不做。可以使用此引擎创建表，但不能将数据存储在其中或从中检索。目的是作为例子来说明如何开始编写新的存储引擎
MySQL查询缓存优化总结
查询缓存的原理
查询执行路径
MySQL整个查询执行的过程，总体分为5个步骤：
客户端向MySQL服务器发送一条查询请求
服务器首先检查查询缓存，如果命中缓存，则立刻返回缓存中的结果，否则进入到下一个阶段
服务器进行SQL解析，预处理、再由优化器生成对应的执行计划
MySQL根据执行计划，调用存储引擎的API来执行查询
将结果返回给客户端，同时缓存查询结果
查询缓存原理
缓存SELECT操作或预处理查询的结果集和SQL语句，当有新的SELECT语句或预处理查询语句请求，先去查询缓存，判断是否存在可用记录集，判断标准：与缓存的SQL语句，是否完全一致，需要区分大小写
查询缓存的优缺点
不需要对SQL语句做任何的分析和执行，当然语法解析必须通过在先，直接从
Query Cache中获取查询结果，提高查询性能
查询缓存的判断的规则，不够智能，也即提高了查询缓存的使用门康，降低效率
查询缓存的使用，会增加检查和清理Query Cache中记录的开销
不会被缓存的查询语句
查询语句中加了
SQL_NO_CACHE参数，指明查询结果不需要缓存
查询语句中含有获得值的函数，包含：自定义函数，eg：now()、curdate()、get_lock()、rand()、convert_tz()等
对系统数据库的查询：mysql、information_schema查询语句中使用session级别变量或存储过程中的局部变量
查询语句中使用了LOCK IN SHARE MODE、FOR UPDATE的语句，查询语句中类似SELECT....INTO导出数据的语句
对临时表的查询操作
存在警告信息的查询语句
不涉及任何表或视图的查询语句
某用户只有列级别权限的查询语句
事务隔离级别为Serializable时，所有查询语句都不能缓存
查询缓存相关的服务器变量
query_cache_min_res_unit：查询缓存中内存块的最小分配单位，默认4k，较小值会减少浪费，但会导致更频繁的内存分配操作，较大值会带来浪费，会导致碎片过多，内存不足
query_cache_limit：单个查询结果能缓存的最大值，单位字节，默认为1M，对于查询结果过大而无法缓存的语句，建议使用SQL_NO_CACHE
query_cache_size：查询缓存总共可用的内存空间；单位字节，必须是1024的整数倍，最小值40KB，低于此值有警报
query_cache_wlock_invalidate：如果某表被其他的会话锁定，是否仍然可以从查询缓存中返回结果，默认值为OFF，表示可以在表被其他会话锁定的场景中继续从缓存返回数据；ON则表示不允许
query_cache_type：是否开启缓存功能，取值为ON、OFF、DEMAND
SELECT语句的缓存控制
SQL_CACHE：显式指定存储查询结果于缓存之中
SQL_NO_CACHE：显式查询结果不予缓存
query_cache_type 参数变量
query_cache_type的值为OFF或0时，查询缓存功能关闭
query_cache_type的值为ON或1时，查询缓存功能打开，SELECT的结果符合缓存条件即会缓存，否则，不予缓存，显式指定SQL_NO_CACHE，不予缓存，此为默认值
query_cache_type的值为DEMAND或2时，查询缓存功能按需进行，显式指定的SQL_CACHE的SELECT语句才会缓存；其他均不予缓存
查询缓存相关的状态变量
show global status like 'Qcache%';
Qcache_free_blocks处于空闲状态Query Cache中内存Block数
Qache_total_blocksQuery Cache中总Block，当Qcache_free_blocks相对此值较大时，可能用内存碎片，执行FLUSH QUERY CACHE清理碎片
Qache_free_memory处于空闲状态的Query Cache内存总量
Qache_hitsQuery Cache命中次数
Qcache_inserts向Query Cache中插入新的Query Cache的次数，即没有命中的次数
Qcache_lowmem_prunes记录因为内存不足而被移除查询缓存的查询数
Qcache_not_cached没有被cache的SQL数，包括无法被cache的SQL以及由于query_cache_type设置的不会被cache的SQL语句
Qcache_queries_in_cache在Query Cache中的SQL数量
查询的优化
命中率和内存使用率估算
查询缓存中内存块的最小分配单位query_cache_min_res_unit
(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
查询缓存命中率
Qcache_hits / (Qcache_hits + Qcache_inserts) *100%
查询缓存内存使用率
(query_cache_size - Qcache_free_memory) / query_cache_size * 100%
MySQL 8.0 变化
MySQL 8.0 取消查询缓存的功能
尽管MySQL Query Cache 皆在提高性能，但他存在严重的可伸缩性的问题，并且很容易成为严重的瓶颈
自MySQL 5.6 （2013） 以来，默认情况下已禁用查询缓存，其不能与多核计算机上在高速吞吐量工作负载情况下进行扩展
另外有时因为查询缓存往往利大于弊。比如：查询缓存的失效非常频繁，只要有对一个表的更新，这个表上的所有查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呐，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率非常低。除非你的业务有一张静态表，很长时间更新一次，比如系统配置表，那么这张表的查询才适合查询缓存目前大多数应用都把缓存作为应用逻辑层，比如：使用
redis或者memcached
MySQL日志各类总结
MySQL支持丰富的日志类型，如下：
事务日志：
transaction log；事务日志的写入类型为“追加”，因此其操作为“顺序IO”；通常也被称为：预写式日志 write ahead logging；事务日志文件：ib_logfile0，ib_logfile1
错误日志：error log
通用日志：general log
慢查询日志：slow query log
二进制日志：binary log
中继日志：reley log；在主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件
事务日志
事务日志：transaction log
redo log实现WAL（Write Ahead Log），数据更新前先记录redo log
undo log保存与执行的操作相反的操作，用于实现rollback
事务型存储引擎自行管理和使用，建议和数据文件分开存放
Innodb事务日志相关配置
15:17:30 (root@(none)) [hellodb]> show variables like '%innodb_log%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| innodb_log_buffer_size | 16777216 | --- 内存缓冲区大小；默认：16M
| innodb_log_checksums | ON |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 50331648 | --- 每个日志文件大小；默认：50M
| innodb_log_files_in_group | 2 | --- 日志组成员个数
| innodb_log_group_home_dir | ./ | --- 事务文件路径；默认与数据文件存放的路径一样
| innodb_log_write_ahead_size | 8192 |
+-----------------------------+----------+
7 rows in set
Time: 0.010s
15:19:16 (root@(none)) [hellodb]> show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1 | --- 默认为1
+--------------------------------+-------+
1 row in set
Time: 0.010s
事务日志性能优化
innodb_flush_log_at_trx_commit = <0|1|2>
0提交时没有写磁盘的操作，而是每秒执行一次将日志缓冲区的提交事务写入刷新到磁盘；这样可提供更好的性能，但服务器崩溃可能丢失最后一秒的事务
1默认值；日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘；这是完全遵守ACID特性
2每次提交后都会写入OS缓冲区，但每秒才会进行一次刷新到磁盘文件中，性能比0略差一些，但操作系统或者停电何能导致最后一秒的交易丢失
高并发业务行业最佳实践，是使用第三种折衷配置（=2）
配置为
2或者0，性能差异并不大，因为将数据从Log Buffer拷贝到OS cache，虽然跨越用户态与内核态，但毕竟只是内存的数据拷贝，速度很快
配置为2或者0，安全性差异巨大，操作系统崩溃的概率相比MySQL应用程序崩溃的概率，小很多，设置为2，只要操作系统不崩溃，也绝对不会丢数据
说明：
设置为1，同时sync_binlog = 1表示最高级别的容错
innodb_flush_log_at_trx_commit =0时，将不能用SET语句重置此变量（MariaDB 10.2.6后弃用）
错误日志
mysqld启动和关闭过程中输出的事件信息
mysqld运行中产生的错误信息
event scheduler运行一个event时产生的日志信息
在主从复制架构中的从服务器上启动从服务器线程时产生的信息
错误日志文件的路径
16:49:01 (root@(none)) [hellodb]> show variables like 'log_error';
+---------------+-------------------------------+
| Variable_name | Value |
+---------------+-------------------------------+
| log_error | /data/mysql/logs/mysqld.error |
+---------------+-------------------------------+
1 row in set
Time: 0.009s
错误日志级别
16:49:35 (root@(none)) [hellodb]> show variables like 'log_warnings';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_warnings | 2 |
+---------------+-------+
1 row in set
Time: 0.010s
--- 日志级别
log_warnings = <0|1|2|3.....>
通用日志
通用日志：记录对数据库的通用操作，包括：错误的
SQL语句
通用日志可以保存在：file（默认值）或table（mysql.general_log表）
通用日志相关设置
--- 是否开启
general_log= ON | OFF
--- 定义日志名称
general_log_file=HOMENAME.log
--- 定义日志类型
log_output= TABLE | FILE | NONE
范例
--- 查看是否开启了通用日志
14:26:56 (root@(none)) [hellodb]> select @@general_log;
+---------------+
| @@general_log |
+---------------+
| 0 |
+---------------+
1 row in set
Time: 0.016s
--- 查看输出类型
--- 如果将输出类型改成table的话，默认会将日志输出到mysql.general_log中
14:29:57 (root@(none)) [hellodb]> select @@log_output;
+--------------+
| @@log_output |
+--------------+
| FILE |
+--------------+
1 row in set
Time: 0.008s
--- 查看通用日志文件路径及文件名称
14:31:39 (root@(none)) [hellodb]> select @@general_log_file;
+-----------------------------+
| @@general_log_file |
+-----------------------------+
| /data/mysql/logs/mysqld.log |
+-----------------------------+
1 row in set
Time: 0.008s
--- 查询执行SQL语句次数最多的前三条语句
14:53:33 (root@(none)) [hellodb]> select argument,count(argument) from mysql.general_log group by argument order by count(argument) desc limit 3;
+----------+-----------------+
| argument | count(argument) |
+----------+-----------------+
0 rows in set
Time: 0.016s
慢查询日志
慢查询日志：记录执行查询时长超出指定时长的操作
慢查询相关变量
--- 是否开启慢查询日志，支持全局和会话，只有全局设置才会生成慢查询文件
slow_query_log= ON｜OFF
--- 慢查询的阀值，单位秒；默认10s
long_query_time = N
--- 慢查询日志文件
slow_query_log_file=HOSTNAME-slow.log
--- 记录日志规则(只有下面查询类型且查询时长超时，则被记录)，MariaDB上使用；MySQL5.7没有
log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table_on_disk
--- 不使用索引或使用全文索引扫描，不论是否达到慢查询阀值的语句是否记录日志，默认为OFF，即不记录
log_queries_not_using_indexes = ON｜OFF
--- 多少次查询才记录，MariaDB特有
log_slow_rate_limit = 1
--- 记录内容
log_slow_verbosity = Query_plan,explain
--- 同slow_query_log，MariaDB 10.0及MySQL5.6.1版之后删除
log_slow_queries = OFF
实例
--- 查询慢日志是否开启
15:31:51 (root@(none)) [(none)]> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | ON |
+----------------+-------+
1 row in set
Time: 0.010s
--- 查询慢日志名称
15:32:04 (root@(none)) [(none)]> show variables like 'slow_query_log_file';
+---------------------+--------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------+
| slow_query_log_file | /data/mysql/logs/mysqlslow.log |
+---------------------+--------------------------------+
1 row in set
Time: 0.010s
--- 查询慢日志阀值
15:34:29 (root@(none)) [(none)]> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set
Time: 0.010s
--- 查看没有使用索引是否记录到慢日志中
15:54:46 (root@(none)) [db1]> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF |
+-------------------------------+-------+
1 row in set
Time: 0.011s
--- 设置没有使用索引的记录到慢日志中
15:55:08 (root@(none)) [db1]> set global log_queries_not_using_indexes = 1;
Query OK, 0 rows affected
Time: 0.001s
15:55:11 (root@(none)) [db1]> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON |
+-------------------------------+-------+
1 row in set
Time: 0.010s
--- 执行存储过程
15:39:04 (root@(none)) [db1]> call sp_testlog;
Query OK, 1 row affected
Time: 659.441s
--- 利用函数sleep制造慢查询语句
10:27:50 (root@(none)) [hellodb]> select sleep(3) from teachers;
+----------+
| sleep(3) |
+----------+
| 0 |
| 0 |
| 0 |
| 0 |
+----------+
4 rows in set
Time: 12.009s
慢查询分析工具：profile
show profile命令用于跟踪执行过的SQL语句的资源消耗信息，可以帮助查看SQL语句的执行情况，可以在做性能分析或者问题诊断时候作为参考
在MySQL 5.7中，show profile命令尚且支持，但不建议使用
--- 查看profile工具是否开启
10:40:45 (root@(none)) [hellodb]> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | OFF |
+---------------+-------+
1 row in set
Time: 0.010s
--- 开启profile工具
10:41:29 (root@(none)) [hellodb]> set profiling = 'on';
Query OK, 0 rows affected
Time: 0.001s
10:41:32 (root@(none)) [hellodb]> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | ON |
+---------------+-------+
1 row in set
Time: 0.010s
--- 查看语句，注意结果中的Query_ID值
10:44:55 (root@(none)) [hellodb]> show profiles;
+----------+-------------+----------------------------------------------+
| Query_ID | Duration | Query |
+----------+-------------+----------------------------------------------+
| 1 | 0.00139675 | show variables like 'profiling' |
| 2 | 0.0015385 | show variables like '%long_query_time%' |
| 3 | 0.00010225 | set long_query_time = 2 |
| 4 | 0.00152525 | show variables like '%long_query_time%' |
| 5 | 0.000221 | select * from testlog where age = 88888 |
| 6 | 0.000293 | show databases |
| 7 | 0.03459325 | select * from db1.testlog where age = 88888 |
| 8 | 0.034684 | select * from db1.testlog where age = 882388 |
| 9 | 0.034368 | select * from db1.testlog where age = 12345 |
| 10 | 0.03415525 | select * from db1.testlog where age = 15422 |
| 11 | 0.00024425 | select * from db1.testlog where id = 666888 |
| 12 | 0.00026925 | select * from db1.testlog where id = 66688 |
| 13 | 12.0007465 | select sleep(3) from teachers |
| 14 | 4.000719 | select sleep(1) from teachers |
+----------+-------------+----------------------------------------------+
14 rows in set
Time: 0.009s
--- 根据Query_ID值查询对应语句的详细执行步骤和时长
10:45:25 (root@(none)) [hellodb]> show profile for query 14;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000088 |
| checking permissions | 0.000009 |
| Opening tables | 0.000017 |
| init | 0.000012 |
| System lock | 0.000010 |
| optimizing | 0.000004 |
| statistics | 0.000012 |
| preparing | 0.000010 |
| executing | 0.000003 |
| Sending data | 0.000020 |
| User sleep | 1.000117 |
| User sleep | 1.000089 |
| User sleep | 1.000085 |
| User sleep | 1.000109 |
| end | 0.000014 |
| query end | 0.000009 |
| closing tables | 0.000009 |
| freeing items | 0.000020 |
| logging slow query | 0.000069 |
| cleaning up | 0.000018 |
+----------------------+----------+
20 rows in set
Time: 0.009s
--- 显示CPU使用情况
10:47:47 (root@(none)) [hellodb]> show profile cpu for query 14;
+----------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting | 0.000088 | 0.000084 | 0.000000 |
| checking permissions | 0.000009 | 0.000007 | 0.000000 |
| Opening tables | 0.000017 | 0.000017 | 0.000000 |
| init | 0.000012 | 0.000011 | 0.000000 |
| System lock | 0.000010 | 0.000010 | 0.000000 |
| optimizing | 0.000004 | 0.000004 | 0.000000 |
| statistics | 0.000012 | 0.000012 | 0.000000 |
| preparing | 0.000010 | 0.000009 | 0.000000 |
| executing | 0.000003 | 0.000003 | 0.000000 |
| Sending data | 0.000020 | 0.000020 | 0.000000 |
| User sleep | 1.000117 | 0.000341 | 0.000000 |
| User sleep | 1.000089 | 0.000313 | 0.000000 |
| User sleep | 1.000085 | 0.000272 | 0.000000 |
| User sleep | 1.000109 | 0.000297 | 0.000000 |
| end | 0.000014 | 0.000009 | 0.000000 |
| query end | 0.000009 | 0.000009 | 0.000000 |
| closing tables | 0.000009 | 0.000009 | 0.000000 |
| freeing items | 0.000020 | 0.000020 | 0.000000 |
| logging slow query | 0.000069 | 0.000069 | 0.000000 |
| cleaning up | 0.000018 | 0.000018 | 0.000000 |
+----------------------+----------+----------+------------+
20 rows in set
Time: 0.009s
慢查询分析工具：musqldumpslow
mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
--verbose verbose
--debug debug
--help write this text to standard output
-v verbose
-d debug
-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default
al: average lock time
ar: average rows sent
at: average query time
c: count
l: lock time
r: rows sent
t: query time
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries
-a don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don't subtract lock time from total time
# 实例
mysqldumpslow -s c -t 2 /data/mysql/logs/mysqlslow.log
Reading mysql slow query log from /data/mysql/logs/mysqlslow.log
Count: 4 Time=0.03s (0s) Lock=0.00s (0s) Rows=0.8 (3), root[root]@localhost
select * from db1.testlog where age = N
Count: 4 Time=0.31s (1s) Lock=0.00s (0s) Rows=25000.0 (100000), root[root]@localhost
select * from testlog
二进制日志
记录导致数据改变或潜在导致数据改变的
SQL语句，SELECT语句是不记录在二进制日志文件中
记录已提交的日志
不依赖于存储引擎类型功能 通过
重放日志文件中的时间来生成数据副本
注意 建议二进制日志和数据文件分开存放
二进制日志记录三种格式
基于
语句记录：statement记录语句，默认模式（MariaDB 10.2.3版本以下），日志量较少
基于行记录：row记录数据，日志量较大，更加安全，建议使用格式
混合模式：mixed让系统自行判定该基于哪种方式进行，默认模式（MariaDB 10.2.4版本以上）
格式配置
--- 查看当前记录格式
11:22:13 (root@(none)) [hellodb]> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set
Time: 0.017s
二进制日志文件的构成
日志文件：
mysql｜mariadb-bin.文件名后缀，二进制格式；eg：on.00001,mariadb-bin.000002
索引文件：mysql｜mariadb-bin.index，文本格式；记录当前已有的二进制日志文件列表
二进制日志相关的服务器变量
--- 是否记录二进制日志，默认ON，支持动态修改，系统变量，而非服务器选项
sql_log_bin = ON | OFF
--- 指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项都开启才可以
--- 只能在配置文件中修改，并设置路径；eg：log-bin=/data/mysql/logs/mysql-bin
log_bin = /path/to/path/BIN_LOG_FILE
--- 二进制日志记录的格式
binlog_format = STATEMENT｜ROW｜MIXED
--- 单个二进制日志文件的最大体积，到达最大值会自动滚动，默认是为1G；
--- 说明：文件达到上限时的大小未必为指定的精确值
max_binlog_size = 524288000
--- 指定在每次事务中保存二进制日志更改记录的缓存大小（每次连接）
binlog_cache_size = 4m
--- 限制用于缓存多事务查询的字节大小
max_binlog_cache_size = 512m
--- 指定是否启动二进制日志即时同步磁盘功能，默认为0，由操作系统负责同步日志到磁盘
sync_binlog = 0｜1
--- 二进制日志可以自动删除的天数；默认为0，即不自动删除
expire_logs_days = N
二进制日志相关配置
--- 查看MariaDB自行管理使用中的二进制日志文件列表及大小
SHOW <BINARY｜MASTER> LOGS;
--- 查看使用中的二进制日志文件
SHOW MASTER STATUS;
--- 在线查看二进制文件中的指定内容
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
--- 范例：
--- show binlog events; 默认查看mysql-bin.000001日志文件的具体信息
09:43:05 (root@(none)) [hellodb]> show binlog events;
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.24-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000001 | 154 | Stop | 1 | 177 | |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
3 rows in set
Time: 0.009s
09:43:36 (root@(none)) [hellodb]> show binlog events in 'mysql-bin.000001' from 123 limit 1,2;
+------------------+-----+------------+-----------+-------------+------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+------------+-----------+-------------+------+
| mysql-bin.000001 | 154 | Stop | 1 | 177 | |
+------------------+-----+------------+-----------+-------------+------+
1 row in set
Time: 0.009s
--- 查看mysql-bin.000027日志文件的具体信息
09:47:38 (root@(none)) [hellodb]> show binlog events in 'mysql-bin.000027';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000027 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.24-log, Binlog ver: 4 |
| mysql-bin.000027 | 123 | Previous_gtids | 1 | 154 | |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set
Time: 0.009s
--- 切换日志类型后，记录日志的变化
09:47:38 (root@(none)) [hellodb]> show binlog events in 'mysql-bin.000027';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000027 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.24-log, Binlog ver: 4 |
| mysql-bin.000027 | 123 | Previous_gtids | 1 | 154 | |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set
Time: 0.009s
09:48:03 (root@(none)) [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 27 | F |
+-----+---------------+-----+--------+
4 rows in set
Time: 0.009s
09:48:31 (root@(none)) [hellodb]> update teachers set age = 36 where tid=4;
Query OK, 1 row affected
Time: 0.038s
09:48:32 (root@(none)) [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 36 | F |
+-----+---------------+-----+--------+
4 rows in set
Time: 0.009s
09:48:39 (root@(none)) [hellodb]> show binlog events in 'mysql-bin.000027';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000027 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.24-log, Binlog ver: 4 |
| mysql-bin.000027 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000027 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000027 | 219 | Query | 1 | 294 | BEGIN |
| mysql-bin.000027 | 294 | Table_map | 1 | 355 | table_id: 116 (hellodb.teachers) |
| mysql-bin.000027 | 355 | Update_rows | 1 | 429 | table_id: 116 flags: STMT_END_F |
| mysql-bin.000027 | 429 | Query | 1 | 505 | COMMIT |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
7 rows in set
Time: 0.009s
09:49:51 (root@(none)) [hellodb]> set binlog_format = 'MIXED';
Query OK, 0 rows affected
Time: 0.001s
09:50:03 (root@(none)) [hellodb]> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set
Time: 0.010s
09:50:14 (root@(none)) [hellodb]> update teachers set age = 98 where tid=4;
Query OK, 1 row affected
Time: 0.012s
09:50:17 (root@(none)) [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 98 | F |
+-----+---------------+-----+--------+
4 rows in set
Time: 0.009s
09:50:20 (root@(none)) [hellodb]> show binlog events in 'mysql-bin.000027';
+------------------+-----+----------------+-----------+-------------+---------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------------------------+
| mysql-bin.000027 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.24-log, Binlog ver: 4 |
| mysql-bin.000027 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000027 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000027 | 219 | Query | 1 | 294 | BEGIN |
| mysql-bin.000027 | 294 | Table_map | 1 | 355 | table_id: 116 (hellodb.teachers) |
| mysql-bin.000027 | 355 | Update_rows | 1 | 429 | table_id: 116 flags: STMT_END_F |
| mysql-bin.000027 | 429 | Query | 1 | 505 | COMMIT |
| mysql-bin.000027 | 505 | Anonymous_Gtid | 1 | 570 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000027 | 570 | Query | 1 | 655 | BEGIN |
| mysql-bin.000027 | 655 | Query | 1 | 775 | use `hellodb`; update teachers set age = 98 where tid=4 |
| mysql-bin.000027 | 775 | Query | 1 | 861 | COMMIT |
+------------------+-----+----------------+-----------+-------------+---------------------------------------------------------+
11 rows in set
Time: 0.009s
查看二进制日志工具
mysqlbinlog二进制日志的客户端命令工具，支持离线查看二进制日志
命令格式
mysqlbinlog [options] log_file ...
· --start-position=N, -j N # 指定开始位置
· --stop-position=N
· --start-datetime=datetime # 指定开始时间；时间格式：YYYY-MM-DD hh:mm:ss
· --stop-datetime=datetime
· --base64-output[= name] # 输出形式
· --verbose, -v, -vvv
范例
# 修改数据库中的内容
######################################################################
# 10:00:06 (root@(none)) [hellodb]> select * from teachers;
# +-----+---------------+-----+--------+
# | TID | Name | Age | Gender |
# +-----+---------------+-----+--------+
# | 1 | Song Jiang | 45 | M |
# | 2 | Zhang Sanfeng | 94 | M |
# | 3 | Miejue Shitai | 77 | F |
# | 4 | Lin Chaoying | 98 | F |
# +-----+---------------+-----+--------+
#
# 4 rows in set
# Time: 0.009s
# 10:00:27 (root@(none)) [hellodb]> update teachers set age = 66;
# You're about to run a destructive command.
# Do you want to proceed? (y/n): y
# Your call!
# Query OK, 4 rows affected
# Time: 0.026s
# 10:00:30 (root@(none)) [hellodb]> select * from teachers;
# +-----+---------------+-----+--------+
# | TID | Name | Age | Gender |
# +-----+---------------+-----+--------+
# | 1 | Song Jiang | 66 | M |
# | 2 | Zhang Sanfeng | 66 | M |
# | 3 | Miejue Shitai | 66 | F |
# | 4 | Lin Chaoying | 66 | F |
# +-----+---------------+-----+--------+
#
# 4 rows in set
# Time: 0.009s
######################################################################
# 查看从2021-04-09 09:30:00到2021-04-09 10:01:00时间段的二进制日志文件内容
mysqlbinlog --no-defaults --start-datetime '2021-04-09 09:30:00' --stop-datetime '2021-04-09 10:01:00' /data/mysql/logs/mysql-bin.000027
######################################################################
# /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
# /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
# DELIMITER /*!*/;
# # at 4
# #210408 14:55:41 server id 1 end_log_pos 123 CRC32 0x436fa0d3 Start: binlog v 4, server v 5.7.24-log created 210408 14:55:41
# # Warning: this binlog is either in use or was not closed properly.
# BINLOG '
# 7ahuYA8BAAAAdwAAAHsAAAABAAQANS43LjI0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
# AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
# AdOgb0M=
# '/*!*/;
# # at 154
# #210409 9:48:31 server id 1 end_log_pos 219 CRC32 0x675a0916 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes
# /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# # at 219
# #210409 9:48:31 server id 1 end_log_pos 294 CRC32 0xf5a997bb Query thread_id=69 exec_time=0 error_code=0
# SET TIMESTAMP=1617932911/*!*/;
# SET @@session.pseudo_thread_id=69/*!*/;
# SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
# SET @@session.sql_mode=1436549152/*!*/;
# SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
# /*!\C utf8mb4 *//*!*/;
# SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/;
# SET @@session.lc_time_names=0/*!*/;
# SET @@session.collation_database=DEFAULT/*!*/;
# BEGIN
# /*!*/;
# # at 294
# #210409 9:48:31 server id 1 end_log_pos 355 CRC32 0xac6a97c5 Table_map: `hellodb`.`teachers` mapped to number 116
# # at 355
# #210409 9:48:31 server id 1 end_log_pos 429 CRC32 0x80d56e2b Update_rows: table id 116 flags: STMT_END_F
#
# BINLOG '
# b7JvYBMBAAAAPQAAAGMBAAAAAHQAAAAAAAEAB2hlbGxvZGIACHRlYWNoZXJzAAQCDwH+BCwB9wEI
# xZdqrA==
# b7JvYB8BAAAASgAAAK0BAAAAAHQAAAAAAAEAAgAE///wBAAMAExpbiBDaGFveWluZxsB8AQADABM
# aW4gQ2hhb3lpbmckAStu1YA=
# '/*!*/;
# # at 429
# #210409 9:48:31 server id 1 end_log_pos 505 CRC32 0x765ec7a9 Query thread_id=69 exec_time=0 error_code=0
# SET TIMESTAMP=1617932911/*!*/;
# COMMIT
# /*!*/;
# # at 505
# #210409 9:50:14 server id 1 end_log_pos 570 CRC32 0xc26c53bc Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no
# SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# # at 570
# #210409 9:50:14 server id 1 end_log_pos 655 CRC32 0x93b20f0a Query thread_id=69 exec_time=0 error_code=0
# SET TIMESTAMP=1617933014/*!*/;
# BEGIN
# /*!*/;
# # at 655
# #210409 9:50:14 server id 1 end_log_pos 775 CRC32 0x2c3c0588 Query thread_id=69 exec_time=0 error_code=0
# use `hellodb`/*!*/;
# SET TIMESTAMP=1617933014/*!*/;
# update teachers set age = 98 where tid=4
# /*!*/;
# # at 775
# #210409 9:50:14 server id 1 end_log_pos 861 CRC32 0xd46c60b1 Query thread_id=69 exec_time=0 error_code=0
# SET TIMESTAMP=1617933014/*!*/;
# COMMIT
# /*!*/;
# # at 861
# #210409 10:00:28 server id 1 end_log_pos 926 CRC32 0x98a77164 Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=no
# SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# # at 926
# #210409 10:00:28 server id 1 end_log_pos 1011 CRC32 0x095b7933 Query thread_id=69 exec_time=0 error_code=0
# SET TIMESTAMP=1617933628/*!*/;
# BEGIN
# /*!*/;
# # at 1011
# #210409 10:00:28 server id 1 end_log_pos 1119 CRC32 0x4bd6cdcb Query thread_id=69 exec_time=0 error_code=0
# SET TIMESTAMP=1617933628/*!*/;
# update teachers set age = 66
# /*!*/;
# # at 1119
# #210409 10:00:28 server id 1 end_log_pos 1205 CRC32 0xe35b9f6b Query thread_id=69 exec_time=0 error_code=0
# SET TIMESTAMP=1617933628/*!*/;
# 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*/;
######################################################################
# 查看从1011post点以后的二进制日志文件内容
mysqlbinlog --no-defaults --start-position=1011 /data/mysql/logs/mysql-bin.000027
######################################################################
# /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
# /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
# DELIMITER /*!*/;
# # at 4
# #210408 14:55:41 server id 1 end_log_pos 123 CRC32 0x436fa0d3 Start: binlog v 4, server v 5.7.24-log created 210408 14:55:41
# # Warning: this binlog is either in use or was not closed properly.
# BINLOG '
# 7ahuYA8BAAAAdwAAAHsAAAABAAQANS43LjI0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
# AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
# AdOgb0M=
# '/*!*/;
# # at 1011
# #210409 10:00:28 server id 1 end_log_pos 1119 CRC32 0x4bd6cdcb Query thread_id=69 exec_time=0 error_code=0
# use `hellodb`/*!*/;
# SET TIMESTAMP=1617933628/*!*/;
# SET @@session.pseudo_thread_id=69/*!*/;
# SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
# SET @@session.sql_mode=1436549152/*!*/;
# SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
# /*!\C utf8mb4 *//*!*/;
# SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/;
# SET @@session.lc_time_names=0/*!*/;
# SET @@session.collation_database=DEFAULT/*!*/;
# update teachers set age = 66
# /*!*/;
# # at 1119
# #210409 10:00:28 server id 1 end_log_pos 1205 CRC32 0xe35b9f6b Query thread_id=69 exec_time=0 error_code=0
# SET TIMESTAMP=1617933628/*!*/;
# 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*/;
######################################################################
# 查看从219到505节点上二进制日志文件的详细内容
# 其中因为之前设置的为row类型，所以会详细的记录了tid为1这行的记录改变
mysqlbinlog --no-defaults --start-position=219 --stop-position=505 /data/mysql/logs/mysql-bin.000027 -v
######################################################################
# /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
# /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
# DELIMITER /*!*/;
# # at 4
# #210408 14:55:41 server id 1 end_log_pos 123 CRC32 0x436fa0d3 Start: binlog v 4, server v 5.7.24-log created 210408 14:55:41
# # Warning: this binlog is either in use or was not closed properly.
# BINLOG '
# 7ahuYA8BAAAAdwAAAHsAAAABAAQANS43LjI0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
# AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
# AdOgb0M=
# '/*!*/;
# # at 219
# #210409 9:48:31 server id 1 end_log_pos 294 CRC32 0xf5a997bb Query thread_id=69 exec_time=0 error_code=0
# SET TIMESTAMP=1617932911/*!*/;
# SET @@session.pseudo_thread_id=69/*!*/;
# SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
# SET @@session.sql_mode=1436549152/*!*/;
# SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
# /*!\C utf8mb4 *//*!*/;
# SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/;
# SET @@session.lc_time_names=0/*!*/;
# SET @@session.collation_database=DEFAULT/*!*/;
# BEGIN
# /*!*/;
# # at 294
# #210409 9:48:31 server id 1 end_log_pos 355 CRC32 0xac6a97c5 Table_map: `hellodb`.`teachers` mapped to number 116
# # at 355
# #210409 9:48:31 server id 1 end_log_pos 429 CRC32 0x80d56e2b Update_rows: table id 116 flags: STMT_END_F
#
# BINLOG '
# b7JvYBMBAAAAPQAAAGMBAAAAAHQAAAAAAAEAB2hlbGxvZGIACHRlYWNoZXJzAAQCDwH+BCwB9wEI
# xZdqrA==
# b7JvYB8BAAAASgAAAK0BAAAAAHQAAAAAAAEAAgAE///wBAAMAExpbiBDaGFveWluZxsB8AQADABM
# aW4gQ2hhb3lpbmckAStu1YA=
# '/*!*/;
# ### UPDATE `hellodb`.`teachers`
# ### WHERE
# ### @1=4
# ### @2='Lin Chaoying'
# ### @3=27
# ### @4=1
# ### SET
# ### @1=4
# ### @2='Lin Chaoying'
# ### @3=36
# ### @4=1
# # at 429
# #210409 9:48:31 server id 1 end_log_pos 505 CRC32 0x765ec7a9 Query thread_id=69 exec_time=0 error_code=0
# SET TIMESTAMP=1617932911/*!*/;
# 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*/;
######################################################################
# 扩展：使用mysqlbinlog查看日志，遇到这样的报错；原因是mysqlbinlog工具无法识别default-character-set=utf8mb4指令，添加--no-defaults即可
mysqlbinlog --start-position=678 --stop-position=752 /data/mysql/logs/mysql-bin.000001
mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8mb4'
mysqlbinlog --no-defaults --start-position=678 --stop-position=752 /data/mysql/logs/mysql-bin.000001
二进制日志事件的格式
# at 4
#210320 6:55:41（事件发生的日期和时间） server id 1（事件发生的服务器标识） end_log_pos 123（事件的结束位置） CRC32 0xd4f43452 Start: binlog v 4, server v 5.7.24-log created 210320 6:55:41 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
7StVYA8BAAAAdwAAAHsAAAABAAQANS43LjI0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADtK1VgEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AVI09NQ=
'/*!*/;
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*/;
清楚指定二进制日志
PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }
范例
--- 删除mysql-bin.000023之前的日志
PURGE BINARY LOGS TO 'mysql-bin.000023';
--- 删除指定时间前的日志
PURGE BINARY LOGS BEFORE '2021-04-07';
PURGE BINARY LOGS BEFORE '2021-04-06 09:25:15';
删除所有二进制日志，index文件重新记数
--- 删除所有二进制日志文件，并重新生成日志文件，文件名从#开始记数，默认从1开始
--- 一般是master主机第一次启动时执行； MariaDB 10.1.6开始支持TO #
RESET MASTER [TO #];
切换日志文件
FLUSH LOGS;
实例
# 切换二进制日志文件
mysqladmin flush-binary-log
mysqladmin flush-logs
# 进入mysql中，执行
14:55:41 (root@(none)) [hellodb]> flush logs;
Query OK, 0 rows affected
Time: 0.152s
# 查看二进制日志文件，从26切换到27了
ll /data/mysql/logs/mysql-bin.*
-rw-r----- 1 mysql mysql 76M Apr 8 14:55 mysql-bin.000026
-rw-r----- 1 mysql mysql 154 Apr 8 14:55 mysql-bin.000027
1958
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
