MariaDB(MySQL) ibdata 파일 손상시에 innodb 테이블 복구 방법

InnoDB 사용을 하다가 ibdata 파일 손상시에 테이블을 복구할 수 있는 방법이 있습니다.

ibd 파일이 손상됐을때는 force recovery ( https://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html ) 방법으로 복구를 하는데요. ibdata 파일이 깨졌을때는 force recovery로 복구가 안될 수 있습니다.

이럴때 아래 방법으로 복구를 할 수 있습니다.

* mysqlfrm 유틸리티 설치
# wget https://repo.mysql.com//mysql57-community-release-el6-11.noarch.rpm
# rpm -ivh mysql57-community-release-el6-11.noarch.rpm
# yum install mysql-utilities

* mysqlfrm 유틸리티로 schema 확인
# mysqlfrm –basedir=/usr/local/mysql –user=root –port=13307 /data/ohnew/innovation.frm –verbose
# mysqlfrm –basedir=/usr/local/mysql –user=root –port=13307 /data/ohnew/osskorea.frm –verbose

* schema 복구하기
> create table restore.innovation …..;
> ALTER TABLE innovation DISCARD TABLESPACE;
# cp /data/ohnew/innovation.ibd /data/restore/
> ALTER TABLE innovation IMPORT TABLESPACE;
> select count(*) from restore.innovation;

 

schema 복구할때 복구할 테이터베이스에 mysqlfrm 유틸리티로 확인한 create table 이용해서 테이블 schema를 먼저 복원합니다.

생성된 table의 ibd 파일을 discard 시키고 기존에 사용하던 ibd 파일을 복사해서 덮어씁니다.

테이블 데이터 복원을 위해서 import tablespace를 하고, 테이터를 확인합니다.

MariaDB AX – OLAP / data warehousing

MariaDB에서 OLTP와 OLAP를 지원하기 위해서 제품을 TX / AX 이름으로 제품군을 분리했습니다.

MariaDB AX는 ColumnStore 엔진을 기반으로 OLAP / data warehousing 관련 서비스에서 사용을 위한 데이터베이스입니다.

 

ColumnStore엔진은 MariaDB에서 InfiniDB를 인수해서 개선한 엔진입니다.

 

MariaDB ColumnStore 엔진은 2016년말에 1.0.6 GA 버전이 나왔습니다.

http://mariadb.com/ 홈페이지에서의 아키텍처 구성은 아래와 같습니다.

MariaDB-columnStore_v1_final.png

 

데이터 파일 사이즈로 볼때 1TB 이하는 MariaDB innodb 엔진을 사용해서 OLTP 관련 서비스에 사용하는게 좋고, ColumnStore 엔진은 1TB 이상의 OLAP 관련 서비스에 좋다고 합니다.

국내에서도 데이터 분석을 위해서 적용하는 사례가 있습니다.

Columnar storage 이기에 데이터 분산으로 대용량 처리에 적합합니다. 기존 MariaDB와 성능적으로 같이 큰 차이라고 하면 massively parallel processing (MPP)이라고 합니다.

기존 opensource 데이터베이스들에 비해서 failover 관련한 기능도 괜찮아 보입니다.

 

데이터분석이나 통계 처리를 위한 opensource database로 사용하기에 좋겠습니다.

 

MariaDB TX 2.0

MariaDB의 제품군이 TX와 AX로 나뉘었습니다.

TX는 OLTP를 처리를 위한 제품군들로 구성이 되어 있습니다.

 

http://mariadb.com/ 웹사이트의 주요 사항은 아래와 같습니다.

Completeness and compatibility
– Common Table Expressions
– Window functions
– JSON & GeoJSON functions
– EXECUTE IMMEDIATE
– CHECK (constraints)
– DEFAULT (BLOB/TEXT)
– DECIMAL (38 places)

Performance and scalability
– MyRocks storage engine
– InnoDB enhancements
– Binary Log read throttling
– Binary Log compression
– Virtual Column indexes
– Query cache
– Streaming inserts

Security and recovery
– Per user resource limits
– Enforced TLS connections
– Data masking
– Prepared Statement filtering
– Result Set limiting
– Delayed replication
– Binary Log based rollback

개인적으로 관심이 가는 사항들은 MyRocks 엔진을 지원하면서 소개를 적극적으로 하는것 같습니다.

데이터 파일 사이즈에 대한 압축 성능이 좋아서 IO 성능에서도 좋을 것으로 기대됩니다.

CTE와 Window functions 지원을 통해서 oracle connectby 등의 대안으로 사용하고 다양한 함수 활용으로 쿼리 튜닝에 폭이 넓어 질것입니다.

JSON 지원 관련해서 MySQL 하고 얼마나 호환성을 유지하는지도 확인해 볼 필요가 있겠습니다.

보안/복구 관련해서도 기능이 추가 됐는데요. MaxScale하고 잘 응용해서 사용하면 좋을듯 합니다.

 

현재 MariaDB Server 최신 버전은 10.2.6 GA  입니다.

2017년 5월 23일에 배포 됐습니다. 아직 마이너버전이 낮아서 안정성이 중요한 서비스에는 적용하기에는 좀 더 기다리는것이 좋을 듯 합니다.

내년초에 10.2.15 정도까지 개선이 되면 실제 서비스에 적용하기에 좋을것으로 예상됩니다.

mariabackup – MariaDB backup

MariaDB에서 Xtrabackup을 이용해서 mariabackup 툴을 배포했습니다.

https://mariadb.com/resources/blog/mariadb-backup-released-mariadb-server-10123

 

기본적인 사용법은 Xtrabackup 하고 동일합니다.

MairaDB 10.1.23을 설치하면 bin 디렉토리에 mariabackup 실행파일이 있습니다.

  • windows 버전에서도 지원이 됩니다.

MariaDB에서 제공하는 암호화를 이용했을때 backup 하기 위한 기능이 추가 되어 있습니다.

encryption 관련 옵션은 아래와 같은것들이 있습니다.

–plugin-dir=name : Server plugin directory
–plugin-load=name : encryption plugin to load
–innodb-encrypt-log : encryption plugin to load

 

Xtrabackup에 있는 ssl 관련 옵션은 빠져 있습니다.

–ssl : Enable SSL for connection (automatically enabled with
other flags).
–ssl-ca=name : CA file in PEM format (check OpenSSL docs, implies
–ssl).
–ssl-capath=name : CA directory (check OpenSSL docs, implies –ssl).
–ssl-cert=name : X509 cert in PEM format (implies –ssl).
–ssl-cipher=name : SSL cipher to use (implies –ssl).
–ssl-key=name : X509 key in PEM format (implies –ssl).
–ssl-crl=name : Certificate revocation list (implies –ssl).
–ssl-crlpath=name : Certificate revocation list path (implies –ssl).
–ssl-verify-server-cert : Verify server’s “Common Name” in its cert against
hostname used when connecting. This option is disabled by

MariaDB (MySQL) 성능 측정 툴

MariaDB, MySQL 서버의 성능을 측정할 수 있는 툴들로 아래와 같은 것들이 있습니다.

  • Percona TPCC
  • sysbench
  • Apache JMeter
  • KaKao MRTE

 

sysbench를 이용하는 방법이 가장 많이 찾을 수 있을건데요.

개인적으로는 Percona TPCC 툴을 사용하는게 더 좋은거 같습니다.

https://www.percona.com/blog/2013/07/01/tpcc-mysql-simple-usage-steps-and-how-to-build-graphs-with-gnuplot/

 

다양한 상황에 대한 테스트를 하려면 JMeter를 이용하면 좋습니다.

 

실제 운영중인 서비스와 동일한 환경으로 테스트를 하려면 KaKao MRTE를 사용할 수 있습니다.

http://tech.kakao.com/2016/02/16/opensource-2-mtre/

 

mariadb(mysql) open_files_limit 설정값

https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_open_files_limit

 

open_files_limit 값은 my.cnf에 설정을 하거나 –open-files-limit 지정을 해도 global variables 값이 다를 수 있습니다.

해당 값은 아래 3가지 조건중에 큰값으로 된다고 합니다.

1) 10 + max_connections + (table_open_cache * 2)
2) max_connections * 5
3) open_files_limit value specified at startup, 5000 if none

 

