java【运维】面试题

InnoDB的事务与日志的实现方式？

有多少种日志？

• redo日志
• undo日志

日志的存放形式？

• redo：在页修改的时候，先写到redo log buffer里面，然后写到redo log的文件系统缓存里面（fwrite），然后再同步到磁盘文件（fsync）
• undo：在MySQL5.5之前，undo只能存放在ibdata文件里面，5.6之后，可以通过设置innodb_undo_toblespaces参数把undo log存放在ibdata之外。

事务时如何通过日志来实现的？

基本流程如下：

• 因为事务在修改页时，要先记undo，在记undo之前要记undo的redo，然后修改数据页，再记数据页修改的redo。redo（里面包括undo修改）一定要比数据页先持久化到磁盘。
• 当事务需要回滚时，因为有undo，可以把数据页回滚到前镜像的状态。
• 崩溃回复时，如果redo log中事务没有对应的commit记录，那么需要用undo把该事务的修改回滚到事务开始之前。如果有commit记录，就用redo前滚到该事务完成时并提交掉。

MySQL binlog的几种日志录入格式以及区别？

各种日志格式的涵义？

binlog有三种格式类型，分别如下：

1）statement

　　每一条会修改数据的SQL都会记录在binlog中。

• 优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。（相比row能节约多少性能与日志量，这个取决于应用的SQL情况，正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该根据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题）
• 缺点：由于记录的只是之行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证语句能在slave得到和在master端执行时候相同的结果。另外MySQL的复制，像一些特定函数功能，slave可与master上要保持一致会有很多相关问题（如sleep（））函数，last_insert_id()，以及user-defined functions（udf）会出现问题。
• 使用以下函数的语句也无法复制：
  • LOAD_FILE()
  • UUID()
  • USER()
  • FOUND_ROW()
  • SYSDATE()除非启动时启用了-system-is-now选项。
  • 同时在insert_select会产生比RBR更多的行级锁。

2）ROW

　　不记录SQL语句上下相关信息，仅保存那条记录被修改。

• 优点：binlog中可以不记录执行的SQL语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题。
• 缺点：所有的执行的语句当记录到日志中的时候，都将以每行记录，这样可能会产生大量的日志内容，比如，一条update语句，修改多条记录，则binlong中，每一条修改都会记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。

3）MixedLevel

是以两种level的混合使用。

• 一般的语句修改使用Statment格式保存binlog
• 如一些函数，statement无法完成主从复制的操作，则采用Row格式保存binlog。

　　MySQL会根据执行的每一条具体的SQL语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。

　　新版本的MySQL中对row level模式也被做了优化，并不是所有的修改都会以row level模式。

• 像遇到表结构变更的时候就会以Statement模式来记录。
• 至于Update或者Delete等修改数据的语句，还是会记录所有行的变更，即使用Row模式。

使用场景？

在一条SQL操作了多行数据时，Statement更节省空间，Row更占用空间，但是Row模式更可靠。

因为，互联网公司，使用Mysql的功能相对少，基本不使用存储过程，触发器，函数的功能，选择默认的语句模式，Statement （默认）即可

结合第一个问题，每一种日志格式在复制中的优劣？

• Statement可能占用空间会相对小一些，传送到slave的时间可能也短，但是没有Row模式可靠。
• Row模式在操作多行数据时更占用空间，但是可靠。

所以，这是在占用空间和可靠之间的选择

如何在线正确清理MySQL binlog？

　　MySQL中的binlog日志记录了数据中的数据变动，便于对数据的基于时间点和机遇位置的回复。但日志文件的大小会越来越大，占用了大量的磁盘克难攻坚，因此需要定时清理一部分日志信息。

#首先查看主从库正在使用的binlog文件名称
show master(slave) status

#删除之前一定要备份
purge master logs before '2017-09-01 00:00:00';#删除指定时间前的日志。
purge master logs to 'mysql-bin.000001' #删除指定的日志文件

#自动删除：通过设置binlog的过期时间让系统自动删除日志
show variables like 'expire_logs_days';查看过期时间
set global expire_log_days=30;# 设置过期时间

MySQL主从复制的流程是怎么样的？

　　MySQL的主从复制是基于如下3个线程的交互（多线程复制里面应该是4类线程）：

• Master上面的binlog dump线程，该线程负责将master的binlog event传到slave
• Slave上面的IO线程，该线程负责接收Master传过来的binlog，并写入relay log。
• Slave上面的SQL线程，该线程负责读取relay log并执行。
• 如果是多线程复制，无论是5.6库级别的假多线程还是MariaDB或者5.7的真正的多线程复制，SQL线程只做coordinator，只负责把relay log中的binlog读出来然后交给worker线程，worker线程负责具体binlog event的执行。

MySQL如何保证复制过程中数据的一致性的？

　　1.在MySQl5.5以及之前，slave的SQL线程执行的relay log的位置只能保存在文件（relay-log.info）里面，并且该文件默认每执行10000次事务醉一次同步到磁盘，这意味着slave意外crash重启时，SQL线程执行到的位置和数据库的数据是不一致的，将导致复制报错。若功不重搭复制，则有可能会导致数据不一致。

