백업을 했으니 복원을 해야지.. 

너무나 당연한 것

util.loadDump 로 복원한다. 

주의사항 

- 복원할 MySQL 인스턴스는 5.7 이상이 필요하다.

- 복원시 LOAD DATA LOCAL INFILE 명령문을 사용함으로 local_infile 시스템 변수가 ON으로 설정되어 있어야한다.

- sql_require_primary_key 시스템 변수가 ON 으로 설정되어 있는 경우 덤프 파일에 PK가 없는 테이블이 있을 경우 오류를 반환한다. 

특이사항 및 특징

- waitDumpTimeout 옵션 사용시 덤프중인 덤프를 복원할 수 있다. 

  - 테이블이 사용가능 해지면 로드되고 새 데이터가 덤프될때까지 지정된 시간(초) 동안 대기한다. 

    제한 시간이 경과하면 덤프가 완료된것으로 판단하고 가져오기를 중단한다. 

- 복구 상태를 진행 상태 파일에 저장한다. 

  - 진행 상태 파일 저장 위치는 기본적으로 덤프 디렉토리에  생성된다. 

    load-process.server_uuid.json 파일 형태로 저장되며 저장위치, 파일명을 설정할 수 있다.

  - 복원을 재개하거나 재시도 할때 진행 상태 파일을 참조하고 완료된 단계를 건너뛴다. 

    부분적으로 로드된 테이블에 대해 중복 제거가 자동으로 관리된다. 

  - Ctrl + C 로 중단후 대시 복원할때 중지된 단계에서 다시 진행된다. 

- resetProgress 옵션으로 처음부터 다시 복원가능

  이전에 로드된 모든 객체(디비, 테이블, 사용자, 뷰, 트리거 등등)을 수동으로 제거해야함.

- DDL 파일은 단일 스레드로 로드, 데이터는 지정한 스레드 수로 병렬 로드됨 (기본값 : 4)

  덤프가 생성될때 테이블 데이터가 청크된 경우 여러 스레드를 사용가능

  그렇지 않은 경우 각 스레드가 한번에 하나의 테이블을 로드함 

- 병렬 처리 최대화를 위해 스레드간에 데이터 가져오기를 예약함 

- 덤프시 mysql shell 에서 압축된 경우 별도의 압축 해제 옵션 필요 없음 (자동 해제)

주요 옵션

- dryRun : 지정된 옵션에 따라 복원시 노출되는 오류 및 단계를 보여준다. 

              단 실제 복원은 하지 않는다. (기본값 false)

- threads : 복원시 사용할 스레드 수 (기본값 4)

- progressFile : 덤프 로드 유틸리티의 진행 생태 저장 파일의 위치 

- showProgress : 복원 진행 정보를 표시할지 여부 (기본값 true)

- resetProgress : true 로 지정시 복원을 처음부터 다시 한다. 

                      복원시 미리 생성되어 있는 중복 오브젝트(디비, 테이블 등등)에 대한 제거를 하지 않는다.

                      수동으로 지워줘야 한다. 

- waitDumpTimeout : 덤프 위치에 업로드된 모든 데이터 청크를 처리후 유틸리티가 추가 데이터를 기다리는

                             제한 시간(초)을 지정하여 동시 로드 활성화 (기본값 0)

- ignoreExistingObjects : true 로 지정시 복원중 중복객체 발견시 계속 진행 (기본값 false)

                                 false 로 지정시 중복 객체 발견시 오류가 발생하고 복원을 중지한다. 

- ignoreVersion : 덤프된 MySQL 과, 복원할 MySQL 의 주 버전 번호가 다를 경우 덤프를 가져올지 여부 (기본값 false)

- showMetadata : 덤프에 포함된 덤프 메타 데이터를 출력할지 여부 

- updateGtidSet [off, append, replace] : 덤프 메타 데이터에 기록된 GTID 를 적용할지 여부 및 적용 방식 (기본값 off)

