[SQL] 터미널로 MySQL, PostgreSQL 설치하기 (MacOS)

이번 포스팅은 간단한 가이드라인 튜토리얼입니다.

Mac에서 MySQL을 설치하는 방법으로 두가지 방법이 있습니다.

• MySQL 홈페이지↗에서 파일을 다운받아 설치하는 방법
• Homebrew 패키지 관리자를 이용해 설치하는 방법

이번 포스팅에서는 Homebrew 패키지 관리자를 통해 MySQL을 설치해보려고 합니다.

MySQL 설치하기

먼저, Brew가 설치되었다는 가정하에 시작하려고 합니다. Brew를 설치하지 못한 분들은 해당 사이트↗ 가이드라인을 보면서 설치할 수 있습니다. Brew 설치가 완료되었으면 사용하기전에 `update`를 진행합니다. 그리고 아래 명령어를 통해 mysql 관련 프로그램을 검색합니다.

brew update
brew search mysql

 

그 중에서 mysql을 설치하려고 합니다. 다음 명령어를 입력해주시면 설치가 완료됩니다.

brew install mysql

 

MySQL 시작하기

이제 mysql을 사용하려고 합니다. 다음 순서를 따라야 합니다.

먼저 아래 `services start` 명령어를 통해 mysql 서비스를 시작해야합니다. mysql를 시작해야 명령어를 입력할 수 있습니다.

만약, mysql을 서비스를 시작하지 안하고 `mysql -u root`을 입력하게되면 다음과 같은 에러가 발생하게 됩니다.

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

 

→ 해당 에러 관련 Stackoverflow 글

brew services start mysql
mysql -u root

 

여기서 `-u`는 유저의 이름을 말하고 `root`가 여러분의 계정을 넣는 대체해 넣을 공간입니다.

`root` 계정으로 로그인하게 되면 접속을 할 수 있습니다.

 

기본적으로, Brew는 루트 비밀번호 없이 MySQL 데이터베이스를 설치할 수 있습니다. 만약 보안을 유지하고자 한다면, 다음 명령어를 실행시키면 됩니다. 계정 설정시 질문들은 더보기란에 확인하시면 자세히 볼 수 있습니다.

mysql_secure_installation

 

계정 비밀번호 설정

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No:

 

보안을 위해 복잡한 비밀번호를 설정할 것인지 여쭤보는 질문입니다. 

복잡한 비밀번호 생성을 원하면 y 그렇지 않으면 N 혹은 아무키를 입력해주시면 됩니다.

 

Yes를 눌렀을 경우

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG:

 

기본적으로 8자 이상으로, 특수문자와 숫자를 섞는 식으로 단계가 증가합니다. 저는 0으로 했습니다.

만약 해당 제약 조건에 벗어나면(비밀번호가 6자리등) 다시 입력하라고 합니다.

New password:

Re-enter new password:

Estimated strength of the password: 50
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y

 

패스워드를 입력하게되면, 패스워드 강도에 대한 질문을 물어봅니다.

그대로 사용하길 원하기 때문에 Y를 입력했습니다. 

Remove anonymous users? (Press y|Y for Yes, any other key for No) :

MySql을 사용하면서 로그인없이 익명으로 MySQL을 접속할 수 있다고 합니다.

이 익명 유저를 삭제할 것인지에 대한 질문입니다. 만약 지우려면 Y, 그냥 남겨두겠다면 아무키를 입력합니다.

저는 Y! 

만약 AWS 서버인 우분투 환경에서 작업하는 경우에는 N을 눌러줍니다! 

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y

 일반적으로는 루트 계정만 localhost에서 연결을 허락하는데 외부에서 들어오면 허락할 것인지 물어봅니다.

이는 원격 접속에 관한 내용입니다. 만약 원격으로 루트 계정을 제어하는 것을 허락하지 않으면 Y, 원격으로도 루트 계정을 제어해도 된다고 하면 아무키를 눌러주세요.

Remove test database and access to it? (Press y|Y for Yes, any other key for No)

 MySQL에서는 Test 데이터베이스가 제공한다고 합니다. 누구나 접근할 수 있고 테스트용으로 사용됩니다.

테스트 데이터베이스를 삭제하고 이에 액세스할 것인지에 관한 질문입니다. 물론, 그냥 둘 필요는 없지만 나중에 필요하게 될 수 있을것 같네요. Y

Reload privilege tables now? (Press y|Y for Yes, any other key for No) :

권한 테이블을 로드하면 모든 변경사항이 보장됩니다. 권한 테이블을 다시 로딩할 것인지 뜹니다.

로딩을 원치 않으면 아무키를 눌러줍니다. 현재까지의 내용을 적용하려면 Y를 눌러줍니다. YYY

MySQL은 3306 포트를 사용합니다.

초기설정이 완료되었으면 MySQL 서비스가 실행되고 있는지 확인해봅시다.

sudo lsof -i:3306

 

MYSQL의 포트 정보 확인은 `SHOW GLOBAL VARIABLES LIKE 'PORT'` 쿼리로 확인할 수 있습니다.

보안 설정은 이후에도 충분히 바꿀 수 있습니다. 

 

다시 MySQL 서비스에 접속해보겠습니다.

`-u root` 루트 계정을 `-p` 패스워드를 사용해 접속합니다.

mysql -u root -p

 

접속이 완료되면 `exit`이나 `quit`을 통해 로그아웃을 할 수 있습니다.

서버 연결을 완전히 종료하고자 할땐, `brew services stop mysql`을 입력하시면 됩니다.

 

커맨드라인으로 접속하는 방법 뿐만 아니라 MySQL WorkBench↗를 설치하여 접속하는 방법도 존재합니다.

 

---

