GoAdmio

collectmail

Collect every email address stored in an email column of any MySQL/MariaDB database into one file

On this page: Overview · Quick start · Syntax · Arguments · Output · Examples · Troubleshooting · Details · See also


Overview

/go/collectmail searches every table in every MySQL/MariaDB database on the server for a column named email, gathers the addresses it finds, and writes them to a single file. The result is lowercased, sorted, free of duplicates, and limited to well-formed addresses.

It is useful for migrations and for building a consolidated list of the addresses your sites already hold.


Quick start

# Collect all email addresses into /root/emails.csv
/go/collectmail /root/emails.csv

[!WARNING] The result contains personal data from all of your customers' sites. Store it securely and use it only where you have the legal right, for example for a migration or for consent-based mailing. The command also leaves per-table copies of the addresses in /usr/local/goadmio/tmp/mail. See Cleanup.


Syntax

/go/collectmail <output file>

The command must be run as root, because it reads the database password from GoAdmio's configuration file.


Arguments

Argument Required Notes
<output file> Yes The file to write. The name must end in .csv or .txt. A relative path is relative to the current directory. An existing file is overwritten.

If the argument is missing, or the name doesn't end in .csv or .txt, the command prints the same two lines and exits with status 1:

Error: No output file provided.
Usage: collectmail <Output CSV File>

Output

On screen

What you see Meaning
Collecting Emails from <database>.<table>... The table currently being read.
Email addresses The addresses found in each table are also printed as they are collected.
ERROR 1054 ... Unknown column 'email' The table has no email column. This is expected and harmless.
Emails: <number> The number of addresses written to the output file.

If no table contains an address, no summary is printed and the output file isn't created.

Output file

The file has one address per line, with no header row. Despite the .csv extension, there is only one column.

Every run is logged with a timestamp, for example [Nov 20 09:15:02]# collectmail /root/emails.csv, to /var/log/go/collectmail.log.


Examples

# Collect addresses and keep only the final count on screen
/go/collectmail /root/emails.csv 2>/dev/null | tail -n 1
# Preview the first few addresses
head /root/emails.csv
# Count the addresses in the file
wc -l /root/emails.csv
# Remove the temporary per-table copies when finished
rm -rf /usr/local/goadmio/tmp/mail

Troubleshooting

Symptom Likely cause Fix
Error: No output file provided. although you gave one The name doesn't end in .csv or .txt. Add the extension, for example emails.csv.
Many ERROR 1054 ... Unknown column 'email' lines Most tables have no email column. Normal. Hide them with 2>/dev/null.
The screen fills with email addresses Each table's addresses are printed as they are read. Normal. Use | tail -n 1 to see only the final count.
No output file and no summary No table has a valid address in an email column, or the database login failed. Check the error messages. Confirm DBPass in /usr/local/goadmio/include/config/config.php.
Emails: 0 Every value was rejected by the format check. Inspect the temporary files in /usr/local/goadmio/tmp/mail.
Addresses from a site are missing Its column isn't named email (for example user_email). This command only reads columns named exactly email.
Tables in some databases are skipped with an SQL syntax error The database or table name contains a hyphen or other special character. Names aren't quoted by the script. Export those tables by hand.
The command is very slow It queries every table in every database. Run it during off-peak hours.

Details

How it works

  1. Checks that the output file name ends in .csv or .txt.
  2. Reads the MariaDB root password from /usr/local/goadmio/include/config/config.php.
  3. Deletes and recreates /usr/local/goadmio/tmp/mail.
  4. Lists every table in every database, including MariaDB's system databases.
  5. For each table, runs select email from <database>.<table> group by email and saves the result as <database>.<table>.csv in the temporary folder.
  6. Deletes each saved file that contains no @.
  7. Joins the remaining files, converts everything to lowercase, sorts it, and removes duplicates.
  8. Keeps only values that look like name@domain.tld and writes them to the output file.
  9. Prints the number of addresses written.

What it collects

Collected Not collected
Values in columns named email, in any table of any MySQL/MariaDB database Columns with other names, such as user_email or mail
Addresses with a valid name@domain.tld format Values with spaces, a missing domain extension, or other formatting problems
GoAdmio's own account data and mailbox folders on disk

Cleanup

The temporary folder /usr/local/goadmio/tmp/mail keeps one file per table plus a combined email.csv, all containing addresses. The command doesn't delete them. Remove them yourself, or run clean, which empties /usr/local/goadmio/tmp/.

Security notes


See also

Command Description
clean Delete old backups, trash, session files, and expired mail to free up disk space
dbls List all databases and their sizes
csv Export a MySQL/MariaDB table to a CSV file
mailtop Check the most active email accounts in GoAdmio
mysql Create a MySQL database