(─━┘_└━─)/ 머리만1톤(Head1Ton)

데이터를 풀백업을 11시에 했을 경우 11시 30분에 장애가 나면 Oracle이나 MSSQL 같은 경우

redo log나 트랜잭션 로그를 사용하여 데이터를 복원합니다.

그리고 mysql에서도 undo/redo log를 사용하여 시작할 때 데이터를 복구가 가능합니다.

(그런 장애가 난 적이 없고 해당하는 부분은 저도 조금 더 공부를 해야봐 알겠네요.

 일단 여러 참고자료에서는 가능하다고 적혀 있어서 적어 놓았습니다.)

그런데 11시에 풀백업이 되고 11시 30분에 장애가 나거나 실수로 쿼리를 잘못 날렸을 경우 

실수로 실행한 쿼리문 이전으로 복구하는 방법을 알려 드리고자 합니다.

저는 이렇게 해서 해당 DATA를 복구하였습니다.

해당하는 기술은 엄청나게 유용하게 사용됩니다.

나중에 장애나 쿼리를 잘못 실행했을 때 사용해 보실 수 있으실 겁니다.

http://blog.cafe24.com/1659

MySQL 은 테이블 생성시 또는 테이블 컬럼의 제약 사항에 따라

ERROR 를 내어야 하는 statement (syntax error는 아니며 제약 사항에 위배되는 statement 등 ) 에 대해서 sql_mode 가 어떻게 설정되느냐에 따라서 그냥 warning 만 내고

Statement 를 완료 시킵니다.

예를 들어 default 가 설정되지 않은 not null 컬럼에 아무 값도 넣지 않으면… 빈 문자열을 넣고 ( mysql 에서 빈문자열 ‘’ 이 null 이 아니네요… 오라클에선 그런 것  같은데 기억이 가물 )

Warning 만 하고 statement를 완료 시킵니다.                                                                                                                           

컬럼이 int 형이면 0 을 넣어버리는 등  default 값을 임의대로 넣어버리고 statement를 완료 시킵니다.

대부분의 개발이 이런 특성을 고려해서 개발 되는 것이 아니기 때문에 statement에 실수가 발생해도 그대로 진행되어서

나중에 의도 하지 않은 결과들을 볼 수 있는 위험이 있습니다.

관련 sql_mode 옵션 : STRICT_TRANS_TABLES

또 한 가지 테이블 생성시 엔진명이 틀리거나 명시하지 않으면 그냥 default storage engine으로 설정해서 테이블 생성해 버립니다.

Warning 제대로 안 보다간 Error도 안나니 전혀 의도 하지 않은 엔진으로 테이블 생성하고 나중에 문제가 될 수 있습니다.

그 외에도 여러 가지가 있는데 위 두 가지가 중요한 것 같습니다.

그래서 5.6 부터는 default my.cnf my.ini 파일에 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 로 기본 설정하고 있습니다.  

그리고 MySQL 에서 warning 확인은 절대적이네요..

Warning 발생시 꼭 확인 필요 ^^;;

https://blogs.oracle.com/supportingmysql/entry/mysql_server_5_6_default

http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html

• STRICT_TRANS_TABLES

If a value could not be inserted as given into a transactional table, abort the statement. For a nontransactional table, abort the statement if the value occurs in a single-row statement or the first row of a multiple-row statement. More detail is given later in this section.

• NO_ENGINE_SUBSTITUTION

Control automatic substitution of the default storage engine when a statement such as CREATE TABLE or ALTER TABLEspecifies a storage engine that is disabled or not compiled in.

Because storage engines can be pluggable at runtime, unavailable engines are treated the same way:

With NO_ENGINE_SUBSTITUTION disabled, for CREATE TABLE the default engine is used and a warning occurs if the desired engine is unavailable. For ALTER TABLE, a warning occurs and the table is not altered.

With NO_ENGINE_SUBSTITUTION enabled, an error occurs and the table is not created or altered if the desired engine is unavailable.

테스트 )

1. Insert 될 수 없는 statement를 임의 처리 하는 CASE

create table test_strict(

id bigint(20) not null auto_increment,

a varchar(50) not null,

b varchar(50) not null,

primary key (id)

)engine=innodb default charset=utf8;

insert into test_strict(id,a)values(1,'a');

insert into test_strict(id,a)values(2,'c');

mysql> select @@sql_mode;

+------------+

| @@sql_mode |

+------------+

|            |

+------------+

1 row in set (0.00 sec)

mysql> create table test_strict(

    -> id bigint(20) not null auto_increment,

    -> a varchar(50) not null,

    -> b varchar(50) not null,

    -> primary key (id)

    -> )engine=innodb default charset=utf8;

Query OK, 0 rows affected (0.05 sec)

mysql> insert into test_strict(id,a)values(1,'a');   ß b가 not null 인데도 error 없이 statement 완료 됨. Warning 만

Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from test_strict;

+----+---+---+

| id | a | b |

+----+---+---+

|  1 | a |   |

+----+---+---+

1 row in set (0.00 sec)

mysql> select b from test_strict;

+---+

| b |

+---+

|   |

+---+

1 row in set (0.00 sec)

mysql> set @@sql_mode = 'strict_trans_tables';

Query OK, 0 rows affected (0.00 sec)

mysql>

mysql>

mysql> insert into test_strict(id,a)values(2,'c');

ERROR 1364 (HY000): Field 'b' doesn't have a default value  ß sql_mode = strict_trans_tables 설정시 error return

create table test_strict(

id bigint(20) not null auto_increment,

a varchar(50) not null,

b varchar(50) not null,

c int not null,

primary key (id)

)engine=innodb default charset=utf8;

mysql> set @@sql_mode='';

