MySQL:: Backup & Recovery

SugarCRM/MySQL

MySQL:: Backup & Recovery

onesixx 2013. 5. 4. 20:00

MySQL 5.5 Backup & Recovery Scenario v1.0

1. Targets of Backup

A. Database Engine

MySQL Engine 을 말한다.
초기 바이너리 로그파일을 압축 해제 후 Database 생성 스크립트를 돌리고 난 이후의 파일들을 말함이다.
데이터 파일은 다 분산 지정했음을 가정하고 이야기 하였다. 엔진은 크기가 1기가 미만으로 작다.

B. my.cnf

MySQL Instance 에 대한 Configuration File 이다.
수 kb 의 크기 정도밖에 하지 않지만 서버에 대한 셋팅값이 다 들어가 있는 파일로 유실되면 위험하다.
특히 InnoDB 의 경우 테이블 스페이스의 물리적 정보가 들어가 있어 더더욱 그러하다.

C. binary log

binary log 에는 DB 의 변경내역이 들어간다. Oracle 의 Redo Log / Archived Log 를 생각하면 된다.
log-bin variable 을 활성화 해야 사용한다. 이 로그가 없는 경우 Crash 시점까지의 데이터 복구가 불가능해진다.
ORACLE 로 보면 Restore 만 하고 Recover 를 하지 못한 경우와 동일하다.

D. innodb datafile, innodb logfile, InnoDB Table frm

Storage Engine InnoDB 는 Tablespace 를 사용한다. 이 Tablespace 가 물리적으로 InnoDB Datafile 에 해당된다.
또 이 Storage Engine 이 사용하는 Redo Log File 이 있는데 이에 해당하는 것이 InnoDB Logfile 이다.
마지막으로 InnoDB를 사용하는 Table 역시 Table 의 물리적 정보를 가지기 위해 {table_name}.frm 을 가진다.

E. MyISAM Table, Index, frm

MyISAM 을 사용하는 table 은 MYD, MYI, frm 확장자의 파일을 한 세트로 가진다.
5.5 는 InnoDB 가 Default Engine 이기 때문에 사용자가 MyISAM Table 을 별도로 지정하지 않았다면
mysql schema 의 table 이 유일한 MyISAM 테이블이 된다.

2. Ways of Backup

A. COPY

COPY 라 함은 OS 의 cp, mv, rsync 등의 명령어를 사용한 백업을 말한다.
MySQL 이 shutdown 된 상태라면 간단히 copy 만 해도 해당 시점의 온전한 데이터베이스를 얻을 수 있다.
하지만 동작중인 DB 라면 MyISAM 을 사용하는 Table 을 제외하고 온전한 백업을 얻을 수 없다.
또 MyISAM 의 경우라 하더라도 lock table 명령을 사용해야 하므로 정상적인 서비스는 사용할 수 없게 된다.

B. mysqldump

mysqldump 는 free backup 도구이다. 백업 대상을 sql 형태로 뽑아낸다. 단점으로는 백업 대상이 된 타겟은 백업시간동안 Read only 상태가 된다는 것이다. 때문에 이 명령을 수행할 수 있는 시간은 한정적이다. 또한 백업하는 시간도 복구하는 시간도 오래 걸린다.

C. ibbackup

핫백업 도구이다. 핫 백업이라 함은 서비스 타임에도 서비스 영향없이 사용할 수 있다는 이야기다. 이 도구는 mysqldump 와 달리 라이선스가 필요하다. 지금은 ORACLE 이 SUN 을 샀고 SUN 이 MySQL 을 가지고 있었기에 라이선스 정책은ORACLE 이 가지고 있다. ibbackup 의 장점은 서비스에 영향없이 백업이 가능하다는 점과 (hot backup) mysqldump 와 비교했을 때 빠른 백업 / 복구 시간을 가진다는 점이다.

3. Reason of Crashed Database

A. USER

i. 테이블 삭제

여기서 말하는 테이블 삭제는 사용자가 의도하지 않은 삭제를 말한다. ORACLE 의 경우 10g 부터 Recyclebin 이 생겨 복구가 가능하지만 MySQL 은 그렇지 않다.

ii. 중요 파일 혹은 디렉토리 삭제

rm 명령을 실수로 사용한 것이 아니라면 일어날 수 없는 일들이다. Script 를 사용할 때는 경로를 주의해 사용해야 할 것이고 rm 과 같은 명령을 사용할 때에는 와일드 카드 대신 타겟을 명시적으로 사용하는게 예방책이라면 예방책이다.

