iRedmail is a great open source email server, however, importing a large number of users can represent a challenge when using the provided import scripts. Depending on the method, some of those scripts still require manual commands to be ran.
I’m going to demonstrate a simple way to import iRedmail users, at any scale, with only an excel spreadsheet and MySQL Workbench.
Scenario
I developed this method when needing to use an free internal email server that would be used with automated testing of applications. We were going to create about 10,000 email accounts which would then receive emails created from automated tests during software developement: new user creation, forgotten passwords, business emails, etc. In this scenario, the mail server chosen was iRedMail, which can be at https://www.iredmail.org/
The plan is simple: create a CSV file that can then be imported into iRedMail’s database.
Install MySQL Workbench
First, if you don’t have a working installation of MySQL Workbench that can connect to iRedMail’s database, you can download the latest copy here: https://dev.mysql.com/downloads/workbench/
After you have MySQL Workbench installed and confirm it can connect to the iRedMail database with the root or vmailadmin user, you can continue to the next step. If you don’t have these credentials on hand, check the admin or postmaster mailbox. iRedMail delivers a post-installation email which includes these credentials.
Download Import Spreadsheet Template
This is where most of the work is done. I created this spreadsheet template and included all of the mandatory columns for the mailbox table of the iRedMail database. There are several worksheets inside: one for pasting raw user data, one that includes the needed formulas to build the content, and a “Copy_From_Here” sheet where you will copy the final data from.
Download
Paste User Data
Open the spreadsheet and go to the “Paste_Here (RAW)” worksheet.
I’ll only use 9 users in this example, but you can easily paste thousands, if you needed to.
Copy Prepared Data
Go to the “Copy_From_Here” worksheet and copy all fields that contain your users. There could be extra rows you will not want to select, depending how many users you pasted in the previous step.
Paste VALUES into new spreadsheet.
Open a new workbook and perform a Paste Special… of VALUES.
Save the speadsheet as a .CSV (Comma Delimited) File.
Import CSV Into the MAILBOX database table.
Connect to the iRedMail database and then find the mailbox table.
1. Right-Click the table, choose Table Data Import Wizard.
2. Choose the .CSV file that you prepared.
3. Confirm the existing table (mailbox) is chosen and table will not be truncated before import.
4. Map all fields to the corresponding column. (Email Address maps to Username)
Continue through the remaining prompts and you should get a confirmation that the import was completed.
Conclusion
This is a simple and mostly graphical way of importing many iRedmail accounts. This is a flexible method and you could update the import template to include default user settings, passwords, or more, by analyzing the mailbox table. In some cases, depending on the number of users, you will need to add additional columns to the “Calculated Fields” and “Copy_From_Here” worksheets, by copy/pasting additional rows from the existing ones.
If this has been useful for you or if you have any questions on the process, please post a comment.