mysql知识体系总结 第1篇

可以看到， MySQL 的架构共分为两层：Server 层和存储引擎层，

mysql -h {IP地址} -P {端口} -u {用户名}  -p{密码}

Q：空闲连接会一直占用着吗？

A：当然不是了，MySQL 定义了空闲连接的最大空闲时长，由 wait_timeout 参数控制的，默认值是 8 小时（28880秒），如果空闲连接超过了这个时间，连接器就会自动将它断开。

Q：MySQL 的连接数有限制吗？

A：MySQL 服务支持的最大连接数由 max_connections 参数控制，比如我的 MySQL 服务默认是 151 个，超过这个值，系统就会拒绝接下来的连接请求，并报错提示“Too many connections”。

A1：

max_conenctions 限制了当前数据库最大的连接数量，这也就限制了应用的最大并发度。这听上去好像我们应该尽可能地增大 max_connections ，防止因为 max_connections 的数值过小而没有充分利用数据库的性能，降低了整体并发度。但是受限于服务器的资源限制，max_connections 并不能无限制地增长。并且在设置了过大的 max_connections 情况下，数据库会因为保持了大量的连接而使服务器资源耗尽而变得无法响应。

Q2：那 max_connections 设置为多少合适呢?

A2：没有银弹！在考虑 max_connections 的设置数量时，往往要根据业务场景，业务并发连接数以及服务器器负载等综合考量。

        在这里，建议根据实际的应用场景以及服务器条件进行测试来设置 max_connections 个数，同时应用连接池，连接池内的同一个 connection 能够在不同的客户端连接之间复用，从而避免了为每个客户端连接单独维护进程或线程的开销。同时在到达连接池的最大连接数后，连接池也不会拒绝新的客户端连接，新的客户端连接会阻塞等待连接池释放空闲的连接。

同时你也可以参考 mysql 官方建议

In the case of active connections using temporary/memory tables, memory usage can go even higher. On systems with small RAM or with a hard number of connections control on the application side, we can use small max_connections values like 100-300. Systems with 16G RAM or higher max_connections=1000 is a good idea, and per-connection buffer should have good/default values while on some systems we can see up to 8k max connections, but such systems usually became down in case of load spikes.

长连接: 连接建立成功后，如果客户端持续有请求，则一直使用同一个链接

短连接: 每次执行完很少的几次查询，就断开连接，下次查询重新再建立一个连接

建议: 尽量减少连接的建立(因为连接建立过程是比较复杂的)，即尽可能的使用长连接

但是: 全部使用长连接，SQL占用内存会增长的非常快(连接是占用资源的，内存无限增长会导致OOM)

解决方案:

① 定期断开长连接：既然断开连接后就会释放连接占用的内存资源，那么我们可以定期断开长连接

② 如果使用的是或更新版本，每次在执行一个比较大的操作后，通过执行mysql_reset_connection来重新初始化连接资源(该过程不会重连和重新做权限验证，但是会将连接恢复到刚刚创建完成的状态)

短链接：每次请求都做负载均衡

长连接

请求粒度的负载均衡：一个客户端与每个服务端都建立连接，发送请求时按照某种负载均衡策略选择一个服务端进行请求

连接粒度的负载均衡：客户端在建立连接时按照某种负载均衡策略挑选一个节点进行建立连接，后续请求都发往这个节点

策略选择依据：如果选择主要是「考量单个服务端可能的连接数量」，如果连接数远不是瓶颈，可考虑请求粒度，否则考虑连接粒度。举例：

Dubbo一个provider节点和订阅Consumer的所有节点都建立了链接，前提是Dubbo一个Provider基本不可能被几万个节点消费

Nocas注册中心就不能使用请求粒度

连接器得工作完成后，客户端就可以向 MySQL 服务发送 SQL 语句了，MySQL 服务收到 SQL 语句后，就会解析出 SQL 语句的第一个字段，看看是什么类型的语句。

如果 SQL 是查询语句（select 语句），MySQL 就会先去查询缓存（ Query Cache ）里查找缓存数据，看看之前有没有执行过这一条命令，这个查询缓存是以 key-value 形式保存在内存中的，key 为 SQL 查询语句，value 为 SQL 语句查询的结果。

如果查询的语句命中查询缓存，那么就会直接返回 value 给客户端。如果查询的语句没有命中查询缓存中，那么就要往下继续执行，等执行完后，查询的结果就会被存入查询缓存中。

这么看，查询缓存还挺有用，但是其实查询缓存挺鸡肋的：对于更新比较频繁的表，查询缓存的命中率很低的，因为只要一个表有更新操作，那么这个表的查询缓存就会被清空。如果刚缓存了一个查询结果很大的数据，还没被使用的时候，刚好这个表有更新操作，查询缓冲就被清空了，相当于缓存了个寂寞。

