09-mysql中的系统数据库

mysql系统数据库

mysql 数据库

  • 是mysql的核心数据库，类似于Sql Server中的master库，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息

information_schema 数据库

  • MySQL 5.0之后产生的，一个虚拟数据库，物理上并不存在information_schema数据库类似与"数据字典"，提供了访问数据库元数据的方式，即数据的数据。比如数据库名或表名，列类型，访问权限（更加细化的访问方式）

performance_schema 数据库

  • MySQL 5.5开始新增的数据库，主要用于收集数据库服务器性能参数,库里表的存储引擎均为PERFORMANCE_SCHEMA，用户不能创建存储引擎为PERFORMANCE_SCHEMA的表

sys 数据库

  • MySQL5.7之后新增加的数据库，库中所有数据源来自performance_schema。目标是把performance_schema的把复杂度降低，让DBA能更好的阅读这个库里的内容。让DBA更快的了解DataBase的运行情况

服务器配置和状态

  • 官方帮助：
https://dev.mysql.com/doc/refman/8.0/en/server-option-variable-reference.html
https://dev.mysql.com/doc/refman/5.7/en/server-option-variable-reference.html
https://mariadb.com/kb/en/library/full-list-of-mariadb-options-system-andstatus-variables/
  • 注意：
    • 其中有些参数支持运行时修改，会立即生效
    • 有些参数不支持动态修改，且只能通过修改配置文件，并重启服务器程序生效
    • 有些参数作用域是全局的，为所有会话设置
    • 有些可以为每个用户提供单独（会话）的设置

服务器选项

  • 注意: 服务器选项用横线,不用下划线
#查看mysqld可用选项列表和及当前值
mysqld --verbose --help
#获取mysqld当前启动选项
mysqld --print-defaults
  • 设置服务器选项方法
    • 在命令行中设置 
shell> /usr/bin/mysqld_safe --skip-name-resolve=1
shell> /usr/libexec/mysqld --basedir=/usr
    • 在配置文件my.cnf中设置
vim /etc/my.cnf
[mysqld]
skip_name_resolve=1
skip-grant-tables

服务器系统变量

  • 服务器系统变量：可以分全局和会话两种
  • 注意: 系统变量用下划线,不用横线
  • 获取系统变量 
#只查看global变量
SHOW GLOBAL VARIABLES;

#查看所有变量(包括global和session)
SHOW [SESSION] VARIABLES;

#查看指定的系统变量
SHOW VARIABLES LIKE 'VAR_NAME';
SELECT @@VAR_NAME;
#查看选项和部分变量
[root@centos8 ~]#mysqladmin variables

#修改服务器变量的值：
help SET

#修改全局变量：仅对修改后新创建的会话有效；对已经建立的会话无效
SET GLOBAL system_var_name=value;
SET @@global.system_var_name=value;

#修改会话变量：
SET [SESSION] system_var_name=value;
SET @@[session.]system_var_name=value;
  • 范例: character_set_results是系统变量并非服务器选项
MariaDB [(none)]> show variables like 'character_set_results';
MariaDB [(none)]> set character_set_results="utf8mb4";
MariaDB [(none)]> show variables like 'character_set_results';

[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf 
[mysqld]
character_set_results=utf8mb4 
#character_set_results不是服务器选项,写入配置文件将导致无法启动
[root@centos8 ~]#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.
  • 修改mysql最大并发连接数
#方法1
[root@centos8 ~]#vim /usr/lib/systemd/system/mariadb.service 
[Service]
#加下面一行
LimitNOFILE=65535
#方法2
[root@centos8 ~]#mkdir /etc/systemd/system/mariadb.service.d/
[root@node3 ~]#vim /etc/systemd/system/mariadb.service.d/limits.conf
[Service]
LimitNOFILE=65535
[root@centos8 ~]#systemctl daemon-reload
[root@centos8 ~]#systemctl restart mariadb
[root@centos8 ~]#mysql -uroot -p -e "select @@max_connections"
  • 范例：修改页大小 
    • 参看：https://mariadb.com/kb/en/innodb-system-variables/#innodb_page_size
    • 说明：初始化数据目录后，不能更改此系统变量的值。 在MariaDB实例启动时设置InnoDB的页面大小，此后保持不变。
/data/mysql3306/bin/mysqladmin -u root -p -S /data/mysql3306/var/mysql.sock variables|grep innodb_page_size
[hellodb]> show variables like 'innodb_page_size';

服务器状态变量

  • 服务器状态变量：分全局和会话两种
  • 状态变量（只读）：用于保存mysqld运行中的统计数据的变量，不可更改 
SHOW GLOBAL STATUS;
SHOW [SESSION] STATUS;

#范例
17:29:49(root@localhost) [hellodb]> show status like "innodb_page_size";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)

