关于mysql:MySQL面试宝典文件篇

一. 请简述 MySQL 配置文件的加载程序？
二.MySQL 启动时如果找不到配置 (参数) 文件，会报错还是启动？
三. 如何查看 MySQL 参数？
四. 如何批改 MySQL 参数?
五：MySQL 有哪些类型表空间，简述各自作用？
六：请简述 MySQL redo log 和 binlog 区别？
一. 请简述 MySQL 配置文件的加载程序？

MySQL 读取配置文件的程序

读取程序：/etc/mysql/my.cnf>/etc/my.cnf>~/.my.cnf

命令验证：

办法 1：

[mysql@mysql01 bin]$ mysql –help|grep my.cnf
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf

                  order of preference, my.cnf, $MYSQL_TCP_PORT,

[mysql@mysql01 bin]$ mysql –verbose –help | grep my.cnf
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf

                  order of preference, my.cnf, $MYSQL_TCP_PORT,

办法 2：

[mysql@mysql01 bin]$ my_print_defaults –help|grep -A2 -B2 my.cnf
Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf
Variables (–variable-name=value)
二.MySQL 启动时如果找不到参数文件，会报错还是启动？

MySQL 数据库参数文件的作用和 Oracle 数据库的参数文件极其相似，不同的是，Oracle 实例在启动时若找不到参数文件，是不能进行装载（mount）操作。

MySQL 略微有所不同，MySQL 实例能够不须要参数文件，这时所有的参数值取决于编译 MySQL 时指定的默认值和源代码中指定参数的默认值。

如果 MySQL 实例在默认的数据库目录下找不到 mysql 架构，则启动同样会失败。

三. 如何查看 MySQL 参数？

能够把数据库参数看成一个键 / 值（key/value）对。

能够通过命令 SHOW VARIABLES 查看数据库中的所有参数，也能够通过 LIKE 来过滤参数名。

从 MySQL 5.1 版本开始，还能够通过 information_schema 架构下的 GLOBAL_VARIABLES 视图来进行查找。

show variables like ‘%timeout%’;
mysql> SHOW [{GLOBAL|SESSION}] VARIABLES [LIKE ”];
mysql> SELECT @@{GLOBAL|SESSION}.VARIABLE_NAME;
mysql> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME=’VARIABLE_NAME’;
mysql> SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME=’VARIABLE_NAME’;
通过配置文件查看参数

cat /etc/my.cnf|grep -i “VARIABLE_NAME”

四. 如何批改 MySQL 参数?

会话级别批改：

set session innodb_lock_wait_timeout=50;

对以后会话立刻失效，退出后，参数生效，不影响后续的会话

全局级别批改：

set global innodb_lock_wait_timeout=50;

以后会话不失效，对后续连贯进来的会话失效

批改配置文件

五：MySQL 有哪些类型表空间，简述各自作用？

MySQL 有五种表空间:

零碎表空间(也叫共享表空间)、独立表空间、长期表空间、undo 表空间、通用表空间。

1. 零碎表空间

次要用来寄存 undo 信息、insert buffer 索引页、double write buffer 等数据。

零碎表空间零碎表空间（system tablespace）是在初始化 mysql 实例时生成的，读取 my.cnf 中的 innodb_data_file_path 参数，初始对应大小的文件。

mysql 默认的零碎表空间文件大小是 12M，只有一个文件(ibdata1)，它默认是保留在 mysql 实例的 datadir 变量的目录下。

在 mysql 实例中查看共享表空间的大小

+——————————–+ # autoextend 主动扩大
1 row in set (0.00 sec)

在 mysql 的 datadir 变量所指定的目录下查看零碎表空间文件

1 row in set (0.00 sec)
当零碎表空间不够用时（也就是 ibdata1 文件），会主动扩大（autoextend），默认每次主动扩大 64M。

1 row in set (0.00 sec)
2. 独立表空间

从 mysql 5.6.6 版本开始，独立表空间（file-per-table tablespaces）默认是开启的（也就是 innodb_file_per_table 参数不设置时，默认等于 1），在开启的状况下，创立一个 innodb 引擎的表，那么表有本人独立的一些数据文件。

这些数据文件在操作系统上的文件体现如下所示：

表名.frm # 表的表构造文件(外面寄存的是表的创立语句)

