Thursday, March 10, 2011

Restore a full mysqldump file w/o the mysql database

So you've got a database dump of a few gigabytes and you wat to restore all databases except one. Using vi won't work as the file is too large to be easily edited. But luckily an awk oneliner does do the job.

gawk '/^CREATE DATABASE.*/ { db=substr($7,2); db=substr(db,1,length(db)-1) } /^/ {if (db !=
"mysql") { print }}' original_dump.sql > original_dump_wo_mysql.sql


The same awk code , but now formatted for readability:
/^CREATE DATABASE.*/
{
db=substr($7,2);
db=substr(db,1,length(db)-1)
}

/^/
{
if (db != "mysql") {
print
}
}
Of course you could pipe it directly into mysql saving some space on the filesystem. And you could also do the same with perl/python.
at

5 comments:

  1. Giuseppe MaxiaMarch 10, 2011 at 5:24 PM

    Here is an alternative way

    ReplyDelete
  2. Daniël van EedenMarch 10, 2011 at 5:39 PM

    @Giuseppe: That's a nice way to dump all but one databases, but my I needed a way to import all but one database from a previously made dump which contained all databases.

    Only dumping the required databases is a better solution, but not always possible.

    ReplyDelete
  3. UnknownMarch 10, 2011 at 7:54 PM

    What if you have a dump file with multiple databases backed up, but want to restore just one of those databases. I edited the code if (db != "mysql") { to if (db = "testv1") {. The only problem being that it restored both testv1 and test? Ideas?

    ReplyDelete
  4. Daniël van EedenMarch 11, 2011 at 11:30 AM

    @Justin: Restoring a single database works for me. Is your situation different?

    $ egrep -v '^(--|/\*!|$)' dump001.sql
    CREATE DATABASE /*!32312 IF NOT EXISTS*/ `dve_test` /*!40100 DEFAULT CHARACTER SET utf8 */;
    USE `dve_test`;
    DROP TABLE IF EXISTS `dve_test1`;
    CREATE TABLE `dve_test1` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    LOCK TABLES `dve_test1` WRITE;
    UNLOCK TABLES;
    CREATE DATABASE /*!32312 IF NOT EXISTS*/ `dve_test2` /*!40100 DEFAULT CHARACTER SET utf8 */;
    USE `dve_test2`;
    DROP TABLE IF EXISTS `dve_test2`;
    CREATE TABLE `dve_test2` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    LOCK TABLES `dve_test2` WRITE;
    UNLOCK TABLES;

    $ gawk '/^CREATE DATABASE.*/ { db=substr($7,2); db=substr(db,1,length(db)-1) } /^/ {if (db == "dve_test") { print }}' dump001.sql > dump001_dvetest.sql

    $ gawk '/^CREATE DATABASE.*/ { db=substr($7,2); db=substr(db,1,length(db)-1) } /^/ {if (db == "dve_test2") { print }}' dump001.sql > dump001_dvetest2.sql

    $ egrep -v '^(--|/\*!|$)' dump001_dvetest.sql
    CREATE DATABASE /*!32312 IF NOT EXISTS*/ `dve_test` /*!40100 DEFAULT CHARACTER SET utf8 */;
    USE `dve_test`;
    DROP TABLE IF EXISTS `dve_test1`;
    CREATE TABLE `dve_test1` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    LOCK TABLES `dve_test1` WRITE;
    UNLOCK TABLES;

    $ egrep -v '^(--|/\*!|$)' dump001_dvetest2.sql
    CREATE DATABASE /*!32312 IF NOT EXISTS*/ `dve_test2` /*!40100 DEFAULT CHARACTER SET utf8 */;
    USE `dve_test2`;
    DROP TABLE IF EXISTS `dve_test2`;
    CREATE TABLE `dve_test2` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    LOCK TABLES `dve_test2` WRITE;
    UNLOCK TABLES;

    ReplyDelete
  5. UnknownMarch 11, 2011 at 4:22 PM

    very odd. When I pipe it into the file and run the file it does work fine. When I try to pipe it into mysql it seems to try to run the whole backup?

    ReplyDelete