Storage engine and log description based on MySQL (comprehensive explanation)

1.1 Introduction to storage engines

1.1.1 File system storage

File system: A mechanism used by an operating system to organize and access data. A file system is a type of software.

Type: ext2 3 4 , xfs data. No matter what file system is used, the data content will not change; what will differ is the storage space, size, and speed.

1.1.2 MySQL database storage

MySQL engine: It can be understood as the "file system" of MySQL, but with more powerful functions.

MySQL engine functions: In addition to providing basic access functions, it also has more functions such as transaction functions, locking, backup and recovery, optimization and special functions.

1.1.3 MySQL storage engine types

MySQL provides the following storage engines:

InnoDB, MyISAM (the two most commonly used)
MEMORY、ARCHIVE、FEDERATED、EXAMPLE
BLACKHOLE, MERGE, NDBCLUSTER, CSV

In addition, third-party storage engines can also be used.

1.1.4 Comparison between InnoDB and MyISAM

InnoDb Engine

Support ACID transactions and four transaction isolation levels;

Supports row-level locks and foreign key constraints: therefore, it can support concurrent writes;

The total number of rows is not stored;

An InnoDb engine is stored in one file space (shared table space, table size is not controlled by the operating system, and one table may be distributed in multiple files), or multiple files (set to independent table space, table size is limited by the operating system file size, generally 2G), which is limited by the operating system file size;

The primary key index uses a clustered index (the data field of the index stores the data file itself), and the data field of the auxiliary index stores the value of the primary key; therefore, to search for data from the auxiliary index, you need to first find the primary key value through the auxiliary index, and then access the auxiliary index; it is best to use an auto-increment primary key to prevent major adjustments to the file when inserting data to maintain the B+ tree structure.

The primary index structure of Innodb is as follows:

MyISAM Engine

Transactions are not supported, but each query is atomic;

Supports table-level locks, that is, each operation locks the entire table;

The total number of rows in the storage table;

A MYISAM table has three files: index file, table structure file, and data file;

Using a clustered index, the data field of the index file stores a pointer to the data file. The secondary index is basically the same as the primary index, but the secondary index does not need to be unique.

The primary index structure of MYISAM is as follows:

The two index data search processes are as follows:

1.2 innodb storage engine

After MySQL version 5.5, it is the default storage engine , providing high reliability and high performance.

1.2.1 Advantages of the Innodb engine

a) Transaction safety (ACID compliant)
b) MVCC (Multi-Versioning Concurrency Control)
c) InnoDB row-level locks d) Support for foreign key referential integrity constraints e) Fast automatic recovery after a failure (crash safe recovery)
f) Buffer pool (data buffer page log buffer page, undo buffer page) used to cache data and indexes in memory
g) Maximum performance on large data volumes h) Mixing queries on tables with different storage engines i) Oracle style consistent non-locking reads (shared locks)
j) Table data is organized to optimize primary key-based queries (clustered index)

1.2.2 Overview of Innodb Features

1.2.3 How to query the storage engine

SELECT @@default_storage_engine;
or show variables like '%engine%';

2. Use SHOW to confirm the storage engine of each table:

SHOW CREATE TABLE City_G
SHOW TABLE STATUS LIKE 'CountryLanguage'\G

3. Use INFORMATION_SCHEMA to confirm the storage engine of each table:

SELECT TABLE_NAME, ENGINE FROM 
INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'City'
AND TABLE_SCHEMA = 'world_innodb'\G

4. Migrate from version 5.1 to version 5.5 or above

Suppose all production tables of version 5.1 database are MyISAM.

After using mysqldump to back up, you must replace the engine field in the backup file from myisam to innodb (you can use the sed command), otherwise the migration will be meaningless.

When upgrading the database, pay attention to the compatibility of other supporting facilities and whether the code is compatible with new features.

1.2.4 Setting the Storage Engine

1. Set the server storage engine in the startup configuration file:

[mysqld]
default-storage-engine=<Storage Engine>

2. Use the SET command to set for the current client session:

