Python MySQL 원격접속

개요

Python으로 MySQL에 연결하여 사용하는법을 정리하도록 하겠습니다
먼저 MySQL이 설치되어있야 하기때문에 밑에 링크를 통해 설치부터 진행 부탁드립니다.

MySQL 설치 및 구성 링크

MySQL 방화벽 오픈 및 사용자 추가

MySQL 서버 주소 확인
공인주소 확인
$ curl ipinfo.io/ip
내부주소 확인
hostname -I
ip a

MySQL 서버 방화벽 오픈
다른 서버에서 MySQL 운영서버로 MySQL에 대한
연결을 시도할때 방화벽이 허용되어있어야 합니다.
sudo ufw allow mysql

$ sudo ufw allow mysql
Rules updated
Rules updated (v6)

MySQL 접속
mysql에 root으로 접속하여 원격접속에 사용할 사용자를 생성합니다.
sudo /usr/bin/mysql -u root -p

$ sudo /usr/bin/mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.33-0ubuntu0.20.04.4 (Ubuntu)

사용자 생성
원격에서 사용할 사용자를 생성하는데 원격 주소를 지정할 수 있습니다.
원격 주소는 '8.8.8.8'처럼 정적주소를 지정할 수 있고 '192.168.10.0/24' 처럼 주소범위로 지정할 수 있습니다. 또한 '*'을 통해 모든 주소 공간을 사용 할 수 있지만 보안에 취약하기때문에 주의해야합니다.
CREATE USER '유저이름'@'원격 및 내부에서 사용할 ip주소' IDENTIFIED BY 'MySQL 정책에 맞는 비밀번호';

저는 원격에서 사용할 것이기 때문에 제 공유기 ip를 지정하도록 하겠습니다.
CREATE USER 'python_hyun'@'원격접속주소' IDENTIFIED BY 'StrongPass123@';

## 범위를 지정하여 구성할 수 있습니다.
mysql> CREATE USER 'mysql_hyun'@'*' IDENTIFIED BY 'StrongPass123@';
Query OK, 0 rows affected (0.02 sec)

## 모든 주소에서 접근하도록 구성
mysql> CREATE USER 'mysql_hyun2'@'192.168.10.0/24' IDENTIFIED BY 'StrongPass123@';
Query OK, 0 rows affected (0.02 sec)

권한 부여

생성한 사용자를 MySQL 데이터베이스와 테이블에 권한을 부여할 수 있습니다.

읽기, 삽입, 업데이트 권한 부여
GRANT SELECT, INSERT, UPDATE ON <database>.<table> TO '<사용자>'@'<주소>';
모든 권한 부여
GRANT ALL PRIVILEGES ON <database>.<table> TO '<사용자>'@'<주소>';

저는 SELECT, INSERT, UPDATE 권한만 부여하겠습니다.
GRANT ALL PRIVILEGES ON hyuntestdb.* TO 'python_hyun'@'<원격주소>';

## 읽기, 삽입, 업데이트 권한 부여
GRANT SELECT, INSERT, UPDATE ON <database>.<table> TO '<사용자>'@'<주소>';

## MySQL에 대한 모든 데이터베이스 및 테이블에 모든 권한 부여
GRANT ALL PRIVILEGES ON <database>.<table> TO '<사용자>'@'<주소>';

## 생성한 사용자에게 권한 부여
mysql> GRANT ALL PRIVILEGES ON *.* TO 'python_hyun'@'원격접속주소';
Query OK, 0 rows affected (0.02 sec)

로컬 쿼리 실행
MySQL이 설치된 로컬에서 쿼리를 실행해봅니다.

Use hyuntestdb;

mysql> SELECT * FROM users;
+----+------+
| id | name |
+----+------+
|  1 | hyun |
|  2 | been |
|  3 | Hyun |
|  4 | Been |
+----+------+
4 rows in set (0.00 sec)

---

Python MySQL 접속

모듈 설치
먼저 mysql 모듈을 사용하기 위해서 다운받아야합니다.
sudo apt update
pip install mysql-connector-python

## 해당 mysql에 모듈이 없을 경우
## pip install mysql-connector-python

import mysql.connector

## MySQL 연결
connection = mysql.connector.connect(
    host='MySQL 서버주소',
    user='생성 및 권한부여 사용자',
    password='사용자 비밀번호',
    database='접속할 데이터베이스',
    port='데이터베이스 포트' ## 기본적으로 3306
)

## cursor 개체만들기
cursor = connection.cursor()

# 'users' 테이블에서 모든 행 가져오기
cursor.execute("SELECT * FROM users")

# 
results = cursor.fetchall()
print(results)

## 결과값
## [(1, 'hyun'), (2, 'been'), (3, 'Hyun'), (4, 'Been')]


# Process the results (e.g., print them)
for row in results:
    print(row)
    
## 결과값
## (1, 'hyun')
## (2, 'been')
## (3, 'Hyun')
## (4, 'Been')