MySQL restores the specified tables and libraries from a full-database backup sample _mysql

MySQL restores the specified tables and libraries sample from the backup database

How do you restore only one library in the official Mysqldump tool?

Full-Library Backup

[Root@he1 ~]# mysqldump-uroot-p--single-transaction-a--master-data=2 >dump.sql

Restore only the contents of the ERP library

[Root@he1 ~]# Mysql-uroot-pmanager ERP--one-database <dump.sql

We can see that the main parameter used here is the--one-database-o parameter, which greatly facilitates our recovery flexibility.

Then how to extract a table from the full database backup, the whole library recovery, and then restore a table of the library can also be a big library is very troublesome, then we can use regular expressions for rapid extraction, the implementation of the following methods:

To extract the table structure of a T-table from a full-library backup

Root@he1 ~]# sed-e '/./{h;$!d;} '-E ' x;/create table ' t '/!d;q ' dump.sql
 
DROP table IF EXISTS ' t ';
/*!40101 set@saved_cs_client   =@ @character_set_client * *;
/*!40101 setcharacter_set_client = UTF8 * *;
CREATE TABLE ' t ' (
 ' id ' int () NOT NULL auto_increment,
 ' age ' tinyint (4) NOT null DEFAULT ' 0 ',
 ' name ' varchar Not NULL default ',
 PRIMARY KEY (' id ')
) engine=innodbauto_increment=4 DEFAULT Charset=utf8;
/*!40101 setcharacter_set_client = @saved_cs_client * *;
 

Extract the contents of the T table from a full-library backup

[Root@he1 ~]# grep ' INSERT into ' t ' dump.sql
INSERT into ' t ' VALUES (0,0, '), (1,0, ' AA '), (2,0, ' BBB '), (3,25, ' Helei ');

Thank you for reading, I hope to help you, thank you for your support for this site!