技术教程 从删库到跑路(二） 正文

从删库到跑路(二）

2022-07-31

服务器选项配置及性能优化

mysqld选项，服务器系统变量和服务器状态变量

mysql

mariadb --是服务器选项

注意：其中有些参数支持运行时修改，会立即生效；有些参数不支持，且只能通过修改配置文件，并重启服务器程序生效；有些参数作用域是全局的，且不可改变；有些可以为每个用户提供单独（会话）的设置

获取mysqld的可用选项列表

mysqld --help --verbose
mysqld --print-defaults 
# 获取默认设置

设置服务器选项方法

在命令行中设置

./mysqld_safe --skip-name-resolve=1

在配置文件my.cnf中设置

skip_name_resolve=1

服务器系统变量分全局和会话两种

获取系统变量

SHOW GLOBAL VARIABLES;
SHOW [SESSION] VARIABLES;
SELECT @@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;

查看最大并发连接数

MariaDB [(none)]> SHOW VARIABLES LIKE '%connection%';
+---------------------------+-----------------+
| Variable_name             | Value           |
+---------------------------+-----------------+
| extra_max_connections     | 1               |
| max_connections           | 151             |
| max_user_connections      | 0               |
+---------------------------+-----------------+

修改最大并发连接数值为3000

MariaDB [(none)]> SET @@max_connections=3000;
ERROR 1229 (HY000): Variable 'max_connections' is a GLOBAL variable and should be set with SET GLOBAL
# 显示为全局变量，所以按照全局变量的语法更改
MariaDB [(none)]> SET GLOBAL max_connections=3000;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW VARIABLES LIKE '%connection%';
+---------------------------+-----------------+
| Variable_name             | Value           |
+---------------------------+-----------------+
| character_set_connection  | utf8            |
| collation_connection      | utf8_general_ci |
| default_master_connection |                 |
| extra_max_connections     | 1               |
| max_connections           | 3000            |
| max_user_connections      | 0               |
+---------------------------+-----------------+
6 rows in set (0.00 sec)

虽然更改了但在下一次重新启动时该变量又会丢失，所以我们将它写入配置文件中my.cnf

写入配置文件中并生效，要求改变量同时为服务器变量

[mysqld]
max_connections=3000

systemctl restart mariadb
# 重启服务查看是否生效

服务器状态变量分全局和会话两种

状态变量（只读）：用于保存mysqld运行中的统计数据的变量，不可更改

SHOW GLOBAL STATUS;
SHOW [SESSION] STATUS;

服务器综合性变量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
#将"||"视为连接操作符而非“或运算符”

SET SQL_MODE='traditional'

查询缓存技术

查询的执行路径

查询缓存原理Query Cache

缓存SELECT操作或预处理查询的结果集和SQL语句，当有新的SELECT语句或预处理查询语句请求，先去查询缓存，判断是否存在可用的记录集，判断标准：与缓存的SQL语句，是否完全一样，区分大小写

优缺点

不需要对SQL语句做任何解析和执行，当然语法解析必须通过在先，直接从Query Cache中获得查询结果，提高查询性能
查询缓存的判断规则，不够智能，也即提高了查询缓存的使用门槛，降低其效率；
查询缓存的使用，会增加检查和清理Query Cache中记录集的开销

哪些查询可能不会被缓存

  • 查询语句中加了SQL_NO_CACHE参数
  • 查询语句中含有获得值的函数，包含自定义函数，如：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_size=100M形式，在修改全局变量只能显示102400000

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语句才会缓存；其它均不予缓存

[query_cache_type](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)

优化查询缓存

查询缓存相关的状态变量

SHOW GLOBAL STATUS LIKE ‘Qcache%';

其他变量

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_hits与Qcache_inserts加起来是总的查询次数

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%

优化查询缓存逻辑

InnoDB存储引擎

InnoDB存储引擎的缓冲池

通常InnoDB存储引擎缓冲池的命中不应该小于99%

查看相关状态变量

show global status like 'innodb%read%'\G

Innodb_buffer_pool_reads 
# 表示从物理磁盘读取页的次数

Innodb_buffer_pool_read_ahead
# 预读的次数

Innodb_buffer_pool_read_ahead_evicted
# 预读页，但是没有读取就从缓冲池中被替换的页数量，一般用来判断预读的效率

Innodb_buffer_pool_read_requests
# 从缓冲池中读取页次数

Innodb_data_read
# 总共读入的字节数

Innodb_data_reads
# 发起读取请求的次数，每次读取可能需要读取多个页

Innodb缓冲池命中率计算

平均每次读取的字节数

索引

索引：是特殊数据结构，定义在查找时作为查找条件的字段，在MySQL又称为键key，索引通过存储引擎实现

优点

索引可以降低服务需要扫描的数据量，减少了IO次数
索引可以帮助服务器避免排序和使用临时表
索引可以帮助将随机I/O转为顺序I/O

缺点

占用额外空间，影响插入速度

索引类型：

  • B+ TREE、HASH、R TREE

  • 聚簇（集）索引、非聚簇索引：数据和索引是否存储在一起

  • 主键索引、二级（辅助）索引

  • 稠密索引、稀疏索引：是否索引了每一个数据项

  • 简单索引、组合索引

    • 左前缀索引：取前面的字符做索引“a%”如果“%a"则影响效率
    • 覆盖索引：从索引中即可取出要查询的数据，性能高

  • 越是唯一的内容越适合建立索引

二叉树

A：根节点

G、H、I：叶子节点

B、C、D、E、F：分支节点

红黑树

红黑树是一种平衡树

B-TREE索引 balance tree

  • 磁盘块1：P1>17 17<=P2<=35 P3 > 35

  • 2个数据3个指针

  • 减少访问次数从而优化查找速度

  • 查找次数不固定

  • 针对范围查找有缺陷

空间数据索引R-Tree（ Geospatial indexing ）

MyISAM支持地理空间索引，可以使用任意维度组合查询，使用特有的函数访问，常用于做地理数据存储，使用不多
InnoDB从MySQL5.7之后也开始支持

全文索引(FULLTEXT)

在文本中查找关键词，而不是直接比较索引中的值，类似搜索引擎
InnoDB从MySQL 5.6之后也开始支持

B+TREE索引

B+TREE结构更加扁平，将数据的完整信息在叶子节点上

B+Tree索引：顺序存储，每一个叶子节点到根结点的距离是相同的；左前缀索引，适合查询范围类的数据

可以使用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列索引无效

聚簇和非聚簇索引

聚簇和非聚簇索引，主键和二级索引

冗余和重复索引

冗余索引：（A），（A，B）
重复索引：已经有索引，再次建立索引

索引优化策略

  • 独立地使用列：尽量避免其参与运算，独立的列指索引列不能是表达式的一部分，也不能是函数的参数，在where条件中，始终将索引列单独放在比较符号的一侧
  • 左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性来评估
  • 索引选择性：不重复的索引值和数据表的记录总数的比值（90%以上就差不多）
  • 多列索引：AND操作时更适合使用多列索引，而非为每个列创建单独的索引
  • 选择合适的索引列顺序：无排序和分组时，将选择性最高放左侧

索引优化建议

  • 只要列中含有NULL值，就最好不要在此例设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
  • 尽量使用短索引（节约磁盘空间），如果可以，应该制定一个前缀长度
  • 对于经常在where子句使用的列，最好设置索引
  • 对于有多个列where或者order by子句，应该建立复合索引
  • 对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引
  • 尽量不要在列上进行运算（函数操作和表达式操作）
  • 尽量不要使用not in和<>操作

SQL语句性能优化

  • 查询时，能不要*就不用*，尽量写全字段名
  • 大部分情况连接效率远大于子查询
  • 多表连接时，尽量小表驱动大表，即小表 join 大表
  • 在有大量记录的表分页时使用limit
  • 对于经常使用的查询，可以开启缓存
  • 多使用explain和profile分析查询语句
  • 查看慢查询日志，找出执行时间长的sql语句优化

管理索引

创建索引

CREATE INDEX [UNIQUE] index_name ON tbl_name (index_col_name[(length)],...);

ALTER TABLE tbl_name ADD INDEX index_name(index_col_name);
# 通过新增索引的方式增加

help CREATE INDEX;

示例：在students

删除索引

DROP INDEX index_name ON tbl_name;

ALTER TABLE tbl_name DROP INDEX index_name(index_col_name);

查看索引

SHOW INDEXES FROM [db_name.]tbl_name;

示例：

MariaDB [(none)]> SHOW INDEXES FROM hellodb.students\G;
*************************** 1. row ***************************
        Table: students
   Non_unique: 0
     Key_name: PRIMARY
     # 索引为主键索引
 Seq_in_index: 1
  Column_name: StuID
    Collation: A
  Cardinality: 25
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
   # B+TREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

ERROR: No query specified


MariaDB [hellodb]> explain select * from students where age = 20 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 25
        Extra: Using where
1 row in set (0.00 sec)

ERROR: No query specified

针对age建立索引

MariaDB [hellodb]> create index idx_age on students(age);
Query OK, 25 rows affected (0.00 sec)              
Records: 25  Duplicates: 0  Warnings: 0
# 注意索引的命名以idx_开头

查看indexes

MariaDB [hellodb]> show indexes from students\G;
*************************** 1. row ***************************
        Table: students
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: StuID
    Collation: A
  Cardinality: 25
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: students
   Non_unique: 1
     Key_name: idx_age
 Seq_in_index: 1
  Column_name: Age
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
2 rows in set (0.00 sec)
# 建立了索引后可以看出
ERROR: No query specified

查看是否利用了索引

MariaDB [hellodb]> explain select * from students where age = 20\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
         type: ref
possible_keys: idx_age
          key: idx_age
          # 显示利用了我们建立的idx_age 索引
      key_len: 1
          ref: const
         rows: 2
        Extra: 
1 row in set (0.00 sec)

ERROR: No query specified

优化表空间

OPTIMIZE TABLE tb_name;

查看索引的使用

SET GLOBAL userstat=1;
# 服务器端命令：默认是未开启，功能为查看索引是否被利用

SHOW INDEX_STATISTICS;

有时候即使有索引，但并不一定会利用索引，是因为数据库的优化过程，所搜索的内容占比太大，还不如全表扫描

建立复合索引

MariaDB [hellodb]> create index idx_name_age on students(name,age);


MariaDB [hellodb]> show indexes from students \G;

*************************** 3. row ***************************
        Table: students
   Non_unique: 1
     Key_name: idx_name_age
 Seq_in_index: 1
  Column_name: Name
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 4. row ***************************
        Table: students
   Non_unique: 1
     Key_name: idx_name_age
 Seq_in_index: 2
  Column_name: Age
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
4 rows in set (0.00 sec)

需要注意的是，在搜索中如果跳过使用Name字段来直接利用age搜索时，是不能够利用索引的。

建立唯一键索引

CREATE UNIQUE INDEX ON table_name(KEY);

MariaDB [hellodb]> CREATE UNIQUE INDEX uni_age ON testlog(age);
# 在testlog表中建立以age为唯一键索引

MariaDB [hellodb]> show indexes from testlog \G;


*************************** 2. row ***************************
        Table: testlog
   Non_unique: 0
   # 是否为唯一键索引
     Key_name: uni_age
 Seq_in_index: 1
  Column_name: age
    Collation: A
  Cardinality: 100165
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment:

实验：快速建立10万行数据

create table testlog (id int auto_increment primary key,name char(20),age int default 20);

delimiter $$

create procedure  pro_testlog() 
begin  
declare i int;
set i = 1; 
while i <=100000 
do  insert into testlog(name,age) values (concat('flamenca',i),i); 
set i = i +1; 
end while; 
end$$

delimiter ;

该脚本定义了一个存储过程

使用该脚本的方法

[root@localhost ~]# mysql hellodb < testlog.sql

[root@localhost ~]# mysql hellodb
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| testlog           |
| toc               |
+-------------------+
8 rows in set (0.00 sec)

# 调用存储过程名称


MariaDB [hellodb]> call pro_testlog;

利用索引后的查询时间对比

MariaDB [hellodb]> select * from testlog where name='flamenca99999';
+-------+---------------+-------+
| id    | name          | age   |
+-------+---------------+-------+
| 99999 | flamenca99999 | 99999 |
+-------+---------------+-------+
1 row in set (0.02 sec)
# 未利用索引0.02秒

建立索引

MariaDB [hellodb]> create index idx_name on testlog(name);
Query OK, 0 rows affected (0.16 sec)                
Records: 0  Duplicates: 0  Warnings: 0

重新查询

MariaDB [hellodb]> select * from testlog where name='flamenca99999';
+-------+---------------+-------+
| id    | name          | age   |
+-------+---------------+-------+
| 99999 | flamenca99999 | 99999 |
+-------+---------------+-------+
1 row in set (0.00 sec)
# 利用索引后时间显示0.00

利用EXPLAIN解析

MariaDB [hellodb]> explain select * from testlog where name='flamenca99999'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: testlog
         type: ref
possible_keys: idx_name
          key: idx_name
      key_len: 61
          ref: const
         rows: 1
        Extra: Using index condition
1 row in set (0.00 sec)

阿里的技术手册中规定数据超过500万条后开始分库

在age字段上建立唯一键索引

MariaDB [hellodb]> CREATE UNIQUE INDEX uni_age ON testlog(age);

EXPLAIN

通过EXPLAIN来分析索引的有效性

输出信息说明

EXPLAIN SELECT clause

获取查询执行计划信息，用来查看查询优化器如何执行查询

id: 当前查询语句中，每个SELECT语句的编号

复杂类型的查询有三种：

  • 简单子查询

  • 用于FROM中的子查询

  • 联合查询：UNION

注意：UNION查询的分析结果会出现一个额外匿名临时表

select_type

简单查询为SIMPLE
复杂查询：

  • SUBQUERY 简单子查询
  • PRIMARY 最外面的SELECT
  • DERIVED 用于FROM中的子查询
  • UNION UNION 语句的第一个之后的SELECT语句
  • UNION RESULT 匿名临时表
table

SELECT语句关联到的表

type

关联类型或访问类型，即MySQL决定的如何去查询表中的行的方式，以下顺序，性能从低到高

ALL: 全表扫描
  • index：根据索引的次序进行全表扫描；如果在Extra列出现“Using index”表示了使用覆盖索引，而非全表扫描
  • range：有范围限制的根据索引实现范围扫描；扫描位置始于索引中的某一点，结束于另一点
  • ref: 根据索引返回表中匹配某单个值的所有行
  • eq_ref：仅返回一个行，但与需要额外与某个参考值做比较
  • const, system: 直接返回单个行
possible_keys：查询可能会用到的索引
key: 查询中使用到的索引
key_len: 在索引使用的字节数
ref: 在利用key字段所表示的索引完成查询时所用的列或某常量值
rows：MySQL估计为找所有的目标行而需要读取的行数

Extra：额外信息

  • Using index：MySQL将会使用覆盖索引，以避免访问表
  • Using where：MySQL服务器将在存储引擎检索后，再进行一次过滤
  • Using temporary：MySQL对结果排序时会使用临时表
  • Using filesort：对结果使用一个外部索引排序

并发控制

锁粒度

  • 表级锁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]
# 查询时加写或读锁

查看mysql中的进程

show processlist;

删除进程编号

kill $

事务

事务Transactions：一组原子性的SQL语句，或一个独立工作单元

事务日志

记录事务信息，实现undo,redo等故障恢复功能

redo是将已完成的事务重做

undo是将未完成的事务撤销

ACID特性

  • A：atomicity原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚

  • C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态

  • I：Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务所见；隔离有多种隔离级别，实现并发

    一个数据在被访问修改的中间状态，我们称为脏数据

  • D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中

ACID所有的信息都将记录于事务日志中

Transaction生命周期

启动事务

BEGIN
BEGIN WORK
START TRANSACTION

结束事务

COMMIT
#提交

ROLLBACK
# 回滚
# 注意：只有事务型存储引擎中的DML语句方能支持此类操作
# DDL语言是撤销不了的！

自动提交

set autocommit={1|0}
# 默认为1，为0时设为非自动提交
# 建议：显式请求和提交事务，而不要使用“自动提交”功能

# 查看该变量的默认值
MariaDB [hellodb]> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

# 将事务设置为非自动提交

[root@localhost ~]# vim /etc/my.cnf

#
# This group is read both both by the client and the server
# use it for options that affect everything
#


[client-server]

[mysqld]
skip-grant-tables
autocommit=OFF


# 改完后重启服务

[root@localhost ~]# systemctl restart mariadb


MariaDB [hellodb]> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

此时如果执行删除行操作，在你的终端上是显示删除，但是在其他终端上数据仍然存在，此为事务的隔离性

即不执行COMMIT不会提交的

事务支持保存点：savepoint

# 可以选择性的选择撤销到保存点，即部分撤销
SAVEPOINT identifier

ROLLBACK [WORK] TO [SAVEPOINT] identifier

RELEASE SAVEPOINT identifier

事务隔离级别

从上至下更加严格

READ UNCOMMITTED
# 可读取到未提交数据，产生脏读

READ COMMITTED
# 可读取到提交数据，但未提交数据不可读，产生不可重复读，即可读取到多个提交数据，导致每次读取数据不一致

REPEATABLE READ
# 可重复读，多次读取数据都一致，产生幻读，即读取过程中，即使有其它提交的事务修改数据，仍只能读取到未修改前的旧数据。此为MySQL默认设置
# 好处是数据库的备份过程中，视为所有数据都不变

SERIALIZABILE
# 可串行化，未提交的读事务阻塞修改事务，或者未提交的修改事务阻塞读事务。导致并发性能差

指定事务隔离级别：

服务器变量tx_isolation指定，默认为REPEATABLE-READ，可在GLOBAL和SESSION级进行设置，注意，该变量虽然是服务器变量，但不是服务器选项，选项名为：transaction-isolation

SET tx_isolation=''
READ-UNCOMMITTED
READ-COMMITTED
REPEATABLE-READ
SERIALIZABLE

服务器选项中指定

vim /etc/my.cnf
[mysqld]
transaction-isolation=SERIALIZABLE

MVCC

多版本并发控制，和事务级别相关

并发控制

死锁现象

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

  • 数据库会直接牺牲其中一个事务

事务日志：
事务日志的写入类型为“追加”，因此其操作为“顺序IO”；通常也被称为：预写式日志write ahead logging
事务日志文件： ib_logfile0， ib_logfile1

日志

事务日志 transaction log
错误日志 error log
通用日志 general log
慢查询日志 slow query log
二进制日志 binary log
中继日志 reley log

事务日志 transaction log

事务型存储引擎自行管理和使用，建议和数据文件分开存放
redo log
undo log

Innodb事务日志相关配置：

show variables like '%innodb_log%';

MariaDB [hellodb]> show variables like '%innodb_log%';
+-------------------------------+------------+
| Variable_name                 | Value      |
+-------------------------------+------------+
| innodb_log_arch_dir           |            |
| innodb_log_arch_expire_sec    | 0          |
| innodb_log_archive            | OFF        |
| innodb_log_block_size         | 0          |# 块大小
| innodb_log_buffer_size        | 16777216   |# 缓存
| innodb_log_checksum_algorithm | DEPRECATED |
| innodb_log_checksums          | ON         |
| innodb_log_compressed_pages   | ON         |
| innodb_log_file_size          | 50331648   |# 文件大小设置，默认500M
| innodb_log_files_in_group     | 2          |# 日志数量，默认为2
| innodb_log_group_home_dir     | ./         |# 事务日志存放路径，相对路径
| innodb_log_optimize_ddl       | ON         |
| innodb_log_write_ahead_size   | 8192       |
+-------------------------------+------------+
13 rows in set (0.00 sec)


[root@localhost ~]# ll /var/lib/mysql/

-rw-rw---- 1 mysql mysql 50331648 Jul 24 15:37 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Jul 13 20:38 ib_logfile1

# 事务日志文件，当第一个事务文件写满，开始写第二个文件ib_logfile1，当该文件写满后，覆盖并重写ib_logfile0文件

#####################################################

innodb_log_file_size 5242880 	# 每个日志文件大小
innodb_log_files_in_group 2 	# 日志组成员个数
innodb_log_group_home_dir ./ 	# 事务文件路径
innodb_flush_log_at_trx_commit 	# 默认为1


innodb_flush_log_at_trx_commit
# 说明：设置为1，同时sync_binlog = 1表示最高级别的容错 innodb_use_global_flush_log_at_trx_commit的值确定是否可以使用SET语句重置此变量
# 1 默认情况下，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。 这是完全遵守ACID特性
# 0 提交时没有任何操作; 而是每秒执行一次日志缓冲区写入和刷新。 这样可以提供更好的性能，但服务器崩溃可以清除最后一秒的事务
# 2 每次提交后都会写入日志缓冲区，但每秒都会进行一次刷新。 性能比0略好一些，但操作系统或停电可能导致最后一秒的交易丢失
# 3 模拟MariaDB 5.5组提交（每组提交3个同步），此项MariaDB 10.0支持

事务日志优化

优化建议

建议把事务日志放在高性能磁盘中或者高性能分区中

错误日志

mysqld启动和关闭过程中输出的事件信息
mysqld运行中产生的错误信息
event scheduler运行一个event时产生的日志信息
在主从复制架构中的从服务器上启动从服务器线程时产生的信息

错误日志相关配置

SHOW GLOBAL VARIABLES LIKE 'log_error'
错误文件路径
log_error=/PATH/TO/LOG_ERROR_FILE
是否记录警告信息至错误日志文件
log_warnings=1|0 默认值1

通用日志

记录对数据库的通用操作，包括错误的SQL语句

文件：file，默认值
表：table

通用日志相关设置

general_log=ON|OFF
general_log_file=HOSTNAME.log
log_output=TABLE|FILE|NONE

MariaDB [(none)]> show variables like 'general%';
+------------------+---------------+
| Variable_name    | Value         |
+------------------+---------------+
| general_log      | OFF           |
| general_log_file | localhost.log |
+------------------+---------------+
2 rows in set (0.00 sec)
# 默认是为OFF状态
# 在/var/lib/mysql文件夹中是没有localhost.log文件的

# 写入配置文件启用general_log

# 重启mariadb服务
MariaDB [(none)]> show variables like 'general%';
+------------------+---------------+
| Variable_name    | Value         |
+------------------+---------------+
| general_log      | ON            |
| general_log_file | localhost.log |
+------------------+---------------+
2 rows in set (0.00 sec)
# 重启后生成localhost.log文件

[root@localhost ~]# tail /var/lib/mysql/localhost.log 
Time                 Id Command    Argument
200727 16:21:29	    8 Connect	root@localhost as anonymous on 
		    8 Query	select @@version_comment limit 1
200727 16:21:51	    8 Quit	
200727 16:21:54	    9 Connect	root@localhost as anonymous on 
		    9 Query	select @@version_comment limit 1
200727 16:22:12	    9 Query	show variable like 'general%'

# 此时该文件开始记录信息

我们可以将该表的内容放入数据库中吗？

可以

MariaDB [(none)]> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)
# 查询该变量显示默认是将该文件以文件FILE形式进行存储
# 在/etc/my.cnf中将该变量改成table

