MySQL 자주 사용하는 쿼리 모음과 관리 팁(링크)

설명글을 너무 잘정리해놓은 곳이 있어 관련 내용 공유 합니다.

링크 : http://blog.kichul.co.kr/2017/03/13/2017-03-13-mysql-note/

모니터링

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

/* 프로세스 보기 */ SHOW PROCESSLIST   /* 좀 더 자세히 프로세스 보기 */ SHOW FULL PROCESSLIST   /* 서버 상태 보기 */ SHOW STATUS   /* 현재 세션 변수들 */ SHOW SESSION VARIABLES   /* Global 변수들 */ SHOW GLOBAL VARIABLES   /* 프로세스 Kill */ kill 프로세스ID   /* 열린 테이블 확인 */ SHOW OPEN TABLES FROM dbname\G

Lock

• READ Lock : 락을 명시적으로 사용한 세션과 모든 세션에서 insert, update, delete가 불가능하고 select만 가능
• WRITE Lock : 락을 명시적으로 사용한 세션에서의 쓰레드만 read, wrtite가 가능.

Oracle PL/SQL

CTRL+C로 복사, CTRL+V로 붙여넣기

/* Read Lock */ lock tables table_name READ; /* Write Lock */ lock tables table_name WRITE; /* 여러개도 가능함. */ lock tables table_name WRITE, table_name2 READ; /* 락 걸려있을 때 해제 */ UNLOCK TABLES; /* Global Lock */ FLUSH TABLES WITH READ LOCK; /* Innodb Dead Lock 확인 */ SHOW innodb status show engine innodb status

 

 

설정

SET GLOBAL concurrent_insert = 'AUTO'; SET SESSION concurrent_insert = 'AUTO'; concurrent_insert = AUTO concurrent_insert = "AUTO"

파티션

CTRL+C로 복사, CTRL+V로 붙여넣기

/* 이미 존재하는 테이블에 LIST 형태로 파티션 등록 */ ALTER table `tablename` PARTITION BY LIST (dt) ( PARTITION p20140409 VALUES IN (20140409), PARTITION p20140410 VALUES IN (20140410) ) /* 이미 존재하는 테이블에 RANGE 형태로 파티션 등록(datetime 필드를 사용하는 경우) */ ALTER TABLE `tablename` PARTITION BY RANGE (UNIX_TIMESTAMP(stamp_inserted)) ( PARTITION p2014082620 VALUES LESS THAN (1365990900), PARTITION p2014082621 VALUES LESS THAN (1365991801) ); /* 이미 파티션이 정의된 테이블에 파티션 추가 */ ALTER TABLE `tablename` ADD PARTITION ( PARTITION p20140423 VALUES IN (20140423) ); /* 파티션 존재 여부 확인 */ SELECT * FROM information_schema.partitions WHERE table_name='tablename' /* 파티션 삭제 */ ALTER TABLE tablename DROP PARTITION partition_name; /* 파티션 재배치. p201410 파티션이 있는 상태에서, 분리한다. */ ALTER TABLE `tablename` REORGANIZE PARTITION p201410 INTO ( PARTITION p201409 VALUES LESS THAN (5415777), PARTITION p201410 VALUES LESS THAN MAXVALUE );

사용자 추가

CTRL+C로 복사, CTRL+V로 붙여넣기

CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY 'password'; grant all privileges on *.* to root@127.0.0.1 with grant option;

1 2	CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY 'password';   grant all privileges on *.* to root@127.0.0.1 with grant option;

View

CTRL+C로 복사, CTRL+V로 붙여넣기

/* View만 보기 */ show full tables where Table_type="VIEW";

1 2	/* View만 보기 */ show full tables where Table_type="VIEW";

Trigger

CTRL+C로 복사, CTRL+V로 붙여넣기

/* 트리거 보기 (use dbname 이후) */ show triggers /* table_a에 INSERT될 때 table_b에 INSERT하는 트리거 등록 */ DELIMITER $$ CREATE TRIGGER `dbname`.`table_a_AFTER_INSERT` AFTER INSERT on `table_a` FOR EACH ROW BEGIN INSERT INTO `dbname`.`table_b` set id = NEW.id; END$$ DELIMITER ; /* table_a가 삭제될 때 table_b의 레코드를 삭제하는 트리거 등록 */ DELIMITER $$ CREATE TRIGGER `dbname`.`table_a_AFTER_DELETE` AFTER DELETE on `dbname`.`table_a` FOR EACH ROW BEGIN DELETE FROM `dbname`.`table_b` WHERE id = old.id; END$$ DELIMITER ;

Event

이벤트가 활성화되었는지 확인하고, 활성화하는 방법이다.

CTRL+C로 복사, CTRL+V로 붙여넣기

/* 확인 방법 */ SHOW VARIABLES LIKE "%event%"; /* Event 활성화 */ SET GLOBAL event_scheduler = on; SET @@global.event_scheduler = on; SET GLOBAL event_scheduler = 1; SET @@global.event_scheduler = 1;

위 방법은 임시적인 방법이라서 설정 파일을 수정해야 한다.

my.ini:

CTRL+C로 복사, CTRL+V로 붙여넣기

[mysqld] event_scheduler=on

이벤트를 생성해서 주기적으로 쿼리나 프로시저를 실행할 수 있다.

CTRL+C로 복사, CTRL+V로 붙여넣기

DELIMITER $$ CREATE EVENT `dbname`.`eventname` on SCHEDULE EVERY 1 MONTH STARTS CURRENT_TIMESTAMP DO BEGIN CALL procedurname(); END$$ DELIMITER ;

기타 SQL

CTRL+C로 복사, CTRL+V로 붙여넣기

/* 버전 확인 */ SHOW VARIABLES LIKE "%version%"; /* KST를 UTC로 변경 */ SELECT CONVERT_TZ('2014-09-25 09:49:31', '+09:00','+00:00') /* 최대 사용가능한 연결수 확인 */ SHOW STATUS WHERE variable_name='Max_used_connections'; /* 사용자 추가 */ CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY '비밀번호'; GRANT ALL PRIVILEGES on *.* TO root@127.0.0.1 WITH GRANT OPTION; /* 사용자 비밀번호 변경 */ SET PASSWORD FOR root@localhost=PASSWORD('새비밀번호'); FLUSH PRIVILEGES; /* 깨진 테이블 복구 */ repair table `tablename`; /* 파일로부터 데이터 불러오는 명령 */ LOAD DATA INFILE 'a.csv' INTO TABLE `tablename` FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\'; /* table의 모든 컬럼 보여주기 */ SHOW FULL COLUMNS FROM `tablename` /* 특정일자 이전인 경우 삭제 */ DELETE FROM `tablename` WHERE date_field < '2013-01-01 00:00:00'

Inno DB 확인

1	SHOW VARIABLES LIKE 'have_innodb';

disabled일 경우 설정파일을 변경해서 innodb를 활성화할 수 있다.

my.cnf

1 2	# 주석 처리로 innodb를 활성화 # skip-innodb

바이너리 로그 지우는 방법

1 2	/* 특정 날짜 이전 바이너리 로그 제거 */ PURGE BINARY LOGS BEFORE '2014-07-15 00:00:00';

설정파일에서 보관기간을 지정할 수 있다.

my.cnf:

1	expire_logs_days = 7