[OSD] 5. Mysql 설치 및 연결 테스트

들어가며

Photo by Jan Antonin Kolar on Unsplash

웹서버가 제 기능을 하기 위해서는 DB도 필요하다. 서버에 mysql을 설치해서 nodejs서버에서 통신하는 것까지 해보려고 한다.

 

Mysql 설치

아래 명령어로 mysql server를 설치한다. - [1]

$ sudo yum install mysql-server

설치되었으면 systemctl을 이용해서 실행한다. 추가로 enable으로 서버가 재시작될 때 실행되도록 한다.

$ sudo systemctl start mysqld
$ sudo systemctl enable mysqld

mysql에 접속하기 전에 root 계정 설정을 해주어야 한다. 아래 명령어로 설정한다.

$ mysql_secure_installation

원하는 옵션으로 선택해서 비밀번호를 만든다. 설정한 비밀번호로 mysql에 접속한다.

$mysql -u root -p
Enter password:

비밀번호를 입력하면 mysql에 접속된다. 간단한 select문으로 테스트해본다.

mysql> select "Hello World!";
+--------------+
| Hello World! |
+--------------+
| Hello World! |
+--------------+
1 row in set (0.00 sec)

 

외부 접속 계정 생성

root 계정으로는 localhost에서만 접속이 가능하다. 새로운 계정을 생성해서 권한을 준다. - [2]

mysql> create user '<username>'@'%' identified by '<password>';
mysql> grant all privileges on *.* to '<username>'@'%';
mysql> flush privileges;

grant *.* 명령어를 이용해 모든 DB의 권한을 주었다. 특정 DB로 한정하기 위해서는 <DBname>.* 으로 줄 수도 있다.

외부에서 접근하기 위해 3306 포트를 열어준다.

$ sudo firewall-cmd --permanent --add-port=3306/tcp
$ sudo firewall-cmd --reload

reload 명령어로 방화벽을 적용하면 외부에서 접근이 가능하다. 

나는 Sequal Ace를 통해 접속했다. host에 도메인을 입력하고 username과 password를 맞게 입력하면 연결할 수 있다.

 

Mysql 테스트 프로젝트

mysql 연결 테스트를 위한 간단한 프로젝트를 만들 것이다. 우선 Mysql에 테스트용 DB를 생성한다.

mysql> CREATE DATABASE test;
mysql> USE test;
mysql> CREATE TABLE `message` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `message` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
mysql> INSERT INTO message (message) VALUES ("Hello World!");

message 테이블을 만들고 Hello World! 데이터를 추가한다. 실제로는 Sequel pro 상에서 진행한다.

 

이제 node 프로젝트를 생성한다.

$ mkdir node-mysql-test
$ cd node-mysql-test
$ node -v
v18.2.0
$ npm init

노드 버전을 확인하고 npm init으로 새로운 프로젝트를 초기화한다.

 

mysql을 연결하기 위해서는 연결 정보가 필요한데, git에 올릴 수 없으므로 따로 db_config.json 파일을 만들어 저장한다.

{
	"host": "xivnick.me",
	"user": "<username>",
	"password": "<password>",
	"database": "test"
}

host는 역시 도메인이고, 해당하는 user와 password를 입력한다. json포맷이므로 마지막 줄에 ,(쉼표)를 넣지 않도록 주의한다.

 

이제 db 처리를 담당할 db.js를 생성한다.

const mysql = require('mysql2/promise');
const db_config = require('./db_config.json');

const queryFormat = (query, values) => {
	if (!values) return query;
	return query.replace(/:(\w+)/g, (txt, key) => {
  		if (Object.prototype.hasOwnProperty.call(values, key)) {
   			return mysql.escape(values[key]);
  		}
  		return txt;
	});
}

const pool = mysql.createPool({
	host: db_config.host,
	user: db_config.user,
	password: db_config.password,
	database: db_config.database,
	queryFormat,
});

module.exports = {
	pool,
};

db.js에서는 mysql 모듈을 가져와 pool을 생성해 반환한다.

queryFormat은 sql injection을 방지하기 위한 escape 코드이다. 이번 프로젝트에서 사용하지는 않지만 추가했다. - [3]

db_config에서 설정을 가져와 createPool의 파라미터를 입력한다. db_config는 같은 폴더에 있도록 구성했다.

 

db 구성이 준비되었으니 서버의 메인 파일 index.js를 작성한다.

const express = require('express');
const app = express();
const port = process.env.PORT || 3000;

const DB = require('./db');