# 重启mariadb服务

# 在数据库mysql中的general_log表中可以看到通用日志
MariaDB [mysql]> select * from general_log \G;
*************************** 1. row ***************************
  event_time: 2020-07-27 16:30:49.655889
   user_host: [root] @ localhost []
   thread_id: 8
   server_id: 1
command_type: Connect
    argument: root@localhost as anonymous on 
*************************** 2. row ***************************
  event_time: 2020-07-27 16:30:49.661627
   user_host: [root] @ localhost []
   thread_id: 8
   server_id: 1
command_type: Query
    argument: select @@version_comment limit 1
...

清库后重启还原

[root@localhost ~]# rm -rf /var/lib/mysql/*
[root@localhost ~]# !sys
systemctl restart mariadb

慢查询日志

记录执行查询时长超出指定时长的操作

slow_query_log=ON|OFF # 开启或关闭慢查询
long_query_time=N # 慢查询的阀值，单位秒
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 新版已废弃

# 慢查询的默认值为10秒
MariaDB [(none)]> show variables like 'long%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

# 默认慢查询日志是未启用的，可以通过slow_query_log查看到
MariaDB [(none)]> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
1 row in set (0.00 sec)

# 在my.cnf中启用
# 重启mariadb
MariaDB [(none)]> show variables like 'slow_query_%';
+---------------------+--------------------+
| Variable_name       | Value              |
+---------------------+--------------------+
| slow_query_log      | ON                 |
| slow_query_log_file | localhost-slow.log |
+---------------------+--------------------+
2 rows in set (0.00 sec)

