Xtrabackup – Whole Focus, single table, partial backup recovery

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;

WordPress database error: [Table 'yf99682.wp_s6mz6tyggq_comments' doesn't exist]
SELECT SQL_CALC_FOUND_ROWS wp_s6mz6tyggq_comments.comment_ID FROM wp_s6mz6tyggq_comments WHERE ( comment_approved = '1' ) AND comment_post_ID = 5401 ORDER BY wp_s6mz6tyggq_comments.comment_date_gmt ASC, wp_s6mz6tyggq_comments.comment_ID ASC