saii 잡다한 블로그

DB 때문에 이렇게 고생을 해야한다는게 화가난다...

Error fetching schema list: Error executing 'SHOW DATABASES' Lost connection to MySQL server during query. SQL Error: 2013

이 에러는 workbench에서 실행한 쿼리에 걸리는 시간이 길어져서 정해진 제한시간 동안 쿼리가 종료되지 못해서 발생한 에러다.

나의 경우는 import하는데 특정 테이블이 커서 오래걸려서 동안 발생했다.

해결방법은 쿼리 제한 시간을 늘려주면 된다.

workbench 탭에서 edit > Preferences >SQL Editor

DBMS connection keep-alive interval : 연결 유지 시간 간격
DBMS connection read timeout interval : 읽기 시간 초과 간격
DBMS connection timeout interval 연결 시간 초과 간격 

이 3개를 늘려주면 해결됩니다.

Dump 파일을 다시 import 했는데 대충 그냥 게이지만 보고 됐겠네 하고 스키마 탭 보니깐 DB가 안생겨져있었다.

다시 import 하고 log를 보니 에러가 있었다.

ERROR 1049 (42000): Unknown database 'DB명'

참 어처구니가 없었다.

MySQL에서 DB 백업 후 복원 할 때 자주 생긴다고 한다.

당연히 내가 DBA가 아닌 이상 왜 그런지는 잘 몰랐다. 당연히 나는 import하는데 DB도 생성해주는 줄 알았기 때문이다.

에러 메세지 그대로 DB를 알 수 없다는 의미다.

create database DB;

이거 한 줄 이면 해결된다.

DB import 시 해당 DB와 동일한 DB가 생성 되어 있어야 한다고 한다.

이제야 Default Target Schema 의미를 알게된듯...?

루트 계정 접속

 mysql -u root -p

user 테이블 확인

mysql> use mysql;
mysql> select user, host from user

user 추가

mysql> create user '사용자 이름 입력하세요'@'localhost(또는 %)' identified by '비밀번호 입력';

'localhost' or '%' or 'IP'  : localhost는 해당 pc만 접속 , %는 외부의 모든 접속 가능, IP는 해당 IP만 접속 가능하다.

user 삭제

mysql> drop user '사용자명 입력'@'localhost';

해당 user의 접속 권한 또한 똑같이 입력해야 한다.

user DB 권한 부여 (Grant)

mysql> grant all privileges on *.* to '사용자'@'localhost';
mysql> grant all privileges on DB이름.* to '사용자'@'localhost';
mysql> grant all privileges on DB이름.테이블명 to '사용자'@'localhost';
mysql> grant select on DB이름.테이블명 to '사용자'@'localhost';
mysql> grant update(컬럼1, 컬럼2) on DB이름.테이블명 to '사용자'@'localhost';

1번 라인은 모든 DB, DCL을 제외한 권한을, 2번 라인은 특정 DB 권한 및 DCL을 제외한 권한을, 3번 라인은 특정 DB의 테이블에 대한 권한 4번 라인은 DB의 테이블에 대한 select 권한 5번 라인은 DB의 테이블에 대한 특정 컬럼 수정 권한이다.

이렇게 특정 DB의 권한을 줄 수도 있고 select, delect 등의 특정 권한만 줄 수도 있다.

all privieges는 모든 권한을 , *.*는 모든 DB의 모든 테이블을 뜻한다.

정리해보면 grant (권한) on (범위)  to (user) 이렇게 정리할 수 있겠다.

1번 라인에 with grant option 을 맨 끝에 추가하면 권한 부여(ROOT 권한)을 부여한다.

user 권한 삭제 (Revoke)

이 부분은 Grant와 구조가 같다. 응용해서 특정 조건을 삭제할 수 잇다.

mysql> revoke all on DB명* from 'user'@'localhost';

user 권한 확인

mysql> show grants for 'user'@'localhost';

AWS에서 RDS를 사용하던 서버에 MySQL-Server로 옮기려고 설정하던 중이었다.

우선 MYSQL 설치를 했다. 이건 뭐 리눅스 계열 좀 해본 사람은 대충 알거라 생각한다. 설치는 다른 글 있다.

2021/02/01 - [IT/우분투서버] - [ubuntu server 20.04 LTS] MySQL 설치

근데 보안을 생각해서 우선 루트계정을 원격접속이 안되게 설정해놨다.

여기부터 계속 PC에서 서버 DB로 접속이 안된다.

문제점을 하나씩 살펴봤다. 정리해본 결과 몇가지가 된다. 나의 경우를 정리했다.

우선 편리를 위해 루트계정으로 했다. 설정 자체는 루트계정이 아닐경우 수정이 안되는 경우가 많고 sudo를 계속 치면서 pw를 치는게 번거롭다.

1. 방화벽 포트포워딩.

- 서버와 내가 사용하는 PC는 다른 IP이기 때문에 너무 당연한 거였다.

이 부분은 결정적인 원인은 아니다.

2. 우분투 IP 및 포트 개방.

- OS에서 포트가 개방 되어있는지 그리고 IP가 로컬로 되어있는지 확인을 했다.

netstat 명령어를 사용하면 된다. 

