MYSQL

MYSQLHow do master and Slave solve data Loss

A master-slave replication

Solving data loss problems:

1. Semi-synchronous replication

Since MySQL5.5, MySQL has supported semi-synchronous replication. Semi-synchronous replication is between asynchronous replication and synchronous replication. The master database does not immediately return the results to the client after executing a transaction, but needs to wait for at least one secondary database to receive the results and write them to the relay log. Compared with asynchronous replication, semi-synchronous replication improves data security and causes a TCP/IP round-trip delay.

Innodb_flush_log_at_trx_commit =1

The default value of sync_binlog is 0. MySQL does not synchronize binlogs to disk. The value indicates how many binlogs are written to disk.

A value of 1 for innodb_flush_LOG_at_trx_COMMIT means that logs are flushed to disk every time a transaction is committed or for instructions outside of a transaction.

Note: When both values are set to 1, write performance is limited. This is recommended only for scenarios with high data security requirements, such as order payment involving money, and the system I/O capability must be supported!

Resolves slave library replication delays

Delay in primary/secondary synchronization of MySQL databases

  • Optimization of the network
  • Upgrade the Slave hardware configuration
  • Slave Adjusts parameters and is disabledbinlog, modifyinnodb_flush_log_at_trx_commitThe parameter value
  • Upgrade MySQL to 5.7, use parallel replication
Synchronize data from multiple equipment rooms in the MYSQL cluster

Subscribe to binlog logs using these open source components and synchronize the parsed change data to the target library. The whole process can be divided into two steps. The first step is to subscribe to obtain the changed data, and the second step is to update the changed data to other target libraries. (Open source canal)

How to solve the problem of large amount of data synchronization and how to optimize it

MQ

How to solve the problem of repeated insertion

There is usually a restriction that records must have a primary key or unique index

How does DDL work

DDL operations lock tables, which can have a significant impact on business.

As a result, the synchronous component typically filters DDL statements and does not synchronize them. Dbas make table structure changes on different database clusters using some online DDL tools.

How to resolve unique index conflicts

Use a globally unique distributed ID generator to generate unique indexes

MySQL index
Innodb indexing principle

The most common index data structure in MySQL is B+ tree, which has the following characteristics:

  1. In B+ tree, all data record nodes are stored in leaf nodes of the same layer according to the size of key values, instead of non-leaf nodes only storing key information, which can greatly reduce the number of keys stored in each node and reduce the height of the B+ tree
  2. The keywords of the leaf nodes of B+ tree are arranged in order from small to large, and the data at the end of the left will save the pointer to the data at the beginning of the node on the right.
  3. B+ trees have fewer levels: Compared to B+ trees, which store more key words per non-leaf node, there are fewer levels of the tree so data is queried faster
  4. B+ tree is more stable in query speed: B+ all keyword data addresses are stored on leaf nodes, so the search times are the same, so the query speed is more stable than B tree.
  5. B+ tree has the natural sorting function: all the leaf node data of B+ tree forms an ordered linked list, which is more convenient for querying the data in the size range, with high data tightness and higher cache hit ratio than B tree.
  6. B+ tree traversal of all nodes is faster: A B+ tree traversal of the whole tree only requires traversal of all leaf nodes, rather than traversal of each layer like a B tree, which is conducive to the database to perform full table scan.

FileSort problem

MySQL needs an extra pass to figure out how to retrieve rows in sort order. Sorting is done by browsing all rows based on the join type and saving the sort key and pointer to all rows that match the WHERE clause. The keywords are then sorted and rows are retrieved in sorted order.

The algorithm used by Filesort is QuickSort, which blocks the metadata generated for the records to be sorted, and then mergesort method is used to merge the blocks. The memory size allowed by filesort is the value of sort_buffer_size, which is 2M by default. When sort_buffer_size is not enough, mysql uses a temporary file to store each partition, and then sorts each partition and merges the partition multiple times to complete the global sort. Filesort slowness can be addressed by increasing sort_buffer_size.