# 将慢查询时间定义为3秒
# 在my.cnf中将变量long_query_time定义为3

MariaDB [(none)]> show variables like 'long%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 3.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

# 通过查询/var/lib/mysql/localhost-slow.log文件可以看到记录内容,
#######注意，必须将log_output值设置为FILE##############

[root@localhost ~]# tail /var/lib/mysql/localhost-slow.log 
Tcp port: 0  Unix socket: (null)
Time                 Id Command    Argument
# Time: 200727 17:15:27
# User@Host: [root] @ localhost []
# Thread_id: 8  Schema: hellodb  QC_hit: No
# Query_time: 4.000357  Lock_time: 0.000000  Rows_sent: 1  Rows_examined: 0
# Rows_affected: 0
use hellodb;
SET timestamp=1595841327;
select sleep(4);




# log_queries_not_using_indexes不使用索引的记录也会被记录下来

# Time: 200727 17:17:17
# User@Host: [root] @ localhost []
# Thread_id: 8  Schema: hellodb  QC_hit: No
# Query_time: 0.000290  Lock_time: 0.000159  Rows_sent: 2  Rows_examined: 25
# Rows_affected: 0
SET timestamp=1595841437;
select * from students where age=20;

查询慢语句详细过程的另外一种方法

# 假设有个语句如下
MariaDB [hellodb]> select sleep(1) from students;


