DBMS 3. MYSQL 기본

1. mysql 설치

 

는 지난번에 함

 

2021.12.03 - [IT 일기 (상반기)/주요정보통신기반시설] - DBMS 1. UBUNTU-MYSQL 설치

 

 

2.  mysql 접속

juu@ubuntu:~$ sudo mysql -u root -p
[sudo] password for juu: 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.27-0ubuntu0.20.04.1 (Ubuntu)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

 

3. 버전 확인

 

나는야 8.0.27 버전

mysql> show variables like "%version%"
    -> ;
+--------------------------+-------------------------------+
| Variable_name            | Value                         |
+--------------------------+-------------------------------+
| admin_tls_version        | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
| immediate_server_version | 999999                        |
| innodb_version           | 8.0.27                        |
| original_server_version  | 999999                        |
| protocol_version         | 10                            |
| replica_type_conversions |                               |
| slave_type_conversions   |                               |
| tls_version              | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
| version                  | 8.0.27-0ubuntu0.20.04.1       |
| version_comment          | (Ubuntu)                      |
| version_compile_machine  | x86_64                        |
| version_compile_os       | Linux                         |
| version_compile_zlib     | 1.2.11                        |
+--------------------------+-------------------------------+
13 rows in set (0.02 sec)

 

 

4. root 계정 패스워드 초기 설정 & mysql의 계정 정보 확인

mysql> alter user 'root'@'localhost' identified by 'mysql';
Query OK, 0 rows affected (0.00 sec)

mysql> select user, host, authentication_string from mysql.usr;
ERROR 1146 (42S02): Table 'mysql.usr' doesn't exist
mysql> select user, host, authentication_string from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+
| user             | host      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| debian-sys-maint | localhost | $A$005$4%qt>aFMQ+Xl'EOLK67oB6sAnqkicZJegafwTMAliEqbhEOMSu3PtSsS6 |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost |                                                                        |
| test             | localhost | $A$005$n
m;Xo0wX|X80xVP71nlcZFuXKS904s9PfMdfeSKPRwbmUVG43p8ujQB |
0.rQYVyjIAdDsrLFrHRrGxjPqghPWgKzYYtJxyt2kN3 |7Nkx%VYoE2N
+------------------+-----------+------------------------------------------------------------------------+
7 rows in set (0.00 sec)

 

 

5. 데이터베이스 생성 & 확인

mysql> create database mydatabase;
Query OK, 1 row affected (0.02 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydatabase         |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

 

 

6. 계정 만들기 & 변경 사항 즉시 반영

mysql> create user 'juu'@'localhost' identified by 'juupass';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

 

 

7. 유저 생성된 것 확인

mysql> select user, host, authentication_string from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+
| user             | host      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| debian-sys-maint | localhost | $A$005$4%
                                          qt>aFM	Q+Xl'EOLK67oB6sAnqkicZJegafwTMAliEqbhEOMSu3PtSsS6 |
| juu              | localhost | $A$005$v2oz,lR_m8cfpZWdRqM3HXrxOASCsA8mi.erFAtw8u6Rr8mQ1EUsSUE8 |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost |                                                                        |
| test             | localhost | $A$005$n
m;Xo0wX|X80xVP71nlcZFuXKS904s9PfMdfeSKPRwbmUVG43p8ujQB |
0.rQYVyjIAdDsrLFrHRrGxjPqghPWgKzYYtJxyt2kN3 |7Nkx%VYoE2N
+------------------+-----------+------------------------------------------------------------------------+
8 rows in set (0.00 sec)

 

 

 

8. 생성해준 데이터베이스를 사용할 수 있도록 권한 부여 & 즉시 반영

mysql> grant all privileges on mydatabase.* to juu@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

 

 

9. 권한 부여받은 것 확인

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

 

*.*  아무 권한이 없다는 의미

juu 계정은 mydatabase 데이터베이스의 모든 권한을 가지고 있고 mysql의 admin이나 system을 접근할 수 있는 권한은 없다는 것을 의미

계정 뒤에 붙은 @localhost는 해당 계정이 localhost 즉 mysql이 설치된 로컬에서만 접속할 수 있다는 의미

만약 다른 서버(remote)에서 접속하고 싶다면 접속하려는 서버의 IP로 계정을 새로 만들어야함.