app.get('/', async (req, res) => {

	const [ messages ] = await DB.pool.query('SELECT * FROM message');

	return res.send(messages);
});

app.listen(port, () => {
  console.log(`Node Mysql Test app listening on port ${port}`);
});

루트로 접근하면 DB에서 select 쿼리를 수행해 리턴해주는 서버 코드다.

포트가 3000으로 이전 프로젝트와 같으면 실행할 수 없으므로 환경변수에서 PORT를 가져와주고, 없다면 그대로 3000으로 실행한다.

$ npm i express mysql2
$ node index.js
Node Mysql Test app listening on port 3000

npm으로 필요한 모듈을 설치하고 서버를 실행한다. 따로 환경변수를 설정하지 않았기 때문에 3000번 포트로 실행된다.

[{"id":1,"message":"Hello World!"}]

localhost:3000으로 접속하면 위 결과를 얻을 수 있다.

 

Git 설정하기

프로젝트를 서버로 옮기기 위해 git에 올린다. git에 올리지 않을 파일들을 .gitignore 파일에 추가한다.

node_modules/
db_config.json

기존에도 추가했던 node_modules/ 외에 password가 저장되어 있는 DB 설정 파일도 git에서 제외한다.

 

github에서 레포지토리를 생성한다. 안내 문구에 따라 다음 명령어를 실행한다.

$ git init
$ git add .
$ git commit -m "first commit"
$ git branch -M main
$ git remote add origin git@github.com:xivnick/node-mysql-test.git
$ git push -u origin main

ssh로 서버에 접속해서 클론한다.

$ git clone git@github.com:xivnick/node-mysql-test.git

git에서 다운로드한 파일에는 db_config가 빠져 있으므로 파일을 생성해준다.

$ cd node-mysql-test
$ vi db_config

config 파일은 위에서 작성한 것과 같이 작성한다.

{
	"host": "xivnick.me",
	"user": "<username>",
	"password": "<password>",
	"database": "test"
}

아래 명령어로 필요한 모듈도 설치해 준다.

$ npm install

실행하더라도 기본 port가 겹칠뿐더러, 방화벽도 닫혀있기 때문에 서비스를 만들어서 실행하도록 하자.

 

시스템 서비스 설정

시스템 서비스 파일을 만들어준다. - [4]

$ vi node-mysql-test.service

해당 파일은 이전과 비슷하게 적어 주되, PORT=3001이라는 환경 변수를 추가해준다. - [5]

[Unit]
Description=Node.js Mysql Test Project

[Service]
Environment="PORT=3001"
ExecStart=/home/xivnick/.nvm/versions/node/v18.2.0/bin/node index.js
Restart=always
User=xivnick
WorkingDirectory=/home/xivnick/node-mysql-test/

[Install]
WantedBy=multi-user.target

만든 시스템 파일을 링크해준다.

$ sudo systemctl link /home/xivnick/node-mysql-test.service
$ sudo systemctl start node-mysql-test

이제 3001번 포트에서 노드 서버가 실행된다.

 

Nginx 설정

마찬가지로 subdomain을 연결해 준다. - [6]

$ sudo vi /etc/nginx/sites-available/test2.conf

우선 sites-available에 test2 설정 파일을 만들어 아래 내용을 적는다.

server {
	listen 80;

	server_name test2.xivnick.me;

	return 301 https://$host$request_uri;	
} 

server {
    listen 443 ssl;

    server_name test2.xivnick.me;

	ssl_certificate /etc/letsencrypt/live/xivnick.me/fullchain.pem;
    ssl_certificate_key /etc/letsencrypt/live/xivnick.me/privkey.pem;

    location / {
        proxy_pass http://localhost:3001;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
    }
}

앞서 했던 설정 파일과 큰 차이가 나지는 않는다. 서브도메인과 연결해주는 port정도의 차이가 있다.

$ sudo ln -s /etc/nginx/sites-available/test2.conf /etc/nginx/sites-enabled/test2.conf
$ sudo systemctl restart nginx

enable 폴더에 링크를 걸어주고 Nginx를 재시작한다. 이제 test2.<domain>으로 접속을 확인할 수 있다!

 

Reference

[1] https://info-lab.tistory.com/172

[2] https://velog.io/@jdk9908/외부에서-MySQL-접속하기

[3] https://github.com/mysqljs/mysql/issues/1626

[4] https://xiv-dev.tistory.com/5

[5] https://serverfault.com/questions/413397/how-to-set-environment-variable-in-systemd-service

[6] https://xiv-dev.tistory.com/6