Mysql 高级特性

1. 字符集

以下四个级别自上而下，下层级别的字符集如果没有指定，则使用上层级别的字符集

1.1 服务器级别

1.2 数据库级别

1.3 表级别

1.4 列级别

1.5 utf8 和 utf8mb4

utf8 其实就是 utf8mb3 使用 1~3个字节表示一个字符
utf8mb4 就是使用 1~4个字节表示一个字符

一般来说 utf8 就可以满足基本需求，如果要存储 emoji 表情则必须使用 utf8mb4

2. sql_mode

# 查看当前模式
show variables like 'sql_mode';

2.1 宽松模式

在插入数据时，填写错误的数据，也可能不会报错。比如给一个 int 类型的字段赋值 “abc”

2.2 严格模式

3. linux 数据库目录结构

3.1 数据库文件存储路径

# 默认是 /usr/lib/mysql
show variables like 'sql_mode';

3.2 数据库命令路径

/usr/bin
/usr/sbin

3.3 数据库配置文件

/etc/mysql(my.cnf)
/usr/share/mysql-8.0

4. 默认数据库 mysql

在默认的数据库 mysql 中有一个 user 表，其中可以看到所有的用的的信息，这里要注意 Host 和 User 两个字段是联合主键

5. 权限控制

开发中尽量不要使用 root 用户，root 用的权限太大了，将 root 的密码放在代码中也是不安全的，一旦泄露将导致数据库变得不安全。

正确的做法就是使用 mysql 的权限控制

5.1 权限表

在默认的 mysql 数据库下，存在 user、db、tables_priv、columns_priv、procs_priv 等表中进行了权限的记录

5.2 角色管理

为了方便权限的分配，我们将具有相同权限的一类人定义为一个角色，角色就是一些权限的集合。

将权限分配的方式将变为：
指定权限赋予指定用户==> ①指定权限赋予角色，②指定角色赋予指定用户，③激活角色

6. Mysql 逻辑架构

整体架构包括以下五层，属于MySQL服务的其实就只有中间的三层

1. Connections
   各种语言或管理工具和数据库建立连接，比如Java通过JDBC连接数据库，navicat 连接数据库
2. MySQL—连接层
   • connection pool: 连接池
     负责和客户端建立 TCP 连接，然后从连接池中分配一个线程和客户端交互
3. MySQL—服务层
   • SQL Interface: SQL 接口
     接收sql命令，返回结果
   • Parser: 解析器
     解析sql语句，进行语法分析、语义分析，生成语法树
   • Optimizer: 优化器
     生成执行计划（表连接顺序、使用什么索引等）
   • Cache & Buffer：查询缓存
     缓存sql执行的结果（8.0中就没有了，因为缓存命中率极低，比如多了一个空格，有一个大小写不一致都会导致缓存无法命中）
4. MySQL—引擎层
   负责数据的存储和读取和存储在文件中的数据交互
5. 存储层
   表结构的定义、表中的数据等都是存储在文件系统之中
   

7. SQL 执行过程

1. 建立TCP连接

2. 查询缓存（8.0后没有）

3. sql解析器生成语法树
   

4. sql优化器生成执行计划

5. sql执行器执行执行计划

6. 返回结果

7. 缓存结果（8.0后没有）
   

8. 缓冲池

9. 存储引擎

9.1 InnoDB 和 MyISAM 对比

对比	InnoDB	MyISAM
外键	支持	不支持
事务	支持	不支持
锁	支持行锁，适合高并发	只支持表级锁
缓存	索引和数据都要缓存(索引和数据存储在同一个文件中 .ibd)，需要更多的内存	只缓存索引不缓存数据(索引和数据存放在两个文件中，索引在 .MYI 数据在 .MYD)

• 只有增加和查询 MyISAM 性能更好，还需要更新和删除则需要使用 InnoDB
• 查询 COUNT(*) 时，MyISAM 更快因为有一个单独的字段存储表中数据的总条数
• MyISAM 不支持事务崩溃后无法恢复
• 没有充分的理由一律使用 InnoDB

10. 索引

索引的本质是数据结构，帮助MySQL高效查找数据

优点： 提高查询速度，减少磁盘IO次数
缺点： 占用较大磁盘空间，降低了更新表的速度，更新删除时都需要动态维护索引

10.1 索引的分类

10.1.1 聚簇索引

