MySQL architecture and various file types

MySQL architecture and various file types
1. mysql Architecture

Composed of databases and database instances, it is a single-site multi-thread architecture.

Database: a collection of physical operating system files or other files. In mysql, database files can be files ending with frm, myd, myi, or ibd. When using the ndb storage engine, it is not an OS file, but a file stored in memory.

Database instance: it consists of the background process/thread of the database and a shared memory zone. The shared memory can be shared by the running background process/thread.

 

2. mysql file type

Mysql has the following types of files:

Parameter file: Where can I find the database file when the mysql instance starts and specify some initialization parameters, which define the size of a memory structure and other settings, the parameter types and scope are also introduced.

Log File: records the files written by mysql in response to certain conditions.

Socket file: The file required for logging on to the linux mysql command line window

Pid file: The process file of the mysql instance

Mysql table structure file: stores the mysql table structure definition file

Storage engine files: files that record the storage engine information.

 

 

3. parameter file my. cnf

? When the Mysql instance is started, the configuration parameter file my. cnf is read first.

? Find my. cnf location

(1): Default: mysql -- help | grep my. cnf

(2) backend process: ps-eaf | grep mysql

(3): Global Search: find/-name my. cnf

? You can use vi to directly maintain and modify the parameter values.

(1) dynamic: can be modified in real time through set

(2) static, which can only be modified in my. cnf and takes effect after restart.

 

The parameters in the Mysql parameter file can be divided into two types: dynamic Parameters and static parameters (staitic)

Dynamic parameters mean that you can modify them when the mysql instance is running. set global sort_buffer_size = 32999999. After the modification, other connections can be connected again.

The effective range is global and session.

 

 

Static description cannot be modified during the entire mysql instance operation, just like a read-only

 

 

4. Log Files

Log Files record various types of activities that affect the mysql database. Common log files include error logs, binary logs, slow query logs, full query logs, redo logs, and undo logs.

 

5. error logs

The error log records the mysql startup, running, and shutdown processes. When a problem occurs, the mysql dba should first view the error log file, which not only records the error information, some warning information and correct information are also recorded. This error log file is similar to the alert file of oracle, but it ends with an error by default. You can use show variables like 'Log _ error ';

 

The file name of the error file is the host name of the server. Of course, you can also set the path of the error log file in my. cnf:

Vim my. cnf

Log-error =/usr/local/mysql/mysqld. log

 

In the error log file, we can see some database startup information, alarm information, and error information.

 

6. slow query log slow log

 

Slow query logs record slow SQL statement information, which helps optimize SQL statements. You can set a threshold value, the running information of SQL statements whose running time exceeds this threshold is recorded in the slow log. This threshold value can be set through long_query_time or in milliseconds:

 

Note that if the running time is equal to the threshold value, it will not be recorded.

 

Another parameter is log_queries_not_using_indexes. If the running SQL statement does not use an index, it is recorded in the slow query log as long as the threshold value is exceeded.

 

Long_query_time = 0 (all SQL statements can be audited). dba can use this audit to promote business development and know which businesses are well performed and which ones are not well performed, through slow SQL, you can analyze which applications require poor performance optimization and improvement. The greatest function and contribution of dba is to promote business development and progress through database maintenance. From data to business, this is the direction we need to work on.

 

 

Slow query logs can also be recorded in tables,

Slow_log table, you can also put slow query logs into a table

Show variables like 'Log _ output'; Check that files are stored in slow log, and tables are stored in slow_log.

 

 

7. Full log query

All request information of the mysql database is recorded. no matter whether the request information is correctly executed, the default file name is host name. log. You can see the request for access denied.

 

Database Audit + troubleshooting (loss of 3%-5% performance)

 

 

8. binary log

The changes to the database are recorded, but the select and show operations are not included, because these operations have not been modified for the database itself. If you want to record select and show operations, you need to view the preceding full query log. In addition, the binlog also includes information such as the operation time and execution time for modifying the database.

