暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / MySQL数据库到底安不安全？

MySQL数据库到底安不安全？

数据库知多少 2021-04-26
3339
MySQL数据库到底安不安全？

    随着MySQL数据库的不断发展，用户的使用量也在不断增加，到目前已经是排名第二的关系型数据库系统；为什么越来越多的人都会把MySQL数据库作为存储数据的首要选择？ 使用MySQL数据库到底安全不安全？ 今天我们就从下面6个方面来分析下。

1. 数据安全之 存储引擎

    我们知道，MySQL中数据的存储是基于存储引擎的，存储引擎是MySQL数据库管理系统的一个重要特征，存储引擎决定了表的类型和表的存储方式。我们可以根据实际情况选择不同类型的存储引擎。存储引擎指定了表的类型，即如何存储数据和索引数据及是否支持事务等。
    我们来看下MySQL的默认存储引擎的发展历史：
    在MySQL 5.1之前的版本中，默认的搜索引擎是MyISAM，从MySQL 5.5之后的版本中，默认的搜索引擎变更为InnoDB。为什么会做调整呢？

我们先来看下这两个引擎的区别：
    InnoDB引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别。该引擎还提供了行级锁和外键约束，它的设计目标是处理大容量数据，MySQL运行时InnoDB会在内存中建立缓冲池，用于缓冲数据和索引。如果需要使用数据库事务时，该引擎当然是首选，由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用InnoDB引擎会极大的提升效率。
    MyISAM没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率会低一些。另一方面，MyISAM引擎的表在数据库crash时或其它场景下，经常会出现表损坏的情况，需要利用专门的工具进行修复。

    其实，从MySQL数据库发展的初期，就一直是支持InnoDB引擎的，之所以在5.5版本后将默认的存储引擎由MyISAM引擎替换为InnoDB引擎，并且在8.0版本中对InnoDB引擎做了大量的改进，很大的一个原因是由于InnoDB引擎支持事务，对并发的控制更高效，并且可以很大程度上保证数据的一致性。同时，也可以看出来，MySQL也希望通过InnoDB引擎来进军金融领域，来和其它商业数据库一争高下。

2. 数据安全性之 数据库事务

    既然InnoDB引擎是支持事务的，我们来看下什么是事务。
    在数据库中执行SQL语句的时候，特别是对于某些金融业务来说，一系列操作必须全部执行或全部不执行，而不能仅执行其中一部分。