表名.ibd # 表的数据文件(当有数据往表中插入时, 数据就保留之个文件中的)

独立表空间的益处:

01: 表数据离开寄存(不把所有鸡蛋放在 1 个蓝子外面); 损坏 1 个文件不至于影响所有表

02: 容易保护, 查问速度快（IO 扩散）

03: 应用 MySQL Enterprise Backup 疾速备份或还原在每表文件表空间中创立的表，不会中断其余 InnoDB 表的应用

毛病：

对 fsync 零碎调用来说不敌对，如果应用一个表空间文件的话单次零碎调用能够实现数据的落盘，然而如果你将表空间文件拆分成多个。

原来的一次 fsync 可能会就变成针对波及到的所有表空间文件别离执行一次 fsync，减少 fsync 的次数。

独立表空间文件中仅寄存该表对应数据、索引、insert buffer bitmap。

其余的诸如：undo 信息、insert buffer 索引页、double write buffer 等信息仍然放在默认表空间，也就是共享表空间中。

当 innodb_file_per_table 参数为 0 时，示意应用零碎表空间，当为 1 时，示意应用独立表空间。

innodb_file_per_table 选项只对新建的表起作用，对于曾经调配了表空间的表不起作用。

如果想把曾经调配到零碎表空间中的表转移到独立表空间，能够应用上面语句:

ALTER TABLE 表名 TABLESPACE [=] innodb_file_per_tables;

如果要将曾经存储在独立表空间的表转移到零碎表空间:

ALTER TABLE 表名 TABLESPACE [=] innodb_system;

其中中括号里的 = 可有可无。

与 InnoDB 不同，MyISAM 并没有什么表空间一说，表的数据和索引都寄存在对应的数据库子目录下。

如果 cjc 表应用的是 MyISAM 存储引擎，那么他所在数据库对应的目录下会为 cjc 表创立上面 3 个文件：

1.cjc.frm 表构造。2.cjc.MYD 表数据。3.cjc.MYI 表索引。

3. 长期表空间

长期表空间用于寄存用户创立的长期表和磁盘外部长期表。

参数 innodb_temp_data_file_path 定义了长期表空间的一些名称、大小、规格属性

1 row in set (0.00 sec)
MySQL 5.7 对于 InnoDB 存储引擎的长期表空间做了优化。

在 MySQL 5.7 之前，INNODB 引擎的长期表都保留在 ibdata 外面，而 ibdata 的贪心式磁盘占用导致长期表的创立与删除对其余失常表产生十分大的性能影响。

在 MySQL5.7 中，对于长期表做了上面两个重要方面的优化：

(1)MySQL 5.7 把长期表的数据以及回滚信息 (仅限于未压缩表) 从共享表空间外面剥离进去，造成本人独自的表空间，参数为 innodb_temp_data_file_path。

(2)MySQL 5.7 把长期表的相干检索信息保留在零碎信息表中：information_schema.innodb_temp_table_info.

而 MySQL 5.7 之前的版本想要查看长期表的零碎信息是没有太好的方法。

select * frominformation_schema.innodb_temp_table_info;

须要留神的一点就是：

尽管 INNODB 长期表有本人的表空间，然而目前还不能自己定义长期表空间文件的保留门路，只能是继承 innodb_data_home_dir。

此时如果想要拿其余的磁盘，比方内存盘来充当长期表空间的保留地址，只能用老办法，做软链。

MySQL 长期表类型

1. 内部长期表，通过 create temporary table 语法创立的长期表，能够指定存储引擎为 memory，innodb, myisam 等等，这类表在会话完结后，会被主动清理。

如果长期表与非长期表同时存在，那么非长期表不可见。show tables 命令不显示长期表信息。

可通过 informationschema.INNODBTEMPTABLEINFO 零碎表能够查看内部长期表的相干信息

2. 外部长期表，通常在执行简单 SQL，比方 group by, order by, distinct, union 等，执行打算中如果蕴含 Using temporary.

还有 undo 回滚的时候，但空间有余的时候，MySQL 外部将应用主动生成的长期表，以辅助实现工作。

内部长期表、外部长期表

参数

tmp_table_size

外部长期表在内存中的的最大值，与 max_heap_table_size 参数独特决定，取二者的最小值。如果长期表超过该值，就会从内存转移到磁盘上；

