MySQL 기본 관리 및 설정

MySQL은 클라이언트-서버 구조의 대표적인 서비스이며, mysql, mysqladmin, mysqldump와 같은 자체 클라이언트 도구를 제공합니다.

MySQL 비밀번호 설정

초기 상태에서 root 사용자의 비밀번호는 비어 있거나 무작위로 생성되며, 로컬 호스트에서만 접속이 허용됩니다. MySQL 설치 후 가장 먼저 수행해야 할 작업은 기본 비밀번호를 변경하는 것입니다.

# 초기 비밀번호 설정 (기존 비밀번호가 없으므로 -p 옵션 불필요)
[root@localhost ~]# mysqladmin -uroot password "new_password"

# MySQL 비밀번호 변경 (이미 비밀번호가 있으므로 기존 비밀번호를 입력해야 새 비밀번호로 설정 가능)
[root@localhost ~]# mysqladmin -uroot -p"old_password" password "new_password"

MySQL 연결 도구 및 방식

MySQL은 다양한 연결 방식과 도구를 지원합니다. 여기서는 주요 내용을 상세히 설명합니다.

1. MySQL 자체 명령줄 도구

MySQL은 mysql이라는 자체 연결 도구를 제공합니다. 주요 옵션은 다음과 같습니다.

mysql
# 일반적인 클라이언트별 연결 옵션:
-u: 사용자 지정  (예: mysql -uroot)
-p: 비밀번호 지정 (예: mysql -uroot -p567)
-h: 호스트 도메인 지정 (예: mysql -uroot -p567 -h127.0.0.1)
-P: 포트 지정 (예: mysql -uroot -p567 -h127.0.0.1 -P3307)
-S: 소켓 파일 지정 (예: mysql -uroot -p567 -S /tmp/mysql.sock)
-e: SQL 문 지정 (명령줄에서 SQL 실행, 예: mysql -uroot -p567 -e "show databases;")
--protocol: 연결 방식 지정 (예: mysql --protocol=TCP  --protocol=SOCKET)

MySQL 서버 접속

# 올바른 접속 명령
[root@localhost ~]# mysql -uroot -p123
[root@localhost ~]# mysql -u root -p123
[root@localhost ~]# mysql  # 비밀번호가 없는 root 사용자로 로컬 호스트 접속

# 잘못된 접속 명령
[root@localhost ~]# mysql -u root -p 123  # -p 옵션과 비밀번호 사이에 공백이 없어야 함

연결 방식

  • TCP/IP 연결 방식
  • 소켓(Socket) 연결 방식
  • 기본적으로 소켓 방식을 사용합니다.

연결 방식 확인:

mysql> status;
--------------
Connection:     Localhost via UNIX socket

연결 방식 예시:

  • TCP/IP 연결: 보통 -h 옵션이 포함된 경우 TCP/IP 연결입니다.
    mysql -uroot -p -h127.0.0.1 -P <포트 번호>
            mysql -uroot -p -h127.0.0.1 -S /tmp/mysql.sock
            
  • 소켓 연결:
    mysql -uroot -p123 (기본 연결 방식, 소켓)
            

주의사항:

  • TCP/IP 연결 시 3-way handshake 과정이 필요합니다.
  • -h localhost는 소켓 연결일 수 있습니다.
    mysql -uroot -p -h localhost
            

2. 서드파티 도구 연결

실무에서는 MySQL 자체 도구보다 Navicat과 같은 서드파티 데이터베이스 관리 도구를 주로 사용합니다. 이러한 도구들은 강력한 기능으로 데이터베이스 작업을 효율적으로 할 수 있게 도와줍니다.

원격 접속을 위해서는 원격 접속 사용자를 생성해야 할 수 있습니다.

-- 모든 데이터베이스의 모든 테이블에 대해 'root' 사용자(localhost에서 접속)에게 'Oldboy@666' 비밀번호로 모든 권한을 부여합니다. WITH GRANT OPTION은 권한을 재부여할 수 있는 권한을 줍니다.
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'Oldboy@666' WITH GRANT OPTION;

FLUSH PRIVILEGES;

Navicat 사용법은 별도로 설명합니다.

MySQL 설정 파일

MySQL 설정 파일은 MySQL 서버의 동작 방식을 구성하는 데 사용됩니다. 설정 파일 내의 주석은 한글로 작성 가능하지만, 설정 항목 자체에는 한글을 사용할 수 없습니다.

# 1. mysqld 명령 실행 시 적용되는 설정 (MySQL 서비스 시작 시 적용)
[mysqld]
# skip-grant-tables  -- 보안상 권장되지 않음
port=3306
character_set_server=utf8
default_storage_engine=innodb
innodb_file_per_table=1

# MySQL 데이터 디렉토리 경로
basedir=E:/mysql-5.7.19-winx64  # 설치 경로
datadir=E:/my_data           # 데이터 저장 경로 (mysqld --initialize 시 지정)

