웹 개발 정보

1. MySQL 검색 결과 행 번호 매기기(ROWNUM)

MySQL은 oracle과 다르게 검색 결과에 행 번호를 넣는 방법이 다릅니다. 

oracle은 ROWNUM을 통해서 검색된 결과에 자동적으로 순번이 들어가는데 MySQL은 그렇지 않습니다. 

그래서 행번호를 적용하는 방법이 아래와 같습니다. 

2. MySQL 페이징

검색결과에 행 번호를 추가를 했으면 페이징을 하는 방법입니다. 

1. MySQL 사용자 추가 

create user '사용자'@'IP' identified by '비밀번호';

사용자 : 사용자 아이디 입력

IP : 사용자가 접속될 IP 입력 예) localhost(로컬 접속), 1.2.3.4(1,2,3,4 IP만 허용), 1.2.3.%(1,2,3 번대 IP 허용), %(모든 IP 허용)

비밀번호 : 사용자 비밀번호 입력 

#db 접속 
mysql -u root -p

#db 사용자 추가 
create user 'test1'@'localhost' identified by 'pass1';
create user 'test2'@'1.2.3.%' identified by 'pass2';
create user 'test3'@'%' identified by 'pass3';

2. 등록된 사용자에게 권한 추가

grant all privileges on DB이름.Table이름 to '사용자'@'IP';

grant all privileges on DB이름.* to '사용자'@'IP'; -- DB에 대한 전체 테이블에 해당하는 권한을 추가시 

grant all privileges on *.* to '사용자'@'IP'; -- 전체 DB, 테이블에 해당하는 권한을 추가시 

#db 사용자 추가 
create user 'test1'@'localhost' identified by 'pass1';
create user 'test2'@'1.2.3.%' identified by 'pass2';
create user 'test3'@'%' identified by 'pass3';

#db 사용자 전체권한 추가 
grant all privileges on DB이름.Table이름 to 'test1'@'localhost';
grant all privileges on DB이름.* to 'test2'@'1.2.3.%'; -- DB에 대한 전체 테이블에 해당하는 권한을 추가시 
grant all privileges on *.* to 'test3'@'%'; -- 전체 DB, 테이블에 해당하는 권한을 추가시

권한에 대한 상세한 내용 : https://jang2r.tistory.com/41?category=794926

3. MySQL 사용자 삭제

drop user '사용자'@'localhost';

#db 사용자 삭제 
drop user 'test1'@'pass1';

1. MySQL 사용자 권한 확인

1-1. 기본권한 조회

# mysql 접속
mysql -u root -p
# mysql DB 접근
mysql> use mysql 
# 기본 권한 조회
mysql> select * from user where user = [사용자ID]

1-2. 사용자 권한 조회

# 사용자별 권한 확인
mysql> SHOW GRANTS FOR '사용자계정'@'호스트';
# 접속된 계정 권한 확인
mysql> SHOW GRANTS FOR CURRENT_USER;

2. MySQL 사용자 권한 추가

예)

GRANT ALL PRIVILEGES ON DB이름.테이블이름 TO 아이디@호스트 IDENTIFIED BY '비밀번호' with grant option;

# 권한 종류

- ALL PRIVILEGES : 모든 권한 추가 

- SELECT, INSERT, UPDATE, DELETE, ... : 권한을 일부분을 추가 

# 적용될 DB 및 테이블 

- DB이름.테이블이름 : *.* 시 모든 DB, 모든 Table 권한 부여 

# with grant option 옵션

- with grant option : GRANT를 사용할 수 있는 권한 추가 

1. DB 덤프(dump) 및 복구

# 전체 DB dump 및 복구
./mysqldump -u [계정] -p  --all-databases > [생성dump파일 이름].sql
./mysql -u [계정] -p < [생성dump파일 이름].sql

# 특정 DB dump 및 복구
./mysqldump -u [계정] -p [DB 이름] > [생성dump파일 이름].sql
./mysql -u [계정] -p [DB 이름] < [생성dump파일 이름].sql

2. 테이블 덤프(dump) 및 복구

# 테이블 dump 및 복구
./mysqldump -u [계정] -p [DB 이름] [Table 이름1] [Table 이름2] .. > [생성dump파일 이름].sql
./mysql -u [계정] -p [DB 이름] < [생성dump파일 이름].sql 

3. 데이터 덤프(dump) 및 복구

# DB 데이터 dump 및 복구
# 테이블 전체 데이터 dump
- ./mysqldump -u [계정] -p -t [DB 이름] [Table 이름1] > [생성dump파일 이름].sql
# 조건에 해당하는 데이터 dump
- ./mysqldump -u [계정] -p [-w '조건절'] [DB 이름] [Table 이름1] > [생성dump파일 이름].sql

- ./mysql -u [계정] -p [DB 이름] [Table 이름1] < [생성dump파일 이름].sql

MySQL 원격 접속 허용

MySQL을 설치를 진행하게 되면 기본적으로는 로컬에서만 접속이 가능하도록 설치가 됩니다. 

이런 설정을 외부에서 DB를 접속 할 수 있게 변경을 해줘야 합니다. 

1. MySQL 접속 IP 확인

5.7 이상 버전 : SELECT Host,User,plugin,authentication_string FROM mysql.user;

5.6 이하 버전 : SELECT Host,User,plugin,password FROM mysql.user;

위에 있는 명령어로 확인이 가능합니다. 

mysql> SELECT Host,User,plugin,authentication_string FROM mysql.user;

+-----------+---------------+-----------------------+-------------------------------------------+
| Host      | User          | plugin                | authentication_string                     |
+-----------+---------------+-----------------------+-------------------------------------------+
| localhost | root          | mysql_native_password | *97925E5A498F7FDBC94DAC363A9753359A6E14D9 |
| localhost | mysql.session | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+---------------+-----------------------+-------------------------------------------+

출력된 결과를 보면 root인 사용자로 DB를 외부에서 접속시 localhost에만 접속이 가능하게 되어 있어 접속이 불가합니다. 

2. 외부 IP 추가

외부 IP접속이 추가하는 방법은 3가지 정도로 나눈다. 

2-1. 모든 ip에서 접속시 허용

GRANT ALL PRIVILEGES ON *.* TO '아이디'@'%' IDENTIFIED BY '패스워드';

FLUSH PRIVILEGES;

2-2. 특정 ip대역에 접속시 허용

GRANT ALL PRIVILEGES ON *.* TO '아이디'@'111.222.%' IDENTIFIED BY '패스워드'; 

FLUSH PRIVILEGES;

2-3. 특정 단일 ip에서 접속시 허용

GRANT ALL PRIVILEGES ON *.* TO '아이디'@'111.222.33.44' IDENTIFIED BY '패스워드';

FLUSH PRIVILEGES;

3. 등록 확인 후 적용

등록이 끝났다고 하면 SELECT Host,User,plugin,authentication_string FROM mysql.user; 명령어로 확인 후 외부에서 다시 접속을 시도해 봅시다.