SET @@storage_engine=<Storage Engine>;

3. Specify in the CREATE TABLE statement:

CREATE TABLE t (i INT) ENGINE = <Storage Engine>;

1.3 Storage Structure of InnoDB Storage Engine

1.3.1 InnoDB System Tablespace Features

By default, InnoDB metadata, undo logs, and buffers are stored in system "tablespaces".

This is a single logical storage area that can contain one or more files.

Each file can be a regular file or a raw partition.

The final file can be automatically extended.

1.3.2 Definition of tablespace

Tablespace: MySQL database storage method

The tablespace contains the data files

MySQl tablespace and data file have a 1:1 relationship

Except for shared tablespace, 1:N relationship is possible

1.3.3 Tablespace Type

1. Shared tablespace: ibdata1~ibdataN, usually 2-3

2. Independent tablespace: stored in the specified library directory, such as city.ibd in the data/world/directory

Tablespace location (datadir):

In the data/directory

1.3.4 Storage Contents of System Tablespace

Shared tablespace (physical storage structure)

ibdata1~N is usually called the system tablespace, which is generated by data initialization

System metadata, base table data, data other than table content data.

tmp tablespace (generally little attention)

Undo log: data--rollback data (used by rollback log)

Redo log: ib_logfile0~N stores some redo logs of the system's innodb table.

Note: The undo log is stored in ibdata by default and can be defined separately after 5.6.

The tmp tablespace was moved out of ibdata1 after version 5.7 and became ibtmp1

Prior to version 5.5, all application data was stored in ibdata by default.

Separate tablespace (a storage engine feature)

After 5.6, by default, each table will be stored in a separate tablespace file.

In addition to the system tablespace, InnoDB creates additional tablespaces in the database directory for .ibd files for each InnoDB table.

Each new table created by InnoDB sets up a .ibd file in the database directory to go with the table's .frm file.

This setting can be controlled using the innodb_file_per_table option, and changing it only changes the default for new tables that are created. .

1.3.5 Setting up a shared tablespace

View the current shared tablespace settings

mysql> show variables like 'innodb_data_file_path';
+----------------------+------------------------+
| Variable_name | Value |
+----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+----------------------+------------------------+
row in set (0.00 sec)

Set up a shared tablespace:

The number is usually configured when the environment is initially built, and the default value is usually 1G; and the last one is automatically expanded.

[root@db02 world]# vim /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:76M;ibdata2:100M:autoextend

Restart the service to view the current shared tablespace settings

mysql> show variables like 'innodb_data_file_path';
+----------------------+-------------------------------------+
| Variable_name | Value |
+----------------------+-------------------------------------+
| innodb_data_file_path | ibdata1:76M;ibdata2:100M:autoextend |
+----------------------+-------------------------------------+
row in set (0.00 sec)

1.3.6 Setting up an independent tablespace

Independent tablespaces are enabled by default in version 5.6.

Notes on independent tablespaces: If independent tablespaces are not opened, the shared tablespace will occupy a large area.

mysql> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
row in set (0.00 sec)

Independent tablespaces can be controlled in the parameter file /etc/my.cnf

Close independent tablespace (0 is closed, 1 is opened)

[root@db02 clsn]# vim /etc/my.cnf
[mysqld]
innodb_file_per_table=0

View independent table space configuration

mysql> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF |
+-----------------------+-------+
row in set (0.00 sec)

summary:

innodb_file_per_table=0 Close independent tablespace innodb_file_per_table=1 Open independent tablespace, single table storage

1.4 Transactions in MySQL

A set of data manipulation execution steps that are considered a unit of work

Used to group multiple statements and can be used when multiple clients concurrently access data in the same table.

All steps succeed or fail

If all steps are OK, they are executed, if steps have errors or are incomplete, they are canceled.

Simply put, a transaction is to ensure that the statements in a unit of work succeed or fail at the same time.

Transaction Processing Flow Diagram

1.4.1 What is a transaction?

Rather than defining transactions, it is better to talk about the characteristics of transactions. As we all know, transactions need to meet the four ACID characteristics.

