WHM Search All Databases for specific emails/phrases

RyanR

Active Member
Jul 22, 2020
36
4
8
London
cPanel Access Level
Root Administrator
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

Well-Known Member
PartnerNOC
Nov 9, 2001
671
233
343
cPanel Access Level
DataCenter Provider
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:

Bash:
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:


Bash:
egrep "_options" some_file.sql | egrep admin_email
The first grep looks for _options and then the second one search for admin_email
 

RyanR

Active Member
Jul 22, 2020
36
4
8
London
cPanel Access Level
Root Administrator
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:

Bash:
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:


Bash:
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:

Code:
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:

Code:
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.
 
Last edited:

ffeingol

Well-Known Member
PartnerNOC
Nov 9, 2001
671
233
343
cPanel Access Level
DataCenter Provider
This "might" work for the grep:

Code:
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.
 
  • Like
Reactions: cPanelAnthony

dstana

Well-Known Member
Jul 6, 2016
104
19
68
Phoenix, AZ
cPanel Access Level
Root Administrator
I've had to do stuff like this on occasion, you can loop through them with bash.
Code:
#!/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.
 
  • Like
Reactions: cPanelAnthony