MySQL 5.7.33 계정생성 / 스키마생성

시스템환경

OS : Ubuntu 20.10 x64

mysql : 5.7.33

 

1. mysql -u root -p 명령어로 MySQL에 접속합니다.

root@vultr:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.33 MySQL Community Server (GPL)

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.

mysql>

 

2. 데이터베이스 목록확인.

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

mysql>

 

3. use mysql 명령어로 mysql 데이터베이스로 이동

show tables; 로 테이블 목록 확인.

mysql> use mysql
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> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

mysql>

 

4. 사용자를 생성합니다.

ID : tokyoaj

PASS : P@ssw0rd

호스트 : %    <- 모든곳에서 접속가능하게 설정

mysql> CREATE USER 'tokyoaj'@'%'IDENTIFIED BY 'P@ssw0rd';
Query OK, 0 rows affected (0.01 sec)

mysql>

 

 

5. 생성한 사용자를  user 테이블에서 확인합니다.

mysql> SELECT user, host, authentication_string FROM user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *8232A1298A49F710DBEE0B330C42EEC825D4190A |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| tokyoaj       | %         | *8232A1298A49F710DBEE0B330C42EEC825D4190A |
+---------------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)

 

6. 비밀번호 변경

 MySQL 5.7 이상

 SET PASSWORD FOR '계정명'@'host명' = PASSWORD('패스워드');

mysql> SET PASSWORD FOR 'tokyoaj'@'%' = PASSWORD('12345');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MySQL 5.6이하

UPDATE mysql.user SET password = PASSWORD('패스워드') WHERE user = '계정명' AND host = 'host명';

 

 

7. 권한설정

tokyoaj 사용자에서 모든 권한(관리자) 권한을 설정했습니다.

FLUSH PRIVILEGES; 명령어로 설정을 저장합니다.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'tokyoaj'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql>

 

8. MySQL서버에 데이터베이스'master'를 생성합니다.

mysql> CREATE SCHEMA `master` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;
Query OK, 1 row affected (0.00 sec)

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

mysql>