mariadb는 아래와 같이 설명되어 있습니다.

If set to 0, then mysqld will reserve max_connections*5 or max_connections + table_open_cache*2 (whichever is larger) number of files.

https://mariadb.com/kb/en/mariadb/server-system-variables/#open_files_limit

 

Persistent auto_increment (MariaDB 10.3 / MySQL 8.0)

MariaDB / MySQL innodb  엔진을 사용할때 서버 restart 하면서 auto_increment값의 초기화와 관련한 이슈들이 있는것들이 MariaDB 10.3, MySQL 8.0에서 변경된다고 합니다.

 

https://mariadb.org/thoughts-mariadb-server-10-3-mariadb-developers-meeting-amsterdam-part-1/

The timelines of 10.3 and 8.0 will partially determine whether these will be in 10.3. There are also a couple of very interesting contributions that will be evaluated for inclusion in MariaDB 10.3: “Lock wait policy” and “Persistent autoincrement”.

 

https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html

In MySQL 5.7 and earlier, a server restart immediately following a ROLLBACK operation could result in the reuse of auto-increment values that were previously allocated to the rolled-back transaction, effectively rolling back the current maximum auto-increment value. In MySQL 8.0, the current maximum auto-increment value is persisted, preventing the reuse of previously allocated values.

innochecksum – mysql (mariadb) undo log size 확인