17:38:21(root@localhost) [hellodb]> SHOW GLOBAL STATUS like 'com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 1     |
+---------------+-------+
1 row in set (0.01 sec)

服务器变量 SQL_MODE

  • SQL_MODE：对其设置可以完成一些约束检查的工作,可分别进行全局的设置或当前会话的设置
  • 常见MODE:
    • NO_AUTO_CREATE_USER： 禁止GRANT创建密码为空的用户
    • NO_ZERO_DATE：在严格模式，不允许使用'0000-00-00'的时间
    • ONLY_FULL_GROUP_BY： 对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么将认为这个SQL是不合法的
    • NO_BACKSLASH_ESCAPES： 反斜杠"\"作为普通字符而非转义字符
    • PIPES_AS_CONCAT： 将"||"视为连接操作符而非"或"运算符 

INDEX 索引 

  • 索引：是排序的快速查找的特殊数据结构，定义作为查找条件的字段上，又称为键key，索引通过存储引擎实现 
  • 优点：
    • 索引可以降低服务需要扫描的数据量，减少了IO次数
    • 索引可以帮助服务器避免排序和使用临时表
    • 索引可以帮助将随机I/O转为顺序I/O
  • 缺点：
    • 占用额外空间，影响插入速度
  • 索引类型：
    • B+ TREE、HASH、R TREE、FULL TEXT
    • 聚簇（集）索引、非聚簇索引：数据和索引是否存储在一起
    • 主键索引、二级（辅助）索引
    • 稠密索引、稀疏索引：是否索引了每一个数据项
    • 简单索引、组合索引: 是否是多个字段的索引
    • 左前缀索引：取前面的字符做索引
    • 覆盖索引：从索引中即可取出要查询的数据，性能高
  • 索引结构：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
  • 二叉树：https://www.cs.usfca.edu/~galles/visualization/BST.html 