Smooth data migration
Smooth migration – double write
  1. Step 1: Upgrade the service, and perform the same modification operation on the new library for “data modification on the old library” (insert, DELETE, update data modification here)
  2. Step 2: Develop a data migration tool for data migration
  3. Step 3: After data migration is complete, use the data verification tool to compare the data in the old database with the data in the new database. If the data in the old database is completely consistent with the expected data, the data in the old database will prevail if the limit inconsistency in Step 2 occurs.
  4. Step 4: After data consistency is complete, traffic is cut to the new database to complete smooth data migration.

How to ensure data consistency

The time when the data write service goes online is taken as the segmentation point. Data consistency before the segmentation point is guaranteed by data migration scripts. The data consistency after the segmentation point is guaranteed by the new and old table double write; Of course, the consistency of these data will be checked mechanism.

SQL Execution Process

How are SQL statements executed

First, mysql is divided into two parts: server layer and storage engine layer. The Server layer consists of four functional modules, which are connector, query cache, optimizer and executor. This layer is responsible for all the core work of mysql, such as built-in functions, stored procedures, triggers, and views.

The storage engine layer is responsible for data access. Note that storage engines are optional in mysql. Common ones include InnoDB, MyISAM, Memory, etc. The most common one is InnoDB. It is now the default storage engine (starting with mysql 5.5.5), and you can see that I specified the InnoDB engine in the above statement. Of course, it’s the default if you don’t specify it.

Since storage engines are optional, all storage engines in mysql actually share a server layer.

Slow query analysis

Enabling Slow Query

Mysql > update mysql > update mysql > update mysql > update mysql

set global slow_query_log=1; 或者 set global slow_query_log=ON;

Localhost-slow. log exists in /var/lib/mysql. If localhost-slow.log is disabled by default.

Method 2 :(permanent)

CFG file to [mysqld] :

slow_query_log=ON
slow_query_log_file=/var/lib/mysql/localhost-slow.log
Copy the code

Set the slow query time

Set the slow query time to 1 second: set long_query_time=1;

See the log

more /var/lib/mysql/localhost-slow.log

Case study:

Tcp port: 0 Unix socket: (null) Time Id Command Argument # Time: 210125 6:30:14 # User@Host: Reptile [reptile] @ [192.168.10.254] Id: 1 # Query_time: 2.000380 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 SET timestamp=1611556214; SELECT SLEEP(2);Copy the code

You can also use the slow query log analysis tool Mysqldumpslow

show profile

Usage: Analyzes the resource consumption of statement execution in the current session

Mysql is not enabled by default because it is expensive to start

  1. set profiling=1; Or the set profiling = ON;
  2. show profiles; Displays the statement currently executed and when.
MYSQL MYSQL gap lock, row lock analysis

Repeatable Read isolation level can prevent phantom Read

MVCC only works under two transaction levels:

  • Read Committed
  • Repeatable Read

MySQL InnoDB supports three types of row locking: InnoDB default is next-key locking.

  • Record Lock: the Lock is placed directly on the index Record, which holds the key.
  • Gap Lock: Locks the Gap between index records to ensure that the Gap between index records remains the same. Gap locking is for transactions whose isolation level is repeatable reads or above.
  • Next-key Lock: a row Lock and a gap Lock together is called a next-key Lock.

The only function of a Gap Lock in InnoDB is to prevent the insertion of other transactions, thus preventing phantom reads.

Prerequisites for Innodb to automatically use gap locking:

  • Must be under Repeatable Read level

  • If there is no index, mysql will scan the entire table and lock all the records in the whole table, including those that do not exist. In this case, other transactions cannot modify, delete or add the records.

The implementation of gap lock:

  • Prevent new data from being inserted in the gap
  • Prevent existing data from being updated to data in the gap (e.g. prevent numer=3 records from being updated to number=5)

The only function of a gap lock in InnoDB is to prevent the insertion of other transactions to prevent phantom read, so there is no shared lock or exclusive lock gap lock.

