1 MySQL架构和性能优化
1.1 存储引擎
MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力，此种技术称为存储引擎，MySQL 支持多种存储引擎，其中**==目前应用最广泛的是InnoDB和MyISAM两种。==**
官方参考资料：https://docs.oracle.com/cd/E17952_01/mysql-5.7-en/storage-engines.html
1.1.1 MyISAM存储引擎
MyISAM引擎特点
- 不支持事务
- 表级锁定
- 读写相互阻塞，写入不能读，读时不能写
- 只缓存索引
- 不支持外键约束
- 不支持聚簇索引
- 读取数据较快，占用资源较少
- 不支持MVCC（多版本并发控制机制）高并发
- 崩溃恢复性较差
- MySQL5.5.5前默认的数据库引擎
MyISAM存储引擎适用场景
- 只读（或者写较少）
- 表较小（可以接受长时间进行修复操作）
MyISAM引擎文件
- tbl_name.frm 表格式定义
- tbl_name.MYD 数据文件
- tbl_name.MYI 索引文件
1.1.2 InnoDB引擎
InnoDB引擎特点
- 行级锁
- 支持事务，适合处理大量短期事务
- 读写阻塞与事务隔离级别相关
- 可缓存数据和索引
- 支持聚簇索引
- 崩溃恢复性更好
- 支持MVCC高并发
- 从MySQL5.5后支持全文索引
- 从MySQL5.5.5开始为默认的数据库引擎
InnoDB数据库文件
- 所有InnoDB表的数据和索引放置于同一个表空间中
数据文件：ibdata1, ibdata2，存放在datadir定义的目录下
表格式定义：tb_name.frm，存放在datadir定义的每个数据库对应的目录下
#mariadb5.5版本
[root@c7-mysql ~]# mysql -uroot -S /mysql/3306/socket/mysql.sock
Server version: 5.5.68-MariaDB MariaDB Server
#默认innodb_file_per_table=off
MariaDB [testdb]> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF |
+-----------------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> create database testdb;
Query OK, 1 row affected (0.01 sec)
MariaDB [(none)]> use testdb;
Database changed
MariaDB [testdb]> create table test01 (id int auto_increment primary key,name varchar(20) not null);
Query OK, 0 rows affected (0.13 sec)
[root@c7-mysql ~]# ll /mysql/3306/data/
-rw-rw---- 1 mysql mysql 18874368 Jun 8 13:33 ibdata1 #数据文件，创建表test01，时间更新了
[root@c7-mysql ~]# ll /mysql/3306/data/testdb/
-rw-rw---- 1 mysql mysql 8586 Jun 8 13:33 test01.frm #表定义
- 每个表单独使用一个表空间存储表的数据和索引
两类文件放在对应每个数据库独立目录中
数据文件(存储数据和索引)：tb_name.ibd
表格式定义：tb_name.frm
#mariadb10.3版本
[root@c8-mysql ~]# mysql -uroot -pmagedu -S /mysql/3306/socket/mysql.sock
Server version: 10.3.17-MariaDB MariaDB Server
MariaDB [(none)]> show variables like 'innodb_file_per_table'; #默认开启
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.015 sec)
MariaDB [(none)]> use testdb;
Database changed
MariaDB [testdb]> create table t1 (id int auto_increment primary key,name varchar(20) not null);
Query OK, 0 rows affected (0.13 sec)
[root@c8-mysql ~]# ll /mysql/3306/data/testdb
-rw-rw---- 1 mysql mysql 922 Jun 8 13:56 t1.frm
-rw-rw---- 1 mysql mysql 98304 Jun 8 13:56 t1.ibd
启用：innodb_file_per_table=ON (MariaDB 5.5以后版是默认值)
参看：https://mariadb.com/kb/en/library/xtradbinnodb-server-system-variables/#innodb_file_per_table
1.1.3 管理存储引擎
查看mysql支持的存储引擎
MariaDB [(none)]> show engines;
+--------------------+---------+---------------------------------------------------------
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+---------------------------------------------------------
| MyISAM | YES | Non-transactional engine with | NO | NO | NO |
good performance and small data footprint
| InnoDB | DEFAULT | Supports transactions, | YES | YES | YES |
row-level locking, foreign
keys and encryption for tables
...其他省略...
+--------------------+---------+---------------------------------------------------------
11 rows in set (0.013 sec)
查看当前默认的存储引擎
show variables like '%storage_engine%';
MariaDB [(none)]> show variables like 'storage_engin%';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | InnoDB | #默认的存储引擎是innodb
+----------------+--------+
1 row in set (0.004 sec)
设置默认的存储引擎
#旧版本编辑vim /etc/my.cnf，新版本编辑/etc/my.cnf.d/mariadb-server.cnf
[mysqld]
default_storage_engine= InnoDB #忽略大小写
查看库中所有表使用的存储引擎
show table status from db_name;
MariaDB [(none)]> show table status from testdb\G;
*************************** 1. row ***************************
Name: student
Engine: InnoDB
*************************** 2. row ***************************
Name: t1
Engine: InnoDB
...省略...
2 rows in set (0.002 sec)
查看库中指定表的存储引擎
MariaDB [(none)]> use testdb;
MariaDB [testdb]> show table status like 't2';
+------+--------+---------+------------+------+----------------+-------------+-----------| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | Max_index_length | Temporary |
+------+--------+---------+------------+------+----------------+-------------+-----------| t2 | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | 4294967296 | 2021-05-27 16:07:56 | NULL | NULL | latin1_swedish_ci | NULL | | | 0 | N |
+------+--------+---------+------------+------+----------------+-------------+----------
1 row in set (0.001 sec)
MariaDB [testdb]> show create table t2;
+-------+--------------------------------------------------------------------------------
| Table | Create Table |
+-------+--------------------------------------------------------------------------------| t2 | CREATE TABLE `t2` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967296 DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------
1 row in set (0.006 sec)
设置表的存储引擎
#修改表t2的存储引擎
MariaDB [testdb]> alter table t2 engine=myisam;
Query OK, 1 row affected (0.203 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [testdb]> show table status like 't2'\G;
*************************** 1. row ***************************
Name: t2
Engine: MyISAM
1 row in set (0.004 sec)
2 MySQL查询缓存
2.1 查询缓存原理
查询缓存原理
缓存SELECT操作或预处理查询的结果集和SQL语句，当有新的SELECT语句或预处理查询语句请求，先去查询缓存，判断是否存在可用的记录集，判断标准：与缓存的SQL语句，是否完全一样，区分大小写
优缺点
- 不需要对SQL语句做任何解析和执行，当然语法解析必须通过在先，直接从Query Cache中获得查询结果，提高查询性能
- 查询缓存的判断规则，不够智能，也即提高了查询缓存的使用门槛，降低效率
- 查询缓存的使用，会增加检查和清理Query Cache中记录集的开销
2.1.1 查询缓存相关的服务器变量
- 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
2.1.2 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语句才会缓存；其它均不予缓存
官方帮助： https://mariadb.com/kb/en/library/server-system-variables/#query_cache_type https://dev.mysql.com/doc/refman/5.7/en/query-cache-configuration.html
范例：把type和size加入到cnf配置文件中
[root@c8-mysql ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
query_cache_type=on
query_cache_size=200M
[root@c8-mysql ~]# systemctl restart mariadb
[root@c8-mysql ~]# mysql
MariaDB [(none)]> show variables like 'query%'; #size已经修改为200M了
+------------------------------+-----------+
| Variable_name | Value |
+------------------------------+-----------+
| query_alloc_block_size | 16384 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 104857600 |
| query_cache_strip_comments | OFF |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 24576 |
+------------------------------+-----------+
8 rows in set (0.009 sec)
2.1.3 查询缓存相关的状态变量
- Qcache_free_blocks：处于空闲状态 Query Cache中内存 Block 数
- Qcache_total_blocks：Query Cache 中总Block ，当Qcache_free_blocks相对此值较大时，可能用内存碎片，执行FLUSH QUERY CACHE清理碎片
- Qcache_free_memory：处于空闲状态的 Query Cache 内存总量
- Qcache_hits：Query 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_type=on和query_cache_size=104857600
#或根据上面的例子放在配置文件中
MariaDB [hellodb]> set global query_cache_type=on;
MariaDB [hellodb]> set global query_cache_size=104857600;
MariaDB [(none)]> show variables like 'query%';
+------------------------------+-----------+
| Variable_name | Value |
+------------------------------+-----------+
| query_alloc_block_size | 16384 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 104857600 |
| query_cache_strip_comments | OFF |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 24576 |
+------------------------------+-----------+
8 rows in set (0.009 sec)
#初次hits、inserts、in_cache都为0
MariaDB [(none)]> show global status like 'Qcache%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 104839760 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+-----------+
8 rows in set (0.005 sec)
MariaDB [hellodb]> select * from students where stuid=1;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
+-------+-------------+-----+--------+---------+-----------+
1 row in set (0.005 sec)
MariaDB [hellodb]> show global status like 'Qcache%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 104838224 |
| Qcache_hits | 0 | #命中记录0
| Qcache_inserts | 1 | #插入1条新记录到缓存
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2 | #没有被cache的记录
| Qcache_queries_in_cache | 1 | #缓存中增加1
| Qcache_total_blocks | 4 |
+-------------------------+-----------+
8 rows in set (0.006 sec)
MariaDB [hellodb]> select * from students where stuid=2;
+-------+------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+------------+-----+--------+---------+-----------+
| 2 | Shi Potian | 22 | M | 1 | 7 |
+-------+------------+-----+--------+---------+-----------+
1 row in set (0.003 sec)
MariaDB [hellodb]> show global status like 'Qcache%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 104837200 |
| Qcache_hits | 0 | #命中记录0
| Qcache_inserts | 2 | #又插入1条新记录到缓存
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2 | #没有被cache的记录
| Qcache_queries_in_cache | 2 | #缓存中增加1
| Qcache_total_blocks | 6 |
+-------------------------+-----------+
8 rows in set (0.004 sec)
MariaDB [hellodb]> select * from students where stuid=1;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
+-------+-------------+-----+--------+---------+-----------+
1 row in set (0.001 sec)
MariaDB [hellodb]> show global status like 'Qcache%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 104837200 |
| Qcache_hits | 1 | #缓存中查询到该记录，命中记录1
| Qcache_inserts | 2 | #无新插入记录
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2 | #没有被cache的记录
| Qcache_queries_in_cache | 2 | #缓存中未增加
| Qcache_total_blocks | 6 |
+-------------------------+-----------+
8 rows in set (0.004 sec)
MariaDB [hellodb]> select * from students where stuid=1; #语句中添加多个空格或有大小写不同的情况，都视为一条新记录
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
+-------+-------------+-----+--------+---------+-----------+
1 row in set (0.003 sec)
MariaDB [hellodb]> show global status like 'Qcache%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 104836176 |
| Qcache_hits | 1 |
| Qcache_inserts | 3 | #视为一条新记录，插入到缓存
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2 |
| Qcache_queries_in_cache | 3 | #缓存中增加1
| Qcache_total_blocks | 8 |
+-------------------------+-----------+
8 rows in set (0.008 sec)
MariaDB [hellodb]> select * from students where stuid=1 ; #语句的句首和末尾添加多个空格，视为同一条记录
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
+-------+-------------+-----+--------+---------+-----------+
1 row in set (0.000 sec)
MariaDB [hellodb]> show global status like 'Qcache%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 104836176 |
| Qcache_hits | 2 | #缓存中再次查询到该记录，命中记录加1
| Qcache_inserts | 3 | #视为同一条记录，不增加
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2 |
| Qcache_queries_in_cache | 3 | #缓存中未增加
| Qcache_total_blocks | 8 |
+-------------------------+-----------+
8 rows in set (0.005 sec)
MariaDB [hellodb]> select * from students where stuid=1;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
+-------+-------------+-----+--------+---------+-----------+
1 row in set (0.000 sec)
MariaDB [hellodb]> show global status like 'Qcache%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 104836176 |
| Qcache_hits | 3 | #缓存中再次查询到该记录，命中记录加1
| Qcache_inserts | 3 | #视为同一条记录，不增加
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2 |
| Qcache_queries_in_cache | 3 | #缓存中未增加
| Qcache_total_blocks | 8 |
+-------------------------+-----------+
8 rows in set (0.003 sec)
#查询命令一般都是在脚本代码中，不会发生改变，要是多个脚本用了同一条查询命令，但中间有多个空格或大小写不一致，就属于不同的查询记录，需要监控cache的使用情况
2.1.4 命中率和内存使用率估算
查询缓存中内存块的最小分配单位query_cache_min_res_unit ：
(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
查询缓存命中率 ：
Qcache_hits / ( Qcache_hits + Qcache_inserts ) * 100%
#如上面例子中的命中率是
3/（3+3）+100%=50%
查询缓存内存使用率：
(query_cache_size – qcache_free_memory) / query_cache_size * 100%
2.1.5 查询的优化
1、如果命中率能接受，就不用优化了；
2、查询缓存query_cache_limit是否够大，是否是影响查询的因素，可以适当增加query_cache_limit查询缓存的大小；
3、查询缓存中碎片是否过多，需要调整query_cache_min_res_unit的值，默认4k，可以再调小一些；
4、或者内存过低，需要增加query_cache_size的大小；
5、查询结果过大而无法缓存的语句，可使用SQL_NO_CACHE；
3 MySQL各种日志管理
3.1 日志管理
MySQL 支持丰富的日志类型，如下：
- 事务日志：transaction log
- 事务日志的写入类型为“追加”，因此其操作为“顺序IO”；通常也被称为：预写式日志 write ahead logging
- 事务日志文件： ib_logfile0， ib_logfile1
- 错误日志 error log
- 通用日志 general log
- 慢查询日志 slow query log
- 二进制日志 binary log
- 中继日志 reley log，在主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件
3.2 事务日志
事务日志：transaction log
事务型存储引擎自行管理和使用，建议和数据文件分开存放，redo log和undo log
Innodb事务日志相关配置：
show variables like '%innodb_log%';
innodb_log_file_size 50331648 #每个日志文件大小
innodb_log_files_in_group 2 #日志组成员个数
innodb_log_group_home_dir ./ #事务文件路径
innodb_flush_log_at_trx_commit #默认为1
MariaDB [hellodb]> show variables like '%innodb_log%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| innodb_log_buffer_size | 16777216 | #缓存大小
| innodb_log_checksums | ON |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 50331648 | #日志文件大小（大一些，如200M）
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_log_optimize_ddl | OFF |
| innodb_log_write_ahead_size | 8192 |
+-----------------------------+----------+
8 rows in set (0.001 sec)
范例：
#默认的事务日志存放路径
[root@c8-mysql ~]# ll /var/lib/mysql
-rw-rw---- 1 mysql mysql 50331648 Jun 20 10:41 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Jun 19 16:01 ib_logfile1
#自定义日志文件路径
[root@c8-mysql ~]# vi /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
innodb_log_group_home_dir=/data/trans_log
innodb_log_file_size=200M
innodb_log_files_in_group=3
[root@c8-mysql ~]# mkdir /data/trans_log
[root@c8-mysql ~]# chown -R mysql.mysql /data/trans_log/
[root@c8-mysql ~]# systemctl restart mariadb
[root@c8-mysql ~]# ll /data/trans_log/
total 614400
-rw-rw---- 1 mysql mysql 209715200 Jun 20 10:43 ib_logfile0
-rw-rw---- 1 mysql mysql 209715200 Jun 20 10:43 ib_logfile1
-rw-rw---- 1 mysql mysql 209715200 Jun 20 10:43 ib_logfile2
MariaDB [hellodb]> show variables like '%innodb_log%';
+-----------------------------+-----------------+
| Variable_name | Value |
+-----------------------------+-----------------+
| innodb_log_buffer_size | 16777216 |
| innodb_log_checksums | ON |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 209715200 |
| innodb_log_files_in_group | 3 |
| innodb_log_group_home_dir | /data/trans_log |
| innodb_log_optimize_ddl | OFF |
| innodb_log_write_ahead_size | 8192 |
+-----------------------------+-----------------+
8 rows in set (0.001 sec)
事务日志性能优化
innodb_flush_log_at_trx_commit=0|1|2
1 此为默认值，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。 这是完全遵守ACID特性 0 提交时没有写磁盘的操作; 而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。 这样可提供更好的性能，但服务器崩溃可能丢失最后一秒的事务 2 每次提交后都会写入OS的缓冲区，但每秒才会进行一次刷新到磁盘文件中。 性能比0略差一些，但操作系统或停电可能导致最后一秒的交易丢失
说明：
设置为1，同时sync_binlog = 1表示最高级别的容错
下面分别是innodb_flush_log_at_trx_commit为1、0和2时的磁盘IO值
MariaDB [hellodb]> show variables like 'innodb_flush_log_at_trx%';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1 |
+--------------------------------+-------+
1 row in set (0.001 sec)
MariaDB [hellodb]> select @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
| 1 |
+----------------------------------+
1 row in set (0.000 sec)
MariaDB [hellodb]> call sp_test_log;
Query OK, 99999 rows affected (31.033 sec)
#占用磁盘IO时间长，数据一边执行一边写入磁盘
Total DISK READ : 0.00 B/s | Total DISK WRITE : 14.59 M/s
Actual DISK READ: 0.00 B/s | Actual DISK WRITE: 15.62 M/s
TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND
3846 be/4 mysql 0.00 B/s 14.01 M/s 0.00 % 28.05 % mysqld --basedir=/usr
MariaDB [hellodb]> set global innodb_flush_log_at_trx_commit=0;
Query OK, 0 rows affected (0.001 sec)
MariaDB [hellodb]> select @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
| 0 |
+----------------------------------+
1 row in set (0.000 sec)
MariaDB [hellodb]> call sp_test_log;
Query OK, 99999 rows affected (5.561 sec)
#磁盘IO占用时间短，影响较小
Total DISK READ : 0.00 B/s | Total DISK WRITE : 6.53 M/s
Actual DISK READ: 0.00 B/s | Actual DISK WRITE: 6.53 M/s
TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND
3800 be/4 mysql 0.00 B/s 4.59 M/s 0.00 % 0.34 % mysqld --basedir=/usr
MariaDB [hellodb]> set global innodb_flush_log_at_trx_commit=2;
Query OK, 0 rows affected (0.000 sec)
MariaDB [hellodb]> select @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
| 2 |
+----------------------------------+
1 row in set (0.000 sec)
MariaDB [hellodb]> call sp_test_log;
Query OK, 99999 rows affected (8.146 sec)
#磁盘IO占用时间较短，影响较小
Total DISK READ : 0.00 B/s | Total DISK WRITE : 6.10 M/s
Actual DISK READ: 0.00 B/s | Actual DISK WRITE: 6.11 M/s
TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND
3793 be/4 mysql 0.00 B/s 6.09 M/s 0.00 % 0.42 % mysqld --basedir=/usr
3.3 错误日志
错误日志
- mysqld启动和关闭过程中输出的事件信息
- mysqld运行中产生的错误信息
- event scheduler运行一个event时产生的日志信息
- 在主从复制架构中的从服务器上启动从服务器线程时产生的信息
错误文件路径
SHOW GLOBAL VARIABLES LIKE 'log_error'
范例：
MariaDB [hellodb]> show global variables like 'log_error';
+---------------+------------------------------+
| Variable_name | Value |
+---------------+------------------------------+
| log_error | /var/log/mariadb/mariadb.log |
+---------------+------------------------------+
1 row in set (0.001 sec)
记录哪些警告信息至错误日志文件
#CentOS7 mariadb 5.5 默认值为1
#CentOS8 mariadb 10.3 默认值为2
log_warnings=0|1|2|3...
官方网站帮助：https://mariadb.com/kb/en/server-system-variables/#log_warnings
log_warnings >= 2
Access denied errors.
Connections aborted or closed due to errors or timeouts.
Table handler errors
Messages related to the files used to persist replication state:
Either the default master.info file or the file that is configured by the master_info_file option.
Either the default relay-log.info file or the file that is configured by the relay_log_info_file system variable.
Information about a master's binary log dump thread.
范例：
MariaDB [hellodb]> select @@log_warnings;
+----------------+
| @@log_warnings |
+----------------+
| 2 |
+----------------+
1 row in set (0.000 sec)
MariaDB [hellodb]> show variables like 'log_warnings';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_warnings | 2 |
+---------------+-------+
1 row in set (0.001 sec)
范例：
[root@c8-mysql ~]# cat /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
innodb_log_group_home_dir=/data/trans_log
innodb_log_file_size=200M
innodb_log_files_in_group=3
character-set-server=utf8mb4
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log #错误日志文件
pid-file=/run/mariadb/mariadb.pid
[root@c8-mysql ~]# chown -R root.root /data/trans_log/ #修改权限，重启提示错误
[root@c8-mysql ~]# systemctl restart mariadb
Job for mariadb.service failed because the control process exited with error code.
See "systemctl status mariadb.service" and "journalctl -xe" for details.
[root@c8-mysql ~]# tail -f /var/log/mariadb/mariadb.log #跟踪错误日志发现信息
[ERROR] InnoDB: /data/trans_log/ib_logfile0 can't be opened in read-write mode.
3.4 通用日志
通用日志：记录对数据库的通用操作，包括错误的SQL语句 通用日志保存位置：文件中file（默认值）或表中table（mysql.general_log表） 通用日志相关设置
general_log=ON|OFF
general_log_file=HOSTNAME.log
log_output=TABLE|FILE|NONE
范例：查看通用日志设置，默认关闭
MariaDB [hellodb]> select @@general_log;
+---------------+
| @@general_log |
+---------------+
| 0 |
+---------------+
1 row in set (0.000 sec)
MariaDB [hellodb]> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log | OFF |
+---------------+-------+
1 row in set (0.002 sec)
#开启通用日志，可以临时开启，也可放在配置文件中
MariaDB [hellodb]> set global general_log=on;
Query OK, 0 rows affected (0.003 sec)
MariaDB [hellodb]> select @@general_log;
+---------------+
| @@general_log |
+---------------+
| 1 |
+---------------+
1 row in set (0.000 sec)
MariaDB [hellodb]> select @@log_output; #查看日志输出格式，是文件
+--------------+
| @@log_output |
+--------------+
| FILE |
+--------------+
1 row in set (0.001 sec)
MariaDB [hellodb]> select @@general_log_file; #查看日志文件的名称
+--------------------+
| @@general_log_file |
+--------------------+
| c8-mysql.log |
+--------------------+
1 row in set (0.000 sec)
[root@c8-mysql ~]# ll /var/lib/mysql/ -t #查看生成的日志文件（默认是"主机名.log"）
-rw-rw---- 1 mysql mysql 213 Jun 20 15:16 c8-mysql.log
#另开一个会话，跟踪数据库的操作
[root@c8-mysql ~]# tail -f /var/lib/mysql/c8-mysql.log -n0
#第一会话中，使用hellodb数据库并查看文件
MariaDB [mysql]> use hellodb;
MariaDB [hellodb]> select * from teachers where tid=4;
+-----+--------------+-----+--------+
| TID | Name | Age | Gender |
+-----+--------------+-----+--------+
| 4 | Lin Chaoying | 60 | F |
+-----+--------------+-----+--------+
1 row in set (0.001 sec)
MariaDB [hellodb]> xxx;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'xxx' at line 1
#第二会话里，能详细记录数据库的操作，无论正确还是错误的操作
[root@c8-mysql ~]# tail -f /var/lib/mysql/c8-mysql.log -n0
210620 15:22:20 9 Query SELECT DATABASE()
9 Init DB hellodb
9 Query show databases
9 Query show tables
9 Field List classes
9 Field List coc
9 Field List courses
9 Field List emp
9 Field List scores
9 Field List student_count
9 Field List student_info
9 Field List students
9 Field List teachers
9 Field List teachers2
9 Field List teachers3
9 Field List test_log
9 Field List toc
9 Field List user
9 Field List v_st_co_sc
9 Field List v_student_teacher
210620 15:22:31 9 Query select * from teachers where tid=4
210620 15:25:52 9 Query xxx
范例：使用数据库中的表来记录通用日志
#修改通用日志，记录通用日志至mysql.general_log表中
MariaDB [mysql]> select @@log_output;
+--------------+
| @@log_output |
+--------------+
| FILE |
+--------------+
1 row in set (0.000 sec)
MariaDB [mysql]> select * from general_log;
Empty set (0.000 sec)
MariaDB [mysql]> select @@log_output;
+--------------+
| @@log_output |
+--------------+
| TABLE |
+--------------+
1 row in set (0.001 sec)
MariaDB [mysql]> xxx;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'xxx' at line 1
MariaDB [mysql]> select * from general_log;
+----------------------------+---------------------------+-----------+-----------+-------
| event_time | user_host | thread_id | server_id | command_type | argument |
+----------------------------+---------------------------+-----------+-----------+-------
| 2021-06-20 15:29:48.776004|root[root]@localhost[]|9|1|Query| select @@log_output |
| 2021-06-20 15:31:07.226992|root[root]@localhost[]|9|1|Query| select * from general_log|
| 2021-06-20 15:31:18.172106|root[root]@localhost[]|9|1|Query| xxx |
+----------------------------+---------------------------+-----------+-----------+-------
3 rows in set (0.000 sec)
MariaDB [mysql]> select * from mysql.general_log\G #分别显示
范例：对访问的语句进行排序
[root@c8-mysql ~]# mysql -uroot -pmagedu -e 'select argument from mysql.general_log' |awk '{sql[$0]++}END{for (i in sql){print sql[i],i}}'|sort -nr
19 show tables
16 xxx
15 select * from general_log
10 show databases
[root@c8-mysql ~]# mysql -uroot -pmagedu -e 'select argument from mysql.general_log' |sort|uniq -c|sort -nr
19 show tables
16 xxx
15 select * from general_log
10 show databases
3.5 慢查询日志
慢查询日志：记录执行查询时长超出指定时长的操作，即查看执行的比较慢的数据库操作
慢查询相关变量
slow_query_log=ON|OFF #开启或关闭慢查询，支持全局和会话，只有全局设置才会生成慢查询文件
long_query_time=N #慢查询的阀值，单位秒，默认是10s（如果数据库需要10s才能查询到数据，那么对于用户来说觉得相应太慢了），生产中一般2~3秒（超过3s的就记录下来，看看哪些语句执行的慢）
slow_query_log_file=HOSTNAME-slow.log #慢查询日志文件
log_queries_not_using_indexes=ON #不使用索引或使用全索引扫描，不论是否达到慢查询阀值的语句是否记录日志，默认OFF，即不记录
范例：开启慢查询日志记录
MariaDB [hellodb]> select @@slow_query_log;
+------------------+
| @@slow_query_log |
+------------------+
| 0 |
+------------------+
1 row in set (0.000 sec)
MariaDB [hellodb]> set slow_query_log=1; #直接设置变量，不能生成慢日志文件
Query OK, 0 rows affected (0.000 sec)
MariaDB [hellodb]> select @@slow_query_log;
+------------------+
| @@slow_query_log |
+------------------+
| 1 |
+------------------+
1 row in set (0.000 sec)
[root@c8-mysql ~]# vi /etc/my.cnf.d/mariadb-server.cnf
[mysql]
slow_query_log
[root@c8-mysql ~]# systemctl restart mariadb
[root@c8-mysql ~]# ll /var/lib/mysql -t
-rw-rw---- 1 mysql mysql 164 Jun 20 16:04 c8-mysql-slow.log
MariaDB [hellodb]> select @@slow_query_log;
+------------------+
| @@slow_query_log |
+------------------+
| 1 |
+------------------+
1 row in set (0.000 sec)
MariaDB [hellodb]> select @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
| 10.000000 |
+-------------------+
1 row in set (0.000 sec)
MariaDB [hellodb]> select @@slow_query_log_file;
+-----------------------+
| @@slow_query_log_file |
+-----------------------+
| c8-mysql-slow.log |
+-----------------------+
1 row in set (0.001 sec)
MariaDB [hellodb]> select sleep(1) from students; #每秒执行一行，一共25行
+----------+
| sleep(1) |
+----------+
| 0 |
...省略...
| 0 |
+----------+
25 rows in set (25.013 sec)
[root@c8-mysql ~]# tail -f /var/lib/mysql/c8-mysql-slow.log
# Time: 210620 16:08:35
# User@Host: root[root] @ localhost []
# Thread_id: 8 Schema: hellodb QC_hit: No
# Query_time: 25.012945 Lock_time: 0.000218 Rows_sent: 25 Rows_examined: 25
# Rows_affected: 0 Bytes_sent: 207
use hellodb;
SET timestamp=1624176515;
select sleep(1) from students; #超过10秒，这条命令就被记录下来了
范例：log_queries_not_using_indexes
log_queries_not_using_indexes=ON #不使用索引或使用全索引扫描，不论是否达到慢查询阀值的语句是否记录日志，默认OFF，即不记录
[root@c8-mysql ~]# vi /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
slow_query_log
long_query_time=3
log_queries_not_using_indexes
[root@c8-mysql ~]# systemctl restart mariadb
MariaDB [hellodb]> select @@slow_query_log;
+------------------+
| @@slow_query_log |
+------------------+
| 1 |
+------------------+
1 row in set (0.000 sec)
MariaDB [hellodb]> select @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
| 3.000000 |
+-------------------+
1 row in set (0.000 sec)
MariaDB [hellodb]> select @@log_queries_not_using_indexes;
+---------------------------------+
| @@log_queries_not_using_indexes |
+---------------------------------+
| 1 |
+---------------------------------+
1 row in set (0.000 sec)
##查询测试，慢查询日志能记录，也能记录未使用索引的记录
MariaDB [hellodb]> select sleep(1) from teachers;
+----------+
| sleep(1) |
+----------+
| 0 |
| 0 |
| 0 |
| 0 |
+----------+
4 rows in set (4.003 sec)
MariaDB [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 | 60 | F |
+-----+---------------+-----+--------+
4 rows in set (0.001 sec)
MariaDB [hellodb]> explain select * from students where classid=2; #未使用索引
+------+-------------+----------+------+---------------+------+---------+------+------+--
| id| select_type | table| type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+------+------+--
| 1 | SIMPLE | students | ALL | NULL | NULL | NULL | NULL | 25 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+--
1 row in set (0.001 sec)
[root@c8-mysql ~]# cat /var/lib/mysql/c8-mysql-slow.log #记录在日志中
# Time: 210620 16:26:25
# User@Host: root[root] @ localhost []
# Thread_id: 8 Schema: hellodb QC_hit: No
# Query_time: 4.002233 Lock_time: 0.000106 Rows_sent: 4 Rows_examined: 4
# Rows_affected: 0 Bytes_sent: 81
use hellodb;
SET timestamp=1624177585;
select sleep(1) from teachers;
# Time: 210620 16:26:40
# User@Host: root[root] @ localhost []
# Thread_id: 8 Schema: hellodb QC_hit: No
# Query_time: 0.000171 Lock_time: 0.000059 Rows_sent: 4 Rows_examined: 4
# Rows_affected: 0 Bytes_sent: 347
SET timestamp=1624177600;
select * from teachers;
# Time: 210620 16:27:05
# User@Host: root[root] @ localhost []
# Thread_id: 8 Schema: hellodb QC_hit: No
# Query_time: 0.000517 Lock_time: 0.000254 Rows_sent: 1 Rows_examined: 0
# Rows_affected: 0 Bytes_sent: 385
SET timestamp=1624177625;
explain select * from students where classid=2;
#创建索引文件，就不会记录在慢查询日志文件中
MariaDB [hellodb]> create index idx_classid on students(classid);
Query OK, 0 rows affected (0.006 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [hellodb]> explain select * from students where classid=2;
+------+-------------+----------+------+---------------+-------------+---------+-------+-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+-------------+---------+-------+-
| 1 | SIMPLE | students | ref | idx_classid | idx_classid | 2 | const | 3 | |
+------+-------------+----------+------+---------------+-------------+---------+-------+-
1 row in set (0.001 sec)
MariaDB [hellodb]> explain select * from students where classid=3;
+------+-------------+----------+------+---------------+-------------+---------+-------+-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+-------------+---------+-------+-
| 1 | SIMPLE | students | ref | idx_classid | idx_classid | 2 | const | 4 | |
+------+-------------+----------+------+---------------+-------------+---------+-------+-
1 row in set (0.001 sec)
慢查询分析工具
mysqldumpslow -s c -t 10 /data/mysql/slow.log
[root@c8-mysql ~]# mysqldumpslow -s c -t 10 /var/lib/mysql/c8-mysql-slow.log
Reading mysql slow query log from /var/lib/mysql/c8-mysql-slow.log
Count: 2 Time=4.00s (8s) Lock=0.00s (0s) Rows_sent=4.0 (8), Rows_examined=4.0 (8), Rows_affected=0.0 (0), root[root]@localhost
select sleep(N) from teachers
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows_sent=1.0 (1), Rows_examined=0.0 (0), Rows_affected=0.0 (0), root[root]@localhost
explain select sleep(N) from teachers
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows_sent=4.0 (4), Rows_examined=4.0 (4), Rows_affected=0.0 (0), root[root]@localhost
select * from teachers
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows_sent=1.0 (1), Rows_examined=0.0 (0), Rows_affected=0.0 (0), root[root]@localhost
explain select * from students where classid=N
Count: 1 Time=25.01s (25s) Lock=0.00s (0s) Rows_sent=25.0 (25), Rows_examined=25.0 (25), Rows_affected=0.0 (0), root[root]@localhost
select sleep(N) from students
Died at /usr/bin/mysqldumpslow line 182, <> chunk 5.
3.6 使用profile工具
#打开后，会显示语句执行详细的过程
MariaDB [hellodb]> set profiling=on;
Query OK, 0 rows affected (0.000 sec)
MariaDB [hellodb]> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 1 |
+-------------+
1 row in set (0.000 sec)
#查看语句,注意结果中的query_id值
MariaDB [hellodb]> show profiles;
+----------+------------+--------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------+
| 1 | 0.00006557 | select @@profiles |
| 2 | 0.00013785 | select @@profiling |
+----------+------------+--------------------+
2 rows in set (0.001 sec)
#显示语句的详细执行步骤和时长：Show profile for query #
MariaDB [hellodb]> select sleep(1) from teachers;
+----------+
| sleep(1) |
+----------+
| 0 |
| 0 |
| 0 |
| 0 |
+----------+
4 rows in set (4.003 sec)
MariaDB [hellodb]> show profiles;
+----------+------------+---------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------+
| 1 | 0.00006557 | select @@profiles |
| 2 | 0.00013785 | select @@profiling |
| 3 | 0.00050006 | explain select sleep(1) from teachers |
| 4 | 4.00287690 | select sleep(1) from teachers |
+----------+------------+---------------------------------------+
4 rows in set (0.000 sec)
MariaDB [hellodb]> show profile for query 4;
+------------------------+----------+
| Status | Duration |
+------------------------+----------+
| Starting | 0.000060 |
| Checking permissions | 0.000006 |
| Opening tables | 0.000018 |
| After opening tables | 0.000005 |
| System lock | 0.000003 |
| Table lock | 0.000006 |
| Init | 0.000013 |
| Optimizing | 0.000007 |
| Statistics | 0.000014 |
| Preparing | 0.000015 |
| Executing | 0.000003 |
| Sending data | 0.000101 |
| User sleep | 1.000941 |
| User sleep | 1.000621 |
| User sleep | 1.000428 |
| User sleep | 1.000404 |
| End of update loop | 0.000027 |
| Query end | 0.000004 |
| Commit | 0.000070 |
| Closing tables | 0.000008 |
| Unlocking tables | 0.000005 |
| Closing tables | 0.000012 |
| Starting cleanup | 0.000004 |
| Freeing items | 0.000007 |
| Updating status | 0.000022 |
| Logging slow query | 0.000069 |
| Reset for next command | 0.000006 |
+------------------------+----------+
27 rows in set (0.000 sec)
#显示cpu使用情况：Show profile cpu for query #
MariaDB [hellodb]> show profile cpu for query 4;
+------------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+------------------------+----------+----------+------------+
| Starting | 0.000060 | 0.000015 | 0.000044 |
| Checking permissions | 0.000006 | 0.000001 | 0.000004 |
| Opening tables | 0.000018 | 0.000005 | 0.000014 |
| After opening tables | 0.000005 | 0.000001 | 0.000003 |
| System lock | 0.000003 | 0.000001 | 0.000003 |
| Table lock | 0.000006 | 0.000001 | 0.000004 |
| Init | 0.000013 | 0.000003 | 0.000010 |
| Optimizing | 0.000007 | 0.000002 | 0.000005 |
| Statistics | 0.000014 | 0.000004 | 0.000010 |
| Preparing | 0.000015 | 0.000003 | 0.000011 |
| Executing | 0.000003 | 0.000001 | 0.000002 |
| Sending data | 0.000101 | 0.000026 | 0.000077 |
| User sleep | 1.000941 | 0.000051 | 0.000150 |
| User sleep | 1.000621 | 0.000000 | 0.000440 |
| User sleep | 1.000428 | 0.000000 | 0.000508 |
| User sleep | 1.000404 | 0.000000 | 0.000331 |
| End of update loop | 0.000027 | 0.000000 | 0.000024 |
| Query end | 0.000004 | 0.000000 | 0.000004 |
| Commit | 0.000070 | 0.000000 | 0.000071 |
| Closing tables | 0.000008 | 0.000000 | 0.000008 |
| Unlocking tables | 0.000005 | 0.000000 | 0.000005 |
| Closing tables | 0.000012 | 0.000000 | 0.000012 |
| Starting cleanup | 0.000004 | 0.000000 | 0.000003 |
| Freeing items | 0.000007 | 0.000000 | 0.000008 |
| Updating status | 0.000022 | 0.000000 | 0.000022 |
| Logging slow query | 0.000069 | 0.000000 | 0.000069 |
| Reset for next command | 0.000006 | 0.000000 | 0.000005 |
+------------------------+----------+----------+------------+
27 rows in set (0.001 sec)
3.7 二进制日志(备份)
- 记录导致数据改变或潜在导致数据改变的SQL语句
- 记录已提交的日志
- 不依赖于存储引擎类型
功能：通过“重放”日志文件中的事件来生成数据副本
==注意：建议二进制日志和数据文件分开存放==
3.7.1 二进制日志记录三种格式
- 基于“语句”记录：statement，记录语句，默认模式（ MariaDB 10.2.3 版本以下 ），日志量较少
- 基于“行”记录：row，记录数据，日志量较大，更加安全，数据不会丢失，建议使用的格式
- 混合模式：mixed，让系统自行判定该基于哪种方式进行，默认模式（ MariaDB 10.2.4及版本以上）
格式配置
#mariadb
show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.001 sec)
#MySQL 8.0 默认使用ROW方式
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.07 sec)
3.7.2 二进制日志文件的构成
两类文件：
- 日志文件：mysql|mariadb-bin.文件名后缀，二进制格式，如： mariadb-bin.000001
- 索引文件：mysql|mariadb-bin.index，文本格式
二进制日志相关的服务器变量：
sql_log_bin=ON|OFF #是否记录二进制日志，默认ON，支持动态修改，系统变量，而非服务器选项
log_bin=/PATH/BIN_LOG_FILE #指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项都开启才可以
#log_bin是只读变量，只能写入到配置文件中
binlog_format=STATEMENT|ROW|MIXED #二进制日志记录的格式，默认STATEMENT
max_binlog_size=1073741824 #单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G
#说明：文件达到上限时的大小未必为指定的精确值
binlog_cache_size=4m #此变量确定在每次事务中保存二进制日志更改记录的缓存的大小（每次连接）
max_binlog_cache_size=512m #限制用于缓存多事务查询的字节大小。
sync_binlog=1|0 #设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘
expire_logs_days=N #二进制日志可以自动删除的天数。 默认为0，即不自动删除，比如存放60天或180天
范例：查看二进制日志变量状态，虽然默认sql_log_bin是开启状态，但log_bin未开启，所以不能记录二进制日志
MariaDB [hellodb]> select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
| 1 |
+---------------+
1 row in set (0.001 sec)
MariaDB [hellodb]> select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
| 0 |
+-----------+
1 row in set (0.000 sec)
MariaDB [hellodb]> set log_bin='/data/';
ERROR 1238 (HY000): Variable 'log_bin' is a read only variable
范例：启用二进制日志记录文件
#log_bin要放在服务器配置文件中，并且指定二进制日志文件的路径和名称前缀；如果只指定名称前缀，会自动保存在默认的数据库安装路径中，即/var/lib/mysql下
[root@c8-mysql ~]# vi /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
log_bin=mysql_bin #直接指定文件名，会在/var/lib/mysql中生成这个文件名+index后缀以及000001后缀的文件
[root@c8-mysql ~]# ll /var/lib/mysql -t
-rw-rw---- 1 mysql mysql 328 Jun 20 17:44 mysql_bin.000001 #日志文件
-rw-rw---- 1 mysql mysql 19 Jun 20 17:44 mysql_bin.index #索引文件，记录每个日志的文件名
#通常需要把二进制日志文件保存在不同的分区目录下，起到备份的作用
[root@c8-mysql ~]# mkdir /data/logbin
[root@c8-mysql ~]# chown -R mysql.mysql /data/logbin
[root@c8-mysql ~]# vi /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
sql_log_bin
log_bin=/data/logbin/mysql_bin
[root@c8-mysql ~]# systemctl restart mariadb
[root@c8-mysql ~]# ll /data/logbin
total 8
-rw-rw---- 1 mysql mysql 328 Jun 20 17:53 mysql_bin.000001
-rw-rw---- 1 mysql mysql 30 Jun 20 17:53 mysql_bin.index
3.7.3 二进制日志相关配置
查看mariadb自行管理使用中的二进制日志文件列表，及大小
SHOW {BINARY | MASTER} LOGS
MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql_bin.000001 | 328 |
+------------------+-----------+
1 row in set (0.000 sec)
MariaDB [hellodb]> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql_bin.000001 | 328 |
+------------------+-----------+
1 row in set (0.000 sec)
查看使用中的二进制日志文件
SHOW MASTER STATUS
MariaDB [hellodb]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql_bin.000001 | 328 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)
范例：查看二进制日志文件的变化情况
#第二会话中使用watch，每秒执行一次ls -l /data/logbin，查看该文件的实时变化
[root@c8-mysql ~]# watch -n1 ls -l /data/logbin/
Every 1.0s: ls -l /data/logbin/ c8-mysql: Sun Jun 20 18:01:48 2021
-rw-rw---- 1 mysql mysql 328 Jun 20 18:01 mysql_bin.000001 #原来的大小
-rw-rw---- 1 mysql mysql 30 Jun 20 17:53 mysql_bin.index
MariaDB [hellodb]> update teachers set age=30 where tid=4; #更新文件后
Query OK, 1 row affected (0.002 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql_bin.000001 | 509 | #数据库内查看日志文件大小
+------------------+-----------+
1 row in set (0.000 sec)
[root@c8-mysql ~]# watch -n1 ls -l /data/logbin/
Every 1.0s: ls -l /data/logbin/ c8-mysql: Sun Jun 20 18:09:48 2021
-rw-rw---- 1 mysql mysql 509 Jun 20 18:08 mysql_bin.000001 #数据库操作后的日志变化
-rw-rw---- 1 mysql mysql 30 Jun 20 17:53 mysql_bin.index
在线查看二进制文件中的指定内容
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
[root@c8-mysql ~]# file /data/logbin/mysql_bin.000001
/data/logbin/mysql_bin.000001: MySQL replication log, server id 1 MySQL V5+, server version 10.3.28-MariaDB-log
[root@c8-mysql ~]# cat /data/logbin/mysql_bin.000001
▒bin.▒`▒10.3.28-MariaDB-log.▒`8
[[E▒.▒`▒hσ\.▒`▒+Hmysql_bin.000001▒ELِ▒`▒*r
▒▒▒`l▒ Tstd---hellodbupdate teachers set age=30 where tid=4a▒▒▒▒`▒[root@c8-mysql ~]#
MariaDB [hellodb]> show binlog events;
+------------------+-----+-------------------+-----------+-------------+-----------------
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------------+-----------+-------------+-----------------
| mysql_bin.000001 | 4 |Format_desc|1|256 | Server ver: 10.3.28-MariaDB-log,Binlogver:4 |
| mysql_bin.000001 | 256|Gtid_list |1|285 | [] |
| mysql_bin.000001 | 285|Binlog_checkpoint| 1 | 328 | mysql_bin.000001 |
| mysql_bin.000001 | 328 | Gtid | 1 | 370 | BEGIN GTID 0-1-1 |
| mysql_bin.000001 | 370 | Query | 1 | 478 | use `hellodb`; update teachers set age=30 where tid=4 |
| mysql_bin.000001 | 478 | Xid | 1 | 509 | COMMIT /* xid=25 */ |
+------------------+-----+-------------------+-----------+-------------+-----------------
6 rows in set (0.000 sec)
范例：查看二进制日志文件的内容
show binlog events in 'mysql-bin.000001' from 6516 limit 2,3
范例：
[root@c8-mysql ~]# vi /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
binlog_format=statement #修改为语句记录模式
sql_log_bin
log_bin=/data/logbin/mysql_bin
[root@c8-mysql ~]# systemctl restart mariadb
[root@c8-mysql ~]# ll /data/logbin/ #重启服务后，二进制文件自动加1
total 12
-rw-rw---- 1 mysql mysql 532 Jun 20 18:22 mysql_bin.000001
-rw-rw---- 1 mysql mysql 342 Jun 20 18:22 mysql_bin.000002
-rw-rw---- 1 mysql mysql 60 Jun 20 18:22 mysql_bin.index
MariaDB [hellodb]> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql_bin.000001 | 532 |
| mysql_bin.000002 | 342 | #新增一个二进制日志文件
+------------------+-----------+
2 rows in set (0.001 sec)
MariaDB [hellodb]> show binlog events; #显示的都是mysql_bin.000001的日志
+------------------+-----+-------------------+-----------+-------------+-----------------
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------------+-----------+-------------+-----------------
| mysql_bin.000001|4| Format_desc|1|256| Server ver: 10.3.28-MariaDB-log, Binlog ver: 4|
...省略... |
+------------------+-----+-------------------+-----------+-------------+-----------------
7 rows in set (0.000 sec)
MariaDB [hellodb]> update teachers set age=20 where tid=3;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [hellodb]> show binlog events; #数据库有操作记录后，也不会记录在000001中
#查看其他二进制日志记录
MariaDB [hellodb]> show binlog events in 'mysql_bin.000002';
+------------------+-----+-------------------+-----------+-------------+-----------------
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------------+-----------+-------------+-----------------
| mysql_bin.000002 | 4 | Format_desc | 1 | 256 | Server ver: 10.3.28-MariaDB-log, Binlog ver: 4 |
| mysql_bin.000002 | 256 | Gtid_list | 1 | 299 | [0-1-1] |
| mysql_bin.000002 | 299 | Binlog_checkpoint | 1 | 342 | mysql_bin.000002 |
| mysql_bin.000002 | 342 | Gtid | 1 | 384 | BEGIN GTID 0-1-2 |
| mysql_bin.000002 | 384 | Query | 1 | 492 | use `hellodb`; update teachers set age=20 where tid=3 |
| mysql_bin.000002 | 492 | Xid | 1 | 523 | COMMIT /* xid=22 */|
+------------------+-----+-------------------+-----------+-------------+-----------------
6 rows in set (0.000 sec)
MariaDB [hellodb]> update teachers set age=22 where tid=2;
Query OK, 1 row affected (0.001 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [hellodb]> show binlog events in 'mysql_bin.000002' from 299;
+------------------+-----+-------------------+-----------+-------------+-----------------
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------------+-----------+-------------+-----------------
| mysql_bin.000002 | 299 | Binlog_checkpoint | 1 | 342 | mysql_bin.000002 |
| mysql_bin.000002 | 342 | Gtid | 1 | 384 | BEGIN GTID 0-1-2 |
| mysql_bin.000002 | 384 | Query | 1 | 492 | use `hellodb`; update teachers set age=20 where tid=3 |
| mysql_bin.000002 | 492 | Xid | 1 | 523 | COMMIT /* xid=22 */ |
| mysql_bin.000002 | 523 | Gtid | 1 | 565 | BEGIN GTID 0-1-3 |
| mysql_bin.000002 | 565 | Query | 1 | 673 | use `hellodb`; update teachers set age=22 where tid=2 |
| mysql_bin.000002 | 673 | Xid | 1 | 704 | COMMIT /* xid=25 */ |
+------------------+-----+-------------------+-----------+-------------+-----------------
7 rows in set (0.000 sec)
MariaDB [hellodb]> show binlog events in 'mysql_bin.000002' from 299 limit 2,3;
+------------------+-----+------------+-----------+-------------+------------------------
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+------------+-----------+-------------+------------------------
| mysql_bin.000002 | 384 | Query | 1 | 492 | use `hellodb`; update teachers set age=20 where tid=3 |
| mysql_bin.000002 | 492 | Xid | 1 | 523 | COMMIT /* xid=22 */ |
| mysql_bin.000002 | 523 | Gtid | 1 | 565 | BEGIN GTID 0-1-3 |
+------------------+-----+------------+-----------+-------------+------------------------
3 rows in set (0.000 sec)
范例：
#读取数据库，是不记录在二进制日志中的
MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql_bin.000001 | 532 |
| mysql_bin.000002 | 704 |
+------------------+-----------+
2 rows in set (0.000 sec)
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 22 | M |
| 3 | Miejue Shitai | 20 | F |
| 4 | Lin Chaoying | 30 | F |
+-----+---------------+-----+--------+
4 rows in set (0.000 sec)
MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql_bin.000001 | 532 |
| mysql_bin.000002 | 704 |
+------------------+-----------+
2 rows in set (0.000 sec)
MariaDB [hellodb]> show binlog events in 'mysql_bin.000002';
+------------------+-----+-------------------+-----------+-------------+-----------------
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------------+-----------+-------------+-----------------
| mysql_bin.000002 | 4 | Format_desc | 1 | 256 | Server ver: 10.3.28-MariaDB-log, Binlog ver: 4 |
| mysql_bin.000002 | 256 | Gtid_list | 1 | 299 | [0-1-1] |
| mysql_bin.000002 | 299 | Binlog_checkpoint | 1 | 342 | mysql_bin.000002 |
| mysql_bin.000002 | 342 | Gtid | 1 | 384 | BEGIN GTID 0-1-2 |
| mysql_bin.000002 | 384 | Query | 1 | 492 | use `hellodb`; update teachers set age=20 where tid=3 |
| mysql_bin.000002 | 492 | Xid | 1 | 523 | COMMIT /* xid=22 */ |
| mysql_bin.000002 | 523 | Gtid | 1 | 565 | BEGIN GTID 0-1-3 |
| mysql_bin.000002 | 565 | Query | 1 | 673 | use `hellodb`; update teachers set age=22 where tid=2 |
| mysql_bin.000002 | 673 | Xid | 1 | 704 | COMMIT /* xid=25 */ |
+------------------+-----+-------------------+-----------+-------------+-----------------
9 rows in set (0.000 sec)
mysqlbinlog：二进制日志的客户端命令工具，支持离线查看二进制日志
命令格式：
mysqlbinlog [OPTIONS] log_file…
--start-position=# 指定开始位置
--stop-position=#
--start-datetime= #时间格式：YYYY-MM-DD hh:mm:ss
--stop-datetime=
--base64-output[=name]
-v -vvv
范例：语句staement和行记录row的在二进制日志中的区别
MariaDB [hellodb]> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.001 sec)
MariaDB [hellodb]> update teachers set age=25; #修改所有教师的年龄为25岁
Query OK, 4 rows affected (0.002 sec)
Rows matched: 4 Changed: 4 Warnings: 0
[root@c8-mysql logbin]# mysqlbinlog mysql_bin.000002 #只记录了一条update命令
# at 746
#210620 18:52:40 server id 1 end_log_pos 842 CRC32 0xc365508c Query thread_id=10 exec_time=0 error_code=0
SET TIMESTAMP=1624186360/*!*/;
update teachers set age=25
/*!*/;
MariaDB [hellodb]> set binlog_format=row;
Query OK, 0 rows affected (0.000 sec)
MariaDB [hellodb]> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.001 sec)
MariaDB [hellodb]> update teachers set age=30;
Query OK, 4 rows affected (0.002 sec)
Rows matched: 4 Changed: 4 Warnings: 0
[root@c8-mysql logbin]# mysqlbinlog mysql_bin.000002
# at 964
#210620 18:55:05 server id 1 end_log_pos 964 CRC32 0x5a649ba9 Annotate_rows:
#Q> update teachers set age=30
#210620 18:55:05 server id 1 end_log_pos 1025 CRC32 0x8698300f Table_map: `hellodb`.`teachers` mapped to number 27
# at 1025
#210620 18:55:05 server id 1 end_log_pos 1211 CRC32 0x77bfdfd8 Update_rows: table id 27 flags: STMT_END_F
BINLOG '
iR7PYBMBAAAAPQAAAAEEAAAAABsAAAAAAAEAB2hlbGxvZGIACHRlYWNoZXJzAAQCDwH+BCwB9wEI
DzCYhg==
iR7PYBgBAAAAugAAALsEAAAAABsAAAAAAAEABP//8AEACgBTb25nIEppYW5nGQLwAQAKAFNvbmcg
SmlhbmceAvACAA0AWmhhbmcgU2FuZmVuZxkC8AIADQBaaGFuZyBTYW5mZW5nHgLwAwANAE1pZWp1
ZSBTaGl0YWkZAfADAA0ATWllanVlIFNoaXRhaR4B8AQADABMaW4gQ2hhb3lpbmcZAfAEAAwATGlu
IENoYW95aW5nHgHY3793
'/*!*/;
# Number of rows: 4 #这里显示修改了4行，详细记录使用base64编码隐藏了，可以加-v显示
[root@c8-mysql logbin]# mysqlbinlog mysql_bin.000002 -v
# at 964
#210620 18:55:05 server id 1 end_log_pos 964 CRC32 0x5a649ba9 Annotate_rows:
#Q> update teachers set age=30
#210620 18:55:05 server id 1 end_log_pos 1025 CRC32 0x8698300f Table_map: `hellodb`.`teachers` mapped to number 27
# at 1025
#210620 18:55:05 server id 1 end_log_pos 1211 CRC32 0x77bfdfd8 Update_rows: table id 27 flags: STMT_END_F
BINLOG '
iR7PYBMBAAAAPQAAAAEEAAAAABsAAAAAAAEAB2hlbGxvZGIACHRlYWNoZXJzAAQCDwH+BCwB9wEI
DzCYhg==
iR7PYBgBAAAAugAAALsEAAAAABsAAAAAAAEABP//8AEACgBTb25nIEppYW5nGQLwAQAKAFNvbmcg
SmlhbmceAvACAA0AWmhhbmcgU2FuZmVuZxkC8AIADQBaaGFuZyBTYW5mZW5nHgLwAwANAE1pZWp1
ZSBTaGl0YWkZAfADAA0ATWllanVlIFNoaXRhaR4B8AQADABMaW4gQ2hhb3lpbmcZAfAEAAwATGlu
IENoYW95aW5nHgHY3793
'/*!*/;
### UPDATE `hellodb`.`teachers`
### WHERE
### @1=1
### @2='Song Jiang'
### @3=25
### @4=2
### SET
### @1=1
### @2='Song Jiang'
### @3=30
### @4=2
### UPDATE `hellodb`.`teachers`
### WHERE
### @1=2
### @2='Zhang Sanfeng'
### @3=25
### @4=2
### SET
### @1=2
### @2='Zhang Sanfeng'
### @3=30
### @4=2
### UPDATE `hellodb`.`teachers`
### WHERE
### @1=3
### @2='Miejue Shitai'
### @3=25
### @4=1
### SET
### @1=3
### @2='Miejue Shitai'
### @3=30
### @4=1
### UPDATE `hellodb`.`teachers`
### WHERE
### @1=4
### @2='Lin Chaoying'
### @3=25
### @4=1
### SET
### @1=4
### @2='Lin Chaoying'
### @3=30
### @4=1
# Number of rows: 4
二进制日志文件大小会比数据库文件大小增长快，要时刻留意大小变化，及时备份，清理
范例：
mysqlbinlog --start-position=678 --stop-position=752 /var/lib/mysql/mariadb-bin.000003 -v
mysqlbinlog --start-datetime="2021-06-18 20:30:00" --stop-datetime="2021-06-18 20:35:22" mariadb-bin.000003 -vvv
切换日志文件：
FLUSH LOGS;
MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql_bin.000001 | 532 |
| mysql_bin.000002 | 1265 |
| mysql_bin.000003 | 711 |
+------------------+-----------+
3 rows in set (0.000 sec)
MariaDB [hellodb]> flush logs;
Query OK, 0 rows affected (0.003 sec)
MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql_bin.000001 | 532 |
| mysql_bin.000002 | 1265 |
| mysql_bin.000003 | 758 |
| mysql_bin.000004 | 385 |
+------------------+-----------+
4 rows in set (0.000 sec)
[root@c8-mysql logbin]# cat mysql_bin.index
/data/logbin/mysql_bin.000001
/data/logbin/mysql_bin.000002
/data/logbin/mysql_bin.000003
/data/logbin/mysql_bin.000004
[root@c8-mysql logbin]# mysqladmin -uroot -pmagedu flush-logs
[root@c8-mysql logbin]# cat mysql_bin.index
/data/logbin/mysql_bin.000001
/data/logbin/mysql_bin.000002
/data/logbin/mysql_bin.000003
/data/logbin/mysql_bin.000004
/data/logbin/mysql_bin.000005
MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql_bin.000001 | 532 |
| mysql_bin.000002 | 1265 |
| mysql_bin.000003 | 758 |
| mysql_bin.000004 | 432 |
| mysql_bin.000005 | 385 |
+------------------+-----------+
5 rows in set (0.000 sec)
清除指定二进制日志
PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }
范例：
PURGE BINARY LOGS TO 'mariadb-bin.000003'; #删除mariadb-bin.000003之前的日志
PURGE BINARY LOGS BEFORE '2017-01-23';
PURGE BINARY LOGS BEFORE '2017-03-22 09:25:30';
范例：删除二进制日志文件
MariaDB [hellodb]> purge binary logs to 'mysql_bin.000003';
Query OK, 0 rows affected (0.002 sec)
MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql_bin.000003 | 758 |
| mysql_bin.000004 | 432 |
| mysql_bin.000005 | 385 |
+------------------+-----------+
3 rows in set (0.000 sec)
[root@c8-mysql logbin]# cat mysql_bin.index
/data/logbin/mysql_bin.000003
/data/logbin/mysql_bin.000004
/data/logbin/mysql_bin.000005
[root@c8-mysql logbin]# ll
total 16
-rw-rw---- 1 mysql mysql 758 Jun 20 19:17 mysql_bin.000003
-rw-rw---- 1 mysql mysql 432 Jun 20 19:19 mysql_bin.000004
-rw-rw---- 1 mysql mysql 385 Jun 20 19:19 mysql_bin.000005
-rw-rw---- 1 mysql mysql 90 Jun 20 19:20 mysql_bin.index
删除所有二进制日志，index文件重新记数，会清除所有的二进制日志，提前备份
RESET MASTER [TO #]; #删除所有二进制日志文件，并重新生成日志文件，文件名从#开始记数，默认从
1开始，一般是master主机第一次启动时执行，MariaDB 10.1.6开始支持TO #
范例：
MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql_bin.000003 | 758 |
| mysql_bin.000004 | 432 |
| mysql_bin.000005 | 385 |
+------------------+-----------+
3 rows in set (0.000 sec)
MariaDB [hellodb]> reset master to 2;
Query OK, 0 rows affected (0.003 sec)
MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql_bin.000002 | 328 |
+------------------+-----------+
1 row in set (0.000 sec)
MariaDB [hellodb]> reset master;
Query OK, 0 rows affected (0.003 sec)
MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql_bin.000001 | 328 |
+------------------+-----------+
1 row in set (0.000 sec)
