Simple Remote MySQL Server Configuration from Scratch with Python

This guide is divided into:
    1. [Step-to-step MySQL Installation](#step-to-step-mysql-installation)
    2. [Server and Database Configuration with Python](#server-and-database-configuration-with-python)
    3. [Client-side Python Example](#client-side-python-example)


## Step-to-step MySQL Installation
1. Download the [MySQL Installer](https://dev.mysql.com/downloads/file/?id=480824). (You can go to the page if you want with [this link](https://dev.mysql.com/downloads/windows/installer/8.0.html))

2. Run the Installer.

2. Select Custom as Setup Type and click Next.
![image.png](https://qiita-image-store.s3.amazonaws.com/0/303209/99ef6adf-8c10-6b28-95c4-8bd8506b72d3.png)

4. Select MySQL Server and the appropriate MySQL Connector for your version and click Next.
![image.png](https://qiita-image-store.s3.amazonaws.com/0/303209/692489b3-b9fb-9cf5-8a46-80b3d5df57f4.png)

5. Click Execute and wait for the installation.
![image.png](https://qiita-image-store.s3.amazonaws.com/0/303209/f3647b60-5f8c-2149-8560-60438ced7ee8.png)

6. Click Next x2
![image.png](https://qiita-image-store.s3.amazonaws.com/0/303209/5332f14e-9ae4-2820-ff21-af8c1b78fd4f.png)
![image.png](https://qiita-image-store.s3.amazonaws.com/0/303209/671cf7c5-0ff7-3c2f-8867-16e336b81f8c.png)

7. Select "Standalone MySQL Server / Classic MySQL Replication" and click Next.
![image.png](https://qiita-image-store.s3.amazonaws.com/0/303209/ad04aba2-cd2c-370b-ae4b-8643b01cac27.png)

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*
![image.png](https://qiita-image-store.s3.amazonaws.com/0/303209/6c924ac4-63ab-8e3c-8502-f8b03e3fb646.png)

9. Select "Use Strong Password Encryption for Authentication (RECOMMENDED)" and click Next.
![image.png](https://qiita-image-store.s3.amazonaws.com/0/303209/6de8d8c2-53b1-6b1f-6206-3829adfbec46.png)

10. Decide on a password for the root account, and create a user with the role "DB Admin". Then, click Next.
![image.png](https://qiita-image-store.s3.amazonaws.com/0/303209/840c7db9-6f42-db60-8fa9-8adf9030dc15.png)

11. Click on Next.
![image.png](https://qiita-image-store.s3.amazonaws.com/0/303209/386a5cab-6c51-45b4-b515-79628decc47e.png)

12. Click on Execute and wait for the configuration.
![image.png](https://qiita-image-store.s3.amazonaws.com/0/303209/c98623fb-955d-70ac-ddcc-729aabe7ac31.png)

13. Click on Finish
![image.png](https://qiita-image-store.s3.amazonaws.com/0/303209/1c0fc777-d8f7-b104-c955-554fc38c1718.png)

14. Click on Next
![image.png](https://qiita-image-store.s3.amazonaws.com/0/303209/4d283d15-1c47-c0a2-f4fd-9dc07e1b67c2.png)

15. Click on Finish
![image.png](https://qiita-image-store.s3.amazonaws.com/0/303209/c4b76a28-91f5-5c53-7032-d6172b7ff147.png)

##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.

```python: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.

```python: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.

```python: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.

```python: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
```python: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.

```python:client_example.py
import mysql.connector as db
```

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

```python: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.

```python: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.

```python: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

```python: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()]
```