根据主键进行排序
数据页内的记录连接成一个单链表
B+树的叶子节点中，存储了完整的一条记录

优点：查询速度快、数据保存在叶子节点中，不存在回表操作。
缺点：插入速度严重依赖于插入顺序，因为是单链表，所以如果不按照顺序插入，可能会导致页分裂。所以一般都使用一个自增的字段做为主键

10.1.2 非聚簇索引（二级索引、辅助索引）

没有存储完整的记录而是存储主键的值，一次查询需要查找两个B+树，先通过二级索引找到主键。再去主键所在的B+树找到对应的记录，这个过程也叫做回表。

10.1.3 联合索引

同时为多个列建立索引，只会生成一个B+树
排序时按照联合索引的顺序，比如创建一个 c1,c2 的联合索引，那么就是先按照 c1 进行排序，如果 c1 的值相同再按照 c2 进行排序

10.2 MyISAM 索引与 InnoDB 索引对比

• MyISAM 的索引都是非聚簇索引
• MyISAM 中叶子节点中存储的是记录所在的地址而不是完整的记录信息
• MyISAM 数据和索引是存储在两个文件中的，而 InnoDB 是一个文件。所以 MyISAM 中叶子节点中存储的是记录所在的地址，而 InnoDB 中存储的是一条完整的记录。
• MyISAM 回表操作是很快的，因为是直接根据地址去找到记录，而 InnoDB 则是要去遍历聚簇索引的B+树
• InnoDB 是一定有主键的，如果没有指定主键会自动选择一个非空且有唯有约束的列做为主键，如果还是没有就会生成一个隐藏列做为主键

10.3 B-Tree 和 B+Tree

MySQL中使用的都是B+Tree

为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引？

• B+Tree的查询效率更加稳定，必须要查询到叶子节点才能获取到数据。但B-Tree可能在根节点就能查询到数据
• B+Tree磁盘读写代价更低，同样大小的数据页对于B+Tree可以存储的关键字更多

10.4 MySQL 8.0 索引新特性

10.4.1 降序索引

默认情况下对一个字段 column1 创建索引后，是按照升序排列来构建B+Tree，但是如果我们的查询条件是这样的 order by column1 desc , 这时索引就无法发挥作用了。

# 8.0 中支持创建降序索引后面加上一个 desc 即可
create index index1 on tablename(column1 desc)

10.4.2 隐藏索引

隐藏索引相当于对索引进行逻辑删除，我们不确定删除索引之后会有怎样的影响，如果出错还需要重新建索引，这是就可以将索引隐藏。

隐藏索引后查询就不会使用该索引，但是更新表时依然会维护隐藏索引，所以如果索引已经没有用的要及时删除

#切换成隐藏索引 
ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; 
#切换成非隐藏索引
ALTER TABLE tablename ALTER INDEX index_name VISIBLE; 

10.5 索引设计原则

10.5.1 适合创建索引的情况

1. 频繁出现在 where 条件中
2. 频繁出现在 group by 条件中
3. 频繁出现在 order by 条件中
4. 多表连接的 where 条件中或连接的字段
5. 字段有唯一性限制
6. DISTINCT 字段

注意事项:

1. 选择区分度高的字段
2. 多个字段都要建索引时，考虑建联合索引
3. 使用最频繁的放在联合索引的左侧（最左前缀原则）
4. 列的类型尽量小比如 int 和 bigint 选择 int
5. 对于字符串类型创建索引时要指定长度，一般来说长度20区分度就比较高

# 可以使用这个公式来寻找长度设置多少，区分度比较好。越接近 1 区分度越高
count(distinct left(列名, 索引长度))/count(*)

10.5.2 不适合创建索引的情况

1. 不用在 where、group by、order by 这些查询条件中
2. 表中数据量很小
3. 字段重复值很多，区分度较小
4. 重复更新的字段（每次更新都要维护索引）
5. 更新频繁的表不要创建过多的索引（维护索引的成本较高）
6. 无法排序的值不建议加索引比如 uuid
7. 不要定义重复的索引，比如创建了一个联合索引 c1,c2 然后又对 c1 创建了索引

10.5.3 索引失效的情况

