ISPConfig_to_Mailu_migration.md

• Ensure that mailu runs on mysql as DB for easier SQL migrations
• Create temporary tables with the correct structure for mailu (remove where filter like domain_id=4 or email like "%@mydomain.com" to dump all domains)

CREATE TABLE mailu_domain
select
    cast(now() as date) as created_at,
    null as updated_at,
    'ispconfig_import' as comment,
    domain as name,
    -1 as max_users,
    -1 as max_aliases,
    0 as max_quota_bytes,
    0 as signup_enabled
 
    from mail_domain
    where active='y'
    and domain_id=4;
 
 
CREATE TABLE mailu_user
select
    cast(now() as date) as created_at,
    null as updated_at,
    'ispconfig_import' as comment,
    SUBSTRING(email, 1, LOCATE('@', email)-1) as localpart,
    password,
    0 as quota_bytes,
    0 as global_admin,
    disableimap='n' as enable_imap,
    disablepop3='n' as enable_pop3,
    length(cc)>0 as forward_enabled,
    cc as forward_destination,
    autoresponder='y' as reply_enabled,
    autoresponder_subject as reply_subject,
    autoresponder_text as reply_body,
    name as displayed_name,
    1 as spam_enabled,
    SUBSTRING(email, LOCATE('@', email)+1) as domain_name,
    email,
    80 as spam_threshold,
    1 as forward_keep,
    cast(autoresponder_end_date as date) as reply_enddate,
    disabledeliver='n' as enabled,
    0 as quota_bytes_used,
    cast(autoresponder_start_date as date) as startdate
 
    from mail_user
    where email like "%@mydomain.com";
 
CREATE TABLE mailu_alias
select
    cast(now() as date) as created_at,
    null as updated_at,
    'ispconfig_import' as comment,
    if (type='catchall',
      SUBSTRING(source, 1, LOCATE('@', source)-1),
      '%') as localpart,
    destination,
    SUBSTRING(source, LOCATE('@', source)+1) as domain_name,
    if (type='catchall',
      concat('%',source),
      source) as email,
    type='catchall' as wildcard
    from mail_forwarding
    where type in ('alias','forward','catchall')
    and active='y'
    and source like "%@mydomain.com";

• Now dump the tables (and rename it in the dump)

mysqldump ispconfig -t mailu_domain -t mailu_user -t mailu_alias | grep "^INSERT INTO" | sed 's/INSERT INTO `mailu_/INSERT INTO `/' > mail.sql

• Copy the dump to the new server and insert it into mailu database

mysql -u root -p$MYSQL_ROOT_PASSWORD mailu < dump.sql

• Finally copy each mail directory to the new server (dovecot should be stopped meanwhile)

rsync -avr --delete-during ispconfig.mydomain.com:/var/vmail/mydomain.com/mailuser1/Maildir/ mailu.mydomain.com:/data/mailu/dovecotmail/mailuser1@mydomain.com/
chown -R mail.man /data/mailu/dovecotmail/mailuser1@mydomain.com/