MySQL Global Search and Replace Script
A day will eventually come when your need to find and replace a string of text in your database. You don’t know which row, or which column, or which table. Heck, you may not even know which database. Your options are: spend the rest of the summer hunting down the elusive table cells, or use the weapon of mass replacement described below. Naturally and as usual, you absolutely must back up your database (or databases) before attempting any far-reaching scripted mumbo jumbo.
#!/bin/bash echo -n "Enter username: " ; read db_user echo -n "Enter $db_user password: " ; stty -echo ; read db_passwd ; stty echo ; echo "" echo -n "Enter database name: " ; read db_name echo -n "Enter search string: " ; read search_string echo -n "Enter replacement string: " ; read replacement_string MYSQL="/usr/bin/mysql --skip-column-names -u${db_user} -p${db_passwd}" echo "SHOW TABLES;" | $MYSQL $db_name | while read db_table do echo "SHOW COLUMNS FROM $db_table;" | $MYSQL $db_name| \ awk -F'\t' '{print $1}' | while read tbl_column do echo "update $db_table set ${tbl_column} = replace(${tbl_column}, '${search_string}', '${replacement_string}');" |\ $MYSQL $db_name done done |
The script will prompt you for username, password, database name, search string, and replacement string. If will then go through every column of every table in search of your text string. And it will replace it with the new string you specified, potentially saving your hours of work and dozens of typos.
Popularity: 4% [?]
Related posts:
- Passing MySQL Commands from Shell Script
- Useful SysAdmin Scripts: Search-and-Replace
- Find and Replace in MySQL
- A simple text string search script
- Exporting spreadsheet data to MySQL
- Creating table indexes in MySQL
- Using GNUPlot to chart MySQL data
- MySQL LOAD DATA Syntax
- The correct MySQL GRANT syntax
- Generating complex SQL queries with shell scripts
great script – thank you! Worked without issues and saved me alot of time. Again, thank you.
[Reply]
[...] KrazyWorks – MySQL Global Search and Replace Script [...]
OK, but how do I run the script?
[Reply]
admin Reply:
April 27th, 2011 at 12:44 pm
Since you are asking, I presume you are running Windows. This script will only work on Linux/Unix computers. You save it to a file like “search_replace.sh”; make it executable by running “chmod 755 search_replace.sh”; and then you run it by typing “./search_replace.sh”. But not on Windows, sorry.
[Reply]
Works fine, thank you!
[Reply]
Worked like a charm. Good one.
[Reply]