MySQL backup file. XB database backup restore

Today an old friend told me that his mysql backup file suffix .xb could not be restored, or it was restored with only tables without data, so I decided to try I did a backup and restore, and I searched the Internet for a Tencent Cloud document. Let’s follow Tencent Cloud’s

First of all

span>

Unpack the backup File

1. Because the backup file is compressed by qpress first, and then packaged by xbstream (xbstream is a packaging/unpacking tool of Percona), you should use xbstream first after downloading the backup file Unpack it. The xbstream tool can be downloaded from the official website of Percona XtraBackup or the binary package can be downloaded directly.
• Percona XtraBackup official website download and install
  Please select Percona XtraBackup 2.4.6 and above. For installation instructions, please refer to the official website documentation.

< /ol>



< p>

After selecting the corresponding system version, select online installation

< pre>yum install https://repo.percona.com/yum/percona-release-latest. noarch.rpm

yum install percona-xtrabackup-24< /pre> 

Actually the above two commands, after the installation is complete

1. After installing XtraBackup, use the xbstream command to unpack the backup file to the target directory.

xbstream -x -C /data <./test.xb /data is the restored file, and the data name is test.xb

< /p>

2. Use the qpress command to extract all the files ending with .qp in the target directory.

xtrabackup --decompress --target -dir=/data /data

3.Prepare backup file

After the backup is decompressed, you need to execute the following command to perform the apply log operation.

xtrabackup --prepare --target -dir=/data

At this time, the folder is as follows



Modify the configuration file

Due to the existing version problem, please comment out the following parameters in the decompressed file backup-my.cnf.

• innodb_checksum_algorithm
• innodb_log_checksum_algorithm
• innodb_fast_checksum
• innodb_page_size
• innodb_log_block_size
• redo_log_version



p>

Modify file attributes

Modify file attributes, and check that the file belongs to the mysql user.

chown -R mysql:mysql / data

Start the mysqld process and log in to verify

1. Start the mysqld process.

   mysqld_safe --defaults-file= /data/backup-my.cnf --user=mysql --datadir=/data &

   here To explain, I reported the error commond not found when using mysqld_safe, so I used mysqld directly. Please Baidu for the specific difference.

   2. Client login mysql authentication.

   mysql -uroot -p 

   
    I didn’t take it here There is no way to set up remote login to the root account password, so I directly use mysqldump to import the required database into sql, and then import sql locally. 
    Tencent documentation https://cloud.tencent.com/document/product/236 /33363

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

yum install percona-xtrabackup-24

xbstream -x -C /data <./test.xb /data is the restored file, the data name is test.xb

xbstream -x -C /data <./ test.xb /data is the restored file, and the data name is test.xb

xbstream -x -C /data <. /test.xb /data is the restored file, the data name is test.xb

xtrabackup --decompress --target-dir=/data /data

3.Prepare backup file

After the backup is decompressed, you need to execute the following command Perform the apply log operation.

xtrabackup --prepare --target -dir=/data

At this time, the folder is as follows



Modify the configuration file

Due to the existing version problem, please comment out the following parameters in the decompressed file backup-my.cnf.

• innodb_checksum_algorithm
• innodb_log_checksum_algorithm
• innodb_fast_checksum
• innodb_page_size
• innodb_log_block_size
• redo_log_version



p>

Modify file attributes

Modify file attributes, and check that the file belongs to the mysql user.

chown -R mysql:mysql / data

Start the mysqld process and log in to verify

1. Start the mysqld process.

   mysqld_safe --defaults-file= /data/backup-my.cnf --user=mysql --datadir=/data &

   here To explain, I reported the error commond not found when using mysqld_safe, so I used mysqld directly. Please Baidu for the specific difference.

   2. Client login mysql authentication.

   mysql -uroot -p 

   
    I didn’t take it here There is no way to set up remote login to the root account password, so I directly use mysqldump to import the required database into sql, and then import sql locally. 
    Tencent documentation https://cloud.tencent.com/document/product/236 /33363

xtrabackup --decompress --target-dir=/data /data

3. Prepare the backup file

After the backup is decompressed, you need to execute the following command to perform the apply log operation.

