How to Configure DNS Logging and Diagnostics on Windows Server 2022

How to Install MySQL on Windows Server 2022

MySQL is one of the most popular open-source relational database management systems and runs well on Windows Server 2022. This guide covers the full installation process using MySQL Installer for Windows, configuring MySQL as a Windows service, securing the installation, connecting via the command-line client, managing databases and users with SQL, tuning the configuration file, enabling remote access, and creating the necessary firewall rules.

Downloading MySQL Installer for Windows

The easiest way to install MySQL on Windows is through MySQL Installer, a GUI-based installer that can deploy and manage MySQL Server, MySQL Workbench, MySQL Shell, connectors, and other MySQL products from a single interface.

Download MySQL Installer from the official MySQL downloads page at https://dev.mysql.com/downloads/installer/. Two versions are available: the web installer (smaller, downloads packages during installation) and the full offline installer (larger, includes all packages). For servers without direct internet access, use the full offline installer.

The file will be named something like mysql-installer-community-8.0.37.0.msi. At the time of writing, MySQL 8.0 is the current GA (Generally Available) release. MySQL 8.4 is the Innovation track release with longer support, appropriate for environments that can maintain regular upgrades. This guide uses MySQL 8.0.

Running the MySQL Installer

Run the downloaded MSI file with administrator privileges. On the first screen, you will be asked to choose a setup type. Select Server only if you only need the database engine on this server and will manage it remotely. Select Custom to choose individual components including MySQL Server and MySQL Workbench.

On the Select Products screen, choose MySQL Server 8.0 and, if desired, MySQL Workbench 8.0 and MySQL Shell 8.0. Workbench provides a GUI for database administration, query development, and schema visualization. MySQL Shell is a modern command-line client that supports SQL, JavaScript, and Python modes, and is required for InnoDB Cluster management.

After clicking Next through the product selection and requirement checks, the installer downloads and installs the selected packages. The next important screen is the product configuration for MySQL Server.

Configuring MySQL Server During Installation

The MySQL Installer guides you through a configuration wizard after installation. The key settings to configure are:

Config Type: Select Development Computer for a server sharing resources with other applications, or Dedicated Computer for a server where MySQL is the primary workload. The Dedicated setting allocates more memory to MySQL’s InnoDB buffer pool.

Connectivity: TCP/IP is enabled by default on port 3306. Leave this enabled. You can also optionally enable MySQL X Protocol on port 33060, which is used by MySQL Shell and the X DevAPI for document store operations.

Authentication Method: Choose Use Strong Password Encryption for Authentication (recommended). This uses the caching_sha2_password authentication plugin introduced in MySQL 8.0, which is more secure than the older mysql_native_password plugin used in MySQL 5.7. Older clients or applications that do not yet support caching_sha2_password may need to use legacy authentication, but prefer upgrading the client first.

Accounts and Roles: Set a strong root password. The root account has full administrative privileges over the MySQL instance. Do not leave it blank or use a weak password even in a development environment.

Windows Service: Configure MySQL to run as a Windows service named MySQL80. Set the service to start automatically. You can choose to run it under the standard System account or a dedicated service account. For isolation and security, a dedicated local account is preferable in production.

After completing the wizard, click Execute to apply the configuration. The installer creates the data directory, initializes the system tables, and starts the MySQL service.

Verifying the Service and Connecting via mysql CLI

Verify that the MySQL service is running using PowerShell or the Services console:

Get-Service -Name MySQL80

The output should show Status as Running. If it is not running, start it:

Start-Service -Name MySQL80

Connect to MySQL using the command-line client. The MySQL bin directory is typically added to the PATH during installation, but if not, find it at C:Program FilesMySQLMySQL Server 8.0bin:

mysql -u root -p

You will be prompted for the root password you set during configuration. After a successful login, you will see the MySQL prompt mysql>. Verify the server version:

SELECT VERSION();

The output should show something like 8.0.37.

Securing the MySQL Installation

