Posted on 2018/04/23 19:30
Filed Under Linux/설정방법

DNS server, DNS service  

원격 접속 가능한 호스트 및 사용자 확인



[root@company bin]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 561388
Server version: 5.0.51-log Source distribution
 
 
 
mysql> select HOST, USER, Password FROM mysql.user;
+-----------------+-------+-------------------------------------------+
| HOST            | USER  | Password                                  |
+-----------------+-------+-------------------------------------------+
| localhost       | root  | *(생략)                           | 
| company         | root  |                                           | 
| 127.0.0.1       | root  |                                           | 
| localhost       |       |                                           | 
| company         |       |                                           | 
| localhost       | news  | *(생략)                                | 
| 211.200.200.200 | news  | (생략)                                     | 
| 100.200.100.100 | news  | (생략)                                      | 
| 192.168.100.110 | news  | (생략)                                      | 
+-----------------+-------+-------------------------------------------+
 
9 rows in set (0.02 sec)


위 경우 HOST에 명시된 호스트에서만 접속 가능.

따라서 사용자 추가.


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
| company            | 
+--------------------+
3 rows in set (0.01 sec)
 
mysql> use mysql
Database changed
 
mysql> insert into user (host,user,password) 
values('%','news',password('newspasswd'));
Query OK, 0 rows affected (0.02 sec)
 
mysql> GRANT all privileges on *.* to 'news'@'%' 
identified by 'newspasswd' ;
Query OK, 0 rows affected (0.02 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)
 
 
mysql> select HOST, USER, Password FROM mysql.user;
+-----------------+-------+-------------------------------------------+
| HOST            | USER  | Password                                  |
+-----------------+-------+-------------------------------------------+
| localhost       | root  | *7540F67EE4(생략)                         | 
| company         | root  |                                           | 
| 127.0.0.1       | root  |                                           | 
| localhost       |       |                                           | 
| company         |       |                                           | 
| localhost       | news  | *7540F67EE4                               | 
| 211.200.200.200 | news  | 49d7                                      | 
| 100.200.100.100 | news  | 49d7                                      | 
| 192.168.100.110 | news  | 49d7                                      | 
| %  | news  | *7540F67EE4                               | 
+-----------------+-------+-------------------------------------------+
10 rows in set (0.02 sec)
 

단 MySQL 5.7에서는 Insert 명령어를 아래와 같이 변경해야 함.
insert into user(host,user,authentication_string,ssl_cipher,x509_issuer,x509_subject) values ('%', 'news', password('password'),'','','');


호스트를 %로 할경우 어떤 호스트에서든 접속 가능.


이렇게 하고도... 외부에서 접속이 안될 경우

1. OS 방화벽 (리눅스 방화벽 확인 ) 해제 - ufw disable
2. MySQL 설정 파일 "my.cnf" (mysqld.cnf) 에, bind-address=127.0.0.1 로 되어 있는 것을 "0.0.0.0" 으로 변경 후, 
3. MySQL 재시작

2018/04/23 19:30 2018/04/23 19:30

트랙백 주소 : 이 글에는 트랙백을 보낼 수 없습니다

About

by CoCo

Notice

Counter

· Total
: 854500
· Today
: 7
· Yesterday
: 67