Adjusting an output of grep in an sh script

Question

Good evening,

I'm trying to grep a list of databases stored within a MYSQL dump but I'd like to adjust some of the settings - I'm unsure how to do so.

The code is currently;

#!/bin/sh

echo "What is the cPanel username!"
read cpuser

cd /home/${cpuser}/public_html

sqldump=$(find . -name \*.sql -type f)
sqlversion=$(grep "Server version" ${sqldump})
sqldbs=$(grep "CREATE DATABASE" ${sqldump})

echo "The dump location is"
echo $sqldump
echo "The SQL version is"
echo $sqlversion
echo "The databases in the dump are"
echo $sqldbs

The output of that is ( in this example )

What is the cPanel username!
domain1mysql4
The dump location is
./test/xxx/database/xxxx.sql
The SQL version is
-- Server version 4.1.14
The databases in the dump are
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `blog` /*!40100 DEFAULT CHARACTER SET latin1 */; CREATE DATABASE /*!32312 IF NOT EXISTS*/ `blog1` /*!40100 DEFAULT CHARACTER SET latin1 */; CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;

I have two questions, ultimately.

Is it possible to only list the databases that it outputs 'blog' 'blog1' 'mysql' rather than the rest of the line?

Is it also possible to then save those as a variable to be called later in the script?

Output after freddy adjustment

sh ./test.sh
enter the cPanel username: domain10mysql4
./home/saiprem/xxxxx/database/xxxx.sql:activesearch
./home/saiprem/xxxxx/database/x.sqxxxxl:alienstats
./home/saiprem/x/database/xxxxxx.sql:anahaw
xxxx./home/saiprem/x/databasxxxe/xxxx.sql:b2
./home/saiprem/x/database/x.sql:beeforumxxx

Answer 1

Assumptions for this answer:

• Zero or more *.sql files might be present in the filesystem
• Calling Bash is allowed, we use it store the database names in an array
• grep with Perl-compatible regular expressions (-P) is available to not output unwanted parts of a match (\K).

#!/bin/sh

read -p "enter the cPanel username: " cpuser
cd "/home/$cpuser/public_html"

find . -name \*.sql -type f -exec bash -c '
    echo "found $# sql dumps"
    for file; do
        version=$(grep -Poe "-- Server version \K.*" "$file")
        dbnames=( $(grep -Poe "CREATE DATABASE [^\`]*\`\K[^\`]*" "$file") )

        printf "\n          file: %s\n" "$file"
        printf "server version: %s\n" "$version"
        printf "%2s database(s): %s\n" "${#dbnames[*]}" "${dbnames[*]}"

        for dbname in "${dbnames[@]}"; do
            echo "do something with $dbname"
        done
    done
' 'find-sh' {} +

Output using two test files:

$ ./script.sh 
enter the cPanel username: me
./script.sh: 4: cd: can't cd to /home/me/public_html
found 2 sql dumps

          file: ./dump2.sql
server version: 4.1.14
 1 database(s): test
do something with test

          file: ./dump1.sql
server version: 4.1.14
 3 database(s): blog blog1 mysql
do something with blog
do something with blog1
do something with mysql

You can see the that directory /home/me/public_html doesn't exist and the script needs error handling.

---

Edit:

All database names with filename prefix:

$ find . -name \*.sql -type f -exec grep -Poe "CREATE DATABASE [^\`]*\`\K[^\`]*" {} +
./dump2.sql:test
./dump1.sql:blog
./dump1.sql:blog1
./dump1.sql:mysql

All database names sorted (add -u to sort to remove duplicates):

$ find . -name \*.sql -type f -exec grep -hPoe "CREATE DATABASE [^\`]*\`\K[^\`]*" {} + | sort
blog
blog1
mysql
test

Answer 2

One way to do this would be to filter the line that outputs the database names through sed.

One thing the database names have in common in your output is the "`" quotation.

So you could pipe the line containing the names through the following process:

1. Place a newline after each quotation
2. Grep for all lines containing the quotation
3. Replace all newlines with a space
4. Delete the space that I think will follow the opening quotation of each name.

That should output only the names inside the quotations on that line.

Bearing in mind that I'm posting this answer just after 1AM, if I'm reading your script right, and if I'm not being dumb, it should look like this:

echo $sqldbs | sed 's/\`/\`\n/' | grep "\`" | sed 's/\n/ /' | sed 's/\` /`/'

It's a bit long-winded, but it should get the job done.

If there are any issues with it, let me know.