You can use the rollback feature of TencentDB for MariaDB to view historical data. To restore your database instance locally, restore the historical data by following the steps in this document.
If you need to restore the database instance locally, please ensure that the basic configuration of the server meets the following requirements:
Take installation of MariaDB 10.0.10 as an example:
vi /etc/yum.repos.d/mariadb-10.0.10.repo): # MariaDB 10.0 CentOS repository list - created 2016-05-30 02:16 UTC # http://downloads.mariadb.org/mariadb/repositories/ [mariadb] name = MariaDB # baseurl = http://yum.mariadb.org/10.0/centos7-amd64 baseurl = http://archive.mariadb.org/mariadb-10.0.10/yum/centos6-amd64/ gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=0
yum makecache yum info MariaDB-server
yum install MariaDB-server
If the system prompts a conflict with a legacy version, you need to remove the previously installed package by running
yum remove mariadb-libsfor example.
yum install MariaDB-client
mysqlagent/bindirectory by default. You can also install it in the
/usr/bindirectory and import it as an environment variable.
yum install -y lz4 percona-xtrabackup yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm yum install percona-xtrabackup
In the TencentDB for MariaDB Console, click an instance name to enter the instance management page and get the backup download address on the Backup and Restore tab.
Sample of a download command:
wget --content-disposition 'http://1x.2xx.0.27:8083/2/noshard1/set_1464144850_587/1464552298xxxxxxxx'
lz4 -d set_1464144850_587.1464552298.xtrabackup.lz4
xtrabackuptmpwith xbstream tool
mkdir xtrabackuptmp/ mv set_1464144850_587.1464552298.xtrabackup xtrabackuptmp/ xbstream -x < set_1464144850_587.1464552298.xtrabackup
After the decompression, the directories and files are as shown below:
innobackupexto apply logs
mkdir /root/dblogs_tmp innobackupex --apply-log --use-memory=1G --tmpdir='/root/dblogs_tmp/' /root/xtrabackuptmp/
After the operation succeeds,
Completed OK!! will be displayed as shown below:
service mysql stop
Clear data files (in data directories, tablespace directories, and log directories):
mkdir /var/lib/mysql-backup mv /var/lib/mysql/* /var/lib/mysql-backup
Modify the database parameter file
(/etc/my.cnf.d/server.cnf). For specific parameter values, please see parameters in the extracted
backup-my.cnf file. Do not directly replace the parameter file with
[mysqld] skip-name-resolve datadir=/var/lib/mysql innodb_checksum_algorithm=innodb innodb_log_checksum_algorithm=innodb innodb_data_file_path=ibdata1:2G:autoextend innodb_log_files_in_group=4 innodb_log_file_size=1073741824 innodb_page_size=4096 innodb_log_block_size=512 innodb_undo_tablespaces=0
innobackupexto load the image
innobackupex --defaults-file=/etc/my.cnf --move-back /root/xtrabackuptmp/
After loading succeeds,
completed OK! will be displayed as shown below:
chmod 777 -R /var/lib/mysql service start mysql
If you fail to start the database, you need to check and fix the error, and then try again.
After starting the database, you may need to connect to the database with the original account and password to view data.
Transparent Data Encryption (TDE) is currently supported only in Percona 5.7. You can access it in TencentDB for MariaDB. Please download and install the critical tool needed by the restoration. Below is the encryption process:
For more information, please see Enter the cold backup file download directory and decompress the file with LZ4 and Decompress the file to a temporary directory
xtrabackuptmp with xbstream tool.
In this example, the backup file is decompressed to the temporary directory
./backup_dir. LZ4 is installed in the
mysqlagent/bin directory by default. You can also install it in the
/usr/bin directory and import it as an environment variable.
You can use an API of Key Management Service (KMS) for this step.
innobackupex --apply-log --rebuild-indexes --use-memory=1G --tmpdir=/tmp ./backup_dir/
For more information, please see Stop database and clear data file.
Before decrypting the data, you need to query the data key ciphertext in Data Security > Connection Encryption on the instance management page in the TencentDB for MariaDB Console. Then, you can use either of the following two schemes to decrypt the data key ciphertext to get the data key plaintext.
./kms_tool.pyprovided by Tencent Cloud to get the data key plaintext.
After getting the data key plaintext, you can use either of the following two schemes to generate the data key file.
./keyring_toolprovided by Tencent Cloud to generate the data key file. The basic command format of
./keyring_tool "[ciphertext]" [File Path].
libboost_program_options.so.1.53.0. If this lib does not exist in the system, you need to run
export LD_LIBRARY_PATH=.:$LD_LIBRARY_PATHfirst before using
./innobackupextool to apply the backup file
The figure below shows performing the
apply operation on the backup file through
./innobackupex till the end. During the operation, you need to use
--keyring-file-data=key_file to specify the key by entering an absolute path.
xtrabackup mentioned in this document is the xtrabackup built in Tencent Cloud's proprietary TDSQL, which is stored in the
xtrabackup directory in the TDSQL installation package directory by default.
Below is a demo of using this tool:
./innobackupex --apply-log --use-memory=1G --tmpdir=/tmp --keyring-file-data=/data/home/test/key_file ./backup/
./innobackupextool to copy the backup file to the data directory
The figure below shows moving the backup file with
./innobackupex. You are recommended to use the permissions of the user who starts MySQL.
Below is a demo of using this tool:
./innobackupex --defaults-file='/data/home/seven/tdsqlinstall/percona-5.7.17/etc/my_8003.cnf' --move-back ./backup_dir/
keyring_filetool to configure the generated key file to MySQL
The figure below shows how to configure the generated key file to MySQL. Please pay attention to the configuration in the red box. You are recommended to run
keyring_file with the permissions of the user who starts MySQL.
The figure below shows the start script that comes with TencentDB. You can also use other schemes to start MySQL.
After the encrypted backup is successfully restored, you can directly access the encrypted table. If the key is missing, the backup can still be restored, but the error message
can't find master key from keying, please check keyring plugin is loaded will be displayed (for open-source MySQL or Percona, "Error" will be displayed) if you access the encrypted table.