• Register
  • FAQs
  • Contact
  • Time Zone
  • Chat on WhatsApp
  • Home
  • Learning Blog
  • How to Set Up and Connect to MySQL on a Windows VPS Server from Python: A Step-by-Step Guide

How to Set Up and Connect to MySQL on a Windows VPS Server from Python: A Step-by-Step Guide

Friday 30 August 2024, by Moshiur Rahman

How to Set Up and Connect to MySQL on a Windows VPS Server from Python: A Step-by-Step Guide

Setting up MySQL on a Windows VPS server and connecting to it from a Python application can seem daunting, especially if you're new to these technologies. This guide will walk you through the entire process, including how to handle common issues like authentication plugin errors.


Table of Contents

  1. Installing MySQL on a Windows VPS Server
  2. Configuring MySQL for Remote Access
  3. Connecting to MySQL from Python
  4. Handling Authentication Plugin Issues
  5. Troubleshooting Tips

1. Installing MySQL on a Windows VPS Server


Step 1: Download and Install MySQL


Download MySQL Installer:
Go to the MySQL Downloads pages and download the MySQL Installer for Windows.

Run the Installer:
Double-click the downloaded file to start the installer.

Choose Setup Type:
Select the “Developer Default” option. This includes the MySQL Server and other necessary tools.

Configure MySQL Server:
During the setup process, you'll be asked to set a root password. Choose a strong password and remember it, as you'll need it later.

Add MySQL to PATH (if not done automatically):
Open System Properties:
Right-click on This PC or Computer on the desktop or in File Explorer, then click Properties.
Click Advanced system settings on the left.
Click the Environment Variables button.

Add MySQL to PATH:
In the System variables section, find the Path variable and click Edit.
Click New and add the path to MySQL’s bin directory, which is typically C:\Program Files\MySQL\MySQL Server 8.0\bin\
Click OK to save changes.

Complete Installation:
Finish the installation process and ensure MySQL Server is running.

Step 2: Verify MySQL Installation


Open Command Prompt:
Press Win + R, type cmd, and press Enter.

Check MySQL Version:

Run the command:
mysql --version
You should see the MySQL version number if the installation was successful.

2. Configuring MySQL for Remote Access


Step 1: Configure MySQL to Listen for Remote Connections


Locate MySQL Configuration File:
The file is typically located at C:\ProgramData\MySQL\MySQL Server 8.0\my.ini or C:\Windows\my.ini.

Edit the Configuration File: Open my.ini with a text editor (e.g., Notepad with Administrator privileges).

Find the line:
bind-address = 127.0.0.1

Change it to:
bind-address = 0.0.0.0
Save and close the file.

Restart MySQL Service:
Open Command Prompt as Administrator:
net stop MySQL80 net start MySQL80

Step 2: Grant Remote Access to MySQL User

Log in to MySQL Locally:
Open Command Prompt and log in:
mysql -u root -p
Enter your root password when prompted.

Grant Privileges: Run the following commands to grant remote access:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password'; mysql> FLUSH PRIVILEGES;

Verify Privileges: Check the privileges with:
mysql> SHOW GRANTS FOR 'root'@'%';

3. Connecting to MySQL from Python


Step 1: Install Python MySQL Connector Library

Open Command Prompt: Run:
pip install mysql-connector-python

Step 2: Write Python Script to Connect to MySQL

Here’s a sample Python script using mysql-connector-python:

import mysql.connector  
from mysql.connector import Error
try:
    # Establish the connection
    connection = mysql.connector.connect(
        host='',
        port=,
        user='root',
        password='',
        database='',
        auth_plugin='mysql_native_password'
    )
    if connection.is_connected() :
        db_info = connection.get_server_info()
        print("Connected to MySQL Server version ", db_info)
        cursor = connection.cursor()
        cursor.execute("SELECT DATABASE();")
        record = cursor.fetchone()
        print("You're connected to database: ", record)
except Error as e:
    print("Error while connecting to MySQL", e)
finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

Step 3: Run the Python Script


  • Save the script as connect_mysql.py
  • Run it from Command Prompt:
    • python connect_mysql.py

4. Handling Authentication Plugin Issues


Issue: Authentication Plugin 'caching_sha2_password' Not Supported If you encounter the error “Authentication plugin 'caching_sha2_password' is not supported,” you can resolve it by:

Changing the Authentication Plugin:
Log in to MySQL:
mysql -u root -p
Run:
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY ''; mysql> FLUSH PRIVILEGES;

Verify the Change:
Check the user’s authentication plugin:
SELECT user, host, plugin FROM mysql.user WHERE user = 'root';

Re-run Your Python Script:
The script should now connect successfully.

5. Troubleshooting Tips


  • Firewall Issues: Ensure that port 3306 is open on your VPS server.
  • Correct IP Address: Verify that you are using the correct IP address for your VPS.
  • Permissions: Ensure the MySQL user has the correct privileges for remote access.
  • Check Logs: Review MySQL server logs for any errors or warnings.

By following this guide, you should be able to set up MySQL on a Windows VPS server, configure it for remote access, and connect to it from a Python application. This guide also covers how to handle common issues, ensuring a smooth setup process.

About The Author

Name: Moshiur Rahman Shohel

Nickname: Shohel

Designation: Lecturer, Full Stack Developer

Specialisation: SQL, C#, Python, PHP, JavaScript, HTML, CSS

Join the discussion by adding your comments below:

« Back to the Learning Blog

We are providing affordable IT training courses at times to suit you - enhance your employability and stand out from the crowd.

Become an IT expert and get that competitive edge that employers are looking for.

Popular Courses

Useful Links

Contact Us

London Academy of IT
Unit 15, Boardman House
64 Broadway
Stratford
London E15 1NT
United Kingdom
UK: +44 (0)208 432 6218
USA: +1 610-234-7870
CANADA: +1 416-900-1211
WhatsApp: +44 0749 461 6045
Chat with London Academy of IT on WhatsApp

Share this page now!

2012 - 2024 © London Academy of IT Limited. All Rights Reserved.
Registered in England & Wales with company no. 07923992.