MySQL 외부 접속 허용 설정MySQL에 원격 접속 허용하기MySQL에 root 원격 접속 허용하기 1 문제상황[ | ]
Host '135.79.246.80' is not allowed to connect to this MySQL server Show
2 확인[ | ]
mysql> SELECT Host,User,plugin,authentication_string FROM mysql.user; +-----------+------+-----------------------+-------------------------------------------+ | Host | User | plugin | authentication_string | +-----------+------+-----------------------+-------------------------------------------+ | localhost | root | mysql_native_password | *8024A6913C57E024BDFC6E813A57DFB924E6803A | | % | root | | | +-----------+------+-----------------------+-------------------------------------------+ 2 rows in set (0.000 sec)즉 root 계정으로는 로컬에서만 접속가능하다.(같은 방법으로 root 대신 다른 사용자명도 조회할 수 있다.)버전이 5.7 이하에선 authentication_string 이 아닌 password 로 사용하여야 한다.[2] 3 변경[ | ]
3.1 모든 IP 허용[ | ]
GRANT ALL PRIVILEGES ON *.* TO '아이디'@'%' IDENTIFIED BY '패스워드'; INSERT INTO mysql.user (host,user,password,ssl_cipher, x509_issuer, x509_subject) VALUES ('%','아이디',password('패스워드'),'','',''); GRANT ALL PRIVILEGES ON *.* TO '아이디'@'%'; FLUSH PRIVILEGES; 3.2 IP 대역 허용[ | ]
GRANT ALL PRIVILEGES ON *.* TO '아이디'@'111.222.%' IDENTIFIED BY '패스워드'; INSERT INTO mysql.user (host,user,password,ssl_cipher, x509_issuer, x509_subject) VALUES ('111.222.%','아이디',password('패스워드'),'','',''); GRANT ALL PRIVILEGES ON *.* TO '아이디'@'111.222.%'; FLUSH PRIVILEGES; 3.3 특정 IP 1개 허용[ | ]
GRANT ALL PRIVILEGES ON *.* TO '아이디'@'111.222.33.44' IDENTIFIED BY '패스워드'; INSERT INTO mysql.user (host,user,password,ssl_cipher, x509_issuer, x509_subject) VALUES ('111.222.33.44','아이디',password('패스워드'),'','',''); GRANT ALL PRIVILEGES ON *.* TO '아이디'@'111.222.33.44'; FLUSH PRIVILEGES; 4 원래 상태로 복구[ | ]
DELETE FROM mysql.user WHERE Host='%' AND User='아이디'; FLUSH PRIVILEGES; 5 LISTEN IP대역 변경[ | ]root@zetawiki:~# netstat -ntlp | grep mysqld tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 7931/mysqld root@zetawiki:~# vi /etc/mysql/my.cnf
#bind-address = 127.0.0.1 [mysqld] bind-address = 0.0.0.0 root@zetawiki:~# service mysql restart mysql stop/waiting mysql start/running, process 8190 root@zetawiki:~# netstat -ntlp | grep mysqld tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 8190/mysqld 6 같이 보기[ | ]
7 주석[ | ]
|