B. MACHINE

i. Page Corruption

디스크에 Corruption 이 있는 경우

쉽게 표현하면 디스크 블록에 문제가 있는 경우를 말한다. 디스크가 미러링 되어 있다면 자동복구 되겠지만 그렇지 않다면 복구 과정을 밟아야 한다. 미러링 되어 있더라도 소스가 전부 훼손된다거나(확률은 낮지만) 레이드가 풀리는 경우(역시 확률은 낮다.) 가 생기면 복구 과정을 밟을 수 밖에 없다.

MyISAM 테이블에 Corruption 이 있는 경우

Table 이 Storage Engine 을 MyISAM 을 사용하는 경우를 말한다.

InnoDB 테이블에 Corruption 이 있는 경우

Table 이 Storage Engine 을 InnoDB 를 사용하는 경우를 말한다.

Checksum Reset 을 이용한 방법

MySQL 은 Page 에 Corruption 을 발견하면 이를 해결하기위해 Instance 를 Restart 한다. Restart 를 통해 Corruption 이 수정되면 다행이지만 그렇지 않은 경우에는 Restart 를 계속 반복하게 된다.

ii. 중요 파일 혹은 디렉토리 손실

[hostname].index 파일의 경우

*.frm 파일의 경우

frm 파일은 해당 테이블의 구조정 정보를 담고 있다. 이 파일이 손상되면 데이터가 온전하더라도 읽을 수 없다.

Database Directory 의 경우

MySQL 의 Database (=Schema) 는 디렉토리형태이며 해당 Database 가 소유한 Table 은 해당 디렉토리 안에 위치하게 된다. MyISAM Table 의 경우 MYD, MYI, frm 이 디렉토리 안에 위치하게 되며 InnoDB 의 경우는 frm 이 안에 위치하게 된다. (Index, Table 은 Tablespace 에 위치한다.)

bin-log 의 경우

bin-log 는 Optional 한 기능이지만 Replication 구성이라던가 Recover 에 필수 불가결하다.

MySQL Engine 이 분실된 경우

MySQL Engine 이 손상된 경우는 다른 경우에 비해 크리티컬하지 않다. 해당 내용은 다시 이야기 하도록 하겠다.

Redolog file 의 경우

Redolog file 이라 함은 InnoDB 의 Redo log file 을 말한다. 이는 bin-log 와 다른것이다.

4. SCENARIO

A. 테이블이 삭제된 경우

테이블이 의도하지 않게 삭제되었고 이 테이블을 이용하는 쿼리가 있다면 필연적으로 서비스가 영향을 받는다. 서비스가 영향을 받는다는 것은 ‘장애’란 이야기다.

B. 디스크에 Corruption 이 있는 경우

데이터 영역이 미러링 되어 있다면 디스크에 일어난 Corruption 이 문제가 될 일은 희박하다. 그럼에도 불구하고 데이터 영역의 Corruption 이 문제가 된다는 것은 Mirroring 이 되어 있지 않다는 이야기다.

데이터 영역이라 함은 MySQL Engine 을 제외한 부분을 말한다. 데이터 파일에 손상이 갔기 때문에 복원 과정을 밟아야 하는데 먼저 미러링 구성을 다시 밟는다. 물론 이 내용은 물리적으로 다른 위치에 온전한 백업파일이 있음을 전제로 한다.

C. MyISAM 테이블에 Corruption 이 있는 경우

check table / repair table 명령으로 처리한다.

D. InnoDB 테이블에 Corruption 이 있는 경우

백업본을 이용하여 복구를 실시한다. 흐름은 핫백업본(ibbackup) 을 이용하는 것과 dump (mysqldump) 를 이용하는 방법이 있다.

E. Checksum Reset 을 이용하여 Corruption 을 처리하는 경우

F. [hostname].index 파일이 손상된 경우

G. *.frm 파일이 손상된 경우

H. bin-log 가 손상된 경우

I. MySQL 엔진이 손상된 경우

J. Redolog File 이 손상된 경우 (InnoDB)

5. PRACTICE

A. 테이블이 삭제된 경우 서비스를 Stop 하고 진행하거나 그대로 진행하는 방법이 있다. 이것을 판단하는 기준은 테이블의 서비스에 대한 영향력과 관련있다.