Query OK, 0 rows affected (0.00 sec)

mysql> drop table test_strict;

Query OK, 0 rows affected (0.00 sec)

mysql> create table test_strict(

    -> id bigint(20) not null auto_increment,

    -> a varchar(50) not null,

    -> b varchar(50) not null,

    -> c int not null,

    -> primary key (id)

    -> )engine=innodb default charset=utf8;

Query OK, 0 rows affected (0.03 sec)

mysql> insert into test_strict(id,a)values(1,'a');

Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> select * from test_strict;

+----+---+---+---+

| id | a | b | c |

+----+---+---+---+

|  1 | a |   | 0 | ß--- int의 경우 0으로 설정

+----+---+---+---+

1 row in set (0.00 sec)

mysql> insert into test_strict(id,a)values(2,'c');

Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql> show warnings\G

*************************** 1. row ***************************

  Level: Warning

   Code: 1364

Message: Field 'b' doesn't have a default value

*************************** 2. row ***************************

  Level: Warning

   Code: 1364

Message: Field 'c' doesn't have a default value

2 rows in set (0.00 sec)

2. Create table storage engine 지정이 잘못 되었을시 default engine으로 대체

create table test_engine(f1 int) engine = kpt;

mysql> create table test_engine(f1 int) engine = kpt;

Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> show warnings\G

*************************** 1. row ***************************

  Level: Warning

   Code: 1286

Message: Unknown storage engine 'kpt'

*************************** 2. row ***************************

  Level: Warning

   Code: 1266

Message: Using storage engine InnoDB for table 'test_engine'

2 rows in set (0.00 sec)

mysql> show create table test_engine;

+-------------+-----------------------------------------------------------------------------------------------+

| Table       | Create Table                                                                                  |

+-------------+-----------------------------------------------------------------------------------------------+