MySQL 사용하기

데이터 베이스 생성하기

• `SHOW DATABASES` : 데이터베이스의 목록을 조회하는 명령어
• `CREATE DATABASE [데이터베이스 이름] CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;` : 데이터베이스 생성 
• `DROP DATABASE [데이터베이스 이름]` : 데이터베이스 삭제
• `USE [데이터베이스 이름]` : 현재 세션에서 데이터베이스에 접속하는 명령어

CREATE DATABASE 부서 default CHARACTER SET UTF8; # 부서 테이블 생성 및 한글 사용
SHOW DATABASES; # 데이터베이스 목록 불러오기

 

MySQL 코드 컨벤션(Code Conventions)

• 테이블과 테이블 내 컬럼명은 중복이 될 수 없다.
• 예약어나 약어, 서술식은 사용하지 않는다.
• 테이블의 이름은 단수형을 사용하며 snake_case를 사용한다.
• 테이블의 key는 {테이블 단수형 이름}_id 형태를 사용한다.
• 구체적으로 작성해 모델의 유일성을 확보한다.

테이블 생성

• `USE [데이터베이스 이름]` : 현재 세션에서 데이터베이스에 접속하는 명령어
• `SHOW [테이블]` : 해당 데이터베이스에 테이블을 확인하는 명령어
• `CREATE TABLE [테이블] (컬럼명 데이터유형 제약조건);` : 테이블을 생성하는 명령어
• `DROP TABLE IF EXISTS [테이블]` : 해당 테이블이 존재하는 경우 삭제하는 명령어로 존재하지 않더라도 오류가 발생하지 않는다. 

USE 부서 # DB 선택

CREATE TABLE 부서
(
    pk CHAR(4) PRIMARY KEY,
    부서명 VARCHAR(10) NOT NULL,
    상위부서코드 CHAR(4),
    담당자 CHAR(3) 
) ENGINE=INNODB;   # 데이터 저장 엔진
DESCRIBE 부서;

 

 

외래키(FK) 정의
- `FOREIGN KEY (컬럼명) REFERENCE 참조 테이블(컬럼명)`

데이터 삽입

INSERT INTO 부서
(pk, 부서명, 담당자)
VALUES ('A001', '대표이사', '김대표');

INSERT INTO 부서
(pk, 부서명, 상위부서코드, 담당자)
VALUES ('A002', '영업 본부', 'A001', '홍길동');

INSERT INTO 부서
(pk, 부서명, 상위부서코드, 담당자)
VALUES ('A003', '경영지원 본부', 'A002', '이순신');

SELECT pk, 부서명, 상위부서코드, 담당자 FROM 부서; # 데이터 조회

MySQL 기타 설정 및 확인

사용자 정보 확인

mysql> SELECT User, Host, authentication_string FROM mysql.user;

MySQL 버전 확인

mysql> show variables like "%version%";

MySQL 비밀 번호 변경

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('바꿀비번');

🚀 PostgreSQL 설치 방법

PostgreSQL 홈페이지↗에서 운영체제에 맞는 PostgreSQL를 설치해야 합니다.

brew install postgresql@14
# @14 버전으로 설치했습니다.

postgres -V
# 버전 확인

brew services start postgresql
# DB 서버 시작하기

psql postgres
# DB 서버 접속하기

create database testdb;
# 데이터 베이스 생성

alter user local createdb;
# 사용자 데이터베이스 권한 부여

grant all privileges on database testdb to local;
# 사용자의 데이터베이스 권한 모두 부여

## testdb : 데이터베이스 예시
## local : 사용자 예시

 

🚀 우분투(Ubuntu) 환경에서 mysql 설치(Ubuntu 22.05)

먼저 `apt`를 최신화 시키고 `mysql-server`를 설치합니다.

sudo apt-get update
sudo apt-get install mysql-server -y
sudo mysql_secure_installation

 

이후 이전에 했었던 방식과 같습니다.

`netstat -ntl`을 통해 3306 포트가 열려있는지 확인하고 활성화 되어있지 않으면 방화벽에서 서비스를 구동시킵니다.

netstat -ntl | grep:3306
sudo ufw allow mysql

 

이번엔 MySQL을 실행시켜봅시다.

sudo systemctl start mysql

sudo /usr/bin/mysql -u root -p // 접속

 

이번엔 mysql을 접속한 후, 내/외부 ip로 접속할 사용자를 생성합니다. 물론, 권한도 함께 추가할 예정입니다.

mysql > CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'password'; // 유저 생성
mysql > GRANT PRIVILEGE ON database.table TO 'username'@'host'; // 권한 부여
mysql > FLUSH PRIVILEFES

 

특정 테이블 대신 별표를 입력하면 전역적으로 권한을 부여할 수 있습니다.

이후, FLUSH 명령어를 통해 이전 명령어 결과 캐시를 메모리에서 해제 시킵니다.

 

🚀 DB 서버와의 어댑터(DB 커넥터) 설치

DB 커넥터는 DB 서버와 웹 애플리케이션 사이를 연결해주는 어댑터 역할을 합니다.

다음과 같이 SQL에 맞게 설치해주시면 됩니다.

• mysql : `mysqlclient`
• postgresql : `psycopg2`

---

☕️ 포스팅이 도움이 되었던 자료

• MySQL 완전 삭제하고 재설치하기 (MacOS)
• [AWS] 5-3. AWS- EC2 Ubuntu에 mysql 8.0 설치하기 (Ubuntu 22.05, mysql 8.0.34)

오늘도 저의 포스트를 읽어주셔서 감사합니다.

설명이 부족하거나 이해하기 어렵거나 잘못된 부분이 있으면 부담없이 댓글로 남겨주시면 감사하겠습니다.