【Mysql高级特性】 InnoDB 最全的文件介绍

相关文章 地址
初探 InnoDB 体系架构 初探 InnoDB 体系架构
InnoDB Checkpoint与 Redo log InnoDB Checkpoint与 Redo log
InnoDB 的关键特性 InnoDB 的关键特性

💓 各类型文件

  • 参数文件:也是mysql 配置文件,比如数据文件地址,舒适化蚕食,内存结大小设置
  • 日志文件:对某种条件做出响应时写入的文件,错误日志、二进制入职文件、慢查询入职文件、查询日志文件
  • socket 文件: 用 unix 域套接字方式进行连接时需要的文件
  • pid文件:进程ID文件
  • MYSQL表结构文件:表结构定义文件
  • 存储引擎文件:真正存储了记录和索引等数据的文件

💓参数文件

mysql 实例启动时,数据库会先去读一个参数配置文件,用来虚招数据库个文件所在位置以及指定某些初始化参数。
参数就是一个 key/value 对,可以使用 show variables命令查看
在这里插入图片描述

参数类型

  1. 动态参数:可以在mysql实例运行时进行更爱
  2. 静态参数:在整个实例的生命周期内都不可以进行更改 (read only)

[client]

# pipe=

# socket=MYSQL

port=3306

[mysql]
no-beep

# default-character-set=

# SERVER SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this 
# file.
#
# server_type=3
[mysqld]


port=3306


datadir=C:/ProgramData/MySQL/MySQL Server 8.0\Data

default_authentication_plugin=caching_sha2_password

default-storage-engine=INNODB

sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

# General and Slow logging.
log-output=FILE

general-log=0

general_log_file="DESKTOP-R05O7NE.log"

slow-query-log=1

slow_query_log_file="DESKTOP-R05O7NE-slow.log"

long_query_time=10

# Error Logging.
log-error="DESKTOP-R05O7NE.err"

log-bin="DESKTOP-R05O7NE-bin"


server-id=1



lower_case_table_names=1

# Secure File Priv.
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads"

# The maximum amount of concurrent sessions the MySQL server will
# allow. One of these connections will be reserved for a user with
# SUPER privileges to allow the administrator to login even if the
# connection limit has been reached.
max_connections=151

# The number of open tables for all threads. Increasing this value
# increases the number of file descriptors that mysqld requires.
# Therefore you have to make sure to set the amount of open files
# allowed to at least 4096 in the variable "open-files-limit" in
# section [mysqld_safe]
table_open_cache=2000

# Maximum size for internal (in-memory) temporary tables. If a table
# grows larger than this value, it is automatically converted to disk
# based table This limitation is for a single table. There can be many
# of them.
tmp_table_size=83M

# How many threads we should keep in a cache for reuse. When a client
# disconnects, the client's threads are put in the cache if there aren't
# more than thread_cache_size threads from before.  This greatly reduces
# the amount of thread creations needed if you have a lot of new
# connections. (Normally this doesn't give a notable performance
# improvement if you have a good thread implementation.)
thread_cache_size=10

#*** MyISAM Specific options
# The maximum size of the temporary file MySQL is allowed to use while
# recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE.
# If the file-size would be bigger than this, the index will be created
# through the key cache (which is slower).
myisam_max_sort_file_size=100G

# The size of the buffer that is allocated when sorting MyISAM indexes
# during a REPAIR TABLE or when creating indexes with CREATE INDEX
# or ALTER TABLE.
myisam_sort_buffer_size=157M

# Size of the Key Buffer, used to cache index blocks for MyISAM tables.
# Do not set it larger than 30% of your available memory, as some memory
# is also required by the OS to cache rows. Even if you're not using
# MyISAM tables, you should still set it to 8-64M as it will also be
# used for internal temporary disk tables.
key_buffer_size=8M

# Size of the buffer used for doing full table scans of MyISAM tables.
# Allocated per thread, if a full scan is needed.
read_buffer_size=64K

read_rnd_buffer_size=256K

#*** INNODB Specific options ***
# innodb_data_home_dir=

# Use this option if you have a MySQL server with InnoDB support enabled
# but you do not plan to use it. This will save memory and disk space
# and speed up some things.
# skip-innodb

