MariaDB(mysql) DBA SQL

1. 최대 접속자 수 
show variables like '%max_connect%';

2. 현재 접속자 수 
show global status like 'threads_connected';

3. 현재 존재하는 데이터베이스 목록 
show databases;

4. 특정 데이타베이스를 사용 
use {databases_name};

5. 현재 사용중인 데이터베이스 테이블 목록 
show tables;

6. 테이블 생성 쿼리 보기 
show create table {table_name};

7. 테이블 컬럼 목록 보기 
select column_name from information_schema.columns where table_name = '{table_name}' and table_schema='{database_name}'

8. 테이블 index 목록 보기 
show index from {table_name}

9. 현재 사용중인 데이터베이스 인덱스 목록 
select distinct table_name, index_name from information_schema.statistics where table_schema = '{database_name}';

10. TABLE Comment 확인 
select 
table_name, table_comment 
from information_schema.tables 
where 
table_schema = '{database_name}'

11. TABLE 내 Column Comment 확인
selecttable_name, column_name,column_commentfrom information_schema.columnswheretable_schema = '{database_name}'

12. MySQL 버전확인
SELECT version();

13. 테이블이 존재하면 삭제, 존재하지않으면 경고만 발생(테이블이 없다고해서 에러를 발생시키지 않음)
DROP TABLE IF EXISTS table_name;

14. 기본키가 중복되는 데이터가 존재하면 뒷부분 update 실행, 존재하지않을경우 앞부분 insert 실행
INSERT INTO table_name(col1, col2) VALUES('a', 'b') ON DUPLICATE KEY UPDATE col1 = 'a';

-- ignore : 중복 시 무시
insert ignore into mmtest.wam_ddl_tbl        select * from mmtest_onl.wam_ddl_tbl      where nat_cd = 'KOR' ; 

15. 테이블명칭 조회
SHOW TABLES LIKE '%table_name%';

16. 테이블 컬럼 명 변경
ALTER TABLE table_name CHANGE before_column_name after_column_name int;
-- 컬럼 삭제
ALTER TABLE table_name DROP COLUMN column_name ;

17. 인덱스 생성
ALTER TABLE {table_name} ADD INDEX {index_name}({index_column_name});
CREATE INDEX {index_name} USING BTREE ON [{database}.]{index_name}({index_column_name});

18. 인덱스 삭제
ALTER TABLE {table_name} DROP INDEX {index_name};

19. 이블 구조 보기DESC table_name;

20. 테이블 구조 보기(DDL)
SHOW CREATE TABLE table_name;


21. 페이징
SELECT * 
FROM ( 
    SELECT @ROWNUM := @ROWNUM + 1 as rnum, SEQ, id, COMMENT, AMOUNT, RDATE 
    FROM RHY_MEM_POINT, (SELECT @ROWNUM :=0) A 
    ORDER BY RDATE DESC 
    ) RESULT 
LIMIT 10, 10

22. 날짜 
-- 날짜 포맷 설정(yyyy-mm-dd) 
DATE_FORMAT(RDATE,'%Y-%m-%d') 
  
-- 날짜 더하기 빼기 
DATE_ADD(NOW() , INTERVAL 12 DAY),'%Y-%m-%d' 
DATE_ADD(NOW() , INTERVAL 12 MONTH),'%Y-%m-%d' 
DATE_ADD(NOW() , INTERVAL 12 YEAR),'%Y-%m-%d' 
DATE_ADD(NOW() , INTERVAL -12 YEAR),'%Y-%m-%d' 
  
-- 현재 날짜와 비교 
SELECT IF( DATE_FORMAT(DATE_ADD(RDATE, INTERVAL 12 MONTH),'%Y-%m-%d') < CURDATE(), 'true', 'false')


23. 프로세스 보기 
SHOW PROCESSLIST 
  
24. 좀 더 자세히 프로세스 보기 
SHOW FULL PROCESSLIST 
  
25. 서버 상태 보기 
SHOW STATUS 
  
26. 현재 세션 변수들
SHOW SESSION VARIABLES 
  
27. Global 변수들 
SHOW GLOBAL VARIABLES 
  
28. 프로세스 Kill(session kill)
show processlist;

kill 'id';


  
29. 열린 테이블 확인 
SHOW OPEN TABLES FROM dbname\G


30. 락(LOCK, 주석은 Oracle PL/SQL)

