Detailed explanation of MySQL transactions and MySQL logs

Transactional Characteristics

1. Atomicity: After a transaction starts, all operations must be completed or not done at all. It is impossible for the transaction to get stuck in the middle.

2. Consistency: The integrity constraints of the database are not violated before and after the transaction starts and ends. For example, if A transfers money to B, it is impossible for A to deduct the money but B does not receive it.

3. Isolation: Only one transaction is allowed to request the same data at the same time, and there is no interference between different transactions. For example, if A is withdrawing money from a bank card, B cannot transfer money to this card before A completes the withdrawal process.

4. Durability: After the transaction is completed, all updates made by the transaction to the database will be saved to the database and cannot be rolled back.

Transaction concurrency issues

1. Dirty read: Transaction A reads the data updated by transaction B, and then B rolls back the operation, so the data read by A is dirty data

2. Non-repeatable read: Transaction A reads the same data multiple times. During the process of transaction A reading multiple times, transaction B updates and commits the data, resulting in inconsistent results when transaction A reads the same data multiple times.

3. Phantom read: System administrator A changes the grades of all students in the database from specific scores to ABCDE grades, but system administrator B inserts a record with a specific score at this time. When system administrator A finishes the change, he finds that there is still a record that has not been changed, as if an illusion has occurred. This is called phantom read.

Summary: Non-repeatable reads and phantom reads are easily confused. Non-repeatable reads focus on modifications, while phantom reads focus on additions or deletions. To solve the problem of non-repeatable reads, you only need to lock the rows that meet the conditions. To solve the problem of phantom reads, you need to lock the table.

Transaction Isolation

MySQL defaults to "repeatable read", after serialization

#Check the global transaction isolation levelSELECT @@global.tx_isolation;
#Check the current session transaction isolation levelSELECT @@session.tx_isolation; 
#Check the current transaction isolation levelSELECT @@tx_isolation;
#Set the global isolation level set global transaction isolation level read committed;
#Set the current session isolation level set session transaction isolation level read committed;

Serializability is the highest isolation level and solves the phantom read problem by forcing transactions to be ordered so that they cannot conflict with each other. In short, it adds a shared lock on each data row read, which at this level may cause a lot of timeouts and lock contention.

Shared lock: The code name of the shared lock is S

MySQL log file system composition

1. Composition of MySQL log file system

a. Error log: records problems that occur when starting, running, or stopping mysqld.
b. General log: records the established client connections and executed statements.
c. Update log: records statements that change data. This log is no longer used in MySQL 5.1.
d. Binary log: records all statements that change data. Also used for replication.
e. Slow query log: records all queries that take longer than long_query_time seconds to execute or queries that do not use indexes.
f. Innodb log: innodb redo log

Binary log (binlog):

Contains all data that has been updated or has potentially been updated (such as a DELETE that did not match any rows)

Contains information about the execution time of each statement that updates the database (DML)

Does not include statements that do not modify any data. If you need to enable this option, you need to enable the general logging function

The main purpose is to restore the database to the point of database failure as much as possible, because the binary log contains all updates made after the backup

Used to log all statements on the master replication server that will be sent to the slave server

Enabling this option will reduce database performance by 1%, but the database integrity is guaranteed. For important databases, the performance is worth trading for integrity. It is somewhat similar to enabling archive mode in Oracle.

show variables like '%version%'; 
show variables like '%log_bin%'; //Whether to enable binlog
show variables like '%binlog%'; //binlog related parameters show variables like '%datadir%'; //Data file directory, where logs are stored by default #Edit my.cnf to set the binary log location (Note: after configuring the binary log path and file name, the system variable log_bin is automatically set to on) 

log_bin=/var/lib/mysql/binarylog/binlog 

#If you only set log_bin in my.cnf, but do not specify file_name, then restart the database. You will find that the binary log file name is in the format of ${hostname}-bin #Switch log show master status;
flush logs;
show master status;

