mysql中物理文件(日志文件、数据库文件、配置文件）

mysql的物理文件这里主要讲：日志文件、数据库文件和配置文件

1.Mysql日志文件

mysql的日志文件主要有5种：错误日志、二进制日志、事务日志、慢查询日志、查询日志。

1.1 错误日志(Error Log)

文件名：*.err

默认情况下错误日志大概记录以下几个方面的信息：

1. 服务器启动和关闭过程中的信息（未必是错误信息，如mysql如何启动InnoDB的表空间文件的、如何初始化自己的存储引擎的等等。
2. 服务器运行过程中的错误信息、事件调度器运行一个事件时产生的信息、在从服务器上启动服务器进程时产生的-信息。

注意：这里并不会记录sql执行错误的日志。

# 查询错误日志位置命令
show global variables like '%log_error%';

1.2 事务日志(InnoDB redo Log & undo Log)

事务日志是InnoDB独有的日志。事务日志又分为：重做日志(redo Log)、回滚日志(undo Log)

使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把修改行为记录到硬盘上持久的事务日志中，而不用每次都将修改的数据本身持久到磁盘。
事务日志采用追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。
事务日志持久以后，内存中被修改的数据在后台可以慢慢的刷回到磁盘。目前大多数的存储引擎都是这样实现的。
事务具体执行流程：https://www.cnblogs.com/maypattis/p/5628355.html

• 重做日志(redo Log)
  文件名：*.ib_logfile0
  redo log是指在回放日志的时候把已经COMMIT的事务重做一遍，对于没有COMMIT的事务按照abort处理，不进行任何操作。Redo日志记录了InnoDB所做的所有物理变更和事务信息
• 回滚日志(undo Log)
  文件名：*.ibdata
  undo log是把所有没有COMMIT的事务回滚到事务开始前的状态，系统崩溃时，可能有些事务还没有COMMIT，在系统恢复时，这些没有COMMIT的事务就需要借助undo log来进行回滚。
  Innodb存储引擎可将所有数据存放于ibdata*的共享表空间，也可将每张表存放于独立的.ibd文件的独立表空间。

-- 查看事务日志 ：
show engine innodb statusG;
-- 查看日志文件设置状态
show variables like 'innodb_%';
-- innodb_log_files_in_group DB中设置几组事务日志，默认是2
-- innodb_log_group_home_dir 事务日志存放目录；不设置，ib_logfile0...存在在数据文件目录下

1.3 二进制日志(Binary Log)

二进制日志，也就是我们常说的binlog。二进制日志记录了MySQL所有修改数据库的操作，然后以二进制的形式记录日志在日志文件中，其中还包括没调语句所执行的时间和消耗的资源，以及相关的事务信息。

作用：

1. 记录sql操作(数据备份)
2. 增量数据备份及恢复
3. sql主从复制

说明：

1. 默认情况下二进制日志是关闭的，但是在宝塔中是默认开启的。
2. 由于记录的是所有sql的执行记录，所以产生的日志文件的量会很大，这个情况下就要进行日志文件的定时挪到备份服务器或者进行定时删除。

# 查询二进制日志开启等情况
show global variables like '%log_bin%';

1.4 慢查询日志(Show Query Log)

文件名：*show.log(默认目录是data目录下)

慢查询日志中记录的是执行时间较长的query，也就是我们常说的slow query。

专业一点：慢查询日志是值所有SQL执行的实际超过long_query_time变量的语句和达到min_examined_row_limit条举例的语句。用户可以针对这部分语句性能调优。慢查询日志通过设置log-slow_queries[=file_name]选项开启后，将记录日志所在的路劲和名称。

注意:慢查询 对事务的执行是不记录的。如果测试的话，请使用select sleep(60);这样的语句来测试

-- 查看慢查询信息
show variables like "%slow%" ;
-- log_slow_queries  off    表示“慢查询”是“关闭的状态”
-- slow_launch_time  2     表示“查询时间超过2秒就记录到慢查询日志中”；
-- slow_queries_log  off   表示慢查询日志开关是关着的
-- slow_query_log_file "路径"   表示慢查询日志存放完整路径

-- 查看long_query_time(最大等待时间,或者慢查询时间)设置时间
-- 注意和slow_launch_time的区别
show variables like "%long%"
-- long_query_time  就这个值

-- 开启慢查询的功能
set global log_slow_queries=on; 
-- 这样就开启了慢查询的功能,此参数打开了，slow_query_log就自动变成了on,关闭了的话也跟着关闭。

注意：
slow_launch_time设定跟慢查询日志的查询阀值设定不同，slow_launch_time表示了thread create(线程创建)的一个阀值，如果thread create(线程创建)的时间超过了这个值，slow_launch_time的值加1。set global slow_launch_time=1 这里的时间值必须是整数，否则的话就话执行出错。
long_query_time表示超过多少秒的查询就写入日志，默认的是10s,设置为0的话表示记录所有的查询。在Mysql 5.5可以追踪到微秒的查询。

开启慢查询：

# 对应的mysql配置文件(my.cnf)中，并且还得重新启动mysql才会生效
slow_query_log=1 # 开启慢查询日志 也可以slow_query_log =on
slow_query_log_file="D:/phpStudy/1.log" # 慢查询日志文件
log_slow_queries=on   # 开启慢查询的功能
long_query_time=2 # 最大等待时间

1.5 查询日志(Query Log)

2.数据库文件

MySQL数据库会在data目录下面建立一个以数据库为名的文件夹，用来存储数据库中的表文件数据。
不同的数据库引擎，每个表的扩展名也不一样，例如：MyISAM用“.MYD”作为扩展名，Innodb用“.ibd”，Archive 用“.arc”，CSV 用“.csv

2.1 .frm文件

无论是那种存储引擎，创建表之后就一定会生成一个以表明命名的.frm文件。
.frm文件主要存放与表相关的数据信息，主要包括表结构的定义信息。当数据库崩溃时，用户可以通过frm文件来恢复数据表结构。

2.2 .MYD文件

.MYD文件是MyISAM存储引擎专用，存放MyISAM表的数据。每一个MyISAM表都会有一个.MYD文件与之对应，同样存放于所属数据库的文件夹下，和.frm文件在一起。

2.3 .MYI文件

.MYI文件也是专属于MyISAM存储引擎的，主要存放MyISAM表的索引相关信息。对于MyISAM存储来说，可以被cache 的内容主要就是来源于.MYI文件中。每一个MyISAM表对应一个.MYI文件，存放于位置和.frm以及.MYD一样。

2.4 .idb文件和iddata文件

这两种文件都是存放Innodb数据的文件，之所以有两种文件来存放Innodb的数据（包括索引），是因为Innodb的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是独享表空间存放存储数据。
独享表空间存储方式使用.ibd文件来存放数据，且每个表一个.ibd文件，文件存放在和MyISAM数据相同的位置。如果选用共享存储表空间来存放数据，则会使用ibdata文件来存放，所有表共同使用一个（或者多个，可自行配置）ibdata文件。

ibdata文件

• 可以通过innodb_data_home_dir（数据存放目录）和innodb_data_file_path(配置每个文件的名称)两个参数配置组成

  // innodb_data_file_path中可以一次配置多个ibdata文件
  innodb_data_file_path=ibdata1:2000M;ibdata2:10M:autoextend 

共享表空间以及独占表空间都是针对数据的存储方式而言的。

共享表空间: 某一个数据库的所有的表数据，索引文件全部放在一个文件中。

独占表空间: 每一个表都将会生成以独立的文件方式来进行存储，每一个表都有一个.frm表描述文件，还有一个.ibd文件。其中这个文件包括了单独一个表的数据内容以及索引内容。

共享表空间和独占表空间的对比

共享表空间：

• 优点：

可以放表空间分成多个文件存放到各个磁盘上。数据和文件放在一起方便管理。

• 缺点：

所有的数据和索引存放到一个文件中，多个表及索引在表空间中混合存储，这样对于一个表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，日值系统这类应用最不适合用共享表空间。

独立表空间：

• 优点:

1. 每个表都有自已独立的表空间。
2. 每个表的数据和索引都会存在自已的表空间中。
3. 可以实现单表在不同的数据库中移动。
4. 空间可以回收：
   a) Drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过:altertable TableName engine=innodb;回缩不用的空间。
   b) 对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。