A (atomicity) Atomicity.

The execution of a transaction is considered as an indivisible minimum unit. The operations in a transaction must either be executed successfully in their entirety or rolled back in failure; you cannot execute only part of them.

All statements are executed successfully or all are canceled as a unit.
update t1 set money=10000-17 where id=wxid1
update t1 set money=10000+17 where id=wxid2

C (consistency) consistency.

The execution of a transaction should not violate the integrity constraints of the database. If the system crashes after the second operation in the above example is executed, it is guaranteed that the total money of A and B will not change.

If the database is in a consistent state when a transaction begins, it remains in a consistent state during the execution of that transaction.
 update t1 set money=10000-17 where id=wxid1
 update t1 set money=10000+17 where id=wxid2
 During the above operation, check your account and it is still 10000

I (isolation) isolation.

Generally speaking, the behavior of transactions should not affect each other. However, in reality, the degree to which transactions affect each other is affected by the isolation level. This will be discussed in detail later in the article.

Transactions do not affect each other. When performing operations, other people may perform any operations on these two accounts under different isolation conditions, and the consistency guarantees may be different.

Isolation Level

The isolation level affects consistency.
 read-uncommit X
 read-commit A level that may be used repeatable-read The default level, the same as Oracle SERIALIZABLE Strict default, generally not used

In addition to being controlled by the isolation level, this rule is also controlled by locks. You can think of the implementation of NFS.

D (durability) durability.

After the transaction is committed, the committed transaction needs to be persisted to disk. Even if the system crashes, the submitted data should not be lost.

Only when data is delivered can the transaction be truly secure

1.4.2 Transaction Control Statements

Commonly used transaction control statements:

START TRANSACTION (or BEGIN): explicitly start a new transaction COMMIT: permanently record the changes made by the current transaction (transaction ends successfully)
 ROLLBACK: Cancel the changes made by the current transaction (transaction fails)

Transaction control statements you need to know:

 SAVEPOINT: Assigns a location within a transaction for future reference ROLLBACK TO SAVEPOINT: Cancels changes made after a savepoint RELEASE SAVEPOINT: Removes a savepoint identifier SET AUTOCOMMIT: Disables or enables the default autocommit mode for the current connection

1.4.3 autocommit parameters

Starting with MySQL 5.5, the begin or start transaction statement is no longer required to start a transaction. Also, Autocommit mode is enabled by default, implicitly committing each statement as a transaction.

In some busy business scenarios, this configuration may have a significant impact on performance, but it will greatly improve security. In the future, we will need to weigh our business needs to adjust whether to submit automatically.

Note: In production, choose whether to enable it based on actual needs. Generally, banking services will be closed.

View the current autocommit status:

mysql> show variables like '%autoc%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
row in set (0.00 sec)

Modify the configuration file and restart

[root@db02 world]# vim /etc/my.cnf
[mysqld]
autocommit=0

Check the autocommit status again

mysql> show variables like '%autoc%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
row in set (0.00 sec)
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
row in set (0.00 sec)

Note: Comparison with autocommit set to on

Advantages: Good data security, every modification will be implemented

Disadvantages: Cannot conduct banking transactions, generates a large number of small IOs

1.4.4 Non-transactional statements that cause commit:

DDL statements: (ALTER, CREATE, and DROP)
DCL statements: (GRANT, REVOKE, and SET PASSWORD)
Locking statements: (LOCK TABLES and UNLOCK TABLES)

Examples of statements that cause an implicit commit:

TRUNCATE TABLE
LOAD DATA INFILE
SELECT FOR UPDATE

SQL statement for implicit commit:

START TRANSACTION
SET AUTOCOMMIT = 1

1.5 redo and undo

1.5.1 Transaction Log Undo

Undo principle:

The principle of Undo Log is very simple. In order to meet the atomicity of transactions, before operating any data, the data must be backed up to a place (the place where the data backup is stored is called Undo Log). Then modify the data.

If an error occurs or the user executes a ROLLBACK statement, the system can use the backup in the Undo Log to restore the data to the state before the transaction started.