所以，MySQL 版本直接将查询缓存删掉了，也就是说 MySQL 开始，执行一条 SQL 查询语句，不会再走到查询缓存这个阶段了。

对于 MySQL 之前的版本，如果想关闭查询缓存，我们可以通过将参数 query_cache_type 设置成 DEMAND。

TIP：这里说的查询缓存是 server 层的，也就是 MySQL 版本移除的是 server 层的查询缓存，并不是 Innodb 存储引擎中的 buffer pool

解析器会做如下两件事情

下面场景分析：

经过解析器后，接着就要进入执行 SQL 查询语句的流程了，每条SELECT 查询语句流程主要可以分为下面这三个阶段：

我们先来说说预处理阶段做了什么事情。

不过，对于 MySQL 判断表或字段是否存在的工作，是在词法分析&语法分析之后，prepare 阶段之前做的。结论都一样，不是在解析器里做的。代码我就不放了，正因为 MySQL 代码结构不好，所以 MySQL 代码结构变化很大，后来判断表或字段是否存在的工作就被放入到 prepare 阶段做了。

经过预处理阶段后，还需要为 SQL 查询语句先制定一个执行计划，这个工作交由「优化器」来完成的。

优化器主要负责将 SQL 查询语句的执行方案确定下来，比如在表里面有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引。

经历完优化器后，就确定了执行方案，接下来 MySQL 就真正开始执行语句了，这个工作是由「执行器」完成的。在执行的过程中，执行器就会和存储引擎交互了，交互是以记录为单位的。

mysql知识体系总结 第2篇

Linux，全称GNU/Linux，是一种免费使用和自由传播的的类UNIX操作系统，其内核由林纳斯·本纳第克特·托瓦兹于1991年10月5日首次发布，它主要受到Minix和Unix思想的启发，是一个基于POSIX的多用户、多任务、支持多线程和多CPU的操作系统。它能运行主要的Unix工具软件、应用程序和网络协议。它支持32位和64位硬件。Linux继承了Unix以网络为核心的设计思想，是一个性能稳定的多用户网络操作系统。Linux有上百种不同的发行版，如基于社区开发的debian、archlinux，和基于商业开发的Red Hat Enterprise Linux、SUSE、Oracle Linux等。

Linux，全称GNU/Linux，是一套免费使用和自由传播的类Unix操作系统，是一个基于POSIX的多用户、多任务、支持多线程和多CPU的操作系统。伴随着互联网的发展，Linux得到了来自全世界软件爱好者、组织、公司的支持。它除了在服务器方面保持着强劲的发展势头以外，在个人电脑、嵌入式系统上都有着长足的进步。使用者不仅可以直观地获取该操作系统的实现机制，而且可以根据自身的需要来修改完善Linux，使其最大化地适应用户的需要。

Linux不仅系统性能稳定，而且是开源软件。其核心防火墙组件性能高效、配置简单，保证了系统的安全。在很多企业网络中，为了追求速度和安全，Linux不仅仅是被网络运维人员当作服务器使用，甚至当作网络防火墙，这是Linux的一大亮点。?

Linux具有开放源码、没有版权、技术社区用户多等特点，开放源码使得用户可以自由裁剪，灵活性高，功能强大，成本低。尤其系统中内嵌网络协议栈，经过适当的配置就可实现路由器的功能。这些特点使得Linux成为开发路由交换设备的理想开发平台。

mysql知识体系总结 第3篇

MyISAM：每个MyISAM在磁盘上存储成三个文件。分别为：表定义文件、数据文件、索引文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。

InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。

mysql知识体系总结 第4篇

: _64/ :

具体的安装步骤，自行百度吧！当你看到这个页面，恭喜你，Linux安装成功了！

?

桥接模式表示虚拟机与主机在同一网段下，也就相当于局域网，如果IP地址为，那么网段就是，也就是说虚拟机ip最多会有255个，这样就有了局限性，容易造成IP冲突。

虚拟机中是独立的网络，通过代理与主机互通，不会造成IP冲突。

只有本机能用的虚拟机，不建议使用。

（1）NAT网络配置

（2）配置网关

（3）设计主机名和hosts映射

修改文件在/etc/hostname指定

mysql知识体系总结 第5篇

(1) 慢查询配置: slow_query_log、slow_query_log_file、long_query_time

(2) 慢查询日志分析工具mysqldumpslow: 捕获前10条查询较慢的 mysqldumpslow -s at -t 5

(3) 查看当前执行语句: show processlist

(1)使用join来代替子查询

(2)拆分大的delete或insert语句

(3)可通过开启慢查询日志来找出较慢的SQL

(4)OR改写成IN: OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内

explain查看执行计划：type、key、extra

① type：访问类型

all：full table scan，全表扫描

index：full index scan，只遍历索引树

range：索引范围扫描。对索引的扫描开始于某一个点，返回匹配值域的行，常见于between、>、<等查询

ref：非唯一索引扫描，返回匹配某个单独值的所有行