- skipBinlog : true 로 지정시 SET sql_log_bin=0  를 실행하여 복원시에 사용되는 명령어를

                  바이너리 로그에 저장하지 않는다. (기본값 false)

- loadIndexes : false 지정시 복원 대상 테이블의 보조 인덱스를 생성하지 않는다. (기본값 true)

- deferTableIndexes : 보조 인덱스 생성 시점 

                            off : 테이블 로드중에 모든 인덱스 생성 

                            fulltext : fulltext 인덱스만 테이블 로드후 인덱스 생성 

                            all : 테이블 로드시에는 pk 만 생성하고 나머지 인덱스는 로드후 생성 

- analyzeTables : 테이블 로드 후 ANALYZE TABLE 실행 여부 (기본값 off)

- characterSet : 복원시 사용할 캐릭터 셋 

- schema : 덤프시 사용할 기본 스키마 

- excludeSchemas : 복원시 제외할 스키마 지정

- includeSchemas : 지정된 스키마만 복원 

- excludeTables : 복원시 제외할 테이블 지정

- includeTables : 지정된 테이블만 복원

- loadDdl : true 로 지정시 DDL 만 복원, 데이터는 복원 안함

- loadData : true 로 지정시 데이터만 복원, DDL 복원 안함 

- loadUsers : 계정 복원 여부 

- excludeUsers : 복원시 제외할 계정 지정

- includeUsers : 지정한 계정만 복원 

- createInvisiblePKs : true 로 지정시 PK 가 없는 테이블 복원시 PK 추가 

복원 테스트 

이전에 백업한것으로 복원테스트 (역시 백업/복원 속도는 xtrabackup 이 최고다. util.loadDump 는 복원 속도가 아쉽다.)

local_infile 이 on 으로 지정안되서 에러 발생 

set global general_log = on; 후 다시 시도

4 thds loading 지정한 thread 수만큼 덤프 파일을 로딩한다. (PC에 hyper-v로 테스트 하는거라 thread 4로 지정)

VM 인거 감안하고 3시간 39분..  ㅡ_ㅡ);;

general_log 와 진행 상태 파일을 열어보자

먼저 진행 상태 파일  DDL , DATA 로드 등의 정보가 기록되어 있다. 

general_log  특별한게 없다. 

참고 

https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-load-dump.html

나의 잔차 삼천리자전거 칼라스 20의 크랭크 3단이 휘어서 교체하기로 맘먹고

알리익스프레이스에서 크랭크, 패달, 스프라켓, 관련된 공구를 구매하고 

외장형 비비는 시마노 sm-bb52 로 구매

알리에서 구매한 물품들이 하나씩 도착하고 드디어 패달, 크랭크 분리완료!

문제는 사각 비비가 안풀렸다. 

자전거 동호회에 찾아보니 자전거 공구는 대만산정도는 써야된다고 한다.

그래 공구를 다시 사는거야.

비비푸는 공구를 대만산으로 구매하고 도전!!!!! 

안된다. ㅡ_ㅡ);; 

결국 카센터에 가서 임팩렌치로 한방에 풀었다. 

이때만해도 모든게 쉽게 될것 같았다.

문제는 외장형 비비 장착이 안된다.

돌려서 끼우는데 완전히 안들어간다.

기존의 사각 비비와 크기 비교를 해봤는데 크기는 동일하다.

그런데 나사산의 모양이 미묘하게 다르다.

외장형 비비를 다른걸로 주문하고 다시 시도하기로 

오늘은 접자..

util.dumpInstance 을 사용할때 pk가 없는경우 chunk 단위가 병렬 dump 가 아니라 하나의 파일로 dump 한다고 했다.

진짜 그런지 해보자.

먼저 테스트용으로 조금 큰 DB가 필요하다. 

아래 링크에서 다운받은 파일들로 14G 짜리 DB를 만들 수 있다. 

