When maintaining applications built with MySQL, it can sometimes be necessary to search an entire database for a text string. For instance, if a website’s domain name has changed and links in the site’s dynamic content had been hard-coded with the full URL, it can be useful to search the entire database for all occurrences of that particular string.
Compared with other databases, one of the neat features of MySQL is its built-in ability to create human-readable full-text dumps of the database. Using the command-line tool “mysqldump”, it is possible to dump the entire database schema and all data into a simple text file. That file can then be edited, updated, and the same dump file can be used to repopulate the database. Alternatively, the dump file can be searched for a text string, and the database can be manually updated using SQL update statements. Searching a MySQL database for a text string is as simple as follows.
First, use mysqldump to create a text dump of the database:
mysqldump -u [user] -p [database name] > [output file]
i.e.:
mysqldump -u root -p mydb > /mydb.sql
This command will work well for most English websites. When analyzing data outside the standard ASCII character set, be sure to add the “ –default-character-set=latin1” modifier to the mysqldump command, so that it will process Unicode as well.
Next, parse the dump for the target text using grep. When parsing databases containing HTML content, it can often be difficult to view the exact text since it will fill up the entire command-line. Instead, use a modified search that displays a specified number of characters before and after the text, as follows:
grep -i -o -P ".{0,20}[search string].{0,20}" [output file]
i.e.:
grep -i -o -P ".{0,20}apharmony\.com.{0,20}" /mydb.sql
Modify the number of characters as necessary to capture the target information. If creating the dump file itself is a security concern, the two commands can be combined into one using a pipe:
mysqldump -q -u [user] -p [database name] | grep -i -o -P ".{0,20}[search string].{0,20}"
i.e.:
mysqldump -q -u root -p mydb | grep -i -o -P ".{0,20}apharmony\.com.{0,20}"
Although this can help reduce disk space overhead, it can use more memory and make further database searches and queries more difficult. When analyzing databases and trying to fix a possible issue, it is almost always better to create a dump file so that multiple search commands can be executed.
Another advantage of the mysqldump technique is that database schema code and business logic can be searched as well. For instance, if a stored procedure is generating a particular error message, this technique can help track down that error message.
Overall, the mysqldump and grep commands can be a very helpful duo in ensuring thorough and highly reliable application maintenance updates.
Written by Andrew Palczewski
About the Author
Andrew Palczewski is CEO of apHarmony, a Chicago software development company. He holds a Master's degree in Computer Engineering from the University of Illinois at Urbana-Champaign and has over ten years' experience in managing development of software projects.
Google+
