WHM Search All Databases for specific emails/phrases

RyanR

• November 28, 2021 18:37

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

  • November 29, 2021 02:20

  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: [CODE=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: [CODE=bash]egrep "_options" some_file.sql | egrep admin_email
  The first grep looks for _options and then the second one search for admin_email

  0
• 

  RyanR

  • November 29, 2021 03:46

  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: [CODE=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: [CODE=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: 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

  0
• 

  ffeingol

  • November 29, 2021 11:38

  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.

  0
• 

  dstana

  • December 07, 2021 18:13

  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.

  0
• 

  fmosse

  • May 16, 2022 05:08

  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

  0
• 

  cPRex Jurassic Moderator

  • May 17, 2022 17:54

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

  0

Please sign in to leave a comment.