그동안 MySQL을 사용하면서 자주 사용했던 질의를 정리했다.
모니터링
/* 프로세스 보기 */
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 : Lock을 명시적으로 사용한 세션과 모든 세션에서 insert, update, delete가 불가능하고 select만 가능
- WRITE Lock : Lock을 명시적으로 사용한 세션에서의 스레드만 read, write 가능
/* 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
/* View만 보기 */
show full tables where Table_type="VIEW";
Trigger
/* 트리거 보기 (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
이벤트가 활성화되었는지 확인하고, 활성화하는 방법이다.
/* 확인 방법 */
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:
[mysqld]
event_scheduler=on
이벤트를 생성해서 주기적으로 질의나 프로시저를 실행할 수 있다.
DELIMITER $$
CREATE EVENT `dbname`.`eventname`
ON SCHEDULE
EVERY 1 MONTH
STARTS CURRENT_TIMESTAMP
DO
BEGIN
CALL procedurname();
END$$
DELIMITER ;
기타 SQL
/* 버전 확인 */
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'
InnoDB 확인
SHOW VARIABLES LIKE 'have_innodb';
disabled일 경우 설정 파일을 변경해서 InnoDB를 활성화할 수 있다.
my.cnf
# 주석 처리로 InnoDB를 활성화
# skip-innodb
바이너리 로그 지우는 방법
/* 특정 날짜 이전 바이너리 로그 제거 */
PURGE BINARY LOGS BEFORE '2014-07-15 00:00:00';
설정 파일에서 보관기간을 지정할 수 있다.
my.cnf:
expire_logs_days = 7