Ask questions?

  1. What is the impact of an abnormal restart of the server database?
  2. What if the database is accidentally deleted, or data in a database table is accidentally deleted?
  3. How does an update statement relate to the database logging system when executed within the database system?
  4. Is it better to have a database backup every day or every week? Next, answer some of the above questions while explaining the logging system.

Log system details:

Redo logs

Redo logs are engine-level logs and are unique to InnoDB. InnoDB’s redo log has a fixed size. For example, it can be configured as a group of 4 files (logfile-1, logfile-2, logfile-3, logfile-4). Each file size is 1GB, so it can log 4GB of operations. A circular structure that starts at the beginning and returns to the beginning at the end.

A circular structure in redo

Structure:

write pos

Redo log function (Answer question 1)

  1. In MySQL, if every update is written to disk, and the disk finds the corresponding record, and then updates, the IO cost is very high, and the search cost is very high. To solve this problem, we use redo logging, which is often called WAL in MySQL,WALIs the full name ofwrite-Ahead-LoggingThe key point isLog first, then disk. In particular, when a log is updated, the InoDB engine logs the redo log and updates the memory. The InnoDb engine updates the operation record to disk when appropriate, and this update is usually done when the system is idle.
  2. Thanks to the redo log,InnoDB can guarantee that many previously committed records will not disappear in the event of an unexpected database restartcrash-safe. As long as the physical records of the database remain in the redo log, if the server database is restarted, the data records can be restored after the database is restored.

Binlog (archive log)

The Mysql infrastructure is divided into two parts: the Server layer and the engine layer. The engine layer is responsible for storage-related matters. It says that it has its own log in the engine layer, and only in the InnoDB engine. The Server layer also has its own log, called binlog. It is written to the log by chase. Appending means that the binlog file will be switched to the next one after being written to a certain size without overwriting the previous log.

Binlog Log function (Answer questions 2)

The crash-safe feature of redo logs alone can be used to deal with database and table deletions. In some cases redo logs are ineffective, but binlogs solve these problems because binlogs record all logical operations and append them.

For example, if the staff of the company found that there was an operation of deleting table data by mistake one afternoon and asked to retrieve the data, what should they do? (Note: it is necessary to consider whether the data was deleted after the backup or before the backup. The following example is deleted before the backup.)

  1. First, find the most recent full backup, depending on how often your company’s database is backed up (some companies back up daily, some weekly, and delete periodically; many companies only keep the most recent monthly backup), and then restore that backup to temporary tables
  2. Then from the backup point in time, take out the backup binlog in turn, and put it back to the time before the mistakenly deleted table
  3. At this time, the temporary database is the same as the online database before the error deletion, and then remove the table data from the temporary data, as needed to restore to the online database.

Redo logs are compared with binlog logs

What are the differences between redo and binlog logs? In fact, a lot of above mentioned, once again summarized, deepen the impression.

  1. Redo logs are unique to InnoDB; Binlog is implemented in the Server layer of MySQL and is available to all engines.
  2. 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 one to the C field on the line ID=2”.
  3. Redo log isrings, cyclic write, space fixed will run out, use up after need to erase; Binlog can be appended. “Appending” is when belog files are written to a certain size and are switched to the next one, without overwriting the previous log.

Update statement execution flow (log relation)

Database statement:

mysql> update Student set c=c+1 where ID=2;

Copy the code

By analyzing the update statement and drawing a flowchart, problem 3 is solved.

How to set the two logging configuration for mysql during development

  • redo log

    Innodb_flush_log_at_trx_commit if set to 1, the redo log of each transaction is persisted directly to disk. I suggest you set this parameter to 1, so that the data will not be lost after MySQL restarts abnormally.

  • binlog

    When sync_binlog is set to 1, the binlog of each transaction is persisted to disk. I also recommend that you set this parameter to 1. This will ensure that MySQL mis-delete operations (delete table data, delete library data) can be recovered through binlog.

How do I view these two types of logs

Some misunderstandings and questions about the log system

  • Do you think that redo logs are enough? Why binlog?

    Answer:

    1. Redo logging is a feature only provided by the InnoDB engine.
    2. Redo logs are written in a circular pattern and are erased at a fixed size. When database or table data is accidentally deleted, the backup may not be fully restored.

  • Here is a question: if the database restarts unexpectedly at the exact moment when erasing and billing overlap, how will the new database operation be recorded? Will it erase part of the database, record it, and lose it, or wait for the restart to add data?

  • About database backup, is it better to backup once a day or once a week? How long do you usually keep backup files? Question 4

    Answer: For the database backup period, you need to consider the following metrics: data volume, increment, backup cost, recovery efficiency.

    1. If the amount of data is too large to be backed up in a day, only once a week or more
    2. If the incremental service data is backed up once a week, incremental backup may fail and the recovery time and success rate are difficult. In this case, you can backup service data once a day.
    3. Services are important and have a low tolerance for recovery time. Data rollback requirements have occurred many times in the past, and data increments are not large. Therefore, you can perform one backup a day. On the contrary, the actual business is not important, the problem can tolerate certain unavailability, incremental is not much, you can consider a week backup.

    In general, it has a lot to do with projects, requirements, scenarios.

  • Writing redo logs is also an I/O operation, and writing data updates to disk is also an I/O operation.

    Answer: Redo logs are written sequentially, so you don’t have to “find the location” to update data.

Summary and Publicity

The above content is about the database log system explanation, at the same time to solve several database log related problems I put forward at the beginning, I hope to help you better understand the learning database, if there is a problem you can always pay attention to the public number contact, learn from each other oh.