# If set to 1, InnoDB will flush (fsync) the transaction logs to the
# disk at each commit, which offers full ACID behavior. If you are
# willing to compromise this safety, and you are running small
# transactions, you may set this to 0 or 2 to reduce disk I/O to the
# logs. Value 0 means that the log is only written to the log file and
# the log file flushed to disk approximately once per second. Value 2
# means the log is written to the log file at each commit, but the log
# file is only flushed to disk approximately once per second.
innodb_flush_log_at_trx_commit=1

# The size of the buffer InnoDB uses for buffering log data. As soon as
# it is full, InnoDB will have to flush it to disk. As it is flushed
# once per second anyway, it does not make sense to have it very large
# (even with long transactions).
innodb_log_buffer_size=1M

# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
# row data. The bigger you set this the less disk I/O is needed to
# access data in tables. On a dedicated database server you may set this
# parameter up to 80% of the machine physical memory size. Do not set it
# too large, though, because competition of the physical memory may
# cause paging in the operating system.  Note that on 32bit systems you
# might be limited to 2-3.5G of user level memory per process, so do not
# set it too high.
innodb_buffer_pool_size=8M

# Size of each log file in a log group. You should set the combined size
# of log files to about 25%-100% of your buffer pool size to avoid
# unneeded buffer pool flush activity on log file overwrite. However,
# note that a larger logfile size will increase the time needed for the
# recovery process.
innodb_log_file_size=48M

# Number of threads allowed inside the InnoDB kernel. The optimal value
# depends highly on the application, hardware as well as the OS
# scheduler properties. A too high value may lead to thread thrashing.
innodb_thread_concurrency=25

# The increment size (in MB) for extending the size of an auto-extend InnoDB system tablespace file when it becomes full.
innodb_autoextend_increment=64

# The number of regions that the InnoDB buffer pool is divided into.
# For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency,
# by reducing contention as different threads read and write to cached pages.
innodb_buffer_pool_instances=8

# Determines the number of threads that can enter InnoDB concurrently.
innodb_concurrency_tickets=5000

# Specifies how long in milliseconds (ms) a block inserted into the old sublist must stay there after its first access before
# it can be moved to the new sublist.
innodb_old_blocks_time=1000

# It specifies the maximum number of .ibd files that MySQL can keep open at one time. The minimum value is 10.
innodb_open_files=300

# When this variable is enabled, InnoDB updates statistics during metadata statements.
innodb_stats_on_metadata=0

# When innodb_file_per_table is enabled (the default in 5.6.6 and higher), InnoDB stores the data and indexes for each newly created table
# in a separate .ibd file, rather than in the system tablespace.
innodb_file_per_table=1

# Use the following list of values: 0 for crc32, 1 for strict_crc32, 2 for innodb, 3 for strict_innodb, 4 for none, 5 for strict_none.
innodb_checksum_algorithm=0

# The number of outstanding connection requests MySQL can have.
# This option is useful when the main MySQL thread gets many connection requests in a very short time.
# It then takes some time (although very little) for the main thread to check the connection and start a new thread.
# The back_log value indicates how many requests can be stacked during this short time before MySQL momentarily
# stops answering new requests.
# You need to increase this only if you expect a large number of connections in a short period of time.
back_log=80

# If this is set to a nonzero value, all tables are closed every flush_time seconds to free up resources and
# synchronize unflushed data to disk.
# This option is best used only on systems with minimal resources.
flush_time=0

# The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use
# indexes and thus perform full table scans.
join_buffer_size=256K

# The maximum size of one packet or any generated or intermediate string, or any parameter sent by the
# mysql_stmt_send_long_data() C API function.
max_allowed_packet=4M

# If more than this many successive connection requests from a host are interrupted without a successful connection,
# the server blocks that host from performing further connections.
max_connect_errors=100

# Changes the number of file descriptors available to mysqld.
# You should try increasing the value of this option if mysqld gives you the error "Too many open files".
open_files_limit=4161

# If you see many sort_merge_passes per second in SHOW GLOBAL STATUS output, you can consider increasing the
# sort_buffer_size value to speed up ORDER BY or GROUP BY operations that cannot be improved with query optimization
# or improved indexing.
sort_buffer_size=256K

# The number of table definitions (from .frm files) that can be stored in the definition cache.
# If you use a large number of tables, you can create a large table definition cache to speed up opening of tables.
# The table definition cache takes less space and does not use file descriptors, unlike the normal table cache.
# The minimum and default values are both 400.
table_definition_cache=1400

