Restore data from a physical backup file of an apsaradb RDS for MySQL instance in the Ubuntu operating system -

Overview

This article describes how to restore apsaradb RDS for MySQL physical backup files in a Ubuntu 16.04 system.

Detail

Alibaba Cloud reminds you that:

Before you perform operations that may cause risks, such as modifying instance configurations or data, we recommend that you check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.

You can modify the configurations and data of instances including but not limited to Elastic Compute Service (ECS) and Relational Database Service (RDS) instances. Before the modification, we recommend that you create snapshots or enable RDS log backup.

If you have authorized or submitted sensitive information such as the logon account and password in the Alibaba Cloud Management Console, we recommend that you modify such information in a timely manner.

During the recovery operation, check whether the data directory is /var/lib/mysql. The following are the recovery operations for two situations.

The data directory is /var/lib/mysql.

Perform the following steps to restore data from a backup file:

Note: switch to the root user before running the command in the instance.

Log on to the instance and run the following command to add your personal software package document to the source list:
```
sudo add-apt-repository 'deb http://archive.ubuntu.com/ubuntu trusty universe'
```
Note: You can also directly put deb http://archive.ubuntu.com/ubuntu The trusty Union field is added to the /etc/apt/sources.list file.
Run the following command to update the apt-get Database:
```
apt-get update
```
Run the following command to unload mysql-common software package:
```
apt-get autoremove mysql-common
```
Run the following commands to verify that MySQL 5.6 version does not exist in the system:
```
apt-cache search mysql | grep mysql-server
```
Run the following command to install mysql-server-5.6 software package.
```
apt-get install mysql-server-5.6
```
Run the following command to check whether a MySQL process exists in the system and record the process PID if it does.
```
ps -ef |grep mysql
```
Run the following command to terminate the MySQL process:
```
kill -9 [$MySQL_PID]
```
Note:[$MySQL_PID] is the PID of the MySQL process you obtained in the previous step.
Run the following command to delete the data directory of the database:
```
rm -rf /var/lib/mysql/*
```
Note: if the server already uses this directory as the database Directory, do not use this server for recovery operations.

Run the following command to download the installation package.

wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb

Run the following command to install the installation package.
```
sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
```
Run the following command to install the percona-xtrabackup-24 file.
```
apt-get install percona-xtrabackup-24
```
Run the following command to download the installation package.
```
wget -c '[$Bak_File]' -O [$Custom_File].tar.gz
```
Note:
- [$Bak_File] is the external download URL data backup files.
- [$Custom_File] is the custom file name.
Run the following command to edit the my5.6.cnf file in text format:
```
vi /etc/mysql/conf.d/my5.6.cnf
```

Add the following content to the my5.6.cnf file.

[mysqld]
innodb_checksum_algorithm=crc32
innodb_data_file_path=ibdata1:200M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=524288000
innodb_undo_directory=/var/lib/mysql/
basedir=/usr
datadir=/var/lib/mysql
innodb_undo_tablespaces=0
server_id=999098802
skip-grant-tables=1
sql_mode=''
socket=/var/lib/mysql/mysql.sock
log-error=/var/lib/mysql/error1.log
explicit_defaults_for_timestamp=true

Run the following command to decompress the package to the specified directory.
```
tar -zxvf a.tar.gz -C /var/lib/mysql
```
Note: assume that the a.tar.gz package is downloaded to the /root directory.

Run the following commands in sequence to restore the decompressed backup file.

cd /var/lib/mysql   
innobackupex --defaults-file=/etc/mysql/conf.d/my5.6.cnf --apply-log /var/lib/mysql

Run the following command to change the owner of the backup-my.cnf file to a user with permissions to manage the user-created MySQL instance:
```
chown -R mysql:mysql .
```
Run the following commands to verify that the MySQL process can be started:
```
mysqld --defaults-file=/etc/mysql/conf.d/my5.6.cnf --user=mysql
```
Note: the configuration file my5.6.cnf contains the skip-grant-tables=1 parameter. Therefore, a password is not required to start the MySQL process.
Run the following command to go to the MySQL interactive page.
```
mysql --socket=/var/lib/mysql/mysql.sock
```
Run the following SQL statement to refresh the database permission table:
```
flush privileges;
```
Run the following SQL statements to set the username and password.
```
set PASSWORD FOR 'root'@'127.0.0.1'=PASSWORD('[$Password]');
```
Note: [$Password] is the Password you set for the root user.
Run the following SQL statement to exit the database:
```
exit
```
Run the following command to obtain information about the MySQL process:
```
ps -ef |grep mysql
```
Run the following command to terminate the MySQL process:
```
kill -9 [$PID]
```
Note:[$PID] is the PID of the MySQL process obtained in the previous step.
Run the following command to edit the my5.6.cnf file in text format:
```
vi /etc/mysql/conf.d/my5.6.cnf
```
On the text editing page, comment out the skip-grant-tables=1 field.

Run the following command to start the MySQL process:

mysqld --defaults-file=/etc/mysql/conf.d/my5.6.cnf --user=mysql

