1. Relationship between databases and file systems

Storage engines like InnoDB and MyISAM store tables on file systems. When we want to read data, these storage engines will read data from the file system and send it back to us, and when we want to write data, these storage engines will write the data back to the file system.

2.MySQL data directory

Unlike the MySQL installation directory, which stores many commands that control the server and client programs, the data directory is used to store the data generated during the running of MySQL

2.1 Finding the location of the MySQL Data directory

Because the data directory is recorded in the system variable ‘datadir’, enter the command directly to find it

mysql> SHOW VARIABLES LIKE 'datadir';
Copy the code

2.2 Data Directory Structure

Given the location of the data directory, the database we create will generate a subdirectory of the same name under this folder and create a file named db.opt under this subdirectory, which contains various properties of the database, such as character sets/comparison rules, etc

The figure below shows the contents of the database tCC-CAP directory, which contains the definition of the table structure and the table data

2.3 Representation of InnoDB tables in file systems

For data tables of InnoDB storage engine, one table corresponds to two files, one is *. FRM, which stores table structure information; One is *.ibd, which stores data in a table.

2.4 Storing table Data

InnoDB data is based on pages. To better manage these pages, MySQL has introduced table Spaces that correspond to one or more real files on the file system. Each table space can be divided into many, many, many pages, and our table data is stored in certain pages of a certain table space.

2.4.1 System tablespaces

By default, InnoDB creates a 12MB file named IBData1 in the data directory. This file represents the corresponding system tablespace on the file system.

In a MySQL server, there is only one system tablespace. In each version from MySQL5.5.7 to MySQL5.6.6, the data in our table is stored in this system table space by default.

2.4.2 Independent tablespace

In MySQL5.6.6 and later, InnoDB does not store the data of individual tables in the system table space by default. Instead, InnoDB creates a separate table space for each table, meaning that we create as many separate table Spaces as possible. An independent table space is used to store table data. A file representing the independent table space is created in the subdirectory of the database to which the table belongs. The file name is the same as the table name, but an. Ibd extension is added

2.5 Representation of MyISAM tables in file Systems

Because the storage engine’s data and indexes are kept separate. So the file system also uses different files to store data files and index files. And unlike InnoDB, MyISAM does not have a table space. Table data is stored in the corresponding database subdirectory.

Therefore, MyISAM requires one more file than innoDB, and the file name suffix is different from innoDB:

  • test.frm
  • test.MYD
  • test.MYI

2.6 View Representation in a File System

Because a view is actually a virtual table with no actual data, only a *.frm file is stored in the corresponding database subdirectory.

2.7 Other files in the Data directory

  • Server process file.

We know that every time we run a MySQL server program, we start a process. The MySQL server writes its process ID to a file.

  • Server log files.

During server running, various logs are generated and need to be stored.

  • Default/automatically generated SSL and RSA certificates and key files.

3. Impact of file systems on databases

  • The file name length is affected

A file generated by the database whose file name is limited to the maximum length supported by the file system

  • File size impact

For files in the database, they are limited by the maximum file size supported by the file system

  • The impact of special characters

To prevent the file system from not supporting special characters in database and table names, MySQL maps all characters in database and table names except numbers and Latin letters to @+ encoded values in file names.

4.MySQL database

MySQL will have several system databases by default

  1. information_schema
  • Information_schema provides access to database metadata. Metadata is data about data, such as database or table names, column data types, or access rights. Other terms sometimes used to express this information include “data dictionary” and “system catalog.”)
  • In other words, information_Schema is an information database that holds information about all other databases maintained by the MySQL server. (Such as database name, database table, table column data type and access limits, etc.) Within INFORMATION_SCHEMA, there are several read-only tables. They are actually views, not base tables.
  • View the specific table:

  1. mysql
  • The mysql core database, similar to the master table in SQL Server, is mainly responsible for storing database users, permission Settings, keywords and other control and management information needed by mysql itself. Change the password of user root in the mysql. User table.
  1. performance_schema
  • Collect database server performance parameters. In addition, the storage engine of the tables in the database is PERFORMANCE_SCHEMA, but users cannot create tables with PERFORMANCE_SCHEMA. MySQL5.7 is enabled by default.
  1. sys
  • All data sources for the Sys library come from: performance_schema. The goal is to reduce the complexity of performance_schema so that DBAs can better read the content in the library. Let the DBA know the DB running status faster.