eq_ref：唯一性索引扫描，对于每一个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

const,system：当mysql对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量

NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引

② possible_keys：可能使用到的索引

③ key：实际使用的索引，若没有使用到索引，显示为NULL

④ key_len：表示索引中使用的字节数，可以通过该列计算查询中使用的索引的长度

⑤ ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

⑥ rows：估算查询到所需记录需要读取的行数

⑦ extra：包含不适合在其他列中显示，但是十分重要的额外信息

using index：使用到覆盖索引

using where：

表示mysql服务器在存储引擎受到记录后进行“后过滤”

如果查询未能使用索引，using where作用是提醒我们mysql将使用where子句来过滤结果集

using temporary：表示mysql需要使用临时表来存储结果集，常见于order by和group by

using filesort：mysql无法使用索引完成的排序操作称为“文件排序”

. 介绍limit随着数据量大，导致低性能的本质

limit语法支持两个参数，offset/limit，其中: offset=偏移量开始查找，limit=返回limit条元组

本质: limit 10000,10的语法，实际上是mysql查找到前10010条数据，之后丢弃前面的10000行，返回10行数据。(可以看到，查找的前10000行数据是十分消耗资源且没有必要的)

. 用id优化

先找到上次分页的最大id

然后利用id上的索引来查询

类似于: select * from user where id >1000000 limit 100.

这样的效率非常快，因为主键上是有索引的；但是这样有个缺点，就是ID必须是连续的，并且查询不能有where语句，因为where语句会造成过滤数据。

淘宝等商品页，就采用该方式: 连续页面查询(上一页/下一页)

. 用覆盖索引杜绝回表查询 — 子查询

先查出索引id，然后根据id查询数据

自身连接

select form course FIRST, course SECOND where

左连接/右连接

from A left join B on (连接条件) #以A的行为主行, B没有的补NULL

from A right join B on (连接条件) #以B的行为主行, A没有的补NULL

内连接

from A inner join B on (连接条件) #A和B的交集

MySQL 集群的主从复制过程梳理成 3 个阶段：

具体详细过程如下：

Q：MySQL 主从复制还有哪些模型？

A：主要有三种：

延迟问题产生原因: 从服务器的两个线程执行速度不一致，可能会造成延迟问题。

① IO线程从主服务器读取日志速度很快(顺序读)，而SQL线程重放SQL速度慢，这就会造成从服务器同步数据远远落后于主服务器，导致从服务器数据远远落后于主服务器，(主从数据库长期处于不一致的状态)，这种现象就是延迟更新。

② (主库经常会开多个线程去写，从库只有一个线程在工作，导致从库效率 << 主库效率)。

主从延迟时间计算方式

1. 主从延迟时间参考show slave status：输出Second_Behind_Master，精度为秒

2. 主从延迟时间是如何计算的？

该值只是个近似值，不是精确值

Second_Behind_Master = 主库Binlog写入时间 — 从库取出当前正在执行的事务的时间值

主从延迟的危害

1. 查询数据不准确：主库的更改，没有同步到从库，导致“读错”

2. 主库宕机，数据没有同步到从库，从库切换成主库，导致数据丢失

常见主从延迟

1. 主从服务器硬件配置不一致：从库的CPU/内存/磁盘IO等硬件配置低

2. 主库执行大事务

3. 表无主键：在主库对一张无主键or无唯一索引的表delete100次数据时，当从库复制这个操作时，执行100个delete动作，每个delete都会做一次全表扫描才能结束

4. 主库DML操作并发大：update/delete/insert

5. 大表DDL

说明：主从延迟影响的是整个集群，该集群下所有的database都会受到影响，而不是只影响这一个database

延迟问题-解决方案: (MTS) 从服务器的数据重放过程采用多线程

MTS: 要遵循两个规则

① 同一个事务中的MDL，必须分发到同一个worker线程

② MDL同一行的多个事务，必须分发到同一个worker

核心原则: 主库只进行更新写操作，从库进行查询读操作

(1)主库: 增删改更新操作，即: 更新操作，一直在主服务器

(2)从库: 查询操作，即: 查询操作，一直在从服务器

为什么要进行分库分表？==> 数据量太大，负荷太高；提高性能，读写分离，冷热分离，系统解耦

读写分离

分区: 指定分区表达式，把记录拆分到不同的区域中(必须是同一个服务器，可以是不同硬盘)，应用看来还是同一张表，没有变化

分库: 业务垂直切分；冷热数据

分表: 单表数据太大(属性列过多；行数过多)

拆分方式: （水平/垂直）分库/分表

垂直: 现在微服务基本已经实现了垂直分库，例如: 订单库、会员库、商品库

水平: 单库数据量太大（超过2kw），例如: 会员库过大，将其按照时间维度进行冷热拆分，一般最近3个月的是热数据，放在热数据库

一般按照冷热数据分库