#在慢日志中如下
[root@localhost ~]# tail /var/lib/mysql/localhost-slow.log -f
Tcp port: 0  Unix socket: (null)
Time                 Id Command    Argument
# Time: 200727 17:41:52
# User@Host: [root] @ localhost []
# Thread_id: 8  Schema: hellodb  QC_hit: No
# Query_time: 25.008985  Lock_time: 0.000100  Rows_sent: 25  Rows_examined: 25
# Rows_affected: 0
use hellodb;
SET timestamp=1595842912;
select sleep(1) from students;

# 在设置中打开profiling
MariaDB [hellodb]> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     |  ON   |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> set profiling=on;
Query OK, 0 rows affected (0.00 sec)

# 测试语句如下
MariaDB [hellodb]> select sleep(1) from teachers;
+----------+
| sleep(1) |
+----------+
|        0 |
|        0 |
|        0 |
|        0 |
+----------+
4 rows in set (4.01 sec)

# 用show profiling命令来看
MariaDB [hellodb]> show profiles;
+----------+------------+---------------------------------+
| Query_ID | Duration   | Query                           |
+----------+------------+---------------------------------+
|        1 | 0.00064595 | show variables like 'profiling' |
|        2 | 0.00013756 | SELECT DATABASE()               |
|        3 | 0.00023233 | show databases                  |
|        4 | 0.00013069 | show tables                     |
|        5 | 4.00205526 | select sleep(1) from teachers   |
+----------+------------+---------------------------------+