select * from information_schema.innodb_lock_waits;
select * from information_schema.innodb_locks;
select * from information_schema.innodb_trx\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
/* Read Lock */ lock tables table_name READ; 

31. 설정SET 
GLOBAL concurrent_insert = 'AUTO'; 
SET SESSION concurrent_insert = 'AUTO'; 
  
concurrent_insert = AUTO 
concurrent_insert = "AUTO"

32. 파티션
/* 이미 존재하는 테이블에 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 
);
  
/* 파티션 삭제 */ 
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 
);


33. View만 보기 
show full tables where Table_type="VIEW";

34. 트리거 보기 (use dbname 이후)
show triggers

35. 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 ;

36. 이벤트가 활성화되었는지 확인하고, 활성화하는 방법이다.
/* 확인 방법 */ 
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 ;


37. 버전 확인
SHOW VARIABLES LIKE "%version%";

38. KST를 UTC로 변경
SELECT CONVERT_TZ('2014-09-25 09:49:31', '+09:00','+00:00')

39.  최대 사용가능한 연결수 확인
SHOW STATUS WHERE variable_name='Max_used_connections';

40. 사용자 추가
CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY '비밀번호';   
GRANT ALL PRIVILEGES ON *.* TO root@127.0.0.1 WITH GRANT OPTION;

41. 사용자 비밀번호 변경 
SET PASSWORD FOR root@localhost=PASSWORD('새비밀번호'); 
FLUSH PRIVILEGES;

42. 깨진 테이블 복구
repair table `tablename`;

43. 파일로부터 데이터 불러오는 명령
LOAD DATA INFILE 'a.csv' INTO TABLE `tablename` 
FIELDS TERMINATED BY ','  ENCLOSED BY '\"' ESCAPED BY '\\';

44. table의 모든 컬럼 보여주기
SHOW FULL COLUMNS FROM `tablename`

45. 특정일자 이전인 경우 삭제
DELETE FROM `tablename` WHERE date_field < '2013-01-01 00:00:00'

46. inno db
SHOW VARIABLES LIKE 'have_innodb';

   -> disabled일 경우 설정파일을 변경해서 innodb를 활성화할 수 있다.
      my.cnf# 주석 처리로 innodb를 활성화 
        # skip-innodb
        
47. 바이너리 로그 보기 및 제거하기

show binary logs;

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

/* 특정 binary 파일 이전까지 제거(아래 명령어느 119 이전인 118까지 삭제) */ 
purge binary logs to 'mysql-bin.000119';


설정파일에서 보관기간을 지정할 수 있다.
    my.cnf:
       expire_logs_days = 7 
       
48. 최대 접속자 수 
show variables like '%max_connect%';

49. 현재 접속자 수 
show global status like 'threads_connected';

50. 현재 존재하는 데이터베이스 목록 
show databases;

51. 특정 데이타베이스를 사용 
use {databases_name};

52. 현재 사용중인 데이터베이스 테이블 목록 
show tables;

53. 테이블 생성 쿼리 보기 
show create table {table_name};

54. 테이블 컬럼 목록 보기 
select column_name from information_schema.columns where table_name = '{table_name}' and table_schema='{database_name}'

55. 테이블 index 목록 보기 
show index from {table_name}

56. 현재 사용중인 데이터베이스 인덱스 목록 
select distinct table_name, index_name from information_schema.statistics where table_schema = '{database_name}';

57. TABLE Comment 확인 
selecttable_name, table_commentfrom information_schema.tableswheretable_schema = '{database_name}'

58. TABLE 내 Column Comment 확인 
selecttable_name, column_name,column_commentfrom information_schema.columnswheretable_schema = '{database_name}'

59. sql파일로 mysql 수행 하는 방법
mysql -u maria -S $MARIA_SOCK < show_binary_log.sql

60. constraint 확인
select table_name, column_name, constraint_name, referenced_table_name, referenced_column_name
from information_schema.key_column_usage
where 1=1
--and referenced_column_name is not null
and table_schema = 'mmtest'
and table_name = 'WAM_DMN'
;

61. 한 데이터베이스의 모든 DDL 수행하는 SQL 만들기
mysqldump -d -u maria -S $MARIA_SOCK mmtest > meta_ddl_20200820.sql

62. federated(oracle의 dblink)
show engines;
install plugin federated soname 'ha_federated.so';


63. 컬럼 1줄로 만들기(Oracle의 listagg기능)

