MySQL (Express와 함께)

MySQL

나의SQL (아님)

오픈소스 라이선스로 무료이고, 다양한 프로그래밍 언어를 지원하며, ANSI SQL을 준수하고 비교적 빠르고 널리 쓰이는 MySQL을 잡수어보세요

이전에 사용했던 sqlite는 한 파일 = 하나의 데이터베이스 였지만 MySQL은 데이터베이스 서버를 사용하기 때문에 사용이 쫌 복잡하다

sqlite는 단순하고 빠른 구축을 원할 때 사용했지만, 데이터베이스 구조가 조금만 복잡해져도 보안상의 이슈나 동시성 문제 때문에 MySQL이 좋은 해결책이 될 수 있다

설치 및 CLI에서 사용해보기

CLI가 어렵고 복잡하다면 워크벤치를 사용하면 좋다 (GUI다!)

0. 로컬 환경에 MySQL 설치 및 설정

$> brew install mysql
$> mysql -V // 버전 확인

기존의 node.js에서만 돌아가던 라이브러리들과 달리, MySQL은 로컬 환경에 설치를 필요로 한다

node.js의 라이브러리인 mysql은 로컬의 mysql과 연동하기 위한 장치일 뿐…

또한 데이터베이스가 서버 경로에 파일로 저장되어 그것을 불러오기만 하면 됐던 sqlite와 달리, MySQL은 독자적인 저장공간 (보통 /var/lib/mysql) 이 있어 CLI나 워크벤치 (GUI) 와 병행하여 사용해야 한다

홈브루로 간단하게 설치하고, 버전을 확인하여 설치가 잘 되었는지 체크한다

 

$> brew services start mysql // mysql 서비스 시작
$> brew services stop mysql // mysql 서비스 중지 
$> brew services restart mysql // mysql 서비스 재시작

$> mysql.server start // mysql 서비스 시작
$> mysql.server stop // mysql 서비스 중지

연결실패 (흑흑)

MySQL 설치가 끝났다면 서비스를 켜 두어야 한다

서비스를 켜두지 않으면 MySQL 데이터베이스 서버가 돌아가지 않으므로 node.js에서 접근해도 오류가 난다

서버 관련 작업을 할 거라면 꼭 켜두고, 아니면 꺼두도록 하자

brew로 켜 두어도 되고, mysql.server를 통해 켜 두어도 된다

$> mysql_secure_installation

서버를 켰다면 MySQL 보안 설정을 진행한다

 

VALIDATE PASSWORD 컴포넌트를 사용하면 비밀번호를 테스트하고 보안 강도를 올릴 수 있다고 한다

일단 y

 

비밀번호의 강도를 지정할 수 있다

LOW는 123456789처럼 8자리 이상 문자열 아무거나 설정 가능

MEDIUM은 8자리 이상에 숫자와 대소문자, 특수문자를 포함

STRONG은 8자리 이상 숫자 + 대소문자 + 특수문자 + 사전 파일 사용

사전 파일을 사용하면 MySQL에 등록한 사전 파일에 있는 단어들을 비밀번호에 포함시킬 수 없다고 한다

로컬 환경이고 테스트 단계이니 LOW로 지정한다

 

루트 계정의 비밀번호를 설정한다

검증이 완료되면 비밀번호의 강도를 알려준다

위에서 설정한 비밀번호 강도에 대한 조건을 만족시키지 못하면 비밀번호 규정을 만족하지 못했다고 비밀번호 생성부터 다시 시키니 대충 하지 말고 (…) 신중하게 하자

마지막엔 이 비밀번호를 쓸 거냐고 한번 더 물어본다

y로 대답한다

 

익명 사용자를 허용할 건지 물어보는 질문이다

y로 대답하면 익명 사용자를 허용하지 않으므로, mysql 접속 시 -u 옵션으로 아이디를 지정해야 한다

n로 대답하면 익명 사용자를 허용하므로 유저로 등록되어 있지 않아도 아무나 접속할 수 있다

y로 대답했다

 

원격에서의 루트 접속을 막을 것인지 물어본다

y로 대답하면 원격에서 루트 계정으로 접속할 수 없고, 무조건 로컬호스트로만 접속해야 한다

n로 대답하면 원격에서 루트 계정으로 접속할 수 있다

y로 대답했다

 

기본으로 제공되는 테스트용 데이터베이스를 삭제할 것인지 물어보는 질문이다

테스트용이고 누구나 접근가능하니 배포 환경에서는 테스트 데이터베이스를 다 지우는 게 좋다고 한다

