Windows MariaDB setup for IceWarp

IceWarp recommending the usage of the MariaDB database for IceWarp internal databases. Below, you will find an essential guide for MariaDB setup for 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):
Example of the my.ini file for 500 users, below. Based on the comments next to variables you can modify the performance of the MariaDB server (for tweak the performance you can use MySQLTuner tool).

[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

3. After changing the values restart the MySQL(MariaDB) in Windows services. If MariaDB will not start then you need to check some of the setup values. In this case, you need to find which value causing the issues.

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

CREATE DATABASE accounts default charset utf8mb4 collate utf8mb4_unicode_ci;
CREATE DATABASE antispam default charset utf8mb4 collate utf8mb4_unicode_ci;
CREATE DATABASE groupware default charset utf8mb4 collate utf8mb4_unicode_ci;
CREATE DATABASE webclient default charset utf8mb4 collate utf8mb4_unicode_ci;
CREATE DATABASE eas default charset utf8mb4 collate utf8mb4_unicode_ci;
CREATE DATABASE dircache default charset utf8mb4 collate utf8mb4_unicode_ci;
CREATE DATABASE asreports default charset utf8mb4 collate utf8mb4_unicode_ci;

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

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

6. Import attached libraries to Windows.

7. If you want to migrate IceWarp databases to MySQL(MariaDB) you can use the database migration tool in the administration console. Guide for the tool you can find here. For migration of account DB use this article.