相识MySQL物理文件
mysql教程栏目介绍MySQL物理文件。
1.数据库的数据存储文件
MySQL 数据库会在data名目下面创立一个以数据库为名的文件夹，用来存储数据库中的表文件数据。不一样 的数据库引擎，每个表的扩展名也不同 ，例如： MyISAM 用“ .MYD ”作为扩展名， Innodb 用 “.ibd” ， Archive 用 “.arc” ，CSV 用 “.csv“。
1. ".FRM"文件
8.0以前不管是那种存储引擎，新建表之后就一定会生成一个以表白命名的'.frm'文件。frm文件主要寄存与表相干的数据信息，主要包含表构造的定义信息。当数据库解体时，会员可以通过frm文件来恢复数据表构造。
2. ".MYD"文件
“.MYD”文件是MyISAM存储引擎专用，寄存MyISAM表的数据。每一个MyISAM表都会有一个“.MYD”文件与 之对应，一样寄存于所属数据库的文件夹 下， 和“.frm”文件在一起。
3. ".MYI"文件
“.MYI”文件也是专属于MyISAM存储引擎的，主要寄存MyISAM表的索引相干信息。关于MyISAM存储来说， 可以被cache 的内容主要就是来源 于“.MYI”文件中。 每一个MyISAM表对应一个“.MYI”文件，寄存于位置 和“.frm”以及“.MYD”同样。
4. ".ibd"文件与".ibdata"文件
这两种文件都是寄存 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文件。其中这个文件包含了 独自一个表的数据 内容以及索引内容。
4.1 两者对照
同享表空间：
长处： 可以放表空间分成多个文件寄存到各个磁盘上。数据和文件放在一起利便治理。
缺陷： 所有的数据和索引寄存到一个文件中，多个表及索引在表空间中混合存储，这样关于一个表做了批量删除操纵后表空间中将会有批量的闲暇，特殊是关于统计剖析，日志系统这类利用最不适合用同享表空间。
独立表空间：
长处:
每个表都有自已独立的表空间。
每个表的数据和索引都会存在自已的表空间中。
可以实现单表在不一样的数据库中挪移。
空间可以回购
a)
Drop table操纵主动回购表空间，要是关于统计剖析或是日值表，删除批量数据后可以通过:alter table TableName engine=innodb；回购不消的空间。b) 关于运用独立表空间的表，无论怎么删除，表空间的碎片不会太重大的影响机能，并且还有时机处置。 缺陷：单表添加过大，如超过100 G。 比拟较之下，运用独有表空间的效率以及机能会更高一点 同享表空间和独立表空间之间的转换。
show variables like "innodb_file_per_table"; ON代表独立表空间治理，OFF代表同享表空间治理； 修改数据库的表空间治理方式 修改innodb_file_per_table的参数值即可，但是修改不克不及影响以前已经 运用过的同享表空间和独立表空间； innodb_file_per_table=1 为运用独有表空间 innodb_file_per_table=0 为运用同享表空间
2. 日志
日志文件：查询日志、慢查询日志、差错日志、事务日志、binlog日志、差错日志、中继日志
2.1 查询日志
查询日志在mysql中被称之为 general log（通用日志），不要被"查询日志"的名字误导，差错的认为查询日志只会记载select语句，其实否则，查询日志记载了数据库施行的下令，无论这些语句可否准确，都会被记载，我想这也是 general log 之所以"通用"的缘由吧，因为数据库操纵下令有可能非常多并且施行比拼频繁，所以当开启了查询日志今后，数据库可能需要不绝的写入查询日志，这样会增大办事器的IO压力，添加许多系统开销，所以默许状况下，mysql的查询日志是没有开启的，但是开启查询日志也有助于我们剖析哪些语句施行密集，施行密集的select语句对应的数据可否能够被缓存，查询日志也可以帮忙我们剖析题目，所以，我们可以依据现实状况决议可否开启查询日志，要是需要可以手动开启。要是开启了查询日志，那么我们可以通过如下3种方式存储查询日志。
方式1：将查询日志寄存于指定的日志文件中。
方式2：将查询日志寄存于
mysql.general_log表中。方式3：将查询日志同时寄存于指定的日志文件与mysql库的general_log表中。
查看查询日志可否开启
show VARIABLES LIKE 'general_log';
general_log:表示查询日志可否开启，ON表示开启，OFF表示未开启，默许为OFF
log_output:表示当查询日志开启今后，以哪种方式寄存，log_output可以设定为4种值，"FILE"、"TABLE"、"FILE,TABLE"、"NONE"。
# 设定查询日志的导出方式 set global log_output=[none|file|table|file,table]; # 设定general log的日志文件途径 set global general_log_file='/tmp/general.log'; # 开启general log set global general_log=on; # 关闭general log set global general_log=off;
2.2 慢日志
所谓的慢查询就是通过设定来记载超过一按时间的SQL语句！
开启MySQL的慢查询日志功能
# 查看可否开启 未运用索引的SQL记载日志查询 show variables like 'log_queries_not_using_indexes'; # 开启 未运用索引的SQL记载日志查询 set global log_queries_not_using_indexs=on/off; # 查看超过多长工夫的查询记入慢查询日志中 show variables like 'long_query_time'; # 设定记载时长，0为全部记载，设定之后需从新启动 set global long_query_time=10 # 查看可否开启 mysql慢查询日志功能 show variables like 'slow_qurey_log' # 开启、关闭慢日志 set global slow_qurey_log=on/off; # 查看日志记载位置 show variables like 'slow_query_log_file'; #日志存储方式 show variables like "log_output";
- flie方式
select sleep(10) 施行完成查看日志
# Time: 2020-10-26T05:12:09.564006Z # User@Host: root[root] @ localhost [] Id: 12 # Query_time: 10.000272 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1 SET timestamp=1603689119; select sleep(10);
table方式
-s ORDER ORDER排序根据（al，at，ar，c，l，r，t），“at”是默许值 al: 均匀锁按时间 ar: 均匀发送行数 at: 均匀查询工夫 c: 计数 l: 锁按时间 r: 已发送行 t: 查询工夫 -r 反转排序次序（最大的最后一个而不是首先个） -t NUM 只显示前n个查询 -a 不要将所有数字抽象为N，将字符串抽象为“S” -n NUM 名字中至少有n个数字的抽象数字 -g PATTERN grep: 只考虑包括此字符串的记载 -h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard), default is '*', i.e. match all -i NAME name of server instance (if using mysql.server startup script) -l don't subtract lock time from total time2.3 差错日志
差错日志（Error Log）是 MySQL 中最常用的一种日志，主要记载 MySQL 办事器启动和休止历程中的信 息、办事器在运转历程中产生的故障和异样状况等。
# 查看差错日志记载位置 show variables like "log_error"; # 在 MySQL 中，可以运用 mysqladmin 下令来开启新的差错日志，以保障 MySQL 办事器上的硬盘空间。 # mysqladmin 下令的语法如下： mysqladmin -uroot -p flush-logs # 施行该下令后，MySQL 办事器第一会主动新建一个新的差错日志，然后将旧的差错日志更名为 filename.err-old 。可以手动直接删除。 #配置文件中配置 [mysqld] log-error=dir/{filename}2.4 二进制日志
二进制日志（Binary Log）也可叫作变动日志（Update Log），是 MySQL 中非常重要的日志。主要用于记载数据库的变化状况，即 SQL 语句的 DDL 和 DML 语句，不包括数据记载查询操纵。
# 查看 binary log 日志可否开启，binary log日志默许关闭 show variables like "log_bin"; # 在MySQL中可以再配置文件中开启二进制文件日志 [mysqld] log-bin=dir/{filename}其中，dir 参数指定二进制文件的存储途径；filename 参数指定二进制文件的文件名，其情势为 filename.number，number 的情势为 000001、000002 等，每次重新启动 MySQL 办事后，都会生成一个新的二进制日志文件，这些日志文件的文件名中 filename 局部不会转变，number 会一直递增。
二进制日志的格局有三种：STATEMENT，ROW，MIXED。
① STATEMENT模式（SBR）
每一条会修改数据的 sql 语句会记载到 binlog 中。长处是并不需要记载每一条 sql 语句和每一行的数据变化， 减少了 binlog 日志量，节约 IO ，提高机能。缺陷是在某些状况下会致使 master-slave 中的数据纷歧致(如 sleep() 函数， last_insert_id() ，以及 user-defined functions(udf) 等会涌现题目)
② ROW模式（RBR）
不记载每条 sql 语句的高低文信息，仅需记载哪条数据被修改了，修改成什么样了。并且不会涌现某些特定状况下 的存储历程、或 function 、或 trigger 的调取和触发没法被准确复制的题目。缺陷是会发生批量的日志，尤为是 alter table的时候会让日志暴涨。
③ MIXED模式（MBR）
以上两种模式的混合运用，个别的复制运用 STATEMENT 模式保留 binlog ，关于 STATEMENT 模式没法复制的操纵运用 ROW 模式保留 binlog ， MySQL 会依据施行的 SQL 语句选中日志保留方式。
binlog复制配置
在mysql的配置文件
my.cnf或中，可以通过一下选项配置binary logbinlog_format = MIXED //binlog日志格局，mysql默许采纳statement，倡议运用mixed log-bin = mysql-bin //binlog日志文件 expire_logs_days = 7 //binlog逾期清算工夫 max_binlog_size = 100m //binlog每个日志文件大小 binlog_cache_size = 4m //binlog缓存大小 max_binlog_cache_size = 512m //最大binlog缓存大小 server-id = 1
2.5二进制文件根本操纵
可以运用如下下令查看 MySQL 中是什么二进制日志文件：
show binary logsshow master status下令用来查看目前的二进制日志；二进制日志运用二进制格局存储，不克不及直接打开查看。要是需要查看二进制日志，运用
show binlog events in 'mysql-bin.000001';下令。删除二进制文件
- 运用
RESET MASTER语句可以删除的所有二进制日志 - 每个二进制日志文件背面有一个 6 位数的编号，如 000001。运用
PURGE MASTER LOGS TO 'filename.number'语句，可以删除指定二进制日志的编号以前的日志 - 运用
PURGE MASTER LOGS TO 'yyyy-mm-dd hh:MM:ss'语句，可以删除指按时间以前新建的二进制日志
- 运用
运用二进制文件恢复数据
新建数据库
CREATE TABLE `33hao_activity` ( `activity_id` mediumint(9) NOT NULL AUTO_INCREMENT COMMENT 'id', `activity_title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '标题', `activity_type` enum('1','2') CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '流动类型 1:商品 2:团购', `activity_banner` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '流动横幅大图片', `activity_style` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '流动页面模板样式标识码', `activity_desc` varchar(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '描述', `activity_start_date` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '开端工夫', `activity_end_date` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '完毕工夫', `activity_sort` tinyint(1) UNSIGNED NOT NULL DEFAULT 255 COMMENT '排序', `activity_state` tinyint(1) UNSIGNED NOT NULL DEFAULT 1 COMMENT '流动状态 0为关闭 1为开启', PRIMARY KEY (`activity_id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '流动表' ROW_FORMAT = Compact;新增 2 条数据
INSERT INTO `33hao_activity` VALUES (1, '2017年跨年满即送流动', '1', '05364373801675235.jpg', 'default_style', '', 1483113600, 1483286400, 0, 0); INSERT INTO `33hao_activity` VALUES (2, '转盘抽奖弹窗', '1', '06480453986921327.jpg', '', '转盘抽奖弹窗', 1594656000, 1594915200, 0, 0);
删除数据
drop table `33hao_activity`;
恢复数据
依据节点需要我们查看日志文件供给给我们的数据库新建，表新建，数据新增等时新建的语句节点，从而恢复数据。
mysqlbinlog --start-position=154 --stop-position=2062 D:/phpstudy_pro/Extensions/MySQL5.7.26/data/mysql-bin.000001 | mysql -uroot -p
依据工夫恢复数据
mysqlbinlog --start-datetime='2020-09-27 22:22:22' --stop-datetime='2020-09-27 22:30:00' /www/server/data/mysql-bin.000036 | mysql -uroot -p
直接施行binlog日志
mysqlbinlog /www/server/data/mysql-bin.000036 | mysql -uroot -p
更多相干免费学习举荐：mysql教程(视频)
以上就是相识 MySQL物理文件的细致内容，更多请关注 百分百源码网 其它相干文章！
