1. MyISAM, InnoDB, XtraDB 특징
1-1) MyISAM 엔진
– ISAM(Indexed Sequential Access Method)의 단점을 보완하기 위해 나온 업그레이드 버전
– 비–트랜잭션–세이프(non-transaction-safe) 엔진
– 읽기 작업(Select) 속도가 빠름
– table-level lock을 사용하기 때문에 쓰기 작업(insert,update) 속도가 느림
– Full-text 인덱싱이 가능하여 검색하고자 하는 내용에 대한 복합검색이 가능
– 데이터 무결성 보장이 되지 않음
– 인덱스(.MYI)와 데이터 파일(.MYD)가 분리
– 백업 시 /usr/local/mysql/data/DB명 을 전체백업
– innodb보다 파일 크기가 작음
– 테이블 단위의 hot backup(파일 복사)을 할 수 있음
– 테이블 파일만 있더라도 복구가 가능
*/usr/local/mysql/data/DB명/테이블명.frm => 테이블 구조
/usr/local/mysql/data/DB명/테이블명.MYD => Myisam Type 테이블의 DATA
/usr/local/mysql/data/DB명/테이블명.MYI => Myisam Type 테이블의 index
* Hot Backup : DB 서버가 온라인 상태에서 DB를 백업 하는 것
Cold Backup : DB 서버를 중단시키고 백업하는 방법
1-2) InnoDB 엔진
– 트랜잭션–세이프 (transaction-safe) 엔진
– commit, rollback, 장애복구, row-level locking, 외래키 등의 다양한 기능 지원
– row-level lock(행 단위)을 사용하기 때문에 변경 작업(insert, update, delete) 속도가 빠름
– Full-text 인덱싱이 불가능
– 데이터 무결성 보장
– ibdata1, ibdata2 과 같은 파일에 index 및 파일데이터가 저장
– DB 및 테이블 정보는 /usr/local/mysql/data/DB명/테이블명.frm 과 같은 구조로 이루어져 있음
– 백업 시 ibdata1와 같은 파일과 /usr/local/mysql/data/DB명/테이블명.frm 파일을 복사하여 백업
– MyIsam에 비해 약 1.5~2.5배 정도 파일이 커짐
– 테이블 단위의 hot backup(파일복사)가 불가능
– mysqldump나 db 전체적인 복사가 필요
1-3) XtraDB 엔진
– Percona는 MySQL에서 근무하던 엔지니어 몇 명이 설립한 MYSQL 개발 및 컨설팅 회사
– Percona Xtradb는 mysql 엔진인 innodb에 Galera패치 등을 적용한 DB엔진
– mariaDB 또는 percona server 에서 사용 가능
– window에서 사용 불가
=> myisam엔진과 innodb 엔진의 가장 큰 차이점은 트랙잭선 지원 유무로 볼 수 있습니다.
트랜잭션처리가 필요하고 높은 퍼포먼스를 요구하는 대용량 사이트 등에서는 InnoDB가,
트랜잭션 처리가 필요없고, 주로 DB 조회(read) 작업이 많은 소규모 사이트인 경우 MyISAM이
효율적입니다.
=> 백업은 가급적 mysqldump를 사용하는 것이 좋습니다.
=> 백업 방법 중 mysqldump 보다 빠른 xtrabackup이 있습니다. (아래 3. 백업 및 복구 참조)
2. MyISAM, InnoDB, XtraDB 설정
2-1) 엔진 확인
– show engines 명령어로 확인 할 수 있습니다.
<mysql>
|
mysql> show engines; +——————–+———+—————————————————————-+————– | Engine | Support | Comment | Transactions | XA | Savepoints | +——————–+———+—————————————————————-+————– | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | +——————–+———+—————————————————————-+————– |
<mariadb>
|
MariaDB [(none)]> show engines; +——————–+———+—————————————————————————- | Engine | Support | Comment | Transactions | XA | Savepoints | +——————–+———+—————————————————————————- | FEDERATED | YES | FederatedX pluggable storage engine l YES | NO | YES | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO | +——————–+———+—————————————————————————-+————–+—— |
=> mariadb 에서의 Innodb엔진은 기본으로 Percona-XtraDB가 적용되어 있습니다.
2-2) 기본 엔진 확인 및 변경
– 확인 : SELECT engine, support FROM information_schema.engines WHERE support=’DEFAULT’;
– 변경 : SET default_storage_enginet=[변경할 엔진];
|
mysql> SELECT engine, support FROM information_schema.engines WHERE support=’DEFAULT’; +——–+———+ | engine | support | +——–+———+ | InnoDB | DEFAULT | +——–+———+ mysql> SET default_storage_engine=MyISAM; |
2-3) 설정 파일 영구 적용
|
# /etc/my.cnf [mysqld] default-storage-engine=[변경 할 엔진] # service mysqld restart |
3. 백업 및 복구
3-1) 백업 및 복원
– 전체백업
|
# mysqldump -u root -p –all-databases > [백업파일명].sql |
– 특정 DB 백업
|
# mysqldump -u root -p [DB명] > [백업파일명].sql |
– 특정 테이블 백업
|
# mysqldump -u root -p [DB명] [테이블명] > [백업파일명].sql |
– 전체복원
|
# mysql -u root -p < [전체 백업한 파일] |
– 특정 DB 복원
|
# mysql -u root -p [DB명] < [특정 DB 백업한 파일] |
3-2) DB복구
– 특정 DB의 모든 테이블 체크 및 자동 복구
|
# /usr/local/mysql/bin/mysqlcheck -u [DB계정] -p[패스워드] –auto-repair [DB명] |
*주의사항 : mysql stop 후 실행, stop 할 수 없는 상황이면 검사할 테이블에 rock을 걸고 실행,
모든작업은 항상 백업 필수.
– LOCK 걸기
|
# mysql -u root -p mysql > lock tables [table명] READ ; mysql > flush tables ; |
*myisamchk 는 테이블에 대한 read만 할 수있으면 되기때문에 read 를 제외한 모든것에 lock을 걸면 됩니다.
– LOCK 풀기
|
# mysql -u root -p mysql > unlock table; |
– myisamchk 로 복구를 위한 LOCK 걸기
|
# mysql -u root -p mysql > lock tables [테이블명] write; mysql > flush tables; |
* 서비스를 죽이지않고 복구를 해야할 경우에는 write lock를 걸어주면 됩니다.
복구는 write 를 해야하기 때문에 write lock를 걸어주어야 합니다.
– InnoDB 엔진 일 경우 복구
|
# /etc/my.cnf [mysqld] innodb_force_recovery=1 |
* InnoDB는 myisamchk와 같은 별도의 복구 도구를 제공하지 않습니다.
위의 명령어를 추가 시켜 주고 서비스를 재시작 하면 강제 복구 모드로 설정됩니다.
숫자는 1에서 6까지 사용 가능하며, 1이 가장 안전하고 좋은 복구 모드이고,
1에서 복구가 안된다면 1~6까지 순차적으로 값을 바꿔가야 합니다.
복구가 되면 dump로 파일을 백업을 하면 됩니다.
* recovery 옵션 값 설명
– 0 기본값
– 1 (SRV_FORCE_IGNORE_CORRUPT)
서버가 깨진 페이지를 발견한다고 하더라도 계속 구동하도록 만든다.
Try to make SELECT * FROM tbl_name로 하여금 깨진 인덱스 레코드와 페이지를 건너 띄도록 만들며, 이렇게 하면 테이블을 덤핑하는데 도움이 된다.
– 2 (SRV_FORCE_NO_BACKGROUND)
메인 쓰레드가 구동되지 못하도록 한다.
만일 퍼지 연산 (purge operation)이 진행되는 동안 크래시가 발생한다면, 이 복구 값은 퍼지 연산이 실행되는 것을 막게 된다.
– 3 (SRV_FORCE_NO_TRX_UNDO)
복구 다음에 트랜젝션 롤백을 실행하지 않는다.
– 4 (SRV_FORCE_NO_IBUF_MERGE)
삽입 버퍼 병합 연산 (insert buffer merge operations)까지 금지한다.
만일 이 연산이 크래시의 원인이 된다면, 그것을 실행하지 않도록 한다. 테이블 통계값을 계산하지 않도록 한다.
– 5 (SRV_FORCE_NO_UNDO_LOG_SCAN)
데이터베이스를 시작할 때 운도 로그 (undo log)를 검사하지 않는다.
InnoDB는 완벽하지 않은 트랜잭션도 실행된 것으로 다루게 된다.
– 6 (SRV_FORCE_NO_LOG_REDO)
복구 연결에서 로그 롤–포워드 (roll-forward)를 실행하지 않는다.
3-3) xtrabackup 이란?
– percona 에서 무료로 제공하는 DB 핫 백업 툴입니다.
백업 시 암호화, 압축, 증분 백업 등 많은 기능이 포함되어있어 다양한 백업 정책을 만들 수 있으며,
핫 백업으로 mysqldump보다 빠른 백업 및 복구가 가능합니다.
지원되는 버전은 mysql(5.1, 5.5, 5.6, 5.7), DB는 xtraDB, InnoDB가 있습니다.
<설치하기>
|
# yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm # yum list | grep percona # yum install percona-xtrabackup-24.x86_64 |
<백업하기>
|
# innobackupex –user=root –password=’패스워드‘ –socket=/tmp/mysql.sock -no-timestamp [백업경로] ex) innobackupex –user=root –password=’패스워드‘ –socket=/tmp/mysql.sock –no-timestamp /backup/data # ls -l /backup drwxr-x— 7 root root 4096 Mar 25 04:10 data # ls -l /backup/data -rw-r—– 1 root root 418 Mar 25 04:10 backup-my.cnf -rw-r—– 1 root root 79691776 Mar 25 04:10 ibdata1 drwxr-x— 2 root root 4096 Mar 25 04:10 mysql drwxr-x— 2 root root 4096 Mar 25 04:10 performance_schema drwxr-x— 2 root root 4096 Mar 25 04:10 test drwxr-x— 2 root root 4096 Mar 25 04:10 vpopmail drwxr-x— 2 root root 4096 Mar 25 04:10 wordpress -rw-r—– 1 root root 28 Mar 25 04:10 xtrabackup_binlog_info -rw-r—– 1 root root 115 Mar 25 04:10 xtrabackup_checkpoints -rw-r—– 1 root root 493 Mar 25 04:10 xtrabackup_info -rw-r—– 1 root root 2560 Mar 25 04:10 xtrabackup_logfile |
<로그 적용>
– 복원하기 위해서는 백업이 진행되는 동안 생성된 MySQL의 로그를 적용시켜야 합니다.
로그 적용은 –apply-log 옵션을 사용하면 됩니다.
|
# innobackupex –apply-log [백업 경로] ex) innobackupex –apply-log /backup/data |
<복원>
– 로그 적용까지 마쳤다면 –copy-back 옵션으로 복원 할 수 있습니다.
복원은 백업 디렉토리를 MySQL 데이터 디렉토리에 복사하는 것과 동일합니다.
따라서, MySQL의 동작을 중지시켜야 하며 데이터 디렉토리는 비어 있어야 합니다.
|
# service mysqld stop # rm -rf /usr/local/mysql/data # innobackupex –copy-back /backup/data |
– 복원이 완료 되면 MySQL 데이터 디렉토리의 권한 재설정 후 MySQL을 다시 시작하면 됩니다.