MySQL

Linux MySQL

 

 

목적

윈도우처럼 GUI 환경이 아닌 CLI 환경에서 MySQL을 제어할 수 있음

Linux DB 서버 생성

 

 

 

 

설치-실행

 

계정 생성

root 계정으로는 작업을 하지 않는 것이 좋음

따로 MySQL 작업용 계정을 만드는 것이 좋음

 

1. mkdir /home/mysql => home directory에 mysql 추가
2. groupadd mysql -g 999 =>  mysql이라는 그룹명을 가지고 GID 999으로 설정해서 그룹 생성
3. useradd -u 999 -g 999 -d /home/mysql -s /bin/bash mysql  =>  UID는 999으로 설정해서 유저 생성, 해당 유저는 그룹 999에 속함, 해당 유저의 홈 디렉토리를 /home/mysql로 설정, 해당 유저가 사용할 쉘 지정 (/bin/bash), mysql이라는 유저명을 가짐
4. chown mysql:mysql /home/mysql => 해당 디렉토리의 owner와 group을 변경함

 

---

사용할 디렉토리 설정 => data directory를 지정

 

MySQL이 생성하는 파일들이 저장될 위치를 지정해주어야 함 (data directory)

 

1. mkdir -p /data1/mysql => mysql이라는 디렉토리 생성 => -p로 하여 없는 디렉토리인(data1의 상위 디렉토리도 만듦)
2. chown mysql:mysql /data1/mysql => 해당 디렉토리의 owner, group 변경

 

 

---

MySQL 엔진 다운로드 => base directory를 지정

 

1. setproxy wget {"mysql 링크"} => wget으로 다운로드
2. tar -xvf mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz  => 해당 파일의 묶어있던 것을 푼다.
3. mv mysql-8.0.18-linux-glibc2.12-x86_64 /usr/local => 해당 파일을 옮긴다. => 해당 디렉토리가 base directory가 될 것임
4. cd /usr/local
5. chown -R mysql:mysql mysql-8.0.18-linux-glibc2.12-x86_64 => 해당 파일 전부에 대한 owner와 group을 하위 디렉토리까지 변경시킨다.
6. ln -s mysql-8.0.18-linux-glibc2.12-x86_64 mysql => symbolic 링크를 생성한다. (mysql... → mysql ) : 긴 파일 명을 줄임
7. chown mysql:mysql mysql => mysql에 대한 owner, group을 mysql로 변경한다.

 

---

 

데이터베이스 생성

 

**my.cnf : Unix/Linux 계열 OS 의 mysqld 및 Client tool 들이 로딩 시 읽는 option file

 

1. cd/etc
2. vi my.cnf => 컨피그 파일 생성 (/etc/my.cnf)
3. my.cnf(아래 참조)
4. cd /usr/local/mysql
5. mkdir mysql-files
6. chown mysql:mysql mysql-files
7. chmod 750 mysql-files => 해당파일의 권한을 owner: group : other= read/write/execute : read/execute : none 으로 변경함
8. bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql => default file을 아까 만들었던 config로 지정, 이전에 만들었던 data directory 지정
9. bin/mysql_ssl_rsa_setup => ssl 인증서 및 RSA-key pair를 생성함

 

my.cnf

[mysqld]
port=3306
datadir=/data1/mysql

 

• Data Directory : 운영 중 생성하는 파일들의 Default 위치
  • my.cnf 에 “datadir” 이라는 변수 값으로 설정할 수 있음, 없다면 MySQL이 설정한 basedir에 위치

 

 

---

프로세스 실행

 

1. ./bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql & => default file이 해당 경로에 있고 user는 mysql로  mysqld_safe 프로세스를 백그라운드로 실행함

 

 

** mysql은 basedir/bin 디렉토리 내에 위치

 

아래 명령어로 프로세스 생성 여부 체크

ps -ef | grep mysqld

 

 

 

 

---

MySQL 접속

 

1. cd bin
2. ./mysql -u root -p => mysql 에 root 로 접속

 

 

---

MySQL 종료

 

1. ./mysqladmin -u root -p shutdown

 

 

 

---

MySQL 각 디렉토리

 

• bin : MySQL 을 실행하는데 필요한 바이너리 파일 및 각종 Client Tool 들이 저장되는 디렉토리
• include : MySQL이 실행되면서 필요한 헤더 파일이 저장된 디렉토리
• supprot-files : MySQL을 운영할 때 사용할 수 있는 각종 스크립트 파일이 존재
• lib : Plugin 모듈과 각종 library 들이 저장되어 있음

 

---

MySQL 옵션 파일

설치할 때나 Startup 시 참조해 그 설정에 맞게 운영하도록 만들어진 파일

 

• my.cnf : Unix/Linux 계열 OS 의 mysqld 및 Client tool 들이 로딩 시 읽는 option file
• my.ini : window
• .my.cnf : 사용자 맞춤형 option file
• .mylogin.cnf : 접속 시 사용하는 파일이다. 비밀번호를 암호화하여 사용하는 파일로 다른 파일들과 사용법이 조금 다르다.

 

어떤 파일을 참조할 지는 미리 지정해두어야 함

로딩 시 어느 Option File을 사용할 지 지정하지 않으면 아래와 같은 순서로 지정됨

• /etc/my.cnf
• /etc/mysql/my.cnf
• SYSCONFDIR/my.cnf
  • CMake 시 지정된 SYSCONFDIR 의 설정 값, 일반적으로는 /etc 이다.
• $MYSQL_HOME/my.cnf
• defaults-extra-file 로 설정한 옵션파일
• ~/.my.cnf
• ~/.mylogin.cnf

 