1. 使用函数、计算
2. like 模糊查询时以 % 开头
3. is not null
4. !=
5. or 的前后
6. 类型转换（包括隐式的类型转换）
7. 字符集不一致
8. 范围查找右侧的列索引失效。例如对于联合索引 c1,c2,c3 如果where 条件如下 c1=1 and c2 > 10 and c3=5。由于 c2 进行了范围查询，所以联合索引只能使用到 c1,c2 的部分，c3 的部分就失效了

11. 性能分析

11.1 慢查询日志

非性能调优阶段不要开启此功能，否则可能会影响性能

# 查询是否开启慢查询日志
show variables like 'slow_query_log';
# 查询慢查询日志文件所在路径
show variables like 'slow_query_log_file';
# 开启慢查询日志
set global slow_query_log='ON';
# 查询慢查询的时间阈值(超过这个时间就被认定为慢查询)
show variables like '%long_query_time%'
# 修改慢查询阈值为1秒
set long_query_time=1;
# 查询当前有多少条慢查询
show status like '%slow_queries%';

11.1 慢查询日志分析工具 mysqldumpslow

#得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/xx-slow.log
#得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/xx-slow.log
#得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/xx-slow.log
#另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/xx-slow.log | more

11.2 explain

12. 查询优化方案

12.1 关联查询优化

主要遵循原则小表驱动大表

• join 连接的字段类型要保持一致，字符集编码也要一致，否则在连接字段上加索引也会失效
• 给被驱动表的连接字段上增加索引
• 连接过程中会使用到 join buffer，会将驱动表中的数据加载到 join buffer 中，所以尽量避免select 驱动表中一些无用的字段，这样在 join buffer 中一次就可以加载更多的数据

12.2 子查询优化

• 可以的化将子查询转换为关联查询，因为子查询会生成临时表，这会消耗过多的 cpu 和 io 资源。而且对于临时表是无法使用索引的
• 尽量不要使用NOT IN 或者 NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代

12.3 order by 优化

• 在 order by 字段上增加索引，有where 条件时，对 order by 字段和 where 条件中的字段建立联合索引。因为一次查询中只能使用一个索引，where 上没有索引就会全表扫描，order by 字段上如果没有索引，就会将数据加载到内存中进行 FileSort。当然在一些情况下没有索引未必就比添加索引效率慢，但还是要注意。
• 如果 order by 上没有索引 select * 可能会带来问题。因为进行 FileSort 时会将查询的所有字段的加载到内存中。如果查询了一些无用的字段，会占用 sort buffer 的空间，消耗更多的 IO 资源

12.4 group by 优化

• 与 order by 一致，group by 字段上增加索引
• 使用 order by、group by、distinct 时最好可以使得 where 条件过滤后结果集保持在 1000 以内

12.5 使用覆盖索引

一般都是指联合索引的情况，覆盖索引就是我们 select 的字段是联合索引包含的字段的子集
覆盖索引的好处就是要查询的数据在二级索引的B+Tree中就可以完全找到，不需要进行回表操作，效率更高

12.6 字符串类型添加索引

一般要使用前缀索引，也就是对于字符串的前多少个字符建立索引。比如有一个字段的类型是 char(30)，但是我们发现只需要看前8个字符就有比较高的区分度。那么我们建立索引时就可以针对前8个字符建立索引。
如果使用了前缀索引，就无法使用覆盖索引带来的好处

12.7 普通索引 vs 唯一索引

查询方面： 对于唯一索引来说，找到第一条匹配的记录查询就结束了，但是普通索引还需要继续找下一条，但是实际情况下性能差别并不大
更新方面：

• 对于普通索引来说如果数据页不在内存中的话，会先将操作保存在 change buffer 中，下一次访问到数据页时就把之前保存在 change buffer 中的操作执行掉。除了访问数据页时会触发执行，后台也会定期将这些修改同步到数据页中。
• 对于唯一索引来说，插入一条数据先要去判断是否存在，这就要先将数据页读取到内存中，既然数据页已经被读取到内存中，那么直接更新就好了，所以唯一索引是不会使用到 change buffer 的。
• 所以对于更新操作普通索引效率更高

12.8 exist 和 in

选择的原则：小表驱动大表
两者都相当于是一个嵌套循环，所以外层循环的数据量越小越好

# B表为外层
select * from A where c1 in (select c1 from B)   
# A表为外层
select  * from A where exist(select c1 from B where A.c1 = B.c1)

所以 A 表为小表选择 exist 否则选择 in