In addition to ensuring the atomicity of transactions, Undo Log can also be used to assist in completing transaction persistence.

What is undo ?

Undo, as the name suggests, "rollback log" is a type of transaction log.

What is the function?

In the transaction ACID process, the role of "A" atomicity is realized.

Simplify the process of implementing atomic and persistent transactions using Undo Log

Suppose there are two data, A and B, with values ​​1 and 2 respectively.
 A.Transaction starts.
 B. Record A=1 to undo log.
 C. Modify A=3.
 D. Record B=2 to the undo log.
 E. Modify B=4.
 F. Write the undo log to disk.
 G. Write the data to disk.
 H. Transaction Commit

There is an implicit premise here: 'The data is first read into memory, then modified in memory, and finally written back to disk. The reason why atomicity and persistence can be guaranteed at the same time is because of the following characteristics:

A. Record the Undo log before updating data.
B. To ensure durability, data must be written to disk before the transaction is committed. As long as the transaction is successfully committed, the data must have been persisted.
C. Undo log must be persisted to disk before data. If the system crashes between G and H, the undo log is intact and can be used to roll back the transaction.
D. If the system crashes between AFs, because the data is not persisted to disk. So the data on disk remains in the state before the transaction started.

defect:

Before each transaction is committed, data and Undo Log are written to disk, which results in a large amount of disk IO and thus very low performance. If data can be cached for a period of time, IO can be reduced and performance can be improved. But this will lose the durability of the transaction.

Therefore, another mechanism is introduced to achieve persistence, namely Redo Log.

1.5.2 Transaction Log Redo

Redo principle:

Contrary to Undo Log, Redo Log records the backup of new data. Before a transaction is committed, it is sufficient to persist the Redo Log, and there is no need to persist the data. When the system crashes, although the data is not persisted, the Redo Log is persisted.

The system can restore all data to the latest state based on the content of the Redo Log.

What is Redo ?

Redo, as the name suggests, "redo log" is a type of transaction log.

What is the function?

In the transaction ACID process, the role of "D" persistence is realized.

Simplified process of Undo + Redo transaction

Suppose there are two data, A and B, with values ​​1 and 2 respectively.
 A.Transaction starts.
 B. Record A=1 to undo log.
 C. Modify A=3.
 D. Record A=3 in the redo log.
 E. Record B=2 to the undo log.
 F. Modify B=4.
 G. Record B=4 to the redo log.
 H. Write the redo log to disk.
 I. Transaction Commit

Characteristics of Undo + Redo Transactions

 A. To ensure durability, the Redo Log must be persisted before the transaction is committed.
 B. Data does not need to be written to disk before a transaction is committed, but is cached in memory.
 C. Redo Log ensures the durability of transactions.
 D. Undo Log ensures the atomicity of transactions.
 E. There is an implicit characteristic that data must be written to persistent storage later than the redo log.

Whether redo is persisted to disk

innodb_flush_log_at_trx_commit=1/0/2

1.5.3 Locks in Transactions

What is a "lock"?

"Lock" means locking, as the name suggests.

What is the function of a "lock"?

In the ACID process of transactions, "locks" and "isolation levels" work together to achieve the role of "I" isolation.

Lock granularity:

1. MyIasm: Low Concurrency Lock - Table Level Lock

2. Innodb: High Concurrency Lock - Row-Level Lock

Four isolation levels:

READ UNCOMMITTED allows transactions to view uncommitted changes made by other transactions. READ COMMITTED allows transactions to view committed changes made by other transactions. REPEATABLE READ****** ensures that the SELECT output of each transaction is consistent; InnoDB's default level SERIALIZABLE completely isolates the results of one transaction from other transactions.

Overhead, locking speed, deadlock, granularity, and concurrency performance

Table-level lock: low overhead, fast locking; no deadlock; large locking granularity, the highest probability of lock conflict, and the lowest concurrency.
Row-level lock: high overhead, slow locking; deadlock may occur; the locking granularity is the smallest, the probability of lock conflict is the lowest, and the concurrency is the highest.
Page lock: The overhead and locking time are between table lock and row lock; deadlock may occur; the locking granularity is between table lock and row lock, and the concurrency is average.