https://northcoder.com/post/using-imdb-as-test-data-set/

title 테이블의 데이터로 pk, 인덱스가 없는 title_bak 테이블을 만든다.

util.dumpInstance('/root/backup',{threads:4}) 로 백업을 하면 아래와 같은 메시지가 나온다. 

사요할 수 있는 인덱스가 없어서 Chunking 을 못한다. 

thread 4개로 덤프한다는 메시지 이외에는 특별한 메시지는 없다. 

제러럴 로그를 보면 dump 하면서 아래의 항목들을 확인한다. 

- DB

- 테이블

- 테이블 정보 (컬럼명, 데이터 타입)

- 테이블 PK 

- 컬럼 통계

- DB 계정

- 사용하지 않는 DB계정 (패스워드 미설정, 만료상태, 잠금상태)

- 이벤트

- 프로시저, 함수

- 트리거 

- 백업 실행 계정의 보유 권한

- 플러그인 상태 

- NDBCLUSTER 사용 여부

- 백업 실행 계정의 스키마, 테이블 권한 

- 백업 실행 계정의 기본 role 

이제 백업을 시작한다. 

- SHOW CREATE USER 계정
- SHOW GRANTS FOR  계정
- SHOW CREATE DATABASE IF NOT EXISTS 디비

여기서 부터는 지정한 thread 수의 thread 가 진행한다. 

- show create table 테이블

- show fields from 테이블 

※ pk가 있고 없는 title, title_bak 테이블을 비교한다. 

title 테이블 

- 테이블 PK 의 MIN/MAX 값 확인 

- SELECT SQL_NO_CACHE MIN(`title_id`), MAX(`title_id`) FROM `imdb`.`title`
- SELECT SQL_NO_CACHE `title_id` FROM `imdb`.`title` ORDER BY `title_id` LIMIT 0,1 /* mysqlsh dumpInstance, chunking table `imdb`.`title`, chunk ID: 0 */

- SELECT SQL_NO_CACHE `title_id` FROM `imdb`.`title` ORDER BY `title_id` LIMIT 695651,1 /* mysqlsh dumpInstance, chunking table `imdb`.`title`, chunk ID: 0 */

chunk ID: 1 ~ 11까지 아래와 같은 쿼리가 실행된다. 

pk 값으로 chunk 를 나눌 범위를 구하는듯.

SELECT SQL_NO_CACHE `title_id` FROM `imdb`.`title` WHERE `title_id` > 'tt0718077' ORDER BY `title_id` LIMIT 0,1 /* mysqlsh dumpInstance, chunking table `imdb`.`title`, chunk ID: 1 */
SELECT SQL_NO_CACHE `title_id` FROM `imdb`.`title` WHERE `title_id` > 'tt0718077' ORDER BY `title_id` LIMIT 695651,1 /* mysqlsh dumpInstance, chunking table `imdb`.`title`, chunk ID: 1 */

SELECT SQL_NO_CACHE `title_id` FROM `imdb`.`title` WHERE `title_id` > 'tt10740924' ORDER BY `title_id` LIMIT 0,1 /* mysqlsh dumpInstance, chunking table `imdb`.`title`, chunk ID: 2 */
SELECT SQL_NO_CACHE `title_id` FROM `imdb`.`title` WHERE `title_id` > 'tt10740924' ORDER BY `title_id` LIMIT 695651,1 /* mysqlsh dumpInstance, chunking table `imdb`.`title`, chunk ID: 2 */

....

SELECT SQL_NO_CACHE `title_id` FROM `imdb`.`title` WHERE `title_id` > 'tt9348300' ORDER BY `title_id` LIMIT 0,1 /* mysqlsh dumpInstance, chunking table `imdb`.`title`, chunk ID: 11 */
SELECT SQL_NO_CACHE `title_id` FROM `imdb`.`title` WHERE `title_id` > 'tt9348300' ORDER BY `title_id` LIMIT 695651,1 /* mysqlsh dumpInstance, chunking table `imdb`.`title`, chunk ID: 11 */