max_heap_table_size

用户创立的内存表的最大值，也用于和 tmp_table_size 一起，限度外部长期表在内存中的大小；

innodb_tmpdir

innodb_temp_data_file_path

innodb 引擎下 temp 文件属性。倡议限度 innodbtempdatafilepath = ibtmp1:1G:autoextend:max:30G；

default_tmp_storage_engine

内部长期表 (create temporary table 创立的表) 默认的存储引擎；

internal_tmp_disk_storage_engine

磁盘上的外部长期表存储引擎，可选值为 myisam 或者 innodb。

应用 innodb 表在某些场景下，比方长期表列太多，或者行大小超过限度，可能会呈现“Row size too large or Too many columns”的谬误，这时应该将长期表的 innodb 引擎改回 myisam。

slave_load_tmpdir

tmpdir

示意磁盘上长期表所在的目录。

长期表目录，当长期表大小超过肯定阈值，就会从内存转移到磁盘上；

max_tmp_tables

状态信息

Created_tmp_disk_tables

执行 SQL 语句时，MySQL 在磁盘上创立的外部长期表数量，如果这个值很大，可能起因是调配给长期表的最大内存值较小，或者 SQL 中有大量排序、分组、去重等操作，SQL 须要优化；

Created_tmp_files

创立的长期表数量；

Created_tmp_tables

执行 SQL 语句时，MySQL 创立的外部长期表数量；

Slave_open_temp_tables

statement 或则 mix 模式下才会看到有应用；

通过复制，以后 slave 创立了多少长期表

information_schema.innodb_temp_table_info

4.undo 表空间

MySQL5.5 时代的 undo log

在 MySQL5.5 以及之前，InnoDB 的 undo log 也是寄存在 ibdata1 外面的。一旦呈现大事务，这个大事务所应用的 undo log 占用的空间就会始终在 ibdata1 外面存在，即便这个事务曾经敞开。

答案是没有间接的方法，只能全库导出 sql 文件，而后从新初始化 mysql 实例，再全库导入。

MySQL 5.6 时代的 undo log

MySQL 5.6 减少了参数 innodb_undo_directory、innodb_undo_logs 和 innodb_undo_tablespaces 这 3 个参数，能够把 undo log 从 ibdata1 移出来独自寄存。

innodb_undo_directory，

指定独自寄存 undo 表空间的目录，默认为.(即 datadir)，能够设置相对路径或者绝对路径。

该参数实例初始化之后尽管不可间接改变，然而能够通过先停库，批改配置文件，而后挪动 undo 表空间文件的形式去批改该参数；

innodb_undo_tablespaces，

指定独自寄存的 undo 表空间个数，例如如果设置为 3，则 undo 表空间为 undo001、undo002、undo003，每个文件初始大小默认为 10M。

该参数咱们举荐设置为大于等于 3，起因下文将解释。该参数实例初始化之后不可改变；

innodb_undo_logs，

指定回滚段的个数(晚期版本该参数名字是 innodb_rollback_segments)，默认 128 个。每个回滚段可同时反对 1024 个在线事务。

这些回滚段会均匀散布到各个 undo 表空间中。

该变量能够动静调整，然而物理上的回滚段不会缩小，只是会管制用到的回滚段的个数。

理论应用方面，在初始化实例之前，咱们只须要设置 innodb_undo_tablespaces 参数 (倡议大于等于 3) 即可将 undo log 设置到独自的 undo 表空间中。

MySQL 5.7 时代的 undo log

MySQL 5.7 引入了新的参数，innodb_undo_log_truncate，开启后可在线膨胀拆分进去的 undo 表空间。

在满足以下 2 个条件下，undo 表空间文件可在线膨胀：

innodb_undo_tablespaces>=2。因为 truncate undo 表空间时，该文件处于 inactive 状态，如果只有 1 个 undo 表空间，那么整个零碎在此过程中将处于不可用状态。

为了尽可能升高 truncate 对系统的影响，倡议将该参数起码设置为 3；

innodb_undo_logs>=35(默认 128)。

因为在 MySQL 5.7 中，第一个 undo log 永远在零碎表空间中，另外 32 个 undo log 调配给了长期表空间，即 ibtmp1，至多还有 2 个 undo log 能力保障 2 个 undo 表空间中每个外面至多有 1 个 undo log；