옵션 파일 지정은 일반적으로 —defaults-file 라는 Command Line 변수를 설정해 실행함

 

---

MySQL 시스템 변수

 

변수설명

datadir	data directory 설정
log_bin	바이너리 로그를 생성할 지의 여부를 설정함
binlog-format	바이너리 로그의 format을 설정함
port	접속 시 사용할 port 설정
socket	사용할 소켓 파일 설정
slow_query_log	long_query_time보다 오래 실행되는 쿼리를 로그로 남기는 작업을 설정함 (ON/OFF)
long_query_time	slow query 기준이 되는 값(초)
max_allowed_packet	가장 크게 생성될 수 있는 packet사이즈를 설정함
max_binlog_size	바이너리 로그 파일의 최대 크기 설정 값
max_connections	동시에 접근 가능한 커넥션 갯수 설정
open-files-limit	mysqld 프로세스가 동시에 열수 있는 파일 desc 갯수
join_buffer_size	전체 테이블 스캔으로 조인하는 경우 사용하는 버퍼 사이즈
read_buffer_size	order by / nested loop 수행시 사용하는 버퍼 사이즈
sort_buffer_size	정렬 작업시 사용하는 버퍼 사이즈
server-id	서버의 유니크한 id 설정
read_only	해당 인스턴스에 DML 작업을 불가함. / ON/OFF
expire_logs_days	바이너리 로그 파일의 update 시간을 기준으로 설정된 일만큼 지나면 자동으로 삭제함
default_storage_engine	기본적으로 사용할 스토리지 엔진 값을 설정
tmpdir	temporary table 생성하는 디렉토리 설정
connect_timeout	클라이언트가 커넥션을 설정하는데 완료해야 하는 시간
interactive_timeout	interactive 커넥션 연결 후 끊기 전에  Client로 부터의 메세지를 받기에 대기하는 시간 (초)
wait_timeout	non-interactive 커넥션 연결 후 Client로 부터의 메세지를 받기에 대기하는 시간 (초)

 

 

 

---

MySQL의 계정 구조

 

mysql 계정 :  'user'@'host' 구조로 이루어짐

계정에서 접근서버에 대한 접근관리도 같이 이루어 지고 있다는 것을 뜻함

계정을 통해 접근 하는 서버에 대한 접근 권한 체크와 user를 통한 접근 권한 체크가 같이 이루어 지는 것

 

**MySQL에서의 user의 개념은 오라클의 user의 개념은 다름

 

 

 

---

 

MySQL 명령어 (쿼리 관련 x)

 

SHOW DATABASES	스키마들의 목록
USE {table_name}	특정 스키마의 테이블에 접근
SHOW TABLES	해당 스키마의 테이블 목록
SHOW CREATE TABLE {table_name}	해당 테이블의 생성 구문을 보여줌
DESC {table_name}	해당 테이블의 컬럼 정보
SHOW TABLE STATUS LIKE ‘table_name’	해당 테이블의 상태 정보를 보여줌. %, _ 사용가능함
SELECT CURRENT_USER()	현재 접속한 Account 정보
SHOW GRANTS FOR ‘root’@’localhost’	‘root’@’localhost’의 권한 목록을 보여줌
SET GLOBAL {variable_name} =value	xxx 시스템 변수 값을 value로 변경한다.
SHOW GLOBAL VARIABLES	서버에서 설정한 시스템 변수의 목록과 값을 확인
SHOW GLOBAL STATUS	운영 중 서버의 상태 정보

 

 

 

---

MySQL을 이용하는 방법

예를 들어 config을 읽어서 실행시키는 경우가 있음

 

command 실행으로 실행하려면 아래와 같은 포맷을 지켜야 함

파일을 읽거나

직접 실행을 하거나

#1 명령 실행
./mysql -p -u root -e “SELECT * FROM mysql.user”
#2 파일 읽기
./mysql -p -u root < batch_file

 

뭐 아래와 같이 파일을 만들어서 실행할 수 있다.

#!/bin/bash

#사용하려는 db에 대한 configuration
CONFIG=./db.conf

#test1 db 접속 정보
#grep 으로 해당 라인을 stdouput으로 만듦
# cut -d= -f2 인 것으로 보아 '='을 기준으로 필드를 나누고 2번째 필드를 취하는 것으로 이해할 수 있음 (ex host=test)
test1_db_host=(`cat $CONFIG | grep test1_db_host | cut -d= -f2`)
test1_db_user=(`cat $CONFIG | grep test1_db_user | cut -d= -f2`)
test1_db_pw=(`cat $CONFIG | grep test1_db_password | cut -d= -f2`)
test1_db_port=(`cat $CONFIG | grep test1_db_port | cut -d= -f2`)


#test1 db 이름 (여러 개 가능)
test1_database=(`cat $CONFIG | grep test1_database | cut -d= -f2`)


#mysql 쿼리 함수 => 해당 데이터베이스를 지우는 함수
#해당 호스트, 유저, 패스워드, 포트로 접속하여 따라오는 {info1,2 to be deleted}를 db에서 삭제하는 명령 실행
function delete_test1_database () {
   for cdb1 in ${test1_database[@]}
   do
      echo `mysql -h $test1_db_host -P $test1_db_port -u $test1_db_user -p$test1_db_pw -e "DELETE FROM $cdb1.{info1 to be deleted};"`
      echo `mysql -h $test1_db_host -P $test1_db_port -u $test1_db_user -p$test1_db_pw -e "DELETE FROM $cdb1.{info2 to be deleted};"`
   done
}

 

 

---

참고링크

 

https://library.gabia.com/contents/infrahosting/3564/