select group_concat({나열할 column} SEPEPARATOR ',')
from {테이블}
[where ~~]

(ex) 테이블의 컬럼 리스트 뽑기
select  table_name, GROUP_CONCAT(column_name order by column_name SEPARATOR ',')
from information_schema.columns 
WHERE table_schema='mmtest'
AND table_name IN ('waa_dmng',
'waa_std_rsvd_seq'
)
GROUP BY table_name
;

64. phsical backup

--full backup
mariabackup --backup --target-dir=/arch/MARIA_01/full --user=maria -S $MARIA_SOCK --databases=mmtest (--password=Shinhan12#)

--compress
mariabackup --backup --target-dir=/arch/MARIA_01/full --user=maria -S $MARIA_SOCK --databases=mmtest --stream=xbstream | gzip > $MARIA_BACKUP/MARIA_01/full/mariabackup/mmtest_20201112.gz

--incremental backup(after full backup)
mkdir /arch/MARIA_01/incre20201101
mariabackup --backup --target-dir=/arch/MARIA_01/full --user=maria -S $MARIA_SOCK --databases=mmtest --incremental-basedir=/arch/MARIA_01/incre20201101
mkdir /arch/MARIA_01/incre20201102
mariabackup --backup --target-dir=/arch/MARIA_01/full --user=maria -S $MARIA_SOCK --databases=mmtest  --incremental-basedir=/arch/MARIA_01/incre20201102
mkdir /arch/MARIA_01/incre20201103
mariabackup --backup --target-dir=/arch/MARIA_01/full --user=maria -S $MARIA_SOCK --databases=mmtest  --incremental-basedir=/arch/MARIA_01/incre20201103
mkdir /arch/MARIA_01/incre20201104
mariabackup --backup --target-dir=/arch/MARIA_01/full --user=maria -S $MARIA_SOCK --databases=mmtest  --incremental-basedir=/arch/MARIA_01/incre20201104


--prepare restore when doing incremetnal backup
mariabackup --prepare --target-dir=/arch/MARIA_01/full --user=maria -S $MARIA_SOCK --databases=mmtest  --incremental-basedir=/arch/MARIA_01/incre20201101
mariabackup --prepare --target-dir=/arch/MARIA_01/full --user=maria -S $MARIA_SOCK --databases=mmtest  --incremental-basedir=/arch/MARIA_01/incre20201102
mariabackup --prepare --target-dir=/arch/MARIA_01/full --user=maria -S $MARIA_SOCK --databases=mmtest  --incremental-basedir=/arch/MARIA_01/incre20201103
mariabackup --prepare --target-dir=/arch/MARIA_01/full --user=maria -S $MARIA_SOCK --databases=mmtest  --incremental-basedir=/arch/MARIA_01/incre20201104

--decompress
gunzip -c $MARIA_BACKUP/MARIA_01/full/mariabackup/mmtest_20201112.gz | mbstream -x  -C $MARIA_BACKUP/MARIA_01/full/mariabackup/mmtest_001


--restore
1) stop mariadb server process
2) empty datadir
3) mariabackup --copy-back --target-dir=/arch/MARIA_01/full -S $MARIA_SOCK --databases=mmtest 
4) chown -R maria:dba /arch/MARIA_01/full

65. logical backup
mysqldump --user=maria -S $MARIA_SOCK mmtest | gzip -c -9 > /arch/MARIA_01/full/mmtest_gzip_comp_`date +%Y%m%d%H%M`.sql.gz
mysqldump --user=maria -S $MARIA_SOCK mmtest | xz -c -9 > /arch/MARIA_01/full/mmtest_xz_comp_`date +%Y%m%d%H%M`.sql.xz

66. 통계정보생성

analyze table {table_name}
analyze table {table_name} persistent for columns({column},{column}) indexes ({index_name});

67. innodb deadlock 확인
show engine status innodb;
select * from performance_schema.data_lock_waits;

68. sleep
--sleep(second);
select sleep(5.5); 



cf) like Oracle rownum

-- 1.
SELECT *
FROM 
(
SELECT row_number() over() AS rowno, A.*
FROM mmtest.wam_cust_info A
) B
WHERE B.rowno <= 10
AND b.nat_cd != 'KOR'
;

-- 2.
SELECT row_number() over() AS rowno, A.*
FROM mmtest.wam_cust_info A
limit 10
;