Restoring a single database from a full dump is pretty easy, using the mysql command line client’s –one-database option:
mysql -u root -p --one-database db_to_restore_name < fulldump.sql
But what if you don’t want to restore the database, you just want to extract it out of the dump file? Well, that happens to be easy as well, thanks to the magic of sed:
sed -n '/^-- Current Database: `test`/,/^-- Current Database: `/p' fulldump.sql > test.sql
You just need to change “test” to be the name of the database you want extracted.
Now let´s extract one table from one dump.
sed -n ‘/^CREATE TABLE `MyTable`/,/^DROP TABLE /p’ test.sql
Now if you have the same table name in 2 datbases and you have full dump instead a single dump.
sed -n '/^-- Current Database: `test`/,/^-- Current Database: `/p' fulldump.sql | sed -n '/^CREATE TABLE `MyTable`/,/^DROP TABLE /p' > test.MyTable.sql
And incase you have a huge mysqldump as a .tar.gz file, and you don´t wantto expand the fullmysqldump.sql file. In this case we can use the following :
gunzip -c mysqldump-20210728.sql.gz | sed -n ‘/^– Current Database: `test`/,/^– Current Database: `/p’ > test.sql
Hope all this exaples help you save some time.
Recent Comments