假设定义一颗B+树高度为2，即一个根节点和若干叶子节点。那么这棵B+树的存放总行记录数=根节点指针数*单个叶子记录的行数。这里先计算叶子节点，B+树中的单个叶子节点的大小为16K
假设每一条目为1K，那么记录数即为16(16k/1K=16)，然后计算非叶子节点能够存放多少个指针，假设主键ID为bigint类型，那么长度为8字节，而指针大小在InnoDB中是设置为6个字节，
这样加起来一共是14个字节。那么通过页大小/(主键ID大小+指针大小），即16384/14=1170个指针，所以一颗高度为2的B+树能存放16*1170=18720条这样的记录。根据这个原理就可以算
出一颗高度为3的B+树可以存放16*1170*1170=21902400条记录。所以在InnoDB中B+树高度一般为2-3层，它就能满足千万级的数据存储
  • 可以使用B+Tree索引的查询类型：(假设前提: 姓,名,年龄三个字段建立了一个复合索引)
    • 全值匹配：精确所有索引列，如：姓wang，名xiaochun，年龄30
    • 匹配最左前缀：即只使用索引的第一列，如：姓wang
    • 匹配列前缀：只匹配一列值开头部分，如：姓以w开头的记录
    • 匹配范围值：如：姓ma和姓wang之间
    • 精确匹配某一列并范围匹配另一列：如：姓wang,名以x开头的记录
    • 只访问索引的查询
  • B+Tree索引的限制：
    • 如不从最左列开始，则无法使用索引，如：查找名为xiaochun，或姓为g结尾
    • 不能跳过索引中的列：如：查找姓wang，年龄30的，只能使用索引第一列
  • 特别提示：
    • 索引列的顺序和查询语句的写法应相匹配，才能更好的利用索引
    • 为优化性能，可能需要针对相同的列但顺序不同创建不同的索引来满足不同类型的查询需求 
  • Hash索引
    • Hash索引：基于哈希表实现，只有精确匹配索引中的所有列的查询才有效，索引自身只存储索引列对应的哈希值和数据指针，索引结构紧凑，查询性能好
    • Memory存储引擎支持显式hash索引，InnoDB和MyISAM存储引擎不支持
    • 适用场景：只支持等值比较查询，包括=, <=>, IN() 不适合使用hash索引的场景
      • 不适用于顺序查询：索引存储顺序的不是值的顺序
      • 不支持模糊匹配
      • 不支持范围查询
      • 不支持部分索引列匹配查找：如A，B列索引，只查询A列索引无效
  • 全文索引(FULLTEXT)
    • 在文本中查找关键词，而不是直接比较索引中的值，类似搜索引擎
  • 冗余和重复索引：
    • 冗余索引：（A），（A，B）,注意如果同时存在,仍可能会使用(A)索引
    • 重复索引：已经有索引，再次建立索引 

 索引优化：

  • 独立地使用列：尽量避免其参与运算，独立的列指索引列不能是表达式的一部分，也不能是函数的参数，在where条件中，始终将索引列单独放在比较符号的一侧，尽量不要在列上进行运算（函数操作和表达式操作）
  • 左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性（不重复的索引值和数据表的记录总数的比值）来评估，尽量使用短索引，如果可以，应该制定一个前缀长度
  • 多列索引：AND操作时更适合使用多列索引，而非为每个列创建单独的索引
  • 选择合适的索引列顺序：无排序和分组时，将选择性最高放左侧
  • 只要列中含有NULL值，就最好不要在此列设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
  • 对于经常在where子句使用的列，最好设置索引
  • 对于有多个列where或者order by子句，应该建立复合索引
  • 对于like语句，以 % 或者 _ 开头的不会使用索引，以 % 结尾会使用索引
  • 尽量不要使用not in和<>操作,虽然可能使用索引,但性能不高
  • 不要使用RLIKE正则表达式会导致索引失效
  • 查询时，能不要*就不用*，尽量写全字段名，比如:select id,name,age from students;
  • 大部分情况连接效率远大于子查询
  • 在有大量记录的表分页时使用limit
  • 对于经常使用的查询，可以开启查询缓存
  • 多使用explain和profile分析查询语句
  • 查看慢查询日志，找出执行时间长的sql语句优化 

管理索引

#创建索引：
CREATE [UNIQUE] INDEX index_name ON tbl_name (index_col_name[(length)],...);
ALTER TABLE tbl_name ADD INDEX index_name(index_col_name[(length)]);
help CREATE INDEX;

#删除索引：
DROP INDEX index_name ON tbl_name;
ALTER TABLE tbl_name DROP INDEX index_name(index_col_name);

#查看索引：
SHOW INDEX FROM [db_name.]tbl_name;

#优化表空间：
OPTIMIZE TABLE tb_name;

#查看索引的使用
SET GLOBAL userstat=1;  #MySQL无此变量
SHOW INDEX_STATISTICS;
  • 案例
SET GLOBAL userstat=1;
SHOW INDEX_STATISTICS;
desc students;
show index from students\G;
SHOW INDEX_STATISTICS;
select * from students where stuid=10;
SHOW INDEX_STATISTICS;
select * from students where stuid=10;
SHOW INDEX_STATISTICS;

explain 工具

MariaDB [hellodb]> explain select * from students where name like 'w%';
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 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.00 sec)
  • explain输出信息说明

  • 说明： type显示的是访问类型，是较为重要的一个指标
  • 结果值从好到坏依次是：NULL> system >const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery >range > index > ALL
  • 一般来说，得保证查询至少达到range级别，最好能达到ref
备注：掌握以下10种常见的即可
NULL>system>const>eq_ref>ref>ref_or_null>index_merge>range>index>ALL

使用 profile 工具

  • 打开后，会显示语句执行详细的过程:set profiling = ON;
MariaDB [hellodb]> show variables like 'profiling%'; 
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+
2 rows in set (0.00 sec)
#查看语句,注意结果中的query_id值
show profiles;

#显示语句的详细执行步骤和时长
show profile for query $n;

#n为第n条sql语句的序列
#显示cpu使用情况
show profile cpu for query $n;#

并发控制

