Set Up a Remote MySQL Database on a Windows VPS

Hosting a remote MySQL database on a Windows VPS allows you to store, manage, and access databases from external applications, websites, or game servers. This guide will walk you through installing, configuring, and securing a remote MySQL server on a Windows Server VPS.

📌 Prerequisites

A Windows Server VPS (2016, 2019, or 2022) from VolticHost
Administrator access
Ports 3306 (TCP) open in the firewall
MySQL Community Server installed

1️⃣ Install MySQL Server on Windows VPS

Download MySQL Community Server from:
👉 https://dev.mysql.com/downloads/installer/
Run the installer and choose "Server Only" when prompted.
Click Next and complete the installation.

✅ MySQL Server is now installed!

2️⃣ Configure MySQL for Remote Access

Open MySQL Configuration File (my.ini):

notepad C:\ProgramData\MySQL\MySQL Server X.X\my.ini

Find the line:
```
bind-address = 127.0.0.1
```
Change it to:
```
bind-address = 0.0.0.0
```
Save the file and restart MySQL:
```
net stop mysql
net start mysql
```

✅ Your MySQL server is now accessible remotely!

3️⃣ Create a Remote MySQL User

Open Command Prompt (Admin) and log into MySQL:
```
mysql -u root -p
```

Create a new user with remote access (replace YourPassword with a strong password):

CREATE USER 'remote_user'@'%' IDENTIFIED BY 'YourPassword';
GRANT ALL PRIVILEGES ON *.* TO 'remote_user'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Exit MySQL:
```
exit
```

✅ Your MySQL user is now set up for remote access!

4️⃣ Open MySQL Port 3306 in Windows Firewall

Open Windows Defender Firewall (Win + R, type wf.msc, press Enter).
Click Advanced Settings → Inbound Rules → New Rule.
Select Port, then click Next.
Choose TCP, enter 3306, then click Next.
Select Allow the connection, then click Next again.
Apply to Domain, Private, and Public networks.
Name the rule MySQL Remote Access and click Finish.

✅ Your MySQL server is now accessible from external connections!

5️⃣ Test the Remote MySQL Connection

From Another Server or PC

Open Command Prompt on a remote machine.

Connect to the MySQL server using:

mysql -u remote_user -p -h Your-VPS-IP

Enter the password when prompted.

If the connection is successful, MySQL will return:

Welcome to the MySQL monitor...

✅ Your remote MySQL database is working!

6️⃣ Secure Your Remote MySQL Server (Recommended)

Limit Remote Access to Specific IPs

Instead of allowing all IPs (%), restrict MySQL access to a specific IP (e.g., 192.168.1.100):

CREATE USER 'remote_user'@'192.168.1.100' IDENTIFIED BY 'YourPassword';
GRANT ALL PRIVILEGES ON *.* TO 'remote_user'@'192.168.1.100' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Disable Root Remote Access

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'root'@'%';

Enable MySQL Firewall Protection

Use Fail2Ban or Windows Firewall rules to prevent brute-force attacks.

✅ Your remote MySQL server is now more secure!

🎉 Conclusion

You've successfully set up a remote MySQL database on a Windows VPS! Your applications, websites, and game servers can now connect to the centralized database remotely. 🚀

For more assistance, contact VolticHost Support.