12.9 避免使用 select *

使用select * 解析时需要去查询数据字典将 * 号转换为实际的字段，耗费资源和时间
以后表中如果增加一个类型很大的字段比如 blob、text 也会影响到查询的效率

12.10 count(1)、count(*)、count(字段)

• 对于 MyISAM 存储引擎来说是有一个变量统计了行数，查询效率是 O(1)
• count(1)和count(*)的效率是差不多的，MySQL 会自动寻找长度最短的二级索引去扫描全表统计行数。
• count(字段)时最好选择二级索引不要选择聚簇索引，因为聚簇索引的B+Tree叶子节点中保存了完整的一条记录，所以加载更耗费资源和时间

13. 事务

13.1 ACID特性

1. A （atomicity）=> 原子性

由数据库的 undo 日志保障
事务整体是不可分割的，要不全部成功要不全部失败
A给B转账100，A减少100，B增加100，必须都要成功。如果中间出现问题要回到最初的状态

2. C （consistency）=> 一致性

由数据库的 undo 日志保障
事务执行前后就是从一种合法的状态，到另一种合法的状态
A给B转账100，A减少了100，但是B并没有增加100。这就是一种不合法的状态。因为A和B的账户总和相加和转账前不一致

3. I （isolation）=> 隔离性

由数据库的锁机制保障
一个事务的执行不受其他事务执行的影响

4. D（durability） => 持久性

由数据库的 redo 日志保障
事务一旦提交，对于数据库的改变就是永久的

13.2 事务的状态

13.3 事务的并发问题

13.3.1 脏写

事务A修改了被事务B修改之后却没有提交的数据

13.3.2 脏读

事务A读取到了被事务B修改却未提交的数据

13.3.3 不可重复读

事务A第一次读取到了一个数值，该值被事务B修改。之后A再次读取这个值时该值发生了变化

13.3.4 幻读

事务A对表中数据进行了查询，查询之后事务B对表进行了新增操作。之后事务A再次对表进行了查询，发现两次查询出来的记录数是不一致的
幻读是针对插入操作来说的，在mysql中通过 select 的方式去查询并不会发现多了一条记录，但是如果再次插入一条主键id相同的记录此时是会报错的，侧面证明了幻读的存在

13.4 数据库的隔离级别

所有的隔离级别均可解决脏写的问题，隔离等级从上往下依次递增解决的并发问题也越多，但同时性能也越差

13.4.1 读未提交 （READ UNCOMMITTED）

不能解决脏读、不可重复读、幻读

13.4.2 读提交（READ COMMITTED）

解决了脏读
不能解决不可重复读、幻读

13.4.3 可重复读（REPEATABLE READ）

解决了脏读、不可重复读
不能解决幻读

13.4.4 串行化（SERIALIZABLE）

可以解决所有的问题

13.5 MySQL 如何手动解决幻读

14. 数据库日志

14.1 redo 日志

也叫做重做日志
事务提交成功之后，修改还在内存中没有刷盘，此时服务宕机，重启之后就通过 redo 日志把修改的内容重新做一遍
记录的是物理层面的修改，哪个数据页操作了什么。将第0号表空间的10号页面的偏移量为100处的值更新为 2
保障了持久性

• redo log 可以降低刷盘的频率，内存中的修改先记录到日志中，相当于是一层缓冲
• 刷盘操作产生的更多可能是随机IO，redo log 的写入是顺序IO
• redo log 在事务执行的过程中一直在不断写入

有的人可能会由疑问，redo log 也是要刷盘的，那么如果再这个期间 MySQL 宕机了怎么办。但是这个执行的速度是很快的，一般都认为是没有这种情况的

14.2 undo 日志

回滚日志
undo 是回滚到之前的某一状态
记录逻辑层面每一个操作的逆操作，比如插入一条数据，undo 里面记录的是删除一条数据
保障原子性和一致性

15. 锁

并发问题主要出现在 读-写、写-读、写-写 的情况下

15.1 读锁和写锁

读锁 <=> 共享锁（Shared Lock）<=> S 锁
写锁 <=> 排他锁 (Exclusive Lock) <=> X 锁

# 5.7 加读锁
select ... lock in share mode;
# 5.7 加写锁
select ... for update;

# 8.0 加读锁
select ... for share;
# 8.0 加写锁
select ... for update;