./innochecksum Ver 5.7.14, for linux-glibc2.5 (x86_64)
Copyright (c) 2000, 2016, 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.

InnoDB offline file checksum utility.
Usage: ./innochecksum [-c] [-s ] [-e ] [-p ] [-v] [-a ] [-n] [-C ] [-w ] [-S] [-D ] [-l ]
See http://dev.mysql.com/doc/refman/5.7/en/innochecksum.html for usage hints.
-?, –help Displays this help and exits.
-I, –info Synonym for –help.
-V, –version Displays version information and exits.
-v, –verbose Verbose (prints progress every 5 seconds).
-c, –count Print the count of pages in the file and exits.
-s, –start-page=# Start on this page number (0 based).
-e, –end-page=# End at this page number (0 based).
-p, –page=# Check only this page (0 based).
-C, –strict-check=name
Specify the strict checksum algorithm by the user.
-n, –no-check Ignore the checksum verification.
-a, –allow-mismatches=#
Maximum checksum mismatch allowed.
-w, –write=name Rewrite the checksum algorithm by the user.
-S, –page-type-summary
Display a count of each page type in a tablespace.
-D, –page-type-dump=name
Dump the page type info for each page in a tablespace.
-l, –log=name log output.

Variables (–variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
——————————— —————————————-
verbose FALSE
count FALSE
start-page 0
end-page 0
page 0
strict-check crc32
no-check FALSE
allow-mismatches 0
write crc32
page-type-summary FALSE
page-type-dump (No default value)
log (No default value)
——————————— —————————————-

5.7 버전부터 –page-type-summary 옵션이 있습니다.

[root@osskdb1 ~]# ./innochecksum -S /MARIA/data/ib*

File::/MARIA/data/ibdata1
================PAGE TYPE SUMMARY==============
#PAGE_COUNT PAGE_TYPE
===============================================
29 Index page
190 Undo log page
5 Inode page
0 Insert buffer free list page
65179 Freshly allocated page
1 Insert buffer bitmap
130 System page
1 Transaction system page
1 File Space Header
0 Extent descriptor page
0 BLOB page
0 Compressed BLOB page
0 Other type of page
===============================================
Additional information:
Undo page type: 126 insert, 64 update, 0 other
Undo page state: 0 active, 190 cached, 0 to_free, 0 to_purge, 0 prepared, 0 other

innochecksum 툴은 InnoDB offline file checksum utility 입니다.
데몬이 실행중인 상태에서는 점검할 수 없습니다.
점검이 필요한 파일을 DB 서비스중인 상태에서 하려면 파일을 복사한 후에 복사한 파일을 점검하면 됩니다.