?

Log in

No account? Create an account
Stargate, science

mysql> ?

Posted on 2014.11.05 at 00:00
Current Location: 67114
Tags: ,

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!
2 comments  |  Leave a comment

Comments:

Tomas Gallucci
schpydurx at 2014-11-15 17:09 (UTC) (Link)
As a programmer, I do have to say that is a lot of bother for just updating a value in a database. But that begs the question: why wasn't there a SQL guy on hand?

For what it's worth, SQL is standard–hence the S in Standard Query Language. Yes, there are dialects like you might expect. But for simple stuff like adding, deleting and modifying values in known tables and rows, it's worth having in your toolbox.

The other thing that makes my Tester panic alarms go off is the fact that you converted a database to a flat file. Fair enough; it obviously is supported and suited your purposes. But messing with the innards of how a database works that you didn't code is like randomly casting spells in Storybook: you just don't do it.

Still, whatever gets the job done, right?
(Reply) (Thread)
ehowton
ehowton at 2014-11-15 19:56 (UTC) (Link)
This was a personal endeavor on a new VPS - not work related - and as I don't know many "standard" commands (or even the "known" table/row in which to modify), the solution I used was quick and efficient. Especially given that's what mysqldump is designed to do, that is, create a flat file, then be able to import from it.

I keep thinking I'll learn more SQL but I use it so infrequently the few commands I do know are adequate for most tasks.
(Reply) (Parent) (Thread)
Previous Entry  Next Entry