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

This article mainly gives you a brief talk about some of the new features of MySQL 8.0, related technical terms you can check online or find some related books to supplement, here is not involved, we will go straight to the topic, I hope some of the new features of MySQL 8.0 This article can bring you some practical help.

This time, InnoDB restarts redo log archiving function, according to the development team, mainly to solve the problem of backup consistency. It says in the file:

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 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 short, backups cannot keep up with redo log generation, resulting in redo logs being overwritten and backups not being consistent. With redo log archiving, redo log archiving can be started synchronously when backup is started, and redo log archiving can be stopped synchronously when backup is finished. In this way, this problem can be avoided. After backup is finished, redo log generated during this period can be used for data recovery.

To enable redo log archiving, simply set the innodb_redo_log_archive_dirs option, which supports online dynamic modification, such as:

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

Specify/data/mysql8-redologies/directory as the redo log archive storage path, and specify label as "redolog-archiving-for-backup", that is, this is the redo log archive storage directory dedicated to backups.

We can also specify another directory for future redo log-based physical replication purposes (I guess, maybe not 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";

The innodb_redo_log_archive_dirs option allows you to specify multiple directories as archive redo log locations. However, this option has several limitations:

After setting up, you can start redo log archiving.

The first parameter is a label we defined earlier, and the second parameter is the subdirectory of the label's corresponding directory, which is "/data/mysql8-redologies/20190722". We can see this 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 usual string of characters in the file name is the UUID of this instance. The file size is 0 bytes.

We launch a sysbench oltp test in another session. After executing sysbench tests, we stop redo log archiving:

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

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

#LSNLOG before test---Log sequence number 27938813989...# LSNLOG after test---Log sequence number 27945024531---Log sequence number 27938813989...# LSNLOG after test---Log sequence number 27945024531

The difference between the two LSNs is 6210542 bytes.

Then we look at the redo log archive file size:

[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

You can see that the file size is 6213632 bytes, which is only 3090 bytes different from the 6210542 bytes above, and is equivalent to the redo log size generated by this test. Later we can use this redo log to do data recovery (however, the corresponding official tools have not yet been developed, wait and see).

In general, redo log archiving has a small impact on performance (sequential writing). In a scenario with a large number of concurrent transactions, the impact on performance may be slightly greater, but don't worry too much. I'll do a performance comparison test later.

Some of the new features of MySQL 8.0 will be mentioned to you first. For other related issues, you can continue to pay attention to our industry information. Our section content captures some industry news and expertise to share with you every day.

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.