垂直: 一个表的属性列过多，拆成多个表

水平: 单表数据量过大水平拆分成多个表

涉及区域等可枚举字段查询的可进行分区

涉及时间的可按照月年的时间分表

实现方式：无论是client模式，还是proxy模式，核心实现步骤都一样: sql解析，重写，路由，执行，结果合并

client模式: 处于业务层和JDBC层中间，是以Jar包方式提供给应用调用，对代码有侵入性

client模式代表作有阿里的TDDL: 2012年关闭了维护通道，不建议使用

开源社区的sharding-jdbc: 仍在活跃使用

Golang的client，封装访问主/从的函数

proxy模式: 部署一台代理服务器伪装成Mysql服务器，代理服务器负责与真实Mysql服务器节点连接，应用层程序只和代理服务器对接，对应用层程序是透明的

阿里的cobar

民间组织的mycat

引发的问题 / 缺点

分布式事务: 本地事务 ==> 分布式事务(引出新知识点: 分布式事务解决方案)

跨库join查询 ==> 对于多库join性能太低，不建议使用sql自带的join，解决方案为

全局表: 比如系统中所有模块都可能会依赖到的一些基础表（即全局表），在每个数据库中均保存一份。

字段冗余: 把需要关联的字段放入主表中，避免关联操作；比如订单表保存了卖家ID（ sellerId），你把卖家名字 sellerName也保存到订单表，这就不用去关联卖家表了。这是一种空间换时间的思想。

数据抽象同步：比如A库中的a表和B库中的b表有关联，可以定时将指定的表做同步，将数据汇合聚集，生成新的表。一般可以借助 ETL工具。

代码组装: 分开多次查询，调用不同模块服务，获取到数据后，代码层进行字段计算拼装

分布式全局唯一ID: 雪花算法

order by，group by等聚合函数问题

跨节点的count,order by,group by以及聚合函数等问题，都是一类的问题，它们一般都需要基于全部数据集合进行计算。可以分别在各个节点上得到结果后，再在应用程序端进行合并

分库分表后的分页问题

全局视野法：在各个数据库节点查到对应结果后，在代码端汇聚再分页。

优点：业务无损，精准返回所需数据

缺点：则是会 返回过多数据，增大网络传输

业务折衷法-禁止跳页查询：这种方案需要业务妥协一下，只有上一页和下一页，不允许跳页查询了

这种方案，查询第一页时，是跟全局视野法一样的。但是下一页时，需要把当前最大的创建时间传过来，然后每个节点，都查询大于创建时间的一页数据，接着汇总，内存排序返回。

停服迁移

双写迁移(修改配置)

你分库分表的姿势对么？——详谈水平分库分表

什么是一个好的分库分表方案？

1. 方案可持续性

何为可持续性？其实就是：业务数据量级和业务流量未来进一步升高达到新的量级的时候，我们的分库分表方案可以持续使用。

        一个通俗的案例，假定当前我们分库分表的方案为10库100表，那么未来某个时间点，若10个库仍然无法应对用户的流量压力，或者10个库的磁盘使用即将达到物理上限时，我们的方案能够进行平滑扩容

2. 数据倾斜问题

        一个良好的分库分表方案，它的数据应该是需要比较均匀的分散在各个库表中的。如果我们进行一个拍脑袋式的分库分表设计，很容易会遇到以下类似问题：

        一般定义分库分表最大数据偏斜率为 ：（数据量最大样本 - 数据量最小样本）/ 数据量最小样本。一般来说，如果我们的最大数据偏斜率在5%以内是可以接受的

3. 常见分库分表方案

. Range

实现原理：根据数据范围划分数据的存放位置

举例：将订单表按照年限为单位，每年的数据存放在单独的库/表

数据热点问题：例如上面案例中的订单表，很明显当前年度所在的库表（相比于往年）属于热点数据，需要承载大部分的IO和计算资源

新库和新表的追加问题：一般线上运行的应用程序，没有访问新库新表的权限，因此需要提前将新库新表创建好，防止线上事故（这一点非常容易被遗忘）

业务上的交叉范围内数据的处理。举例，订单模块无法避免一些中间状态的数据补偿逻辑，即需要定时任务到订单表中扫描那些T+1处于待支付确认等状态的订单（这里就需要注意了，因为通过年份进行分库分表，那么元旦的那一天，你的定时任务很有可能会漏掉上一年最后一天的数据扫描）

. HASH分库分表（最普遍最大众的解决方案）

. 常见错误案例

1. 库数量和表数量是非互质的，会导致的数据偏斜

2. 扩容难以持续

        该方案有个比较大的问题，那就是在计算表序号的时候，依赖了总库的数量，那么后续翻倍扩容法进行扩容时，会出现扩容前后数据不在同一个表中，从而无法实施。

. 常用姿势

面试题：今天我被问了个 分库分表下面怎么查询带非分片键的数据，比如，uid是分片键，但是要用name来查询。

