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

This article is from the Internet.

This series of articles will be sorted out in my Java interview Guide warehouse on GitHub. Please check out more wonderful content in my warehouse.

Https://github.com/h3pl/Java-Tutorial

Have some trouble with Star if you like.

The article was first posted on my personal blog:

Www.how2playlife.com

This article is one of the "re-learning MySQL database" of Wechat official account [Java technology jianghu]. Part of this article comes from the Internet. In order to explain the topic of this article clearly and thoroughly, it also integrates a lot of technical blog content that I think is good. I quote some good blog articles, if there is any infringement, please contact the author.

This series of blog posts will show you how to start to advanced, from the basic usage of sql, from MySQL execution engine to index, transaction and other knowledge, to learn the implementation principles of MySQL-related technologies step by step, to better understand how to optimize sql based on this knowledge, to reduce SQL execution time, to analyze SQL performance through execution plans, and then to master-slave replication and master-slave deployment of MySQL. So that you can have a more complete understanding of the whole MySQL technical system and form your own knowledge framework.

If you have any suggestions or questions about this series of articles, you can also follow the official account [Java Technology jianghu] to contact the author. You are welcome to participate in the creation and revision of this series of blog posts.

Relearn the logs in the MySQL database 10:MySQL

Like most relational databases, log files are an important part of MySQL databases. MySQL has several different log files, usually including error log files, binary logs, generic logs, slow query logs, and so on. These logs can help us locate internal events in mysqld, database performance failures, record the change history of data, users restore the database, and so on. This article mainly describes the error log file.

The composition of the 1.MySQL journal file system

Error log: record problems that occur when starting, running, or stopping mysqld. B, general log: record the client connection established and the statement executed. C. Update log: a statement that records change data. This log is no longer in use in MySQL 5.1. D, binary log: a statement that records all change data. It is also used for replication. E, slow query log: record all queries with execution time longer than long_query_time seconds or queries that do not use indexes. F, Innodb logs: innodb redo log and undo log

By default, all logs are created in the mysqld data directory. You can force mysqld to close and reopen the log file (or in some cases switch to a new log) by refreshing the log. When you execute a FLUSH LOGS statement or execute mysqladmin flush-logs or mysqladmin refresh, the log is aging. In the case of MySQL replication, more log files, called replacement logs, are maintained from the replication server.

two。 Error log

The error log is a text file. The error log records the details of each startup and shutdown of MySQL Server, as well as all serious warnings and error messages during operation. You can open the mysql error log with the-log-error [= file_name] option, which specifies the location where mysqld saves the error log file. For specifying the-log-error [= file_name] option without a file_ name value, mysqld uses the error log name host_name.err and writes to the log file in the data directory. While mysqld is writing the error log to a file, the server closes and reopens the log file when FLUSH LOGS or mysqladmin flush-logs is executed. It is recommended that you manually rename the error log file before flush, after which the mysql service will open a new file with the original file name. The following is the error log backup method: shell > mv host_name.err host_name.err-old shell > mysqladmin flush-logs shell > mv host_name.err-old backup-directory

Logs in 3.InnoDB

MySQL Database InnoDB Storage engine Log roaming

1-Undo Log

Undo Log is to realize the atomicity of transactions. In the MySQL database InnoDB storage engine, Undo Log is also used to implement multi-version concurrency control (MVCC for short).

Atomicity of transactions (Atomicity) all operations in a transaction are either completed or nothing is done, and only part of the operation cannot be done. If an error occurs during execution, Rollback to the state before the start of the transaction as if the transaction had never been executed.

The principle of Undo Log is simple: in order to satisfy the atomicity of the transaction, before manipulating any data, first backup the data to a place (the place where the backup of the data is called Undo Log). Then modify the data. If an error occurs or the user executes the ROLLBACK statement, the system can use the backup in Undo Log to restore the data to the state it was before the transaction began.

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

Transaction persistence (Durability) once the transaction is completed, all changes made by the transaction to the database are persisted to the database. In order to ensure persistence, the database system will completely record the modified data to persistent storage.

The simplified process of implementing atomic and persistent transactions with Undo Log assumes that there are two data An and B, with values of 1 and 2, respectively. a. The business begins. b. Record Agg1 to undo log. c. Modify Agg3. D. Record bread2 to undo log. e. Modify Bamboo 4. F. Write undo log to disk. g. Write the data to disk. h. There is an implicit prerequisite for transaction commit: 'the data is first read into memory, then modified, and finally written back to disk'.

The reason why atomicity and persistence can be guaranteed at the same time is due to the following characteristics: A. Record the Undo log before updating the data. b. To ensure persistence, the data must be written to disk before the transaction commits. As long as the transaction is successfully committed, the data must have been persisted. C. Undo log must be persisted to disk before the data is persisted. If the system crashes between GMAH, the undo log is complete and can be used to roll back transactions. d. If the system crashes between Amurf because the data is not persisted to disk. So the data on disk remains the same as it was before the transaction started.

Flaw: data and Undo Log are written to disk before each transaction is committed, which results in a large amount of disk IO, so performance is very low.

If you can cache data for a period of time, you can reduce IO and improve performance. But this loses the durability of the transaction. Therefore, another mechanism is introduced to achieve persistence, namely Redo Log.

2-Redo Log

