MySQL error log, binary log, slow query log, transaction log

Error log:

By default, the error log probably records the following areas of information:

1. Information during server startup and shutdown (not necessarily error messages, for example, how MySQL launches InnoDB tablespace files, how to initialize its own storage engine, etc.)

2. Error messages during server operation

3. Information generated when the event Scheduler runs an event

4. Information generated when starting the server process from the server

Attention:

1, according to their own needs to set different error log values

1= log only at Errors level

2= logging of errors, warnings levels

3= logging of errors, warnings, notes (defaults) levels

2. How to delete the old error log

Before mysql5.7: The database administrator can delete the error log for a long time to ensure hard disk space on the MySQL server. MySQL database, you can use the Mysqladmin command to open a new error log:

The command syntax is as follows: Mysqladmin-u root-p Flush_logs can also log in to the MySQL database using the Flush logs statement to open the new error log

After 5.7: The server will turn off this feature. You can only use the Rename original error log file to manually flush the log to create a new

The method is as follows: MV Mysqld.err Mysqld.err.old

Binary logs: Binary log & binary log Index

The binary log, which we often call the Binlog log, is also one of the most important logs in MySQL server, used primarily to record modified data or MySQL statements that may cause data changes, and to record:

1. Time of statement occurrence

2. Length of execution

3, the operation of the data

4, etc.

So the binary log allows you to query the MySQL database for those changes.

General volume limit is 1G

When we open the function of the record through "Log-bin=file_name", MySQL logs all query that modifies the database data to the log file in binary form. Of course, not only is the query statement so simple in the log, it also includes the time that each query executes, the resources it consumes, and the related transaction information, so Binlog is transaction-safe.

As with the error log, the Binlog logging function also requires the display of the "bin-log=file_name" parameter to be enabled, and if no file_name is specified, it is recorded in the Data directory as mysql-bin.* (* represents a number between 0~9, To indicate the ordinal of the log)

Bin-log There are some additional option parameters:

Max_binlog_size: To set the maximum storage limit of binlog, usually set to 512M or 1G, generally not more than 1G, when the log reaches that limit, MySQL will re-create a journal to continue recording. Occasionally, however, there are more binlog than this setting, generally because a larger transaction is generated when the upper limit is reached and MySQL does not log the same transaction to two binlog in order to ensure the security of the transaction.

Binlog-do-db=db_name: If the display of this parameter is specified, MySQL ignores the query executed against the other database and simply logs the query executed against the specified database.

"Binlog_ignore_db=db_name" is the exact opposite of "Binlog-do-db=db_name", and he displays a binlog record that specifies that a database is ignored, and when this parameter is specified, MySQL records the binlog of all databases outside the specified database.

There is a common concept of "binlog-do-db=db_name" and "Binlog_ignore_db=db_name" two parameters:

The db_name in the parameter is not the database in which the value query statement was updated, but the database currently in which query was executed. Regardless of updating the data for that database, MySQL simply compares the database in which the current connection is located (the database that was switched on using the use db_name) with the same name as the database set by the parameter, without parsing the database in which the query statement was updated.

The function of the Mysql_bin.index file (binary log index) is to record the absolute path of all binary logs, ensuring that all MySQL threads are able to successfully find all the required binary log files.

bin_cache_size=32768 (default)

Bin_cache_size: A transaction that, when not committed (uncommitted), logs to the cache: when the transaction commit (committed) needs to be committed, the log is persisted to disk. In general, if there are no large transactions in our database, writing is not particularly frequent, 2MB~4MB is a suitable choice. However, if our database large transaction, write volume is large, can be appropriately adjusted high binlog_cache_size. At the same time, we can use Binlog_cache_use and binlog_cache_disk_use to analyze whether the set of binlog_cache_size is sufficient, whether there is a large number of Binlog_ Cache is cached with temporary files due to insufficient memory size

binlog_stmt_cache_size=32768 #当非事务语句使用二进制日志缓存, but when the binlog_stmt_cache_size is exceeded, a temporary file is used to store the statements.

binlog-format={row| Statement| MIXED} #指定二进制日志的类型, default is MIXED.

1. Statement mode (SBR)

Each SQL statement that modifies the data is recorded in the Binlog.

The advantage is that there is no need to record data changes per row, reduce Binlog day quality, save Io, improve performance,

The disadvantage is that in some cases the data in Master-slave is inconsistent (such as the Sleep () function, last__insert_id (), and User-defind Functions (UDF), and so on)

2. Row mode (RBR)

Do not log the information of each SQL statement, only need to record that data has been modified, how to change the

The disadvantage is that a large number of logs are generated, causing the log to skyrocket.

3. Mixed Mode (MBR)

Mixed use of the above two modes, general replication using the statement mode to save the Binlog, for statement mode cannot replicate operations using row mode save Binlog,mysql will choose the log save method according to the SQL statement executed. That is, the use of alternating lines and statements, the MySQL server self-judgment.

