Bash: how to easily edit one line in a 5.4G SQL dump

Question

We're running into issues where we need to work on recovering some lost data (yay improper use of maatkit on a master-to-master replication setup), and I'm trying to import an old dump of the database. The problem is that at the top of the file, it's explicitly specifying the database (mysqldump was run with the --all-databases option), and I need to change that DB to something else so I can get another in there alongside it for comparison. The line reads:

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `dms` /*40100 DEFAULT CHARACTER SET latin1 */

I've been unsuccessful at opening the file in vi to edit due to the large size of the dump, and am a little hesitant to use sed because of how it's going to read line-by-line for any pattern matching. What is the easiest and most efficient way to change the above line in the sql dump to read:

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `dms_old` /*40100 DEFAULT CHARACTER SET latin1 */

Or simply use some mysql black magic to import it into database dms_old?

Try looking at this issue: http://stackoverflow.com/questions/1591723/linux-text-editor-for-working-with-huge-files — cjm2671, Nov 21 '11 at 22:31
possible duplicate of [Edit very large sql dump/text file (on linux)](http://stackoverflow.com/questions/699785/edit-very-large-sql-dump-text-file-on-linux) — ephemient, Nov 21 '11 at 23:09

score 4 · Accepted Answer · answered Nov 21 '11 at 22:35

4

sed should have no problems with that. Just do:

`sed '/CREATE DATABASE/s/dms/dms_old/'`

with appropriate redirections.

answered Nov 21 '11 at 22:35

ninjalj

42,493
9
106
148

score 3 · Answer 2 · answered Nov 21 '11 at 22:37

3

I think this is right.

sed '0,/dms/s/dms/dms_old/' dump.sql

That will only run the replacement once, and only for the first line.

answered Nov 21 '11 at 22:37

Jonathan

325
1
1

Bash: how to easily edit one line in a 5.4G SQL dump

2 Answers2