Changing Drupal database URLs programmatically from the command line

I had a need today to batch-change a lot of database connection strings in Drupal installations. You might need to do this if you have a lot of sites and are switching from webserver-and-database-server-on-the-same-box to Drupal with a separate database server. Here were some handy shell commands that I used.

Find all the settings.php files in this directory tree:

find /var/www/html -name settings.php -print

Show all the current database connection strings (make sure no one's looking over your shoulder!):

find . -name settings.php -exec grep -nH ^\$db_url {} \;

The grep finds lines beginning with $db_url. The -n switch prints out the line number of the match; the -H switch prints the filepath. Sample output:

./foo/site1/sites/default/settings.php:93:$db_url = 'mysql://myuser:secret@localhost/database1';
./foo/site2/sites/default/settings.php:93:$db_url = 'mysql://myotheruser:othersecret@localhost/database2';

Now, change the sites from pointing to localhost to pointing to

find /var/www/html -name settings.php -exec sed -i '80,' {} \;

Translation to English: descend recursively into the /var/www/html directory. When you find a file named settings.php, look in lines 80-95 of the file. If you find a string that contains t@localhost, change it to Save the file, overwriting the file that is currently there (that's the -i switch to sed). The # characters are delimiters.

Note that I'm cheating here, because I happen to know that all passwords end with the letter t which makes my string matching easier.