In MySQL 5.x, a separate mysql_secure_installation script was run after installation to remove anonymous users, restrict root to localhost, and remove the test database. In MySQL 8.0 installed via MySQL Installer, many of these hardening steps are handled during the configuration wizard. However, verify and complete them manually to be certain.

-- Connect as root and check for anonymous users
SELECT user, host FROM mysql.user WHERE user = '';

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

-- Ensure the root account can only connect from localhost
UPDATE mysql.user SET host = 'localhost' WHERE user = 'root' AND host != 'localhost';

-- Remove the test database if it exists
DROP DATABASE IF EXISTS test;

-- Flush privileges to apply changes
FLUSH PRIVILEGES;

To run the equivalent of mysql_secure_installation via command line on Windows, it is available at:

"C:Program FilesMySQLMySQL Server 8.0binmysql_secure_installation.exe"

Creating Databases and Users

Never use the root account for application connections. Create dedicated databases and users with the minimum privileges required by each application.

-- Create a new database with a specific character set and collation
CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create a user that can connect only from localhost
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'Str0ng!AppP@ss2024';

-- Create a user that can connect from a specific IP (for application servers)
CREATE USER 'appuser'@'10.0.1.100' IDENTIFIED BY 'Str0ng!AppP@ss2024';

-- Create a user that can connect from any host (least secure, use only when needed)
CREATE USER 'appuser'@'%' IDENTIFIED BY 'Str0ng!AppP@ss2024';

-- Grant all privileges on a specific database to the user
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'localhost';

-- Grant only read and write privileges (no DROP, CREATE, etc.)
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'appuser'@'10.0.1.100';

-- Grant SELECT only (read-only replica user)
GRANT SELECT ON appdb.* TO 'readonly'@'localhost' IDENTIFIED BY 'Read0nly!Pass#99';

-- Grant privileges on a specific table
GRANT SELECT, INSERT ON appdb.orders TO 'appuser'@'localhost';

-- Apply privilege changes
FLUSH PRIVILEGES;

-- View grants for a user
SHOW GRANTS FOR 'appuser'@'localhost';

-- Revoke a privilege
REVOKE DELETE ON appdb.* FROM 'appuser'@'localhost';

-- Drop a user
DROP USER 'appuser'@'localhost';

The my.ini Configuration File

MySQL on Windows uses my.ini as its main configuration file, typically located at C:ProgramDataMySQLMySQL Server 8.0my.ini. This file controls all server behavior including memory allocation, character sets, logging, InnoDB settings, and network binding.

Key settings to review and tune in the [mysqld] section:

[mysqld]
# Basic settings
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 8.0/"
datadir="C:/ProgramData/MySQL/MySQL Server 8.0/Data/"

# Character set defaults (strongly recommended for new installations)
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

# InnoDB buffer pool - set to 50-70% of available RAM for dedicated servers
innodb_buffer_pool_size=4G

# InnoDB buffer pool instances (tune for large buffer pools: 1 per GB, max 64)
innodb_buffer_pool_instances=4

# InnoDB redo log size (larger = fewer checkpoints, better write performance)
innodb_redo_log_capacity=1073741824

# Max connections (default 151, increase for high-concurrency applications)
max_connections=500

# Slow query log (log queries taking longer than 1 second)
slow_query_log=1
slow_query_log_file="C:/ProgramData/MySQL/MySQL Server 8.0/Data/mysql-slow.log"
long_query_time=1

# General query log (disable in production for performance)
# general_log=0

# Error log
log-error="C:/ProgramData/MySQL/MySQL Server 8.0/Data/mysql-error.log"

# Binary logging (required for replication and point-in-time recovery)
log-bin="C:/ProgramData/MySQL/MySQL Server 8.0/Data/mysql-bin"
binlog_format=ROW
expire_logs_days=7

# Temporary table size limits (prevent disk-based temp tables for small operations)
tmp_table_size=64M
max_heap_table_size=64M

After editing my.ini, restart the MySQL service for changes to take effect:

