MySQL Learning notes-database files

Database Files

MySQL main file types are as follows

Parameter file: When the My.cnf--mysql instance starts, where the database file can be found, and specifies some initialization parameters that define settings such as the size of a memory structure, the type of the parameter, and the scope of the definition;

Log files: Files that are written when MySQL responds to a condition, including error logs, query logs, slow query logs, binary logs;

MySQL table structure file: the MySQL table structure definition file, regardless of the storage engine used, MySQL has a file with the name of the frm suffix;

Socket file: Files needed when logging in with Linux MySQL command line window;

PID file: The process ID file of the MySQL instance;

Storage Engine files: Each storage engine has its own folder to hold a variety of data, and these storage engines really store data such as data and indexes.

Parameter file

When the MySQL instance starts, MySQL reads a configuration parameter file that is used to locate the various files in the database and to specify some initialization parameters that typically define how large a memory structure is. By default, MySQL instances are taken in a certain order, and can be viewed from the command line to the location of the my.cnf file;

 
   
  

  
    
   
mysql --help | grep my.cnf
 
   
  

MySQL may not need a parameter file at startup, but if the MySQL schema is not found in the default database directory, the startup will fail;

MySQL parameters can be viewed through "show variables", since the mysql5.1 version can be found through the Global_variables view under the INFORMATION_SCHEMA schema, so you can also view

 
   
  

  
    
   
* From information_schema . Global_variables ;

 
   
  

Mysql parameter type:It is divided into dynamic and static parameters (static).
dynamic parameters mean that changes can be made in the MySQL instance runtime, some parameter modifications can be either session-based or life-cycle based on the entire instance;
The static parameter description must not be changed throughout the instance declaration cycle, as if it were read only. Mysql5.1 's dynamic parameters are here.


log Filein general, log files are stored in the following path:

• Error log
  

The file forMysqlstart, run, shutdown process is recorded, in the case of problems, should first look at this file, you can through the "Show variables like ' log_error ';" To locate the file.

• Slow query log
  

This file records all SQL statements that have elapsed time exceeding the threshold value, which can be set by parameter long_query_time. The default value is 10 seconds.

 
   
  

  
    
   
show variables like ‘long_query_time‘;
 
   
  

However, by default,Mysqldo not start full query log, you need to manually modify this parameter;

 
   
  

  
    
   
show variables like ‘log_slow_queries‘;
 
   
  

 
two things to keep in mind:first, the slow query log records a SQL statement that is larger than the threshold value, not greater than or equal to!!! second, fromMysqlstarting at 5.1, Long_quey_time begins to record the SQL statement run time in microseconds.
another parameter that is related to the slow query is Log_queries_not_using_index, and if the SQL statement that is running does not use an index, theMysqlThe SQL statement will also be recorded in the slow query log file;

 
   
  

  
    
   
show variables like ‘log_queries_not_using_indexes‘;
 
   
  

 
when the slow query log file grows, consider using the tools Mysqldumpslow tool to help us analyze. The use of the tool you can use "man mysqldumpslow" or go here to understand. Mysql5.1 start to put the log records for the slow query into a table in the Mysql.slow_log table. Whether it is placed in the table is determined by the parameter ' Log_output ';
parameter log_output Specifies the format of the slow query output, the default is file, you can also set it to table, you can go to Mysql.slow_log query.

 
   
  

  
    
   
show variables like ‘log_output‘;
 
   
  

It is also possible to put records of query logs into the General_log table under the MySQL schema.

• Query log
  

The query log records all the information requested by the MySQL database, regardless of whether the requests were executed correctly. The default file name is: Host name. Log. We look at a query log:
4, binary log because the binary log is too important to mention here for the moment. InnoDB Storage Engine Files

From for notes (Wiz)

MySQL Learning notes-database files