Shulou(Shulou.com)06/01 Report--

The following together to understand the composition of the MYSQL database physical file which parts, I believe you will benefit a lot after reading, the text in the essence is not much, hope to form the MYSQL database physical file which part of this short content is what you want.

Physical file composition

Log files: error log, query log, slow query log, transaction log, binary log

Logs are an essential part of the mysql database. The log file records the changes that occurred during the operation of the Heng mysql database, that is, it is used to record the client connection status of the mysql database, the execution of SQL statements and error messages.

1. Error log (error log)

Error logging is enabled by default and is stored in the data directory of the mysql database.

The error log file is usually named hostname.err hostname to indicate the host name of the CVM.

Error log information can also be configured on its own, through log-error, which defines the storage location, and log-warnings, which defines whether warnings are also defined in the error log.

Note that the initial password is saved in the error log when MySQL 5.7 is installed for the first time

two。 Binary file (binlog)

Used to record mysql statements that modify data or that may cause data changes, and record the time it takes to execute the statement, the data of the operation, etc.

Can be queried through show global variables like'% log_bin%';

Add the log-bin=file_name open record function to the main configuration file

MYSQL records all the query that modifies the database data into the log file in binary form. The log file also includes the time spent by each query, the resources consumed, and related transaction information.

Additional option parameters for binlog

"max_binlog_size" sets the maximum storage limit for binlog, which is generally set to 512m or 1G. When the log reaches the upper limit, the mysql will recreate a log and continue recording.

Binlog-do-db=db_name "record the binlog to a db_name (database)

If the "binlog-do-db=db_name" parameter is explicitly specified, MySQL ignores the query executed against other databases and records only the query executed against the specified database

"binlog-ignore-db=db_name" is the exact opposite of "binlog-do-db=db_name". It explicitly specifies that the binlog record of a (db_name) database is ignored, and when this parameter is specified, MySQL records the binlog of all databases other than the specified database.

After starting the binlog record, a mysql-bin.index file is also generated in the mysql database directory

The main function is to record the absolute paths of all Binary Log to ensure that various threads of MySQL can successfully find all the required Binary Log files according to it.

There are three main ways of MYSQL replication:

Replication based on SQL statement (statement-based replication, SBR)

Row-based replication (row-based replication, RBR)

Mixed mode replication (mixed-based replication, MBR)

Correspondingly, there are three formats of binlog: STATEMENT, ROW, and MIXED.

Statenment mode (SBR)

Every sql statement that modifies the data is recorded in binlog. The advantage is that there is no need to record the data changes of each row.

Reduce the number of binlog logs, save IO, and improve performance. The disadvantage is that the data in master-slave is inconsistent in some cases.

Row mode (RBR)

Do not record the information of each SQL statement, but only record that the data has been modified and changed into what it looks like. The disadvantage is that a large number of logs will be generated and the logs will soar.

Mixed mode (MBR)

Mixed mode, the above two modes are mixed. General replication uses statement mode to save binlog, and for operations that cannot be copied in statement mode, using row mode to save binlog,mysql will determine when to use it.

"sync_binlog=10" sets how often the binary log is synchronized to the disk file. 0 means out of sync, 1 means every statement is synchronized.

Several methods of querying binary log information

Show binlog events\ G; view all binary information

Show bin log events in'mysql-bin.000001'\ G; view the specified binary information

Show binlog events in 'mysql-bin.000001'from 727; start at the specified event location

Mysqlbinlog mysql-bin.00001 is viewed under the command line

Several methods of deleting binary log information

Purge binary logs to 'mysql-bin.000006'; deletes binaries before' mysql-bin.000006'

Reset master deletes all binaries and regenerates a binary file

3. Transaction log

Transaction logs (InnoDB-specific logs) can help improve the efficiency of transactions.

Using the transaction log, the storage engine only needs to modify the memory copy of the table when it modifies the data, and then records the modification behavior in the transaction log on the hard disk, instead of persisting the modified data to the disk every time. The transaction log is appended, so the operation of writing the log is the sequential IPUP O in a small area of the disk, unlike the random IUnip O, which needs to move the head in multiple places on the disk, so using the transaction log method is relatively faster.

After the transaction log is persistent, the modified data in memory can be slowly brushed back to disk in the background.

4. Slow query log

The slow query log records the query with a long execution time, which is easy to view in a simple text format.

Through the slow query log, you can find out which query statements are inefficient for optimization.

It is also added to the main configuration

"slow_query_log=1" is on

"slow_query_log_file=/usr/local/mysql/data/mysqld-slow.log" storage location

The time that "long_query_time=1" needs to record

Or define it directly in the database.

Analysis log

Enter the slow query log storage directory and use mysqldumpslow analysis

"mysqldumpslow mysqld-slow.log"

After reading this article about the physical files of MYSQL database, many readers will want to know more about it. For more industry information, please follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.