• MySQL5.6引入参数relay_log_info_repository,将该参数设置为TABLE时，MySQL将SQL线程执行到的位置存到mysql.slave_relay_info_log表，这样更新该表的位置和SQL线程执行的用户事务绑定完成一个事务，这样slave意外宕机后。slave通过innodb的崩溃回复可以把SQL线程执行到的位置和用到事务回复到一致性的状态。

　　2.MySQL5.6引入GTID复制，每个GTID对应的事务在每个实例上面最多执行一次，这极大地提高了复制的数据一致性。

　　3.MySQL5.5引入版同步复制，用户安装版同步复制插件并且开启参数后，设置超时时间，可保证在超时时间内如果binlog不穿到slave上面，那么用户提交事务时不会返回，直到超时后切成异步复制，但是如果切成异步之前用户线程提交时在master上面等待的时候，事务已经提交，该事务对master上面的其他session是可见的，如果这时master宕机，那么到slave上面该事务又不可见了，该问题直到5.7才解决。

　　4.MySQL5.7引入半同步复制，引入参数rpl_semi_sync_master_wait_point,该参数默认为after_sync，指的是在切成半同步之前，事务不提交，而是接受slave的AVK确认之后才提交该事务，从此，复制真正可以做到无损的了。

　　5.可以说一下5.7的无损情况下，master意外宕机，重启后发现有binlog没有传到slave上面，这部分binlog怎么办？分两种情况讨论：1.宕机时已经切成异步了，2.是宕机时还没有切成异步？这个怎么判断宕机时有没有切成异步呢？分别怎么处理？

MySQL如何解决主从复制的延时性？

　　5.5是单线程复制，5.6是多库复制（对于单库或者单表的并发操作是没用的），5.7是真正意义的多线程复制，它的原理是基于group commit，只要master上面的事务时group commit的，那slave上面也可以通过多个worker线程去并发执行，和MairaDB10.0.0.5引入多线程复制原理基本相同。

工作遇到的复制bug解决办法？

5.6的多库复制有时候自己会停止，我们写了一个脚本重新start slave。

你是否做过主从一致性校验，如果有，怎么做的，如果没有，打算怎么做？

主从一致性校验的工具有很多例如：checksum、mysqldiff、pt-table-checksum等

聊聊MySQL备份方式？备份策略是怎么样的？

MySQL 一般有 3 种备份方式。

1）逻辑备份

使用 MySQL 自带的 mysqldump 工具进行备份。备份成sql文件形式。

优点：最大好处是能够与正在运行的 MySQL 自动协同工作，在运行期间可以确保备份是当时的点，它会自动

将对应操作的表锁定，不允许其他用户修改(只能访问)。可能会阻止修改操作。SQL 文件通用方便移植。

缺点：备份的速度比较慢。如果是数据量很多的时候，就很耗时间。如果数据库服务器处在提供给用户服务

状态，在这段长时间操作过程中，意味着要锁定表(一般是读锁定，只能读不能写入数据)，那么服务就会影响

的。

2）物理备份

：因为现在主流是 InnoDB ，所以基本不再考虑这种方式。

直接拷贝只适用于 MyISAM 类型的表。这种类型的表是与机器独立的。但实际情况是，你设计数据库的时候不可

能全部使用 MyISAM 类型表。你也不可能因为 MyISAM 类型表与机器独立，方便移植，于是就选择这种表，这并

不是选择它的理由。

缺点：你不能去操作正在运行的 MySQL 服务器(在拷贝的过程中有用户通过应用程序访问更新数据，这样就

无法备份当时的数据)，可能无法移植到其他机器上去。

3）双机热备份。

当数据量太大的时候备份是一个很大的问题，MySQL 数据库提供了一种主从备份的机制，也就是双机热备。

优点：适合数据量大的时候。现在明白了，大的互联网公司对于 MySQL 数据备份，都是采用热机备份。搭建

多台数据库服务器，进行主从复制。

🦅 数据库不能停机，请问如何备份? 如何进行全备份和增量备份?

可以使用逻辑备份和双机热备份。完全备份：完整备份一般一段时间进行一次，且在网站访问量最小的时候，这样常借助批处理文件定时备

份。主要是写一个批处理文件在里面写上处理程序的绝对路径然后把要处理的东西写在后面，即完全备份数

据库。

增量备份：对 ddl 和 dml 语句进行二进制备份。且 5.0 无法增量备份，5.1 后可以。如果要实现增量备份需要

在 my.ini 文件中配置备份路径即可，重启 MySQL 服务器，增量备份就启动了。

🦅 你的备份工具的选择？备份计划是怎么样的？

视库的大小来定，一般来说 100G 内的库，可以考虑使用 mysqldump 来做，因为 mysqldump 更加轻巧灵活，备

份时间选在业务低峰期，可以每天进行都进行全量备份(mysqldump 备份出来的文件比较小，压缩之后更小)。

100G 以上的库，可以考虑用 xtrabackup 来做，备份速度明显要比 mysqldump 要快。一般是选择一周一个全