그대로 진행한다는 것은 해당 테이블과 연관된 연산을 그대로 무시한다는 것이다. 동시에 이것은 에러를 감내할 수 있다라던가 별로 중요하지 않다란 말과 동일하다.

아래의 경우는 서비스를 막고 Recover 하는 과정의 예이다. fulldump 는 Table 이 삭제되기 전의 백업이며 백업이후 테이블을 삭제하였다. 결과적으로 Binary Log 어딘가에 삭제구문이 들어있을 것이다.

mysql> source fulldump.sql

oracle32@/home/mysql/mysql1>head -n 50 fulldump.sql

-- MySQL dump 10.13 Distrib 5.5.8, for linux2.6 (i686)

-- Host: localhost Database:

-- ------------------------------------------------------

-- Server version 5.5.8-enterprise-commercial-advanced-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

-- Position to start replication or point-in-time recovery from

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000033', MASTER_LOG_POS=107;

MySQL Dump 의 파일 내부를 살펴보면 위와같이 Binary Log / Binary Log Pos 정보를 볼 수 있다.

이를 통해 Binry Logs 가 어디까지 적용되어 있나 알 수 있다.

남은 문제는 어느 Binary Log 파일까지 적용할지에 대한 것과 어느 Position 까지 적용할지에 대한 문제이다.

oracle32@/home/mysql/mysql1>./bin/mysqlbinlog ./data/mysql-bin.000033 | grep DROP

위와 같은 방법으로 어느 Binary Logs 에서 실행되었는지 알 수 있다. binary log 는 말그대로 binary 파일로 myqlbinlog 를 통하지 않으면 파일내 sql 구문을 제대로 볼 수 없다.

grep 은 DROP 명령어가 사용된 적 있는지 확인하기 위해 사용하였다.

DROP 이 어느 Bin log 에서 수행되었다는걸 확인하면 mysqlbinlog 와 출력자 지정을 통해 sql 구문으로 뽑아내는 과정을 밟는다.

oracle32@/home/mysql/mysql1>./bin/mysqlbinlog --start-position=107 ./data/mysql-bin.000033 > recover_f.sql

oracle32@/home/mysql/mysql1>vi recover_f.sql

vi 편집기에서 DROP 구문을 찾은 후 밑에줄을 잘라버린다

(예 command d + {매우 큰 숫자} + enter)

위의 과정을 통해 Drop 이 수행되기 이전까지의 SQL 을 얻을 수 있다.

recover_f.sql 을 통해 데이터를 넣었고 Table b_image 가 조회 가능함을 확인하였다. 아래에서는 recover_f.sql 을 수행하기 전/후 를 비교하였다.

mysql> show databases;

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

| Database |

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

| information_schema |

| bin_bak |

| innodb_test |

| mysql |

| performance_schema |

| test |

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

6 rows in set (0.00 sec)

mysql> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

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

| Tables_in_test |

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

| id |

| id1 |

| id2 |

| sample |

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

4 rows in set (0.00 sec)

mysql> source recover_f.sql

Database changed

mysql> show tables;

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

| Tables_in_test |

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

| b_image |

| id |

| id1 |

| id2 |

| sample |

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

5 rows in set (0.00 sec)

mysql> select * from b_image;

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

| a |

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

| before image |

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

1 row in set (0.00 sec)

B. 디스크에 Corruption 이 있는 경우

사전에 다음의 백업을 가지고 있다고 가정한다.

- MySQL Engine Backup

- my.cnf

- MyISAM Backup

- InnoDB Backup (ibbackup)

- binary log Backup

Backup 본은 sqldump 도 사용가능하다. sqldump 의 경우는 A 에서 이야기 한 것과 흐름은 동일하다.

ibbackup 은 sqldump 와 달리 복구 소요시간이 짧기 때문에 1차적인 선택안이 된다.

먼저 기존에 Backup 본이 있다고 가정한다. 백업본의 위치는 data2 이며 이를 data 에 위치한 innodb file 에 덮어씌운다.

ibbackup: We were able to parse ibbackup_logfile up to

ibbackup: lsn 0 8283158

ibbackup: Last MySQL binlog file position 0 561, file name ./mysql-bin.000005

ibbackup: The first data file is '/home/mysql/mysql/data2/ibdata1'

ibbackup: and the new created log files are at '/home/mysql/mysql/data2/'

110517 17:51:15 ibbackup: Full backup prepared for recovery successfully!

