자주 쓰는 DB Command와 Query

해당 포스팅에서는 현업에서 자주 사용하는 DB 커맨드 및 쿼리를 정리합니다.

DB는 Mysql기준입니다.

 

보통 개발 DB나 로컬 DB에서 실행하겠지만...

그게 아니라면 주의해서 실행하도록 합시다.

목차

• 1. 사용자 생성 및 권한 설정
• 2. safe mode update 해제
• 3. 테이블 백업
• 4. 시스템 변수  변경
• 5. 상태 확인
• 6. 백업 & 복구

1. 사용자 생성 및 권한 설정

-- 사용자 계정 생성
CREATE USER 'my_name'@'%' identified WITH mysql_native_password BY 'my_password';

-- 특정 Database에 대한 모든 권한 지급
GRANT ALL PRIVILEGES ON dbname.* TO 'my_name'@'%';

-- Read 권한만 지급
GRANT select ON dbname.* TO 'my_name'@'%';

-- CRUD 권한 지급
GRANT select, insert, update, delete ON dbname.* TO 'my_name'@'%';

-- GRANT 사용후 변경사항 즉시 적용
FLUSH PRIVILEGES;

사용자 계정을 만들고

특정 데이터 베이스에 대한 권한을 지급할 때는 위와 같은 명령어가 자주 사용됩니다.

 

2. safe mode update 해제

SET SQL_SAFE_UPDATES = 0;

-- 실행하고자 하는 쿼리 수행

SET SQL_SAFE_UPDATES = 1;

사용자가 실수로 데이터를 삭제하는 것을 방지하기 위해

Mysql에서는 safe mode가 default로 설정되어 있습니다.

이를 해제하려면 위와 같이 수행 가능합니다.

 

 

3. 테이블 백업

-- 테이블 스키마 복사
CREATE TABLE IF NOT EXISTS `복사할테이블` LIKE `원본테이블`; 

-- 테이블 데이터 복사
INSERT INTO `복사할테이블` SELECT * FROM `원본테이블`;

간혹 테이블을 복사해야 할 때가 있는데

위와 같이 가능합니다.

 

4. 시스템 변수  변경

-- 시스템 변수 wait_timeout 설정
SET wait_timeout=60;

-- 글로벌 시스템 변수 wait_timeout 설정
SET @@GLOBAL.wait_timeout=60;

-- 시스템 변수 중 timeout이 포함된 설정 확인
SHOW VARIABLES LIKE '%time%';

-- 글로벌 시스템 변수 중 timeout이 포함된 설정 확인
SHOW GLOBAL VARIABLES LIKE '%time%';

application 과 테스트 또는 버그 재현을 위해 

wait_timeout을 변경해야 할때가 있습니다. 이 때 시스템 변수를 변경해 재현해 볼 수 있습니다.

 

5. 상태 확인

-- Max_used_connections 상태 확인
SHOW STATUS WHERE `variable_name` = 'Max_used_connections';

-- Threads_connected 상태 확인
SHOW STATUS WHERE `variable_name` = 'Threads_connected';

-- 프로세스 상태 확인
SHOW processlist;

 

 

6. 백업 & 복구

# 백업
/usr/bin/mysqldump -u root -proot testdb > backup.sql

# 복구
mysql -u root -p root testdb < backup.sql

위 케이스는 사용자명: root, 패스워드: root 이고

db: testdb 일 때 예시입니다.