ubuntu에 mysql 설치 및 DBeaver로 연결하기

1. 사전 준비

 

 

AWS

https://forfull.tistory.com/entry/ec2-%EA%B0%80%EC%83%81%EB%A8%B8%EC%8B%A0-%EB%A7%8C%EB%93%A4%EA%B3%A0-%EC%A0%91%EC%86%8D%ED%95%B4%EB%B3%B4%EA%B8%B0?category=909239 

ec2 가상머신 만들고 접속해보기

 

 

GCP

https://forfull.tistory.com/entry/GCP%EB%A5%BC-%EC%9D%B4%EC%9A%A9%ED%95%98%EC%97%AC-Ubuntu-%EA%B0%80%EC%83%81%EB%A8%B8%EC%8B%A0-%EB%A7%8C%EB%93%A4%EA%B3%A0-%EC%8B%A4%ED%96%89%ED%95%B4%EB%B3%B4%EA%B8%B0?category=911254 

GCP를 이용하여 Ubuntu 가상머신 만들고 실행해보기

 

 

https://dbeaver.io/

DBeaver Community | Free Universal Database Tool

 

 

AWS

https://forfull.tistory.com/entry/route53%EC%9D%84-%EC%9D%B4%EC%9A%A9%ED%95%98%EC%97%AC-%EB%8F%84%EB%A9%94%EC%9D%B8-%EC%97%B0%EA%B2%B0%ED%95%98%EA%B8%B0?category=909239

route53을 이용하여 도메인 연결하기

 

 

되도록이면 위 두 작업을 모두 끝내는 것을 권장한다.

 

 

 

2. mysql 설치하기

su root

mysql을 설치해려면 먼저 root 계정으로 전환해야 한다.

 

 

 

 

 

apt-get install -y mysql-server

mysql은 mysql-server, mysql-client가 존재한다.

 

서버에 정의해놓을 DB를 mysql-server로 설치하고

설치된 DB에 접근하기 위해 mysql-client로 접근한다.

 

 

서버 내에서 직접 mysql로 접근하는 경우는 거의 없다.

때문에, mysql-server로 설치한다.

 

 

 

 

 

mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2455
Server version: 5.7.36-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

바로 mysql로 접속이 될 것이다.

 

 

 

 

 

 

 

 

3. root 사용자 설정하기

use mysql

우선 사용자 정보를 설정해야하므로 mysql 데이터베이스로 접속한다.

 

 

 

 

# mysql 5.x
select host, user, password from user;

# mysql 8.x 이상
select host, user, authentication_string from user;

위 명령어대로 입력하면 mysql 초기화 기준 사용자 정보가 나타날 것이다.

 

 

root 사용자는 기본적으로 비밀번호가 없다.

그래서, 보안을 위해 비밀번호 설정은 필수다.

 

 

 

 

 

 

 

# mysql 5.x
update user set password=password('1234') where user='root';

# mysql 8.x 이상
alter user 'root'@'localhost' identified with mysql_native_password by '1234';

# 변경사항 적용
FLUSH PRIVILEGES;

# 종료
exit

위 명령어를 입력하여 root 사용자의 비밀번호를 설정하고 변경사항을 적용하고 종료해보자.

 

비밀번호는 자유롭게 입력한다.

 

 

 

 

 

mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

다시 mysql로 접속하려고하면 접근이 거부되었다고 뜬다.

 

 

root 사용자에 비밀번호가 설정됐으니 접근하려면 사용자명과 비밀번호를 명시해야 한다.

 

 

 

명령어 옵션을 이용하여 사용자명과 비밀번호를 명시해보자.

 

 

 

 

mysql -u root -p
Enter password:
mysql>

 

위 명령어대로 입력하면 비밀번호를 묻는 창이 나온다.

 

 

보안상, 입력된 내용이 표기되지 않기 때문에,

입력이 안된다고 당황하지말고 모두 입력 후 엔터를 누르면 된다.

 