The row-based definition of the data size is larger but can guarantee the accuracy of the data.

Sync_binlog=10 #设定多久同步一次二进制日志至磁盘文件中, 0 means no synchronization, and any positive value indicates that there are not many write operations to the binary after synchronization. When the value of autocommit is momentary, every statement execution causes binary log synchronization, otherwise the commit of each transaction causes synchronization of the binary log.

Max_binlog_cache_size #二进制日志缓存空间大小, 5.5.9 that later versions are only used for transaction caching, which is determined by the max_stmt_cache_size on-line.

expire_log_days={0,99} #设定二进制日志日志的过期天数, binary logs that exceed the number of days will be automatically deleted, with a default of 0, which means that expired auto-deletion is not enabled. If this feature is enabled, automatic removal usually occurs at MySQL startup or flush logs.

Current binary files and where they are located

Show binary logs;

viewing binary file information

Show master status;

View all binary information

Show Binlog Event\g

View binary information for a specified log

Show Binlog events in ' mysql-bin.* ' \g

* * View binary logs under command line

Mysqlbinlog "Binlog_name"

Delete binary log information

Purge {Binary|master} logs {to ' log_name ' | Before datetime_expr}

Example: Purge binary logs to ' mysql-bin.000006 '

Delete all binary logs (with caution)

Reset Master;

It is not recommended to use this operation in a production environment

Transaction log: (or called redo log)

Transaction logs (InnoDB-specific logs) can help improve the efficiency of transactions. With transaction logs, the storage engine modifies the table's data only by modifying its memory copy, and then logging the modification behavior to the transaction log that is persisted on the hard disk, rather than persisting the modified data to the hard disk each time. The transaction log is appended, so the log operation is sequential I/O in a small area of the disk, rather than the need to move the heads in multiple places, like the random I/O, so the transaction log is relatively fast in the way it is used. After the transaction log is persisted, the in-memory modified data can slowly be brushed back to disk in the background. Most of the storage engines are now implemented in this way.

If a transaction's modification has been logged and persisted to the transaction log, the single data itself has not been written to disk, and the system crashes, and the storage engine restarts automatically to recover this part of the modified data. The recovery method you have is dependent on the storage engine.

See what search engines MySQL has to offer

Show engines

View MySQL's current default storage engine

Show variables like '%storage_engine% '

See what engine a table uses

Show create teble table name;

Changing the table's storage engine

CREATE table library name. Indicates engine= "InnoDB"

View the definition of a transaction log

Show global variables like ' innodb_flush_log_at% '

#在事务提交时innodb是否同步日志从缓冲区到文件中,

When this value is 1 (the default), when each transaction commits, the log is written to the log file, the log file is refreshed with disk operations, and the performance is poor, resulting in a lot of disk I/O, but this approach is safest:

If set to 2, each commit transaction is written to the log, but does not perform a brush operation. Timed to log files per second is not guaranteed to be flushed to disk 100% per second, depending on the scheduling of the process.

Set to 0, the day to the buffer is written to the log file once per second, and the log file is refreshed with disk operations, but no action is taken on a thing commit.

* * The concept of brush writing

Brush writing is actually two operations, brush (flush) and write (writing), it is important to distinguish between the two concepts. In most operating systems, the InnoDB log buffer (memory) is written to the log (called by the system to call write), simply to transfer the data to the operating system cache, the operating system cache also refers to memory. There is no persisted data.

So at 0 and 2, the last second is lost when a crash or power outage occurs because the data is only present in the operating system cache. It is generally possible to lose more than one second of data, such as blocking when performing a flush operation.

Summarize:

Set to 1 is of course the safest, but the performance is also the worst (compared to the other two parameters, but not unacceptable). If the data consistency and integrity requirements are not high, can be set to 2, if only demand performance, such as high concurrent write log server, set to zero for higher performance.

Slow query log: Slow query log

As the name implies, the slow query log is a long execution of query, which is what we often call slow query. The slow query log is a simple text format that allows you to view the content in a variety of text editors. It records the moment of execution, the time spent executing, executing the user, connecting the host and other related information.

The role of the slow query log:

The slow query log is used to record a long execution query, which is what we often call slow query.

Through the slow query log can be found out those query statement execution efficiency is low, in order to optimize, generally recommended to open, he has little impact on server performance, but can record the MySQL server on the execution of a long time query statement. can help us locate performance issues. MySQL also provides utility Mysqldumpslow, which is designed to analyze slow query logs, to help database managers resolve possible performance issues.

To view the definition of a slow query log

Show global variables like '%slow_query_log% '

Start and set the slow query log

Method One: Open the slow query log via MY.CNF

Method Two: Login MySQL server direct settings (using set_query_log=1)

Mostly versions can use show variables like '%slow% ' or show variables like '%long% '