例如：基于订单数据的分库分表场景，按照 order_id 取模虽然很好地满足了订单数据均匀地保存在数据库中，但在 买家 查看自己订单的业务场景中，就出现了全表扫描的情况，而且买家查看自己订单的请求是非常频繁的，必然给数据库带来扩展和性能的问题，有违“尽量减少事务边界”这一原则。

解决方案：异构索引表（空间换时间）

        针对这类场景问题，最常用的是采用“异构索引表”的方式解决，即采用异步机制将原表的每一次创建或更新，都换另一个维度保存一份完整的数据表或索引表，拿空间换时间。

        也就是应用在插入或更新一条订单ID为分库分表键的订单数据时，也会再保存一份按照买家ID为分库分表键的订单索引数据，其结果就是同一买家的所有订单索引表都保存在同一数据库中，这就是给订单创建了异构索引表。

流程详解：

异构索引表的数据怎么插入呢？

防止每次请求都发到数据库上，使用缓存，降低连接数据库操作、数据库处理操作次数，提高数据库性能

mysql知识体系总结 第6篇

TIMESTAMP在_ CURRENT_TIMESTAMP数据类型上做什么

答：只要表中的其他字段发生更改，_ CURRENT_TIMESTAMP修饰符就将时间戳字段更新为当前时间

列设置为auto increment时，如果在表中达到最大值，会发生什么？

它会停止递增，任何插入操作都会返回错误

记录货币使用什么字段好？DECIMAL(precision,scale)

precision：代表将被用于存储值的总的位数

scale：代表将被用于存储小数点后的位数

mysql知识体系总结 第7篇

（1）、简介

crondtab进行定时任务的设置

基本语法：crontab [选项]

常用选项：

crond相关指令：

（2）、举例说明

*/1 * * * * ls -l /etc/ > /tmp/

定时每分钟执行，将etc的ls内容重定向到tmp下文件中。

一周中的星期几?

特殊符号：

代表不连续的时间，比如0 8,12,16 * * *? 代表每天的8点0分，12点0分，16点0分都执行一次

（3）应用实例

*/1 * * * * date >> /tmp/

步骤：

vim /home/ 写入内容 date >> /home/mydate 和 cal >> /home/mydate 给增加执行权限，chmod u+x /home/ crontab -e 增加?*/1 * * * *??/home/

指令：mysqldump -u root -p密码 数据库 > /home/

crontab -e 0 2?* * *??mysqldump -u root -proot testdb > /home/

（4）at定时任务

at [选项][时间]

Ctrl + D 结束at命令的输入

（1）进程号

在Linux中，每个执行的程序都称为一个进程，每一个进程都会分配一个ID号（pid，进程号）。

（2）ps指令

显示系统执行的进程

属性：

-a：显示当前终端的所有进程信息

-u：以用户的格式显示进程信息

-x：显示后台进程运行的参数

分页显示：ps -aux | more

过滤显示：ps -aux | grep sshd

（3）ps显示信息详解

USER：用户名称 PID：进程号 %CPU：进程占用CPU的百分比 VSZ：进程占用的虚拟内存大小（单位：KB） RSS：进程占用的物理内存大小（单位：KB） TT：终端名称，缩写 STAT：进程状态（S-睡眠，s-表示该进程是会话的先导进程，N-表示进程拥有比普通优先级更低的优先级，R-正在运行，D-短期等待，Z-僵死进程，T-被跟踪或者被停止等等） STARTED：进程的启动时间 TIME：CPU时间，即进程使用CPU的总时间 COMMAND：启动进程所用的命令和参数，如果过长会被截断显示

（4）终止进程

kill和killall

基本语法：

kill [选项] 进程号（功能描述：通过进程号杀死进程）

killall 进程名称 （功能描述：通过进程名称杀死进程，也支持通配符，这在系统因负载过大而变得很慢时很有用）

常用选项：-9 表示强制停止进程。

（5）查看进程树

pstree [选项]，可以更加直观的查看进程信息

常用选项：

-p：显示进程的pid

-u：显示进程的所属用户

（1）简介

服务本质就是进程，但是是运行在后台的，通常都会监听某个端口，等待其它程序的请求，比如mysql、sshd、防火墙等，因此我们又称之为守护进程，是Linux中非常重要的知识点。

（2）service管理指令

service 服务名[start | stop | restart | reload | status] 在后，很多服务不再使用service，而是使用systemctl service指令管理的服务在/etc/查看

（3）chkconfig指令

通过chkconfig可以给服务的各个运行级别设置自启动/关闭。

基本语法：

chkconfig --list [| grep xxx] chkconfig 服务名 --list chkconfig --level 5 服务名 on/off

（4）systemctl指令

基本语法：

systemctl [start | stop | restart | reload | status] 服务名

systemctl指令管理的服务在/us/lib/systemd/system查看

