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

Source: Internet
Author: User

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!

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.