Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.
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.
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:
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:
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
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;
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.
Finally, many database concepts aren't discussed in detail here. If you feel you want to dig more into databases with Python, I highly suggest you get these courses:
Happy Coding ♥View Full Code