Introduction to MySQL new feature archiving

MySQL 8.0.17 was released. After reading the release note, I found that the redo log archiving function was restored as expected. The reason why it is called "recovery" is because it existed in a very old version of InnoDB (versions before MySQL 4.0.6), and was canceled later. At that time, redo log mirror was still supported, and older MySQL DBAs may still have it. impression, but these two functions were of little use at the time, so they were cancelled.

Proposal tutorial:MySQL database introductory video tutorial

## This time, InnoDB restarts the redo log archiving function, follow According to the development team, it is mainly to solve the problem of backup consistency. This is what the document says:

Backup utilities that copy redo log records may sometimes fail to keep pacewith redo log generation while a backup operation is in progress, resultingin lost redo log records due to those records being overwritten. The redolog archiving feature addresses this issue by sequentially writing redo logrecords to an archive file. Backup utilities can copy redo log records fromthe archive file as necessary, thereby avoiding the potential loss of data.
in lost redo log records due to those records being overwritten. The redo
log archiving feature addresses this issue by sequentially writing redo log
records to an archive file. Backup utilities can copy redo log records from
the archive file as necessary, thereby avoiding the potential loss of data.

In short,

the backup speed cannot keep up with the speed of redo log generation. As a result, the redo log is overwritten, and then the backup cannot guarantee consistency. With redo log archiving, you can start the redo log archive synchronously when the backup starts, and stop the redo log archive synchronously when the backup ends. This way you can avoid this problem , and you can use the data generated during this period after the backup is completed. redo log for data recovery.

To enable the redo log archiving function, just set the

innodb_redo_log_archive_dirs option. This option can support online dynamic modification, for example:

[root@yejr.me]> SET GLOBAL innodb_redo_log_archive_dirs = "redolog-archiving-for-backup:/data/mysql8-redologs/";

Specify

/ data/mysql8-redologs/ The directory is used as the redo log archive storage path, and the label is specified as "redolog-archiving-for-backup", that is, this is the redo log archive storage directory dedicated to backup. .

We can also designate another directory for future physical replication based on redo log (I am guessing, it may not be implemented so soon).

[root@yejr.me]> SET GLOBAL innodb_redo_log_archive_dirs = "redolog-archiving-for-backup:/data/mysql8-redologs1/;redolog-archiving-for-repl:/data/mysql8-redologs2";

Option

innodb_redo_log_archive_dirsYou can specify multiple directories as archive redo log storage locations. However, this option has several limitations:

After setting it up, you can start redo log archiving.

The first parameter is a label we defined before, and the second parameter is the subdirectory under the directory corresponding to the label, which is

"/data/mysql8-redologs/20190722". We can see such a redo log archive file in the corresponding directory:

[root@yejr.me]> ls -l /data/mysql8-redologs/20190722-r--r-----. 1 mysql mysql 0 Jul 22 20:54 archive.f0ff5743-97be-11e9-a5d6-0050568bba82.000001.log

The string of characters often in the file name is the UUID of this instance. The size of the file at this time is 0 bytes.

We launch a sysbench oltp test in another session. After executing the sysbench test, we stopped the redo log archiving work:

[root@yejr.me]> DO innodb_redo_log_archive_stop();Query OK, 0 rows affected (0.00 sec)

I recorded the changes in the redo log LSN before and after the test as follows:

# 测试前的LSNLOG---Log sequence number          27938813989...# 测试后的LSNLOG---Log sequence number          27945024531
---
Log sequence number          27938813989
...

# 测试后的LSN
LOG
---
Log sequence number          27945024531

The difference between the two LSNs is: 6210542 byte.

Then we check the size of the redo log archive file:

[root@yejr.me]> ls -l /data/mysql8-redologs/20190722-r--r-----. 1 mysql mysql 6213632 Jul 22 21:19 archive.f0ff5743-97be-11e9-a5d6-0050568bba82.000001.log

We can see that the file size is 6213632 bytes, which is only 3090 bytes different from the 6210542 bytes above. The redo log generated by the test is of comparable size. We can use this redo log for data recovery later (however, the corresponding official tools have not been developed yet, so we will wait and see).

Under normal circumstances, redo log archiving has a relatively small impact on performance (sequential writing). In scenarios with a large number of highly concurrent transactions, the impact on performance may be slightly greater, but don’t worry too much. In the future I'll do a performance comparison test when I get the chance.

Before departure, Yueyue reminded me that the backup tool of MySQL Enterprise Edition already supports redo archiving in advance. I hope Percona Xtrabackup can also support it as soon as possible.

Finally, let me say one more thing. You can also notice that after MySQL version 8.0, it becomes more and more similar to ORACLE. With ORACLE, the most successful commercial database, in front of us, we have every reason not to worry about the future of MySQL.

The above is the detailed content of Introduction to MySQL new feature archiving. For more information, please follow other related articles on the PHP Chinese website!