MySQL 외부 접속 사용자 추가

MySQL 접속
$ mysql -u root -p


root 암호 초기화
mysql> UPDATE user SET password=password('new_password') WHERE user='root';
mysql> FLUSH privileges;


데이터베이스 선택 및 보기
mysql> USE mysql;
mysql> SELECT Host, User, Password FROM user;


사용자 추가
- 서버 주소의 경우 localhost, %, 127.0.0.1 도 같이 처리해 줍니다. 
- 외부 접속은 'localhost'  대신에 '%' 를 사용합니다. 
mysql> INSERT INTO user (Host, User, Password) VALUES ('localhost', '유저명', password('유저패스워드'));
mysql> INSERT INTO user (Host, User, Password) VALUES ('127.0.0.1', '유저명', password('유저패스워드'));
mysql> INSERT INTO user (Host, User, Password) VALUES ('%', '유저명', password('유저패스워드'));
mysql> FLUSH privileges;

* password('유저패스워드')에서 반드시 작은따옴표를 사용해야함

root 사용자 원격 접속 되도록 수정

mysql>SELECT Host, User, Password FROM user;

+-----------------------+------+-------------------------------------------+

| host                  | user | password                                  |

+-----------------------+------+-------------------------------------------+

| localhost             | root | *514F8A4F9D8BBC49B0FBFA7186DD96C8919E8630 | 

| id.server.com      | root |                                           | 

| 127.0.0.1             | root |                                           | 

| localhost             |      |                                           | 

| id.server.com      |      |                                           | 

+-----------------------+------+-------------------------------------------+

요렇게 되어 있는 부분중에 127.0.0.1 이 부분의 host를 외부에서 접속할 수있겠금 변경한다.

% <-- 모든 외부접속 허용
000.000.000.000 <-- 특정 아이피 접속허용
000.000.000.%   <-- 특정 아이피 대역 접속허용으로 변경

mysql>update user set host = '원하는 설정값' where user ='root' and host='127.0.0.1';

을 실행한다.
여기서 주의할 점은 root계정중 host에 localhost라는 컬럼역시 있어야 된다는거다.

사용자 삭제 예
mysql> DELETE FROM user WHERE user='root'  and host='%';


데이터베이스 추가 및 권한 주기
- 모든 권한시 데이터베이스명.* 대신 *.* 로 처리합니다. 
mysql> CREATE DATABASE 데이터베이스명;
mysql> GRANT ALL privileges ON 데이터베이스명.* TO 유저명@localhost IDENTIFIED BY '유저패스워드';
mysql> GRANT ALL privileges ON 데이터베이스명.* TO 유저명@127.0.0.1 IDENTIFIED BY '유저패스워드';
mysql> GRANT ALL privileges ON 데이터베이스명.* TO 유저명@'%' IDENTIFIED BY '유저패스워드';

이렇게 해서 외부접속이 안되면 insert into 구문 잘못 입력시 % 가 잘못 입력되거나 패스워드를 유저 패스워드가 아닌 root 패스워드를 입력했을 가능성이 있습니다. 이 경우 % 를 다시 바꿔주면 됩니다.
mysql> UPDATE INTO user SET Host='%' WHERE user='유저명';
mysql> FLUSH privileges;

이렇게 해주면 외부입력 처리가 됩니다. 


mysql 테이블 명 변경
rename table 원래 테이블명 to 바꿀테이블명;
alter table 테이블명 rename 바꿀테이블명;

1.원래 있던 테이블을 이름만 바꿔서 새로 만들고 싶을때
create table 새테이블명 select * from 원본테이블명

2.테이블 스키마가 같은 경우 데이터 복사할때
insert into tableA select * from tableB

3.테이블이 다른 곳에 복사
create table tableA.fld as select from tableB.fld
create table project.zipcode as select from exam.zipcode;

참고 - http://gudle.net/336

---

※ 외부에서 접속 가능 유무 확인 방법

로컬 컴퓨터에서 telnet localhost 3306 또는
다른 컴퓨터에서 telnet xxx.xxx.xxx.xxx 3306

1. my.cnf 수정

$ sudo vi /etc/mysql/my.cnf

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
#
# * Fine Tuning

위의 bind-address = 127.0.0.1 부분을 아래처럼 주석처리해 준다.

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
# bind-address = 127.0.0.1
#
# * Fine Tuning

2. 사용자 등록

• MySQL 실행 및 데이터베이스 선택
  

$ mysql -u root -p
Enter password:

mysql> use mysql
Database changed

• user 테이블에 데이터 추가
  

mysql> insert into user(host, user, password) values('%', 'root', password('PASSWORD'));

- host 필드에 '%' 데이터를 넣는 것은 어디서든 접속할 수 있다라는 뜻이다.
- 특정 자리에서만 접속하도록 하려면 '192.168.1.131' 처럼 IP 또는 해당 자리에서만 접속이 가능하게 하려면 'localhost' 입력.

• db 테이블에 데이터 추가
  

mysql> insert into db(host, dbname, user) values('%', 'MYSITE', 'root');

- 위의 필드를 추가 후 각 필드의 값으 확인해 보면 'N' 값이 들어 있는데 이 값을 'Y'로 수정. 

mysql> update db set Select_priv='Y', Insert_priv='Y', Update_priv='Y', Delete_priv='Y', Create_priv='Y', Drop_priv='Y', Grant_priv='Y', References_priv='Y', Index_priv='Y', Alter_priv='Y', Create_tmp_table_priv='Y', Lock_tables_priv='Y', Create_view_priv='Y', Show_view_priv='Y', Create_routine_priv='Y', Alter_routine_priv='Y', Execute_priv='Y' where user='root';

• 사용자에게 모든 권한을 주고 싶을 때 다음과 같이 grant 구문을 사용 할 수도 있다.
  

mysql> grant all privileges on *.* to root@'%' identified by 'PASSWORD' width grant option;

mysql> flush privileges;

3. MySQL 재실행

$ sudo /etc/init.d/mysql restart

or

$ mysqladmin -u root -p reload 

출처 - http://cshong.tistory.com/entry/MySQL-%EC%99%B8%EB%B6%80-%EC%A0%91%EC%86%8D-%ED%97%88%EC%9A%A9