# Specify the maximum size of a row-based binary log event, in bytes.
# Rows are grouped into events smaller than this size if possible. The value should be a multiple of 256.
binlog_row_event_max_size=8K

# If the value of this variable is greater than 0, a replica synchronizes its master.info file to disk.

# If the value of this variable is greater than 0, the MySQL server synchronizes its relay log to disk.
# (using fdatasync()) after every sync_relay_log writes to the relay log.
sync_relay_log=10000

# If the value of this variable is greater than 0, a replica synchronizes its relay-log.info file to disk.
# (using fdatasync()) after every sync_relay_log_info transactions.
sync_relay_log_info=10000

# Load mysql plugins at start."plugin_x ; plugin_y".
# plugin_load

# The TCP/IP Port the MySQL Server X Protocol will listen on.
loose_mysqlx_port=33060

💓 日志文件

  • 错误文件 (error log)
  • 二进制文件 (bin log)
  • 慢查询日志 (slow query log)
  • 查询日志 (log)

错误日志

错误日志文件时对 mysql 的启动、运行、关闭过程进行了记录。
当出现MYSQL 数据库不能正常启动时,第一时间必须查找的文件就是错误日志文件。该文件记录了错误信息。

慢查询日志

慢查询日志可以定位可能存在问题的SQL日志,从而进行SQL层面的优化。可以在Mysql 启动时设置一个阈值,将运行时间超过该值的所有语句都记录到慢查询日志文件中。该阈值可以通过 long_query_time来设置,默认值是10。但运行时间正好等于阈值的情况下并不会被记录下

查询日志

查询日志巨鹿了所有对Mysql 数据库请求的信息,无论这些请求是否得到了正确的执行

二进制日志

bin log 记录了对Mysql 数据库执行更改的所有操作,但是不包括 select 和 show 这类操作。若操作本身并没有导致数据库发生变化,那么该操作可能也会写入二进制文件
二进制文件的作用

  • 恢复: 数据库全备文件恢复
  • 复制:mysql 主从复制
  • 审计:通过二进制日志中的信息来进行审计,判断是否有对数据进行注入的攻击

Mysql 5.1 引入了 binlog_format 参数

  • statement:二进制日志文件记录的是日志的逻辑sql语句
  • ROW: 记录表的更改情况
  • MIXED:默认才作用 statement 模式,但是再某一些情况下会使用Row 模式
    1. 使用了 UUID() 、 user()、current_user()、found_rows() 、 row_count()等不确定函数
    2. 使用了INSERT DELAY 语句 5.7已不再支持
    3. 使用了用户自定义函数
    4. 使用了临时表
      在通常情况下 ,将参数binlog_format 设置为ROW ,为数据库恢复和复制带来更好的可靠性,但是会带来魏晋至文件大小的增加

💓socket文件

再unix 系统下本地连接Mysql可以采用 unix域套接字方式,这种方式需要一个套接字文件

💓pid文件

当 mysql 实例启动时,会将自己的进程ID 写入一个文件,该文件即为pid文件

💓表结构定义文件

Mysql 数据的存储是根据表进行的,每个表都会有与之对应的文件。 mysql 都有以 frm 为后缀名的文件,该文件记录了该表的表结构定义, frm还用来存放视图的定义

💓InnoDB存储引擎文件

InnoDB采用将存储的数据按照表空间 (tanlespace)进行存放的设计。
在这里插入图片描述
这里将/db/ibdata1 和 dr2/db/ibdata2 两个文件组成表空间,若两个文件位于不同的磁盘上,磁盘的负载可能被平均。因此可以提高数据库的整体性能。
设置 innodb_data_file_path 后,所有基于InnoDB的表数据都会记录到该共享表空间中, 命名规则为: 表名+.ibd。
在这里插入图片描述

重做日志文件

redo log file: InnoDB 存储引擎的数据目录下会由两个名为ib_logfile0 和 ib_logfile1 文件,他们记录了对于InnoDB存储引擎的事务日志
当数据库宕机时,InnoDB存储引擎会使用重做日志恢复宕机前的时刻,来保证数据的完整性

在这里插入图片描述


版权声明:本文为qq_44808472原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。