Shell script to get all WordPress databases and URL’s

Below you will find a simple script that you can use to find all the databases and URL’s associated to wordpress sites in a shared database. This script requires a SQL user with the ability to ‘show databases’ and access to the DB’s you want to query.

The script grabs a list of all the databases on the server then loops through them all retrieving the URL from the wp_options table. The script also takes into consideration the fact the the table might not actually be wp_options (could contain some random characters) so we use a wildcard there.

#!/bin/bash
# mysql credential 
user="username"
pass="password"
host="DATABASE"

# Get a list of all databases:
all_dbs="$(mysql -u$user -p$pass -h$host -e "show databases;")"

# Select each database from list, get the URL and write it to output.txt
 
for db in $all_dbs
        do 
                mysql -u$user -p$pass -h$host -D$db -e "Select option_value from %_options where option_id=1;" >> output.txt
        done