systemctl设置服务的自启动状态

systemctl list-unit-files [|grep 服务名]（查看服务开机启动状态，grep可以进行过滤） systemctl enable 服务名（设置服务开机启动） systemctl disable 服务名（关闭服务开机启动） systemctl is-enabled 服务名（查询某个服务示范是自启动的）

应用案例：

查看当前防火墙的状况，关闭防火墙和重启防火墙。

systemctl status firewalld;

systemctl stop firewalld;

systemctl start firewalld;

（5）firewall指令

打开端口：firewall-cmd --permanent --add-port=端口号/协议 关闭端口：firewall-cmd --permanent --remove-port=端口号/协议 重新载入，才能生效：firewall-cmd --reload 查询端口是否开放：firewall-cmd --query-port=端口号/协议

mysql知识体系总结 第8篇

定义: 多个SQL语句的集合，就像是函数，但是它没返回值

优点: 一次连接，执行存储过程中所有的SQL语句，效率高

只在创建时编译一次，之后不编译；可以重复使用，提高开发效率

安全性高: 可以设定某个用户是否具有某个存储过程的使用权限

触发器: 需要有触发条件，当条件满足以后做什么操作

例如1: 校内网，开心网，facebook，你发一个日志，自动通知好友，其实就是增加日志时做的一个后触发，再向_通知表_写入条目。==> 触发器的效率高

mysql知识体系总结 第9篇

Q：redo log 和 undo log 区别在哪？

这两种日志是属于 InnoDB 存储引擎的日志，它们的区别在于：

应用场景

之前已经说过，innodb支持事务，它具有事务日志redo/undo，而myisam不支持事务，它没有这两种事务日志。

在事务提交之前，会先写入redo/undo日志

1. 是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复

2. 保证: 所有已经提交的事务的数据仍然存在

redo_log作用: 用于记录事务的变化，记录的是数据修改之后的值，不管事务是否提交都会记录。如果某时刻系统宕机，重启后，可以通过redo log恢复之前的数据

数据库宕机恢复过程: 先从redo log中把未落盘的脏页数据恢复回来，重新写入磁盘，保证用户数据不丢失

redo_log写入磁盘过程: 先写入redo log buffer(redo log缓存)，之后调用fsync，刷新写入redo log物理磁盘(它的写入是可进行参数配置的)

1. 是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC

2. 保证: 所有没有提交的事务的数据自动回滚

数据更新时，会先写入undo log(该操作和数据更新执行操作相反，即如果是插入数据，则undo log是删除数据)

回滚: 当事务失败或回滚时，根据undo log，把未提交的事务回滚到更新前的状态

1. 是 Server 层生成的日志，主要用于数据备份和主从复制；

2. 不管SQL使用那种存储引擎，都有Binlog，它是Server层的(而redo log是innodb层的)

SQL会为每一个事务，分配一个事务ID(XID)

commit被分为2个阶段: papare/commit

Binlog会被当作事务协调者

step1: 准备阶段(papare)

1. 此时SQL已经成功执行，生成XID信息以及Redo/Undo的内存日志

2. 然后调用papare方法，将事务状态设置为TRX_PREPARED，并将Redo/Undo log刷入磁盘

step2: 提交阶段(commit)

(1)提交 or 回滚

如果事务涉及的所有存储引擎的papare都执行成功，则将SQL语句写入Binlog，调用fsync写入磁盘

如果事务涉及的所有存储引擎的papare都执行失败，则SQL语句不会写入Binlog，此时事务回滚

(2)告诉引擎进行commit

(假设papare成功，完成事务提交)会清除undo信息，调用fsync刷redo日志到磁盘，将事务设置为TRX_NOT_STARTED状态

不是的。redo log 也有自己的缓存—— redo log buffer（默认大小 16 MB）

InnoDB在缓冲池中变更数据时，会产生一条redo log

缓存在 redo log buffer 里的 redo log 还是在内存中，它什么时候刷新到磁盘？

主要有下面几个时机：

        操作系统的文件系统是带有缓存的，当InnoDB向磁盘写入数据时，有可能只是写入到了文件系统的缓存中，没有真正的“落袋为安”。InnoDB的innodb_flush_log_at_trx_commit属性，可以控制每次事务提交时InnoDB的行为（根据innodb_flush_log_at_trx_commit选项的值，决定不同的策略）

这两个日志有四个区别。

1.适用对象不同：

2.文件格式不同：

3.写入方式不同：

4.用途不同：

如果不小心整个数据库的数据被删除了，能使用 redo log 文件恢复数据吗？

答：不可以使用 redo log 文件恢复，只能使用 binlog 文件恢复。

因为 redo log 文件是循环写，是会边写边擦除日志的，只记录未被刷入磁盘的数据的物理日志，已经刷入磁盘的数据都会从 redo log 文件里擦除。