y로 대답하면 테스트 DB를 삭제해주고, n로 대답하면 그냥 놔둔다

y로 대답했다

 

지금까지 설정한 내용들을 권한 테이블에 적용할 것인지 물어본다

y로 대답하면 설정 내용을 적용해주고, n로 대답하면 적용하지 않는다

y로 대답했다

 

잘했어요~

MySQL을 사용하기 위한 준비가 끝났다

이제 CLI 환경에서 MySQL에 접속해보자

1. CLI에서 접속하기

그냥 mysql만 치면 접속이 안 된다

익명 유저 비허용을 해 두었기 때문이다..

 

$> mysql -uroot -p

-u (유저명) 플래그에 root를 입력하여 루트 계정으로 접속한다

플래그와 유저명은 띄어쓰기 없이 붙여서 입력한다

비밀번호도 마찬가지로 -p 플래그를 사용하는데, -u 플래그 사용하듯이 플래그 옆에 비밀번호를 그대로 입력하면 비밀번호가 노출되므로 -p 플래그만 입력하자

그러면 아래줄에서 비밀번호를 입력할 수 있도록 나온다

비밀번호를 제대로 입력하면 Welcome 메시지가 출력된다

2. CLI에서 데이터베이스 조회하기

mysql> show databases; // 이제 mysql 프로그램이 실행되었으므로 쉘의 $> 대신 mysql> 이 출력된다

show databases; 명령을 통해 지금까지 생성된 모든 데이터베이스를 볼 수 있다

모자이크한 최상단 데이터베이스를 제외하곤 MySQL에서 기본으로 만들어준다

3. CLI에서 현재 바라보는 데이터베이스 변경하기

mysql> use 데이터베이스명;

유저 설정을 만져볼 것이기 때문에 MySQL 관련 설정이 들어있는 mysql 데이터베이스로 변경하였다

use 명령을 사용하자

4. SELECT 명령을 통해 테이블 보기

mysql> select 필드이름 from 테이블이름

예시로 user 테이블의 값을 가져와 봤다

지금은 유저를 한 명 추가한 상태라 5명이지만, 하위 4개의 유저는 MySQL에서 기본으로 제공한다

테이블의 컬럼이 매우매우매우 많기 때문에 host (접속할 호스트명) 와 user (유저이름) 만을 가져왔다

select * ... 을 통해 모든 컬럼의 데이터를 다 가져올 수도 있긴 하다

5. 유저 추가, 권한설정, 삭제

mysql> create user 유저명 identified by '비밀번호';

유저가 하나 더 생성되었다

host에 다른 유저와 같이 localhost가 적혀있는 것이 아니라 %라고 되어있는데, 외부 접속이 가능하다는 것을 의미한다

 

mysql> create user '유저명'@'호스트명' identified by '비밀번호';
// 호스트명은 ip주소 또는 localhost 등

호스트명을 지정해주면 해당 호스트의 유저가 된다

이 유저는 해당 호스트 ip에서만 접속할 수 있으므로 로컬 환경 등에서 접속을 시도하면 접근 거부된다

 

mysql> grant {명령어모음 | all privileges} on DB이름.테이블이름 to '유저명';
mysql> grant {명령어모음 | all privileges} on DB이름.테이블이름 to '유저명'@'호스트명';
mysql> grant 명령어(컬럼이름, ...) on DB이름.테이블이름 to 사용자;

권한을 주고 싶을 땐 grant 명령을 사용한다

SELECT, INSERT 등 특정 명령어만 사용가능하게끔 설정하려면 해당 명령어를 쉼표 기준으로 나열하면 되고, 전체 다 주고 싶으면 all privileges 하면 된다

특정 컬럼에만 특정 명령어의 권한을 주고 싶다면 괄호 안에 컬럼 이름을 쉼표 기준으로 나열하면 된다

 

mysql> grant {명령어모음 | all privileges} on DB이름.테이블이름 to '유저명'@'호스트명' identified by '비밀번호';

맨 뒤에 identified by 를 통해 비밀번호를 명시하면 생성과 동시에 권한부여가 된다

 

mysql> flush privileges;

flush 명령을 통해 테이블을 새로고침하여 권한 변경 사항을 즉시 반영한다

너무 자주 하면 안 좋다고 한다

 

mysql> delete from user where user='유저명';

유저를 삭제할 땐 delete 명령을 사용한다

where 조건을 이용하여 여러 사용자를 한번에 삭제하는 것도 가능하다

그 외 명령어들

그 외의 모든 쿼리문을 명령줄에서 바로 사용가능하다 (굿)

