MySQL DBA面试整理

文档：MySQL DBA面试整理.note
链接：http://note.youdao.com/noteshare?id=8ac1a828da5dfd0d05d3c2edf2744311&sub=C0463009298A48A4951D3321D406DD59

MySQL DBA面试整理

主从复制

为什么要进行主从？

背景：有一个SQL语句需要进行表锁

影响：表暂停服务，不对外提供服务

目的：读写分离，一个库用来读，一个用来写写

工作：据的热备

用途：架构拓展，IO过高，降低IO的频率，提高单个机器的IO性能

优点：减轻主库的负载，当主库宕机，可以快速切换到从库

主从与集群的区别

主从：配置好主从之后同一张表只能对一个服务器进行写操作。如果在从上进行了写操作，之后主也进行了写的操作，会造成主从不同步。宕机之后需要手工切换

集群：有多台数据库服务器组成，数据库的写入和查询随机到一台数据库服务器，其他的机器会自动同步，单一出现问题不会造成影响

缺点：目前mysql集群值针对NDB，如果是innodb或其他存储引擎不可以

原理：

1、数据库有一个binlog文件：最重要的日志文件，所有的DDL/DML语句以事件的形式记录

二进制索引文件（xxx.index）用于记录所有的二进制文件

二进制文件（xxx.000*）记录DDL/DML

2：基本原理：将binlog文件复制

3：过程：在从库relay-log中重做日志文件中执行binlog中的SQL语句

4线程：

1、主库进行DDL/DML

2、从库发起连接请求

3、主库开启Binlog dump thread 将binlog传输到从库relay-log

4、从库启动I/Othread线程，将binlog写入到relay-log

5、从库创建一个SQL线程，从relay-log中读取，将内容写入到从库的数据库

方式：

异步

主库把提交事件写进binlog

给用户返回提交成功

异步传输给从库relay-log

从库应用relay-log

半同步

主库将提交事件写进binlog

将binlog传给从库relay-log

从库相应主库binlog传输完成

异步传输给从库relay-log

从库应用relay-log

延迟问题：

传输延迟

产生原因：

dump是单线程读取binlog速度慢---------增加物理读能力

网络延迟-----------------------------------------增加网络带宽

从库IO线程写能力小------------------------------raid+flash（缓存）

应用延迟

从库只有一个SQL线程----------------------------避免主库有大量的DML

慢查询-------------------------------------------创建主键/索引

从库性能低---------------------采用mixed：SQL语句（update会丢失）/行（binlog太大）/混合复制

MHA

MHA原理

该软件由两部分组成：MHA Manager（管理节点）和MHA Node（数据节点）。MHA
Manager可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台slave节点上。MHA
Node运行在每台MySQL服务器上，MHA
Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其他的slave重新指向新的master。整个故障转移过程对应用程序完全透明。

在MHA自动故障切换过程中，MHA试图从宕机的主服务器上保存二进制日志，最大程度的保证数据的不丢失，但这并不总是可行的。例如，如果主服务器硬件故障或无法通过ssh访问，MHA没法保存二进制日志，只进行故障转移而丢失了最新的数据。使用MySQL
5.5的半同步复制，可以大大降低数据丢失的风险。MHA可以与半同步复制结合起来。如果只有一个slave已经收到了最新的二进制日志，MHA可以将最新的二进制日志应用于其他所有的slave服务器上，因此可以保证所有节点的数据一致性。

manager做了几件事情

1、监控主和从

2、主宕机之后，manager会做几件事情

1. 找到新的主
   1. 如果指定候选，直接使用候选作为新的主
   2. 如果没有指定候选，或者指定了多个候选，选择延迟最小的作为主
2. 从旧的主保存binlog
3. 应用binlog到新的主
4. 在新的主上启动新的地址，也就是地址的漂移

通过执行一个脚本来实现

3、一定要注意查看manager上面的日志

如何把坏掉的主加入到 mha 环境中：

mha 默认情况下，宕掉的旧主通过以下步骤加入到 mha 环境中去，以后不能再充当
master，因为加上了 no_master 参数，认为重新成为主可能会造成数据的不一致。

1、让旧主成为从

CHANGE MASTER TO MASTER_HOST='192.168.10.52',MASTER_PORT=3306,
MASTER_LOG_FILE='mha-server.000003',MASTER_LOG_POS=154, MASTER_USER='repl',
MASTER_PASSWORD='repl';

确保新主上有旧主可以使用的复制用户

2、加入到 manager 的监控中

masterha_conf_host --command=add --conf=/etc/masterha/app1.cnf
--hostname=192.168.10.51 --block=server2 --params="no_master=1;ignore_fail=1"

也可以手工直接编辑 app1.cnf

如何将失败的 master 强行加进来

1、删除 fialover complete 文件

2、dead master 重新安装 mysql 软件、使用备份恢复搭建新的主从

3、编辑 app1.cnf，将新的从加入到配置文件中，重启 manager 进程

1、手工编辑

