How to Configure DHCP with DNS Dynamic Update on Windows Server 2022

How to Install MariaDB on Windows Server 2022

MariaDB is a community-developed, open-source relational database management system that originated as a fork of MySQL. It is fully compatible with MySQL applications while offering additional features, storage engines, and better performance characteristics in many workloads. Installing MariaDB on Windows Server 2022 involves downloading the Windows MSI installer, configuring it as a service, securing the installation, and tuning it for production use. This guide covers the complete process.

Downloading MariaDB for Windows

Go to the MariaDB downloads page at https://mariadb.org/download/. Under the MariaDB Server section, select the latest stable version (10.11 LTS or 11.x), choose Windows as the operating system, and download the MSI package (x86_64). The filename will look like mariadb-10.11.7-winx64.msi.

MariaDB also provides a ZIP archive for portable installations, but the MSI installer is recommended for production Windows Server deployments because it handles service registration, PATH configuration, and initial setup automatically.

Running the MariaDB Installer

Right-click the MSI and select Install as administrator. The MariaDB Server Setup Wizard opens. Work through the following screens:

Accept the license agreement. On the Custom Setup screen, ensure MariaDB Server, HeidiSQL (the GUI client), and the command-line tools are all selected. The default installation path is C:Program FilesMariaDB 10.11. On the Default Instance Properties screen, set the root password — this is the database superuser password. Check Use UTF8 as default server’s character set (recommended for new deployments). Check Install as service and set the service name to MySQL (the traditional name, which many applications expect) or MariaDB. Leave the port at 3306. Click Install.

Verifying the Windows Service

After installation completes, verify the service is running:

Get-Service -Name "MySQL"

If you named the service MariaDB:

Get-Service -Name "MariaDB"

Manage the service with standard PowerShell commands:

Stop-Service -Name "MySQL"
Start-Service -Name "MySQL"
Restart-Service -Name "MySQL"

The service binary is located at C:Program FilesMariaDB 10.11binmysqld.exe and reads its configuration from C:Program FilesMariaDB 10.11datamy.ini.

Connecting with the mysql CLI

The MariaDB command-line client is mysql.exe, located at C:Program FilesMariaDB 10.11bin. The installer typically adds this to the system PATH. Connect as root:

mysql -u root -p -h 127.0.0.1 -P 3306

Enter the root password set during installation. You will see the MariaDB prompt: MariaDB [(none)]>.

Check the server version and list databases:

SELECT VERSION();
SHOW DATABASES;
SHOW VARIABLES LIKE 'character_set_server';
SHOW VARIABLES LIKE 'collation_server';

Securing the Installation

MariaDB ships with a security script equivalent to mysql_secure_installation. On Windows, you can run it directly from PowerShell:

mysql_secure_installation

If the script is not available, perform the equivalent steps manually through the mysql CLI. Connect as root and run:

-- Remove anonymous users
DELETE FROM mysql.user WHERE User='';

-- Disallow remote root login (root should only connect from localhost)
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

-- Remove the test database
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\_%';

-- Apply the changes
FLUSH PRIVILEGES;

Creating Databases and Users

Create application databases and dedicated users with appropriate permissions. Never use the root account for application connections:

-- Create database
CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create user (localhost only)
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'SecureAppPass123!';

-- Create user for remote app server access
CREATE USER 'appuser'@'192.168.1.%' IDENTIFIED BY 'SecureAppPass123!';

-- Grant privileges
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'appuser'@'192.168.1.%';

FLUSH PRIVILEGES;

Verify user grants:

SHOW GRANTS FOR 'appuser'@'localhost';

Configuring my.ini on Windows

The main configuration file is C:Program FilesMariaDB 10.11datamy.ini. Open it as Administrator. Key sections and settings to configure for a production server:

[mysqld]
# Basic settings
port=3306
datadir=C:/Program Files/MariaDB 10.11/data
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

# Networking
bind-address=127.0.0.1          # Set to 0.0.0.0 or specific IP for remote access
skip-name-resolve               # Skip DNS lookups for faster connections

# InnoDB storage engine
innodb_buffer_pool_size=1G      # ~70-80% of RAM for dedicated DB server
innodb_buffer_pool_instances=4  # Set to number of vCPUs (up to 8)
innodb_log_file_size=256M       # Larger = better write performance, slower crash recovery
innodb_flush_log_at_trx_commit=1  # 1=fully durable; 2=better performance, 1-sec risk
innodb_file_per_table=ON        # Each table in its own .ibd file

# Connection management
max_connections=200
max_allowed_packet=64M
wait_timeout=600
interactive_timeout=600

# Query cache (deprecated in MariaDB 10.10+ for most uses)
# Use query_cache_type=0 to disable

# Temporary tables
tmp_table_size=64M
max_heap_table_size=64M

# Slow query log
slow_query_log=1
slow_query_log_file=C:/Program Files/MariaDB 10.11/data/slow.log
long_query_time=1               # Log queries taking more than 1 second
log_queries_not_using_indexes=1