binlog 文件保存的是全量的日志，也就是保存了所有数据变更的情况，理论上只要记录在 binlog 上的数据，都可以恢复，所以如果不小心整个数据库的数据被删除了，得用 binlog 文件恢复数据

显示启动事务: begin/start transaction，commit/roolback

set autocommit=0 该命令会将这个线程的自动提交关闭掉，即:

只要你执行一个select语句，事务就启动了，而且并不会自动提交(直到你主动执行commit\rollback语句，或者断开连接)

建议: 总是使用set autocommit=1

为什么尽量避免长事务？

长事务意味着系统里面会存在很老的事务视图，在这个事务提交之前，回滚记录都要保留，这会导致占用大量的存储空间

长事务还占用锁资源，可能会拖垮库

长事务，commit后才会写入binlog，会造成主从延时问题

默认情况下， InnoDB 存储引擎有 1 个重做日志文件组( redo log Group），「重做日志文件组」由有 2 个 redo log 文件组成，这两个 redo 日志的文件名叫 ：ib_logfile0 和 ib_logfile1 

重做日志文件组是以循环写的方式工作的，从头开始写，写到末尾就又回到开头，相当于一个环形。

在重做日志组中，每个 redo log File 的大小是固定且一致的，假设每个 redo log File 设置的上限是 1 GB，那么总共就可以记录 2GB 的操作。

所以 InnoDB 存储引擎会先写 ib_logfile0 文件，当 ib_logfile0 文件被写满的时候，会切换至 ib_logfile1 文件，当 ib_logfile1 文件也被写满时，会切换回 ib_logfile0 文件。

我们知道 redo log 是为了防止 Buffer Pool 中的脏页丢失而设计的，那么如果随着系统运行，Buffer Pool 的脏页刷新到了磁盘中，那么 redo log 对应的记录也就没用了，这时候我们擦除这些旧记录，以腾出空间记录新的更新操作。

redo log 是循环写的方式，相当于一个环形，InnoDB 用 write pos 表示 redo log 当前记录写到的位置，用 checkpoint 表示当前要擦除的位置（checkpoint可以理解为环形队列中的read pos，向前移动，即read数据，写入磁盘），如下图：

图中的：

        如果 write pos 追上了 checkpoint，就意味着 redo log 文件满了，这时 MySQL 不能再执行新的更新操作，也就是说 MySQL 会被阻塞（因此所以针对并发量大的系统，适当设置 redo log 的文件大小非常重要），此时①会停下来将 Buffer Pool 中的脏页刷新到磁盘中，②然后标记 redo log 哪些记录可以被擦除，③接着对旧的 redo log 记录进行擦除，等擦除完旧记录腾出了空间，④checkpoint 就会往后移动（图中顺时针），然后 MySQL 恢复正常运行，继续执行新的更新操作。

        所以，一次 checkpoint 的过程就是脏页刷新到磁盘中变成干净页，然后标记 redo log 哪些记录可以被覆盖的过程。

mysql知识体系总结 第10篇

具体更新一条记录 _ t_user SET name = 'xiaolin' WHERE id = 1; 的流程如下:

两阶段提交把单个事务的提交拆分成了 2 个阶段，分别是「准备（Prepare）阶段」和「提交（Commit）阶段」，每个阶段都由协调者（Coordinator）和参与者（Participant）共同完成。

注意：不要把提交（Commit）阶段和 commit 语句混淆了，commit 语句执行的时候，会包含提交（Commit）阶段

事务的提交过程有两个阶段，就是将 redo log 的写入拆成了两个步骤：prepare 和 commit，中间再穿插写入binlog，具体如下：

prepare 阶段：将 XID（内部 XA 事务的 ID） 写入到 redo log，同时将 redo log 对应的事务状态设置为 prepare，然后 将 redo log 持久化到磁盘（innodb_flush_log_at_trx_commit = 1 的作用）；

commit 阶段：把 XID 写入到 binlog，然后 将 binlog 持久化到磁盘（sync_binlog = 1 的作用），接着调用引擎的提交事务接口，将 redo log 状态设置为 commit （此时该状态并不需要持久化到磁盘，只需要 write 到文件系统的 page cache 中就够了，因为只要 binlog 写磁盘成功，就算 redo log 的状态还是 prepare 也没有关系，一样会被认为事务已经执行成功）

什么是双1？

双1的缺陷：两阶段提交虽然保证了两个日志文件的数据一致性，但是性能很差，主要有2个方面的影响

MySQL 引入了 binlog 组提交（group commit）机制，当有多个事务提交的时候，会将多个 binlog 刷盘操作合并成一个，从而减少磁盘 I/O 的次数，如果说 10 个事务依次排队刷盘的时间成本是 10，那么将这 10 个事务一次性一起刷盘的时间成本则近似于 1。

引入了组提交机制后，prepare 阶段不变，只针对 commit 阶段，将 commit 阶段（参考章节）拆分为三个过程：

        上面的每个阶段都有一个队列，每个阶段有锁进行保护，因此保证了事务写入的顺序，第一个进入队列的事务会成为 leader，leader领导所在队列的所有事务，全权负责整队的操作，完成后通知队内其他事务操作结束。

        对每个阶段引入了队列后，锁就只针对每个队列进行保护，不再锁住提交事务的整个过程，可以看的出来，锁粒度减小了，这样就使得多个阶段可以并发执行，从而提升效率。

接下来介绍每个阶段的过程：

①flush 阶段：多个事务按进入的顺序将 binlog 从 cache 写入文件（不刷盘）

第一个事务会成为 flush 阶段的 Leader，此时后面到来的事务都是 Follower： 

接着，获取队列中的事务组，由绿色事务组的 Leader 对 redo log 做一次 write + fsync，即一次将同组事务的 redolog 刷盘：

完成了 prepare 阶段后，将绿色这一组事务执行过程中产生的 binlog 写入 binlog 文件（调用 write，不会调用 fsync，所以不会刷盘，binlog 缓存在操作系统的文件系统中）。

从上面这个过程，可以知道 flush 阶段队列的作用是用于支撑 redo log 的组提交。

如果在这一步完成后数据库崩溃，由于 binlog 中没有该组事务的记录，所以 MySQL 会在重启后回滚该组事务。

②sync 阶段：

绿色这一组事务的 binlog 写入到 binlog 文件后，并不会马上执行刷盘的操作，而是会等待一段时间，这个等待的时长由 Binlog_group_commit_sync_delay 参数控制，目的是为了组合更多事务的 binlog，然后再一起刷盘。

不过，在等待的过程中，如果事务的数量提前达到了 Binlog_group_commit_sync_no_delay_count 参数设置的值，就不用继续等待了，就马上将 binlog 刷盘

组提交参数设置

如果在这一步完成后数据库崩溃，由于 binlog 中已经有了事务记录，MySQL会在重启后通过 redo log 刷盘的数据继续进行事务的提交。

③commit 阶段

最后进入 commit 阶段，调用引擎的提交事务接口，将 redo log 状态设置为 commit。

commit 阶段队列的作用是承接 sync 阶段的事务，完成最后的引擎提交，使得 sync 可以尽早的处理下一组事务，最大化组提交的效率。

mysql知识体系总结 第11篇

查看：ls -ahl

修改文件所有者：chown 用户名 文件名

创建组：groupadd 组名

创建一个用户tom，并将其放入moster组中

useradd -g monster tom

ls -l中显示的内容如下：

-rwxrw-r-- 1 root root 1213 Feb 2 09:39 abc

可被执行

0-9位说明

（1）第0位确定文件类型（d，-，l，c，b）

（2）第1-3位确定所有者的权限

（3）第4-6位表示所在组对该文件的权限

（4）第7-9位表示其他用户对该文件的权限

通过chmode指令，可以修改文件或目录的权限

（1）+，-，= 变更权限

u：所有者

q：所在组

o：其它人

a：?所有人

chmod?u=rwx,g=rx,o=x 文件/目录名

chmod?o+w 文件/目录名

chmod a-x?文件/目录名

（2）通过数字变更权限

r=4 w=2 x=1??rwx = 4+2+1=7

chmod?u=rwx,g=rx,o=x 文件/目录名 相当于 chmod 751文件名

基本介绍

chown newowner 文件/目录名 改变所有者

chown newowner:newgroup?文件/目录名 改变所有者和所在组

-R 如果是目录 则使其下所有子文件或目录递归生效

例如：

（1）请将/home/文件的所有者修改为tom

chown tom /home/

（2）请将/home下所有文件的所有者修改为tom

chown -R tom /home

-chgrp newgroup?文件/目录??改变所在组

警察和土匪游戏

police，bandit

Jack，Jerry：警察

zs，ls：土匪

（1）创建组

groupadd police；groupadd bandit

（2）创建用户

useradd -g police jack;

useradd -g police jerry;

useradd -g bandit zs;

useradd -g bandit ls;

（3）jack创建一个文件，自己可以读写，本组人可以读，其它组没任何权限

vim ;

chmod?u=rwx,g=rx,o=x

（4）jack修改该文件，让其他组人可以读，本组人可以读写

chmod o=r,g=r

（5）zs投靠警察，看看是否可以读写

usermod -g police zs

（6）测试，看看zs是否可以读写，ls是否可以

mysql知识体系总结 第12篇

dump线上数据

mysqldump -h主机 -P端口 -u用户 -p密码 --databases 数据库 --tables 表名 --single-transaction > /tmp/

mysql -h主机 -P端口 -u用户 -p密码 --database 数据库 < /tmp/

连接数据库

mysql -h 主机 -P 端口 -u 用户 -p密码; use 数据库

查询Mysql里面目前正在执行的语句