# netstat -tnlp

기본적으로 MySQL은 3306 포트를 사용한다. 

우선 Local Address를 살펴보면 3306포트가 127.0.0.1로 되어있는것을 확인할 수 있다. 그렇단 뜻은 로컬에서만 mysql을 접속 할 수 있다. 이건 mysql 설정을 바꿔주면 된다. 우선 맨끝에 프로그램 이름을 확인하자. mysqld 라고 되어있다. 이걸 기억한 후 mysql 폴더로 가야한다.

cd /etc/mysql
ls -a

mysql 폴더는 etc 밑에 있다. cd 명령어를 통해서 이동 후 ls 명령어를 통해서 폴더 안에 어떤게 있는지 확인한다.

여기서부터 문제가 많았다.

이 설정은 os 버전과 mysql 버전에 따라 설정 파일이 다를 수 있다. 나도 이 부분에서 굉장히 헤맸다. 어떤 사람은 my.conf , mysql.conf, mysqld.conf ... 어떤 사람은 이 폴더에서 찾는 사람도 있다. 많은 시도 끝에 해결했다. 우선 아까 프로그램이름이 mysqld 였는데 이 폴더 안에는 같은 이름의 cnf가 없었다.

그래서 mysql.conf.d 폴더로 이동했다. 그 안에 mysqld.cnf 파일이 있었다.

cd mysql.conf.d
ls -a
vi mysqld.cnf

vi로 mysqld.cnf 파일을 편집하자.

bind-address가 있다. 그걸 0.0.0.0으로 바꿔주자. 그리고 :wq를 입력하고 나오면 된다. 

수정했다면 mysql를 재실행한다.

service mysql restart

재실행했으면 아까 netstat 명령어로 IP가 제대로 설정됐는지 확인해보자

이렇게까지 했으면 이제 Mysql에서 user를 살펴봐야한다.

3. User 설정

mysql에 root 계정으로 접속한다.

mysql -u root -p

접속 한 후 데이터베이스를 선택한 후 user 테이블을 확인해보자.

mysql> use mysql;

mysql> select user,host from user;

user 테이블을 보면 host 컬럼에 localhost만 되어있다. 이 뜻은 외부 접속이 안된다.

그러면 user를 하나 생성해주자.

mysql> create user 'user'@'%' identified by '!QAZ2wsx';

'user'@'%'는 user는 user name이고 , % 는 외부의 모든 접근 허용이다.

여기까지 한다면 workbench에서 접속이 가능할 것이다.

설치 명령어다. 

# apt-get install mysql-server

기본적으로 리눅스 계열의 세팅이 필요한듯하다.

# ufw allow mysql
# systemctl start mysql
# systemctl enable mysql

1 외부접속 기능 설정(Port 3306 개방)

2 mysql 실행

3 서버 리부팅 시 mysql 자동 재시작

Mysql은 설치 후 추가 설치 과정이 필요하다.

# mysql_secure_installation

암호 검증을 말하는 것 같다. 

암호 난이도? 인듯 하다. 

루트 계정 암호 입력이다.  암호를 입력하면 입력한 암호의 보안 강도를 나타내며 사용할 것인지 또 묻는다.

MySQL은 디폴트로 익명의 유저를 있고, 어차피 이건 필요하지 않으니 지울거냐고 물어보네요

일반적으로 루트유저는 localhost에서만 접속이 되네요. 원격 접속을 할 때 막을 건지 물어보는듯 하네요?

디폴트로 testDB를 생성하네요 지울거냐고 묻네요

끝났습니다. 아래 명령어로 MySQL에 접속 해보고 마무리 해야죠.

# mysql -u root -p

개발을 하다보니 쿼리문에서 인젝션에 대한 문제들이 나오기 시작했습니다. 

그래서 쿼리문에 ? 물음표를 넣어두고 query() 함수의 두번째 파라미터 자리에 변수를 넣어줍니다.

이런식으로 사용할 수 있겠죠?

그렇다면 2개 이상은 어떻게 해야될까요?

[] 괄호를 사용하여 2번째 파라미터를 묶어주면 됩니다.

이런식으로 묶어주면 됩니다.

다른 쿼리들도 이렇게 많이 사용하는데 insert문이 조금 특이합니다.

insert into values가 보통입니다만

var sql = 'INSERT INTO table (name, email, age) VALUES(?, ?, ?)';
var params = ['hyun','94onad@naver.com','26']
connection.query(sql, params, function(err, rows, fields){

보통은 이렇게 많이들 쓰실텐데 INSERT문은 SET을 사용하기도 합니다. SET을 사용하게 되면 컬럼 전체를 넣어야합니다. 

  var users = {
    userid: req.body.userid,
    pwd: req.body.pwd,
    name: req.body.name,
    email: req.body.email,
    phone: req.body.phone,
    created: today,
    modified: today,
  };
  connection.query("INSERT INTO user SET ?", users, function (error, results, fields)

직접 쿼리문 안에 넣어주는 분들도 계시는데 이럴경우 escape() 함수를 사용하는 방법도 있습니다.

var sql = 'SELECT * FROM CLUB WHERE CLUB_SN = ' + mysql.escape(clubSn) + '; ';