• 缺点：

1. 单表增加过大，如超过100个G。

相比较之下，使用独占表空间的效率以及性能会更高一点

共享表空间和独立表空间之间的转换

show variables like "innodb_file_per_table"; 
-- ON代表独立表空间管理，OFF代表共享表空间管理；

-- 修改数据库的表空间管理方式
-- 修改innodb_file_per_table的参数值即可，但是修改不能影响之前已经使用过的共享表空间和独立表空间；
innodb_file_per_table=1 # 1为使用独占表空间;0为使用共享表空间

3.配置文件

3.1 系统配置文件(my.ini或my.cnf)

系统核心配置文件

linux/mac : etc/my.cnf
windows : mysql/my.ini

MySQL 的系统配置文件一般都是my.cnf，默认存放在"/etc"目录下，my.cnf文件中包含多种参数选项组（group），每一种参数组都通过中括号给定了固定的组名，如“[mysqld]”组中包括了mysqld服务启动时候的初始化参数，“[client]”组中包含着客户端工具程序可以读取的参数。

3.2 socket文件

MySQL服务器启动后socket文件自动生成，该文件主要用来连接客户端
在有些时候连接MySQL会出现如下的问题：

这个问题的解决主要是sock文件没有找到.

通常的解决办法：可以尝试重启一下MySQL的服务器；如果不信就可以去查找mysql.sock，并在my.cnf中指定该文件的位置（移动也可以）。