| test_engine | CREATE TABLE `test_engine` (

  `f1` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-------------+-----------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> show global variables like '%default%';

+------------------------+--------+

| Variable_name          | Value  |

+------------------------+--------+

| default_storage_engine | InnoDB |

| default_week_format    | 0      |

+------------------------+--------+

2 rows in set (0.00 sec)

Ubuntu에 깔려있는 MySQL을 업그레이드했는데, 뭔가 잘못됐는지 다음과 같은 메시지와 함께 실행이 안되었다.

error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'

데비안이나 우분투에서 MySQL을 관리하다보면 debian-sys-maint계정이 접근권한이 없어서 mysql데몬을 관리하는 것이 안되는 경우가 발생한다. 이것은 /etc/init.d/mysql스크립트를 start,stop,restart등등을 할때 /etc/mysql/debian.cnf의 debian-sys-maint게정과 암호를 사용하기 때문이다. 
그런데 debian.cnf파일의 password부분이 encryption이 된것으로 알았는데 그냥 clear-text라고 한다... 
따라서, debian-sys-maint계정의 암호를 잃어버렸을 경우에는 이 파일의 password부분에 임의의 랜덤 문자열로 암호를 넣어주고 아래와 같이 mysql에서 debian-sys-maint에 대한 암호를 변경한다.

mysql> update user set password = password('랜덤스트링') where user = 'debian-sys-maint' and host = 'localhost'; 
mysql> flush privileges;

안녕하세요.

오늘은 (제 스타일의) ERD 작성시 Table / Column Comment 를 추출하는 쿼리 공유드리고자 합니다.

저희가 지원하는 서비스의 경우, Life Time 이 너무 짧아 ERD 를 꾸준히 관리하지는 못하지만,

초기 서비스를 시작할 때 관련담당자들(DB, DEV, DW, 사업PM)의 이해도를 높이기 위해 ERD 를 그리곤 합니다.

경험상 대부분의 모델링 도구들이 '한글' 또는 '영문'명만 표시해주면서 실제 코딩시 활용도가 낮은데,

"영문컬럼명 [한글정의]" 형태로 ERD 를 그림으로써 다소나마 가시성을 높여 사용하고 있습니다.

(생각해보니 DA# 에서 이런 형태로 보여주는 기능이 있네요. Padding 이 안되어 이쁘진 않지만... ㅎㅎ)

■ ERD 예제

■ 추출쿼리

=====================================
쿼리 Type
=====================================

select concat(rpad(column_name, a.start_no, ' '),'[',trim(b.column_comment),']') as col_desc
     , b.table_schema, b.table_name, b.column_name, b.column_comment, a.start_no
  from (select table_schema, table_name, max(length(column_name))+2 as start_no
          from information_schema.columns
         where table_schema not in ('information_schema','common_schema','mysql','performance_schema','ps_helper')
         group by table_schema, table_name) a
      , information_schema.columns b
 where a.table_schema = b.table_schema
   and a.table_name   = b.table_name
 order by b.table_schema, b.table_catalog, b.table_schema, b.table_name, b.ordinal_position;

show full columns from information_schema.columns;

=====================================
프로시져 Type
=====================================

use test;

DROP PROCEDURE IF EXISTS usp_show_table_desc;

delimiter //

CREATE PROCEDURE usp_show_table_desc()
BEGIN

   DECLARE done           INT DEFAULT 0;

   DECLARE v_table_schema VARCHAR(50);
   DECLARE v_table_name   VARCHAR(50);
   DECLARE v_max_length   INT;

   DECLARE cur1 CURSOR
   FOR 
   select table_schema, table_name, max(length(column_name))+2 as max_length
     from information_schema.columns
    where table_schema not in ('information_schema','common_schema','mysql','performance_schema','ps_helper')
    group by table_schema, table_name
    order by table_schema, table_name;

   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

   OPEN cur1;

   read_loop:
   LOOP

      FETCH cur1 INTO v_table_schema, v_table_name, v_max_length;

      -- select v_table_schema, v_table_name, v_max_length;

      select "===============================================" as "___comments___"
       union
      select concat(table_name, ' [',trim(table_comment),']')  as "___comments___"
        from information_schema.tables
       where table_schema = v_table_schema
         and table_name   = v_table_name
       union all
      select "==============================================="
       union all
      select concat(rpad(column_name, v_max_length, ' '),'[',trim(column_comment),']') as col_desc
        from (
             select column_name, column_comment
               from information_schema.columns
              where table_schema = v_table_schema
                and table_name   = v_table_name
              order by ordinal_position
             ) x
       union all
      select "==============================================="
       union all
      select ""
       union all
      select "";

      IF done = 1 THEN
         LEAVE read_loop;
      END IF;

   END LOOP;

   CLOSE cur1;

END
//

delimiter ;

call usp_show_table_desc();

-- shell 에서 실행

mysql -u root -p --skip-column-names -e "use test; call usp_show_table_desc();" > tab_col_desc.txt

-- Output 확인

vi tab_col_desc.txt
-------------------------------------------------------------------
...
===============================================
user_stage [유저별 스테이지 정보]
===============================================
usr_id        [유저 고유번호]
stage_id      [스테이지 고유번호]
stage_level   [스테이지 현재등급]
wins          [(누적) 승수]
kills         [(누적) 킬수]
===============================================
...
-------------------------------------------------------------------

innodb tablespace structure.pptx

시스템을 구성하고 시스템의 IO 상황이 어떤지 확인할 필요가 있습니다. 특히, DBMS와 같이 IO에 의존하는 시스템은 반드시!!! 시스템의 IO 현황을 확인해야 합니다. IO 현황을 확인할 때 사용하는 iostat의 출력값을 해석하는 방법을 서술해 봅니다.

아래의 예와 초록색 표시한 부분을 따라서 읽어보면 됩니다. 그리고 일반적인 해결 방법도 제일 아래에 붙입니다.

%util은 현재 응용 프로그램이 얼마나 I/O에 연관된 것인가를 보여주는 지표입니다. 아래 식으로 계산합니다.

%util = ((Write operation 개수 + Read operation 개수) * 평균 처리 시간)  / 측정 interval

예:
Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
dm-0              0.00     0.00  611.40  414.23 20286.60  1656.93 42.79    17.50   17.33   0.96  98.57

• Device: the block device whose performance counters are being reported
• r/s and w/s: number of read and write requests issued per second to the device (in this case 611 and 414)
• rsec/s and wsec/s – number of sectors read/written per second
• rkB/s and wkB/s – number of kilobytes read/written per second
• avgrq-sz – average number of sectors per request (for both reads and writes). ie (rsec + wsec) / (r + w)
• avgqu-sz – average queue length in the monitoring interval (in this case 42.79)
• await – average time that each IO Request took to complete. This includes the time that the request was waiting in the queue and the time that the request took to be serviced by the device
• svctim – average time each IO request took to complete  during the monitoring interval
• Note: await includes svctim. Infact await (average time taken for each IO Request to complete) = the average time that each request was in queue (lets call it queuetime) PLUS the average time each request took to process (svctim)
• %util: This number depicts the percentage of time that the device spent in servicing requests. This can be calculated with the above values. In the above example the total number of reads and writes issued per second is 611 + 414 => 1025. Each request takes 0.96 ms to process. Therefore 1025 requests would take 1025 x 0.96 => 984 ms to process. So out of the 1 second that these requests were sent to the device in, 984 ms were taken to process the requests. This means the device utilization is 984/1000 * 100 => ~98.4%. As you can see in the above iostat output the %util does show ~ 98.5%

일반적으로 %util이 80%를 넘으면 문제를 해결하는 방법은 아래와 같습니다.

• increasing RAM so dependence on disk reduces
• increasing RAID controller cache so disk dependence decreases
• increasing number of disks so disk throughput increases (more spindles working parallely)
• horizontal partitioning
• [출처] iostat의 %util 계산하는 방법|작성자 정일동

http://blog.naver.com/idjung/150082332311

1. closing tables
스레드는 테이블 체크 오퍼레이션을 실행중이다.

2. Closing tables
스 레드가 변경된 테이블 데이터를 디스크로 쏟아내고 사용된 테이블을 닫는 것을 의미한다. 이것은 빠른 오퍼레이션이다. 만약 그렇지 않다면, 풀(full) 디스크를 가지고 있지 않은지 그리고 디스크가 너무 과중하게 사용되지는 않는지 체크해야 한다.

3. Connect Out
리플리케이션 슬레이브는 그것의 마스터와 연결되어 있다.

4. Copying to tmp table
서버는 메모리에 있는 테이블로 복사한다.

5. Copying to tmp table on disk
서버가 디스크에 있는 임시 테이블로 복사한다. 임시 결과 세트는 tmp_table_size 보다 크고, 스레드는 메모리를 절약하기 위해서 인-메모리에서 디스크 기반 포멧으로 임시 테이블을 변경시킨다.

6. Creating tmp table
스레드는 쿼리의 결과 일부를 기억장치에 남겨두기 위해 임시 테이블을 생성한다.

7. deleting from main table
서버는 복함-테이블 삭제의 두 번째 부분을 실행하며, 다른 테이블로부터 매치된 로우를 삭제한다.

8. Flushing tables
스레드는 FLUSH TABLES를 실행하고 그 테이블을 종료하기 위해서 모든 스레드를 기다린다.

9. FULLTEST initialization
서버는 자연 언어처리 전문 검색을 준비한다.

10. Killed
누 군가 KILL 문을 스레드에 보내면 kill 플래그를 체크하여 다음에 중단해야 한다. 플래그는 MySQL의 각각의 메이저 루프에서 점검된다. 그러나 몇몇의 경우, 스레드가 멈추는데 짧은 시간이 걸리곤 한다. 만약 스레드가 몇몇 다른 스레드에 의해 잠겨 있다면, kill은 다른 스레드가 잠금을 해제하자마자 효력을 나타낸다.

11. Locked
쿼리는 다른 쿼리에 의해 잠긴다.

12. Sending data
스레드는 SELECT문으로 로우를 프로세싱하며 데이터를 클라이언트로 보낸다.

13. Sorting group
스레드는 GROUP BY 를 만족시키도록 정렬된다.

14. Sorting for order
스레드는 ORDER BY를 만족시키도록 정렬된다.

15. Opening tables
스레드는 테이블을 열러고 한다. 무언가 오픈을 방해하지 않는다면, 이것은 매우 빠를 프로시져이다. 예를 들어, ALTER TABLE 또는 LOCK TABLE 문은 문이 끝날 때 까지 테이블 오픈을 방해할 수 있다.

16. Reading from net
서버는 네트워크로 부터 패킷을 읽는다.

17. Removing duplicates
초 기단계에서 개별 오퍼레이션을 최적화 할 수 없는 것과 같은 그런 방법에서 쿼리는 SELECT DISTINCT를 사용하고 있다. 이런 이유로 결과를 클라이언트에게 전달하기 전에 모든 중복된 로우를 제거하기 위해서 MySQL은 별도의 단계가 필요하다.

18. Reopen table
스레드는 테이블을 잠갔다. 그러나 잠근 이후에 뒤에 숨은 테이블 구성이 변경되었다는 것을 인지한다. 잠금을 해제하고 테이블을 종료하며 그것을 다시 오픈하려 한다.

19. Repair by sorting
복구 코드는 인덱스를 생성하기 위해 사용된다.

20. Repair with keycache
복구 코드는 키 캐쉬 메모리를 통해 하나씩 키를 생성하는데 사용한다. 이것은 Repair by soting 보다 훨씬 느리다.

21. Searching rows for update
스레드는 로우를 업데이트하기 전에 모든 부합하는 로우를 찾기 위해 첫번째 단계를 행한다. UPDATE가 관련된 로우를 찾는데 사용된 인덱스를 변경시킨다면, 이것은 실행해야 한다.

22. Sleeping
새로운 문을 보내기 위해서 스레드는 클라이언트를 기다린다.

23. statistics
서버는 쿼리 실행 플랜을 발전시키기 위해 통계를 계산한다.

24. System lock
스레드는 테이블의 외부 시스템 잠금을 기다린다. 같은 테이블에 액세스하는 복합 mysqld 서버를 사용하지 않는다면, --skip-external-locking 옵션을 이용해 시스템 잠금 기능을 억제할 수 있다.

25. unauthenticated user
클라이언트 커넥션과 결합되었으나 클라이언트 유저의 인증을 아직 받지 못한 스레드의 상태

26. Upgrading lock
INSERT DELAYED 핸들러는 로우를 삽입하기 위해 테이블을 잠그려고 한다.

27. Updating
스레드는 업데이트할 로우를 찾아 업데이트 한다.

28. updating main table
서버는 복합-테이블 업데이트의 첫 번째 부분을 실행한다. 그것은 오로지 첫번째 테이블만 업데이트 하며, 다른 (레퍼런스) 테이블을 업데이트하는데 사용될 필드와 오프셋을 절약한다.

29. updating reference tables
서버는 복합-테이블 업데이트의 두 번째 부분을 실행하고, 다른 테이블로부터 부합하는 로우를 업데이트 한다.

30. User Lock
스레드는 GET_LOCK()을 기다린다.

31. Waiting for tables
스 레드는 테이블의 기초가 되는 체계가 변경되었다는 통보를 받았고, 새로운 체계를 만들기 위해 테이블을 다시 오픈할 필요가 있다. 그러나 테이블을 다시 열기 위해서, 모든 다른 스레드가 본 건에 대해 테이블을 종료할 때까지 기다려야 한다.

다른 스레드가 FLUSH TABLES를 사용하거나 또는 다음 문들 중 하나를 테이블에서 사용하면, 이런 경고가 발생한다. :FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE,REPAIR TABLE, ANALYZE TABLE, 또는 OPTIMIZE TABLE.

32. waiting for handler insert
INSERT DELAYED 핸들러는 모든 팬딩된 삽입을 실행하고 새로운 것을 기다린다.

33. Writing to net
서버는 네트워크에 패킷을 쓴다.

http://ziugae80.blog.me/71765559

* 이 문서는 dev.mysql.com 을 번역해 만들었음을 밝힌다.

1.    Semisynchronous Replication

MySQL 5.5 부터 기존의 built-in asynchronous replication 에 semisynchronous replication 이 추가되었다.

MySQL replication 은 기본적으로 asynchronous replication 을 사용한다. Master 는 Slave 가 Binary log 에 언제 엑세스를 했는지 혹은 수행을 했는지 아무정보가 없다.

Master crash 의 경우 Commit 된 정보가 어떤 Slave 로도 전송되지 말아야 한다. (하지만 Asynchronous Replication 에서는 이를 확인할 길이 없다.)

그 결과 Master 에서 Slave 로의 failover 시 누락된 트랜젝션이 발생하게 된다.

Semisynchronous Replication 은 asynchronous replication 의 대안이 될 수 있다.

l  semisynchronous-capable slave 는 master 에 접속시 이를 알린다.

l  master 에서 semisynchronous replication 활성 시 반드시 하나 이상의 semisynchronous slave 를 가져야 한다.

Master block 의 transaction commit 을 수행하는 쓰레드는 commit 의 완결과 하나 이상의

semisynchronous slave 로부터 트랜잭션에 대한 모든 정보를 받았음에 대한 확인이나 timeout 발생을 대기한다.

l  Slave 는 이벤트가 relay log 에 쓰였고 disk 로 flush 되었음을 알린다.

l  만약 모든 slave 가 transaction 에 대한 확인에 대한 알림없이 timeout 이 나는 경우 asynchronous replication 으로 변경된다. 이후 최소 하나 이상의 Slave 에 대해 확인이 되면 semisynchronous slave 로 변경된다.

l  Semisynchronous replication 은 master 와 slave 모두 활성화 되어야 한다. 만약 semisynchronous replication 이 master 상에서 비활성화 되어 있거나 Slave 가 없는 경우 master 는 asynchronous 를 사용한다.

마스터가 블로킹 상태 (commit 수행 후 slave 로부터 알림을 기다리는 상태) 중에는 transaction 을 수행중인 세션으로 돌아가지 않는다. 블록이 끝나면 다른 구문을 수행하기 위해 세션으로 돌아간다. 이 시점에 마스터 사이드의 트랜젝션은 commit 되며 그 이벤트에 대한 확인이 최소 하나 이상의 Slave 에 의해 알려진다.

/*+ 해석 다시 볼 것 Ref.A */

블록킹은 binay log 에 쓰여진 rollbacks 후에도 발생하며, nontransactional table 에 대한 수정이 rollback 될 때 일어난다. rolled-back transaction 은 그것이 transactional table 에 아무 영향이 없더라도 로깅된다. 그 이유는 nontransactional tables 에 대한 변경은 rollback 될 수 없으며 slave 에 반드시 전달되야하기 때문이다.

semisynchronous replication 의 semi 를 이해하기 위해선 asynchronous 와 full synchronous replication 을 비교한다.

l  asynchronous replication 에서는 master 가 events 를 바이너리 로그에 기록하며 slaves 는 받을 준비가 되면 요청한다. 이 과정엔 event 가 slave 에 도달한다는 보증이 없다.

l  full synchronous  replication 에서는 마스터가 transaction 을 commit 하면 마스터가 세션으로 돌아가 transaction 을 수행하기 전에 모든 slave 가 transaction 에 대해 commit 해야 한다. 이로 인해

transaction 종료까지 많은 delay 가 생길 수 있다.

l  semisynchronous replication 은 asynchronous 와 full synchronous Replication 사이에 위치한다. 마스터는 다만 하나이상의 slave 가 event 를 받고 기록했음을 대기한다. 이는 모든 slave 에 대해 대기하지는 않는다. 또 slave side 에서 완전히 다 실행되었고 commit 되었는가에 대한 대기가 아니다.

Asynchronous replication 에 대해 semisynchronous replication 은 보다 향상된 데이터 무결성을 제공한다. commit 이 성공적으로 완료되었을 때 데이터가 최소한 두 곳에 존재함을 알 수 있다. (master , 최소하나의 slave) 만약 master 가 commit 했지만 master 가 Slave 의 확인을 대기중에 crash 가 발생한 경우 트랜젝션이 아무 slave 에도 도달하지 못했음을 알 수 있다.

semisynchronous replication 은 성능상 부하가 있을 수 있다. 그 이유는 slave 를 대기하는 시간으로 인해 commit 이 느려지기 때문이다. 이 지연은 최소한 slave 로 commit 을 보내고 slave 로부터 확인을 받기위한 TCP/IP roundtrip 시간만큼 발생할 것이다.

이것이 의미하는 바는 가까운 네트워크에 구성 시 잘 동작하겠지만 거리가 있는 서버의 경우 부하가 커짐을 의미한다.

2.    Semisynchronous Replication Administrative Interface

이 기능을 사용하기 위해 두개의 플러그인이 필요하며, 하나는 Master 다른 하나는 Slave 를 위한 플러그인이다.

system 환경변수가 plugin 의 행동을 컨트롤한다.

아래는 몇가지 예이다.

status 변수로 semisynchronous replication 모니터링이 가능하다.

위의 system 과 status 변수들은 master 나 slave 에 플러그인이 ‘INSTALL PLUGIN’ 을 통해 설치 된 경우 사용가능하다.

3.    Semisynchronous Replication Installation and Configuration

semisynchronous replication 을 사용하기 위해서는 다음의 조건을 만족해야 한다.

l  MySQL 5.5 나 그 이상의 버전이 설치되어 있어야 한다.

l  plugin 설치에는 MySQL server 가 dynamic loading 을 지원해야 한다. 이를 확인하기 위해서는 system 변수 have_dynamic_loading 변수가 YES 인지를 확인한다. Binary 배포버전은 dynamic loading 을 지원해야 한다.

l  Replication 이 이미 구성되어 있고 동작중이어야 한다.

Semisynchronous replication 구성을 위해서는 다음의 명령이 필요하다. INSTALL PLUGIN, SET GLOBAL, STOP SLAVE, START SLAVEJ. 이 명령어는 SUPER 권한으로 수행되어야 한다.

semisynchronous replication plugins 은 MySQL 배포버전에 포함되어 있다.

플러그인 위치는 system variable plugin_dir 을 확인한다.

On the master :

On the slave :

linux 에 plugin 설치시 다음과 같은 에러메시지를 확인하면 libimf 의 설치가 필요하다.

semisynchronous replication plugin 이 설치되면 disabled 상태이다.

plugin 을 활성화 시키기 위해서는 system variables 셋팅해야 한다. 이는 runtime 에 set global 을 사용해 가능하다. (option file + restart 도 가능)

<Runtime 시>

On the Master  :

On each slave :  

위 작업 후 slave io thread 를 정지했다 기동해야한다. 이 과정을 생략하면 replication 은 asynchronous 로 동작한다.

On each slave :

<Runtim 이 아닌경우 : optionfile + restart>

On the master’s option file

On each slave’s option file

4.    Semisynchronous Replication Monitoring

다음의 명령을 통해 semi synchronous replication 구성을 확인할 수 있다.

<system variables>

<slave status>

5.    Practice

기존에 Replication 구성이 되어 있음을 가정한다.

DG1 ,DG2 의 Semisynchronous Replication 조회를 한 결과 아무 결과가 나오지 않았다.

Semisynchronous Replication 사용을 위해 Plugin 위치를 조회했다.

사실 설치에 사용한 5.5 binary 버전 자체가 플러그인을 가지고 있는 상태로 위 조회는 plugin 을 수동으로 다운 받아 위치시킬때나 필요하다.

<on the master side>

PLUGIN 설치 후 Global Variable 변경을 통해 활성화 하였다.

위 과정 이후 Variables 조회를 하자 Semisynchronous 와 관련된 파라미터가 나왔다.

<on each slave>

Slave 에도 Semisynchronous Replication 사용을 위해 Plugin 설치와 Variable 설정을 했다. 다만 셋팅 전이나 셋팅 후나 show slave status\G 의 결과는 변함이 없었다.

DG2 (Slave) 의 slave status (start / slave) 에 따라 Master 에서 변하는 값이 있는데 이는 show status 명령을 통해 관찰할 수 있었다. ( show variables like ‘rep%’ 로는 변함이 없었다.)

<on the Master side>

6.    References

A.     MySQL 5.5 Reference Manual :: 15 Replication :: 15.3 Replication Solutions :: 15.3.8 Semisynchronous Replication | http://dev.mysql.com/doc/refman/5.5/en/replication-semisync.html

B.     MySQL 5.5 Reference Manual :: 15 Replication :: 15.3 Replication Solutions :: 15.3.8 Semisynchronous Replication :: 15.3.8.1 Semisynchronous Replication Administrative Interface | http://dev.mysql.com/doc/refman/5.5/en/replication-semisync-interface.html

C.     MySQL 5.5 Reference Manual :: 15 Replication :: 15.3 Replication Solutions :: 15.3.8 Semisynchronous Replication :: 15.3.8.2 Semisynchronous Replication Installation and Configuration | http://dev.mysql.com/doc/refman/5.5/en/replication-semisync-installation.html

D.     MySQL 5.5 Reference Manual :: 15 Replication :: 15.3 Replication Solutions :: 15.3.8 Semisynchronous Replication :: 15.3.8.3 Semisynchronous Replication Monitoring |

http://dev.mysql.com/doc/refman/5.5/en/replication-semisync-monitoring.html

■ 목적

점검시 DB Restart 가 진행되어 Buffer Memory 가 초기화 되었을 때,

점검 오픈전 데이터들을 일부 Buffer Memory 로 Caching 함으로서

유저유입시 발생하는 Disk IO 충격을 완화함

■ Reference

- MySQL NF 5.6 - InnoDB Buffer Pool Warm-Up (5.6.3~)

  : http://cafe.naver.com/mysqlpg/102

- MariaDB InnoDB Buffer Pool Warm-Up

  : http://cafe.naver.com/mysqlpg/75

■ dba_cache_data.sh

■ dba_cache_data.sql

■ dba_cache_data_old.sql

http://blog.naver.com/bomyzzang/220118755731

MySQL 5.6 새로운 기능 (Percona Server and MariaDB​ 지원)

InnoDB 설정 중 innodb_buffer_pool_size, 기본적으로 메모리에 InnoDB의 데이터와 인덱스를 저장하는 방식.

MySQL 서버를 재시작 할 경우, 버퍼-풀이 비어진 상태이기 때문에, 보통의 경우 버퍼의 워밍업에 약간의 시간이 소요됨.

이럴경우, 사용하는 방식이다.

1. mysql>  SET GLOBAL innodb_buffer_pool_dump_at_shutdown = 1;

MySQL 서버를 재기동하기 전에 해당 명령어 사용, shutdown 할 경우, 버퍼-풀 덤프 받음.​

MySQL 서버를​ 중지할 때, ib_buffer_pool 이라는 파일이 MySQL의 DATADIR 에 생성되어짐.

2. ( my.cnf 설정 추가)  

[mysqld]​

innodb_buffer_pool_dump_at_shutdown = 1

innodb_buffer_pool_load_at_startup = 1 

3. MySQL 재기동 (mysql.err 로그 확인 또는 status 확인)

2014-09-11 14:18:24 7fba24d5a700 InnoDB: Loading buffer pool(s) from /data/mysql_data/ib_buffer_pool

2014-09-11 14:18:24 7fba24d5a700 InnoDB: Buffer pool(s) load completed at 140911 14:18:24 

 ​mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';

+--------------------------------+------------------------+

| Variable_name                                    | Value                                 |

+--------------------------------+------------------------+

| Innodb_buffer_pool_load_status            | Loaded 5121/6441 pages     |

+--------------------------------+------------------------+

1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';

+--------------------------+-----------------------------------------+

| Variable_name                         | Value                                                               |

+--------------------------+-------------------------- --------------+

| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 140911 14:18:24 |

+--------------------------+-----------------------------------------+

4. innodb_buffer_pool_dump_now ,innodb_buffer_pool_load_now.​ 사용하여

재기동 없이 즉시 버퍼-풀 적용하는 방법

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.6.19-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show global variables like "innodb_buffer_pool_dump_now";

+-----------------------------+-------+

| Variable_name                              | Value    |

+-----------------------------+-------+

| innodb_buffer_pool_dump_now       | OFF      |

+-----------------------------+-------+

1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';

+--------------------------------+--------------------------------------------------+

| Variable_name                                    | Value                                                                               |

+--------------------------------+--------------------------------------------------+

| Innodb_buffer_pool_load_status            | Buffer pool(s) load completed at 140911 14:57:18                |

+--------------------------------+--------------------------------------------------+

1 row in set (0.00 sec)

mysql> set global innodb_buffer_pool_dump_now = on ;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status';

+--------------------------------+--------------------------------------------------+

| Variable_name                                    | Value                                                                               |

+--------------------------------+--------------------------------------------------+

| Innodb_buffer_pool_dump_status          | Buffer pool(s) dump completed at 140911 14:58:05              |

+--------------------------------+--------------------------------------------------+

1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';

+--------------------------------+--------------------------------------------------+

| Variable_name                                    | Value                                                                               |

+--------------------------------+--------------------------------------------------+

| Innodb_buffer_pool_load_status            | Buffer pool(s) load completed at 140911 14:57:18                |

+--------------------------------+--------------------------------------------------+

1 row in set (0.00 sec)

mysql> set global innodb_buffer_pool_load_now = on ;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';

+--------------------------------+--------------------------------------------------+

| Variable_name                                    | Value                                                                               |

+--------------------------------+--------------------------------------------------+

| Innodb_buffer_pool_load_status            | Buffer pool(s) load completed at 140911 14:58:41                |

+--------------------------------+--------------------------------------------------+

1 row in set (0.00 sec)

The best way to specify server connection information is with your .mylogin.cnf file. Not only is this file encrypted, but any logging of the utility execution will not expose the connection information. Thus, no user names, passwords, ports, etc. are visible in the log. This is the preferred method for using MySQL Utilities to connect to servers.

Note: MySQL Utilities version 1.2.1 and later support login-paths.

Utilities support the use of login-paths in the connection string provided they use the following format login-path-name[:port][:socket] where the port and socket parameters are optional. If used, these optional parameters override the respective options from the specified login-path.

When using login-paths, there are no default values except on Posix systems when specifying a socket. In this case, the host option defaults to localhost. This means that combining the values specified in the login-path with the two optional values port and socket, one needs to specify at least a user, a hostname and a port or socket.

Use the mysql_config_editor tool (http://dev.mysql.com/doc/en/mysql-config-editor.html) to add the connection information as follows.

shell> mysql_config_editor set --login-path=instance_13001 --host=localhost --user=root --port=13001 --password
Enter password: <Password is prompted to be inserted in a more secure way>

Next, use the following command to confirm that the login-path data was correctly added to .mylogin.cnf (the encrypted file):

shell> mysql_config_editor print --login-path=instance_13001
[instance_13001]
user = root
password = *****
host = localhost
port = 13001

Once your .mylogin.cnf file is configured, you need only specify the section of the .mylogin.cnf file for the server connection. For example, the section created in the previous example is 'instance_13001'. Thus, we use --server=instance_13001. The following shows the execution of a utility specifying the login-path section.

shell> mysqlserverinfo --server=instance_13001 --format=vertical

# Source on localhost: ... connected.
*************************       1. row *************************
server: localhost:13001
config_file: /etc/my.cnf, /etc/mysql/my.cnf
binary_log: clone-bin.000001
binary_log_pos: 341
relay_log:
relay_log_pos:
version: 5.6.17-log
datadir: /Volumes/Source/source/temp_13001/
basedir: /Volumes/Source/source/bzr/mysql-5.6
plugin_dir: /Volumes/Source/source/bzr/mysql-5.6/lib/plugin/
general_log: OFF
general_log_file:
general_log_file_size:
log_error:
log_error_file_size:
slow_query_log: OFF
slow_query_log_file:
slow_query_log_file_size:
1 row.
#...done.

See the online MySQL Reference Manual for more information about login-paths, the .mylogin.cnf file, and themysql_config_editor client.

MySQL 5.6 에 보안을 위한Tool (mysql_config_editor)이 추가되어 테스트 해보았습니다.

mysql 접속시 암호를 넣지않고 접속할 수 있네요.

Then leave a small post on how to mount an automatic notification via mail that tells us what is the status of our database. For example we can manage it a bit before starting our work day and before we leave or while on vacation (jaja. ..). This example is about a MySQL 5.x running on Debian. Steps as follows:

• Check server uptime, memory and disk space.
   
• Check the latest backups or logs of the same
   
• Obtaining the status of the database
   
• Error logs from the database.
   
• Notification via mail.

The system is to build a script that builds our report in a file and then mail it. Moreover, in this example the state of the database I get to mysqlreport *. This gives us enough information to know that has happened recently in the database and can detect any problem if we know what the baseline.

* Be careful because it is available from version 5.x

Step 1) Edit the script based báscia checklist.sh. As you can see has no secret because it is always just redirect the output of a specific command to a temporary file which is then attached as the body of mail or attachment. The format and style and are things of each ...

Step 2) Execute Permissions and programming

For Unix systems you can use crontab to schedule the execution, after assigning execute permissions to the file for the user who will put it in cron

Example email:

0.UPTIME
 09:00:01 up 13:20,  0 users,  load average: 0.00, 0.00, 0.00
USER     TTY      FROM              LOGIN@   IDLE   JCPU   PCPU WHAT

>>> 1.MEMORY
             total       used       free     shared    buffers     cached
Mem:           884        877          6          0         44        745
-/+ buffers/cache:         88        795
Swap:          737          0        737

>>> 2.Disk Space
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1              17G  8.4G  7.9G  52% /
tmpfs                 443M     0  443M   0% /lib/init/rw
tmpfs                 443M     0  443M   0% /dev/shm

>>> 3.BACKUPS
>>> 4.MYSQL STATE

MySQL 5.0.32-Debian_7et  uptime 0 13:9:20       Wed Jul 14 09:00:01 2010

__ Key _________________________________________________________________
Buffer used   387.00k of  16.00M  %Used:   2.36
  Current       1.84M            %Usage:  11.52
Write ratio      0.03
Read ratio       0.10

__ Questions ___________________________________________________________
Total           7.61k    0.16/s
  Com_          5.47k    0.12/s  %Total:  71.93
  COM_QUIT        807    0.02/s           10.61
  DMS             665    0.01/s            8.74
  +Unknown        602    0.01/s            7.92
  QC Hits          61    0.00/s            0.80
Slow                6    0.00/s            0.08  %DMS:   0.90
DMS               665    0.01/s            8.74
  SELECT          648    0.01/s            8.52         97.44
  UPDATE           12    0.00/s            0.16          1.80
  DELETE            4    0.00/s            0.05          0.60
  INSERT            1    0.00/s            0.01          0.15
  REPLACE           0    0.00/s            0.00          0.00
Com_            5.47k    0.12/s           71.93
  show_create   1.15k    0.02/s           15.10
  show_status     797    0.02/s           10.48
  show_variab     797    0.02/s           10.48

__ SELECT and Sort _____________________________________________________
Scan            3.92k    0.08/s %SELECT: 605.09
Range               0    0.00/s            0.00
Full join           1    0.00/s            0.15
Range check         0    0.00/s            0.00
Full rng join       0    0.00/s            0.00
Sort scan           7    0.00/s
Sort range          1    0.00/s
Sort mrg pass       0    0.00/s

__ Query Cache _________________________________________________________
Memory usage  106.50k of  16.00M  %Used:   0.65
Block Fragmnt   0.68%
Hits               61    0.00/s
Inserts            65    0.00/s
Prunes              1    0.00/s
Insrt:Prune      65:1    0.00/s
Hit:Insert     0.94:1

__ Table Locks _________________________________________________________
Waited              0    0.00/s  %Total:   0.00
Immediate       1.32k    0.03/s

__ Tables ______________________________________________________________
Open               64 of   64    %Cache: 100.00
Opened          2.01k    0.04/s

__ Connections _________________________________________________________
Max used            3 of  100      %Max:   3.00
Total             810    0.02/s

__ Created Temp ________________________________________________________
Disk table      1.19k    0.03/s
Table           3.52k    0.07/s
File                5    0.00/s

Now, who likes or knows a little of topic you can think a thousand things to add or things that are missing. For some specific queries as the number of processos so we can use from the command line mysqladmin-u-p <user> <key> <option> for more interesting facts ... Everyone who makes it his own way!

https://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/install.html

PLUK2012-commonschema-dbas-framework-mysql.pdf

http://odysseymoon.tistory.com/53

중단없이 온라인상에서 MySQL의 테이블 스키마 변경 작업에 대해서 알아본다.
무중단으로 스키마를 변경할 수 있는 방법으로는 현재, openark kit의 "oak-online-alter-table", Percona의 "pt-online-schema-change"와 Facebook의 "OSC(Online Schema Change for MySQL)"를 가장 많이 사용할 것 같다.

oak-online-alter-table과 pt-online-schema-change 차이는 oak-online-alter-table에는 지원안하는 포린키와 슬래이브 상태 고려 등을 빼고는 비슷하다. 그래서 좀 더 기능이 보완된 pt-online-schema-change 도구를 가지고 하는 방법을 설명해 본다. Facebook에서 사용하는 툴은 아직 분석하지 못했다. ^^

pt-online-schema-change는 Percona Toolkit의 여러 기능 중에 하나이고, Percona Toolkit은 Maatkit과 Aspersa를 기반으로해서 탄생되었다.
참고로 MySQL 5.6에서는 ALTER TABLE ... ADD INDEX / DROP INDEX 중 이어도 참조·갱신이 함께 실행이 될 수 있다고 하니 참고하면 될 듯 하다.
그리고 TokuDB를 사용할 경우에도 온라인상에서 스키마변경이 자유롭다.

Alter Table의 이슈

Alter Table 명령을 날리면 보통 아래와 같은 순서대로 진행된다.(innodb의 경우)

1. 해당 테이블을 TL_WRITE_ALLOW_READ로 잠근다. 즉, READ는 허용하고 WRITE를 차단한다는 의미이다.
2. 새로운 테이블의 빈 임시 테이블을 만들고.
3. 이전 테이블에서 새 테이블에 데이터를 복사하며.
4. 새 테이블을 이전 테이블과 같은 이름으로 바꾸고 이전 테이블은 삭제한다.
5. 새로운 테이블에 차단했던 WRITE를 푼다.

이렇듯 Alter를 진행하게 되면 해당 테이블의 쓰기 잠금이 발생하여 일반적으로 온라인상에서는 테이블의 스키마 변경은 할 수 없는 구조이다. 스키마 변경을 할 시에는 데이터를 복사하게 되므로 해당 테이블의 사이즈만큼 스토리지 공간이 필요하게 된다.
Alter는 테이블의 행 수가 증가에 따라 선형적으로 시간이 길어지는 것은 아니다. 실제는 더 늦어질 수 있다는 의미이다. 왜냐하면 인덱스 재구성은 O (N log N)의 시간이 걸리기 때문이다. 그래서 테이블의 건수를 기준으로 절대적인 시간을 예측하기 보다는 실제 건수를 만들어서 테스트를 해보기를 권장한다. 거기에 Alter는 한번의 명령에 몰아서 처리해야 테이블 복사를 한번만 할 수 있다. 예를 들면 아래와 같다.