# Binary log (for replication and point-in-time recovery)
log_bin=mysql-bin
binlog_format=ROW
expire_logs_days=7

After editing my.ini, restart the service:

Restart-Service -Name "MySQL"

InnoDB Buffer Pool Size Tuning

The innodb_buffer_pool_size is the single most impactful configuration parameter for InnoDB performance. It controls how much RAM MariaDB uses to cache data and index pages. On a dedicated database server, set it to 70-80% of available RAM. On a server running other services, set it lower to avoid memory contention.

Monitor buffer pool hit rate after tuning with:

SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';
-- Hit rate = (read_requests - reads) / read_requests * 100
-- Target: above 99%

If innodb_buffer_pool_reads is a significant fraction of innodb_buffer_pool_read_requests, increase the buffer pool size.

Enabling the Slow Query Log

The slow query log identifies queries that take too long to execute. It is configured in my.ini as shown above. After enabling it, check which queries appear most frequently in the log using mysqldumpslow (available in the MariaDB bin directory):

mysqldumpslow -s t -t 10 "C:Program FilesMariaDB 10.11dataslow.log"

The -s t flag sorts by total time, and -t 10 shows the top 10 slowest queries. Use EXPLAIN on slow queries to identify missing indexes:

EXPLAIN SELECT * FROM orders WHERE customer_id = 12345 AND status = 'pending';

Backup with mariabackup

mariabackup is MariaDB’s physical backup tool (equivalent to Percona XtraBackup). It creates a consistent copy of the data directory while the server is running, without locking tables for extended periods. It is included in the MariaDB installation on Windows.

Run a full backup:

mariabackup --backup `
  --target-dir="C:Backupsmariabackup_full" `
  --user=root `
  --password=RootPassword123!

Prepare the backup (makes it consistent and ready for restore):

mariabackup --prepare --target-dir="C:Backupsmariabackup_full"

For logical backups of individual databases, use mysqldump:

mysqldump -u root -p --single-transaction --routines --triggers `
  appdb > C:Backupsappdb_backup.sql

The –single-transaction flag uses a consistent snapshot for InnoDB tables without locking, making it safe to run against a live production database.

MariaDB Galera Cluster Overview

MariaDB Galera Cluster provides synchronous multi-master replication, meaning every write is committed on all nodes simultaneously with no slave lag. This enables reads and writes on any node with automatic failover. The cluster uses the Galera library (wsrep) for certification-based replication.

In my.ini on each cluster node, add the Galera configuration:

[mysqld]
wsrep_on=ON
wsrep_provider=C:/Program Files/MariaDB 10.11/lib/galera/libgalera_smm.dll
wsrep_cluster_name="pr_galera_cluster"
wsrep_cluster_address="gcomm://192.168.1.61,192.168.1.62,192.168.1.63"
wsrep_node_address="192.168.1.61"
wsrep_node_name="node1"
wsrep_sst_method=mariabackup
wsrep_sst_auth=sst_user:sst_password
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

Galera requires a minimum of three nodes for proper quorum and split-brain prevention. Note that Galera Cluster on Windows is primarily used in mixed environments; Linux nodes are more common in production Galera deployments.

MariaDB vs MySQL Differences on Windows

While MariaDB is largely drop-in compatible with MySQL, there are notable differences relevant to Windows deployments. MariaDB includes additional storage engines such as Aria (a transactional MyISAM replacement), ColumnStore (columnar analytics), and Spider (sharding). MariaDB’s optimizer improvements often result in better query performance for complex queries. MariaDB 10.4 introduced separate authentication plugins and the unix_socket authentication plugin for root, which behaves differently on Windows. MariaDB does not include the MySQL X Protocol or MySQL Shell — use HeidiSQL or DBeaver as GUI alternatives. MariaDB’s binary log format and replication protocol are wire-compatible with MySQL 5.7 but may diverge in features with MySQL 8.x. For new Windows Server deployments, MariaDB is a compelling choice where MySQL licensing costs are a concern or where Galera replication or the broader set of storage engines are needed.

Opening Windows Firewall for Remote Connections

If your application servers need to connect remotely to MariaDB, open port 3306 and change bind-address in my.ini:

New-NetFirewallRule -DisplayName "MariaDB 3306" `
  -Direction Inbound `
  -Protocol TCP `
  -LocalPort 3306 `
  -Action Allow `
  -Profile Domain,Private

Summary

Installing MariaDB on Windows Server 2022 through the MSI installer provides a complete, production-ready relational database server. After installation, key steps include securing the root account, creating application-specific users with least-privilege grants, tuning my.ini with appropriate InnoDB buffer pool sizing and slow query logging, and establishing backup routines using mariabackup for physical backups and mysqldump for logical backups. MariaDB’s compatibility with MySQL applications, combined with its additional features and open-source licensing, makes it an excellent choice for Windows Server database workloads.