November 5th, 2014

Stargate, science

mysql> ?

I am decidedly NOT a database administrator. So when I needed to find an errant string within a database and replace it with something else, SELECTING text from ROWS within TABLES was simply not in my area of expertise. Thankfully, dude Dave Burke of NIXMASH does there what I like to do here - leave himself notes, and maybe help a brother out.

Because I had specifically searched for use sed to find and replace database in hopes I could find something remotely usable using regex (I'd already grep'd the variable from the strings command so I knew it was there somewhere), Mr. Burke shows us how to turn a database into a flat-file, then back into a database, easily making regex changes in between.

  • Create a mysqldump of the database

    • #mysqldump -u root -p database_name > /tmp/database_name.sql

  • Manipulate your strings with sed

    • #sed `s/url\.com\/subdirectory_to_remove/url\.com/g`

  • Re-import the database using mysql

    • mysql -u root -p database_name < /tmp/database_name.sql

Thanks, Dave!