MySQL 명령행 클라이언트 및 관리 명령어

GUI로 된 툴을 쓸 수 있다면 좋겠지만, 사용할 수 있는게 터미널 밖에 없는 상황도 존재 합니다. MySQL Command Line Client의 사용법을 알아 두면 편리할 때가 많습니다.

 

프로그램의 이름은 mysql 입니다. 윈도우와 리눅스에서 동일하게 사용할 수 있습니다. MariaDB 에서도 동일하게 사용할 수 있습니다.

 

하는김에 몇가지 관리 명령어들도 알아보겠습니다.

 

 

1. Command Line Client로 접속하기

 

기본적인 사용법은 다음과 같습니다.

 

mysql -h서버 -u아이디 -p비밀번호 데이터베이스명

 

서버의 IP 주소가 "192.168.0.100" 이고, 아이디는 "root", 비밀번호는 "rootpassword" 이며, 접속시 바로 사용할 데이터베이스 이름이 "testdb" 라면 다음과 같이 입력합니다. 옵션이름 -p 와 옵션값 사이에 공백을 두지 않습니다.(DB명으로 처리되어 버립니다.)

 

C:\> mysql -h192.168.0.100 -uroot -prootpassword testdb

 

서버가 localhost (127.0.0.1) 일 경우에는 -h 옵션을 생략해도 됩니다. 처음 DB 를 바로 지정하지 않고 접속 후 지정해서 사용하려면 DB명을 생략해도 됩니다.

 

C:\> mysql -uroot -prootpassword

 

보안 목적으로 입력되는 비밀번호를 다른 사람이 보지 못하게 하려면 -p 옵션에 값이 없이 사용하면 됩니다. 비밀번호 입력 프롬프트가 나오고 입력되는 값은 * 표 처리 됩니다.

 

C:\> mysql -uroot -p

Enter password: ****

 

이제 데이터베이스 서버에 접속이 되면 mysql> 프롬프트가 보일 것입니다.

 

 

 

 

 

2. 데이터베이스의 생성 및 삭제

 

다음 명령으로 데이터베이스를 생성할 수 있습니다.

 

mysql> CREATE DATABASE testdb CHARACTER SET utf8 COLLATE utf8_general_ci;

 

데이터베이스 이름이 "testdb", 캐릭터 셋은 "utf8", COLLATE는 "utf8_general_ci" 인 데이터베이스가 생성됩니다. COLLATE는 문자열 비교시 어떻게 처리될지를 지정하는 방법이라고 생각하면 되겠습니다. utf8_general_ci 는 대소문자를 구분하지 않습니다. 대소문자를 구분하려면 binary 타입으로 지정해야 합니다. 위의 경우라면 "utf8_bin" 이 되겠습니다. 필요에 따라 선택을 하면 되겠습니다.

 

데이터베이스 생성시 캐릭터셋과 COLLATE 를 생략하면 서버 설치시 지정한 기본 값으로 설정이 됩니다. 다음 처럼 사용 됩니다.

 

mysql> CREATE DATABASE testdb;

 

데이터베이스의 삭제는 DROP 명령으로 수행합니다. 다음과 같습니다. 데이터베이스의 DROP 은 백업이 없으면 되돌릴 수 없으므로 신중하게 생각해서 작업해야 겠습니다.

 

mysql> DROP DATABASE testdb;

 

 

3. 사용자 추가 및 제거

 

이제 데이터베이스가 만들어 졌으니 사용자를 추가해 보겠습니다. 사용자를 만들면 mysql DB내에 user 테이블에 정보가 저장됩니다. 사용자 아이디와 접속할 수 있는 호스트의 두 가지 정보로 유일하게 구별됩니다.

 

사용자를 생성하기 위해서  CREATE 명령을 사용합니다. 아이디는 "testdbuser" 이고, 비밀번호는 "password"이고, localhost 에서 접속할 수 있는 사용자를 생성합니다.

 

mysql> CREATE USER 'testdbuser'@'localhost' IDENTIFIED BY 'password';

 

GRANT 명령을 사용해서 위에서 생성한 사용자에게 권한을 줍니다. ALL PRIVILEGES ON *.* 의 의미는 모든 권한(ALL PRIVILEGES)을 모든 DB의 모든 테이블(*.*)에 준다는 뜻입니다. WITH GRANT OPTION의 의미는 다른 사용자에게 자신이 가진권한을 주거나 회수할 수 있다는 뜻입니다.

 

mysql> GRANT ALL PRIVILEGES ON *.* TO 'testdbuser'@'localhost' WITH GRANT OPTION;

 

다음과 같이 명령하면 testdb 에만 모든 권한을 가지게 되고 다른 사용자에게 권한을 줄수는 없습니다. 실제로 CRAETE 명령을 사용하지 않고 GRANT 명령만 사용하면 사용자가 생성되고, 권한이 주어 집니다.

 

mysql> GRANT ALL PRIVILEGES ON testdb.* TO 'testdbuser'@'localhost';

 

사용자를 생성할 때 다음처럼 접속할 수 있는 주소 부분에 '%' 를 사용하면 localhost 를 제외한 모든 주소에서 접속이 가능합니다.

 