Node.js에서 사용하기 (MySQL2)

npm 링크

mysql

mysql

mysql2

mysql2

yarn 링크

Contributors

https://yarnpkg.com/package/mysql

Contributors

https://yarnpkg.com/package/mysql2

설치

$> npm i mysql;
$> npm i mysql2;
$> yarn mysql;
$> yarn mysql2;

mysql / mysql2는 입맛대로 결정하자

필자는 Promise 형태의 함수가 구현되어 있는 mysql2를 택했다

mysql? mysql2?

• mysql
  • Promise를 지원하지 않기 때문에 비동기 작업을 위해 promise-mysql을 추가 설치해야한다
• mysql2
  • Promise를 자체적으로 지원한다 ⇒ 콜백 지옥에서 탈출
  • 성능이 조금 더 빠르고, 안정적이라고 한다

압도적 수준은 아니지만 mysql2의 다운로드 횟수가 조금 더 많다 ⇒ 2021년 하반기 기점으로 역전한 것을 볼 수 있다

두 개를 다 써보고 성능을 비교해보는 것을 권장하고 있다

0. 주의

$> mysql.server start
$> brew services start mysql

앞서 적었듯 Node.js의 mysql 패키지는 파일을 읽어들여주던 sqlite와 다르게, 컴퓨터에 존재하는 (또는 서버나 클라우드에 존재하는) MySQL 데이터베이스와 연결해주는 역할을 하기 때문에, CLI 환경설정을 전부 마친 후, MySQL 서버가 돌아가는 상태에서 진행해야 한다

서버가 꺼져 있으면 라이브러리도 연결을 실패한다!!

1. 연결 생성하기

import mysql from 'mysql2/promise'; // ES6 module

const connection = mysql.createConnection({
    host: 'localhost',
    user: '유저이름',
    password: '비밀번호',
    database: '연결할데이터베이스이름'
});

MySQL 데이터베이스 서버와의 연결을 생성한다

createConnection의 인자로 객체를 넘겨 설정값을 지정해줄 수 있다

사전에 cli 환경에서 미리 만들어둔 유저 계정을 넣어주자

 

mysql> show global variables like 'port'; // 현재 포트번호 확인
mysql> use mysql; select host, user from user; // 유저 목록 확인
mysql> show databases; // 데이터베이스 목록 확인

포트번호는 MySQL이 실제로 구동되고 있는 포트를 입력해야 하고, 기본값은 3306이다

유저명과 비밀번호 또한 사전에 MySQL CLI나 워크벤치에서 만들어뒀던 유저, 또는 루트 계정 등을 이용해야 한다 (권한설정 필수!!!)

데이터베이스명도 존재하는 데이터베이스로 지정해야 하며, 없으면 지정 안 해줘도 상관없다

하나라도 잘못 설정하면 연결에 실패한다… 미리 체크하고 진행하자 (삽질함)

 

앞으로 모든 데이터베이스 관련 작업은 이 ‘connection’ 객체를 가지고 진행한다

connection 객체를 통해 데이터베이스 서버로 요청을 보내서 쿼리를 처리한다고 보면 된다

1.5. 연결 풀 생성하여 연결 빌리기

const pool = mysql.createPool({
    host: "localhost", // 호스트명
    port: 3306, // 기본값: 3306
    user: '유저이름',
    password: '비밀번호',
    database: '연결할데이터베이스이름',
    connectionLimit: 10 // 최대 연결 개수
});

풀을 만드는 방법도 있다

쿼리 요청을 받을 때마다 createConnection을 통해 연결을 생성하는 방식은 단기간 요청 수가 많을 경우 연결 - 연결 해제를 반복하면서 과부하가 발생할 수 있다

 

대신 풀 방식은 서버와 연결된 USB 선을 여러 개 가져다놓고 ‘필요하신 분 가져다 쓰세요' 하는 느낌이다

서버와의 연결이 필요할 때, USB 선을 빌려서 슥 연결하고 데이터 통신이 완료되면 다시 반납하는 형식으로, 사용하지 않을 땐 반납하여 다음 사용자가 빌려갈 수 있도록 하면서 풀 안에 있는 연결은 유지하여 불필요한 리소스 낭비를 방지하는 것이다

또한 USB 선이 여러 개 존재하기 때문에, 한 번에 여러 명이 데이터베이스에 연결할 수 있는 장점도 있다

 

설정값은 createConnection과 비슷하게 지정하되, connectionLimit을 두어 최대 몇 개의 연결을 생성할 것인지 설정할 수 있다 (기본값은 10)

 