설정한 비밀번호를 입력하면 mysql에 접근가능하다.

 

 

 

 

 

 

 

DB 사용자 계정 만들기

이제 외부에서 접근을 허용할 사용자를 만들어야 한다.

 

 

(DB 내부 별도의 설정은 root로 하고, 외부에서 데이터처리 시, 별도 사용자를 만들어 관리하는 게 좋다.)

# 어디서든 외부 접속 허용하기
create user 'username'@'%' identified by 'password'

# 특정 호스트만 접속 허용하기
create user 'username'@'host' identified by 'password'

# 권한 설정하기
grant all privileges on *.* to username@host;

 

 

 

 

어디서든 외부로 접속할 수 있게 한다면 호스트에 %를 붙인다.

create user 'username'@'%' identified by 'password'

 

 

 

 

특정 호스트만 접속을 허용한다면 host를 명시해준다.

예로, ip 주소가 253.22.1.5 인 사람만 접속 허용하고싶으면 아래와 같이 입력하면 된다.

create user 'username'@'253.22.1.5' identified by 'password'

 

 

 

 

 

# mysql 5.x
select host, user, password from user;

# mysql 8.x 이상
select host, user, authentication_string from user;

사용자가 제대로 생성됐는가를 다시 한 번 확인한다.

 

 

외부 접속용 사용자를 하나 만들었으니, Mysql-server를 외부에서 접속할 수 있게 설정하자.

 

 

 

mysql>exit
cd /etc/mysql/mysql.conf.d
vi mysqld.cnf

 

 

 

 

 

mysqld.cnf 내 파일을 계속 읽다보면 다음 문구가 보일 것이다.

# If MySQL is running as a replication slave, this should be
# changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir
# tmpdir                = /tmp
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1

아마 bind-address가 주석처리가 돼 있을 텐데, 주석을 풀고 ip를 0.0.0.0으로 변경한다.

(GCP의 경우 bind-address가 자동으로 주석이 해제 돼 있다.)

 

이제 실질적으로 사용할 Database를 만들자.

 

 

 

 

 

# DB를 만들 때 기본 인코딩을 설정할 때
CREATE DATABASE {DB_NAME} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
create database {db_name} default character set utf8 collate utf8_general_ci;

# 이미 생성된 DB의 언어 인코딩을 변환할 때
ALTER DATABASE {DB_NAME} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
alter database {db_name} default character set utf8 collate utf8_general_ci;

(2023.09.08 추가)

query 작성 시, 소문자를 써도 상관없다는 것을 뒤늦게 깨달았다.

 

 

 

 

 

service mysql restart

저장 후 mysql을 재시작한다.

 

이제 외부 GUI 툴로 접속을 시도해볼 차례다.

DBeaver를 이용해서 연결을 해볼 것이다.

좌측 상단 데이터베이스 > 새 데이터베이스 연결 > Mysql 선택 (없으면 다운로드)

 

 

 

 

 

 

위 화면처럼 나오면 제대로 따라온 것이다.

 

Server Host에는 발급받은 도메인을 입력한다. (도메인을 발급받지 않았다면 가상머신 public IP를 입력한다.)

 

 

Database에는 CMD에서 생성했던 Database을 입력한다.

 

 

Username, Password에는 방금 추가했던 사용자, 비밀번호를 입력한다.

이후 좌측 하단 Test Connection을 눌러서 연결 테스트를 해보자.

 

 

 

 

 

 

 

 

 

 

4. 공개키 관련 오류 발생

 

Public Key Retrieval is not allowed

 

 

 

만약 이렇게 뜬다면 해결방법이 있다.

 

상단 Driver properties > Driver properties > allowPublicKeyRetrieval 에서

값을 TRUE로 바꾸고 다시 연결을 해본다.

 

 

 

 

 

Test Connection이 성공했으면 완료버튼을 누를 시, DBeaver 좌측에 저렇게 추가된다.

 

 

 

이제 DataBase에서 작업을 진행하면 된다.