关于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...