oracle32@/home/mysql/mysql/data2>mv * ../data/

ibbackup 을 실시했을 때 binlog file 과 position 정보가 나온다. 이 정보는 백업본을 사용했을 때 다시 나오기 때문에 외울 필요성은 없다.

InnoDB 를 덮어 씌운 후 startup 을 하면 error log 에 다음과 같은 메시지를 확인할 수 있다.

110517 17:54:22 InnoDB: Database was not shut down normally!

InnoDB: Starting crash recovery.

InnoDB: Reading tablespace information from the .ibd files...

InnoDB: Restoring possible half-written data pages from the doublewrite

InnoDB: buffer...

InnoDB: Last MySQL binlog file position 0 561, file name ./mysql-bin.000005

110517 17:54:22 InnoDB: 1.1.4 started; log sequence number 8283660

110517 17:54:22 [Note] Event Scheduler: Loaded 0 events

위의 내용에 binlog file / position 정보가 나옴을 확인할 수 잇다.

위 정보를 토대로 가장 최근시점까지의 갭을 알 수 있다.

mysql> show binary logs;

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

| Log_name | File_size |

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

| mysql-bin.000001 | 27838 |

| mysql-bin.000002 | 993661 |

| mysql-bin.000003 | 126 |

| mysql-bin.000004 | 1222768 |

| mysql-bin.000005 | 580 |

| mysql-bin.000006 | 126 |

| mysql-bin.000007 | 107 |

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

7 rows in set (0.00 sec)

binary logs 를 조회한 결과 가장최근의 파일이 7번임을 알 수 있었다.

oracle32@/home/mysql/mysql>./bin/mysqlbinlog --start-position=561 ./data/mysql-bin.000005 ./data/mysql-bin.000006 ./data/mysql-bin.000007 >>recover.sql

위 정보를 통해 recover 에 필요한 정보를 sql 로 만든다. 이 정보는 서버에서 수행하면 된다.

mysql> set sql_log_bin=0;

mysql> source recover.sql

참고로 recover 하기 위해 만든 쿼리를 sql_log_bin=0 셋팅없이 수행하면 binary log 를 쌓게 된다. 장애복구를 하고나면 바로 백업을 실시해야 되지만 그렇지 않은 경우 또 다음 풀백업 전에 또다시 장애가 났고 사용했던 binary log 를 또 사용해야 한다면 dml 이 중첩된 binary log 를 사용하게 된다. (이런 일이 일어날 일은 낮지만 sql_log_bin 을 셋팅해 둬서 나쁠일은 없다.)

C. MyISAM 테이블에 Corruption 이 있는 경우

check table 을 실시한다. 여기서는 check table 의 option 에 대한 설명은 따로 하지 않겠다. 핵심은 이 명령어를 통해 Corruption 검사가 가능하다는 것이다.

mysql> check table db for upgrade;

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

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

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

1 row in set (0.01 sec)

mysql> check table db quick;

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

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

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

1 row in set (0.00 sec)

mysql> check table db fast;

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

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

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

1 row in set (0.00 sec)

mysql> check table db medium;

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

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

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

1 row in set (0.00 sec)

mysql> check table db extended;

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

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

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

1 row in set (0.00 sec)

mysql> check table db changed;

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

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

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

1 row in set (0.00 sec)

Corruption 이 확인되었다면 repair table 을 실시한다.

mysql> repair local table db;

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

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

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

1 row in set (0.00 sec)

mysql> repair no_write_to_binlog table db;

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

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

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

1 row in set (0.00 sec)

mysql> repair local table db quick;

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

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

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

1 row in set (0.00 sec)

mysql> repair local table db extended;

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

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

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

1 row in set (0.00 sec)

mysql> repair local table db use_frm;

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

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

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

2 rows in set (0.00 sec)

D. InnoDB 테이블에 Corruption 이 있는 경우

핫백업이 있는 경우엔 B 의 과정과 동일하다.

핫백업이 없는 경우엔 mysqldump 를 이용해야 하는데 A 와 동일한 과정을 밟는다.

E. Checksum Reset 을 이용하여 Corruption 을 처리하는 경우

/*+ 테스트 케이스 추가 */

F. [hostname].index 파일이 손상된 경우

mysql-bin.index 가 손상된 경우 Standalone (ORACLE 의 Single Instance 를 생각) 의 경우 별 문제 없으며 Instance 재기동시 파일도 정상적으로 생성된다.