Binary has the following two main functions:

(1): Restore the recovery. The recovery of some data requires binary logs. After the full database file is restored, you can use the binary log to recover point-to-time data.

(2): replication ). The principle is similar to restoration. By copying and executing binary logs, a remote mysql database (slave) is synchronized in real time on a mysql database (master.

 

By setting log-bin =/home/data/mysql/binlog/mysql-bin.log in my. cnf take effect, the default is under the data Directory datadir

 

Binlog log parameters:

Max_binlog_size: specifies the maximum value of a single binary file. If this value is exceeded, a new log file with the suffix + 1 will be generated and recorded in the. index file. The default value is 1 GB. However, 64 MB is a general size setting in the dba's life.

 

Binlog_cache_size:

When using the innodb Storage engine, all uncommitted binary logs that have not been submitted will be recorded in a cache. when the transaction is committed, committed will directly write the binary logs in the buffer into the binary log file, the buffer size is determined by binlog_cache_size. This buffer is based on the session, that is, mysql allocates a binlog_cache_size cache when every thread needs a transaction, therefore, you must be very careful when setting the value to avoid memory overflow.

 

Sync_binlog:

Sync_binlog = N, introduced in parameter optimization, probably means that each write buffer is synchronized to the disk file N times. If N is set to 1, it is written to the binlog disk file every time, this is the safest and safest. If N> 1, when an accident occurs, it means there will be a N-1 dml not written into the binlog, active data inconsistency may occur.

 

Binlog-do-db and binlog-ingore-db:

Indicates the database logs to be written or ignored. The default value is null, indicating that logs of all databases can be written to binary files.

 

Log-slave-update:

Enable the slave log function on the slave server so that this computer can be used to form an image chain (A-> B-> C) and generate binary log files from the database, attach another slave database to the slave database.

 

Binlog-format: log format

Statement, row, and mixed formats

Statement: Each SQL Statement that modifies data is recorded in the binlog.

Advantages:You do not need to record the changes of each row, which reduces the binlog volume, saves IO, and improves performance. (The performance and log volume can be reduced compared to the row, which depends on the application's SQL condition. The log volume generated by modifying the same record normally or inserting the row format is smaller than the log volume generated by Statement, however, considering the conditional update operations, as well as the entire table deletion and alter TABLE operations, the ROW format will generate a large number of logs, therefore, when considering whether to use the ROW format log, it should be based on the actual situation of the application, the amount of logs generated will increase, and the IO performance problems .)

Disadvantages:Because only execution statements are recorded, in order that these statements can run correctly on slave, some related information of each statement must be recorded during execution, to ensure that all the statements can get the same results as the execution on the master end in slave. In addition, mysql replication, such as some specific function functions, slave can be consistent with the master, there will be many related problems (such as sleep () function, last_insert_id (), and user-definedfunctions (udf) problems ).

2. Row:The context information of the SQL statement is not recorded. Only the record that is saved is modified.

Advantages:Binlog does not record the context-related information of the executed SQL statement. You only need to record what the record is modified. Therefore, rowlevel logs Clearly record the details of each row of data modification. In addition, there will be no problems in certain situations where stored procedures, functions, trigger calls and triggers cannot be correctly copied.

Disadvantages:When all the executed statements are recorded in the log, they are recorded with modifications recorded in each line. This may produce a large amount of log content, such as an update statement, if you modify multiple records, each modification to the binlog will be recorded, resulting in a large amount of binlog logs. Especially when you execute statements such as altertable, because of the table structure modification, if each record changes, each record in the table is recorded in the log.

3. Mixedlevel:The preceding two levels are used in combination. For general statement modification, binlog is saved in statment format. For example, if statement cannot complete master-slave replication, binlog is saved in row format, mySQL will differentiate the log format of the Record Based on each specific SQL Statement executed, that is, select one between Statement and Row. the rowlevel mode of the new MySQL squadron has also been optimized. Not all modifications are recorded in rowlevel mode, as shown in statement mode when the table structure is changed. For statements that modify data such as update or delete, changes to all rows are recorded.

 

The statements using the following functions cannot be copied:

* LOAD_FILE ()

* UUID ()

* USER ()

* FOUND_ROWS ()

* SYSDATE () (unless the -- sysdate-is-now option is enabled at startup)

At the same time, INSERT... SELECT will generate more row-level locks than RBR.

 

Row and mixed

 

9. socket File

In Linux, local connection to mysql can adopt the linux domain socket method. A socket is required to send a file, which can be controlled by the parameter socket. Generally, it is in the/tmp directory by default, you can also view it in the following two ways:

1, ps-eaf | grep mysql | grep socket

[Root @ data01 binlog] # ps-eaf | grep mysql | grep socket

Mysql 3152 1979 0 Feb28? 00:00:02/usr/local/mysql/bin/mysqld -- basedir =/usr/local/mysql -- datadir =/home/data/mysql/data -- plugin-dir =/usr/local /mysql/lib/plugin -- user = mysql -- log-error =/usr/local/mysql/mysqld. log -- open-files-limit = 8192 -- pid-file =/usr/local/mysql/mysqld. pid -- socket =/usr/local/mysql. sock -- Ports = 3306

[Root @ data01 binlog] #

2,

3, my. cnf

Socket =/usr/local/mysql. sock

 

10. pid File

When the mysql instance is started, its process id is written into a file, which is a pid file controlled by the pid_file parameter. The default path is located in the database directory, you can view it in the following three ways:

1) show variableslike 'pid _ file ';

 

2) ps-eaf | grepmysql | grep pid

 

