This is the sixth day of my participation in the August Text Challenge.More challenges in August

Record a physical backup to restore to the production database

1. The foreplay

Docker-compose compose compose compose compose compose compose compose compose compose compose compose compose compose compose compose compose compose compose compose compose compose compose compose compose compose compose compose

Open Ali Cloud found a backup

**20210528080912_qp.xb end file, haven’t seen, say again, I am not an old bird, ignorant force

2. Start crazy Baidu

The first time to find this old brother, blog.csdn.net/weixin_4291… According to his operation the first step did not go down, to install XtraBackup, I did not say he wrote the dishes

3. Precautions (Step on pits)

The first thing to do is to find a server, online or local, in my environment

  • Ali cloud server 1 core 2G stuck me…
  • MySQL 8.0.16

Secondly, we must pay attention to the version ！！！！！！ MySQL 8.0 is not installed on a server that has MySQL 5.7 installed on it. MySQL 8.0 is installed on a server that does not have MySQL 5.7 installed on it. MySQL 8.0 is installed on a server that does not have MySQL 5.7 installed. Then restore the following login users are also the previous backup account password

4. Install the open-source software XtraBackup

Also watch out for version 2.4, which supports MySQL 5.7 but not MySQL 8.0.

Download Percona XtraBackup 8.0

Install Percona XtraBackup 8.0

    1. userootPermissions to installPercona 库
sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
Copy the code
  • 2. Start the repository
percona-release enable-only tools release
Copy the code

Output:

[root@aliyun ~]# percona-release enable-only tools release
* Disabling all Percona Repositories
* Enabling the Percona Tools repository
<*> All done!
Copy the code
    1. useyumThe installationPercona XtraBackup
yum install percona-xtrabackup-80
Copy the code
    1. Test the output version number
[root@aliyun ~]# xtrabackup -versionxtrabackup: recognized server arguments: -- Datadir =/data Xtrabackup version 8.0.23-16 based on MySQL Server 8.0.23 Linux (Revision ID: 934BC8f)Copy the code

Install the qpress

  • Direct use ofyumThe installation
yum -y install qpress 
Copy the code
  • usetarThe installation
$ wget http://www.quicklz.com/qpress-11-linux-x64.tar
$ tar -xf qpress-11-linux-x64.tar -C /usr/local/bin
$ source /etc/profile
Copy the code

Unpack thexbfile

Select a directory as the final data storage directory, in this case the use of /home/mysql.data

xbstream -x -C /home/mysql/data < ~/xxxx_data_20210528080912_qp.xb
Copy the code

Then extract the Qpress binary

–remove-original removes the original compressed file when unzipping

xtrabackup --decompress --remove-original --target-dir=/home/mysql/data
Copy the code

After the backup package is decompressed, run the apply log command.

xtrabackup --prepare  --target-dir=/home/mysql/data
Copy the code

A completed OK! It is ok, appear unusual much baidu Baidu, MY side also encountered a lot of strange strange mistake for the first time

Start the MySQL

The green unzipped version of MySQL is used here because it has bin/ mysqLD_safe

The configuration file /etc/my.cnf, the configuration of related blogs on the Internet is not available to me, I put the configuration file that I can run successfully

[mysqld]
datadir=/home/mysql/data
lower_case_table_names=1
Copy the code

To give permission

chown -R mysql:mysql /home/mysql/data
Copy the code

run

./mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --datadir=/home/mysql/data &
Copy the code

After the startup is successful, log in using the original account password