다만 Replication 의 경우 binary log 를 이용해 Slave 에 전달하기 때문에 정합성이 깨질 수 있다.

아래의 예시는 mysql-bin.index 를 다른 위치로 이동시킬 때 mysql 상에 나타나는 변화를 확인해 본 결과이다.

oracle32@/home/mysql/mysql2/data>ls

ibbackup_logfile mysql mysql-bin.000004 oracle32.test.com.err

ibdata1 mysql-bin.000001 mysql-bin.000005 oracle32.test.com.pid

ib_logfile0 mysql-bin.000002 mysql-bin.000006 performance_schema

ib_logfile1 mysql-bin.000003 mysql-bin.index test

oracle32@/home/mysql/mysql2/data>mv mysql-bin.index ../../

oracle32@/home/mysql/mysql2>./bin/mysql -uroot

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

Your MySQL connection id is 2

Server version: 5.5.8-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

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 binary logs;

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

| Log_name | File_size |

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

| mysql-bin.000001 | 27838 |

| mysql-bin.000002 | 993661 |

| mysql-bin.000003 | 126 |

| mysql-bin.000004 | 1222768 |

| mysql-bin.000005 | 126 |

| mysql-bin.000006 | 107 |

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

6 rows in set (0.00 sec)

위 결과를 보면 mysql-bin.index 파일을 mv 시켰음에도 불구하고 show binary logs 조회 했을 때 내용이 다 출력됨을 알 수 있었다.

아래 결과는 데이터베이스를 내렸다 올린 시점의 결과이며 mv 했던 mysql-bin.index 파일이 재생성 되었음을 알 수 있다. 다만 binary logs 를 조회했을 때 Binary Logs 가 6번에서 7번으로 바뀌었고 7번 이전은 파일 시스템에 존재함에도 불구하고 없는것으로 나온다.

Replication Master 에서 이와 같은 일이 일어났다고 하면 Slave 측에서는 손실된 Binary log file 의 정보를 요구하는 상황이 벌어질 수 있다.

oracle32@/home/mysql/mysql2/data>ls

ibbackup_logfile mysql mysql-bin.000004 mysql-bin.index test

ibdata1 mysql-bin.000001 mysql-bin.000005 oracle32.test.com.err

ib_logfile0 mysql-bin.000002 mysql-bin.000006 oracle32.test.com.pid

ib_logfile1 mysql-bin.000003 mysql-bin.000007 performance_schema

oracle32@/home/mysql/mysql2>./bin/mysql -uroot

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

Your MySQL connection id is 1

Server version: 5.5.8-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

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 binary logs;

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

| Log_name | File_size |

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

| mysql-bin.000007 | 107 |

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

1 row in set (0.00 sec)

G. *.frm 파일이 손상된 경우

frm 파일은 Table 의 Dictionary 정보를 가지고 있으며 이 파일이 손상되는 경우 Table 의 Access 가 불가능해 진다.

oracle32@/home/mysql/mysql2>mv ./data/test/City.frm ./data/test/City

mysql> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

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

| Tables_in_test |

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

| Country |

| CountryLanguage |

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

2 rows in set (0.00 sec)

mysql> select * from City;

ERROR 1146 (42S02): Table 'test.City' doesn't exist

위에서 frm 파일을 이동시키자 City table 이 보이지 않게 되었고 접근도 불가능했다.

oracle32@/home/mysql/mysql2>mv ./data/test/City ./data/test/City.frm

mysql> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

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

| Tables_in_test |

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

| City |

| Country |

| CountryLanguage |

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

3 rows in set (0.00 sec)

mysql> select count(*) from City;

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

| count(*) |

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

| 4079 |

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

1 row in set (0.00 sec)

frm 파일을 원래대로 복원하자 Table 이 다시 보였고 Access 도 가능했다.

위와 같은 복구는 Table 의 구조적 정보 (이를 테면 컬럼 같은…) 변경이 없었을 때 가능하며 물리적 구조가 변경이 된 경우에는 변경 이후의 frm 파일이 필요하다. 이것이 없는 경우 백업해 둔 frm 을 사용하되 Access 가 되는지 확인하며 이 방법도 안된다면 의도하지 않은 Table 삭제 때와 동일한 복구 절차를 밟아야 한다.

H. bin-log 가 손상된 경우