위에서 확인한 pk 값으로 chunk 를 나누어서 덤프 

SELECT SQL_NO_CACHE `title_id`,`content_type_id`,`primary_title`,`original_title`,`is_adult`,`start_year`,`end_year`,`runtime_minutes` FROM `imdb`.`title` WHERE `title_id` BETWEEN 'tt0000001' AND 'tt0718077' OR `title_id` IS NULL ORDER BY `title_id` /* mysqlsh dumpInstance, dumping table `imdb`.`title`, chunk ID: 0 */
SELECT SQL_NO_CACHE `title_id`,`content_type_id`,`primary_title`,`original_title`,`is_adult`,`start_year`,`end_year`,`runtime_minutes` FROM `imdb`.`title` WHERE `title_id` BETWEEN 'tt0718078' AND 'tt10740924' ORDER BY `title_id` /* mysqlsh dumpInstance, dumping table `imdb`.`title`, chunk ID: 1 */

....

SELECT SQL_NO_CACHE `title_id`,`content_type_id`,`primary_title`,`original_title`,`is_adult`,`start_year`,`end_year`,`runtime_minutes` FROM `imdb`.`title` WHERE `title_id` BETWEEN 'tt9348302' AND 'tt9916880' ORDER BY `title_id` /* mysqlsh dumpInstance, dumping table `imdb`.`title`, chunk ID: 11 */

백업 디렉토리에는 아래와 같이 11개의 파일이 생성되었다. 

title_bak 테이블 

title_bak 테이블은 그냥 전체 덤프한다. 

SELECT SQL_NO_CACHE `title_id`,`content_type_id`,`primary_title`,`original_title`,`is_adult`,`start_year`,`end_year`,`runtime_minutes` FROM `imdb`.`title_bak` /* mysqlsh dumpInstance, dumping table `imdb`.`title_bak`, chunk ID: 1 */

백업 파일도 아래와 같이 1개로 생성된다. (데이터 파일 기준)

테스트한 general log와 util.dumpInstance log 

mysql-shell 기능중 가장 눈에 띄는것은 parallel dump, load 

보통 mysqldump와 xtrabackup 을 사용한다. (본인은 ㅋㅋㅋ)

mysqldump

- 장점 : 압축백업하면 원래 DB의 1/4 이하로 크기가 줄어든다.

- 단점 : 백업시간이 오래걸린다.  핫백업이 어렵다

xtrabackup

- 장점 : 빠르다. 핫백업/원격백업 증분백업 등 다양한 백업방법을 지원한다

- 단점 : 백업결과가 논리백업보다 크다.

          디스크 공간이 부족해서 mysqldump 를 사용하는 경우도 있다.

mysqldump, mysqlpump, mydumper, mysql shell 4가지에 대한 성능 테스트 결과는 아래의 사이트를 확인한다. 

http://mysqlserverteam.com/mysql-shell-dump-load-part-2-benchmarks/

mysql shell 이 성능이 제일 좋단다.

그냥 참고만 하고 도입여부는 실제 테스트를 해보고 결정해야 한다. 

병렬로 백업을 할때 시스템 부하도 봐야한다. 성능은 잘나오는데 시스템이 죽을려고 하면 안되니 

시스템에서 안정적으로 동작하는 최적의 thread 수도 찾아야되고 상황에 따라서는 I/O 제한 값도 찾아야 하니 테스트를 해보자.

util 부분을 살펴보면 백업에 관련되는것은 dumpInstance(), dumpSchemas(), dumpTables(), loadDump() 로 보인다. https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities.html

mysql-shell 에서 util. 을 입력하고 탭을 누르면 아래와 같이 확인이 가능하다. 

dump utility 

