1

I Have an access to MYSQL Server with more than 2000 Databases in it. I want to scan all databases to get all email addresses that saved in the tables of databases.

So would you please give me a solution to extract email address from all of databases !?

I already have a root privileges and phpmyadmin.

Thank you

kamranonline
  • 315
  • 1
  • 5
  • 14

1 Answers1

5

If you have access to all tables (i.e. as root), you can dump all tables and grep email address, like this:

mysqldump -u root -p --all-database | egrep -i "\b[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}\b"

The regular expression I used is taken from here: http://www.regular-expressions.info/email.html

Edit: the command above will print the whole rows containing an email address regardless of the column. If you have email dedicated columns you can print only email with a little modification:

mysqldump -u root -p --all-database | perl -pe "s/,/\n/g; s/'//g;" | egrep -i "\b[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}\b"

This will remove also surrounding quotes.

spider
  • 1,164
  • 9
  • 16
  • I just want email address, is this extract just email address or whole records with email field ? – kamranonline Dec 06 '11 at 10:53
  • Wow, that's a clever solution! XD Yep, it extract only email addresses, not all the fields. The fact is that you have to access your server through a shell console to run that command. Can you? – lorenzo-s Dec 06 '11 at 11:00
  • Yes, I have SSH Access to my server. – kamranonline Dec 06 '11 at 11:08
  • no, the command will print the whole row containing an email address regardless of the column. if you have fields dedicated to email address (and containing anything else) you can change the command as edited. – spider Dec 06 '11 at 14:01
  • Thank you spider, would you please modify this code to save collected email address into a text file and each line just one email address ? – kamranonline Dec 06 '11 at 14:15
  • the command I added already put email address once for line. To save in a text file just add a trailing >textfile.txt at the end – spider Dec 06 '11 at 14:17
  • Thank you spider but there is a problem ! I have a field that contain HTML and email is in this field. your code export HTML code as well ! would you please edit it to just extract email and removed unwanted data from that field ? – kamranonline Dec 06 '11 at 20:40
  • this is why it's better to show all relevant information right in the question enunciation ;-) Anyhow, you can add the "-o" option to egrep command. If you use it you can also remove the perl command. Before you ask how to remove duplicates ;-) you can add "| sort | uniq" at the end of command. – spider Dec 09 '11 at 13:35