mysqldump restoring the specified library

Recovery of the specified data from a fully-prepared data, there are many ways to remove the data database name and table name from a fully-prepared data file, and so on, you can also restore the specified data by a fully-specified library name, this method is simple and fast, this method restores the unwanted library, but does not recover the data.

The process is as follows:

1. Check the status of the database before it has been backed up

MariaDB [(None)]> show databases;

+--------------------+

| Database |

+--------------------+

| bb |

| CC |

| DD |

| Grafana |

| Information_schema |

| MySQL |

| Performance_schema |

| Test |

+--------------------+

MariaDB [(None)]> use Grafana;

MariaDB [grafana]> Show tables;

+--------------------+

| Tables_in_grafana |

+--------------------+

| Api_key |

| Dashboard |

| Dashboard_snapshot |

| Dashboard_tag |

| Data_source |

| Migration_log |

| org |

| Org_user |

| Star |

| user |

+--------------------+

Rows in Set (0.00 sec)

MariaDB [test]> select * from AA;

+------+-------+

| ID | S_num |

+------+-------+

| 1 | 1 |

| 2 | 2 |

| 3 | 33331 |

| 4 | 33334 |

+------+-------+

4 rows in Set (0.00 sec)

2, a full sample of data mysqldump mode

/usr/local/mysql/bin/mysqldump-s/tmp/mysql3306.sock-p--all-databases >aa.sql

3. Delete the Business library:

DROP database xxxx

4, restore the specified library, when you restore the specified library, you need to add the library to be restored, otherwise you will be prompted Error 1049 (42000): Unknown database ' Test '

MariaDB [(None)]> CREATE DATABASE test;

Query OK, 1 row Affected (0.00 sec)

MariaDB [(None)]> show databases;

+--------------------+

| Database |

+--------------------+

| Information_schema |

| MySQL |

| Performance_schema |

| Test |

+--------------------+

4 rows in Set (0.00 sec)

5. Restore the specified library

/usr/local/mysql/bin/mysql-s/tmp/mysql3306.sock-p--one-database Test <aa.sql

Summarize the phenomena that exist:

grep "CREATE DATABASE" All.sql |grep-v "VERSION" by checking the backup set information

CREATE DATABASE/*!32312 IF not exists*/' cc '/*!40100 DEFAULT CHARACTER SET UTF8 */;

CREATE DATABASE/*!32312 IF not exists*/' MySQL '/*!40100 DEFAULT CHARACTER SET UTF8 */;

CREATE DATABASE/*!32312 IF not exists*/' test '/*!40100 DEFAULT CHARACTER SET UTF8 */;

CREATE DATABASE/*!32312 IF not exists*/' test01 '/*!40100 DEFAULT CHARACTER SET UTF8 */;

CREATE DATABASE/*!32312 IF not exists*/' test02 '/*!40100 DEFAULT CHARACTER SET UTF8 */;

CREATE DATABASE/*!32312 IF not exists*/' wl_tj56_dict '/*!40100 DEFAULT CHARACTER SET UTF8 */;

You can see the database execution information (this argument is not necessarily right), specify to restore a library, according to the information of the backup set to check the data of the full standby grep "CREATE database" All.sql |grep-v "VERSION", when the restored library in the middle position, Restoring the data restores the first library record and the next library information for the Restore library, and if the restored library is in the last row, only the first library and the restored library will be restored, and the restored library will return only the library name, not the data.

To quickly restore a table under a library method: http://suifu.blog.51cto.com/9167728/1830651

This article is from the "DBSpace" blog, so be sure to keep this source http://dbspace.blog.51cto.com/6873717/1884405

mysqldump restoring the specified library