Mysql 권한, 원격접속 설정방법

mysql 권한, 원격접속 설정방법

계정생성

cd /usr/bin

mysql -u root -p 

패스워드 입력

mysql>use mysql;

mysql>select host,user,password from user; 로 계정확인

mysql>insert into user (host,user,password) values('localhost','계정',password('비번'));

mysql>flush privileges;

mysql>create database 데이터베이스이름 <--- DB생성

[GRANT 구문사용하기]

mysql> grant all           on *.*  to '아이디'@'localhost' identified by 'PASS';

mysql> grant all           on DB.* to '아이디'@'localhost' identified by 'PASS';

mysql> grant select        on DB.* to '아이디'@'localhost' identified by 'PASS';

mysql> grant update        on DB.* to '아이디'@'localhost' identified by 'PASS';

mysql> grant select,update on DB.* to '아이디'@'localhost' identified by 'PASS';

mysql> flush privileges; 

 

[Mysql 원격 접속 설정]

mysql> grant all on DB명.* to 아이디@접속아이피 identified by 'PASS'; 

mysql> grant all on DB명.* to 아이디@'%' identified by '패스워드';            // 전체 아이피

mysql> flush privileges; 

[컬럼에 권한 설정]

mysql> GRANT SELECT (column_1), INSERT (column_1,column_2) ON DB.* TO 'someuser'@'localhost';

[root 변경]

mysql> update user set password = password('패스') where user='root';

[계정 삭제 설정]

mysql> drop user 아이디@localhost;

[grant 주요 privilege 옵션]

- SELECT,UPDATE,INSERT,DELETE,FILE

- ALTER,CREATE,INDEX,PROCESS,RELOAD,DROP,EXECUTE

[grant 기타 privilege 옵션]

- CREATE TEMPORARY TABLES

- LOCK TABLES,REPLICATION CLIENT

- REPLICATION SLAVE

- SHOW DATABASES

- SHUTDOWN

- SUPER

- GRANT OPTION

===============================================================================================

MySQL UTF-8로 세팅하기

1. etc/my.cnf (또는 my.ini) 에서 캐릭터셋 수정

[client]

#password = your_password

default-character-set=utf8

[mysqld]

init_connect=SET collation_connection = utf8_general_ci

init_connect=SET NAMES utf8

default-character-set=utf8

character-set-server=utf8

collation-server=utf8_general_ci

[mysql]

default-character-set=utf8

2. 환경변수를 모두 수정후 mysql 서비스 재시작

3. mysql에서 캐릭터셋 확인

# mysql

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like 'c%';

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

| Variable_name                  | Value                                |

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

| character_set_client          | utf8                                    |

| character_set_connection  | utf8                                    |

| character_set_database     | utf8                                   |

| character_set_results        | utf8                                    |

| character_set_server         | utf8                                    |

| character_set_system        | utf8                                    |

| character_sets_dir            | /usr/share/mysql/charsets/ |

| collation_connection         | utf8_general_ci                    |

| collation_database           | utf8_general_ci                     |

| collation_server               | utf8_general_ci                     |

| concurrent_insert             | ON                                     |

| connect_timeout               | 5                                        |

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

12 rows in set (0.00 sec)

* MySql에서 데이터베이스 생성

mysql>CREATE DATABASE PAINBLOG DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

* mysql 5.5 버전 이상부터는 

default-character-set=utf8 이 안먹힌다.

character-set-server=utf8 만 써야 먹힘