Linux 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 CentOS7. 

1. Recommended performance of the MariaDB server:
For up to 1000 IceWarp users: Clean machine, minimum 2x CPU, 8GB RAM, 60GB HDD
For up to 5000 IceWarp users: Increase the amount of RAM to 16GB or 32GB
For anything above 5000 users: We recommend using a clustered SQL solution

2. Clean CentOS system:
Tested with CentOS 7.6
Set system time to UTC, format drives with xfs or ext4, set the root password

3. Uninstall the following packages:

sudo yum -y remove postfix firewalld

4. Add MariaDB 10.3 repo or higher:
Create a new repo file /etc/yum.repos.d/mariadb.repo in your system and add below code:

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.3/rhel7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

5. Install the following packages:

sudo yum -y install wget MariaDB-server MariaDB-client iptables-services unzip sudo nano

6. Apply the DB Server settings:
Copy the appropriate my.cnf file to /etc/my.cnf, overwriting any existing files. 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).

[mysql]
default-character-set=utf8mb4
[mysqld]
user=mysql
basedir=/usr/
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
pid_file=/var/lib/mysql/mysql.pid
port=3306
log_error=/var/log/mysql/mysqld.log
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
innodb_data_file_path = ibdata1:100M:autoextend
## 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
query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 2M
table_open_cache=512
lower_case_table_names=1
#
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

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

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.cnf and to [mysqld] section add following row: 

skip_ssl

7. Stop MySQL(MariaDB)  service, remove buffer pool and log files:

sudo systemctl stop mysql
rm -rf /var/lib/mysql/ib_buffer_pool
rm -rf /var/lib/mysql/ibdata1
rm -rf /var/lib/mysql/ib_logfile0
rm -rf /var/lib/mysql/ib_logfile1

8. Start MySQL(MariaDB) service and setup stating of service after the start of OS:

sudo systemctl enable mysql
sudo systemctl start mysql

9. It is a good idea to secure the MariaDB installation:

sudo /usr/bin/mysql_secure_installation

10. Configure the database server firewall, depending on your requirements. If MariaDB runs on the default port, make sure that port 3306 TCP is open for inbound communication from the IceWarp Server host. If you change the MariaDB port, then you need to open the corresponding port on the firewall.

11. Log in to the MariaDB database:

sudo mysql -u root -p

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

MariaDB [(none)]> CREATE DATABASE iw_accounts default charset utf8mb4 collate utf8mb4_unicode_ci;
MariaDB [(none)]> CREATE DATABASE iw_antispam default charset utf8mb4 collate utf8mb4_unicode_ci;
MariaDB [(none)]> CREATE DATABASE iw_groupware default charset utf8mb4 collate utf8mb4_unicode_ci;
MariaDB [(none)]> CREATE DATABASE iw_webclient default charset utf8mb4 collate utf8mb4_unicode_ci;
MariaDB [(none)]> CREATE DATABASE iw_eas default charset utf8mb4 collate utf8mb4_unicode_ci;
MariaDB [(none)]> CREATE DATABASE iw_dircache default charset utf8mb4 collate utf8mb4_unicode_ci;

13. Create a DB user and his password, for example:

MariaDB [(none)]> CREATE USER 'icewarp'@'192.168.10.101' IDENTIFIED BY 'mypassword';

Change the user name, IP address, and password above to correspond to your IceWarp Server settings.
Note: The user name here is only a database user name and does not relate to any user accounts
created on the IceWarp Server.

14. Grant permissions for all databases to the created user:

MariaDB [(none)]> GRANT ALL ON iw_accounts.* TO 'icewarp'@'192.168.10.101';
MariaDB [(none)]> GRANT ALL ON iw_antispam.* TO 'icewarp'@'192.168.10.101';
MariaDB [(none)]> GRANT ALL ON iw_groupware.* TO 'icewarp'@'192.168.10.101';
MariaDB [(none)]> GRANT ALL ON iw_webclient.* TO 'icewarp'@'192.168.10.101';
MariaDB [(none)]> GRANT ALL ON iw_eas.* TO 'icewarp'@'192.168.10.101';
MariaDB [(none)]> GRANT ALL ON iw_dircache.* TO 'icewarp'@'192.168.10.101';

Change the user name and IP address above to correspond to your previous settings.

15. Apply the privileges:

MariaDB [(none)]> FLUSH PRIVILEGES;

16. Exit the database:

MariaDB [(none)]> exit

17. It is recommended to configure the SQL database in a redundant scenario to avoid any single point of
failure. This can be done in multiple ways. The most basic one is a single-node master-slave deployment.
Please check MariaDB documentation on how to set up master-slave replication:
https://mariadb.com/kb/en/library/setting-up-replication.

18. 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.