15.2 表级锁、行锁、页锁

并发性能 表级锁 < 页锁 < 行锁
开销 表级锁 < 页锁 < 行锁
锁空间是有限的，如果某个层级的锁数量超过了阈值，就会自动进行锁升级，比如行锁升级为页锁

15.2.1 表级锁

15.2.1.1 表级共享锁、表级排他锁

InnoDB 不推荐自己加表级锁，因为由更细粒度的行锁
MyISAM 只有表锁

15.2.1.2 意向锁

意向锁解决的问题是当我们想添加表级锁时，需要先判断表中的数据有没有被添加行级锁。如果数据量很大，依次判断肯定是不现实的。意向锁就是为了解决这个问题，当一个事务对表中的记录添加行级共享锁时，就会自动在表上添加意向共享锁。当一个事务对表中记录添加行级排他锁时，就会自动在表上添加意向排他锁

15.2.1.3 元数据锁 MDL

当对表进行增删改查时会自动加 MDL 读锁，而对表进行修改比如增加字段等 DDL 操作会添加MDL 写锁，通过这个锁保障查询时表结构不会被修改

15.2.2 行锁

15.2.2.1 记录锁

15.2.2.2 间隙锁

对于一个区间加锁或是对于一个确定的位置加锁，比如对于主键 id= 5 的位置加锁，注意这个是不存在 id = 5 的记录的。也就是对不存在的记录加锁，主要针对 insert 操作用于解决幻读问题

间隙锁没有共享锁和排他锁之分，都是一样的效果，因为这个区间就是没有数据的
加锁的方式就是加行锁的方式，只不过是加在了不存在的记录上，被叫做间隙锁

一个表中主键id如下所示

1	2	3	9	10

如果对 id = 5 加锁（select * from table where id = 5 for update），那么 (3 , 9) 都不能插入记录，这就叫做间隙锁。（注意加锁的地方一定要是一个不存在的记录）

特殊情况： 如果希望大于10的都不能插入，可以的 id =10 加锁

注意： 对同一个区间加了间隙锁，可能会导致死锁问题

15.2.2.3 临键锁（Next Key Lock）

行锁和间隙锁的结合，间隙锁锁定的是一个开区间，在间隙锁的基础之上把边界也锁定

一个表中主键id如下所示

1	2	3	9	10

如果我们希望锁定 （3，9 ] 这样一个区间，那么就是 (select * from table where id > 3 and id <=9 for update)，这样的加锁方式就叫做 next key lock

15.2.3 页锁

一个页包含多个数据行，所以页锁的粒度介于行锁和表锁之间

15.3 乐观锁、悲观锁

是一种设计的思想

15.3.1 乐观锁

适合读多的场景
程序实现、不会死锁

• 版本号实现
• CAS 实现

15.3.2 悲观锁

适合写多的场景
通过数据库的锁机制来实现，会死锁
害怕数据被其他事务修改

select ... where ... for update 

注意：where 条件中一定要使用上索引，否则如果是全表扫描，会把扫描到的行全部锁住，如果你要查询的记录在表中的最后，那就相当于锁表了

16. 多版本并发控制 MVCC

解决了并发读写问题，属于乐观锁

16.1 隐藏字段

16.2 Undo Log版本链

16.3 readview

可重复读隔离级别第一次查询操作会生成 Readview，后续的查询操作不会再次生成
读提交隔离级别每一次查询操作都会生成 Readview

读取原则

trx_id 表示要读取的行中隐藏字段的 trx_id

1. 如果 creator_trx_id 和 trx_id 相等则该版本数据可以访问（说明被读取行就是由当前事务修改的）
2. 如果 trx_id 小于 up_limit_id 则该版本数据可以访问（说明对被读取行进行修改的事务，在本次创建ReadView 之前就已经提交了）
3. 如果 trx_id 大于或等于 low_limit_id 则该版本数据不能访问 （说明被读取行在本次创建 ReadView 之后又被修改过）
4. 如果 trx_id 存在于 trx_ids 列表中则该版本数据不能访问（说明对被读取行修改的事务还没有提交）
5. 如果 up_limit_id <= trx_id < low_limit_id，并且不存在于 trx_ids 列表中，则可以访问 （说明对被读取行进行修改的事务，在本次创建ReadView 之前已经提交了）

16.4 为什么可重复读隔离级别下可以解决幻读问题