bin-log 는 Optional 한 기능으로 손상되어도 큰 문제는 없다. 다만 추후 Recover 시에 문제가 될 수 있다. InnoDB 의 경우 ib logfile 이 Instance Recovery 역할을 하기에 문제가 없지만 다른 Type 의 Storage Engine 은 그렇지 않다. 또 Replication 의 경우 bin log 에서 Slave 로 DML 이 전파되기 때문에 Master 와 Data 가 달라질 수 있다.

운영중 Binary Log 에 문제가 생기면 Flush 를 해 다시 로그를 쌓도록 만들어야 한다.

다음의 예시는 mysql-bin.000009 를 OS 상에서 삭제후 world_innodb.sql 을 통해 data 를 insert 한 후의 File_size 이다.

mysql> show binary logs;

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

| Log_name | File_size |

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

| mysql-bin.000007 | 150 |

| mysql-bin.000008 | 150 |

| mysql-bin.000009 | 1223081 |

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

3 rows in set (0.00 sec)

mysql> exit

Bye

oracle32@/home/mysql/mysql2>cd data

oracle32@/home/mysql/mysql2/data>ls

ibbackup_logfile mysql-bin.000001 mysql-bin.000006 oracle32.test.com.pid

ibdata1 mysql-bin.000002 mysql-bin.000007 performance_schema

ib_logfile0 mysql-bin.000003 mysql-bin.000008 test

ib_logfile1 mysql-bin.000004 mysql-bin.index

mysql mysql-bin.000005 oracle32.test.com.err

world_innodb.sql 을 실행하면서 mysql-bin.000009 의 크기도 증가했지만 파일 시스템 상을 확인해 본 결과 binary log file 자체가 없음을 알 수 있었다.

mysql> flush logs;

Query OK, 0 rows affected (0.00 sec)

mysql> show binary logs;

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

| Log_name | File_size |

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

| mysql-bin.000007 | 150 |

| mysql-bin.000008 | 150 |

| mysql-bin.000009 | 107 |

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

5 rows in set (0.00 sec)

oracle32@/home/mysql/mysql2>cd data

oracle32@/home/mysql/mysql2/data>ls

ibbackup_logfile mysql-bin.000001 mysql-bin.000006 oracle32.test.com.err

ibdata1 mysql-bin.000002 mysql-bin.000007 oracle32.test.com.pid

ib_logfile0 mysql-bin.000003 mysql-bin.000008 performance_schema

ib_logfile1 mysql-bin.000004 mysql-bin.000009 test

mysql mysql-bin.000005 mysql-bin.index

binary log 는 flush logs 를 실행하면 Binary log 를 재생성한다. binary logs 가 쌓이지 않은 시점부터 재생성까지는 정보가 손실된 것이다. 물론 Optional 한 기능인 만큼 Database 를 복원하는 경우가 아니라면 이슈가 되지 않지만 Binary log 를 사용하여 Recover 과정을 밟아야 한다면 이 부분은 손실되었기 때문에 문제가 된다.

I. MySQL 엔진이 손상된 경우

MySQL 은 Engine 을 메모리에 로드하기 때문에 Engine 이 손상되어도 정상적으로 동작한다. Instance 를 내리기 전에 백업해 둔 Engine 을 복원하면 된다.

oracle32@/home/mysql/mysql2>ps -ef | grep mysql

root 4912 4394 0 15:31 pts/1 00:00:00 su - mysql

mysql 4913 4912 0 15:31 pts/1 00:00:00 -ksh

mysql 12312 4913 0 18:13 pts/1 00:00:00 /bin/sh ./bin/mysqld_safe --defaults-file=/home/mysql/mysql2/conf/my.cnf

mysql 12635 12312 0 18:13 pts/1 00:00:06 /home/mysql/mysql2/bin/mysqld --defaults-file=/home/mysql/mysql2/conf/my.cnf --basedir=/home/mysql/mysql2 --datadir=/home/mysql/mysql2/data --plugin-dir=/home/mysql/mysql2/lib/plugin --log-error=/home/mysql/mysql2/data/oracle32.test.com.err --pid-file=/home/mysql/mysql2/data/oracle32.test.com.pid --socket=/tmp/mysql.sock --port=3306

mysql 19770 4913 0 22:27 pts/1 00:00:00 ps -ef

oracle32@/home/mysql/mysql2>

oracle32@/home/mysql/mysql2>ls

2 data INSTALL-BINARY man scripts support-files

bin docs lib mysql-test share world_innodb.sql

