There are three methods for partial backups, which are:
1. Use regular expressions to indicate the name of the library and table to be backed up (the parameter is – include)
2. Write the name of the table or library to be backed up in a text file (parameter is –tables-file)
3. Write the name of the table or library to be backed up in the command line (The parameter is: –databases).
(Translator’s Note: No matter which database or table you back up, it is strongly recommended to back up the mysql library together, and use it when restoring.)
Use regular pattern matching to backup part of the library table, you need to use the parameter –include
innobackupex –include=’test.test*’ /backup –user=root –password=msds007
cat /tmp/tables.txt
test.t
test.testflashback2
innobackupex –tables-file=/tmp/tables.txt /backup –user=root –password=msds007
innobackupex –databases=test,mysql /backup –user=root –password=msds007
Execute preparing partial backups, which is similar to restoring individual tables (Restoring Individual Tables): use the –apply-log and –export parameters, and include the timestamp folder generated in the previous step.
innobackupex – apply-log –export /backup/2018-05-10_19-38-44
1) View the files in the backup set
[ [emailprotected] test]# pwd
/backup/2018-05-10_19-38-44/test
[ [emailprotected] test]# ll
tota l 256
-rw-r–r–. 1 root root 390 May 10 19:58 t.cfg
-rw-r–r–. 1 root root 1087 May 10 19:58 testflashback2 .cfg
-rw-r–r–. 1 root root 16384 May 10 19:58 testflashback2.exp
-rw-r—–. 1 root root 9030 May 10 19:38 testflashback2 .frm
-rw-r—–. 1 root root 98304 May 10 19:38 testflashback2.ibd
-rw-r–r–. 1 root root 16384 May 10 19:58 t .exp
-rw-r—–. 1 root root 8586 May 10 19:38 t.frm
-rw-r—–. 1 root root 98304 May 10 19:38 t .ibd
# mysqlfrm –diagnostic t.frm
# WARNING: Cannot generate character set or collation names without the –server option.
# CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
# Reading .frm file for t.frm:
# The .frm file is a TABLE.
# CREATE TABLE Statement:
–server=dba_user: [email protected] Remote server, data to be read Dictionary
/app/mysqldata/3306/data/test/tt.frm The local location where the frm source file is placed
–user=root –port=2323 323 ed on the server port starting
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY `PRIMARY` (`id`)
) ENGINE=InnoDB;
Creating Partial Backups (Creating Partial Backups)
There are three types of partial backups The methods are:
1. Use regular expressions to indicate the name of the library and table to be backed up (parameter is –include)
2. Write the name of the table or library to be backed up in a text file (The parameter is –tables-file)
3. Write the name of the table or library to be backed up in the command line (parameter: –databases).
(Translator’s note: No matter which database or table you back up, it is strongly recommended to back up the mysql library together, and use it when restoring.)
Method 1: Use- -include parameter
Use regular pattern matching to backup part of the library table, you need to use the parameter –include
innobackupex –include=’test.test*’ /backup –user=root –password=msds007
Method 2: Use the table list to back up some tables, you need to use the parameter –tables-file, the statement is similar to the following:
cat /tmp/tables.txt
test.t
test.testflashback2
innobackupex –tables-file=/tmp/tables.txt /backup –user=root –password=msds007
Method three : Use the –databases parameter
innobackupex –databases=test,mysql /backup –user=root –password=msds007
Preparing Partial Backups )
Execute preparing partial backups, which is similar to restoring individual tables (Restoring Individual Tables): use the –apply-log and –export parameters, and include the timestamp folder generated in the previous step.
innobackupex – apply-log –export /backup/2018-05-10_19-38-44
Restoring Partial Backups (Restoring Partial Backups)
1) View the files in the backup set
[ [emailprotected] test]# pwd
/backup/2018-05-10_19-38-44/test
[ [emailprotected] test]# ll
total 256
– rw-r–r–. 1 root root 390 May 10 19:58 t.cfg
-rw-r–r–. 1 root root 1087 May 10 19:58 testflashback2.cfg
-rw-r–r–. 1 root root 16384 May 10 19:58 testflashback2.exp
-rw-r—–. 1 root root 9030 May 10 19:38 testflashback2.frm
-rw-r—–. 1 root root 98304 May 10 19:38 testflashback2.ibd
-rw-r–r–. 1 root root 16384 May 10 19:58 t.exp
-rw-r—–. 1 root root 8586 May 10 19:38 t.frm
-rw-r—–. 1 root root 98304 May 10 19:38 t.ibd
2) Create a table, you can parse the table statement from the frm file
# mysqlfrm –diagnostic t.frm < br># WARNING: Cannot generate character set or collation names without the –server option.
# CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
# Reading .frm file for t.frm:
# The .frm file is a TABLE.
# CREATE TABLE Statement:
[ [email protected] ~]# mys qlfrm –server=dba_user: [email protected] /app/mysqldata/3306/data/test/tt.frm –user=root –port=2323
–server=dba_user: [email protected] remote Server, the data dictionary to be read
/app/mysqldata/3306/data/test/tt.frm The local location where frm source files are placed
–user=root –port=2323 ed on Starting the spawn server port 2323
CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY `PRIMARY` (`id`)
) ENGINE=InnoDB;
#…done.
3) Import data ALTER TABLE test.t DISCARD TABLESPACE; After this, copy t.ibd and t.exp (or t.cfg if importing to MySQL 5.6) files to database’s home, and import its tablespace: chown mysql:mysql t.ibd t.exp t.cfg ALTER TABLE test .t IMPORT TABLESPACE;