The principle is contrary to Undo Log, Redo Log records the backup of new data. You just need to persist the Redo Log before the transaction is committed, and there is no need to persist the data. When the system crashes, the data is not persisted, but the Redo Log is persisted. The system can restore all data to the latest state according to the content of Redo Log.

The simplified process of Undo + Redo transaction assumes that there are two data An and B, and the value is 1pm 2.A. The business begins. b. Record Agg1 to undo log. c. Modify Agg3. D. Record Aban 3 to redo log. e. Record bread2 to undo log. f. Modify Bamboo 4. G. Record bread4 to redo log. h. Write redo log to disk. i. Transaction commit

Undo log saves the pre-modified data and saves it to memory, reads the contents during rollback (thus achieving atomicity), redolog saves the modified data (backup of new data, but also redolog backup), writes to disk during transaction commit, thus ensuring persistence

4-slow query log

Summary of database query speed is a major factor affecting project performance, for the database, in addition to optimizing the SQL, it is more important to find the SQL that needs to be optimized. How to find inefficient SQL is the main purpose of writing this article.

The MySQL database has a "slow query log" function, which is used to record the SQL whose query time exceeds a certain set value, which will greatly help us to quickly locate the problem and prescribe the right medicine. As for how much query time is slow, each project and business has different requirements. Traditional enterprise software allows query time to be higher than a certain value, but if you put this standard on an Internet project or a website with a large number of visitors, it is estimated to be a bug, or even upgrade to a functional defect.

To avoid misleading readers, it is stated that the discussion of this article is limited to Win 64-bit + MySQL 5.6. I have not tried other platforms or database types and versions, so I will not repeat them.

With regard to the slow query log, the setting log feature mainly involves the following parameters:

Slow_query_log: whether to enable slow log function (required) long_query_time: if it exceeds the set value, it will be regarded as slow query and recorded in the slow log file (required) log-slow-queries: slow log file (not available). Automatically create a [hostname]-slow.log file in\ data\, that is, only the above three conditions are met. The slow query function can be turned on or off correctly.

5. The basis of master-slave replication of binary logs: binlog logs and relaylog logs

What is MySQL master-slave replication? simply speaking, it ensures that the data of master SQL (Master) and slave SQL (Slave) are consistent. After inserting data into Master, Slave will automatically synchronize the modified data from Master (with a certain delay). In this way to ensure data consistency, that is, master-slave replication.

There are two ways for MySQL5.6 to start master-slave replication: log-based (binlog) and GTID-based (global transaction identifier). This article only deals with master-slave configuration based on log binlog

Replication principle 1. Master records data changes in the binary log (binary log), that is, the files specified by the configuration file log-bin. These records are called binary log events (binary log events) 2. Slave reads the binary log events in Master and writes it to its relay log (relay log) 3 through the Slave O thread, Slave redoes the events in the relay log, and executes the event information in the relay log one by one locally. Complete the local storage of the data so that the changes are reflected in its own data (data playback)

1. What is binlog binlog is a file in binary format, which is used to record the information of SQL statements that users update to the database, such as SQL statements that change database tables and change contents will be recorded in binlog, but queries on database tables and other contents will not be recorded.

By default, binlog logs are in binary format and cannot be viewed using commands from the text view tool (for example, cat,vi, etc.), but with mysqlbinlog parsing.

The role of 2.binlog when data is written to the database, it will also write updated SQL statements to the corresponding binlog file, which is the binlog file mentioned above. When using mysqldump backup, you only complete the data for a period of time, but if you suddenly find that the database server fails after the backup, you will need to use the binlog log at this time.

The main function is for master-slave replication of database and incremental recovery of data.

1. What is binlog? Record the addition, deletion and modification of the database, not the binary log of the query. two。 Function: for data synchronization. 3. How to enable the binlog log feature in the mysql configuration file my.cnf, add the log_bin parameter to enable the binlog log, and you can also specify the file name of the binlog log by assigning values. Examples are as follows:

[root @ DB02 ~] # grep log_bin / etc/my.cnf log_bin = / application/mysql/logs/dadong-bin

Log_bin

[root @ DB02 ~] # hint: you can also name it by "log_bin = / application/mysql/logs/dadong-bin". Why refresh binlog if the directory exists? Find the recovery tipping point for complete data and binlog files.

Summary

The binlog and relay log logs of the mysql database play an important role, and relay log only exists in the slave library of mysql. Its function is to record the binlog received by the io process in the slave library from the master library, and then wait for the sql process of the slave library to read and apply to ensure master-slave synchronization, but both the binlog master library and the slave library (slave) can exist to record SQL statements that occur or potentially change the data. And saved in binary form on disk, so you can use binlog to back up and restore the database in real time.

1. What is binlog binlog is a file in binary format, which is used to record the information of SQL statements that users update to the database, such as SQL statements that change database tables and change contents will be recorded in binlog, but queries on database tables and other contents will not be recorded.

By default, binlog logs are in binary format and cannot be viewed using commands from the text view tool (for example, cat,vi, etc.), but with mysqlbinlog parsing.

The role of 2.binlog when data is written to the database, it will also write updated SQL statements to the corresponding binlog file, which is the binlog file mentioned above. When using mysqldump backup, you only complete the data for a period of time, but if you suddenly find that the database server fails after the backup, you will need to use the binlog log at this time.

The main function is for master-slave replication of database and incremental recovery of data.

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.