xtrabackup --prepare --target -dir=/data

At this time, the folder is as follows



Modify the configuration file

Due to the existing version problem, please comment out the following parameters in the decompressed file backup-my.cnf.

• innodb_checksum_algorithm
• innodb_log_checksum_algorithm
• innodb_fast_checksum
• innodb_page_size
• innodb_log_block_size
• redo_log_version



p>

Modify file attributes

Modify file attributes, and check that the file belongs to the mysql user.

chown -R mysql:mysql / data

Start the mysqld process and log in to verify

1. Start the mysqld process.

   mysqld_safe --defaults-file= /data/backup-my.cnf --user=mysql --datadir=/data &

   here To explain, I reported the error commond not found when using mysqld_safe, so I used mysqld directly. Please Baidu for the specific difference.

   2. Client login mysql authentication.

   mysql -uroot -p 

   
    I didn’t take it here There is no way to set up remote login to the root account password, so I directly use mysqldump to import the required database into sql, and then import sql locally. 
    Tencent documentation https://cloud.tencent.com/document/product/236 /33363

xtrabackup --decompress --target-dir=/data /data

xtrabackup --prepare --target-dir=/data

At this time, the folder is as follows

< p>

< h3 id=".E4.BF.AE.E6.94.B9.E9.85.8D.E7.BD.AE.E6.96.87.E4.BB.B6">Modify the configuration file

Because Existing version problems, please unzip the file backup- Comment out the following parameters in my.cnf.

• innodb_checksum_algorithm
• innodb_log_checksum_algorithm
• innodb_fast_checksum
• innodb_page_size
• innodb_log_block_size
• redo_log_version



p>

Modify file attributes

Modify file attributes, and check that the file belongs to the mysql user.

chown -R mysql:mysql / data

Start the mysqld process and log in to verify

1. Start the mysqld process.

   mysqld_safe --defaults-file= /data/backup-my.cnf --user=mysql --datadir=/data &

   here To explain, I reported the error commond not found when using mysqld_safe, so I used mysqld directly. Please Baidu for the specific difference.

   2. Client login mysql authentication.

   mysql -uroot -p 

   
    I didn’t take it here There is no way to set up remote login to the root account password, so I directly use mysqldump to import the required database into sql, and then import sql locally. 
    Tencent documentation https://cloud.tencent.com/document/product/236 /33363

xtrabackup --prepare --target-dir=/data

At this time, the folder is as follows

p>



p>

Modify the configuration file

< p>Due to the existing version problem, please comment out the following parameters in the decompressed file backup-my.cnf.

• innodb_checksum_algorithm
• innodb_log_checksum_algorithm
• innodb_fast_checksum
• innodb_page_size
• innodb_log_block_size
• redo_log_version



p>

Modify file attributes

Modify file attributes, and check that the file belongs to the mysql user.

chown -R mysql:mysql / data

Start the mysqld process and log in to verify

1. Start the mysqld process.

   mysqld_safe --defaults-file= /data/backup-my.cnf --user=mysql --datadir=/data &

   here To explain, I reported the error commond not found when using mysqld_safe, so I used mysqld directly. Please Baidu for the specific difference.

   2. Client login mysql authentication.

   mysql -uroot -p 

   
    I didn’t take it here There is no way to set up remote login to the root account password, so I directly use mysqldump to import the required database into sql, and then import sql locally. 
    Tencent documentation https://cloud.tencent.com/document/product/236 /33363

xtrabackup --prepare --target-dir=/data

chown -R mysql:mysql /data

start the mysqld process and login verification

1. Start the mysqld process.

   mysqld_safe --defaults-file= /data/backup-my.cnf --user=mysql --datadir=/data &

   here To explain, I reported the error commond not found when using mysqld_safe, so I used mysqld directly. Please Baidu for the specific difference.

   2. Client login mysql authentication.

   mysql -uroot -p 

   
    I didn’t take it here There is no way to set up remote login to the root account password, so I directly use mysqldump to import the required database into sql, and then import sql locally. 
    Tencent documentation https://cloud.tencent.com/document/product/236 /33363

< div class="code">

chown -R mysql:mysql /data