Simple Remote MySQL Server Configuration from Scratch with Python

This guide is divided into:
1. Step-to-step MySQL Installation
2. Server and Database Configuration with Python
3. Client-side Python Example

Step-to-step MySQL Installation

1. Download the MySQL Installer. (You can go to the page if you want with this link)

2. Run the Installer.

3. Select Custom as Setup Type and click Next.
   

4. Select MySQL Server and the appropriate MySQL Connector for your version and click Next.
   

5. Click Execute and wait for the installation.
   

6. Click Next x2
   
   

7. Select "Standalone MySQL Server / Classic MySQL Replication" and click Next.
   

8. Select "Server Computer" as Config Type and click Next.
   Note: There is not really a difference between Developer Computer and Server Computer for this tutorial
   

9. Select "Use Strong Password Encryption for Authentication (RECOMMENDED)" and click Next.
   

10. Decide on a password for the root account, and create a user with the role "DB Admin". Then, click Next.
    

11. Click on Next.
    

12. Click on Execute and wait for the configuration.
    

13. Click on Finish
    

14. Click on Next
    

15. Click on Finish
    

Server And Database Configuration with Python

Note: The following can be done in MySQL Shell, but I decided to do so in Python as you may want to implement the following code in your projects.

Install the Connector

First, in the CommandPrompt enter pip install mysql-connector to install the connector to be used in Python.

We can start creating our python file.

Python Step-by-Step

Let's start by importing the recently installed package.

setup_example.py

import mysql.connector as db

Then, we create an instance of MySQLConnection using the root account and the password created during the MySQL installation and initialize the cursor.

setup_example.py

mydb = db.connect(
	host = "localhost",
	user = "root",
	passwd = "password"
	)
mycursor = mydb.cursor()

After this, we create a database and a user with privileges to use the database.

setup_example.py

mycursor.execute("CREATE DATABASE IF NOT EXISTS my_database;")

# the username should be quoted as well as the user's password.
# replace clientipaddress with the client-computer's ip address
mycursor.execute("CREATE USER IF NOT EXISTS 'username'@clientipaddress IDENTIFIED BY 'password';")
mycursor.execute("GRANT ALL PRIVILEGES ON *.* to 'username'@clientipaddress;")

After this we can access the database to create a simple table.

setup_example.py

mycursor.execute("use my_database;")
mycursor.execute("CREATE TABLE my_table (name varchar(20),age int(2));")

With this, we have finished the configuration of the database.

Python Full Code

setup_example.py

import mysql.connector as db

mydb = db.connect(
	host = "localhost",
	user = "root", # the server's main account
	passwd = "password" # and its password
	)

mycursor = mydb.cursor()

mycursor.execute("CREATE DATABASE IF NOT EXISTS my_database;")

# the username and password should be quoted should be quoted with ' ' .
# replace clientipaddress with the client-computer's ip address
mycursor.execute("CREATE USER IF NOT EXISTS 'username'@clientipaddress IDENTIFIED BY 'password';")
mycursor.execute("GRANT ALL PRIVILEGES ON *.* to 'username'@clientipaddress;")

# select which database we will be using
mycursor.execute("use my_database;")

# create the table
mycursor.execute("CREATE TABLE my_table (name varchar(20),age int(2));")

Let's make a simple example of a client application.

Client-side Python Example

In this example we will connect to the remote database and insert a tuple into the 'my_table' table.

Python Step-by-Step

As before, we import the connector.

client_example.py

import mysql.connector as db

Then, we connect to the remote database and initialize our cursor.

client_example.py

mydb = db.connect(
	host = serverpaddress, # replace for the server's IP address
	user = "username", # previously created user
	passwd = "password", # and its password
	database = "my_database" # we directly connect to the desired database
	)

mycursor = mydb.cursor()

After this, we define the command, assign values, and execute the command. We also commit out database.

client_example.py

sql_command = "INSERT INTO my_table (name, age) values (%s, %s);"
values = ('my_name', 1)
mycursor.execute(sql_command, values)

# save changes to the database
mydb.commit();

We can view the information inside the table using SELECT.

client

sql_command = "select * from my_table"
mycursor.execute(sql_command)

[print(x) for x in mycursor.fetchall()]

The output should be ('my_name', 1)

Example's Full Code

client_example.py

# import necessary packages
import mysql.connector as db

# connect to the database
mydb = db.connect(
	host = serveripaddress, # replace for the server's IP address
	user = "username", # previously created user
	passwd = "password", # and its password
	database = "my_database" # we directly connect to the desired database
	)

# initialize cursor
mycursor = mydb.cursor()

# define command
sql_command = "INSERT INTO my_table (name, age) values (%s, %s);"

# assign values
values = ('my_name', 1)

# execute command
mycursor.execute(sql_command, values)

# save changes to the database
mydb.commit();

# import information from the database
sql_command = "select * from my_table"
mycursor.execute(sql_command)

# output information
[print(x) for x in mycursor.fetchall()]