因为可重复隔离级别下，只会生成一次 readview 。假定生成 readview 时，数据库中活跃的事务id有 5、8。那么 readview 中记录的 trx_ids 中就包括了 5、8。

此时事务 8 插入了两条数据，因为是可重复读级别不重新生成 readview。所以再次查询数据时，发现新增加数据的 trx_id = 8 。而 readview 的 trx_ids 中也包含了 8，根据规则这条数据是不能被访问的，也就解决了幻读的问题

17. bin log

binary log 二进制日志
主要用于数据恢复、数据复制（主从复制）
记录了所有会产生变更的语句包括 DML 和 DDL，但不包括 SELCECT、SHOW

sync_binlog 参数可以控制刷盘的时机，默认是 0 由操作系统决定刷盘的时机，也可以改成1 就是每次提交事务都会刷盘

17.1 bin log 数据恢复

# 使用binlog恢复数据，指定filename时，要先恢复编号小的文件再恢复大的文件
# 执行恢复命令时可以先执行 flush logs 重新生成一个 binlog 文件，否则你恢复数据的过程又被记录到了之前的 binlog 文件中
/**
option可以指定如下的参数
--start-date 和 --stop-date ：可以指定恢复数据库的起始时间点和结束时间点。
--start-position和--stop-position ：可以指定恢复数据的开始位置和结束位置。
*/
mysqlbinlog [option] filename|mysql –uuser -ppass;

17.2 bin log 和 redo log 对比

bin log是逻辑日志记录的是做了什么修改，比如把 id = 1 的记录的 name 改成了张三
redo log 是物理日志记录的是哪个数据页偏移量多少做了什么修改

bin log 主要用于数据复制和恢复，保障了集群之间数据的一致性
redo log 主要用于保证事务的一致性，使 Mysql 具有崩溃恢复的能力

bin log 在事务提交之后才会写入
redo log 在事务执行过程中就不断写入

redo log 写入肯定优先于 redo log

17.3 两阶段提交

由于bin log 和 redo log 的写入时机并不同所以，如果修改的记录写入了 redo log 但是还没来得及写入 bin log 时 MySQL 服务器就宕机了。这样一来，备机或者从节点读取主节点的 bin log 进行数据同步时就会出现问题，因为还有一个事务提交了，但是只在 redo log 中有记录在 bin log 中没有记录。这样一来当主节点恢复运行时从 redo log 中重做了已提交的事务，就会导致主备节点数据的不一致问题

两阶段提交就可以解决这个问题，将 redo log 的写入分为两个阶段，可以理解为给 redo log 加了个事务，有了 begin 和 commit 。这样一来如果 bin log 写入时发生了宕机因为没有 redo log commit ，所以当主节点从 redo log 恢复时就只能读取到 prepare 却没有 commit 就不会恢复这个事务，保障了主备一致性。

18. relay log

存在于从节点中，主节点中没有。
从节点读取主机点的 bin log 日志然后写入自己的 relay log 中，用于数据同步，此过程存在延迟

19. 主从复制

19.1 异步复制

19.2 半同步复制

19.3 组复制

基于 Paxos ，提交时需要半数以上同意

20. 数据库备份恢复

20.1 逻辑备份

20.1.1 备份

mysqldump –u 用户名称 –h 主机名称 –p密码 待备份的数据库名称[tbname, [tbname...]]> 备份文件名 称.sql

20.1.2 恢复

mysql –u root –p [dbname] < backup.sql

20.1.2.1 从全量备份中恢复单库

只有所有数据备份的sql文件，想要恢复某一个数据时

# all_database.sql 表示备份了所有数据库的备份文件名称
# 提取出指定数据库的备份部分
sed -n '/^-- Current Database: `要恢复的数据库名`/,/^-- Current Database: `/p' all_database.sql > 分离出的备份文件名称.sql

20.1.2.2 从单库备份中恢复单表

# database.sql 表示备份了当前数据库的备份文件名称
# 提取出指定表的表结构备份
cat database.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `要恢复的表名`/!d;q' > table_structure.sql 
# 提取出指定表的表数据备份
cat database.sql | grep --ignore-case 'insert into `要恢复的表名`' > table_data.sql

20.2 物理备份

20.2.1 备份

直接复制数据目录

20.2.2 恢复

直接复制数据目录