MySQL restores the specified table and database example from full-database backup. mysql example

MySQL restores the specified table and database example from full-database backup. mysql example

MySQL restores the specified table and database example from the backup database

In the official Mysqldump tool, how does one restore a database?

Full database backup

[Root @ HE1 ~] # Mysqldump-uroot-p -- single-transaction-A -- master-data = 2> dump. SQL

Only restore the content of the erp Database

[Root @ HE1 ~] # Mysql-uroot-pMANAGER erp -- one-database <dump. SQL

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

So how can we extract a table from the full database backup, recover the whole database, and restore a small table database, then we can use regular expressions for quick extraction. The specific implementation method is as follows:

Extract the table structure of table t from full database 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(10) NOT NULL AUTO_INCREMENT, `age` tinyint(4) NOT NULL DEFAULT '0', `name` varchar(30) NOT NULL DEFAULT '', PRIMARY KEY (`id`)) ENGINE=InnoDBAUTO_INCREMENT=4 DEFAULT CHARSET=utf8;/*!40101 SETcharacter_set_client = @saved_cs_client */; 

Extract table t content from full database backup

[Root @ HE1 ~] # Grep 'insert INTO 't''dump. SQL
Insert into 't'values (, ''), (, 'A'), (, 'bbb '), (, 'helei ');

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!