# 查询query 5

MariaDB [hellodb]> show profile for query 5;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000116 |
| checking permissions | 0.000005 |
| Opening tables       | 0.000013 |
| After opening tables | 0.000003 |
| System lock          | 0.000005 |
| Table lock           | 0.000004 |
| init                 | 0.000010 |
| optimizing           | 0.000005 |
| statistics           | 0.000009 |
| preparing            | 0.000011 |
| executing            | 0.000002 |
| Sending data         | 0.000021 |
| User sleep           | 1.000854 |
| User sleep           | 1.000379 |
| User sleep           | 1.000306 |
| User sleep           | 1.000220 |
| end                  | 0.000015 |
| query end            | 0.000004 |
| closing tables       | 0.000002 |
| Unlocking tables     | 0.000010 |
| freeing items        | 0.000009 |
| updating status      | 0.000011 |
| logging slow query   | 0.000039 |
| cleaning up          | 0.000002 |
+----------------------+----------+
24 rows in set (0.00 sec)

# 展示出了各个阶段详情的时间

整理优化数据库

optimize table TABLE_NAME

二进制日志

记录导致数据改变或潜在导致数据改变的SQL语句
记录已提交的日志
不依赖于存储引擎类型
功能：通过“重放”日志文件中的事件来生成数据副本
注意：建议二进制日志和数据文件分开存放

