모니터링

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가 가능.

/* 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