const connection = await pool.getConnection(async (connection) => connection)

풀로부터 연결을 빌릴 땐 getConnection 메서드를 활용한다

createConnection을 사용했을 땐 해당 반환값을 그대로 연결로 사용하면 됐는데, 풀은 getConnection 과정을 한번 더 거쳐야 한다는 살짝의 귀찮음이 있지만…

 

mysql2의 비동기 라이브러리를 사용했으므로 연결을 가져오는 것은 비동기로 동작하고, 따라서 async와 await를 적절히 사용하지 않으면 연결을 가져오지도 않았는데 연결을 사용하는 불상사가 발생할 수 있다

연결을 가져오면, 해당 연결을 이용하여 데이터베이스를 생성하거나 SQL 명령을 실행하는 등 작업을 수행할 수 있다

또한 then 체이닝을 통해 연결을 가져온 뒤에 수행할 동작을 콜백지옥 없이 정의할 수 있어서 편하다

 

await pool.getConnection(async (connection) => connection)
                    .then((connection) => initDatabase(connection))
                    .then(() => app.listen(PORT, callback));

예를 들면, 필자는 풀로부터 연결을 빌려 데이터베이스 초기화까지 끝낸 후 서버를 열어주는 과정을 then으로 연결하여 비동기로 진행하였다

 

connection.release();

연결을 풀에 반납할 땐 release 메서드를 사용한다

release를 통해 연결을 반납하지 않으면 해당 연결의 재사용을 못 하게 되면서 사실상 createConnection과 다를 바가 없기 때문에 꼬박꼬박 동작이 완료되면 연결을 반납하자

위의 예시에서 필자는 initDatabase(connection) 함수 내에서 데이터베이스와 테이블을 모두 생성하고 커넥션을 반납하였다 (app.listen은 커넥션을 사용하지 않기 때문)

2. 생성한 연결을 통해 쿼리 수행하기

await connection.query('쿼리문');
const [row] = await connection.query('select 쿼리문');
const [row, field] = await connection.query('select 쿼리문');

mysql2/promise를 사용하면 메서드가 비동기로 동작하기 때문에, async await를 적절히 걸어줘야 한다

(위의 예시에선 나오지 않았지만 async 함수로 감싸주어야 한다)

만약 SELECT 를 통해 특정 레코드를 가져온다면, 비동기 문제 때문에 레코드 배열을 사용하는 부분보다 레코드를 받아오는 부분이 느리게 동작하는 바람에 값이 undefined가 나올 수 있다

 

async function selectInfo(connection) {
    return await connection.query('SELECT * FROM testTable WHERE id < 4;');
}

필자는 쿼리마다 함수를 따로 분리해서 데이터베이스 조작이 필요할 때마다 함수를 불러와 사용하도록 했다

쿼리문도 CREATE TABLE같은 건 필드명과 속성을 하나하나 넣다 보면 쿼리문이 매우 길어지므로 상수로 분리해서 쓰는 것이 편하다

오류 해결하기

ERROR 2002 (HY000): Can't connect to local MySQL server through socket

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock'

$> brew services start mysql

brew를 통해 mysql 서비스를 켜주어야 mysql 커맨드가 동작한다

ERROR 1045 (28000): Access denied

$> mysql -u root -p
Enter password: // 비밀번호 설정 안 되어있으므로 엔터 한번 더 입력

처음 MySQL을 설치하면 비밀번호가 설정되어 있지 않기 때문에 뭘 입력해도 접근이 거부된다

비밀번호 없이 로그인한 후 비밀번호를 설정해주자

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

mysql> show variables like 'validate_password%'
mysql> set global validate_password.policy=LOW;

간혹 비밀번호 강도가 MEDIUM으로 되어있을 수 있다

validate_password로 시작하는 모든 변수를 출력하고, 그 중 validate_password.policy를 LOW로 바꿔준다

또한 계정 생성 시 비밀번호를 누락하진 않았는지 체크하자

---

참고자료

MySQL 계열의 FLUSH PRIVILEGES 명령어

MySQL 사용자(user) 조회, 생성, 제거, 권한 부여

MYSQL 설치 및 사용자 권한 설정, 외부접근 할당

Mysql 사용자 조회/추가/생성/삭제

What is the difference between MySQL & MySQL2 considering NodeJS

해피쿠 블로그 - [mysql] Connection Pool에 대해 알아보자

MySQL 연동 | PoiemaWeb

Node.js MySQL 연동 (Express)

Nodejs MySQL 연결하기

nodejs mysql 연동하기