mc14-docker.md

Create mailcow db backups

First you need to modify the table mailcow. Mailcow-dockerized adds three and moves two existing columns in the table mailbox. The columns tls_enforce_in and tls_enforce_out get moved two rows up (behind domain). The columns key, multiple_bookings and wants_tagged_subject need to be added after tls_enforce_out.

It should look like this:

MariaDB [mailcow]> desc mailbox;
+----------------------+--------------+------+-----+-------------------+-----------------------------+
| Field                | Type         | Null | Key | Default           | Extra                       |
+----------------------+--------------+------+-----+-------------------+-----------------------------+
| username             | varchar(255) | NO   | PRI | NULL              |                             |
| password             | varchar(255) | NO   |     | NULL              |                             |
| name                 | varchar(255) | YES  |     | NULL              |                             |
| maildir              | varchar(255) | NO   |     | NULL              |                             |
| quota                | bigint(20)   | NO   |     | 0                 |                             |
| local_part           | varchar(255) | NO   |     | NULL              |                             |
| domain               | varchar(255) | NO   | MUL | NULL              |                             |
| tls_enforce_in       | tinyint(1)   | NO   |     | 0                 |                             |
| tls_enforce_out      | tinyint(1)   | NO   |     | 0                 |                             |
| kind                 | varchar(100) | NO   |     |                   |                             |
| multiple_bookings    | tinyint(1)   | NO   |     | 0                 |                             |
| wants_tagged_subject | tinyint(1)   | NO   |     | 0                 |                             |
| created              | datetime     | NO   |     | CURRENT_TIMESTAMP |                             |
| modified             | datetime     | YES  |     | NULL              | on update CURRENT_TIMESTAMP |
| active               | tinyint(1)   | NO   |     | 1                 |                             |
+----------------------+--------------+------+-----+-------------------+-----------------------------+

You can do this with a UI like adminer or use the MySQL CLI like :

MariaDB [mailcow]> ALTER TABLE mailbox MODIFY COLUMN tls_enforce_in TINYINT(1) NOT NULL DEFAULT '0' AFTER domain,
MODIFY COLUMN tls_enforce_out TINYINT(1) NOT NULL DEFAULT '0' AFTER tls_enforce_in;
MariaDB [mailcow]> ALTER TABLE mailbox ADD COLUMN `kind` VARCHAR(255) NOT NULL AFTER `tls_enforce_out`,
ADD COLUMN `multiple_bookings` TINYINT(1) NOT NULL DEFAULT '0' AFTER `kind`,
ADD COLUMN `wants_tagged_subject` TINYINT(1) NOT NULL DEFAULT '0' AFTER `multiple_bookings`;
MariaDB [mailcow]> DESC mailbox;

When this is done we can backup the tables:

mysqldump --replace -t --default-character-set=utf8mb4 \
    -u $MAILCOWDB_USER -p$MAILCOWDB_PW $MAILCOWDB_NAME \
    alias alias_domain domain domain_admins mailbox quota2 sender_acl spamalias > backup_mailcow.sql

--replace: Write REPLACE statements rather than INSERT statements

--no-create-info: Don't write CREATE TABLE statements.

--default-character-set make sure our exported charset is utf8mb4.

Remember to adjust your mysql details MAILCOWDB_*

Prepare the new mailcow-dockerized

Install mailcow-dockerized as described in its documentation and visit your installation (http://host.domain.tld) with a browser of your choice to initiate the empty mailcow database. Please check if the tables where created:

• visit your new installation (http://host.domain.tld) with a browser of your choice to initiate the empty database. Check if the DB is initiated:

  source mailcow.conf
  docker-compose exec mysql-mailcow mysql -u${DBUSER} -p${DBPASS} ${DBNAME}
  MariaDB [mailcow]> show tables;
  

Import your backups:

source mailcow.conf
docker exec -i $(docker-compose ps -q mysql-mailcow) mysql -u${DBUSER} -p${DBPASS} ${DBNAME} < backup_mailcow.sql

Recalculate used quota with doveadm:

docker-compose exec dovecot-mailcow doveadm quota recalc -A

Restart SOGo:

docker-compose restart sogo-mailcow