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

This article will explain in detail what to pay attention to when making a backup of MYSQL Xtrabackup, the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

As we all know, Xtrabackup is an important backup tool in mysql, but there is a problem in database backup, especially in MYSQL backup with large memory, that is, innodb_buffer_pool exists. After the backup MYSQL is restored, the data of innodb _ buffer_pool will no longer appear on the restored database. The larger the memory and the busy MYSQL, there will be a buffer period after data recovery, which needs to be warmed up for a period of time. Generally speaking, we all want the backed-up data to be restored with the data in memory. In fact, MYSQL itself has this setting, that is, when the computer is turned off and turned on, the innodb buffer pool is written to files, read these files at the beginning, and loaded into memory.

But to the backup is not very realistic, in the end, for this function, the database can not be turned off, in the backup bar.

Since XTRABACKUP 2.4.13, xtrabackup software has been able to back up innodb_buffer_pool data to backup and load it into memory during recovery.

This feature in the large memory and the urgent need to recover after the use of MYSQL is very meaningful, imagine you restore a 128G MYSQL, the query is not warm-up, during this period of query will start to go to disk, of course, this is not a professional practice, and during this time there are likely to be some problems you do not want to encounter.

So XTRABACKUP provides a backup of the innodb_buffer_pool directly when it is backed up, and when it is restored, it will be restored to the MYSQL. After the restored MYSQL is started, your innodb_buffer_pool will have the same innodb_buffer_pool as the source server to deal with a large number of queries coming soon.

We can make relevant backups using the following command

Xtrabackup-defaults-file=/etc/my.cnf-user=root-password='password'-socket=/data/mysql/mysql.sock-backup-dump-innodb-buffer-pool-target-dir=/data/backup/2019 08-31

Added-- dump-innodb-buffer-pool but some students complained that this is a junk invalid command, using this command to restore the MYSQL does not have the data of innodb_buffer_pool as expected.

Is there a problem with the order, or.

In fact, this function is provided by MYSQL 5.7. XTRABACKUP only embodies this function in XTRABACKUP.

What on earth caused it? it's a big deal here.

How does my backup above prove that I backed up innodb_buffer_pool?

SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status'

Through the above command, you can show the time of your last dump buffer pool

It proves that I did buffer_pool dump down at 6:59 in the morning, during the backup.

So you can prove that the parameter is valid, or you can correct your recovered data by looking at the bufferpooldata of the database you have recovered.

OK so far, we can prove that this parameter is valid and what causes some students' commands to be invalid.

You can try to open the following parameters

SET GLOBAL innodb_buffer_pool_dump_now=ON

By default, this parameter is off.

At the same time, you can also set the number of buffer_ pools [mysqld] innodb_buffer_pool_dump_pct=40 to be download when backing up.

So if you can't innodb_buffer_pool download when backing up, the main reason is whether the parameter of your online download buffer_pool is on.

In fact, sometimes, the operation of some things seems to be unsuccessful and inexplicable. In fact, if you dig deeper and look down, you can mostly find the problem.

MYSQL Xtrabackup backup about what attention to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.