2、使用命令加入

MHA 日常监控命令：

# masterha_check_status --conf=/etc/masterha/app1.cnf //日常 mha
监控，主要监控manager 节点

# masterha_check_repl --conf=/etc/masterha/app1.cnf

# masterha_check_ssh --conf=/etc/masterha/app1.cnf //经常用来排错

1、发现旧主已经over，就地保存binlog，指定start-position

2、再次确定旧主已经over

3、确定其他的从的状态

4、关闭主应用和漂移ip

1. MHA的优缺点

MHA工具的优点:

由Perl语言开发的开源工具master自动监控和故障转移

master crash 不会导致主从数据不一致性

可以支持基于GTID的复制模式(MySQL 5.7版本)MHA在进行故障转移时更不易产生数据丢失

同一个监控节点可以监控多个集群

MHA加强了数据的安全性

MHA工具的缺点:

需要编写脚本或利用第三方工具来实现VIP的配置

MHA启动后只会对主数据库进行监控

需要基于SSH免认证配置，存在一定的安全隐患

没有提供从服务器的读负载均衡功能

mha 因为延迟不能启动新的主时，解决办法：

方法 1：

1. 删除 fialover complete 文件
2. 在希望成为主的那个下面，添加 check_repl_delay=0
3. 再次启动 manager
4. 原来的延迟还会继续跑，新的操作不能进行，因为漂移地址没有过来，read_only

没有关闭。mha 也会等到所有的应用日志都跑完以后，才会提升新的主。 你可以强行关闭
readonly，连接本地地址 50，先进行操作，很可能会发生主键冲突。

方法 2：

或者干等，slave 赶上 master 以后，删除 error，重启启动 manager。
需要等到新主应用所有的 binlog，生产上接受不了。所以对于 mha
来说，如果存在严重的延迟的话，基本上不能用。

方法 3：

不依靠 mha，关闭 manager，将其中一个 slave 的 ip 地址进行修改，readolny
参数去掉，提升为主

1. MHA能切换几次

只能一次

3.触发器的作用域是什么以及触发器的最小单位

作用域是表，最小单位是行

读写分离

1.应用层解决;

2.中间件实现

应用层实现的的优缺点:

优点:

1、多数据源切换方便，由程序自动完成;

2、不需要引入中间件;

3、理论上支持任何数据库;

缺点:

1、由程序员完成，运维参与不到;

2、不能做到动态增加数据源;

SQL的执行顺序

1、先执行from、join、where、on
部分，这是最主要的资源消耗点，要合理使用索引，注意表的关联顺序

2、group by、having（分组聚合），要注意优化分组聚合

3、order by，要注意优化order by，使用索引去除磁盘排序

4、执行limit，要注意优化limit，特别是在分页查询中，通过limit
实现优化，特别是order by limit，两种手法：1、前面的页使用order by limit，走order
by 索引2、后面的页使用join 的方式来优化

5、最后执行select 列，不要将子查询放在select 和from
之间，因为对于返回的每一行数据，子查询都要执行一次

锁

答：

数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。

加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。

基本锁类型：锁包括行级锁和表级锁

Mysql中有哪几种锁？

1.表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

2.行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

1. 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

一. 如何避免锁

1. 可以在修改资源的时候一下子获得所有需要修改的资源的锁，以后不再能获得其它的锁，直到本次修改完成。

2. 可以按某种顺序依次获得资源的锁。

二. 如果已经产生了锁的解决办法

1. 通过检查机制找出锁，然后牺牲掉那个进程。
2. 通过设置超时机制，自动杀掉持锁时间超时的进程，

Latch，mutex、pin

1. 在innodb 中查询到需要修改的数据页
   1. 此时会发生latch(r)争用,因为需要锁住数据链对数据链进行遍历
2. 没有找到的话释放latch(r),去磁盘查找,发生物理读
3. 在磁盘中查找时分为使用索引和不使用索引
   1. 使用索引会提高查询速度,提高sql的工作效率
   2. 不使用索引会发生全表扫描,会导致大量数据涌入innodb_buffer_pool
4. 查找到的数据会放到innodb_buffer_pool里面
   1. 此时需要在innodb_buffer_pool中查找free或者clean页
   2. 在查找free和clean页时如果free已经全部用完而且在clean不够用的情况下会发生磁盘写,导致db_write工作,然后也会更新redo和ondo,redo的作用是用来记录未写入到磁盘的脏数据的日志,用于后期数据库将在innodb_buffer_pool中的脏数据更新到磁盘中,ondo的作用是记录脏数据之前的数据,用于用户执行rollback操作
   3. 此时会发生latch(x)争用,因为要将磁盘中的数据写入到innodb_buffer_pool中
5. 在数据从磁盘上将数据写入到free或者clean中时,会发生waits特别是在数据的查找时没有使用索引时会导致大量数据涌入innodb_buffer_pool,导致发生waits
6. 释放所有的latch
   1. 写完数据后释放latch(x)和latch(s)
