WHM Search All Databases for specific emails/phrases

[RyanR]

Hi,

Our server has a lot of WordPress installations installed across different cpanel accounts and I am looking for a way to search all of the databases to find certain emails/phrases.

For example, search 1:
- Search all databases in the table "wp_options" and/or "*_options" for the option_name of "admin_email"

For example, search 2:
- Search all databases and all tables for a specific email address

For example, search 3:
- Search all databases in the table "wp_users" and/or "*_users" for the nicename of "xxxx"

 

[ffeingol]

That's not possible with SQL. You can only search the database that you are currently "using".

You could dump all the databases to a file and then use grep to search for what you are looking for:

mysqldump --all-databases --skip-extended-insert > some_file.sql

The --skip-extended-insert will cause the insert statements to be one row per line, which will make grep'ing easier

Then you can do things like:

egrep "_options" some_file.sql | egrep admin_email

The first grep looks for _options and then the second one search for admin_email

 

[RyanR]

That's not possible with SQL. You can only search the database that you are currently "using".

You could dump all the databases to a file and then use grep to search for what you are looking for:

mysqldump --all-databases --skip-extended-insert > some_file.sql

The --skip-extended-insert will cause the insert statements to be one row per line, which will make grep'ing easier

Then you can do things like:

egrep "_options" some_file.sql | egrep admin_email

The first grep looks for _options and then the second one search for admin_email

Interesting, I tried running the first command and it threw:

mysqldump: Got error: 1356: "View 'sys.host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them" when using LOCK TABLES

The server is running 10.3.31 MariaDB, though I have CloudLinux so I'd have tofollow MySQL/MariaDB Upgrade | cPanel & WHM Documentation to upgrade SQl if I need to upgrade to 10.5 or 10.6

As per some discussions I found on Stackoverflow and even on this forum I ran the following:

DROP DATABASE sys;

Which allowed me to then dump all databases.

The search (grep) you suggested works perfectly at finding it, but it doesn't help me know what database the SQL is coming from.

 

[ffeingol]

This "might" work for the grep:

egrep "_options|Current Database" some_file.sql | egrep "admin_email|Current Database"

That's going to search for _options or "Current Database" (which will have the DB name) and then admin_email or "Current Database"

MySQL dump will put the Current Database line as it starts to dump each database, so you "should" get the DB name and then admin_email. You're just going to get the database name for any databases that are not WordPress.

 

[dstana]

I've had to do stuff like this on occasion, you can loop through them with bash.

#!/bin/bash

mysql -e "SHOW DATABASES;" > dbs

for db in `cat dbs`; do

mysql -e "use ${db}; ##SQL QUEREIES GO HERE;"

done

Probably want to pull some of the system databases out of the list or anything else you know you don't want to mess with. Then add conditionals depending on the output of your query.

 

[fmosse]

I've had to do stuff like this on occasion, you can loop through them with bash.

#!/bin/bash

mysql -e "SHOW DATABASES;" > dbs

for db in `cat dbs`; do

mysql -e "use ${db}; ##SQL QUEREIES GO HERE;"

done

Probably want to pull some of the system databases out of the list or anything else you know you don't want to mess with. Then add conditionals depending on the output of your query.

Hi! How do I run tthis? Using WHM CONSOLE? Or I have tu put in inside a file and execute it? Can you please explain me?
Thanks
Francisco

 

[cPRex]

Any command that starts with "mysql -e" will run directly from an SSH prompt, so you can run this from the WHM >> Terminal application.