conf include LICENSE.mysql README sql-bench

oracle32@/home/mysql/mysql2>rm -rf bin docs include lib man mysql-test scripts share sql-bench support-files

oracle32@/home/mysql/mysql2>ls

2 conf data INSTALL-BINARY LICENSE.mysql README world_innodb.sql

oracle32@/home/mysql/mysql2>ps -ef | grep mys

root 4912 4394 0 15:31 pts/1 00:00:00 su - mysql

mysql 4913 4912 0 15:31 pts/1 00:00:00 -ksh

mysql 12312 4913 0 18:13 pts/1 00:00:00 /bin/sh ./bin/mysqld_safe --defaults-file=/home/mysql/mysql2/conf/my.cnf

mysql 19823 4913 0 22:28 pts/1 00:00:00 ps -ef

oracle32@/home/mysql/mysql>ls

bin data2 INSTALL-BINARY man scripts support-files

conf docs lib mysql-test share world_innodb.sql

data include LICENSE.mysql README sql-bench

oracle32@/home/mysql/mysql>cp -R bin ../mysql2

oracle32@/home/mysql/mysql>cp -R conf ../mysql2

oracle32@/home/mysql/mysql>cp -R docs ../mysql2

oracle32@/home/mysql/mysql>cp -R include ../mysql2

oracle32@/home/mysql/mysql>cp -R lib ../mysql2

oracle32@/home/mysql/mysql>cp -R man ../mysql2

oracle32@/home/mysql/mysql>cp -R mysql-test ../mysql2

oracle32@/home/mysql/mysql>cp -R share ../mysql2

oracle32@/home/mysql/mysql>cp -R sql-bench ../mysql2

oracle32@/home/mysql/mysql>cp -R support-files ../mysql2

oracle32@/home/mysql/mysql>cd ../mysql2

oracle32@/home/mysql/mysql2>ls

2 data INSTALL-BINARY man share world_innodb.sql

bin docs lib mysql-test sql-bench

conf include LICENSE.mysql README support-files

oracle32@/home/mysql/mysql2>./bin/mysql -uroot

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

Your MySQL connection id is 9

Server version: 5.5.8-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

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 databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| test |

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

4 rows in set (0.00 sec)

mysql> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

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

| Tables_in_test |

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

| City |

| Country |

| CountryLanguage |

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

3 rows in set (0.00 sec)

Engine 을 삭제했다가 다시 복원한 후 MySQL 에 접속했을 때 아무런 이상이 없었음을 알 수 있었다.

J. Redolog File 이 손상된 경우 (InnoDB)

oracle32@/home/mysql/mysql/data>rm ib_logfile1

oracle32@/home/mysql/mysql>./bin/mysql -uroot

mysql> insert into hello values('1123');

Query OK, 1 row affected (0.00 sec)

ib_logfile1 을 OS 에서 삭제하였다. 이상태에서 MySQL 접속도 가능했으며 value 입력도 가능했다.

110517 17:23:18 mysqld_safe mysqld from pid file /home/mysql/mysql/data/oracle32.test.com.pid ended

110517 17:25:00 mysqld_safe Starting mysqld daemon with databases from /home/mysql/mysql/data

110517 17:25:00 [Note] Plugin 'FEDERATED' is disabled.

InnoDB: The InnoDB memory heap is disabled

InnoDB: Mutexes and rw_locks use InnoDB's own implementation

InnoDB: Compressed tables use zlib 1.2.3

110517 17:25:00 InnoDB: Using Linux native AIO

110517 17:25:00 InnoDB: Initializing buffer pool, size = 16.0M

110517 17:25:00 InnoDB: Completed initialization of buffer pool

InnoDB: Error: log file /home/mysql/mysql/data/ib_logfile1 is of different size 0 0 bytes

InnoDB: than specified in the .cnf file 0 5242880 bytes!

110517 17:25:00 [ERROR] Plugin 'InnoDB' init function returned error.

110517 17:25:00 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

110517 17:25:00 [ERROR] Unknown/unsupported storage engine: InnoDB

110517 17:25:00 [ERROR] Aborting

110517 17:25:00 [Note] /home/mysql/mysql/bin/mysqld: Shutdown complete

110517 17:25:00 mysqld_safe mysqld from pid file /home/mysql/mysql/data/oracle32.test.com.pid ended

shutdown 후 startup 을 하고자 하니 위와 같은 에러가나며 abort 되었다.