满足以上 2 个条件后，把 innodb_undo_log_truncate 设置为 ON 即可开启 undo 表空间的主动 truncate，这还跟如下 2 个参数无关：

(1)innodb_max_undo_log_size，undo 表空间文件超过此值即标记为可膨胀，默认 1G，可在线批改；

(2)innodb_purge_rseg_truncate_frequency, 指定 purge 操作被唤起多少次之后才开释 rollback segments。当 undo 表空间外面的 rollback segments 被开释时，undo 表空间才会被 truncate。由此可见，该参数越小，undo 表空间被尝试 truncate 的频率越高。

MySQL 8.0 膨胀 UNDO

1、增加新的 undo 文件 undo003。mysql8.0 中默认 innodb_undo_tablespace 为 2 个，有余 2 个时，不容许设置为 inactive，且默认创立的 undo 受爱护，不容许删除。

2、将收缩的 undo 长期设置为 inactive，以及 innodb_undo_log_truncate=on，主动 truncate 开释收缩的 undo 空间。

3、从新将开释空间之后的 undo 设置为 active，可从新上线应用。

具体操作如下：

5 rows in set (0.00 sec)
查看 undo 大小

mysql[(none)]> system du -sh /app/dbdata/datanode3307/log/undo*
10G /app/dbdata/datanode3307/log/undo_001
增加新的 undo 表空间 undo003。零碎默认是 2 个 undo，大小设置 4G

mysql[(none)]>
mysql[(none)]> create undo tablespace undo001 add datafile ‘/usr/local/mysql/data/undo/undo001.ibu’;
Query OK, 0 rows affected (0.21 sec)
留神：创立增加新的 undo 必须以.ibu 结尾，否则触发如下谬误提醒

mysql[(none)]> create undo tablespace undo003 add datafile ‘/app/dbdata/datanode3307/log/undo_003.’ ;
ERROR 3121 (HY000): The ADD DATAFILE filepath must end with ‘.ibu’.
5. 通用表空间

通用表空间(General Tablespaces)

通用表空间为通过 create tablespace 语法创立的共享表空间。

通用表空间能够创立于 mysql 数据目录外的其余表空间，其能够包容多张表，且其反对所有的行格局。

通过 create table tab_name … tablespace [=] tablespace_name 或 alter table tab_name tablespace [=] tablespace_name 语法将其增加与通用表空间内。

六：请简述 MySQL redo log 和 binlog 区别？

redo log 和 binlog 的区别：

日志归属：

binlog 由 Server 层实现，所有引擎都能够应用。

redo log 是 innodb 引擎特有的日志。

日志类型：

binlog 是逻辑日志，记录原始 SQL 或数据变更前后内容。

redo 是物理日志，记录在哪些页上进行了哪些批改。

写入形式：

binlog 是追加写，写满一个文件后创立新文件持续写。

redo log 是循环写，全副写满后笼罩从头写。

实用场景：

binlog 实用于主从复原和误删除复原。

redo log 实用于解体复原。

尽管在更新 BufferPool 后，也写入了 binlog 中，但 binlog 并不具备 crash-safe 的能力。

因为解体可能产生在写 binlog 后，刷脏前。在主从同步的状况下，从节点会拿到多进去的一条 binlog。

所以 server 层的 binlog 是不反对解体复原的，只是反对误删数据恢复。InnoDB 思考到这一点，本人实现了 redo log。

因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，然而 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。

而 InnoDB 是另一个公司以插件模式引入 MySQL 的，既然只依附 binlog 是没有 crash-safe 能力的，所以 InnoDB 应用另外一套日志零碎——也就是 redo log 来实现 crash-safe 能力。

源码附件曾经打包好上传到百度云了，大家自行下载即可～

链接: https://pan.baidu.com/s/14G-b…
提取码: yu27
百度云链接不稳固，随时可能会生效，大家放松保留哈。

如果百度云链接生效了的话，请留言通知我，我看到后会及时更新～

开源地址
码云地址：
http://github.crmeb.net/u/defu

Github 地址：
http://github.crmeb.net/u/defu

本文转载自：http://blog.itpub.net/2978580…