1. DB 관리

🎯 SHOW

디렉토리 내 DB 목록을 조회

✍ 입력

show databases;

💻 출력

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
6 rows in set (0.00 sec)

🎯 CREATE

이름을 지정하여 새로운 DB생성

✍ 입력

create database nembizzang; # nembizzang : 데이터베이스 이름

💻 출력

mysql> create database nembizzang;
Query OK, 1 row affected (0.02 sec)   # nembizzang DB 생성 완료

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| nembizzang         |				  # nembizzang DB 생성 확인
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
7 rows in set (0.00 sec)

🎯 USE

database 사용(이동)

✍ 입력

use nembizzang

💻 출력

mysql> use nembizzang;
Database changed

🎯 DROP

database 삭제

✍ 입력

drop database nembizzang;

💻 출력

mysql> drop database nembizzang;
Query OK, 0 rows affected (0.04 sec)   # DB 제거 완료

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
6 rows in set (0.00 sec)               # DB 제거 확인

2. USER 관리

🎯 SELECT user FROM user

mySQL 내 user 조회

✍ 입력

use mysql;

select user, host from user;

💻 출력

mysql> use mysql;
Database changed
mysql> select host, user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
4 rows in set (0.00 sec)

🎯 CREATE USER @localhost

현재 PC에서만 접속 가능한 사용자를 비밀번호와 함께 생성

✍ 입력

create user 'nembizzang'@'localhost' identified by '1234';

select host, user from user;

💻 출력

mysql> create user 'nembizzang'@'localhost' identified by '1234';
Query OK, 0 rows affected (0.03 sec)           # nembizzang user 생성 완료

mysql> select host, user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | nembizzang       |               # nembizzang user 확인
| localhost | root             |
+-----------+------------------+
5 rows in set (0.00 sec)

🎯 CREATE USER @'%'

외부에서 접속 가능한 사용자를 비밀번호와 함께 생성

✍ 입력

create user 'nembizzang'@'%' identified by '1234';

select host, user from user;

💻 출력

mysql> create user 'nembizzang1'@'%' identified by '1234';
Query OK, 0 rows affected (0.02 sec)         # nembizzang1 user 생성 완료

mysql> select host, user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | nembizzang1      |             # nembizzang1 user 확인
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | nembizzang       |
| localhost | root             |
+-----------+------------------+
6 rows in set (0.00 sec)

📌 host 정보가 다른 user는 id가 동일해도 생성가능

🎯 DROP USER

같은 ID의 user여도 접근 경로에 따라 별도로 삭제

✍ 입력

DROP USER 'nembizzang'@'localhost';

DROP USER 'nembizzang1'@'%';

SELECT host, user FROM user;

💻 출력

mysql> DROP USER 'nembizzang'@'localhost';
Query OK, 0 rows affected (0.03 sec)

mysql> DROP USER 'nembizzang1'@'%';
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT host, user FROM user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
4 rows in set (0.00 sec)

3. USER 권한 관리

🎯 SHOW GRANTS FOR _

사용자에게 부여된 모든 권한 목록을 확인

✍ 입력

show grants for 'nembizzang'@'localhost';

💻 출력

mysql> show grants for 'nembizzang'@'localhost';
+------------------------------------------------+
| Grants for nembizzang@localhost                |
+------------------------------------------------+
| GRANT USAGE ON *.* TO `nembizzang`@`localhost` |
+------------------------------------------------+
1 row in set (0.00 sec)

🎯 GRANT ALL ON _ TO

사용자에게 특정 데이터베이스의 모든 권한을 부여

✍ 입력

grant all on testdb.* to 'nembizzang'@'localhost';

show grants for 'nembizzang'@'localhost';

💻 출력

mysql> grant all on testdb.* to 'nembizzang'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for 'nembizzang'@'localhost';
+----------------------------------------------------------------+
| Grants for nembizzang@localhost                                |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `nembizzang`@`localhost`                 |
| GRANT ALL PRIVILEGES ON `testdb`.* TO `nembizzang`@`localhost` |
+----------------------------------------------------------------+
2 rows in set (0.00 sec)

🎯 REVOKE ALL ON _ FROM

사용자에게서 특정 데이터베이스의 모든 권한을 삭제

✍ 입력

revoke all on testdb_* from 'nembizzang'@'localhost';

show grants for 'nembizzang'@'localhost'

💻 출력

mysql> revoke all on testdb.* from 'nembizzang'@'localhost';
Query OK, 0 rows affected (0.04 sec)

mysql> show grants for 'nembizzang'@'localhost';
+------------------------------------------------+
| Grants for nembizzang@localhost                |
+------------------------------------------------+
| GRANT USAGE ON *.* TO `nembizzang`@`localhost` |
+------------------------------------------------+
1 row in set (0.00 sec)

🎯 FLUSH PRIVILEGES

수정 내용이 적용되지 않은 경우 새로고침

✍ 입력

flush privileges;