记录的是增删改，就如同一个记录器一样，该日志是不清理的，查操作是不会记录的

了解二进制日志就了解了数据库的整个操作过程

利用二进制日志可以做数据库的还原

虽然名叫二进制日志，其实是文本混合二进制

中继日志

relay log

主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件

二进制日志记录格式

# 二进制日志记录三种格式
基于“语句”记录：statement，记录语句，默认模式
基于“行”记录：row，记录数据，日志量较大
混合模式：mixed, 让系统自行判定该基于哪种方式进行

# 格式配置
show variables like ‘binlog_format';

二进制日志文件的构成

有两类文件
日志文件：mysql|mariadb-bin.文件名后缀，二进制格式
如： mariadb-bin.000001
索引文件：mysql|mariadb-bin.index，文本格式

二进制日志相关的服务器变量

sql_log_bin=ON|OFF	
# 是否记录二进制日志，默认ON
MariaDB [hellodb]> show variables like 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | ON    |
+---------------+-------+
1 row in set (0.00 sec)
# 该变量可以临时修改

log_bin=/PATH/BIN_LOG_FILE 
# 指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项都开启才可
MariaDB [hellodb]> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.00 sec)
# 写在配置文件中，同时为服务器选项
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
log_bin
# 注意，该变量后加内容是给日志加名称，所以只要写log_bin即可
-rw-rw---- 1 mysql mysql      332 Jul 27 18:53 localhost-bin.000001
-rw-rw---- 1 mysql mysql       23 Jul 27 18:53 localhost-bin.index
# localhost-bin.index为索引
# 默认情况下二进制文件与数据库文件放在一起，为了安全起见，建议分开发
# 假设放在 /data/logbin目录下，并设置好所属组主
[root@localhost mysql]# mkdir -p /data/logbin
[root@localhost mysql]# chown mysql.mysql /data/logbin
# 更改配置文件路径方式
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
log_bin=/data/logbin/mysql-bin
# 保存后重启mariadb服务
[root@localhost mysql]# ll /data/logbin/
total 8
-rw-rw---- 1 mysql mysql 328 Jul 27 18:58 mysql-bin.000001
-rw-rw---- 1 mysql mysql  30 Jul 27 18:58 mysql-bin.index
# 利用二进制日志管理工具可以查看，该日志文件增长速度比数据库还快，所以要用大硬盘
# 重启mariadb后会生成新的文件

