mysql dba 试题_MySQL DBA常见面试题及解答（二）

1、mysqldump及xtrabackup的备份原理。mysqldump的原理

(1)、调用FTWRL(flush tables with read lock)，全局禁止读写

(2)、开启快照读，获取此时的快照(仅对innodb表起作用)

(3)、备份非InnoDB表数据(*.frm,*.myi,*.myd等)

(4)、非InnoDB表备份完毕后，释放FTWRL锁

(5)、逐一备份InnoDB表数据

(6)、备份完成。innobackupex的原理

(1)、开启redo日志拷贝线程，从最新的检查点开始顺序拷贝redo日志；

(2)、开启idb文件拷贝线程，拷贝InnoDB表的数据

(3)、idb文件拷贝结束，通知调用FTWRL，获取一致性位点

(4)、备份非InnoDB表(系统表)和frm文件

(5)、由于此时没有新事务提交，等待redo日志拷贝完成

(6)、最新的redo日志拷贝完成后，相当于此时的InnoDB表和非InnoDB表数据都是最新的

(7)、获取binlog位点，此时数据库的状态是一致的。

(8)、释放锁，备份结束。

2、描述InnoDB支持的四种事务隔离级别。Read uncommitted(读未提交): 在该隔离级别，所有事务都可以看到其他未提交的事务的执行结果。读取未提交的事务，称之为“脏读”。Read Committed(读已提交)：一个事务只能看见已经提交事务所做的改变。因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。(不可重复读)Repeatable Read(可重复读)：这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。消除了脏读、不可重复读，默认也不会出现幻读。Serializable(串行)：这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题，例如：A客户端操作表时会锁定该数据，如果B客户端想要操作就需要等待A客户端释放。

3、MySQL中InnoDB引擎的行级锁是通过加在什么上实现的，为什么?

InnoDB是通过在索引记录上加锁实现行锁的。

InnoDB是将primary key和相关的行数据共同放在B+树的叶节点；通过secondary index查找数据的时候，也是通过找到对应的primary，再找对应的数据行；

因此，没有索引时就无法实现行锁，而升级成全表记录锁

4、谈一谈MySQL优化的总体思路。

(1)、硬件层：CPU、磁盘、内存

(2)、系统层：文件系统、内核参数

(3)、MySQL层：版本、参数、schema设计规范及SQL规范

(4)、流程制度

(5)、架构

5、如何提高MySQL的安全性。

(1)、数据库层

权限：禁止root账号远程登录，数据库用户权限最小化，坚决不允许DROP、TRUNCATE权限，datadir权限最小化，socket文件放在datadir下

数据一致性：从库设置read only

数据备份：每天一次全被，利用binlog server机制将binlog传输到其他远程主机上。

1)、备份策略是否合理

2)、备份数据是否安全

3)、备份数据是否可用

登录：使用 login-path

故障预案：常见故障预案、极端情况预案、定期演习

开启数据库审计

启用safe-update选项

设置MySQL账号的密码安全策略，包括长度、复制性。

(2)、操作系统层面

把sshd端口调到10000以上

把MySQL服务独立出来，不要和web server或者redis等放在一起。

(3)、网络

MySQL只对内网开放

设置防火墙策略，只允许信任的服务器连接sshd和MySQL端口；

(4)、线上操作篇

修改或删除数据前先备份，先备份，先备份(重要事情说三遍)

线上变更一定要有回退方案

批量操作中间添加sleep

DDL操作要谨慎，对于大表的alter操作最好使用pt-online-schema-change

变更操作先在测试环境测试

重启数据库前先刷脏页

禁止批量删除大量的binlog

对于变更操作一定要写详细的操作步骤，并review

按enter之前再进行一次环境确认

连接数满先修改内存变量，而不是重启，修改方式如下：

gdb -p pid -ex "set max_connections=1000" -batch

(5)、架构层

选择合适的高可用架构

上线操作前先理清架构

(6)、应用端

在web server层，可以用一些安全模块，比如NGINX的WAF模块

在APP层，做好安全审计、安全扫描，防止SQL注入、XSS攻击、CSRF攻击等

尽量把明文密码采用加密方式存储。

防止SQL注入

6、mysqldump的参数--master-data有那几个值，区别是什么？0：备份文件不记录二进制信息

1：备份文件记录二进制日志，并且不会有注释

2：备份文件记录二进制日志，会有注释

7、造成MySQL主从数据不一致的因素有哪些？

(1)、Slave没设置readonly ，client直接在从库写入了

(2)、未设置sync_binlog=1或者innodb_flush_log_at_trx_commit=1的情况主库宕机了。

(3)、主从sql_mode不一致

(4)、主从版本不一致

8、redo log和undo log的区别redo恢复是提交事务修改的页操作，undo恢复是回滚行记录到某个特定版本

redo通常是物理日志，undo是逻辑日志

9、pt-online-schema-change的原理

(1)、检查是否有触发器，检查表是否有主键。

(2)、新建一个与源表表结构相同的临时表，临时表表名为源表名+"_new"对这个表执行DDL操作，并校验是否执行成功。

(3)、然后针对源表创建三个触发器，分别如下：

delete触发器：源表执行delete操作的时候，删掉新表中db._tb_new.id <=> OLD.id的数据，否则忽略操作

update触发器：源表执行update的时候，把对应的数据replace into的方式写入新表

insert触发器：源表执行iinsert操作的时候，把对应的数据replace into的方式写入新表

(4)、触发器创建好之后会执行INSERT LOW_PRIORITY IGNORE INTO......语句复制源表数据到新表。

(5)、复制完成之后同时执行：把源表名修改为old结尾的表名，把临时表("_new"结尾的)修改为源表名字。

(6)、接着，如果没有加不删除old表的选项，那么就会删除old表，然后删除三个触发器。完成了在线表结构的修改 。整个过程只在rename表的时间会锁一下表，其他时候不锁表。

10、工作或者学习过程中碰到过哪些棘手的问题？

开放型问题，最好挑难一点的，并总结自己的处理方式。