Change MySQL database name in SQL file

Why do you want to complicate it?

1). vi the backup.sql file. 

2). /mydb -> find for line 'mydb'

3). For sure the first two line will be this.



 CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mydb` ;
    use `mydb`;

4). Just change mydb from both the lines to mydb2 and save the file
and import it.

sed 's/CREATE DATABASE \/*!32312 IF NOT EXISTS*\/ `mydb`/`mydb2`/g'

The first problem here is that this will replace every occurrence on any line of this:

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mydb`

with this:

`mydb2`

That's not what you want, because you still need the "CREATE DATABASE" at the beginning of the line. Also, the database name will be used in at least one more place, that you also need to scrub, which is the USE that follows CREATE DATABASE in a mysqldump file that has more than one schema in it.

If `mydb` is a unique identifier that doesn't conflict with any other names in the file (such as a table or column also called `mydb` then you should be able to scrub it like so:

sed 's/`mydb`/`mydb2`/g'

If no joy there either, then the simplest approach is to divide up the file into three pieces -- the part before the database in question, the part after, and the part in the middle, where this database is.

Using grep, you can identify the line numbers of each CREATE DATABASE statement in the file, which you can use as split-points:

grep -n '^CREATE\ DATABASE' dumpfile.sql

Example output:

22:CREATE DATABASE /*!32312 IF NOT EXISTS*/ `accounting` /*!40100 DEFAULT CHARACTER SET utf8 */;
173:CREATE DATABASE /*!32312 IF NOT EXISTS*/ `activity` /*!40100 DEFAULT CHARACTER SET utf8 */;
2635:CREATE DATABASE /*!32312 IF NOT EXISTS*/ `auctions` /*!40100 DEFAULT CHARACTER SET utf8 */;

In this case, the "activity" database is from line 173 through 2634 (the line prior to 2635 where the next database starts) in the file, so we can extract just those lines with sed.

sed -n '173,2634p' < originaldump.sql > activity.sql

Now you have a smaller and more manageable copy of just the "activity" database in a file by itself.