binlog_format=STATEMENT|ROW|MIXED
# 二进制日志记录的格式，默认STATEMENT
# 生产中还是最好用ROW
# 也可以让数据库决定MIXED

max_binlog_size=1073741824
# 单个二进制日志文件的最大体积，到达最大值会自动滚动生成新文件，默认为1G
# 说明：文件达到上限时的大小未必为指定的精确值

sync_binlog=1|0
# 设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘
# 1 只要发生二进制日志会立即写入，会影响磁盘性能，但更安全

expire_logs_days=N
# 二进制日志可以自动删除的天数。 默认为0，即不自动删除

二进制日志相关配置

SHOW {BINARY | MASTER} LOGS;
# 查看mariadb自行管理使用中的二进制日志文件列表，及大小
MariaDB [(none)]> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       328 |
+------------------+-----------+
1 row in set (0.00 sec)


SHOW MASTER STATUS;
# 查看使用中的二进制日志文件
MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      328 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)


SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
show binlog events in ‘mysql-bin.000001' from 6516 limit 2,3
# 查看二进制文件中的指定内容，文本方式展现
MariaDB [hellodb]> show binlog events in 'mysql-bin.000001'\G;
*************************** 1. row ***************************
   Log_name: mysql-bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 256
       Info: Server ver: 10.2.32-MariaDB-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin.000001
        Pos: 256
 Event_type: Gtid_list
  Server_id: 1