锁机制

  • 锁类型：
    • 读锁：共享锁，也称为 S 锁,只读不可写（包括当前事务） ，多个读互不阻塞
    • 写锁：独占锁，排它锁，也称为 X 锁,写锁会阻塞其它事务（不包括当前事务）的读和写
    • S 锁和 S 锁是兼容的，X 锁和其它锁都不兼容，举个例子，事务 T1 获取了一个行 r1 的 S 锁，另外事务 T2 可以立即获得行 r1 的 S 锁，此时 T1 和 T2 共同获得行 r1 的 S 锁，此种情况称为锁兼容，但是另外一个事务 T2 此时如果想获得行 r1 的 X 锁，则必须等待 T1 对行 r1 锁的释放，此种情况也称为锁冲突
  • 锁粒度：
    • 表级锁：MyISAM
    • 行级锁：InnoDB
  • 实现
    • 存储引擎：自行实现其锁策略和锁粒度
    • 服务器级：实现了锁，表级锁，用户可显式请求
  • 分类：
    • 隐式锁：由存储引擎自动施加锁
    • 显式锁：用户手动请求
  • 锁策略：在锁粒度及数据安全性寻求的平衡机制 

显式使用锁 

  • 加锁
LOCK TABLES tbl_name [[AS] alias] lock_type  [, tbl_name [[AS] alias]
lock_type] ...
lock_type: 
READ   #读锁
WRITE  #写锁
  • 解锁：unlock tables;
#关闭正在打开的表（清除查询缓存），通常在备份前加全局读锁
FLUSH TABLES [tb_name[,...]] [WITH READ LOCK]

#查询时加写或读锁
SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]

#范例：加读锁
MariaDB [hellodb]> lock tables students read; 
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> update students set classid=2 where stuid=24;
ERROR 1099 (HY000): Table 'students' was locked with a READ lock and can't be updated
MariaDB [hellodb]> unlock tables; 
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> update students set classid=2 where stuid=24;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

事务

  • 事务 Transactions：一组原子性的 SQL语句，或一个独立工作单元
  • 事务日志：记录事务信息，实现undo,redo等故障恢复功能

事务特性：ACID特性

  • A：atomicity 原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚
  • C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态,类似于能量守恒定律(N50周启皓语录)
  • I：Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务所见；隔离有多种隔离级别，实现并发
  • D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中

管理事务

  • 显式启动事务
BEGIN
BEGIN WORK
START TRANSACTION
  • 结束事务
#提交,相当于vi中的wq保存退出
COMMIT
#回滚,相当于vi中的q!不保存退出
ROLLBACK
  • 注意：只有事务型存储引擎中的DML语句方能支持此类操作
  • 自动提交
set autocommit={1|0}

#默认为1，为0时设为非自动提交
#建议：显式请求和提交事务，而不要使用"自动提交"功能
  • 事务支持保存点
SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier
  • 查看事务
#查看当前正在进行的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
#以下两张表在MySQL8.0中已取消
#查看当前锁定的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
#查看当前等锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

死锁

  • 两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态

事务隔离级别

  • MySQL 支持四种隔离级别，事务隔离级别从上至下更加严格

  • READ UNCOMMITTED
    • 可读取到未提交数据，产生脏读
  • READ COMMITTED
    • 可读取到提交数据，但未提交数据不可读，产生不可重复读，即可读取到多个提交数据，导致每次读取数据不一致
  • REPEATABLE READ
    • 可重复读，多次读取数据都一致，产生幻读，即读取过程中，即使有其它提交的事务修改数据，仍只能读取到未修改前的旧数据。此为MySQL默认设置
  • SERIALIZABLE
    • 可串行化，未提交的读事务阻塞修改事务（加读锁，但不阻塞读事务），或者未提交的修改事务阻塞其它事务的读写（加写锁，其它事务的读，写都不可以执行）。会导致并发性能差

MVCC和事务的隔离级别

  • MVCC（多版本并发控制机制）只在READ COMMITTED和REPEATABLE READ隔离级别下工作。
  • 其他两个隔离级别都和MVCC不兼容,因为READ UNCOMMITTED总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁

指定事务隔离级别

  • 服务器变量tx_isolation(MySQL8.0改名为transaction_isolation)指定，默认为REPEATABLE-READ，可在GLOBAL和SESSION级进行设置
#MySQL8.0之前版本
SET tx_isolation='READ-UNCOMMITTED|READ-COMMITTED|REPEATABLEREAD|SERIALIZABLE'

