■ 사용방법 일반적으로 log_bin 파라미터가 ON으로 설정되면 모든 DDL, DML이 로그 파일에 작성되며 사용자가 MySQL세션에 접속할 경우 DDL, DML을 수행할때도 역시 바이너리 로그에 작성됩니다. 그러나 서버에 접속한 세션에서 특정 이유(유지보수 작업이나 기타 설정작업)로 바이너리 로그에 DDL, DML에 작성되지 않게끔 하는게 필요할 수도 있습니다. 이때 사용하는 파라미터가 sql_log_bin입니다. 이 명령어에 대해 사용방법을 알아보겠습니다. 사용방법 바이로그 작성을 금지시킬 경우 mysql> SET sql_log_bin = OFF 바이너리 로그 작성을 원할 경우(기본값) mysql> SET sql_log_bin = ON ■ 제약사항 1. 트랜잭션 또는 하위 쿼리 내에서 sql_log..
■ RESET MASTER 명령어 사용방법 : mysql> RESET MASTER 바이너리 로깅이 활성화된(my.cnf에서 log_bin이 ON인) 서버의 경우 RESET MASTER는 기존의 모든 바이너리 로그 파일을 삭제하고 바이너리 로그 인덱스 파일을 재설정하여 서버를 바이너리 로깅이 시작되기 전의 상태로 재설정합니다. 바이너리 로깅을 다시 시작할 수 있도록 새로운 바이너리 로그 파일이 생성됩니다. GTID가 사용 중인 서버의 경우(gtid_mode가 ON임) RESET MASTER를 실행하면 GTID 실행 기록이 재설정됩니다. gtid_purged 시스템 변수의 값은 빈 문자열('')로 설정되고, gtid_executed 시스템 변수의 글로벌 값(세션 값은 아님)은 빈 문자열로 설정되며, mysq..
MySQL Thread는 접속방법과 아키텍쳐와 관련이 있습니다. MySQL은 스레드 방식의 아키텍쳐를 가지고 있습니다. 유저가 접속하게 되면 Thread가 할당이 되고 유저가 접속을 종료하면 할당했던 스레드를 회수하게 됩니다. 이때 스레드를 할당하고 회수하는 작업이 MySQL 성능에 영향을 많이 미치게 됩니다. 특히 사용량이 많은 시스템에서는 이 스레드 작업이 부담스러울 정도로 영향을 많이 받게 됩니다. 이 접속 스레드에 대한 모니터링 방법 및 튜닝 방법을 알아보겠습니다. ■ Global Status 확인 MySQL Client에서 다음과 같이 확인을 합니다. mysql> show global status like '%Threads%'; 그러면 다음과 같은 시스템 상태의 변수들이 나옵니다. mysql> ..
MySQL 클라이언트를 이용해서 접속을 하면 자신이 현재 SSL/TLS을 이용해서 접속을 했는지 아니면 일반 암호 접속을 했는지 알아내는 방법입니다. my.cnf 의 파라미터를 이용해서 SSL/TLS 접속만을 강제하거나 클라이언트 환경에 따라 일반접속, SSL/TLS접속을 선택해서 접속할 수 있습니다. 혹은 계정을 생성할 때 강제로 TLS/SSL접속만 접속할 수 있게끔 할 수 있습니다. 여기서는 자신의 접속상태가 SSL/TLS인지 일반접속인지 여부에 대해서 알아보고 SSL/TLS 환경설정 및 접속방법에 대해서는 다음 페이지를 참고하시기 바랍니다. https://myinfrabox.tistory.com/14?category=804724 ■ SSL/TLS 접속 여부 확인 mysql클라이언트를 이용해서 접속 ..
■ 카탈로그를 이용하여 각종 정보 조회 ▶︎ 최근 사용이 되었던 table select * from information_schema.TABLES order by UPDATE_TIME desc ; ▶︎ 테이블 생성시간별 조회 select * from information_schema.TABLES where table_schema='database' order by create_time desc ; ▶︎ 활성중인 프로세스 리스트만 확인 select * from information_schema.PROCESSLIST where 1=1 and command 'Sleep' and user='service_user' and info is not null order by time desc; ▶︎ host CONNE..
■ MySQL 메모리 사용률 모니터링에 필요한 기본 내용 1. MySQL은 하나의 프로세스로 동작. 세션은 thread로 확장되고 시스템 자원을 할당. MySQL의 Memory 부분은 Connection 수의 영향을 받는 부분이며 항상 주의를 요함. 2. InnoDB의 기본 페이지 크기는 16KB 컴파일을 통하여 페이지 변경 가능 3. 메모리 파리미터는 세션 파라미터와 글로벌(서버)파라미터로 구분 서버 메모리외에 세션별로 할당되는 메모리가 있기 때문에 컨넥션이 만으면 그만큼 메모리 사용량이 높아짐. 메모리 크기를 잘 산정해야 함 ■ 기본 메모리 설정 1. 성능에 가장 영향을 미치는 buffer pool 크기 시스템 전체의 50% ~ 60%로 잡는다. 파리미터명 : innodb_buffer_pool_siz..
performance schema 스키마 테이블중 events_statements_summary_by_digest는 수행된 쿼리에 대해 실행 이력을 보관하는 테이블입니다. 쿼리가 처음 실행된 날짜부터 마지막으로 수행된 날짜, 테이블 Full scan수행여부, 인덱스 사용여부, 에러횟수, 그리고 수행된 쿼리등등을 볼수 있는 아주 유용한 테이블입니다. 이 테이블에 대해서 유용하게 조회해 볼수 있는 성능 조회 쿼리에 대해 알아봅니다. ■ 활성화 방법 1. performance schema 스키마 활성화 mysql> show variables like 'performance_schema'; +--------------------+-------+ | Variable_name | Value | +----------..
Ubuntu에서 MySQL을 deb패키지로 설치하면 데이터가 저장되는 datadir에 특수한 설정이 자리잡게 됩니다. 바로 apparmor란 것인데 말 그대로 특정 앱에 대해 방어를 하는 목적으로 만들어진 것입니다. 즉 보안성을 높인 기능이라 할 수 있습니다. 좋으면 좋고 귀찮으면 귀찮은 기능입니다. 구글로 검색하면 주로 사용하지 않는 방법이 더 많이 검색되는것을 보면 말이죠. 여기에서는 Ubuntu에서 데이터 디렉토를 바꾸는 방법을 알아봅니다. 기본적으로 아래 환경 설정은 MySQL서버를 내리고 진행합니다. shell> systemctl stop mysqld ■ datadir을 apparmor에 추가하는 방법 다음위치의 파일을 편집하여 신규 datadir을 추가하는 방법입니다. 1. apparmor m..
스토어드 프로그램은 다음과 같은 옵션이 있습니다. Definer ; 스토어드 프로그램이 기본적으로 가지는 옵션. 스토어드 프로그램을 작성한 유저. 프로그램이 실행될 때의 권한 SQL Security 옵션 : 프로그램을 실행할 때 누구의 권한으로 실행할지 결정하는 옵션. Invoker 또는 Definer둘중에 하나로 선택. definer는 스토어드 프로그램을 생성한 사용자를 의미하며 Invoker는 해당 프로그램을 호출한 사용자. 아래와같이 definer가 creator@%으로 생성된 스토어드 프로그램을 executer@% 사용자가 실행한다고 가정합니다 . SQL SECURITY가 INVOKER와 DEFINER일때 이 스토어드 프로그램이 어느 사용자의 권한으로 실행되는지 나타내는 표입니다. SQL SECU..
MySQL에서 Function과 Procedure를 합쳐서 Routine이라고 부릅니다. 이 Routine들은 MySQL내에서 좀 특별히 취급됩니다. 아래 내용에서 좀더 자세하게 확인해 봅니다. 1. Definer Routine은 생성될 때 Definer라는 특별한 내용이 추가가 됩니다. 예제를 통해 확인해 봅니다. [예제] 보통 아래와 같이 프로시저를 만듭니다. CREATE PROCEDURE `sbtest`.`proc1`() BEGIN select * from sbtest.sbtest1; select * from sbtest.sbtest2; END 그리고 그 소스를 확인해 보면 다음과 같이 Definer라는 내용이 추가됩니다. CREATE DEFINER=`jhlee`@`%` PROCEDURE `sbte..
RDS는 AWS에 제공하는 Database 서비스입니다. Oracle, MS-SQL, MySQL, MariaDB, PostgreSQL Database가 제공됩니다. 다양한 스펙의 인스턴스가 제공되며 엔진도 일반적으로 가장 많이 사용되는 DBMS가 제공됩니다. 원하는 사양의 스펙으로 생성하여 사용하면 됩니다. ■ RDS 생성방법 RDS를 생성하는 순서는 Subnet Group 생성, Parameter Group 생성 후 RDS를 생성하는 것입니다. Subnet Group, Parameter Group의 생성 순서는 상관없습니다. 먼저 AWS Console에 로그인 후 RDS를 클릭합니다. 파라미터 그룹을 생성합니다. 아래 파라미터 그룹을 클릭합니다. 파라미터 그룹을에 사용될 엔진을 선택합니다. 여기서는 M..
■ Procedure 및 Function에서 User와 Execute의 관계 ▶︎ Execute 권한관련 보통 Function과 Procedure를 생성하면 세션에 접속되어 있는 User에 IP를 이용해서 Definer라는 정보를 생성하게 됩니다. 그런데 이때 조심해야 할 것이 있습니다. Definer가 EXECUTE권한이 없으면 Procedure나 Function 생성권한이 있더라도 수행이 되지 않습니다. 즉 Create권한만 있으면 자신이 생성했다 하더라도 Procedure나 Function을 수행할 수 없는 것입니다. ▶︎ Definer 및 생성 User 존재 여부 Function이나 Procedure를 만든 User가 반드시 존재해야 합니다. 예를 들어 user1@IP를 가지는 User가 특정 P..