7. 将innodb_buffer_pool中的数据进行修改,增加pin锁
   1. Pin锁的目的是为了在修改的一瞬间保证这个数据页不会被进行任何ddl操作,以避免修改失败
   2. 在修改开始前会对改数据行所在的数据页增加表级别的ix锁,用于避免此时其他事务对该表执行ddl操作
   3. 修改过程中会对要修改的行添加x锁,使该行进入到排他状态,以保证修改数据中,不会再其他线程不会发生脏读
8. 此时如果有其他用户线程查找该行数据,会读取到该行数据最新一次commit后的数据,而不会读取到当前事务修改的数据,这也是为了避免脏读
9. 修改完数据后释放pin锁
10. 当修改完数据后会发生undo和redo
    1. Undo记录修改前的数据用于rollback
    2. redo用于记录修改后的日志写入磁盘中的log file以用来慢慢更新脏数据
11. 此时ix锁和x锁都没有释放,因为没有发生commit
    1. 也就意味着此时该表仍然处于可以进行的是select和dml不能进行ddl的状态
    2. 当然如果dml中要更新的数据是被加了x锁的这一行和读取这一行数据也是不可以的,因为该行处于x锁状态
12. commit提交
    1. 在数据修改成功后,也就是该事务完成后提交,ix表锁和x行锁释放,但数据不会及时的写入到磁盘上,因为用通过redo进程中的log
       file来后台更新磁盘上的数据

事务

事务（transaction）是作为一个单元的一组有序的数据库操作。如果组中的所有操作都成功，则认为事务成功，即使只有一个操作失败，事务也不成功。如果所有操作完成，事务则提交，其修改将作用于所有其他数据库进程。如果一个操作失败，则事务将回滚，该事务所有操作的影响都将取消。 

事务特性：

（1）原子性：即不可分割性，事务要么全部被执行，要么就全部不被执行。

（2）一致性或可串性。事务完成时，数据必须处于一致状态，数据的完整性约束没有被破坏，事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

（3）隔离性。在事务正确提交之前，不允许把该事务对数据的任何改变提供给任何其他事务，

（4）
持久性。事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后有了其他故障，事务的处理结果也会得到保存。

或者这样理解：

事务就是被绑定在一起作为一个逻辑工作单元的SQL语句分组，如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上有个节点。为了确保要么执行，要么不执行，就可以使用事务。要将有组语句作为事务考虑，就需要通过ACID测试，即原子性，一致性，隔离性和持久性。

MVCC

实现了一致性读:

1、避免脏读(已提交读)，写不阻塞读

2、实现可重复读

MVCC 实现原理:

修改删除一个数据页里的数据行，事务开始后，分配一个事务槽，数据页，先把数据行里的旧的事务
id 和 roll pointer 和原来的数据写到数据页里去，新的roll pointer
指向新的数据页的位置，事务提交了，又开启了一个事务，还是修改这行数据，相当于新的
roll pointer 从 undo 事务块指向了旧的 undo 事务块，这就是多版本控制 MVCC，在
undo 里面可以保存数据的不同时刻的多个版本。

描述 MVCC

1、MVCC 使得在一个事务中，所有的 select 访问到的是同一个时刻的数据，
反复执行一个 select，得到的数据是一致的

2、通过 undo 来实现

3、实现了 select 的可重复读隔离级别

4、可重复读隔离级别通过 MVCC 来实现 select，通过 gap lock 来实现 dml

5、mvcc 会因为长事务、大事务导致 undo 暴涨。undo 在低版本中，没有办
法自动回收，在新的版本中，undo 的自动回收机制风险很大，因此也要谨慎使
用。要避免长事务，通过监控 innodb_trx 视图，里面有两个列，一个是事务的开
始时间，一个是事务的修改行数，判断是长事务还是大事务，对于长事务，要及
时进行回滚或者提交。

备份

冷备

冷备份发生在数据库已经正常关闭的情况下，当正常关闭时会提供给我们一个完整的数据库。冷备份时将要害性文件拷贝到另外的位置的一种说法。对于备份MySQL信息而言，冷备份时最快和最安全的方法。

冷备份的优点是：

　　１、 是非常快速的备份方法（只需拷文件）

　　２、 轻易归档（简单拷贝即可）

　　３、 轻易恢复到某个时间点上（只需将文件再拷贝回去）

　　４、 能与归档方法相结合，做数据库“最佳状态”的恢复。

５、 低度维护，高度安全。

但冷备份也有如下不足：

１、 单独使用时，只能提供到“某一时间点上”的恢复。

２、
再实施备份的全过程中，数据库必须要作备份而不能作其他工作。也就是说，在冷备份过程中，数据库必须是关闭状态。

３、 若磁盘空间有限，只能拷贝到磁带等其他外部存储设备上，速度会很慢。

４、 不能按表或按用户恢复。

冷备份中必须拷贝的文件包括：

１、 所有数据文件

２、 所有控制文件