Run the following command and enter the password and account to confirm that you can log on to the MySQL interactive interface.
```
mysql -uroot -h127.0.0.1 -p[$Password] --socket=/var/lib/mysql/mysql.sock
```

The data directory is not /var/lib/mysql.

Perform the following steps to restore data from a backup file:

Note: switch to the root user before running the command in the instance.

Log on to the instance and run the following command to add your personal software package document to the source list:
```
sudo add-apt-repository 'deb http://archive.ubuntu.com/ubuntu trusty universe'
```
Note: You can also directly put deb http://archive.ubuntu.com/ubuntu The trusty Union field is added to the /etc/apt/sources.list file.
Run the following command to update the apt-get Database:
```
apt-get update
```
Run the following command to unload mysql-common software package:
```
apt-get autoremove mysql-common
```
Run the following commands to verify that MySQL 5.6 version does not exist in the system:
```
apt-cache search mysql | grep mysql-server
```
Run the following command to install mysql-server-5.6 software package.
```
apt-get install mysql-server-5.6
```
Run the following command to check whether a MySQL process exists in the system and record the process PID if it does.
```
ps -ef |grep mysql
```
Run the following command to terminate the MySQL process:
```
kill -9 [$MySQL_PID]
```
Note:[$MySQL_PID] is the PID of the MySQL process you obtained in the previous step.
Run the following command to delete the specified directory.
```
rm -rf /var/lib/mysql
```

Run the following command to download the installation package.

wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb

Run the following command to install the installation package.
```
sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
```
Run the following command to install the percona-xtrabackup-24 file.
```
apt-get install percona-xtrabackup-24
```
Run the following command to download the installation package.
```
wget -c '[$Bak_File]' -O [$Custom_File].tar.gz
```
Note:
- [$Bak_File] is the external download URL data backup files.
- [$Custom_File] is the custom file name.
Run the following command to create a data directory.
```
mkdir /data/mysql -p
```
Run the following command to change the directory owner and group.
```
chown -R mysql:mysql /data/mysql
```
Run the following command to authorize the Data Directory:
```
chmod 700 /data/mysql
```
Run the following command to edit the my5.6.cnf file in text format:
```
vi /etc/mysql/conf.d/my5.6.cnf
```

Add the following content to the my5.6.cnf file.

[mysqld]
innodb_checksum_algorithm=crc32
innodb_data_file_path=ibdata1:200M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=524288000
innodb_undo_directory=/data/mysql/
basedir=/usr
datadir=/data/mysql
innodb_undo_tablespaces=0
server_id=999098802
skip-grant-tables=1
sql_mode=''
socket=/data/mysql/mysql.sock
log-error=/data/mysql/error1.log
explicit_defaults_for_timestamp=true

Run the following command to decompress the package to the specified directory.
```
tar -zxvf a.tar.gz -C /data/mysql
```
Note: assume that the a.tar.gz package is downloaded to the /root directory.

Run the following commands in sequence to restore the decompressed backup file.

cd /data/mysql   
innobackupex --defaults-file=/etc/mysql/conf.d/my5.6.cnf --apply-log /data/mysql

Run the following command to change the owner of the backup-my.cnf file to a user with permissions to manage the user-created MySQL instance:
```
chown -R mysql:mysql .
```
Run the following command to create a short-lived connection:
```
ln -s /data/mysql /var/lib/mysql
```
Run the following command to edit the specified file.
```
vi /etc/apparmor.d/tunables/alias
```
On the edit page, add the following code.
```
alias /var/lib/mysql/ -> /data/mysql/,
```
Run the following command to reload the configuration file:
```
sudo /etc/init.d/apparmor reload
```
Run the following commands to verify that the MySQL process can be started:
```
mysqld --defaults-file=/etc/mysql/conf.d/my5.6.cnf --user=mysql
```
Note: the configuration file my5.6.cnf contains the skip-grant-tables=1 parameter. Therefore, a password is not required to start the MySQL process.
Run the following command to go to the MySQL interactive page.
```
mysql --socket=/data/mysql/mysql.sock
```
Run the following SQL statement to refresh the database permission table:
```
flush privileges;
```
Run the following SQL statements to set the username and password.
```
set PASSWORD FOR 'root'@'127.0.0.1'=PASSWORD('[$Password]');
```
Note: [$Password] is the Password you set for the root user.
Run the following SQL statement to exit the database:
```
exit
```
Run the following command to obtain information about the MySQL process:
```
ps -ef |grep mysql
```
Run the following command to terminate the MySQL process:
```
kill -9 [$PID]
```
Note:[$PID] is the PID of the MySQL process obtained in the previous step.
Run the following command to edit the my5.6.cnf file in text format:
```
vi /etc/mysql/conf.d/my5.6.cnf
```
On the text editing page, comment out the skip-grant-tables=1 field.

Run the following command to start the MySQL process:

mysqld --defaults-file=/etc/mysql/conf.d/my5.6.cnf --user=mysql

Run the following command and enter the password and account to confirm that you can log on to the MySQL interactive interface.
```
mysql -uroot -h127.0.0.1 -p[$Password] --/data/mysql/mysql.sock
```

Application scope

ApsaraDB RDS for MySQL