which

Slow_query_log;off off state, on open state

Slow_query_log_file: Slow query log storage location

Long_query_time: Set the time value, the time in seconds, can be accurate to microseconds, if the time exceeded (the default is 10 seconds) This query statement will be recorded in the slow query log. When set to 0, indicates that all queries are logged.

Note:

1, if you do not specify a path, the default is stored in the MySQL database data file, if you do not specify a filename, the default file name is Hostname-slow.log

2. Turn Unix time into a readable time, you can use the timestamp in the date [email protected] Log

3. Mysqldumpslow option parameter

-S is the way to indicate how to sort by, c,t,l,r are sorted according to the number of records, time, query time, returned records, ac,at,al,ar the corresponding reverse.

-T, which is the meaning of top N, which is to return the data of the previous number of bars

-G, the following can write a regular expression, case-insensitive

File type: the

1. frm file

Table-related metadata (meta) information is stored in the ". frm" file, including the definition information for the table structure. Regardless of what storage engine is dedicated to the storage engine, each table will have a. frm file named after the table. Stored under the folder of the owning database

2. MyISAM database Table file:. myd file: Table data file. MYI: Table Index file

3. InnoDB uses tablespace (tablespace) to manage data, store table data and indexes

. ibd Files: But table tablespace files, each table uses a tablespace file (file per table) that holds user database table data and indexes

InnoDB shared tablespace (that is, InnoDB file set, Ib-file set): Ibdata1, IBDATA2, etc., stores InnoDB system Information and user database table data and indexes, all tables are shared

4. ibd Files and Ibdata files

Both files are files that hold InnoDB data, and there are two types of files that hold InnoDB data, including indexes, because InnoDB's data storage can be configured to determine whether to use shared tablespace to store data or to store it in a single-table space.

Exclusive table space:. IBD

Shared table spaces:. ibdata

The Ibdata file can be

Innodb_data_file_path The total directory where the configuration data is stored

You can configure more than one Idbata file at a time, the file can be specified in size, or it can be automatically extended, but only the last Ibdata file can be set to auto-expand.

A restart is required to complete the Ibdata add job

Summarize:

Shared tablespaces and exclusive tablespaces are all about how data is stored

Shared tablespace: All table data for a single database, and all index files in one file

Exclusive tablespace: Each will be generated in a separate file way for storage, and each table has a. frm table description file, and one. ibd file, where this file includes the data content of a single table and the contents of the index

The pros and cons of both:

Shared table Spaces

Advantages:

You can put a table space into multiple files and store them on each disk. Data and documents together for easy management

Disadvantages:

All data and indexes are placed in a single file, multiple tables and indexes are stored in a table space, so there will be a lot of voids in the table space after a large number of deletions for a table, especially for statistical analysis, such applications are most unsuitable for shared table space.

Stand-alone table space

Advantages:

1, each table has its own independent table space

2, the data and index of each table will exist in its own table space

3, you can implement a single table in different databases to move

4, space can be recycled

A, drop table operation automatically reclaim the table space, if for statistical analysis or daily value table, delete large amounts of data, can pass: altertable TableName engine = InnoDB; back Up unused space

b, for tables that use separate table spaces, no matter how they are deleted, the fragmentation of the tablespace does not affect performance too much, and there is a chance to process it.

Disadvantages:

Single table increase too large, such as more than 100 g

In contrast, the efficiency and performance of using exclusive table spaces is a bit higher

On represents a stand-alone table space, and off represents a shared table space

Replication (stored procedure) related files

1. master.info file

The Master.info file exists in the data directory on the slave side, which contains information about the master side of the slave, including the host address of master, connection user, connection password, connection port, current log location, and the log location information that has been read.

2. Relay log and relay log index

The Mysql-relay-bin.xxxxxn file is used to hold the binary log information read from the master side of the I/O thread on the slave side, and then the SQL thread from the slave side reads and parses the corresponding log information from the relay log. Convert to the SQL statement executed by master and then slave end application

The Mysql-relay-bin.index file functions similarly to Mysql-bin.index, which is the absolute path where the log is stored, except that she does not log the binary log relay log

3. relay-log.index file

Similar to Master.info, it holds information about the slave I/O thread writes to the local relay log, a Slave SQL thread, and some administrative operations at any time to obtain information about the current replication.

Other files:

1. system Config file

MySQL's system configuration file is generally my.cnf

2. pid File

PID files are a process file for MySQL applications in the Unix/linux environment

3. Socket file

Socket file only in the Unix/linux environment, you can directly only use the socket file to connect to MySQL, faster than TCP, but only for MySQL and application on a single PC.

Summarize:

1. View system settings

Show [global|session] variables [like_or_where]

2. Operation Status

Show [global|session] status [Like_or_where]

3. Refresh the Log

Flush Log

MySQL error log, binary log, slow query log, transaction log