Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
howtos:network_services:postfix_dovecot_mysql:database [2015/02/16 09:44 (UTC)] astrogeek First complete version |
howtos:network_services:postfix_dovecot_mysql:database [2015/06/11 14:42 (UTC)] (current) tim [Creating Mail Database Tables] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ===== Virtual Mail Server Database ===== | + | ====== Virtual Mail Server Database ====== |
| + | |||
| + | This page is supplemental to main article: [[howtos:network_services:postfix_dovecot_mysql|Creating a Virtual Mail Server with Postfix, Dovecot and MySQL]] | ||
| The database is used to provide configuration and authentication for your virtual mail domains and mail boxes. Email content is not stored in the database in this configuration. | The database is used to provide configuration and authentication for your virtual mail domains and mail boxes. Email content is not stored in the database in this configuration. | ||
| Line 5: | Line 7: | ||
| If you have a full Slackware installation with MySQL already working then you may skip to the next section. | If you have a full Slackware installation with MySQL already working then you may skip to the next section. | ||
| - | If you need to install MySQL (MariaDB) then grab the package from a repo, verify it, then install it: | + | If you need to install MySQL (MariaDB) then grab the package and sigfile from a repo, verify it, then install it: |
| <code> | <code> | ||
| # For 32 bit package # | # For 32 bit package # | ||
| wget http://slackware.osuosl.org/slackware-14.1/patches/packages/mariadb-5.5.40-i486-2_slack14.1.txz | wget http://slackware.osuosl.org/slackware-14.1/patches/packages/mariadb-5.5.40-i486-2_slack14.1.txz | ||
| + | wget http://slackware.osuosl.org/slackware-14.1/patches/packages/mariadb-5.5.40-i486-2_slack14.1.txz.asc | ||
| # For 64 bit package # | # For 64 bit package # | ||
| wget http://slackware.osuosl.org/slackware64-14.1/patches/packages/mariadb-5.5.40-x86_64-2_slack14.1.txz | wget http://slackware.osuosl.org/slackware64-14.1/patches/packages/mariadb-5.5.40-x86_64-2_slack14.1.txz | ||
| - | gpg --verify mariadb-5.5.40...txz {path to your downloaded package} | + | wget http://slackware.osuosl.org/slackware64-14.1/patches/packages/mariadb-5.5.40-x86_64-2_slack14.1.txz.asc |
| + | gpg --verify mariadb-5.5.40...txz.asc {path to your downloaded package sigfile} | ||
| installpkg mariadb-5.5.40...txz | installpkg mariadb-5.5.40...txz | ||
| </code> | </code> | ||
| Line 55: | Line 59: | ||
| * virtual_users - Contains an entry for each virtual mail box served | * virtual_users - Contains an entry for each virtual mail box served | ||
| * virtual_aliases - Maps aliases (source) to actual mail boxes (destination) | * virtual_aliases - Maps aliases (source) to actual mail boxes (destination) | ||
| + | to create these tables, type | ||
| + | <code>mysql --user="mailuser" -p -e "CREATE TABLE virtual_domains( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL, PRIMARY KEY ( id ) );" mailserver | ||
| + | |||
| + | mysql --user="mailuser" -p -e "CREATE TABLE virtual_users( id INT NOT NULL AUTO_INCREMENT, domain_id INT NOT NULL , password VARCHAR(106) NOT NULL, email VARCHAR(100) NOT NULL, PRIMARY KEY ( id ) ,FOREIGN KEY (domain_id) REFERENCES virtual_domains(id));" mailserver | ||
| + | mysql --user="mailuser" -p -e "CREATE TABLE virtual_aliases( id INT NOT NULL AUTO_INCREMENT, domain_id INT NOT NULL, source VARCHAR(100) NOT NULL, destination VARCHAR(100) NOT NULL, PRIMARY KEY ( id ),FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) );" mailserver | ||
| + | </code> each time supplying the password you gave for the mysql mailuser user. | ||
| ==== Adding Domain Entries ==== | ==== Adding Domain Entries ==== | ||
| To add entries to the virtual_domains table using the mysql client: | To add entries to the virtual_domains table using the mysql client: | ||
| Line 145: | Line 155: | ||
| For troubleshooting, use the queries shown above to verify the domains, email users and aliases in the database at any time, and to reset virtual user passwords. | For troubleshooting, use the queries shown above to verify the domains, email users and aliases in the database at any time, and to reset virtual user passwords. | ||
| [[howtos:network_services:postfix_dovecot_mysql|Return to main article page]] | [[howtos:network_services:postfix_dovecot_mysql|Return to main article page]] | ||
| + | ====== Sources ====== | ||
| + | * Originally written by [[wiki:user:astrogeek | astrogeek]] | ||
| {{tag>howtos email postfix dovecot mysql}} | {{tag>howtos email postfix dovecot mysql}} | ||