From the above characteristics, it can be seen that it is difficult to say in general which lock is better. We can only say which lock is more suitable based on the characteristics of the specific application!

From the perspective of locks only: table-level locks are more suitable for applications that are query-oriented and only update a small amount of data according to index conditions, such as Web applications; while row-level locks are more suitable for applications that have a large number of concurrent updates of a small amount of different data according to index conditions and concurrent queries, such as some online transaction processing (OLTP) systems.

1.6 MySQL log management

1.6.1 Introduction to MySQL log types

Description of log types:

1.6.2 Configuration method

Status Error Log:

[mysqld]
log-error=/data/mysql/mysql.log

View the configuration method:

mysql> show variables like '%log%error%';

effect:

Recording general status information and error information of MySQL database is our

Common logs for general database error processing.

mysql> show variables like '%log%err%';
+---------------------+----------------------------------+
| Variable_name | Value |
+---------------------+----------------------------------+
| binlog_error_action | IGNORE_ERROR |
| log_error | /application/mysql/data/db02.err |
+---------------------+----------------------------------+
rows in set (0.00 sec)

1.6.3 General query log

Configuration method:

[mysqld]
general_log=on
general_log_file=/data/mysql/server2.log

View the configuration method:

show variables like '%gen%';

effect:

Record all successfully executed SQL statements in MySQL for auditing purposes, but we rarely enable it.

mysql> show variables like '%gen%';
+------------------+----------------------------------+
| Variable_name | Value |
+------------------+----------------------------------+
| general_log | OFF |
| general_log_file | /application/mysql/data/db02.log |
+------------------+----------------------------------+
rows in set (0.00 sec)

1.7 Binary Logging

The binary log does not depend on the storage engine.

Depends on the sql layer, records information related to sql statements

Binlog log function:

1. Provide backup function

2. Perform master-slave replication

3. Any recovery based on time point

Record the statements that have been executed in the SQL layer. If it is a transaction, record the completed transaction.

Function: Point-in-time backup and point-in-time recovery, master-slave

The "main gate" of binary log

effect:

1. Whether to enable 2. Binary log path /data/mysql/
3. Binary log file name prefix mysql-bin 
4. The file name starts with "prefix".000001~N
log-bin=/data/mysql/mysql-bin

Binary log "switch":

It only makes sense when the main switch is turned on, and it is turned on by default.
We sometimes temporarily close.
Affects the current session only.
sql_log_bin=1/0

1.7.1 Binary Log Format

statement, statement mode:

The recorded information is concise and only the SQL statement itself is recorded. However, if function operations appear in the statement, the recorded data may be inaccurate.

This is the default mode in 5.6, but it should be used with caution in production environments. It is recommended to change it to row.

row, row mode

The process of changing row data in a table.
The recorded data is detailed and has high requirements on IO performance. The recorded data is accurate under any circumstances.
This is generally the mode in production.
The default mode after 5.7.

mixed, mixed mode

After judgment, a mixed row+statement recording mode is selected. (Generally not used)

1.7.2 Enable binary logging

mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+
rows in set (0.00 sec)

Modify the configuration file to enable binary logging

[root@db02 tmp]# vim /etc/my.cnf
[mysqld]
log-bin=/application/mysql/data/mysql-bin

Command line modification method

mysql> SET GLOBAL binlog_format = 'STATEMENT'
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';

View the type of binary log file

[root@db02 data]# file mysql-bin.*
mysql-bin.000001: MySQL replication log
mysql-bin.index: ASCII text

View the MySQL configuration:

mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------------------+
| log_bin | ON |
| log_bin_basename | /application/mysql/data/mysql-bin |
| log_bin_index | /application/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-----------------------------------------+
rows in set (0.00 sec)

1.7.3 Defining the recording method

View the current format