比如，我们通常的银行转账操作：
从id=1 的银行账户上转账100元给id=2的账户：
    --第一步：将id=1的A账户余额减去100
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    -- 第二步：将id=2的B账户余额加上100
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
    复制
    我们知道，这两条SQL语句必须全部执行完成才能完成转账业务；或者由于某些原因，第一条语句成功，第二条语句失败，那么第一条语句就必须撤销所做的修改。
        我们把这种多条语句作为一个整体进行操作的功能，称为数据库的事务。
        数据库事务可以确保该事务范围内的所有操作要么全部成功或者全部失败。如果事务失败了，其效果对数据库来说就相当于没有执行这些SQL，不会对数据库数据有任何改动。

    可见，数据库事务具有ACID这4个特性：
    A：原子性：从 begin 开始一个事务，直到 commit 或者 rollback，中间执行的DML语句都属于一个整体，要么都commit，要么都rollback。 
    C：一致性：当开始一个事务，如果还没提交，数据库crash了，这时候恢复的时候就需要把事务回滚。同理，如果已经提交了，但是内存中的数据没写到磁盘，那么恢复的时候就需要前滚。
    I：隔离性：会话1开始了一个事务，会话2开始的事务对于会话1的事务没有影响。同样的，会话1的事务对于会话2的事务没有影响，简单来说，多个事务间互不影响。 
    D：持久性：如果一个事务提交了，它对数据库中的影响是永久的，即便系统遇到故障的情况下，数据也不会丢失。
    事务隔离性由锁实现，原子性、一致性和持久性由数据库的REDO LOG和UNDO LOG。
    REDO LOG称为重做日志，用来保证事务的原子性和持久性，恢复提交事务修改的页操作。
    UNDO LOG来保证事务的一致性，undo回滚行记录到某个特性版本及MVCC功能。

    先来看看隔离性：
    提前复习下这几个概念：
    1.脏读：事务A对数据作修改且未执行提交（commit），此时事务B在读取相同的记录则读取到了事务A没有提交的数据。 
    2.不可重复读：事务A中对同一行数据进行多次读取，在读取期间事务B对该行数据进行了修改，那么事务A会读取到提交后的数据，导致出现不一致的情况。 
    3.幻读：幻读指的是在同一事务下，当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。 
    4.丢失更新：丢失更新体现逻辑上，事务A的更新操作会被事务B覆盖。
    在多事务并发的系统中，是存在如上的几个问题的，为了解决这几类问题，数据库系统里就有了四种隔离级别：
    • Read Uncommitted（读取未提交内容）
      所有事务都可以看到其他未提交事务的执行结果。读取未提交的数据，也被称之为脏读（Dirty Read）。
    • Read Committed（读取提交内容）
        满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他会话在该会话处理期间可能会有新的commit，所以同一select可能返回不同结果。
    • Repeatable Read（可重读）
        确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。
    • Serializable（可串行化）
        这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决了幻读问题。
    ANSI/ISO SQL标准定义了事务隔离级别
    在MySQL中，默认的隔离级别是Repeatable Read(RR)。
     
    而对于事务的隔离性，是通过具体的锁来实现的，什么是锁呢？
        锁是计算机协调多个进程或线程并发访问某一资源的机制。锁保证数据并发访问的一致性、有效性；锁冲突也是影响数据库并发访问性能的一个重要因素。

    InnoDB锁模式：
    InnoDB 的标准实现的锁只有 2 类，一种是记录级锁，一种是意向锁。
    InnoDB 实现了以下两种类型的记录锁：
    • 共享锁（读锁 S Lock）：允许一个事务去读一行记录，阻止其它事务获得相同数据集的排他锁。
    • 排他锁（写锁 X Lock）：允许获得排它锁的事务更新数据，阻止其它事务取得相同数据集的共享读锁和排他写锁。

    为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：
    • 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
    • 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。
    锁模式的兼容情况：
    首先解释一下意向锁，以下为意向锁的意图解释：
    The main purpose of IX and IS locks is to show that someone is locking a row, or going to lock a row in the table.
        加意向锁为了表明某个事务正在锁定一行或者将要锁定一行数据。
    　首先申请意向锁的动作是InnoDB完成的，怎么理解意向锁呢？例如：事务A要对一行记录r进行上X锁，那么InnoDB会先申请表的IX锁，再锁定记录r的X锁。在事务A完成之前，事务B想要来个全表操作，此时直接在表级别的IX就告诉事务B需要等待而不需要在表上判断每一行是否有锁。意向排它锁存在的价值在于节约InnoDB对于锁的定位和处理性能。
    锁的算法：
    InnoDB 有 3 种记录锁的算法：
    • Record Lock：单个行记录上的锁。
    • Gap Lock：间隙锁，锁定一个范围，而非记录本身。
    • Next-Key Lock：结合 Gap Lock 和 Record Lock，锁定一个范围，并且锁定记录本身。主要解决的问题是 RR 隔离级别下的幻读。

    3. 数据库安全之 REDO LOG 、UNDO LOG 和 BINLOG

    3.1 REDO

        重做日志由重做日志缓冲(redo LOG BUFFER)和重做日志文件(redo log file)组成，前者是易失的，后者是持久的。InnoDB中的REDO LOG FILE文件，是通过循环覆写的方式使用的。即如果REDO LOG由四个文件构成，则第一个文件、第二个文件、第三个文件都写满后，第四个文件最后写满后，会重新使用第一个文件进行写入。

        InnoDB通过Force Log at Commit机制来实现持久性，当commit时，必须先将事务的所有日志写到重做日志文件进行持久化，待commit操作完成才算完成。当事务提交时，日志不写入重做日志文件，而是等待一个事件周期后再执行fsync操作，由于并非强制在事务提交时进行一次fsync操作，显然这可以提高数据库性能。
    　　请记住3点：
    • 重做日志是在InnoDB层产生的
    • 重做日志是物理格式日志，记录的是对每个页的修改
    • 重做日志在事务进行中不断被写入

    如何保证REDO LOG的及时写入磁盘？
        在InnoDB中，参数InnoDB_flush_log_at_trx_commit可以控制InnoDB的事务提交方式：InnoDB写日志有2块缓存。一块是自己定义在内存的LOG BUFFER, 另一个是磁盘映射到内存的os cache。mysql可以调用 flush主动将LOG BUFFER 内容刷新到磁盘缓存，也可以调用 fsync 强制操作同步磁盘缓存到磁盘。还可以同时调用 flush + fsync, 将LOG BUFFER直接写入磁盘。
        如果InnoDB_flush_log_at_trx_commit设置为0，LOG BUFFER将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下，在事务提交的时候，不会主动触发写入磁盘的操作，而是MySQL数据库每秒调用 flush + fsync ，定时器自己维护。
        如果InnoDB_flush_log_at_trx_commit设置为1，每次事务提交时MySQL都会把LOG BUFFER的数据写入log file，并且flush(刷到磁盘)中去。即实时调用 flush + fsync，没法批处理，性能较低。
        如果InnoDB_flush_log_at_trx_commit设置为2，每次事务提交时MySQL都会把LOG BUFFER的数据写入log file，但flush(刷到磁盘)操作并不会同时进行。每秒执行一次 flush(刷到磁盘)操作，定时 fsync 交给OS维护定时器。
    注意：
      由于进程调度策略问题,这个“每秒执行一次 flush(刷到磁盘)操作”并不是保证100%的“每秒”。

    InnoDB_flush_log_at_trx_commit在不同取值时的示意图：

    3.2 BINLOG
        MySQL 的二进制日志 binlog 可以说是 MySQL 最重要的日志，它记录了所有的 DDL 和 DML 语句（除了数据查询语句select、show等），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。
        对支持事务的引擎如InnoDB而言，必须要提交了事务才会记录binlog。
        binlog什么时候刷新到磁盘呢？ 
        由参数 sync_binlog来控制： 

    • sync_binlog 的默认值是0，像操作系统刷其他文件的机制一样，MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。


    • 当sync_binlog =N (N>0) ，MySQL 在每写 N次 二进制日志binary log时，会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。设为1是最安全的，在系统故障时最多丢失一个事务的更新，但是会对性能有所影响。


    3.3 UNDO

        事务回滚和MVCC，是通过UNDO功能来实现的。
        UNDO LOG的原理很简单，为了满足事务的原子性，在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为UNDO LOG）。然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用UNDO LOG中的备份将数据恢复到事务开始之前的状态。
        UNDO是逻辑日志，只是将数据库逻辑的恢复到原来的样子，但是数据结构和页本身在回滚之后可能不同。例如：用户执行insert 10w条数据的事务，表空间因而增大。用户执行ROLLBACK之后，会对插入的数据回滚，但是表空间大小不会因此收缩。
    UNDO记录的是与之前操作语句相反的操作，INSERT对应DELETE，UPDATE对应反向UPDATE来实现原子性。

    举个例子来理解，例如：记录的更新过程
    1. 初始数据记录
    F1～F6是某行列的名字，1～6是其对应的数据。后面三个隐含字段分别对应该行的事务号和回滚指针，假如这条数据是刚INSERT的，可以认为ID为1，其他两个字段为空。


    2.事务1更改该行的各字段的值

    当事务1更改该行的值时，会进行如下操作：
    · 用排他锁锁定该行
    · 记录redo log
    · 把该行修改前的值Copy到UNDO LOG，即上图中下面的行
    · 修改当前行的值，填写事务编号，使回滚指针指向UNDO LOG中的修改前的行

    3.事务2修改该行的值

        与事务1相同，此时UNDO LOG，中有有两行记录，并且通过回滚指针连在一起。
    因此，如果UNDO LOG一直不删除，则会通过当前记录的回滚指针回溯到该行创建时的初始内容，在InnoDB中存在purge线程，它会查询那些比现在最老的活动事务还早的UNDO LOG，并删除它们，从而保证UNDO LOG文件不至于无限增长。

    4. 事务提交
        当事务正常提交时Innbod只需要更改事务状态为COMMIT即可，不需做其他额外的工作，而Rollback则稍微复杂点，需要根据当前回滚指针从UNDO LOG中找出事务修改前的版本，并恢复。
    注意：如果事务影响的记录非常多，回滚则可能会需要很长的时间来完成ROLLBACK。

    4. 数据库安全之 两阶段提交

        MySQL在修改数据时，MySQL是先从磁盘中将数据copy到内存，然后再将内存中的数据进行修改，并记录REDO LOG BUFFER 然后在通过系统调用将事务日志写入磁盘REDO LOG FILE，最后事务提交后将内存中修改后的数据在开始写入磁盘中。

    试想一下：
    假设当前 ID=2,c=0 的行,在执行 update 语句将c=1进行提交的过程中，当写完第一个REDO LOG日志后，BIN LOG 日志还没有写完期间发生了 crash，会出现什么情况呢？
     
        1. 先写REDO LOG 后写 BINLOG
        假设在REDO LOG 写完，BINLOG 还没有写完的时候，MySQL 进程异常crash。我们知道，REDO LOG写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。但是由于BINLOG没写完就 crash 了，这时候BINLOG里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 BINLOG里面就没有这条语句。然后你会发现，如果需要用这个 BINLOG来恢复临时库的话，由于这个语句的 BINLOG丢失，这个新数据库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。
        2. 先写 BINLOG后写 REDO LOG
        如果在 BINLOG写完之后 crash，由于REDO LOG还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 BINLOG里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 BINLOG来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。

    为了解决这个问题，MySQL中引入了“两阶段提交”的方式。
     两阶段提交原理描述:
    阶段1：InnoDB REDO LOG 写盘，InnoDB 事务进入 prepare 状态;
    阶段2：如果前面prepare成功，BINLOG写盘，那么再继续将事务日志持久化到BINLOG，如果持久化成功，那么InnoDB的事务则进入 commit 状态(实际是在REDO LOG里面写上一个commit记录)；
    备注: 每个事务BINLOG的末尾，会记录一个 XID event，标志着事务是否提交成功，也就是说，recovery 过程中，BINLOG最后一个 XID event 之后的内容都应该被 purge。
        简单说，将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是"两阶段提交"。redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

    5. 数据库安全之 Double Write

        在介绍double write之前，我们要先了解下partial page write 问题 :    
        在InnoDB数据库引擎中，数据库的Page Size一般是16KB，其数据校验也是针对16KB来计算的，在将数据写入到磁盘时也是以Page为单位进行操作的。但是计算机硬件和操作系统层，最小的读写单位是4K，即数据库在写16K的页时，是需要原子性的连续多次写入4个4K块的，在极端情况下（比如断电）往往并不能保证这一操作的原子性，16K的数据，写入4K或8K 时，系统发生了断电，导致os crash ，这时只有一部分页是写成功的，这样就出现了部分页写入的情况，导致该页在磁盘上出现了损坏，这就是 partial page write 问题。
        
        如果系统恢复后，MySQL是要根据REDO LOG进行恢复，在恢复过程中也会检查page的checksun，即最后的事务号，但发生partial page write 问题时，page是不完整的，无法利用磁盘上的4个4K块组成完整的page，从而无法进行恢复操作。

        为了解决 partial page write 问题 ，当mysql将脏数据flush到data file的时候, 先使用memcopy 将脏数据复制到内存中的double write buffer ，之后通过double write buffer再分2次，每次写入1MB到共享表空间，然后马上调用fsync函数，同步到磁盘上，避免缓冲带来的问题，在这个过程中，Double Write是顺序写，开销并不大，在完成Double Write写入后，在将double write buffer写入各表空间文件，这时是离散写入。

        如果发生了极端情况（断电），InnoDB再次启动后，发现了一个Page数据已经损坏，那么此时就可以从doublewrite buffer中进行数据恢复了。这就是MySQL数据库引入的Double Write。    

        InnoDB中的Double Write 是存放在 tablespace上的128个页（2个区）是2MB；
    原理图就是下面这样的：  
     

    6. 数据库安全之主从复制

    Master--Slave复制：
        在MySQL中，为了保证单机由于某些原因导致的存储不可用、数据丢失等问题，可以将数据同步给其它的数据库实例，从而保证数据在另外的机器上也有一份数据副本，并且与当前实例保持很小的延迟或几乎无延迟，这就是MySQL数据库的复制；
    复制就是让一台服务器的数据和其它服务器保持同步，一台主库可以同步到多台备库上面，备库也可以作为另一台服务器的主库。主库和备库之间可以有多种不同的组合方式。
     复制的过程：

        1)：主库记录二进制日志，每次准备提交事物完成数据库更新前，先记录二进制日志，记录二进制日志后，主库会告诉存储引擎可以提交事物了
        2)：备库将主库的二进制日志复制到本地的中继日志中，首先，备库会先启动一个工作进程，称为IO工作线程，负责和主库建立一个普通的客户端连接。如果该进程追赶上了主库，它将进入睡眠状态，直到主库有新的事件产生通知它，才会被唤醒，将接收到的事件记录到中继日志中。
        3)：备库的SQL线程执行最后一步，该线程从中继日志中读取事件并且在备库执行，当SQL线程赶上IO线程的时候，中继日志通常记录在系统缓存中，所以中继日志的开销很低。SQL线程也可以根据配置选项来决定是否写入其自己的二进制日志中。

        复制从架构上确保了数据保存在不同的地方，从而保障了主库数据在遭遇不可预知的物理损坏的情况下，保证数据不丢失，业务可以正常运行。
     
    7. 总结

    在MySQL中，以上的各种特性从各个方面保证了数据库中数据的安全性，也相当于说，如果你在MySQL中commit一个事务后，该事务锁涉及的数据修改都已经完整的持久化到存储上，下次再查询的时候，就一定是事务修改后的数据。所以不要对MySQL数据库的安全性再有疑虑，放心大胆的使用MySQL数据库吧。

    完

    =end=

    mysql安全
    文章转载自数据库知多少，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

    评论