Restart-Service -Name MySQL80

Enabling Remote Access

By default, MySQL on Windows binds to all available network interfaces (bind-address is not explicitly set, defaulting to listening on all interfaces). The restriction on remote access comes from the MySQL user account host specification, not from a bind-address setting. To verify what MySQL is listening on:

netstat -an | findstr :3306

To restrict MySQL to listen only on a specific IP (for example, the server’s private IP for application traffic, preventing public interface exposure):

[mysqld]
bind-address=10.0.1.50

If you want to bind to multiple specific addresses in MySQL 8.0.13 and later, use a comma-separated list:

[mysqld]
bind-address=127.0.0.1,10.0.1.50

For remote applications to connect, the MySQL user account must have the correct host in its definition (as shown in the CREATE USER section above), and the firewall must allow inbound TCP 3306.

Windows Firewall Rule for MySQL

# Allow inbound TCP on port 3306 for MySQL
New-NetFirewallRule -DisplayName "MySQL Server" `
    -Direction Inbound `
    -Protocol TCP `
    -LocalPort 3306 `
    -Action Allow

# Scope the rule to specific source IPs for better security (application server only)
New-NetFirewallRule -DisplayName "MySQL Server (App Servers)" `
    -Direction Inbound `
    -Protocol TCP `
    -LocalPort 3306 `
    -RemoteAddress "10.0.1.0/24" `
    -Action Allow

# Allow MySQL X Protocol port if using MySQL Shell X DevAPI
New-NetFirewallRule -DisplayName "MySQL X Protocol" `
    -Direction Inbound `
    -Protocol TCP `
    -LocalPort 33060 `
    -Action Allow

Scoping the firewall rule to specific source addresses is strongly recommended in production. Exposing MySQL on port 3306 to the public internet without restriction is a significant security risk.

MySQL Workbench Overview

MySQL Workbench is the official GUI administration and development tool for MySQL. It provides a visual schema designer, query editor, server administration panel, data export and import wizards, and a migration tool for moving databases from other RDBMS platforms to MySQL.

After installing MySQL Workbench, create a new connection by clicking the + icon on the home screen. Specify the hostname or IP, port (3306), username, and password. Click Test Connection to verify connectivity before saving.

Workbench’s Server Status panel shows uptime, active connections, buffer pool hit rate, and InnoDB metrics at a glance. The Performance Schema Dashboard visualizes query latency, lock waits, and I/O wait time — useful for identifying slow queries without parsing the slow query log manually.

MySQL Upgrade Considerations

When upgrading MySQL (for example, from 8.0.30 to 8.0.37, or from 8.0 to 8.4), MySQL Installer handles the upgrade by downloading the new version and running it through the configuration wizard. Before upgrading, always:

1. Take a full mysqldump backup of all databases. 2. Check the MySQL release notes for deprecated features or behavior changes. 3. Test the upgrade on a non-production server first. 4. Run mysqlcheck --all-databases --check-upgrade to identify tables that need analysis or repair before the upgrade.

# Full logical backup before upgrade using mysqldump
"C:Program FilesMySQLMySQL Server 8.0binmysqldump.exe" `
    --all-databases `
    --single-transaction `
    --routines `
    --triggers `
    --events `
    -u root -p `
    > "C:Backupsmysql_all_databases_preupgrade.sql"

# Check all tables for upgrade compatibility
"C:Program FilesMySQLMySQL Server 8.0binmysqlcheck.exe" `
    --all-databases --check-upgrade -u root -p

MySQL 8.0 to MySQL 8.4 is an in-place upgrade path but requires verifying that no applications use deprecated syntax (for example, the old GROUP BY behavior, implicit data type conversions, or functions removed in 8.4). Review the MySQL 8.4 release notes for the complete list of incompatible changes before scheduling the upgrade.

With MySQL installed as a Windows service, secured, configured with appropriate users and firewall rules, and monitored via Workbench and the slow query log, your MySQL deployment on Windows Server 2022 is ready for production use.