Command Center

There have been times that I needed to restore one database from a dump file that contains all of the databases from the server. There are a few ways to accomplish this goal. One way would be to grep through the entire file for the table schema and insert statements. There are some problems with this method in some environments. For example, what if a table name in the targeted databases had the same name in another database on the same instance? The logic for grep now gets increasingly more complex.

It’s a good thing that the developers at MySQL already thought of this and implemented it, --one-database. In the example below I have created 4 databases, test, test[1-3] and filled each of them with 10K rows. I run mysqldump for all databases and put them in a file called test.all.sql then truncate test2.tbl1. Using mysql with the --one-database option I restore the data to its original state.

Example:

mysql> select min(anum), max(anum) from test.tbl1;
+-----------+-----------+
| min(anum) | max(anum) |
+-----------+-----------+
| 0 | 10000 |
+-----------+-----------+
1 row in set (0.01 sec)

mysql> select min(anum), max(anum) from test1.tbl1;
+-----------+-----------+
| min(anum) | max(anum) |
+-----------+-----------+
| 10001 | 20000 |
+-----------+-----------+
1 row in set (0.01 sec)

mysql> select min(anum), max(anum) from test2.tbl1;
+-----------+-----------+
| min(anum) | max(anum) |
+-----------+-----------+
| 20001 | 30000 |
+-----------+-----------+
1 row in set (0.01 sec)

mysql> select min(anum), max(anum) from test3.tbl1;
+-----------+-----------+
| min(anum) | max(anum) |
+-----------+-----------+
| 30001 | 40000 |
+-----------+-----------+
1 row in set (0.01 sec)

shell> mysqldump -uroot -S mysql.sock --opt --all-databases > test.all.sql

mysql> use test2;

mysql> select count(*) from tbl1;
+----------+
| count(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.01 sec)

mysql> truncate table tbl1;
Query OK, 0 rows affected (0.05 sec)

mysql> select count(*) from tbl1;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)

shell> mysql -uroot -S mysql.sock --one-database test2 < test.all.sql

mysql> select min(anum), max(anum) from test.tbl1;
+-----------+-----------+
| min(anum) | max(anum) |
+-----------+-----------+
| 0 | 10000 |
+-----------+-----------+
1 row in set (0.01 sec)

mysql> select min(anum), max(anum) from test1.tbl1;
+-----------+-----------+
| min(anum) | max(anum) |
+-----------+-----------+
| 10001 | 20000 |
+-----------+-----------+
1 row in set (0.01 sec)

mysql> select min(anum), max(anum) from test2.tbl1;
+-----------+-----------+
| min(anum) | max(anum) |
+-----------+-----------+
| 20001 | 30000 |
+-----------+-----------+
1 row in set (0.01 sec)

mysql> select min(anum), max(anum) from test3.tbl1;
+-----------+-----------+
| min(anum) | max(anum) |
+-----------+-----------+
| 30001 | 40000 |
+-----------+-----------+
1 row in set (0.01 sec)

As you can see test2.tbl1 has the original data back in and we did not have to write our own script to accomplish this.

REFERENCE
https://www.joomla-webmasters.com/index/articles/open-sourcenews/49-mysql-news/3781-fast-restore-one-database-from-a-dump-of-all-databases.html