Each time the MySQL service is restarted, a new binary log file is generated, which is equivalent to binary log switching. When you switch binary logging, you will see these numbers continue to increase. In addition to these binary log files, you will see that a DB-Server-bin.index file is also generated. This file stores a list of all binary log files, also known as the index of binary files.

Binary logs can be deleted manually through commands or set to be automatically cleaned up.

show binary logs;
mysql> purge binary logs to 'DB-Server-bin.000002';

purge binary logs to xxx; means deleting all binary log files before a certain log. This command will modify the relevant data in the index: purge binary logs before '2017-03-10 10:10:00'; clear the binary log files before a certain time point.
purge master logs before date_sub( now() ), interval 7 day); Clear the binary log files 7 days ago reset master; Clear all binary log files (there is currently no master-slave replication relationship)

show variables like 'expire_logs_days'; We can also set the expire_logs_days parameter to set automatic cleanup. The default value is 0, which means that the automatic deletion function is not enabled. If the automatic cleanup function is enabled, the binary log files that exceed this number of days will be automatically deleted. Automatic deletion usually occurs when MySQL is started or when the log is flushed.
set expire_logs_days=7;

Binary log related parameters

1. The system variable log_bin_trust_function_creators is OFF by default. Enabling this parameter will restrict the creation of stored procedures, functions, and triggers.

2: The system variable sql_log_bin is used to control whether the session-level binary log function is turned on or off. The default value is ON, which means that the binary log function is enabled.

3. The system variable binlog_cache_size indicates that a cache of binlog_cache_size size is allocated to each client. The default value is 32768. The prerequisite for using binary log cache is that the server uses an engine that supports transactions and has the bin log function enabled. It is a memory area designed by MySQL to temporarily cache binlog data for a short period of time to improve the efficiency of binlog. Generally speaking, if there are no large transactions in our database and writes are not particularly frequent, 2MB to 4MB is a suitable choice. However, if our database has many large transactions or multi-transaction statements and the write volume is relatively large, we can appropriately increase binlog_cache_size. At the same time, we can use binlog_cache_use and binlog_cache_disk_use to analyze whether the set binlog_cache_size is sufficient, and whether there is a large amount of binlog_cache using temporary files (binlog_cache_disk_use) for caching due to insufficient memory size.

You can check Binlog_cache_disk_use and Binlog_cache_use to determine whether binlog_cache_size needs to be adjusted.

4. System variable max_binlog_cache_size The maximum cache memory size that can be used by binary logs. When executing a multi-statement transaction, if max_binlog_cache_size is not large enough, the system may report an error "Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage".

5. System variable max_binlog_stmt_cache_size

max_binlog_cache_size is for transaction statements, and max_binlog_stmt_cache_size is for non-transaction statements. When we find that Binlog_cache_disk_use or Binlog_stmt_cache_disk_use is relatively large, we need to consider increasing the cache size.

6. The system variable max_binlog_size indicates the maximum size of the binary log, which is generally set to 512M or 1GB, but cannot exceed 1GB. This setting cannot strictly control the size of the binary log, especially when the binary log is close to a large transaction. In order to ensure the integrity of the transaction, it is impossible to switch the log. All SQL statements of the transaction can only be recorded in the current log until the transaction ends.

7. The system variable binlog_checksum is used for master-slave verification of replication. NONE means no checksum is generated, and CRC-32 means using this algorithm for checking.

8. System variable sync_binlog. This parameter is crucial for the MySQL system. It not only affects the performance loss of the binary log file to MySQL, but also affects the integrity of the data in MySQL.

sync_binlog=0, when the transaction is committed, Mysql only writes the data in binlog_cache to the binlog file, but does not execute disk synchronization instructions such as fsync to notify the file system to refresh the cache to disk, but lets the Filesystem decide when to synchronize. The default setting in MySQL is sync_binlog=0, which means that no mandatory disk refresh instructions are made. This setting has the best performance, but also the greatest risk. Once the system crashes, all binary log information in the file system cache will be lost. This leads to the problem of incomplete data.

sync_binlog=n, after n transactions are committed, Mysql will execute a disk synchronization instruction such as fsync, and the file system will refresh the Binlog file cache to disk.