备，其余每天进行增量备份，备份时间为业务低峰期。

：一般情况下，选择每周备份 + 每天增量备份比较靠谱。

🦅 备份恢复时间是多长？

物理备份恢复快，逻辑备份恢复慢。

这里跟机器，尤其是硬盘的速率有关系，以下列举几个仅供参考：

20G 的 2 分钟（mysqldump）

80G 的 30分钟（mysqldump)

111G 的 30分钟（mysqldump)

288G 的 3 小时（xtrabackup)

3T 的 4 小时（xtrabackup)

逻辑导入时间一般是备份时间的 5 倍以上。

🦅 备份恢复失败如何处理？

首先在恢复之前就应该做足准备工作，避免恢复的时候出错。比如说备份之后的有效性检查、权限检查、空间检查

等。如果万一报错，再根据报错的提示来进行相应的调整。

🦅 mysqldump 和 xtrabackup 实现原理？

1）mysqldump

mysqldump 是最简单的逻辑备份方式。

在备份 MyISAM 表的时候，如果要得到一致的数据，就需要锁表，简单而粗暴。

在备份 InnoDB 表的时候，加上 –master-data=1 –single-transaction 选项，在事务开始时刻，记录下

binlog pos 点，然后利用 MVCC 来获取一致的数据，由于是一个长事务，在写入和更新量很大的数据库上，

将产生非常多的 undo ，显著影响性能，所以要慎用。

优点：简单，可针对单表备份，在全量导出表结构的时候尤其有用。

缺点：简单粗暴，单线程，备份慢而且恢复慢，跨 IDC 有可能遇到时区问题

2）xtrabackup

xtrabackup 实际上是物理备份+逻辑备份的组合。

在备份 InnoDB 表的时候，它拷贝 ibd 文件，并一刻不停的监视 redo log 的变化，append 到自己的事务日

志文件。在拷贝 ibd 文件过程中，ibd文件本身可能被写”花”，这都不是问题，因为在拷贝完成后的第一个

prepare 阶段，xtrabackup 采用类似于 Innodb 崩溃恢复的方法，把数据文件恢复到与日志文件一致的状

态，并把未提交的事务回滚。如果同时需要备份 MyISAM 表以及 InnoDB 表结构等文件，那么就需要用 flush tables with lock 来获

得全局锁，开始拷贝这些不再变化的文件，同时获得 binlog 位置，拷贝结束后释放锁，也停止对 redo log 的

监视。

🦅 如何从 mysqldump 产生的全库备份中只恢复某一个库、某一张表？

 

聊聊 MySQL 集群?

🦅 对于简历中写有熟悉 MySQL 高可用方案？

我一般先问他现在管理的数据库架构是什么，如果他只说出了主从，而没有说任何 HA 的方案，那么我就可以判断

出他没有实际的 HA 经验。

不过这时候也不能就是断定他不懂 MySQL 高可用，也许是没有实际机会去使用，那么我就要问 MMM 以及 MHA

以及 MM + keepalived 等的原理、实现方式以及它们之间的优势和不足了，一般这种情况下，能说出这个的基本

没有。

MMM 那东西好像不靠谱，据说不稳定，但是有人在用的，和 mysql-router 比较像，都是指定可写的机器和

只读机器。

MHA 的话一句话说不完，可以搜索下相关博客。

🦅 使用过其他分支版本的数据库吗？Percona、Mariadb 等。对Percona 的 pxc 集群了解吗？

除了 Oracle 旗下的 MySQL 外，我还使用过 Percona Server 。

Percona 是在原生 MySQL 的基础上，进行了优化和改进，所以 Percona 的性能比 MySQL 更好。

目前，我知道 Percona 提供免费的线程池功能，而社区版的 MySQL 没有线程池的功能（当然，企业版的

mysql是有线程池的，但是需要收费）

另外 Percona 还支持 NUMA 等功能。

我熟悉 pxc ，我曾经在测试环境搭建过 pxc ，但是没有在生产上使用，因为目前使用 pxc 的企业不是很多，目前

我知道搜狐在用 pxc 。 

pxc 是摒弃 MySQL 主从的概念，即对于 pxc 来说，每个节点都可以读写，并且写一份数据，其他节点会同时

拥有，这是一种同步的复制方案（区别于 MySQL 主从的异步复制）。

MySQL 有哪些日志？

错误日志：记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。二进制文件：记录了所有的DDL（数据定义语言）语句和 DML（数据操纵语言）语句，不包括数据查询语

句。语句以“事件”的形式保存，它描述了数据的更改过程。（定期删除日志，默认关闭）。

就是我们上面看到的 MySQL binlog 日志。

查询日志：记录了客户端的所有语句，格式为纯文本格式，可以直接进行读取。（log 日志中记录了所有数据

库的操作，对于访问频繁的系统，此日志对系统性能的影响较大，建议关闭，默认关闭）。

慢查询日志：慢查询日志记录了包含所有执行时间超过参数long_query_time（单位：秒）所设置值的 SQL

语句的日志。（纯文本格式）