CODEWITHSUNDEEP
mysqlbashreplacesedcommand-line
Vicky Dev
Vicky Dev

Reputation: 2173

Grep/Sed replace small text in large .sql file

I have a large .sql file of size ~ 220 MB.

Now in that I have a TRIGGER statement in that file, which uses a different user than the mysql database user, i am trying to import database with, so it gives below error when trying to import database:

ERROR 1142 (42000) at line 9732: TRIGGER command denied to user 'user_name'@'localhost' for table 'sales_flat_creditmemo'

Now how can I find all lines containing the TRIGGER statements and change "user_name" to mysql user "username_actual" in the whole file without even opening it. Opening this file in Geany also causes the Geany to crash.

Upvotes: 1

Views: 1638

Answers (1)

Inian
Inian

Reputation: 85663

You can use a combination of grep and sed for in-place replacement, without actually opening the file.

grep -Iril 'TRIGGER' sql_file | xargs sed -i 's/user_name/username_actual/g'

The purposes of the flags are:-

-I -> to ignore binary files while doing grep on files. ( may not be applicable here) -r -> recursive search under all sub-levels of directories -i -> Ignore case (case insensitive search) -l -> Just print the file-name in which the match is found.

Check more in the man grep page.

There is also one cool alternate way of using Vim in Ex mode for performing the substitution in-place.

ex -sc '/TRIGGER/s/user_name/username_actual/|x' file

Also one last solution using sed alone for in-place replacement

sed -i '/TRIGGER/s/user_name/username_actual/g'

Upvotes: 2

Related Questions