#MySQL8.0
SET transaction_isolation='READ-UNCOMMITTED|READ-COMMITTED|REPEATABLEREAD|SERIALIZABLE'
  • 服务器选项中指定
vim /etc/my.cnf
[mysqld]
transaction-isolation=SERIALIZABLE

日志管理

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事务日志相关配置
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=0|1|2

  •  说明
1 此为默认值，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。 这是完全遵守ACID特性
0 提交时没有写磁盘的操作; 而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。 这样可提供更好的性能，但服务器崩溃可能丢失最后一秒的事务
2 每次提交后都会写入OS的缓冲区，但每秒才会进行一次刷新到磁盘文件中。 性能比0略差一些，但操作系统或停电可能导致最后一秒的交易丢失
  • 高并发业务行业最佳实践，是使用第三种折衷配置（=2）：
1.配置为2和配置为0，性能差异并不大，因为将数据从Log Buffer拷贝到OS cache，虽然跨越用户态与内核态，但毕竟只是内存的数据拷贝，速度很快
2.配置为2和配置为0，安全性差异巨大，操作系统崩溃的概率相比MySQL应用程序崩溃的概率，小很多，设置为2，只要操作系统不奔溃，也绝对不会丢数据
  • 说明：
    • 设置为1，同时sync_binlog = 1表示最高级别的容错
    • innodb_use_global_flush_log_at_trx_commit=0 时，将不能用SET语句重置此变量（ MariaDB10.2.6 后废弃） 

错误日志 

  • mysqld启动和关闭过程中输出的事件信息
  • mysqld运行中产生的错误信息
  • event scheduler运行一个event时产生的日志信息
  • 在主从复制架构中的从服务器上启动从服务器线程时产生的信息
  • 错误文件路径：SHOW GLOBAL VARIABLES LIKE 'log_error' ; 
  • 记录哪些警告信息至错误日志文件
#CentOS7 mariadb 5.5 默认值为1
#CentOS8 mariadb 10.3 默认值为2
log_warnings=0|1|2|3...         #MySQL5.7之前
log_error_verbosity=0|1|2|3...   #MySQL8.0

SHOW GLOBAL VARIABLES LIKE 'log_warnings';
SHOW GLOBAL VARIABLES LIKE 'log_error_verbosity';

通用日志

  • 通用日志：记录对数据库的通用操作，包括:错误的SQL语句
  • 通用日志可以保存在：file（默认值）或 table（mysql.general_log表）
  • 通用日志相关设置
general_log=ON|OFF
general_log_file=HOSTNAME.log
log_output=TABLE|FILE|NONE
  • 范例: 启用通用日志并记录至文件中
#默认没有启用通用日志
select @@general_log;

#启用
set global general_log=1;

#默认通用日志存放在文件中
SHOW GLOBAL VARIABLES LIKE 'log_output';

#通用日志存放的文件路径
select @@general_log_file;
  • 范例：通用日志记录到表中 
#修改通用日志，记录通用日志至mysql.general_log表中
set global log_output="table";
SHOW GLOBAL VARIABLES LIKE 'log_output';
#general_log表是CSV格式的存储引擎
show table status like 'general_log'\G

#general_log表是CSV的文本文件
[root@centos8 ~]#file /var/lib/mysql/mysql/general_log.CSV
/var/lib/mysql/mysql/general_log.CSV: ASCII text
[root@centos8 ~]#head /var/lib/mysql/mysql/general_log.CSV
select * from mysql.general_log\G
  • 范例: 查找执行次数最多的前三条语句 
select argument,count(argument) num from mysql.general_log group by argument order by num desc limit 3;
  • 范例:对访问的语句进行排序 
mysql -e 'select argument from mysql.general_log'|awk '{sql[$0]++}END{for(i in sql){print sql[i],i}}'|sort -nr
mysql -e 'select argument from mysql.general_log'|sort |uniq -c|sort -nr

慢查询日志 

  • 慢查询日志：记录执行查询时长超出指定时长的操作
  • 慢查询相关变量 
