Mysql – Search and replace a term in a MySQL database


I recently added a new project to our issue tracker, which is Redmine. When creating a project, you give it a name and an identifier (which are often the same). There is a note when creating the project that you cannot change the identifier once it has been set. For this reason I was very careful to choose a generic identifier. Unfortunately, I wasn't careful enough and I spelled it wrong! The misspelled identifier appears in the issue tracker URLs. These will be seen by other developers and another company we are working with, so it's a very embarrassing mistake.

So I'm looking for suggestions as to how to fix this. Either Redmine-specific, or something I can do at the database level (which is MySQL).

I've already found a solution that I will probably go with, but I thought it would be worth asking here anyway. I'm hoping someone can offer a simpler solution – maybe a magically SQL one-liner.

The solution I've found is this:

  • Dump the database to SQL (using mysqldump)
  • search and replace with sed or a text editor
  • recreate the database from this SQL.

Thanks for any suggestions.

Best Solution

Turns out it was as simple as:

update `projects` set `identifier` = '[NEWNAME]' where `indentifer` = '[OLDNAME]';