oracle32@/home/mysql/mysql/data>ls -lrt

total 25824

drwx------ 2 mysql mysql 4096 May 15 16:06 performance_schema

-rw-rw---- 1 mysql mysql 993661 May 15 16:06 mysql-bin.000002

-rw-rw---- 1 mysql mysql 27838 May 15 16:06 mysql-bin.000001

drwxr-xr-x 2 mysql mysql 4096 May 15 16:06 mysql

-rw-rw---- 1 mysql mysql 126 May 15 16:10 mysql-bin.000003

-rw-rw---- 1 mysql mysql 1222768 May 15 16:34 mysql-bin.000004

-rw-rw---- 1 mysql mysql 95 May 17 16:59 mysql-bin.index

drwxr-xr-x 2 mysql mysql 4096 May 17 17:20 test

-rw-rw---- 1 mysql mysql 580 May 17 17:22 mysql-bin.000005

-rw-rw---- 1 mysql mysql 5242880 May 17 17:22 ib_logfile0

-rw-rw---- 1 mysql mysql 18874368 May 17 17:22 ibdata1

-rw-rw---- 1 mysql mysql 0 May 17 17:22 ib_logfile1

-rw-rw---- 1 mysql mysql 7919 May 17 17:25 oracle32.test.com.err

oracle32@/home/mysql/mysql/data>rm -rf ib_logfile*

ls -lrt 의 결과를 보니 ib_logfile 의 생성일시는 동일하나 ib_logfile1 에 데이터가 존재하지 않는다. 남아있는 ib_logfile 을 전부 삭제후 다시 start 해보았다.

oracle32@/home/mysql/mysql/data>ls -lrt

total 30968

drwx------ 2 mysql mysql 4096 May 15 16:06 performance_schema

-rw-rw---- 1 mysql mysql 993661 May 15 16:06 mysql-bin.000002

-rw-rw---- 1 mysql mysql 27838 May 15 16:06 mysql-bin.000001

drwxr-xr-x 2 mysql mysql 4096 May 15 16:06 mysql

-rw-rw---- 1 mysql mysql 126 May 15 16:10 mysql-bin.000003

-rw-rw---- 1 mysql mysql 1222768 May 15 16:34 mysql-bin.000004

drwxr-xr-x 2 mysql mysql 4096 May 17 17:20 test

-rw-rw---- 1 mysql mysql 580 May 17 17:22 mysql-bin.000005

-rw-rw---- 1 mysql mysql 18874368 May 17 17:22 ibdata1

-rw-rw---- 1 mysql mysql 5 May 17 17:29 oracle32.test.com.pid

-rw-rw---- 1 mysql mysql 9711 May 17 17:29 oracle32.test.com.err

-rw-rw---- 1 mysql mysql 114 May 17 17:29 mysql-bin.index

-rw-rw---- 1 mysql mysql 107 May 17 17:29 mysql-bin.000006

-rw-rw---- 1 mysql mysql 5242880 May 17 17:29 ib_logfile1

-rw-rw---- 1 mysql mysql 5242880 May 17 17:29 ib_logfile0

mysql> show tables;

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

| Tables_in_test |

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

| City |

| Country |

| CountryLanguage |

| hello |

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

4 rows in set (0.00 sec)

mysql> select * from hello;

+------+

| id |

+------+

| 1121 |

| 1122 |

| 1123 |

+------+

3 rows in set (0.00 sec)

ib logfile 이 재생성되었고 mysql 도 이상없이 Startup 되었다. 또 ib logfile 삭제 직전에 입력했던 데이터 1123 도 이상없이 조회되는 것을 확인하였다.

'SugarCRM > MySQL' 카테고리의 다른 글

myisam innodb 변경 (0)	2013.05.22
mysql (0)	2013.05.22
[SQL] 행열변환 방법 (0)	2012.11.08
PHP Mysql 기본 (0)	2012.11.07
[퍼옴] mysql 조인(Join)에 대하여 (0)	2012.09.22
각 DB 별 테이블 복사 쿼리 구문 MS-SQL (0)	2012.08.26

현재글MySQL:: Backup & Recovery

Dios mio!~~

中國語, 중국어법, Excel, ipod, Setting, music, 애드센스, flex, PMP, Adsense, Google, sugarCRM, 중국어, U43, Eng, TIP, Strida, i-station, Cydia, Firefox,

Today :
Yesterday :

Sixx