slow_query_log=ON|OFF
#开启或关闭慢查询，支持全局和会话，只有全局设置才会生成慢查询文件
long_query_time=N
#慢查询的阀值，单位秒,默认为10s
slow_query_log_file=HOSTNAME-slow.log 
#慢查询日志文件
log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk 
#上述查询类型且查询时长超过long_query_time，则记录日志
log_queries_not_using_indexes=ON
#不使用索引或使用全索引扫描，不论是否达到慢查询阀值的语句是否记录日志，默认OFF，即不记录
log_slow_rate_limit = 1
#多少次查询才记录，mariadb特有
log_slow_verbosity= Query_plan,explain
#记录内容
log_slow_queries = OFF
#同slow_query_log，MariaDB 10.0/MySQL 5.6.1 版后已删除

二进制日志(备份)

  • 记录导致数据改变或潜在导致数据改变的SQL语句
  • 记录已提交的日志
  • 不依赖于存储引擎类型
  • 功能：通过"重放"日志文件中的事件来生成数据副本
  • 注意：建议二进制日志和数据文件分开存放 
  • 二进制日志记录三种格式
    • 基于"语句"记录：statement，记录语句，默认模式（ MariaDB 10.2.3 版本以下 ），日志量较少
    • 基于"行"记录：row，记录数据，日志量较大，更加安全，建议使用的格式，MySQL8.0默认格式
    • 混合模式：mixed, 让系统自行判定该基于哪种方式进行，默认模式（ MariaDB 10.2.4及版本以上）
  • 格式配置：show variables like 'binlog_format';
  • 二进制日志文件的构成：
有两类文件
1.日志文件：mysql|mariadb-bin.文件名后缀，二进制格式,如： on.000001,mariadb-bin.000002
2.索引文件：mysql|mariadb-bin.index，文本格式,记录当前已有的二进制日志文件列表
  • 二进制日志相关的服务器变量
log_bin=/PATH/BIN_LOG_FILE
#指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项都开启才可以
binlog_format=STATEMENT|ROW|MIXED
#二进制日志记录的格式，mariadb5.5默认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，即不自动删除
  • 二进制日志相关配置
#查看mariadb自行管理使用中的二进制日志文件列表，及大小
SHOW {BINARY | MASTER} LOGS     
#查看使用中的二进制日志文件 
SHOW MASTER STATUS
#在线查看二进制文件中的指定内容
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]


#范例：
show binlog events in 'mysql-bin.000001' from 6516 limit 2,3
  • mysqlbinlog：二进制日志的客户端命令工具，支持离线查看二进制日志
命令格式：
mysqlbinlog [OPTIONS] log_file…
 --start-position=# 指定开始位置
 --stop-position=#
 --start-datetime=  #时间格式：YYYY-MM-DD hh:mm:ss
 --stop-datetime= 
 --base64-output[=name]
        -v -vvv

#范例：
mysqlbinlog --start-position=678 --stop-position=752 /var/lib/mysql/mariadbbin.000003 -v
mysqlbinlog  --start-datetime="2018-01-30 20:30:10"   --stop-datetime="2018-01-30 20:35:22" mariadb-bin.000003 -vvv
  • 二进制日志事件的格式： 
# at 328
#151105 16:31:40 server id 1 end_log_pos 431   Query   thread_id=1     
exec_time=0     error_code=0
use `mydb`/*!*/;
SET TIMESTAMP=1446712300/*!*/;
CREATE TABLE tb1 (id int, name char(30))
/*!*/;  
事件发生的日期和时间：151105 16:31:40
事件发生的服务器标识：server id 1
事件的结束位置：end_log_pos 431
事件的类型：Query 
事件发生时所在服务器执行此事件的线程的ID：thread_id=1
语句的时间戳与将其写入二进制文件中的时间差：exec_time=0
错误代码：error_code=0
事件内容：
GTID：即Global Transaction ID，mysql5.6以mariadb10以上版本专属属性：GTID
  • 清除指定二进制日志
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';
  • 删除所有二进制日志，index文件重新记数
RESET MASTER [TO #];
#删除所有二进制日志文件，并重新生成日志文件，文件名从#开始记数，默认从1开始，一般是master主机第一次启动时执行，MariaDB 10.1.6开始支持TO #
  • 切换日志文件：
FLUSH LOGS;

#范例: 切换二进制日志
[root@centos8 ~]#mysqladmin   flush-binary-log
[root@centos8 ~]#mysqladmin   flush-logs
[root@centos8 ~]#mysql
MariaDB [hellodb]> flush logs;

 

posted @ 2022-07-20 02:08  西瓜的春天  阅读(467)  评论(0)    收藏  举报