MySql 명령어

DB 목록 보기, DB 선택, 테이블 목록, 테이블 구조 보기

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mygame             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> USE mygame;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| mygame     |
+------------+
1 row in set (0.00 sec)

mysql> SHOW TABLES;
+------------------+
| Tables_in_mygame |
+------------------+
| tbl_user         |
+------------------+
1 row in set (0.00 sec)

mysql> DESCRIBE tbl_user;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| idx   | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | YES  |     | NULL    |                |
| level | int         | YES  |     | NULL    |                |
| gold  | int         | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql>

테이블 생성

mysql> SHOW CREATE TABLE tbl_user;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                      |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl_user | CREATE TABLE `tbl_user` (
  `idx` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `level` int DEFAULT NULL,
  `gold` int DEFAULT NULL,
  PRIMARY KEY (`idx`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tbl_user;
+-----+-------+-------+--------+
| idx | name  | level | gold   |
+-----+-------+-------+--------+
|   1 | Clack |    46 | 786000 |
|   2 | Obung |    46 | 345000 |
+-----+-------+-------+--------+
2 rows in set (0.00 sec)

mysql>

계정 추가, 권한설정, 계정 삭제

mysql> CREATE USER 'mygame_admn'@'localhost' IDENTIFIED BY '##########';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, INDEX ON mygame.* TO 'mygame_admin'@'localhost';
ERROR 1410 (42000): You are not allowed to create a user with GRANT

mysql> SELECT USER(), CURRENT_USER();
+----------------+----------------+
| USER()         | CURRENT_USER() |
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+
1 row in set (0.00 sec)

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

mysql> CREATE USER 'mygame_admin'@'localhost' IDENTIFIED BY '##########';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, INDEX ON mygame.* TO 'mygame_admin'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql>

 

DB 접속 포트 확인

mysql> SHOW VARIABLES LIKE 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.01 sec)

mysql>

MySQL 접속 종료

mysql> exit;
Bye

MySQL 실행관련 변수값 확인

mysql> SHOW VARIABLES LIKE 'datadir';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| datadir       | /usr/local/mysql/data/ |
+---------------+------------------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'pid_file';
+---------------+----------------------------------------+
| Variable_name | Value                                  |
+---------------+----------------------------------------+
| pid_file      | /usr/local/mysql/data/mysqld.local.pid |
+---------------+----------------------------------------+
1 row in set (0.00 sec)