Install MySQL on Windows Server
Install MySQL 9.7 LTS on Windows Server, configure the service and accounts, allow filtered and encrypted remote access, then schedule backups with mysqldump.
On this page
Your values
Enter them once and the commands in this guide update.
This guide installs MySQL Community Server 9.7 LTS with the official MSI package, then configures the Windows service, an application account, restricted remote access and automatic backups.
Prerequisites
- Windows Server 2019, 2022 or 2025 64-bit, with an administrator account. Check your version in the list of platforms supported by MySQL
- The Visual C++ Redistributable required by MySQL (step 1)
- The public IP address of the allowed computer, if you need remote access
Run the commands in Windows PowerShell as administrator.
Step 1: Install MySQL
- Install Microsoft's Visual C++ Redistributable x64. MySQL 9.7 needs the Visual C++ 2019 package, and this recent version replaces it.
- On the MySQL Community Server download page, select 9.7 LTS, then Microsoft Windows, and download the MSI Installer.
- Run the
.msifile and choose the Typical installation. MySQL is installed inC:\Program Files\MySQL\MySQL Server 9.7. - At the end, leave the option that launches MySQL Configurator checked. The server does not start until it is configured.
Step 2: Configure the server
In MySQL Configurator, fill in each screen:
| Screen | Recommended setting |
|---|---|
| Type and Networking | Server if other applications run on the machine, Dedicated if it is used only for MySQL |
| Connectivity | TCP/IP, port 3306 |
| Open Windows Firewall port | Unchecked: you will create a rule limited to your address |
| Accounts and Roles | Strong password for root |
| Windows Service | Service enabled, Start the MySQL Server at System Startup checked |
| Run Windows Service as | Standard System Account |
| Server File Permissions | Default option, restricting the data to the service and administrators |
| Sample Databases | None in production |
Finish with Execute, then check the service and add the tools to the machine Path, so they are available in every session:
Get-Service -Name 'MySQL*'
$bin = 'C:\Program Files\MySQL\MySQL Server 9.7\bin'
[Environment]::SetEnvironmentVariable('Path', [Environment]::GetEnvironmentVariable('Path', 'Machine') + ";$bin", 'Machine')
$env:Path += ";$bin"
mysql --user=root --password --execute="SELECT VERSION();"The service name depends on the version. Use the one shown by Get-Service in the following commands.
The important files are in C:\ProgramData\MySQL\MySQL Server 9.7: my.ini for the configuration and the Data folder for the databases and the error log. ProgramData is a hidden folder.
Step 3: Create a database and an account
Open the client:
mysql --user=root --passwordCreate a database and an account reserved for a local application:
CREATE DATABASE app_db CHARACTER SET utf8mb4;
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'CHANGE_THIS_PASSWORD';
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_user'@'localhost';Add CREATE, ALTER, INDEX, DROP only if the application manages its own schema. Do not use root in your applications.
Step 4: Choose the access mode
Application on the same server
If only local applications use MySQL, restrict listening to the loopback interface. Open my.ini in Notepad, run as administrator, and add these lines to the [mysqld] section:
bind_address=127.0.0.1
mysqlx_bind_address=127.0.0.1Restart the service, then go to step 6:
Get-Service -Name 'MySQL*' | Restart-ServiceAccess from a remote computer
Create an account limited to your address and require encryption:
CREATE USER 'remote_user'@'YOUR_IP' IDENTIFIED BY 'CHANGE_THIS_PASSWORD_2' REQUIRE SSL;
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'remote_user'@'YOUR_IP';You can also refuse all unencrypted TCP connections on the server. First check that your local applications support TLS:
SET PERSIST require_secure_transport = ON;Then allow only your address in the firewall:
New-NetFirewallRule -DisplayName 'MySQL-3306-In' -Direction Inbound -Protocol TCP -LocalPort 3306 -RemoteAddress 'YOUR_IP' -Action AllowThis rule does not change your RDP access. The Firewall configuration guide details the graphical method. Do not open the X Protocol port 33060 without a specific need.
If you left the firewall option checked in MySQL Configurator, find the rule it created and delete it after checking its name:
Get-NetFirewallPortFilter |
Where-Object { $_.LocalPort -contains '3306' -or $_.LocalPort -contains '33060' } |
Get-NetFirewallRule |
Select-Object Name, DisplayName, Enabled, Direction, ActionThen delete the rule in question with Remove-NetFirewallRule -Name RULE_NAME, using the value from the Name column.
Step 5: Connect remotely
Your computer needs the mysql client, which you can install with the same MSI package, for example. On first startup, MySQL generates self-signed certificates in its Data folder. Copy only ca.pem to your computer, and never copy the *-key.pem files. Then test the port and the connection:
Test-NetConnection YOUR_SERVER_IP -Port 3306
mysql --host=YOUR_SERVER_IP --user=remote_user --password --ssl-mode=VERIFY_CA --ssl-ca=C:\path\to\ca.pem app_dbVERIFY_CA checks that the server presents a certificate signed by this authority. The VERIFY_IDENTITY mode does not work with the automatically generated certificates, because they do not contain the server name.
Once connected, check the encryption:
SHOW SESSION STATUS LIKE 'Ssl_cipher';A non-empty value confirms an encrypted connection.
Step 6: Automate backups
Create a dedicated account, limited to the read access mysqldump needs:
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'CHANGE_THIS_PASSWORD_3';
GRANT SELECT, SHOW VIEW, TRIGGER, EVENT ON *.* TO 'backup_user'@'localhost';The global SELECT is required by the --routines option.
While signed in to Windows with the account that will run the task, save its MySQL credentials. The password is requested without being displayed:
mysql_config_editor set --login-path=backup --host=localhost --user=backup_user --password
mysql_config_editor print --allThis file, stored in %APPDATA%\MySQL, is specific to the Windows user. Its content is only obfuscated, not truly encrypted: keep this account well protected.
Prepare the folders:
New-Item -ItemType Directory -Force -Path 'C:\Backups\MySQL', 'C:\Scripts' | Out-Null
icacls 'C:\Scripts' /inheritance:r /grant:r '*S-1-5-32-544:(OI)(CI)F' '*S-1-5-18:(OI)(CI)F'
icacls 'C:\Backups\MySQL' /inheritance:r /grant:r '*S-1-5-32-544:(OI)(CI)F' '*S-1-5-18:(OI)(CI)F'Create C:\Scripts\backup-mysql.ps1:
$bin = 'C:\Program Files\MySQL\MySQL Server 9.7\bin'
$backupDir = 'C:\Backups\MySQL'
$databases = @('app_db')
$retentionDays = 14
$stamp = Get-Date -Format 'yyyyMMdd-HHmm'
foreach ($db in $databases) {
$file = Join-Path $backupDir "$db-$stamp.sql"
& "$bin\mysqldump.exe" --login-path=backup --single-transaction --routines --events --no-tablespaces "--result-file=$file" $db
if ($LASTEXITCODE -ne 0) {
Remove-Item -Path $file -ErrorAction SilentlyContinue
exit 1
}
}
Get-ChildItem -Path $backupDir -Filter '*.sql' |
Where-Object { $_.LastWriteTime -lt (Get-Date).AddDays(-$retentionDays) } |
Remove-Item--single-transaction produces a consistent copy of InnoDB tables without locking them. MyISAM or MEMORY tables may change during the export.
Test the script, then schedule it. Specify the Windows account used for mysql_config_editor, which must be a member of the Administrators group to read the script: Windows will ask for its password.
powershell.exe -NoProfile -ExecutionPolicy Bypass -File 'C:\Scripts\backup-mysql.ps1'
schtasks /Create /TN 'Backup-MySQL' /TR "powershell.exe -NoProfile -ExecutionPolicy Bypass -File C:\Scripts\backup-mysql.ps1" /SC DAILY /ST 02:45 /RU 'YOUR_USER' /RP '*' /RL HIGHESTRegularly copy the files off the server: a backup kept on the same disk does not protect you from a failure of that disk.
Test a restore
Restore into a test database so you do not overwrite production. Use forward slashes in the path:
mysql --user=root --password --execute="CREATE DATABASE app_db_test CHARACTER SET utf8mb4;"
mysql --user=root --password app_db_test --execute="source C:/Backups/MySQL/app_db-20260101-0245.sql"
mysql --user=root --password --execute="SHOW TABLES FROM app_db_test;"Once you have checked it, delete the copy:
DROP DATABASE app_db_test;Check the result
Get-Service -Name 'MySQL*' | Select-Object Name, Status, StartTypeshowsRunningandAutomatic.- The application account connects, and
SHOW DATABASES;lists only its own database andinformation_schema. - With remote access,
Ssl_cipheris not empty and another address cannot reach port 3306. - With local access only,
Get-NetTCPConnection -LocalPort 3306 -State Listenshows127.0.0.1. Get-ScheduledTaskInfo -TaskName 'Backup-MySQL'showsLastTaskResultat0after a run, and a recent.sqlfile exists.
Troubleshooting
The service does not start
Start the service from the console to see the message, replacing the name:
Get-Service -Name 'MySQL*'
net start SERVICE_NAMEThen check the .err file in the C:\ProgramData\MySQL\MySQL Server 9.7\Data folder. System error 1067 with Can't open and lock privilege tables often points to incorrect basedir or datadir paths in my.ini. A line added outside the [mysqld] section can also prevent startup.
ERROR 2003 (HY000): Can't connect to MySQL server (10061 or 10060)
- 10061: the connection is refused. Check that the service is running, that the port is correct and that
bind_addressis not limited to127.0.0.1. - 10060: the connection timed out, which often suggests a firewall. Check the
MySQL-3306-Inrule and your current public address.
ERROR 1130 (HY000): Host is not allowed to connect
No account matches your address. Compare it with the existing accounts:
SELECT user, host FROM mysql.user ORDER BY user, host;If your public address has changed, create the account for the new address, grant it the rights and delete the old one.
ERROR 1045 (28000): Access denied for user
The password is incorrect, or the user@host account being used is not the one you think. localhost and your public address are two separate accounts in MySQL.
ERROR 3159: Connections using insecure transport are prohibited
require_secure_transport is active and the client is attempting an unencrypted connection. Enable TLS in the client with --ssl-mode=REQUIRED or, better, VERIFY_CA.
ERROR 2061: Authentication requires secure connection
The account uses caching_sha2_password, the default plugin, and the connection is not encrypted. Enable TLS on the client side. If your application is too old for this plugin, update its connector.
Access denied; you need the PROCESS privilege or an error on routines
Keep --no-tablespaces in the script, and check the account's rights:
SHOW GRANTS FOR 'backup_user'@'localhost';The scheduled task fails even though the script works
The task must run under the same Windows account that ran mysql_config_editor, because the credentials file is specific to each user. If the Windows password has changed, update the task:
schtasks /Change /TN 'Backup-MySQL' /RU 'YOUR_USER' /RP '*'Lost root password
- Stop the MySQL service.
- Create
C:\mysql-init.txtcontaining only this line, with your new password:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NEW_PASSWORD';- In an administrator command prompt, start the server with this file:
cd "C:\Program Files\MySQL\MySQL Server 9.7\bin"
mysqld --defaults-file="C:\\ProgramData\\MySQL\\MySQL Server 9.7\\my.ini" --init-file=C:\\mysql-init.txt --console- As soon as the server has started, delete
C:\mysql-init.txt, stop this process withCtrl+Cif necessary, then restart the service normally.