You can adjust sync_binlog appropriately to obtain higher concurrency and performance at the expense of a certain degree of consistency.

9. The system variable binlog_format specifies the type of binary log. There are three values: STATEMENT, ROW, and MIXED. The default mode before MySQL 5.7.6 is STATEMENT. The default mode for MySQL 5.7.7 and later is ROW mode. This parameter mainly affects master-slave replication.

SQL statement-based replication (SBR),

row-based replication (RBR),

Mixed-based replication (MBR).

View binary log contents

Method 1: Use the show binlog events method to obtain the logs of the current and specified binlogs. It is not suitable for extracting a large number of logs.

SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

SHOW BINLOG EVENTS IN 'mysql-bin.000005' \G

Method 2: Use the mysqlbinlog command line to view log contents (suitable for batch log extraction).

system mysqlbinlog /var/lib/mysql/DB-Server-bin.000013;
mysqlbinlog /var/lib/mysql/DB-Server-bin.000013 > test.sql;

Types of binary logs

Segment-based log format

binlog_format=STATEMENT

The SQL statement of the operation is recorded.

advantage:

The amount of log records is relatively small, saving disk and network I/O. The amount of log generated by modifying or inserting only one record in ROW format is less than the amount of log generated by the segment.

shortcoming:

The context information must be recorded to ensure that the execution results of the statement on the slave server are the same as those on the master server.

Certain non-deterministic functions such as UUID and USER() cannot be replicated.

This may cause data inconsistency between the primary and secondary servers of MySQL replication, thereby interrupting the replication link.

Display binlog format

show variables like 'binlog_format';

set session binlog_format=statement;

Line-based log format

Change the binary format of my.ini to binlog_format=ROW

Advantages of row: The row format can avoid the problem of master-slave inconsistency in MYSQL replication. This format is officially recommended. The same SQL statement modifies 10,000 pieces of data. Segment-based logging will only record this SQL statement. A row-based log will have 10,000 records, recording the modification of each row of data.

1. MySQL master-slave replication is safer.

2. Modifying each row of data is more efficient than segment-based replication. If the data in the database is modified by an error operation and there is no backup to restore, we can recover the data by analyzing the binary log and reversing the data modification operations recorded in the log.

Disadvantages of row: large amount of log records

binlog_row_image=[full,minimal,noblob]

full: record all changes to columns; minimal: only record modified columns. noblob: If the field is of text type or clob, these logs are not recorded.

Use mysqlbinlog -vv ../data/mysql-bin.000005 to view the detailed log.

set session binlog_row_image=minimal

Hybrid log format:

binlog_format=MIXED

Features: The system determines the choice between segment and row-based log formats based on the SQL statement. The amount of data is determined by the SQL executed.

How to choose a binary format

It is recommended to use binlog_format = mixed or binlog_format = row; binlog_row_image = minimal;

Copy method:

1. SQL statement-based replication (SBR)

Advantages: less logs are generated, saving IDs for network transmission. It does not require the table definitions of the master and slave databases to be exactly the same.

It is more flexible than row-based replication.

Disadvantages: For non-deterministic events, the consistency of master-slave replication data cannot be guaranteed. For stored procedures, triggers

2. Row-Based Replication (RBR)

Advantages: Can be applied to any SQL replication including non-deterministic functions, stored procedures, etc. Can reduce the use of database locks.

Disadvantages: The table structure of the master and slave databases must be the same, otherwise replication will be interrupted.

3. Copying the working method

1. The master server writes the changes to the binary log.

2. The slave reads the master's binary log changes and writes them to relay_log.

Log point-based replication, GTID-based replication.

3. Replay the logs in relay_log on the slave.

SQL segment-based logging re-executes recorded SQL on the slave database.

Row-based logging applies changes to data rows directly on the slave.

The above is a detailed explanation of MySQL transactions and MySQL logs. For more information about MySQL transactions and MySQL logs, please pay attention to other related articles on 123WORDPRESS.COM!