# 2. 클라이언트 명령에 대한 전역 설정 (mysql 클라이언트 명령 실행 시 적용)
[client]
port=3306
default-character-set=utf8
user=root
password=123

# 3. mysql 클라이언트 전용 설정 (위 [client] 설정은 전역, 이것은 해당 클라이언트에만 적용)
[mysql]
# port=3306
# default-character-set=utf8
user=root
password=4573

# !!! 만약 [mysql] 섹션이 없다면, mysql 명령 실행 시 [client] 섹션의 설정이 우선 적용됩니다.

문자셋 인코딩 통일

데이터 정합성을 위해 데이터베이스의 문자셋 인코딩을 통일하는 것은 매우 중요합니다. 통일되지 않으면 데이터 조회 시 오류가 발생할 수 있습니다.

1. 시스템 문자셋 변경

# 1. 명령줄에서 임시 변경:
LANG=en_US.UTF-8
LANG=ko_KR.UTF-8

# 2. 시스템 문자셋 영구 변경:
# CentOS 6:
[root@localhost ~]# vim /etc/sysconfig/i18n
# CentOS 7:
[root@localhost ~]# vim /etc/locale.conf

2. 컴파일 시 지정

cmake . \
-DDEFAULT_CHARSET=UTF8 \
-DDEFAULT_COLLATION=UTF8_GENERAL_CI

3. 설정 파일 지정

# 1. 설정 파일 수정
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

# 2. 서비스 재시작

# 3. 변경 결과 확인
mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.07 sec)

정렬 규칙 (Collation): utf8_general_ci (ci는 대소문자 구분 안 함), utf8_bin (대소문자 구분). 테이블 내에서는 동일한 이름의 필드를 대소문자만 다르게 가질 수 없습니다.

utf8 vs utf8mb4: utf8mb4는 이모지(emoji)와 같은 확장 문자를 지원합니다. utf8은 이를 지원하지 않습니다.

4. SQL 문으로 문자셋 지정

# 1. 데이터베이스 생성 시 문자셋 및 정렬 규칙 지정
create database db1 charset utf8mb4 collate utf8mb4_general_ci;

MySQL root 비밀번호 변경

MySQL 초기 설정 후 또는 정기적인 유지보수를 위해 root 비밀번호를 변경하는 것은 보안상 매우 중요합니다. 다음과 같은 방법으로 변경할 수 있습니다.

# 첫 번째 방법: MySQL 클라이언트에 접속하여 변경
[root@localhost ~]# mysql -uroot -p"old_password"
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
mysql> FLUSH PRIVILEGES;

# 두 번째 방법: GRANT 문을 사용하여 변경 (원격 접속 허용 시)
MySQL> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'new_password';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

# 주의: 위 두 가지 방법은 적용 대상(localhost vs %)에 차이가 있습니다.

mysqladmin 명령어

mysqladmin은 MySQL 서버를 관리하는 데 유용한 명령줄 유틸리티입니다.

# 1. 비밀번호 변경
[root@db01 ~]# mysqladmin -uroot -p'old_password' password 'new_password'

# 2. MySQL 서비스 종료
[root@db01 ~]# mysqladmin -uroot -p'password' -S /path/to/mysql.sock shutdown

# 3. 명령줄에서 데이터베이스 생성
[root@db01 ~]# mysqladmin -uroot -p'password' create new_db
[root@db01 ~]# mysql -uroot -p'password' -e 'create database another_db'

# 4. 명령줄에서 데이터베이스 삭제
[root@db01 ~]# mysqladmin -uroot -p'password' drop database_to_drop
Do you really want to drop the 'database_to_drop' database [y/N] y
Database "database_to_drop" dropped

# 5. 모든 기본 파라미터 보기
[root@db01 ~]# mysqladmin -uroot -p'password' variables
[root@db01 ~]# mysqladmin -uroot -p'password' variables | grep server_id

# 6. MySQL 프로세스 상태 확인 (ping)
[root@db01 ~]# mysqladmin -uroot -p'password' ping

# 7. 서버 상태 정보 조회
[root@db01 ~]# mysqladmin -uroot -p'password' status
# Uptime: 서버 실행 시간 (초)
# Threads: 활성 클라이언트 스레드 수
# Questions: 총 쿼리 수
# Slow queries: long_query_time 초과 쿼리 수
# Opens: 열린 테이블 수
# Flush tables: flush/refresh/reload 명령어 실행 횟수
# Open tables: 현재 열려있는 테이블 수
# Queries per second avg: 초당 평균 쿼리 수 (부하 지표)

# 8. 권한 테이블 다시 로드 (flush privileges와 동일)
[root@db01 ~]# mysqladmin -uroot -p'password' reload

# 9. 바이너리 로그(binlog) 롤링
[root@db01 ~]# mysqladmin -uroot -p'password' flush-log

태그: MySQL 데이터베이스 관리 비밀번호 설정 연결 도구 설정 파일

10월 15일 07:36에 게시됨