MySQL 자주 사용하는 질의 모음과 관리 팁

/* 프로세스 보기 */
SHOW PROCESSLIST

/* 좀 더 자세히 프로세스 보기 */
SHOW FULL PROCESSLIST

/* 서버 상태 보기 */
SHOW STATUS

/* 현재 세션 변수들 */
SHOW SESSION VARIABLES 

/* Global 변수들 */
SHOW GLOBAL VARIABLES 

/* 프로세스 Kill */
kill 프로세스ID

/* 열린 테이블 확인 */
SHOW OPEN TABLES FROM dbname\G

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

/* 이미 존재하는 테이블에 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
);

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

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

/* 트리거 보기 (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 ;

/* 확인 방법 */
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;

[mysqld]
event_scheduler=on

DELIMITER $$

CREATE  EVENT `dbname`.`eventname`

ON SCHEDULE
    EVERY 1 MONTH
    STARTS CURRENT_TIMESTAMP
DO
    BEGIN
        CALL procedurname();
    END$$

DELIMITER ;

/* 버전 확인 */
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'

SHOW VARIABLES LIKE 'have_innodb';

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

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

expire_logs_days = 7