- mysqldump에서 제공하지 않는 아래 기능을 제공함

  - Oracle Cloud Infrastructure Object Storage 스트리밍

  - MySQL 데이터베이스 서비스 호환성 확인 및 수정,

  - 다중 스레드를 사용한 병렬 덤프

  - 파일 압축

- dump utility 의 종류 

  - util.dumpInstance(outputUrl[, options]) : 데이터베이스(인스턴스) 전체 
  - util.dumpSchemas(schemas, outputUrl[, options]) : 논리 스키마 (스키마, DB)
  - util.dumpTables(schema, tables, outputUrl[, options]) : 테이블별 

util.dumpInstance(outputUrl[, options]) 

아래의 이미지를 보면 알수 있을듯 mysql, information_schema, performance_schema, sys DB는 백업 받지 않는다. 

백업할때 어떤 명령이 실행되는지 general log 를 켜서 보자 

- FLUSH TABLES WITH READ LOCK 걸고 

- 테이블 PK 정보 확인 (뷰, 테이블 컬럼 등도 확인한다.)

- binlog position 확인 

- DB, Table 생성 DDL 문 확인 

- dump 할 테이블의 pk 값 최소/최대값 확인 

  - pk 기준으로 여러개의 chunk (기본 64MB) 로 나누어 저장하기 위해 확인하는 듯 

  - 테이블에 pk가 없는 경우 chunk 단위로 병렬 dump/load 가 안되서 하나의 파일로 dump 

- Data dump for table 에 해당하는 테이블 select 

  - 기본 4개의 thread 에서 dump 

백업된 파일 

주요 옵션

- threads : data chunk dump 시 사용할 thread 수 (기본값 4)

- maxRate : dump 중 데이터 읽기 처리량에 대한 thread 당 초당 최대 바이트수  (0, 빈값 지정시 제한 없음)

- showProgress : dump 진행 정보 표시여부 (true/false)

- compression : dump 데이터 파일 압축 유형 (기본 zstd)

- excludeSchemas /excludeTables : 지정한 schema/table 제외하고 dump 

- includeSchemas / includeTables  : 지정한 schema/table 만 dump 

- excludeUsers : 지정한 사용자 계정을 제외하고 dump 

- includeUsers : 지정한 사용자 계정만 dump 

- compatibility : 호환성을 위해 dump 시 DB 설정을 변경해서 dump  

- users, event, routines, triggers : dump 에 사용자, 이벤트, 함수, 저장프로시저, 트리거 포함여부 (true/false)

- defaultCharacterSet : dump 를 위해 MySQL Shell 에서 MySQL 서버로 연결할 때 사용할 캐릭터셋 

- consistent : 일관된 데이터 백업을 위해 dump 시 인스턴스 잠금을 할지 여부 (true/false)

- ddlOnly : 데이터 없이 DDL 문만 dump 할지 여부 (true/false)

- dataOnly : 데이터만 dump 할지 여부 (true/false)

- chunking : 테이블 데이터를 여러 파일로 분할 여부 (true/false)

- bytesPerChunk : chunk 활성화시 chunk 파일 크기

참고

https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities.html

https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html

http://mysqlserverteam.com/mysql-shell-dump-load-part-2-benchmarks/

mysql shell 이라는게 생겼다.

https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-features.html

일단 써보자.

설치는 https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-install-linux-quick.html 를 참고하고 

mysqlsh 를 실행하고 \connect root@localhost:3306 으로 접속하는데 신기하게 MySQL JS 가 컬러로 나온다. 오~ @_@)

그리고 패스워드를 저장할것인지 물어본다. (테스트니 걍 y)

패스워드를 저장하긴 했는데 운영서버에서 저장하면 보안사고! 저장한거 지워보자 

shell.listCredentials() 로 패스워드가 저장된 연결을 확인 후 

shell.deleteCredential(url) 로 삭제한다.

mysqlsh mysql://root@localhost:3306 으로 다시 접속 

