Vicky Dev Vicky Dev - 21 days ago 6
MySQL Question

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.

Answer

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'