mysql> show variables like '%format%';
+--------------------------+-------------------+
| Variable_name | Value |
+--------------------------+-------------------+
| binlog_format | STATEMENT |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| time_format | %H:%i:%s |
+--------------------------+-------------------+
rows in set (0.00 sec)

Modify the format

[root@db02 data]# vim /etc/my.cnf
[mysqld]
binlog_format=row

Check after modification

mysql> show variables like '%format%';
+--------------------------+-------------------+
| Variable_name | Value |
+--------------------------+-------------------+
| binlog_format | ROW |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| time_format | %H:%i:%s |
+--------------------------+-------------------+
rows in set (0.00 sec)

1.8 Binary Log Operation

1.8.1 View

View at the operating system level

[root@db02 data]# ll mysql-bin.*
-rw-rw---- 1 mysql mysql 143 Dec 20 20:17 mysql-bin.000001
-rw-rw---- 1 mysql mysql 120 Dec 20 20:17 mysql-bin.000002
-rw-rw---- 1 mysql mysql 82 Dec 20 20:17 mysql-bin.index

Refresh log

mysql> flush logs;

Log directory after refresh is completed

[root@db02 data]# ll mysql-bin.*
-rw-rw---- 1 mysql mysql 143 Dec 20 20:17 mysql-bin.000001
-rw-rw---- 1 mysql mysql 167 Dec 20 20:24 mysql-bin.000002
-rw-rw---- 1 mysql mysql 120 Dec 20 20:24 mysql-bin.000003
-rw-rw---- 1 mysql mysql 123 Dec 20 20:24 mysql-bin.index
[root@db02 data]#

View the binary log file currently in use

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
row in set (0.00 sec)

View all binary log files

mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
|mysql-bin.000001 | 143 |
|mysql-bin.000002 | 167 |
| mysql-bin.000003 | 120 |
+------------------+-----------+
rows in set (0.00 sec)

1.8.2 Viewing binary log contents

Glossary:

1. events

How to define binary log: the smallest unit of command occurrence

2. Position

The position number that each event corresponds to in the entire binary file is the position number.

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
row in set (0.00 sec)
[root@db02 data]# mysqlbinlog mysql-bin.000003 >/tmp/aa.ttt

Export all information

[root@db02 data]# mysqlbinlog mysql-bin.000003 >/tmp/aa.ttt

How to view binlog:

1. View the original binlog information

mysqbin mysql-bin.000002

2. In row mode, translate into statements

mysqlbinlog --base64-output='decode-rows' -v mysql-bin.000002

3. View binlog events

show binary logs; all binlog information in use show binlog events in 'log file'

4. How to intercept binlog content and restore it as needed (conventional ideas)

(1) show binary logs; show master status;

(2) show binlog events in '' Read from the end to the beginning, find the transaction with the wrong operation, and determine the start and end positions of the transaction

(3) Eliminate the incorrect operations and leave the normal operations in two SQL files

(4) First, restore the test database, export the data caused by the erroneous operation, and then resume production.

Problems encountered using the above method:

Longer recovery events

It has a certain impact on production data, and redundant data may appear

Better solution.

1. Flashback function

2. Delay from the database through backup

1.8.3 How to intercept binary logs using mysqlbinlog

The common options for mysqlbinlog are as follows:

1.8.4 Deleting binary logs

SET GLOBAL expire_logs_days = 7;
or PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;

Delete logs according to the file name:

PURGE BINARY LOGS TO 'mysql-bin.000010';

Reset the binary log count, start counting from 1, and delete the original binary log.

reset master

1.9 MySQL slow query log (slow log)

1.9.1 What is this?

slow-log records all slow SQL statements within the conditions

A tool log for optimization. Can help us locate the problem.

1.9.2 Slow query log

It records the relevant SQL statements that affect database performance in the MySQL server to the log file

By analyzing and improving these special SQL statements, the purpose of improving database performance can be achieved. Slow log settings

