credits to author



letz say you got some 2 gb .sql db dump file and you're only interested in getting users e-mail from it. what's the best way to do it?


1. find out db's structure
since you don't need whole db, it will save your time & server load if you work only with user table from this point on.

first, you must somehow get db's structure. in order to do that, use grep:
code:
grep .table structure. somesitedb.sql | cut -d\` -f2 > dbstruct.txt
now open dbstruct.txt and search for user table (_user, users, _members, etc.) - your file structure will look similar to (this is vb db structure):
code:
.
.
.
prefix_user
prefix_useractivation
.
.
.
so you found your user table (prefix_user), but you'll need to write down the following one too (prefix_useractivation) because you'll need it in next command.


2. extract user table
we'll use sed to do it (be careful where you'll put prefix_user and prefix_useractivation and don't change anything else!):
code:
sed -ne "/- table structure for table .prefix_user./,/- table structure for table .prefix_useractivation./p" somesitedb.sql > usertable.sql
basically, you're copying everything between those two strings (prefix_user and prefix_useractivation) using that command ^^^...


3. extract e-mails
ok, this is the last and easiest step - perl script should do it just fine:
code:
perl -wne'while(/[\w\.\-][email protected][\w\.\-]+\w+/g){print "$&\n"}' usertable.sql | sort -u > emails.tx