Clearance lock conditions:

  • By default,InnoDB works at Repeatable Read isolation level withNext-Key LockIs used to lock data rowsTo prevent illusionary reading.Next-Key LockIs a combination of row lock and gap lock whenCondition and range retrieval of data, locks values in its range that may not exist.
  • For normal secondary indexes, range locking is done using traditional next-key Lock.
  • Innodb storage engine optimizes next-key lock to record lock when the query index has unique attributes (unique index, primary key index), that is, only the index itself is locked, not the range.

To disable gap locking, either set the isolation level to Read Committed or turn on the innodb_locks_unsafe_for_binlog parameter.

For snapshot reads, phantom reads are resolved by MVCC. For current reads, gap-lock is used.

Note:

  • Snapshot read: a simple select operation. Snapshot read is not locked. (There are exceptions, of course, which are discussed below.)

    • select * from table where ? ;

  • Current read: special read operations, such as insert/update/delete operations, belong to current read and need to be locked.

    • select * from table where ? lock in share mode;
    • select * from table where ? for update;
    • Insert into table values (…) ;
    • update table set ? where ? ;
    • delete from table where ? ;

    All of the above statements belong to the current read, the latest version of the read record. In addition, after reading, it is necessary to ensure that other concurrent transactions cannot modify the current record and lock the read record. Except for the first statement, which locks the read record with S (shared lock), all other operations are locked with X (exclusive lock).

Summary: In RR level, gap locks are applied to non-unique or non-primary key indexes, no indexes, conditional or range indexes, and query failure. In other cases, only row-level locks are applied.

Information:

Select lock analysis (Mysql)

MySQLDeadlock troubleshooting cases

Three kinds of log
  • binlogMainly used for replication and data recovery.
  • redo logUsed to recover data that has not been flushed to disk after a memory update.
  • undo logUsed for rollback and multiple versioning.

Redo Log

Transaction logs, archive logs

Why do you want toredo log

If every update is written to disk, random IO write performance is poor!

Innodb writes records to the redo log and updates the data pages in memory. At the same time, background threads asynchronously update operation records to data pages on disk.

The update process is time consuming with random IO on disk. Redo logs are more efficient because they do sequential I/OS on disk. The redo log is an efficient way to transform random global writes into sequential local writes.

innodb_flush_log_at_trx_commit

There are three ways to write redo log buffers to redo log files.

parameter meaning
0 (deferred write) The redo log buffer is not written during a transaction commitos buffer, but writes per secondos bufferAnd call thefsync()Write to redo logfile. This means that 0 is written to disk (approximately) every second, and when the system crashes, 1 second of data is lost.
1 (Real-time write, real-time refresh) Each commit writes a log to the redo log buffer os bufferAnd call the fsync()Redo logfile. This way, no data is lost even if the system crashes, but IO performance is poor because every commit is written to disk.
2 (Real-time write, delay refresh) Each commit only writes toos buffer, followed by calls per secondfsync()will os bufferLog files are written to the redo log file.

crash-save

That is, in InnoDB storage engine, if MySQL crashes suddenly at any stage during the transaction submission process, the integrity of the transaction can be guaranteed after the restart. The committed data will not be lost, and the incomplete data will be automatically rolled back. This capability, called crash-safe, relies on redo log and undo log.

Undo Log

Undo log mainly provides rollback, but there is also a multi-line version control (MVCC) to ensure atomicity of transactions.

During data modification, a logical log that is contrary to the current operation is recorded in the Undo log.

If the transaction fails due to some reasons, you can use the undo log to roll back the transaction to ensure the integrity of the transaction.

binlog

Application Scenarios:

  • Primary/secondary replication: Enable this function on the MasterbinlogAnd thenbinlogSend to each Slave end, Slave end playbackbinlogTo achieve master/slave data consistency.
  • Data recovery: By usingmysqlbinlogTools to recover data.

There are three main differences between redo logs and binlogs:

  • Redo log isInnoDBEngine-specific;binlogMySQL Server layer implementation, all engines can use.
  • A redo log is a physical log of changes made to a data page. The binlog is the logical log that records the original logic of the statement, such as **” add 1 to the age field on ID=2 “**.
  • Redo logs are written in a redo log cycle.binlogIt can be appended.Additional writingRefers to thebinlogWhen a file is written to a certain size, it switches to the next one without overwriting the previous log.