preface
In the previous two articles, we learned about the overall architecture of MySQL, which is divided into four layers, including the network connection layer, core layer, storage engine layer, physical layer, and the role of each layer. We also learned about the architecture of InnoDB’s storage engine layer, including cache pools and threads.
Don’t understand, please move on two. Today we’ll look at the various files that make up the MySQL database and InnoDB storage engine tables.
Parameter file
Tells the MySQL instance where to find the database files when it starts, and specifies certain initialization parameters, such as the size Settings for certain memory structures.
What about the parameters up here? In simple terms, you can think of a database parameter as a key/value pair, such as Innodb_buffer_POOL_size =1G. So where do all these key-value pairs go? Ini contains a large number of key/value pairs stored in the MySQL installation directory, as shown below.
If we open up this file and look at it, it’s full of key-value pairs. If we want to change something, we just change it over here and restart it.
The log file
It is used to record the files that the MySQL instance writes in response to a condition.
The error log
Records error information about MySQL startup, running, and shutdown. Let’s take a look at where the file is stored.
We go to the corresponding path to view the file, you can find the error message. So when the MySQL database fails to start properly, the first file you must look for is the error log file.
Binary log
Log all operations that make changes to the MySQL database, except operations like SELECT and show, which have no effect on the data itself. However, an update or delete operation will be logged to the binary log even if it has no impact on the database.
This paragraph is not particularly difficult to say, I can not understand, ok, let’s actually operate.
First, binary log files are not started by default, and you need to manually specify parameters to start them. Does this mean that enabling this option has an impact on the overall performance of the database? However, according to tests in the official MySQL manual, enabling binary logging results in an acceptable performance drop of 1%.
We don't know if it's going to be a 1% drop, but it's going to have an impact, but it's not going to be a problem. After all, logging after every insert, DELETE, or update operation costs time and space.
So let’s start the show.
Ini contains key-value pairs of configuration information. Add log-bin to this file and specify the name of log as host name -bin, suffix as serial number, and path as database path.
Next, restart the server as follows. After the restart, we can see the corresponding files.
Finally, after we’re ready, let’s query test2 and see if the size of the two files has changed. It’s obviously the same size as before.
Write another update statement and we can see that the number of rows affected is 0, but the size of the two files has increased.
InnoDB storage engine file
Table space
InnoDB stores data by table space. In the default configuration, there is a file named ibDatA1 with an initial size of 10MB, which is the table space for all tables. You can also use innodb_file_per_table to create a separate tablespace for each table named.ibd. Familiar? Yes, this is one of the files innoDB tables are stored on the hard disk as we talked about last time.
Redo log files
If there is a power outage, InnoDB will redo the log to restore the state before the power outage to ensure data integrity.
Every InnoDB storage engine has at least one redo log group, and two files ib_logFILE0 and ib_logFILe1.
They are of the same size and run in a circular write mode, that is, write redo log 1 first, switch to redo log 2 when log 2 is full, and switch to redo log 1 when log 2 is full.
The end of the
Code word is not easy, please pay more attention to oh.