패스워드가 삭제되서 다시 패스워드를 저장할지 물어본다. 

패스워드 저장 관련 옵션을 찾아보자 

아래와 같이 3가지가 있다. 

- credentialStore.excludeFilters []

  패스워드 자동저장에서 제외할 서버의 url 
- credentialStore.helper default

  패스워드 저장여부를 물어보는 부분을 사용할지 여부 

  - default : 사용

  - disabled : 미사용
- credentialStore.savePasswords prompt

  패스워드 자동저장 처리 방식 

  - always : 자동저장
  - never : 저장하지 않음 
  - prompt : 사용자에게 물어본다. 

메뉴얼에 MySQL Shell은 JavaScript, Python 및 SQL로 작성된 코드를 처리합니다. 라고 되어 있다.

메뉴얼을 살짝 보니 내용이 많다. 시간날때 까봐야지 

MySQL Binlog format

- Statement : 쿼리를 그대로 기록하는 방식

- Row : 생성/변경된 모든 Row 정보를 기록하는 방식 

- Mixed : Statement, Row 가 혼합된 방식 

Statement 사용시 복제 구성에서 now(), sysdate() 같은 함수를 쓰는 쿼리가 있을 경우 Slave 에서 반영될때 시간차이가 발생한다. 

Master 에서 10:00:00 에 쿼리가 실행된 쿼리가 Slave에 10:00:04초에 반영되면 4초의 오차 발생!

Row 의 경우 기록되는 로그사이즈가 크다. 이로 인한 디스크 공간, I/O, 네트워크 부하 등등이 문제다.

그래서 사용하는게 Mixed! 

Mixed 의 경우 now(), sysdate() 같은 함수는 row 형태로 저장된다. 

binlog_row_image 

Row 방식으로 binlog를 기록할 때 로깅할 컬럼 Set을 지정한다. 

- FULL : 변경 전/후 이미지 전체를 binlog 에 기록

- MINIMAL : 최소한의 값만 기록, 변경된 데이터와 PK 만 기록 

- NOBLOB : BLOB, TEXT 같이 사이즈가 큰 값을 제외하고 기록 

Statement, Mixed 만 써봤네요.

ftp 게임하다 갑자기 이게 왜 생각날까? ㅡ_ㅡ);;

생각난김에 써봤음

데이터 건수 130건 정도에 작은 컬렉션에서 슬로우 쿼리가 발생한다. 

db.컬렉션.find() 만 해도 1800ms 가 나온다. 

열심 찾아보니 repairDatabase를 하던지 compact를 하란다. 

먼저 compact 시도... 동일하다. 

그래 repairDatabase 를 하자

rs.printReplicationInfo()로 db.repairDatabase() 하고 그동안의 변경분을 oplog 에서 받아오는데

문제가 없을지 공간 확인을 한다. 

Replica Set을 구성하는 노드들을 한대를 shutdown 하고 Conf 파일에서 Replica Set 구성 부분을 주석처리하고

Standalone 으로 구동! 

해당 컬렉션이 있는 DB에서 db.repairDatabase() 를 실행하고 완료되면 Conf 파일에서 주석처리 했던 부분을 해제하고

Replica Set의 맴버로 구동해서 SECONDARY 로 올라오는지 확인한다.

(PRIMARY 에서 rs.status()로 resync 완료후 SECONDARY 로 올라왔는지 확인)

이 과정을 한대씩 다른 노드들에서 실행한다. 

마지막으로 PRIMARY에 접속해 rs.stepDown() 을 실행!

SECONDARY 가 PRIMARY 로 승격되는것을 확인하고 Shutdown 후 db.repairDatabase()를 실행 후 Replica Set의 맴버로 구동한다. 

이후 해당 증상 해결 정확한 원인을 알수 없어서 아쉽지만 해결!

점검 잡고 하면 좋겠지만 그게 안되면, 운영중에 어느정도 리스크를 감안하고 진행!