long_query_time: Set the slow query threshold. SQL that exceeds the set value will be recorded in the slow query log. The default value is 10s
slow_query_log: Specifies whether to enable the slow query log slow_query_log_file: Specifies the location where the slow log file is stored. It can be empty and the system will give a default file host_name-slow.log
min_examined_row_limit: SQL that returns fewer rows than the parameter specified by the query check is not recorded in the slow query log log_queries_not_using_indexes: Whether the slow query log that does not use the index is recorded in the index

Slow query log configuration

[root@db02 htdocs]# vim /etc/my.cnf
slow_query_log=ON
slow_query_log_file=/tmp/slow.log
long_query_time=0.5 # Control the threshold of slow log records log_queries_not_using_indexes

After the configuration is complete, restart the service...

Check whether the slow query log is enabled and its location.

mysql> show variables like '%slow%'
 -> ;
+---------------------------+---------------+
| Variable_name | Value |
+---------------------------+---------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /tmp/slow.log |
+---------------------------+---------------+
rows in set (0.00 sec)

1.9.3 mysqldumpslow Command

/path/mysqldumpslow -sc -t 10 /database/mysql/slow-log

This will output the top 10 SQL statements with the most records, where:

1.9.4 How to ensure the consistency of committed transactions in binlog and redolog

mysql> show variables like '%sync_binlog%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog | 0 | #Control binlog commit phase+---------------+-------+
row in set (0.00 sec)

sync_binlog ensures that every committed transaction is written to the binlog.

1.9.5 Double One Standard in MySQL:

The two parameters innodb_flush_log_at_trx_commit and sync_binlog are key parameters for controlling MySQL disk write strategy and data security.

Parameter meaning:

innodb_flush_log_at_trx_commit=1

If innodb_flush_log_at_trx_commit is set to 0, the log buffer will be written to the log file once per second, and the flush operation of the log file will be performed simultaneously. In this mode, when the transaction is committed, the write operation to disk will not be triggered actively.

If innodb_flush_log_at_trx_commit is set to 1, MySQL will write the data in the log buffer to the log file and flush it to disk each time a transaction is committed.

If innodb_flush_log_at_trx_commit is set to 2, MySQL will write the data in the log buffer to the log file each time a transaction is committed. However, the flush operation will not be performed at the same time. In this mode, MySQL will perform a flush operation once per second.

Notice:

Due to the process scheduling strategy, this "flush operation is performed once per second" is not guaranteed to be 100% "per second".

Parameter meaning:

sync_binlog=1

The default value of sync_binlog is 0. Like the operating system's mechanism for refreshing other files, MySQL will not synchronize to disk but rely on the operating system to refresh the binary log.

When sync_binlog = N (N>0), MySQL will use the fdatasync() function to synchronize its binary log to disk every time it writes the binary log N times.

Note:

If autocommit is enabled, there is one write operation per statement; otherwise there is one write operation per transaction.

Safety Notes

When innodb_flush_log_at_trx_commit and sync_binlog are both 1, it is safest. In the event of a mysqld service crash or a server host crash, the binary log may only lose a maximum of one statement or one transaction. However, you cannot have your cake and eat it too. Double 11 will lead to frequent IO operations, so this mode is also the slowest way.

When innodb_flush_log_at_trx_commit is set to 0, a crash of the mysqld process will result in the loss of all transaction data in the last second.

When innodb_flush_log_at_trx_commit is set to 2, all transaction data in the last second may be lost only if the operating system crashes or the system loses power.

Double 1 is suitable for businesses with very high data security requirements and sufficient disk IO write capacity to support services, such as order, transaction, recharge, and payment consumption systems. In the double 1 mode, when disk IO cannot meet business needs, such as the pressure of the 11.11 event. The recommended approach is to set innodb_flush_log_at_trx_commit=2, sync_binlog=N (N is 500 or 1000) and use a cache with a battery backup power supply to prevent system power outages.

System performance and data security are necessary factors for high availability and stability of business systems. We need to find a balance point for system optimization. The right one is the best. According to different business scenario requirements, we can combine and adjust the two parameters to optimize the performance of the db system.

The above storage engine and log description based on MySQL (comprehensive explanation) is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.