xampp - mysql 설정

XAMPP를 설치하고 나면 mysql의 패스워드가 설정되어 있지 않다.
하기 작업을 수행해야 한다.

1. XAMPP control panel을 열고 MySQL 실행

XAMPP control panel - MySQL Start 클릭

c:\xampp\mysql\bin>mysql -u root -p
Enter password:        <-- 처음엔 암호가 없음으로 그냥 엔터!!!
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.4.13-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> use mysql;
Database changed
MariaDB [mysql]> SELECT host, user, password FROM user;
+-----------+------+----------+
| Host      | User | Password |
+-----------+------+----------+
| localhost | root |          |
| 127.0.0.1 | root |          |
| ::1       | root |          |
| localhost | pma  |          |
+-----------+------+----------+
4 rows in set (0.002 sec)

MariaDB [mysql]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'p@ssw0rd';
Query OK, 0 rows affected (0.003 sec)

MariaDB [mysql]> ALTER USER 'root'@'127.0.0.1' IDENTIFIED BY 'p@ssw0rd';
Query OK, 0 rows affected (0.003 sec)

MariaDB [mysql]> ALTER USER 'root'@'::1' IDENTIFIED BY 'p@ssw0rd';
Query OK, 0 rows affected (0.003 sec)

MariaDB [mysql]> SELECT host, user, password FROM user;
+-----------+------+-------------------------------------------+
| Host      | User | Password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *8D2**********************************66E |
| 127.0.0.1 | root | *8D2**********************************66E |
| ::1       | root | *8D2**********************************66E |
| localhost | pma  |                                           |
+-----------+------+-------------------------------------------+
4 rows in set (0.000 sec)

MariaDB [mysql]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.001 sec)

MariaDB [mysql]>

 

2. 신규 유저 생성 및 DB 권한 추가

MariaDB [mysql]> CREATE USER tank@localhost IDENTIFIED BY 'p@ssw0rd';
Query OK, 0 rows affected (0.003 sec)

MariaDB [mysql]> CREATE USER tank@127.0.0.1 IDENTIFIED BY 'p@ssw0rd';
Query OK, 0 rows affected (0.003 sec)

MariaDB [mysql]> SELECT host, user, password FROM user ORDER BY user, host;
+-----------+------+-------------------------------------------+
| Host      | User | Password                                  |
+-----------+------+-------------------------------------------+
| localhost | pma  |                                           |
| 127.0.0.1 | root | *8D2*******************************B2066E |
| ::1       | root |                                           |
| localhost | root | *8D2*******************************B2066E |
| 127.0.0.1 | tank | *FD3*******************************7D9CAE |
| localhost | tank | *FD3*******************************7D9CAE |
+-----------+------+-------------------------------------------+
6 rows in set (0.001 sec)

MariaDB [mysql]> DELETE FROM user WHERE user = 'pma';
Query OK, 1 row affected (0.003 sec)

MariaDB [mysql]> SELECT host, user, password FROM user ORDER BY user, host;
+-----------+------+-------------------------------------------+
| Host      | User | Password                                  |
+-----------+------+-------------------------------------------+
| 127.0.0.1 | root | *8D2*******************************B2066E |
| ::1       | root |                                           |
| localhost | root | *8D2*******************************B2066E |
| 127.0.0.1 | tank | *FD3*******************************7D9CAE |
| localhost | tank | *FD3*******************************7D9CAE |
+-----------+------+-------------------------------------------+
5 rows in set (0.001 sec)

MariaDB [mysql]>
MariaDB [mysql]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.000 sec)

 

 

3. 위창을 그대로 두고 새로운 커맨드 창을 열고 접속 여부 테스트 진행.

MariaDB [mysql]> CREATE USER tank@localhost IDENTIFIED BY 'p@ssw0rd';
Query OK, 0 rows affected (0.003 sec)

MariaDB [mysql]> CREATE USER tank@127.0.0.1 IDENTIFIED BY 'p@ssw0rd';
Query OK, 0 rows affected (0.003 sec)

MariaDB [mysql]> select host, user, password from user;
+-----------+------+-------------------------------------------+
| Host      | User | Password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *8D2*********************************066E |
| localhost | tank | *FD3*********************************9CAE |
| 127.0.0.1 | root | *8D2*********************************066E |
| ::1       | root |                                           |
| localhost | pma  |                                           |
| 127.0.0.1 | tank | *FD3*********************************9CAE |
+-----------+------+-------------------------------------------+
6 rows in set (0.001 sec)

MariaDB [mysql]> SELECT host, user, password FROM user ORDER BY user, host;
+-----------+------+-------------------------------------------+
| Host      | User | Password                                  |
+-----------+------+-------------------------------------------+
| localhost | pma  |                                           |
| 127.0.0.1 | root | *8D2*********************************066E |
| ::1       | root |                                           |
| localhost | root | *8D2*********************************066E |
| 127.0.0.1 | tank | *FD3*********************************9CAE |
| localhost | tank | *FD3*********************************9CAE |
+-----------+------+-------------------------------------------+
6 rows in set (0.001 sec)

MariaDB [mysql]> DELETE FROM user WHERE user = 'pma';
Query OK, 1 row affected (0.003 sec)

MariaDB [mysql]> SELECT host, user, password FROM user ORDER BY user, host;
+-----------+------+-------------------------------------------+
| Host      | User | Password                                  |
+-----------+------+-------------------------------------------+
| 127.0.0.1 | root | *8D2*********************************066E |
| ::1       | root |                                           |
| localhost | root | *8D2*********************************066E |
| 127.0.0.1 | tank | *FD3*********************************9CAE |
| localhost | tank | *FD3*********************************9CAE |
+-----------+------+-------------------------------------------+
5 rows in set (0.001 sec)

MariaDB [mysql]>

 

4. 필요한 계정 생성 및 권한 추가

CREATE USER tank@localhost IDENTIFIED BY 'p@ssw0rd';
CREATE USER tank@127.0.0.1 IDENTIFIED BY 'p@ssw0rd';

GRANT ALL PRIVILEGES ON [DB이름].[테이블] TO tank@127.0.0.1 IDENTIFIED BY 'p@ssw0rd';
GRANT ALL PRIVILEGES ON [DB이름].[테이블] TO tank@localhsot IDENTIFIED BY 'p@ssw0rd';

FLUSH PRIVILEGES;