@[TOC](第二十三章 MYSQL数据库 手册5 索引、并发控制和事务、日志)
目的
索引，并发控制和事务，日志，管理数据库。
前提
linux系统，已安装好数据库。
⽣成⼤数据，百万条记录
7 ~]# vim /etc/my.cnf
[mysqld]
innodb_file_per_table #添加
7 ~]# systemctl start mariadb #重启服务
hellodb_innodb.sql下载 testlog.sql下载
7 ~]# vim testlog.sql
create table testlog (id int auto_increment primary key,name char(10),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('wang',i),i);
set i = i +1;
end while;
end$$
delimiter ;
将表导⼊hellodb数据库
7 ~]# mysql < hellodb_innodb.sql
7 ~]# mysql hellodb < testlog.sql
执⾏
7 ~]# mysql hellodb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [hellodb]> call pro_testlog;
Query OK, 1 row affected (47.67 sec)
⽣成
MariaDB [hellodb]> select count(*) from testlog;
+----------+
| count(*) |
+----------+
| 999999 |
+----------+
1 row in set (0.01 sec)
主键索引
MariaDB [hellodb]> show indexes from testlog\G
*************************** 1. row ***************************
Table: testlog
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 1000544
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
命令介绍
1、索引
1.1、索引的定义
索引：是特殊数据结构，定义在查找时作为查找条件的字段，在MySQL又称为键key，索引通过存储引擎实现
优点：
索引可以降低服务需要扫描的数据量，减少了IO次数
索引可以帮助服务器避免排序和使用临时表
索引可以帮助将随机I/O转为顺序I/O
缺点：
占用额外空间，影响插入速度
索引类型：
B+ TREE、HASH、R TREE
聚簇（集）索引、非聚簇索引：数据和索引是否存储在一起
主键索引、二级（辅助）索引
稠密索引、稀疏索引：是否索引了每一个数据项
简单索引、组合索引
左前缀索引：取前面的字符做索引
覆盖索引：从索引中即可取出要查询的数据，性能高
1.2、优化
索引优化建议
只要列中含有NULL值，就最好不要在此例设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
尽量使用短索引，如果可以，应该制定一个前缀长度
对于经常在where子句使用的列，最好设置索引
对于有多个列where或者order by子句，应该建立复合索引
对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引
尽量不要在列上进行运算（函数操作和表达式操作）
尽量不要使用not in和<>操作
SQL语句性能优化
查询时，能不要*就不用*，尽量写全字段名
大部分情况连接效率远大于子查询
多表连接时，尽量小表驱动大表，即小表 join 大表
在有大量记录的表分页时使用limit
对于经常使用的查询，可以开启缓存
多使用explain和profile分析查询语句
查看慢查询日志，找出执行时间长的sql语句优化
1.3、管理索引
创建索引：
CREATE [UNIQUE] INDEX index_name ON tbl_name (index_col_name[(length)],...);
ALTER TABLE tbl_name ADD INDEX index_name(index_col_name);
help CREATE INDEX;
删除索引：
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;
优化表空间：
OPTIMIZE TABLE tb_name;
查看索引的使用
SET GLOBAL userstat=1;
SHOW INDEX_STATISTICS;
【例1】主键索引
MariaDB [hellodb]> desc students;
+-----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+----------------+
| StuID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar(50) | NO | | NULL | |
| Age | tinyint(3) unsigned | NO | | NULL | |
| Gender | enum('F','M') | NO | | NULL | |
| ClassID | tinyint(3) unsigned | YES | | NULL | |
| TeacherID | int(10) unsigned | YES | | NULL | |
+-----------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
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:
1 row in set (0.00 sec)
【例2】建⽴索引
MariaDB [hellodb]> create index idx_name on students(name);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
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_name
Seq_in_index: 1
Column_name: Name
Collation: A
Cardinality: 25
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)
1.4、EXPLAIN，分析索引的有效性
EXPLAIN SELECT clause 获取查询执行计划信息，用来查看查询优化器如何执行查询
id: 当前查询语句中，每个SELECT语句的编号
复杂类型的查询有三种：
简单子查询
用于FROM中的子查询
联合查询：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：对结果使用一个外部索引排序
【例3】查询主键索引
MariaDB [hellodb]> explain select * from students where stuid=20;
+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | students | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
possible_keys可能的索引：PRIMARY主键索引。索引key：PRIMARY主键索引。
没有利⽤索引
MariaDB [hellodb]> explain select * from students where age=20;
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 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)
【例4】只有左前缀能利⽤索引
MariaDB [hellodb]> explain select * from students where name like 'S%';
+------+-------------+----------+-------+---------------+----------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+-------+---------------+----------+---------+------+------+-----------------------+
| 1 | SIMPLE | students | range | idx_name | idx_name | 152 | NULL | 4 | Using index condition |
+------+-------------+----------+-------+---------------+----------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> explain select * from students where name like '%S%';
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 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)
25条记录中有6条记录，系统⾃动不⽤索引。
MariaDB [hellodb]> explain select * from students where name like 'x%';
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | students | ALL | idx_name | NULL | NULL | NULL | 25 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
【例5】对testlog这张⼤表做操作。
MariaDB [hellodb]> select * from testlog where age=90000;
+-------+-----------+-------+
| id | name | age |
+-------+-----------+-------+
| 90000 | wang90000 | 90000 |
+-------+-----------+-------+
1 row in set (0.02 sec)
建⽴索引
MariaDB [hellodb]> create index idx_age on testlog(age);
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [hellodb]> select * from testlog where age=90000;
+-------+-----------+-------+
| id | name | age |
+-------+-----------+-------+
| 90000 | wang90000 | 90000 |
+-------+-----------+-------+
1 row in set (0.00 sec)
没有索引，很慢。
MariaDB [hellodb]> select * from testlog where name='wang90000';
+-------+-----------+-------+
| id | name | age |
+-------+-----------+-------+
| 90000 | wang90000 | 90000 |
+-------+-----------+-------+
1 row in set (0.02 sec)
【例6】复合索引，name和age。
MariaDB [hellodb]> desc students;
+-----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+----------------+
| StuID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar(50) | NO | MUL | NULL | |
| Age | tinyint(3) unsigned | NO | | NULL | |
| Gender | enum('F','M') | NO | | NULL | |
| ClassID | tinyint(3) unsigned | YES | | NULL | |
| TeacherID | int(10) unsigned | YES | | NULL | |
+-----------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
创建复合索引
MariaDB [hellodb]> create index idx_name_age on students(name,age);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
查看索引，3，4，同⼀个索引的两个字段
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_name
Seq_in_index: 1
Column_name: Name
Collation: A
Cardinality: 25
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: students
Non_unique: 1
Key_name: idx_name_age
Seq_in_index: 1
Column_name: Name
Collation: A
Cardinality: 25
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: 25
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
4 rows in set (0.01 sec)
MariaDB [hellodb]> select * from students where name like 's%';
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+-------------+-----+--------+---------+-----------+
4 rows in set (0.00 sec)
可以⽤两个索引，实际⽤了idx_name索引。
MariaDB [hellodb]> explain select * from students where name like 's%';
+------+-------------+----------+-------+-----------------------+----------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+-------+-----------------------+----------+---------+------+------+-----------------------+
| 1 | SIMPLE | students | range | idx_name,idx_name_age | idx_name | 152 | NULL | 4 | Using index condition |
+------+-------------+----------+-------+-----------------------+----------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
删除多余的索引
MariaDB [hellodb]> drop index idx_name on students;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [hellodb]> explain select * from students where name like 's%';
+------+-------------+----------+-------+---------------+--------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+-------+---------------+--------------+---------+------+------+-----------------------+
| 1 | SIMPLE | students | range | idx_name_age | idx_name_age | 152 | NULL | 4 | Using index condition |
+------+-------------+----------+-------+---------------+--------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
复合索引，不能跳过第⼀个索引，直接⽤第⼆个
MariaDB [hellodb]> explain select * from students where age='20';
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 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)
【例7】索引的统计
MariaDB [hellodb]> SHOW INDEX_STATISTICS;
Empty set (0.00 sec)
查看索引的使⽤
MariaDB [hellodb]> SET GLOBAL userstat=1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> show variables like 'userstat';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| userstat | ON |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [hellodb]> select * from students where name like 's%';
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+-------------+-----+--------+---------+-----------+
4 rows in set (0.00 sec)
MariaDB [hellodb]> SHOW INDEX_STATISTICS;
+--------------+------------+--------------+-----------+
| Table_schema | Table_name | Index_name | Rows_read |
+--------------+------------+--------------+-----------+
| hellodb | students | idx_name_age | 4 |
+--------------+------------+--------------+-----------+
1 row in set (0.00 sec)
2、并发控制
2.1、锁
锁粒度：
表级锁
行级锁
锁：
读锁：共享锁，只读不可写（包括当前事务） ，多个读互不阻塞
写锁：独占锁，排它锁，写锁会阻塞其它事务（不包括当前事务）的读和它锁
实现
存储引擎：自行实现其锁策略和锁粒度
服务器级：实现了锁，表级锁，用户可显式请求
分类：
隐式锁：由存储引擎自动施加锁
显式锁：用户手动请求
锁策略：在锁粒度及数据安全性寻求的平衡机制
显式使用锁
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]
查询时加写或读锁
死锁：
两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态
【例8】读锁，在⼀台虚拟机上开两个终端
终端1加锁，⾃⼰删，报错。
MariaDB [hellodb]> lock tables students read;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> delete from students where stuid=25;
ERROR 1099 (HY000): Table 'students' was locked with a READ lock and can't be updated
终端2删不掉，定住了，要等待超过超时时长后退出。
7 ~]# mysql hellodb
MariaDB [hellodb]> delete from students where stuid=25;
终端1，解锁
MariaDB [hellodb]> unlock tables;
Query OK, 0 rows affected (0.00 sec)
终端2可以删除
MariaDB [hellodb]> delete from students where stuid=25;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
24 rows in set (0.00 sec)
【例9】写锁，在⼀台虚拟机上开两个终端
终端1加写锁,⾃⼰依然可以读和写
MariaDB [hellodb]> lock tables students write;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> delete from students where stuid=24;
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
+-------+---------------+-----+--------+---------+-----------+
23 rows in set (0.00 sec)
终端2定住了,不能读也不能写。
MariaDB [hellodb]> select * from students;
终端1解写锁
MariaDB [hellodb]> unlock tables;
Query OK, 0 rows affected (0.00 sec)
终端2能读、能写。
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
+-------+---------------+-----+--------+---------+-----------+
23 rows in set (0.00 sec)
【例10】读全局锁，所有表都不能做操作。
MariaDB [hellodb]> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> delete from students where stuid=23;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
MariaDB [hellodb]> create user test@'%' identified by 'centos';
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
释放锁
MariaDB [hellodb]> unlock tables;
Query OK, 0 rows affected (0.00 sec)
2.2、事务
事务Transactions：一组原子性的SQL语句，或一个独立工作单元
事务日志：记录事务信息，实现undo,redo等故障恢复功能
ACID特性：
A：atomicity原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚
C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态
I：Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务所见；隔离有多种隔离级别，
实现并发
D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中
启动事务：
BEGIN
BEGIN WORK
START TRANSACTION
结束事务：
COMMIT：提交
ROLLBACK: 回滚
注意：只有事务型存储引擎中的DML语句方能支持此类操作
自动提交：set autocommit={1|0} 默认为1，为0时设为非自动提交
建议：显式请求和提交事务，而不要使用“自动提交”功能
事务支持保存点：savepoint
SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier
7 ~]# vim /etc/my.cnf
[mysqld]
autocommit=OFF #默认为1，为0时设为非自动提交
7 ~]# systemctl restart mariadb #重启服务
【例11】脏数据。在⼀台虚拟机上开两个终端。
终端1，事务形式添加数据
7 ~]# mysql hellodb
MariaDB [hellodb]> begin ;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> insert students (name,age,gender)values('a',30,'m');
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> insert students (name,age,gender)values('b',25,'f');
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from students; #可以看到刚刚添加的两条记录
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 26 | a | 30 | M | NULL | NULL |
| 27 | b | 25 | F | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
终端2，⽆法查到
7 ~]# mysql hellodb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
+-------+---------------+-----+--------+---------+-----------+
23 rows in set (0.00 sec)
终端1，commit提交
MariaDB [hellodb]> commit;
Query OK, 0 rows affected (0.00 sec)
终端2，可以查到
MariaDB [hellodb]> select * from students; #可以查看到终端1刚刚添加的两条记录
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 26 | a | 30 | M | NULL | NULL |
| 27 | b | 25 | F | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
【例12】DDL语⾔是直接执⾏的，不能撤销。
MariaDB [hellodb]> SHOW TABLES;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| testlog |
| toc |
+-------------------+
8 rows in set (0.00 sec)
MariaDB [hellodb]> begin;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> drop table toc;
Query OK, 0 rows affected (0.01 sec)
MariaDB [hellodb]> rollback;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> select * from toc;
ERROR 1146 (42S02): Table 'hellodb.toc' doesn't exist
【例13】⾃动提交。
MariaDB [hellodb]> show variables like 'auto%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON |
| automatic_sp_privileges | ON |
+--------------------------+-------+
4 rows in set (0.00 sec)
以变量⽅式，改为⾮⾃动提交。
MariaDB [hellodb]> set autocommit=off;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> show variables like 'auto%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | OFF |
| automatic_sp_privileges | ON |
+--------------------------+-------+
4 rows in set (0.00 sec)
MariaDB [hellodb]> insert students(name,age,gender)values('tom',20,'M');
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 26 | a | 30 | M | NULL | NULL |
| 27 | b | 25 | F | NULL | NULL |
| 28 | tom | 20 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
26 rows in set (0.00 sec)
可以回滚，必须提交。
MariaDB [hellodb]> rollback; #回滚
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> select * from students; #发现已经回滚、刚刚添加的tom没生效
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 26 | a | 30 | M | NULL | NULL |
| 27 | b | 25 | F | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.01 sec)
【例14】事务保存点。
MariaDB [hellodb]> begin;
Query OK, 0 rows affected (0.00 sec)
添加记录
MariaDB [hellodb]> insert students(name,age,gender)values('tom',20,'M');
Query OK, 1 row affected (0.00 sec)
添加保存点
MariaDB [hellodb]> savepoint tom;
Query OK, 0 rows affected (0.00 sec)
添加记录
MariaDB [hellodb]> insert students(name,age,gender)values('jerry',22,'M');
Query OK, 1 row affected (0.00 sec)
添加保存点
MariaDB [hellodb]> savepoint jerry;
Query OK, 0 rows affected (0.00 sec)
添加记录
MariaDB [hellodb]> insert students(name,age,gender)values('xiaoqiang',23,'M');
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 26 | a | 30 | M | NULL | NULL |
| 27 | b | 25 | F | NULL | NULL |
| 29 | tom | 20 | M | NULL | NULL |
| 30 | jerry | 22 | M | NULL | NULL |
| 31 | xiaoqiang | 23 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
28 rows in set (0.00 sec)
回滚到jerry保存点
MariaDB [hellodb]> rollback to jerry; #发现刚刚的'xiaoqiang'记录没有了
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 25 | a | 30 | M | NULL | NULL |
| 26 | b | 25 | F | NULL | NULL |
| 28 | tom | 20 | M | NULL | NULL |
| 29 | jerry | 22 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
27 rows in set (0.01 sec)
删除Jerry保存点
MariaDB [hellodb]> release savepoint jerry;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> rollback to jerry;
ERROR 1305 (42000): SAVEPOINT jerry does not exist
2.3、事务隔离级别
事务隔离级别：从上至下更加严格
READ UNCOMMITTED 可读取到未提交数据，产生脏读
READ COMMITTED 可读取到提交数据，但未提交数据不可读，产生不可重复读，即可读取到多个提交数据，
导致每次读取数据不一致
REPEATABLE READ 可重复读，多次读取数据都一致，产生幻读，即读取过程中，即使有其它提交的事务修改
数据，仍只能读取到未修改前的旧数据。此为MySQL默认设置
SERIALIZABLE 可串行化，未提交的读事务阻塞修改事务，或者未提交的修改事务阻塞读事务。导致并发性
能差
MVCC: 多版本并发控制，和事务级别相关
指定事务隔离级别：
服务器变量tx_isolation指定，默认为REPEATABLE-READ，可在GLOBAL和SESSION级进行设置
SET tx_isolation=''
READ-UNCOMMITTED
READ-COMMITTED
REPEATABLE-READ
SERIALIZABLE
服务器选项中指定
vim /etc/my.cnf
[mysqld]
transaction-isolation=SERIALIZABLE
【例15】临时修改事务隔离级别
MariaDB [hellodb]> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)
作为变量改
MariaDB [hellodb]> set tx_isolation='READ-UNCOMMITTED';
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> show variables like 'tx_isolation';
+---------------+------------------+
| Variable_name | Value |
+---------------+------------------+
| tx_isolation | READ-UNCOMMITTED |
+---------------+------------------+
1 row in set (0.00 sec)
【例16】长久修改事务隔离级别，tx_isolation不是服务选项，要⽤对应的选项。
7 ~]# vim /etc/my.cnf
[mysqld]
tx_isolation='READ-UNCOMMITTED'
7 ~]# 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.
启动时报错。
7 ~]# vim /etc/my.cnf
[mysqld]
transaction_isolation='READ-UNCOMMITTED'
#tx_isolation='READ-UNCOMMITTED'
7 ~]# systemctl restart mariadb
【例17】事务隔离级别调整为READ-UNCOMMITTED，第⼆个终端也可以看到脏读了。
终端1
7 ~]# vim /etc/my.cnf
[mysqld]
transaction_isolation='READ-UNCOMMITTED'
7 ~]# systemctl restart mariadb
进⼊数据库mysql；use hellodb；
7 ~]# mysql hellodb;
MariaDB [hellodb]> begin;
Query OK, 0 rows affected (0.00 sec)
终端2，进⼊数据库mysql；use hellodb；
7 ~]# mysql hellodb;
MariaDB [hellodb]> begin;
Query OK, 0 rows affected (0.00 sec)
终端1
MariaDB [hellodb]> insert students (name,age)values('xiaoming',20);
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from students;
...
| 25 | a | 30 | M | NULL | NULL |
| 26 | b | 25 | F | NULL | NULL |
| 27 | xiaoming | 20 | F | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
26 rows in set (0.01 sec)
终端2，可以看到脏读
MariaDB [hellodb]> select * from students;
...
| 25 | a | 30 | M | NULL | NULL |
| 26 | b | 25 | F | NULL | NULL |
| 27 | xiaoming | 20 | F | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
26 rows in set (0.01 sec)
【例18】事务隔离级别调整为READ-COMMITTED，第⼆个终端看不到脏读，必须等待终端⼀提交。产⽣不可重复读，在 ⼀个事务⾥，每次的查看结果不⼀样。
终端1
7 ~]# vim /etc/my.cnf
[mysqld]
transaction_isolation='READ-COMMITTED'
7 ~]# systemctl restart mariadb
终端1，进⼊数据库mysql；use hellodb；
7 ~]# mysql hellodb;
MariaDB [hellodb]> begin;
Query OK, 0 rows affected (0.01 sec)
终端2，进⼊数据库mysql；use hellodb；
7 ~]# mysql hellodb;
MariaDB [hellodb]> begin;
Query OK, 0 rows affected (0.01 sec)
终端1，添加记录，⾃⼰能看到。
MariaDB [hellodb]> insert students (name,age)values('xiaoming',20);
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from students;
...
| 26 | a | 30 | M | NULL | NULL |
| 27 | b | 25 | F | NULL | NULL |
| 28 | xiaoming | 20 | F | NULL | NULL |
| 29 | xiaoqiang | 23 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
27 rows in set (0.00 sec)
终端2，看不到脏读
MariaDB [hellodb]> select * from students;
...
| 26 | a | 30 | M | NULL | NULL |
| 27 | b | 25 | F | NULL | NULL |
| 28 | xiaoming | 20 | F | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
26 rows in set (0.00 sec)
终端1，提交
MariaDB [hellodb]> commit;
Query OK, 0 rows affected (0.01 sec)
终端2，能够看到了
MariaDB [hellodb]> select * from students;
...
| 26 | a | 30 | M | NULL | NULL |
| 27 | b | 25 | F | NULL | NULL |
| 28 | xiaoming | 20 | F | NULL | NULL |
| 29 | xiaoqiang | 23 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
27 rows in set (0.00 sec)
终端1，添加记录
MariaDB [hellodb]> insert students (name,age)values('xiaohong',18);
Query OK, 1 row affected (0.00 sec)
终端2，不可重复度
MariaDB [hellodb]> select * from students;
...
| 27 | b | 25 | F | NULL | NULL |
| 28 | xiaoming | 20 | F | NULL | NULL |
| 29 | xiaoqiang | 23 | M | NULL | NULL |
| 30 | xiaohong | 18 | F | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
28 rows in set (0.00 sec)
【例19】事务隔离级别调整为REPEATABLE-READ，可重复度产⽣幻读，适合事务⽅式，默认的隔离级别。
终端1
7 ~]# vim /etc/my.cnf
[mysqld]
transaction_isolation='REPEATABLE-READ'
7 ~]# systemctl restart mariadb
进⼊数据库mysql；use hellodb；
7 ~]# mysql hellodb;
MariaDB [hellodb]> begin;
终端2，进⼊数据库mysql；use hellodb；
7 ~]# mysql hellodb;
MariaDB [hellodb]> begin;
终端1，添加，提交
MariaDB [hellodb]> insert students (name,age)values('xiaochen',38);
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> commit;
Query OK, 0 rows affected (0.00 sec)
终端2,依然看不到。如果看到了，可能是缓存问题。看不到‘xiaochen’记录
MariaDB [hellodb]> select * from students;
···
| 27 | b | 25 | F | NULL | NULL |
| 28 | xiaoming | 20 | F | NULL | NULL |
| 29 | xiaoqiang | 23 | M | NULL | NULL |
| 30 | xiaohong | 18 | F | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
28 rows in set (0.00 sec)
【例20】事务隔离级别调整为SERIALIZABLE，可串⾏化，安全不冲突，严重影响并发性。未提交的事务阻⽌了读、写操 作。
终端1
7 ~]# vim /etc/my.cnf
[mysqld]
transaction_isolation='SERIALIZABLE'
7 ~]# systemctl restart mariadb
进⼊数据库mysql；use hellodb；
7 ~]# mysql hellodb
MariaDB [hellodb]> begin;
终端2，进⼊数据库mysql；use hellodb；
7 ~]# mysql hellodb
MariaDB [hellodb]> begin;
终端1，删除操作（再次查看发现'第29条已经删除'）
MariaDB [hellodb]> delete from students where stuid=31;
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from students;
...
| 26 | a | 30 | M | NULL | NULL |
| 27 | b | 25 | F | NULL | NULL |
| 28 | xiaoming | 20 | F | NULL | NULL |
| 29 | xiaoqiang | 23 | M | NULL | NULL |
| 30 | xiaohong | 18 | F | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
28 rows in set (0.00 sec)
终端2，定住了，等待提交
MariaDB [hellodb]> select * from students;
终端1,提交
MariaDB [hellodb]> commit;
Query OK, 0 rows affected (0.00 sec)
终端2，⾃动执⾏了。(16.96 sec)
MariaDB [hellodb]> select * from students;
...
| 26 | a | 30 | M | NULL | NULL |
| 27 | b | 25 | F | NULL | NULL |
| 28 | xiaoming | 20 | F | NULL | NULL |
| 29 | xiaoqiang | 23 | M | NULL | NULL |
| 30 | xiaohong | 18 | F | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
28 rows in set (16.96 sec)
【例21】死锁，发现后，直接杀进程。慎⽤，丢数据。
终端1
7 ~]# vim /etc/my.cnf
[mysqld]
transaction_isolation='SERIALIZABLE'
7 ~]# systemctl restart mariadb
进⼊数据库mysql；use hellodb；
7 ~]# mysql hellodb;
MariaDB [hellodb]> begin;
终端2，进⼊数据库mysql；use hellodb；
7 ~]# mysql hellodb;
MariaDB [hellodb]> begin;
终端1，删除操作
MariaDB [hellodb]> delete from students where stuid=30;
Query OK, 1 row affected (0.00 sec)
终端2，定住了，等待提交
MariaDB [hellodb]> select * from students;
终端1，查找进程，杀掉。
MariaDB [hellodb]> show processlist;
+----+------+-----------+---------+---------+------+--------------+------------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+------+-----------+---------+---------+------+--------------+------------------------+----------+
| 4 | root | localhost | hellodb | Query | 4 | Sending data | select * from students | 0.000 |
| 5 | root | localhost | hellodb | Query | 0 | NULL | show processlist | 0.000 |
+----+------+-----------+---------+---------+------+--------------+------------------------+----------+
2 rows in set (0.00 sec)
MariaDB [hellodb]> kill 4;
Query OK, 0 rows affected (0.00 sec)
终端2
MariaDB [hellodb]> select * from students;
ERROR 2013 (HY000): Lost connection to MySQL server during query #查询期间与MySQL服务器的连接中断
3、⽇志
事务日志 transaction log 错误日志 error log 通用日志 general log 慢查询日志 slow query log 二进制日志 binary log 中继日志 reley log
事务日志的写入类型为“追加”，因此其操作为“顺序IO”；通常也被称为：预写式日志 write ahead logging 事务日志文件：ib_logfile0， ib_logfile1
3.1、事务⽇志
事务型存储引擎自行管理和使用，建议和数据文件分开存放
redo log
undo log
Innodb事务日志相关配置：
show variables like '%innodb_log%';
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支持
【例22】事务⽇志，ib_logfile0，ib_logfile1
7 ~]# ll /var/lib/mysql/
total 28700
-rw-rw---- 1 mysql mysql 16384 May 30 16:42 aria_log.00000001
-rw-rw---- 1 mysql mysql 52 May 30 16:42 aria_log_control
drwx------ 2 mysql mysql 150 May 30 01:08 hellodb
-rw-rw---- 1 mysql mysql 18874368 May 30 16:44 ibdata1
-rw-rw---- 1 mysql mysql 5242880 May 30 16:44 ib_logfile0 <--事务⽇志
-rw-rw---- 1 mysql mysql 5242880 May 30 01:05 ib_logfile1 <--事务⽇志
drwx------ 2 mysql mysql 4096 May 30 01:05 mysql
srwxrwxrwx 1 mysql mysql 0 May 30 16:42 mysql.sock
drwx------ 2 mysql mysql 4096 May 30 01:05 performance_schema
drwx------ 2 mysql mysql 6 May 30 01:05 test
MariaDB [(none)]> show variables like '%innodb_log%';
+---------------------------+---------+
| Variable_name | Value |
+---------------------------+---------+
| innodb_log_block_size | 512 | 块大小'512字节'
| innodb_log_buffer_size | 8388608 | 缓存大小'8M'
| innodb_log_file_size | 5242880 | 每个日志文件大小
| innodb_log_files_in_group | 2 | 日志组成员个数
| innodb_log_group_home_dir | ./ | 事务文件路径
+---------------------------+---------+
5 rows in set (0.00 sec)
【例23】事务⽇志，⾃定义存放地址，放到不同的磁盘上，io效率更⾼。
7 ~]# mkdir -pv /data/mysql
mkdir: created directory ‘/data/mysql’
7 ~]# chown mysql.mysql /data/mysql
7 ~]# ll -d /data/mysql
drwxr-xr-x 2 mysql mysql 6 May 30 18:46 /data/mysql
7 ~]# vim /etc/my.cnf
[mysqld]
innodb_log_group_home_dir=/data/mysql
7 ~]# systemctl restart mariadb
7 ~]# ll /data/mysql/
total 10240
-rw-rw---- 1 mysql mysql 5242880 May 30 18:49 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 May 30 18:49 ib_logfile1
【例24】选项innodb_flush_log_at_trx_commit ，决定着什么时候把事务⽇志的信息写⼊磁盘。
7 ~]# mysql
MariaDB [(none)]> show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1 |
+--------------------------------+-------+
1 row in set (0.00 sec)
3.2、错误⽇志
错误日志
mysqld启动和关闭过程中输出的事件信息
mysqld运行中产生的错误信息
event scheduler运行一个event时产生的日志信息
在主从复制架构中的从服务器上启动从服务器线程时产生的信息
错误日志相关配置 SHOW GLOBAL VARIABLES LIKE 'log_error'
错误文件路径
log_error=/PATH/TO/LOG_ERROR_FILE
是否记录警告信息至错误日志文件
log_warnings=1|0 默认值1
【例25】错误⽇志
MariaDB [(none)]> show global variables like 'log_error';
+---------------+------------------------------+
| Variable_name | Value |
+---------------+------------------------------+
| log_error | /var/log/mariadb/mariadb.log |
+---------------+------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> exit
Bye
7 ~]# ll /var/log/mariadb/mariadb.log
-rw-r----- 1 mysql mysql 25934 May 30 18:50 /var/log/mariadb/mariadb.log
3.3、通⽤⽇志
通用日志：记录对数据库的通用操作，包括错误的SQL语句
文件：file，默认值
表：table
通用日志相关设置
general_log=ON|OFF
general_log_file=HOSTNAME.log
log_output=TABLE|FILE|NONE
【例26】通⽤⽇志
临时启动
7 ~]# mysql
MariaDB [(none)]> show global variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log | OFF |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> set global general_log=on;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> show global variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log | ON |
+---------------+-------+
1 row in set (0.00 sec)
主机名+log
[root@centos7 ~]# ll /var/lib/mysql/
-rw-rw---- 1 mysql mysql 273 May 30 18:55 centos7.log #主机名+log
永久启动
7 ~]# vim /etc/my.cnf
[mysqld]
general_log
7 ~]# systemctl restart mariadb
7 ~]# mysql
MariaDB [(none)]> show global variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log | ON |
+---------------+-------+
1 row in set (0.00 sec)
跟踪通⽤⽇志：每⼀步，操作都有
7 ~]# tail -f /var/lib/mysql/centos7.log
2 Query select @@version_comment limit 1
220530 18:59:31 2 Query show global variables like 'general_log'
【例27】把通⽤⽇志写在表中
7 ~]# vim /etc/my.cnf
[mysqld]
general_log
log_output=TABLE
7 ~]# systemctl restart mariadb
7 ~]# mysql mysql
MariaDB [mysql]> select * from general_log;
+----------------------------+-----------------------------------+-----------+-----------+--------------+---------------------------------------------+
| event_time | user_host | thread_id | server_id | command_type | argument |
+----------------------------+-----------------------------------+-----------+-----------+--------------+---------------------------------------------+
...
| 2022-05-30 19:05:58.800235 | root[root] @ localhost [] | 2 | 0 | Query | select @@version_comment limit 1 |
| 2022-05-30 19:06:16.988863 | root[root] @ localhost [] | 2 | 0 | Query | select * from general_log |
+----------------------------+-----------------------------------+-----------+-----------+--------------+---------------------------------------------+
31 rows in set (0.00 sec)
3.4、慢查询⽇志
慢查询日志：记录执行查询时长超出指定时长的操作
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 新版已废弃
【例28】慢查询⽇志，默认关闭
临时启⽤
MariaDB [mysql]> show global variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF |
+----------------+-------+
1 row in set (0.00 sec)
MariaDB [mysql]> set global slow_query_log=on;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> show global variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | ON |
+----------------+-------+
1 row in set (0.00 sec)
⽣成慢查询⽇志
7 ~]# ll /var/lib/mysql/
-rw-rw---- 1 mysql mysql 176 May 30 19:16 centos7-slow.log
慢查询⽇志的默认超时时间10秒
7 ~]# mysqladmin variables |grep ong_query_time
| long_query_time | 10.000000
永久启⽤
7 ~]# vim /etc/my.cnf
[mysqld]
slow_query_log
long_query_time=1
7 ~]# systemctl restart mariadb
7 ~]# mysql
MariaDB [(none)]> show global variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | ON |
+----------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> use hellodb;
MariaDB [hellodb]> select tid,sleep(1) from teachers;
+-----+----------+
| tid | sleep(1) |
+-----+----------+
| 1 | 0 |
| 2 | 0 |
| 3 | 0 |
| 4 | 0 |
+-----+----------+
4 rows in set (4.03 sec)
记录进⼊⽇志
7 ~]# cat /var/lib/mysql/centos7-slow.log
/usr/libexec/mysqld, Version: 5.5.60-MariaDB (MariaDB Server). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
/usr/libexec/mysqld, Version: 5.5.60-MariaDB (MariaDB Server). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 220530 19:21:45
# User@Host: root[root] @ localhost []
# Thread_id: 2 Schema: hellodb QC_hit: No
# Query_time: 4.030022 Lock_time: 0.000117 Rows_sent: 4 Rows_examined: 4
use hellodb;
SET timestamp=1653909705;
select tid,sleep(1) from teachers;
【例29】慢查询⽇志，只要不⽤索引就计⼊慢查询⽇志
7 ~]# vim /etc/my.cnf
[mysqld]
log_queries_not_using_indexes
slow_query_log
long_query_time=1
7 ~]# systemctl restart mariadb
7 ~]# mysql hellodb
MariaDB [hellodb]> show global variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON |
+-------------------------------+-------+
1 row in set (0.00 sec)
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
...
7 ~]# cat /var/lib/mysql/centos7-slow.log
/usr/libexec/mysqld, Version: 5.5.60-MariaDB (MariaDB Server). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
/usr/libexec/mysqld, Version: 5.5.60-MariaDB (MariaDB Server). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 220530 19:21:45
# User@Host: root[root] @ localhost []
# Thread_id: 2 Schema: hellodb QC_hit: No
# Query_time: 4.030022 Lock_time: 0.000117 Rows_sent: 4 Rows_examined: 4
use hellodb;
SET timestamp=1653909705;
select tid,sleep(1) from teachers;
/usr/libexec/mysqld, Version: 5.5.60-MariaDB (MariaDB Server). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 220530 19:30:37
# User@Host: root[root] @ localhost []
# Thread_id: 2 Schema: hellodb QC_hit: No
# Query_time: 0.000200 Lock_time: 0.000067 Rows_sent: 28 Rows_examined: 28
use hellodb;
SET timestamp=1653910237;
select * from students;
3.5、⼆进制⽇志
二进制日志：记录导致数据改变或潜在导致数据改变的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
log_bin=/PATH/BIN_LOG_FILE：指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项都开启才可
binlog_format=STATEMENT|ROW|MIXED：二进制日志记录的格式，默认STATEMENT
max_binlog_size=1073741824：单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G
说明：文件达到上限时的大小未必为指定的精确值
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
【例30】可以⽤shell脚本操作mysql数据库，使⽤mysql的-e参数可以执⾏各种sql的(创建，删除，增，删，改、查)等各种操作 。在mysql外删除数据库hellodb。
[root@centos7 ~]# ll /var/lib/mysql/
total 28708
-rw-rw---- 1 mysql mysql 16384 May 30 19:29 aria_log.00000001
-rw-rw---- 1 mysql mysql 52 May 30 19:29 aria_log_control
-rw-rw---- 1 mysql mysql 1182 May 30 19:05 centos7.log
-rw-rw---- 1 mysql mysql 1027 May 30 19:30 centos7-slow.log
drwx------ 2 mysql mysql 150 May 30 01:08 hellodb <--删除此项
-rw-rw---- 1 mysql mysql 18874368 May 30 19:30 ibdata1
-rw-rw---- 1 mysql mysql 5242880 May 30 19:30 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 May 30 01:05 ib_logfile1
drwx------ 2 mysql mysql 4096 May 30 01:05 mysql
srwxrwxrwx 1 mysql mysql 0 May 30 19:29 mysql.sock
drwx------ 2 mysql mysql 4096 May 30 01:05 performance_schema
drwx------ 2 mysql mysql 6 May 30 01:05 test
[root@centos7 ~]# mysql -e 'drop database hellodb' #在mysql外删除数据库hellodb。
[root@centos7 ~]# ll /var/lib/mysql/ #发现刚刚的'hellodb'已删除
total 28708
-rw-rw---- 1 mysql mysql 16384 May 30 19:29 aria_log.00000001
-rw-rw---- 1 mysql mysql 52 May 30 19:29 aria_log_control
-rw-rw---- 1 mysql mysql 1182 May 30 19:05 centos7.log
-rw-rw---- 1 mysql mysql 1027 May 30 19:30 centos7-slow.log
-rw-rw---- 1 mysql mysql 18874368 May 30 19:30 ibdata1
-rw-rw---- 1 mysql mysql 5242880 May 30 19:41 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 May 30 01:05 ib_logfile1
drwx------ 2 mysql mysql 4096 May 30 01:05 mysql
srwxrwxrwx 1 mysql mysql 0 May 30 19:29 mysql.sock
drwx------ 2 mysql mysql 4096 May 30 01:05 performance_schema
drwx------ 2 mysql mysql 6 May 30 01:05 test
【例31】每个表的结构和数据分开存放，10版本以上默认。
7 ~]# vim /etc/my.cnf
[mysqld]
innodb_file_per_table
7 ~]# systemctl restart mariadb
导⼊数据库
7 ~]# mysql < hellodb_innodb.sql
.frm为表结构⽂件，.ibd为表数据⽂件。
7 ~]# ll /var/lib/mysql/hellodb/
total 1432
-rw-rw---- 1 mysql mysql 8636 May 30 19:49 classes.frm
-rw-rw---- 1 mysql mysql 98304 May 30 19:49 classes.ibd
-rw-rw---- 1 mysql mysql 8630 May 30 19:49 coc.frm
-rw-rw---- 1 mysql mysql 98304 May 30 19:49 coc.ibd
-rw-rw---- 1 mysql mysql 8602 May 30 19:49 courses.frm
-rw-rw---- 1 mysql mysql 98304 May 30 19:49 courses.ibd
-rw-rw---- 1 mysql mysql 61 May 30 19:49 db.opt
-rw-rw---- 1 mysql mysql 8658 May 30 19:49 scores.frm
-rw-rw---- 1 mysql mysql 98304 May 30 19:49 scores.ibd
-rw-rw---- 1 mysql mysql 8736 May 30 19:49 students.frm
-rw-rw---- 1 mysql mysql 98304 May 30 19:49 students.ibd
-rw-rw---- 1 mysql mysql 8656 May 30 19:49 teachers.frm
-rw-rw---- 1 mysql mysql 98304 May 30 19:49 teachers.ibd
-rw-rw---- 1 mysql mysql 8622 May 30 19:49 toc.frm
-rw-rw---- 1 mysql mysql 98304 May 30 19:49 toc.ibd
【例32】⼆进制⽇志记录格式
7 ~]# mysql hellodb
MariaDB [hellodb]> show global variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
7 ~]# vim /etc/my.cnf
[mysqld]
binlog_format=row
7 ~]# systemctl restart mariadb
7 ~]# mysql hellodb
MariaDB [hellodb]> show global variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)
【例33】⼆进制⽇志,默认没有启⽤
组要同时开启两项：是否记录和指定⽂件位置
MariaDB [hellodb]> show global variables like 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [hellodb]> show global variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
1 row in set (0.00 sec)
sql_log_bin，变量⾥可以改，临时禁⽤（还原数据库时）。log_bin只读变量不能改，只能改配置。
MariaDB [hellodb]> set global log_bin=off;
ERROR 1238 (HY000): Variable 'log_bin' is a read only variable
7 ~]# vim /etc/my.cnf
[mysqld]
log_bin
7 ~]# systemctl restart mariadb
7 ~]# ll /var/lib/mysql/
...
-rw-rw---- 1 mysql mysql 245 May 30 20:01 mariadb-bin.000001
-rw-rw---- 1 mysql mysql 21 May 30 20:01 mariadb-bin.index
【例34】建议⼆进制⽇志和数据库⽂件分开存放，提⾼可⽤性
7 ~]# mkdir /data/bin
7 ~]# chown mysql.mysql /data/bin
7 ~]# vim /etc/my.cnf
7 ~]# vim /etc/my.cnf
[mysqld]
log_bin=/data/bin/mysql-bin
7 ~]# systemctl restart mariadb
新的⼆进制⽇志⽣成
7 ~]# ll /data/bin
total 8
-rw-rw---- 1 mysql mysql 245 May 30 20:04 mysql-bin.000001
-rw-rw---- 1 mysql mysql 27 May 30 20:04 mysql-bin.index
7 ~]# cat /data/bin/mysql-bin.index
/data/bin/mysql-bin.000001
【例35】⼆进制⽇志,修改会记录，查看不会修改记录。
7 ~]# ll /data/bin
total 8
-rw-rw---- 1 mysql mysql 245 May 30 20:04 mysql-bin.000001
-rw-rw---- 1 mysql mysql 27 May 30 20:04 mysql-bin.index
7 ~]# mysql
MariaDB [(none)]> grant all on hellodb.* to test@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
7 ~]# ll /data/bin
total 8
-rw-rw---- 1 mysql mysql 377 May 30 20:06 mysql-bin.000001 <--大小377
-rw-rw---- 1 mysql mysql 27 May 30 20:04 mysql-bin.index
7 ~]# mysql
MariaDB [(none)]> select * from hellodb.students; #查看不会修改记录
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
...
7 ~]# ll /data/bin
total 8
-rw-rw---- 1 mysql mysql 377 May 30 20:06 mysql-bin.000001 <--大小377
-rw-rw---- 1 mysql mysql 27 May 30 20:04 mysql-bin.index
7 ~]# mysql
MariaDB [(none)]> insert hellodb.students (stuid,name,age)values(26,'tom',28); #修改、添加一条记录
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> select * from hellodb.students;
...
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 26 | tom | 28 | F | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
26 rows in set (0.00 sec)
7 ~]# ll /data/bin
total 8
-rw-rw---- 1 mysql mysql 586 May 30 20:10 mysql-bin.000001 <--修改会记录、发现文件大小从之前的'377'变成'586'
-rw-rw---- 1 mysql mysql 27 May 30 20:04 mysql-bin.index
【例36】⼆进制⽇志的⼤⼩，单个⼆进制⽇志⽂件的最⼤体积，到达最⼤值会⾃动滚动，默认为1G。
MariaDB [(none)]> show global variables like 'max_binlog_size';
+-----------------+------------+
| Variable_name | Value |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0.00 sec)
【例37】查看⼆进制⽇志。
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 586 |
+------------------+-----------+
1 row in set (0.00 sec)
MariaDB [(none)]> show binlog events in 'mysql-bin.000001';
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 245 | Server ver: 5.5.60-MariaDB, Binlog ver: 4 |
| mysql-bin.000001 | 245 | Query | 1 | 377 | grant all on hellodb.* to test@'%' identified by '123456' |
| mysql-bin.000001 | 377 | Query | 1 | 441 | BEGIN |
| mysql-bin.000001 | 441 | Query | 1 | 559 | insert hellodb.students (stuid,name,age)values(26,'tom',28) |
| mysql-bin.000001 | 559 | Xid | 1 | 586 | COMMIT /* xid=12 */ |
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> show binlog events in 'mysql-bin.000001' from 441;
+------------------+-----+------------+-----------+-------------+-------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+------------+-----------+-------------+-------------------------------------------------------------+
| mysql-bin.000001 | 441 | Query | 1 | 559 | insert hellodb.students (stuid,name,age)values(26,'tom',28) |
| mysql-bin.000001 | 559 | Xid | 1 | 586 | COMMIT /* xid=12 */ |
+------------------+-----+------------+-----------+-------------+-------------------------------------------------------------+
2 rows in set (0.00 sec)
【例38】重启数据库，会⾃动⽣成新的⼆进制⽂件
[root@centos7 ~]# ll /data/bin
total 8
-rw-rw---- 1 mysql mysql 586 May 30 20:10 mysql-bin.000001
-rw-rw---- 1 mysql mysql 27 May 30 20:04 mysql-bin.index
[root@centos7 ~]# systemctl restart mariadb
[root@centos7 ~]# ll /data/bin
total 12
-rw-rw---- 1 mysql mysql 605 May 30 20:24 mysql-bin.000001
-rw-rw---- 1 mysql mysql 245 May 30 20:24 mysql-bin.000002 <--⽣成新的⼆进制⽂件
-rw-rw---- 1 mysql mysql 54 May 30 20:24 mysql-bin.index
【例39】⽣成⼤⽂件，⼆进制⽂件变得巨⼤，testlog.sql
7 ~]# mysql hellodb < testlog.sql
执⾏前，⽂件⼤⼩
[root@centos7 ~]# du -sh /data/bin
12K /data/bin
[root@centos7 ~]# du -sh /var/lib/mysql/
30M /var/lib/mysql/
[root@centos7 ~]# mysql hellodb
MariaDB [hellodb]> call pro_testlog();
Query OK, 1 row affected, 3 warnings (20.95 sec)
MariaDB [hellodb]> select count(*) from testlog;
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.08 sec)
执⾏后，⽂件⼤⼩
7 ~]# du -sh /data/bin
60M /data/bin
7 ~]# du -sh /var/lib/mysql/
38M /var/lib/mysql/
7 ~]# ll /data/bin
total 61176
-rw-rw---- 1 mysql mysql 605 May 30 20:24 mysql-bin.000001
-rw-rw---- 1 mysql mysql 29078480 May 30 20:33 mysql-bin.000002
-rw-rw---- 1 mysql mysql 81 May 31 00:47 mysql-bin.index
3.6、mysqlbinlog：⼆进制⽇志的客户端命令⼯具
命令格式：
mysqlbinlog [OPTIONS] log_file…
--start-position=# 指定开始位置
--stop-position=#
--start-datetime=
--stop-datetime=
时间格式：YYYY-MM-DD hh:mm:ss
--base64-output[=name]
-v -vvv
清除指定二进制日志：
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;
【例40】mysqlbinlog：⼆进制⽇志的客户端命令⼯具。-v查看，解密的命令。
7 ~]# mysqlbinlog /data/bin/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
#220530 20:04:57 server id 1 end_log_pos 245 Start: binlog v 4, server v 5.5.60-MariaDB created 220530 20:04:57 at startup
ROLLBACK/*!*/;
...
# at 559
#220530 20:10:56 server id 1 end_log_pos 586 Xid = 12
COMMIT/*!*/;
# at 586
#220530 20:24:33 server id 1 end_log_pos 605 Stop
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
【例41】查看指定的⼆进制命令
看mysql-bin.000002⾥的全部内容
7 ~]# mysqlbinlog /data/bin/mysql-bin.000002 -v
查看指定的⼆进制命令
#查看'/data/bin/mysql-bin.000002'文件、开始位置'29078160'、结束位置'29078453'
7 ~]# mysqlbinlog --start-position=29078160 --stop-position=29078453 /data/bin/mysql-bin.000002 -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
#220530 20:24:40 server id 1 end_log_pos 245 Start: binlog v 4, server v 5.5.60-MariaDB created 220530 20:24:40 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
iLeUYg8BAAAA8QAAAPUAAAABAAQANS41LjYwLU1hcmlhREIAbG9nAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACIt5RiEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAw/a44w==
'/*!*/;
# at 29078160
#220530 20:33:23 server id 1 end_log_pos 29078187 Xid = 300016
COMMIT/*!*/;
# at 29078187
#220530 20:33:23 server id 1 end_log_pos 29078258 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1653914003/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 29078258
#220530 20:33:23 server id 1 end_log_pos 29078286 Intvar
SET INSERT_ID=100000/*!*/;
# at 29078286
#220530 20:33:23 server id 1 end_log_pos 29078453 Query thread_id=3 exec_time=0 error_code=0
use `hellodb`/*!*/;
SET TIMESTAMP=1653914003/*!*/;
insert into testlog(name,age) values (concat('wang', NAME_CONST('i',100000)), NAME_CONST('i',100000))
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
可以导出，恢复数据库⽤
7 ~]# mysqlbinlog --start-position=29078160 --stop-position=29078453 /data/bin/mysql-bin.000002 > a.sql
【例42】清理⼆进制⽇志⽂件命令
MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 605 |
| mysql-bin.000002 | 29078480 |
+------------------+-----------+
2 rows in set (0.00 sec)
MariaDB [hellodb]> flush logs;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 605 |
| mysql-bin.000002 | 29078480 |
| mysql-bin.000003 | 245 |
+------------------+-----------+
3 rows in set (0.00 sec)
删除3之前的
MariaDB [hellodb]> PURGE BINARY LOGS TO 'mysql-bin.000003';
Query OK, 0 rows affected (0.01 sec)
MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000003 | 245 |
+------------------+-----------+
1 row in set (0.00 sec)
真的删除了
7 ~]# ll /data/bin
total 8
-rw-rw---- 1 mysql mysql 245 May 31 01:12 mysql-bin.000003
-rw-rw---- 1 mysql mysql 27 May 31 01:13 mysql-bin.index
【例43】并重新⽣成⽇志⽂件，做主从复试时需要⽤到！10.1版本以上可以指定从哪⾥开始。
MariaDB [hellodb]> reset master;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 245 |
+------------------+-----------+
1 row in set (0.00 sec)
在数据库外，刷新
7 ~]# ll /data/bin/
total 8
-rw-rw---- 1 mysql mysql 245 May 31 01:15 mysql-bin.000001
-rw-rw---- 1 mysql mysql 27 May 31 01:15 mysql-bin.index
7 ~]# mysqladmin flush-logs
7 ~]# ll /data/bin/
total 16
-rw-rw---- 1 mysql mysql 245 May 31 01:17 mysql-bin.000001
-rw-rw---- 1 mysql mysql 288 May 31 01:18 mysql-bin.000002
-rw-rw---- 1 mysql mysql 81 May 31 01:18 mysql-bin.index
