How to replace collation, URL or anything in SQL files with SED on Linux

Importing SQL files in databases while transferring websites from one host to another implies changing at least URL values in the SQL file and sometimes the collation of the tables.

The easiest way to do this is using the linux SED utility. SED stands for Stream EDitor.

For changing the collation in your SQL file, let’s say from “utf8mb4_unicode_520_ci” to “utf8mb4_unicode_ci”  just open the SSH console with PUTTY or your favorite client and use this command:

sed -i 's/utf8mb4_unicode_520_ci/utf8mb4_unicode_ci/g' /full/path/to/your/file.sql

Replace the “/full/path/to/your/file.sql” to your actual path, hit Enter and watch the magic!

Now, for changing the URL use this command:

sed -i '' /full/path/to/your/file.sql

Again, replace the above variables with yours. With this command you may have slashes in the URL as well (useful when transferring a whole website into a subdirectory).

Never forget about the -i parameter, it’s used by SED to make the changes in the same file; otherwise it will output the result to console. Or you may use a pipe to create another file out of the original one (good for testing). For more details see the SED manual page.

Have fun!

Leave a Reply