mysql> CREATE USER 'testdbuser'@'%' IDENTIFIED BY 'password';

 

 

ALL PRIVILEGES 부분에는 SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER 등 권한 명칭을 콤마로 분리해서 나열하는 방식으로 특정 권한만을 줄 수도 있습니다.

 

이제 권한을 제거하는 방법을 알아보도록 하겠습니다. 권한을 없앨때는 REVOKE 명령을 사용합니다. 아래 명령으로 'localhost' 에서 접속가능한 'testdbuser' 에게서 모든 권한을 뺐습니다.

 

mysql> REVOKE ALL PRIVILEGES *.* FROM 'testdbuser'@'localhost';

 

 

GRANT 명령과는 달리 REVOKE 명령은 모든권한을 뺏더라도 user 테이블에서 사용자 정보사 완전히 삭제되지 않습니다. 사용자 정보의 완전한 제거를 원한다면 DROP USER 명령을 사용합니다.

 

mysql> DROP USER 'testdbuser'@'localhost';

 

이것으로 사용자를 추가, 권한부여 및 회수, 사용자 삭제 명령을 알아보았습니다.

 

 

4. 데이터베이스 선택 및 쿼리 작업

 

MySQL은 여러개의 데이터베이스를 만들어서 사용할 수 있습니다. 각 DB에 대해 작업을 하려면 현재 작업을 할 데이터베이스를 선택해야합니다. 

 

먼저 데이터베이스 목록을 보기 위해서 show databases 명령을 사용합니다.

 

mysql> show databases;

 

use 명령을 사용하여 "testdb" 를 선택 합니다.

 

mysql> use testdb;

 

이제부터 작업은 testdb 에 대해 수행됩니다. 현재 선택된 testdb가 가지고 있는 모든 테이블 목록을 보기 위해서 show tables 명령을 사용합니다.

 

mysql> show tables;

 

show databases 와 show tables 명령을 실행했을 때 데이터베이스나 테이블이 너무 많으면 보기 힘들수가 있습니다. 이때는 like 절을 사용해서 필터링할 수 있습니다. 다음은 테이블명이 'time' 으로 시작하는 테이블만 보여줍니다.

 

mysql> show tables like 'time%';

 

데이터베이스가 선택되고 테이블 생성, 삭제, 조회, 입력, 수정, 삭제 등을 쿼리를 사용해서 처리할 수 있습니다.

 

 

5. 데이터베이스 및 테이블 생성 스크립트 보기

 

show 명령으로 데이터베이스 생성할 때 사용된 쿼리와 테이블 생성시 사용된 쿼리를 확인할 수 있습니다. 다음처럼 명령을 사용합니다. 데이터베이스명이 "testdb" 이고, 테이블 명이 "tb_test" 라고 가정한 명령입니다.

 

 

mysql> show create databse testdb;

 

mysql> show create table tb_test;

 

 

데이터베이스 생성 및 테이블 생성시 사용된 쿼리를 확인하면 지정된 캐릭터셋도 간단히 확인할 수 있습니다.

 

 

6. 쿼리 스크립트 파일 실행하기

 

명령행에서 쿼리를 실행하려면 오타가 나거나 하면 처음부터 다시 입력해야 되는 사태가 발생합니다. 쿼리를 파일로 만들어 두고 그것을 실행하는 방법이 있습니다.

 

첫 번째는 mysql 명령실행시에 지정하여 실행하는 방법 입니다. insert.sql 파일을 실행합니다.

 

C:\> mysql -utestdbuser -p testdb < insert.sql

Enter password: ****

 

"testdb" 에 대해 insert.sql 파일내의 쿼리가 실행됩니다. 만약 insert.sql 파일내에 use 명령으로 데이터베이스를 선택하고 있고, 현재 사용자가 그 DB에 대한 권한이 있다면 작업 DB 가 use 로 지정한 DB로 바뀝니다.

 

두 번째 방법을 mysql 클라이언트가 실행된 상태에서 source 명령을 사용하는 것입니다. 데이터베이스에 접속한 상태에서 다음 명령을 내립니다.

 

mysql> source D:\insert.sql;

 

insert.sql 파일의 내용이 실행됩니다. 파일의 절대경로를 지정하지 않으면 접속하기 위해서 mysql 명령을 내릴 때의 폴더가 현재 폴더가 됩니다.

 

 

7. 프로세스 확인하기

 

현재 접속자를 확인하고, lock 이 걸린 프로세스를 죽이거나 하는 작업에 필요합니다.

 

- 프로세스 리스트보기

 

mysql> show processlist;

 

- 프로세스 죽이기(프로세스 아이디는  리스트에 나오는 Id 입니다.)

 

mysql> kill 프로세스아이디

 

 

8. 관리자 비밀번호 분실시 비밀번호 변경 방법

 

- 현재 MySQL 서버를 중지합니다.

- 권한을 체크하지 않게하는 --skip-grant 옵션을 사용하여 서버를 시작합니다.

 

C:\> mysqld --skip-grant

 

- mysql 데이터베이스의 user 테이블에서 필요한 계정의 비밀번호를 변경합니다.

- 서버 중지 후 정상적으로 다시 시작 합니다