Fast restore one database from a dump of ALL databases: "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.
PlanetMySQL Voting:
Vote UP /
Vote DOWN"
"
Google has just announced that it is open-sourcing a set of tools its own developers use to build some of the company’s most well known products, including Gmail, Google Docs, and Google Maps.
"
When you spend as much time at the terminal as I do, having a calculator at your fingertips can be a really useful feature, and the Command-Line Fu web site tells us how to create one easily.
"
Posts