End_log_pos: 285
       Info: []
*************************** 3. row ***************************
   Log_name: mysql-bin.000001
        Pos: 285
 Event_type: Binlog_checkpoint
  Server_id: 1
End_log_pos: 328
       Info: mysql-bin.000001
3 rows in set (0.00 sec)

mysqlbinlog

二进制日志的客户端命令工具


mysqlbinlog [OPTIONS] log_file…
--start-position=#  
# 指定开始位置
--stop-position=#
--start-datetime=
--stop-datetime=
# 时间格式：YYYY-MM-DD hh:mm:ss
--base64-output[=name]
-v -vvv

# 示例

mysqlbinlog --start-position=6787 --stop-position=7527 /var/lib/mysql/mariadb-bin.000003 -v
mysqlbinlog --start-datetime="2018-01-30 20:30:10" --stop-datetime="2018-01-30 20:35:22" mariadb-bin.000003 -vvv

不加-v

[root@localhost logbin]# mysqlbinlog --start-position=265 --stop-position=285  mysql-bin.000001 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200727 18:58:31 server id 1  end_log_pos 256 CRC32 0x6ef271ca 	Start: binlog v 4, server v 10.2.32-MariaDB-log created 200727 18:58:31 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
V7MeXw8BAAAA/AAAAAABAAABAAQAMTAuMi4zMi1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABXsx5fEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAEEwQADQgICAoKCgHKcfJu
'/*!*/;
ERROR: Error in Log_event::read_log_event(): 'Event invalid', data_len: 0, event_type: 29
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

加-v

[root@localhost logbin]# mysqlbinlog --start-position=265 --stop-position=285  mysql-bin.000001 -v
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200727 18:58:31 server id 1  end_log_pos 256 CRC32 0x6ef271ca 	Start: binlog v 4, server v 10.2.32-MariaDB-log created 200727 18:58:31 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
V7MeXw8BAAAA/AAAAAABAAABAAQAMTAuMi4zMi1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABXsx5fEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAEEwQADQgICAoKCgHKcfJu
'/*!*/;
ERROR: Error in Log_event::read_log_event(): 'Event invalid', data_len: 0, event_type: 29
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

将二进制日志导出为sql文件

mysqlbinlog --start-position=265 --stop-position=285  mysql-bin.000001 -v > /data/test.sql

导出的文件可以用于重演

二进制日志事件的格式

# 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’; # 删除3之前的日志
PURGE BINARY LOGS BEFORE '2017-01-23';
PURGE BINARY LOGS BEFORE '2017-03-22 09:25:30';

删除所有二进制日志，index文件重新记数

RESET MASTER [TO #];删除所有二进制日志文件，并重新生成日志文件，文件名从#开始记数，默认从1开始，一般是master主机第一次启动时执行，MariaDB10.1.6开始支持TO #

切换日志文件

FLUSH LOGS;

事务日志与二进制日志区别

事务日志：记录事务的未完成形态，数据库中默认是开启事务日志的，默认情况下是循环覆盖的过程，两个5M文件写满后重新覆盖

二进制日志：记录事务的完成形态，默认是不开启二进制日志，不断地增长，速度比数据库还快，二进制日志是记录数据库的增删改，利用二进制日志可以做数据库还原的操作的，二进制日志最好与数据库相分离，不要放在一起。

本文地址：https://blog.csdn.net/FlamencaH/article/details/107637802

《从删库到跑路(二）.doc》

下载本文的Word格式文档，以方便收藏与打印。

Copyright 2023. 昆居客 - IT编程,互联网信息技术文档大全!