3) My. cnf (pid-file =/usr/local/mysql/mysqld. pid)

 

11. Table Structure File

*. Frm

*. Ibd

 

12. innodb Storage files

The innodb Storage engine imitates oracle in terms of storage design. This file is the default tablespace file and can be set by the innodb_data_file_path parameter. The format is as follows:

Innodb_data_file_path = IBdata1: 128 M; IBdata2: 128 M: autoextend

 

You can use multiple files to form a tablespace and define the attributes of the file,

IBdata1 and IBdata2 are on different disk groups, which can improve the performance to a certain extent. The attributes after the file indicate the file size, and autoextend indicates that the file can be expanded.

However, if innodb_file_per_table is set to true, the table data file will be in a separate. ibd file, not in this ibdata file.

 

13. redo File

All databases use logs first. logs are written first and data files are written. Therefore, the redo log Rules are available.

 

By default, two files are named ib_logfile0 and ib_logfile1 respectively. The two files can be seen in the mysql database Directory, which is very important to the innodb Storage engine, because they record transaction logs for the innodb Storage engine.

 

The main purpose of redo log files is to redo the logs in case of media failure in the instance or media failure. If the instance fails due to power failure on the host, the innodb Storage engine restores the redo logs to the time before power loss to ensure data integrity.

 

Each innodb Storage engine must have at least one redo log group, with at least two redo log files in each group, such as the default ib_logfile0 and ib_logfile1. To achieve higher reliability, you can set multiple groups or place each group on different disks to improve performance.

 

LSN logsequence number:

The incremental generation can uniquely mark a redo log, which is very important for database fault recovery. It can uniquely locate the database running status. As for how to locate details, you can check the source code of redo and undo. The source code is in "storage/innobase/include/log0log. h"

 

 

View parameter settings: show variables like 'innodb % log % ';

 

 

14. undo logs

In the shared tablespace ibdata1, there is a rollback segment address that stores the header information, configuration header information, and segment header information, which stores data update operations opposite to redo, if rollback is used, the data in the undo segment is written back to the data file.

 

If an independent tablespace is used, it is directly stored in the table's private space instead of in the shared tablespace. In the innodb Storage engine, the undo log is used to perform transaction rollback and MVCC functions.

 

Redo and undo are not independent of each other. They are associated and work together to ensure data consistency and security,