How to Connect to a Remote MySQL Database in Python

Abdeladim Fadheli · 4 min read · Updated may 2024 · Database

Get a head start on your coding projects with our Python Code Generator. Perfect for those times when you need a quick solution. Don't wait, try it today!

As a software developer, you may encounter the necessity of connecting to a remote MySQL server in your application. However, the MySQL default setting doesn't allow remote connections. In this tutorial, you will learn how to connect to a remote MySQL server in Python.

We'll be using the Python MySQL connector library, let's install it:

pip3 install mysql-connector-python

The below code is responsible for connecting to a MySQL server:

import mysql.connector as mysql

# enter your server IP address/domain name
HOST = "x.x.x.x" # or "domain.com"
# database name, if you want just to connect to MySQL server, leave it empty
DATABASE = "database"
# this is the user you create
USER = "python-user"
# user password
PASSWORD = "Password1$"
# connect to MySQL server
db_connection = mysql.connect(host=HOST, database=DATABASE, user=USER, password=PASSWORD)
print("Connected to:", db_connection.get_server_info())
# enter your code here!

mysql.connect() method requires 4 arguments:

host: this is your remote MySQL server, you can either use its IP address or domain name.
database: the name of the database you want to access, you can leave it empty if you only want to connect to the MySQL server.
user: this is the name of the user that you'll be creating for remote accesses, we'll see in a minute how to create one.
password: the password of that user.

If you change these parameters to your server details (even if correct details), you'll encounter a confusion error like this:

mysql.connector.errors.ProgrammingError: 1045 (28000): Access denied for user 'python-user'@'your ip address' (using password: YES)

That's because simply, MySQL doesn't allow remote connections, let's see can we fix that.

Allowing Remote Connections on MySQL Server

First, you'll need to search for the mysqld.cnf file in your system (my.ini on Windows), this file placement may vary depending on your MySQL version and operating system. The easiest way to find your MySQL configuration file, is to search for it, let's run the following command:

$ locate mysqld.cnf

After I ran the above command on Ubuntu 18.04, the following path was printed:

/etc/mysql/mysql.conf.d/mysqld.cnf

So go to that file and search for the line that begins with bind-address. By default, it's set to 127.0.0.1, which means the server will only accept local connections, you need to set it to your external public IP address, or you want to set it to 0.0.0.0 if your IP address isn't static (may change on reboots, etc). If you can't find the line in that file, just add it:

Allowing remote connections on MySQL Server

Secondly, you need to allow your client/remote IP address the access to the MySQL port on the firewall:

$ sudo ufw allow from <Remote_IP_Address> to any port 3306

If your remote IP address changes or you may want to allow it from all over the world (maybe a little bit dangerous), you can allow that port for everyone:

$ sudo ufw allow 3306

Creating a User for Remote Access

Finally, go to your MySQL server and create a user:

/* '%' means from any where in the world*/
mysql> CREATE USER 'python-user'@'%' IDENTIFIED BY 'Password1$';

You may be familiar creating users for localhost, in this case, we used '%' character, which means this user can be accessed from any remote host.

Again, if you want a specific remote IP address instead of everyone, you can change '%' with '<Remote_IP_Address>', will look something like:

mysql> CREATE USER 'python-user'@'<Remote_IP_Address>' IDENTIFIED BY 'password';

You need to replace <Remote_IP_Address> with the actual IP address of the machine you plan to connect with.

If you go back to your script and run it, you'll get a different error this time:

mysql.connector.errors.ProgrammingError: 1044 (42000): Access denied for user 'python-user'@'%' to database 'database'

That's easy to fix, we need to grant the newly created user all the privileges, go back to your MySQL server, and execute the following command:

/* grant all privileges to the user on all databases & tables available in the server */
mysql> GRANT ALL ON *.* TO 'python-user'@'%';

With this command, we have granted this user all privileges on all databases and all tables, you can customize that in whatever way you want.

Lastly, let's flush the privileges so MySQL will begin using them:

mysql> FLUSH PRIVILEGES;

Conclusion

Awesome, now by following this tutorial, you are able to connect to your remote MySQL server not just from Python, but from any programming language.

If you're not sure how to use Python's MySQL connector, check this tutorial which teaches you how you can create a database, tables, adding rows to that table, fetching data, etc.

Happy Coding ♥

Loved the article? You'll love our Code Converter even more! It's your secret weapon for effortless coding. Give it a whirl!

View Full Code Convert My Code

Sharing is caring!

Comment panel

Got a coding query or need some guidance before you comment? Check out this Python Code Assistant for expert advice and handy tips. It's like having a coding tutor right in your fingertips!