Windows MariaDB setup for IceWarp

IceWarp recommends the MariaDB database to use for internal databases. Follow the steps below to set up MariaDB on Windows. 

1. Download and install MariaDB 10.3 or higher
https://downloads.mariadb.org/ 

2. Change values in my.ini (by default, is saved in %MYSQL_HOME%\my.ini)

An example of the my.ini file for 500 users is below. Based on the comments next to the variables, you can modify the performance of the MariaDB server (to tweak the performance, you can use the MySQLTuner tool).

The directories which were in the file before pasting it and replacing them must exist.

[mysqld]
# Folder to physically save databases
datadir=C:/Program Files/MariaDB 10.3/data
# Folder to log file
log_error=C:/Program Files/MariaDB 10.3/mysqld.log
# Level of logging for MySQL service
log_warnings=2
# log_output = FILE
### INNODB OPTIONS
innodb_buffer_pool_size=6G ## change according to node memory size, free -m, set up to 85% of total physical memory of the node
innodb_flush_log_at_trx_commit=0
innodb_file_per_table=1
## You may want to tune the below depending on number of cores and disk sub
innodb_read_io_threads=6 ## set to number of node CPU cores
innodb_write_io_threads=6 ## set to number of node CPU cores
innodb_doublewrite=1
innodb_log_file_size=512M
innodb_log_buffer_size=128M
innodb_buffer_pool_instances=6 ## same number as in "innodb_buffer_pool_size" recommended here
innodb_log_files_in_group=2
innodb_thread_concurrency=6 ## same number as in "innodb_buffer_pool_size" recommended here
# innodb_file_format = barracuda
innodb_flush_method = O_DIRECT
# innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode=2
## avoid statistics update when doing e.g show tables
innodb_stats_on_metadata=0
default_storage_engine=innodb
open-files-limit=331072

# CHARACTER SET
collation_server = utf8mb4_unicode_ci ## set according to application language preferences
init_connect='SET NAMES utf8mb4'
character_set_server = utf8mb4

# OTHER THINGS, BUFFERS ETC
key_buffer_size = 24M
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 512M
sort_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
# myisam_sort_buffer_size = 8M
skip_name_resolve
memlock=0
sysdate_is_now=1
max_connections=600
thread_cache_size=64
table_open_cache=512
#
binlog_cache_size=1048900
binlog_stmt_cache_size=1048900
table_definition_cache=1024
wait_timeout=93600
connect_timeout=30
net_read_timeout=180
net_write_timeout=360

[client]
# Port for MySQL service
port=3306
# Path to plugin folder
plugin-dir=C:/Program Files/MariaDB 10.3/lib/plugin

SSL connection:

If you are using newer versions of MariaDB such as MariaDB 11.3, the database uses SSL connection by default. IceWarp does not support SSL connection yet, so the database won't connect. To disable SSL, edit my.ini and to [mysqld] section add following row: 

skip_ssl

3. After changing the values, restart the MySQL(MariaDB) in Windows services.

If MariaDB does not start, then you need to check some of the setup values. In this case, you need to find which value is causing the issues.

4. Create databases (create collations based on the location more - https://mariadb.com/kb/en/supported-character-sets-and-collations/)

* accounts store information about user accounts

* antispam stores information about antispam

* groupware stores information about calendars, contacts, etc.

* webclient stores information about WebClient's cache

* eas stores information about Active Sync (EAS is Exchange Active Sync) on mobiles

* dircache stores information about the directory cache

* asreports stores information about antispam reports

CREATE DATABASE iw_accounts default charset utf8mb4 collate utf8mb4_unicode_ci;
CREATE DATABASE iw_antispam default charset utf8mb4 collate utf8mb4_unicode_ci;
CREATE DATABASE iw_groupware default charset utf8mb4 collate utf8mb4_unicode_ci;
CREATE DATABASE iw_webclient default charset utf8mb4 collate utf8mb4_unicode_ci;
CREATE DATABASE iw_eas default charset utf8mb4 collate utf8mb4_unicode_ci;
CREATE DATABASE iw_dircache default charset utf8mb4 collate utf8mb4_unicode_ci;
CREATE DATABASE iw_asreports default charset utf8mb4 collate utf8mb4_unicode_ci;

5. Create a user and give him permission for the specific database.

Don't forget to replace "database.*" with the actual name of your database, e.g. "accounts.*". Repeat the command GRANT ALL PRIVILEGES for all databases.

CREATE USER 'icewarp'@'127.0.0.1' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database.* TO 'icewarp'@'127.0.0.1';

6. Import attached libraries to Windows.

If you want to migrate IceWarp databases to MySQL(MariaDB), you can use the database migration tool in the administration console.

The guide for the tool is available here. For migration of account DB, use this article.