MySQL 5.5 Backup & Recovery Scenario v1.0
|
2011.05.11 |
1.0 |
|
|
|
|
|
|
|
|
|
1.
Backup & Recovery
DBMS 의 Recovery 에 앞서 반드시 선행되어야 할 작업이 있다. 그것은
당연히도 Backup 이다. 또 Backup 이 있어도 이를 제대로 사용하지 못해서는 Recovery 가
의미가 없다.
2.
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 테이블이 된다.
3.
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 와 비교했을 때 빠른 백업 / 복구 시간을 가진다는 점이다.
4.
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 와 다른것이다.
5.
SCENARIO
테이블이 의도하지 않게 삭제되었고 이 테이블을 이용하는 쿼리가 있다면 필연적으로 서비스가 영향을 받는다. 서비스가 영향을 받는다는 것은 ‘장애’란 이야기다.
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)
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; +----------+-------+----------+----------+ | Table | Op
| Msg_type | Msg_text | +----------+-------+----------+----------+ | mysql.db |
check | status | OK | +----------+-------+----------+----------+ 1 row in set
(0.01 sec) mysql> check
table db quick; +----------+-------+----------+----------+ | Table | Op
| Msg_type | Msg_text | +----------+-------+----------+----------+ | mysql.db |
check | status | OK | +----------+-------+----------+----------+ 1 row in set
(0.00 sec) mysql> check
table db fast; +----------+-------+----------+-----------------------------+ | Table | Op
| Msg_type | Msg_text | +----------+-------+----------+-----------------------------+ | mysql.db |
check | status | Table is already up
to date | +----------+-------+----------+-----------------------------+ 1 row in set
(0.00 sec) mysql> check
table db medium; +----------+-------+----------+----------+ | Table | Op
| Msg_type | Msg_text | +----------+-------+----------+----------+ | mysql.db |
check | status | OK | +----------+-------+----------+----------+ 1 row in set
(0.00 sec) mysql> check
table db extended; +----------+-------+----------+----------+ | Table | Op
| Msg_type | Msg_text | +----------+-------+----------+----------+ | mysql.db |
check | status | OK | +----------+-------+----------+----------+ 1 row in set
(0.00 sec) mysql> check
table db changed; +----------+-------+----------+-----------------------------+ | Table | Op
| Msg_type | Msg_text | +----------+-------+----------+-----------------------------+ | mysql.db |
check | status | Table is already up
to date | +----------+-------+----------+-----------------------------+ 1 row in set
(0.00 sec) |
Corruption 이 확인되었다면 repair table 을 실시한다.
|
mysql> repair
local table db; +----------+--------+----------+----------+ | Table | Op
| Msg_type | Msg_text | +----------+--------+----------+----------+ | mysql.db |
repair | status | OK | +----------+--------+----------+----------+ 1 row in set
(0.00 sec) mysql> repair no_write_to_binlog
table db; +----------+--------+----------+----------+ | Table | Op
| Msg_type | Msg_text | +----------+--------+----------+----------+ | mysql.db |
repair | status | OK | +----------+--------+----------+----------+ 1 row in set
(0.00 sec) mysql> repair
local table db quick; +----------+--------+----------+----------+ | Table | Op
| Msg_type | Msg_text | +----------+--------+----------+----------+ | mysql.db |
repair | status | OK | +----------+--------+----------+----------+ 1 row in set
(0.00 sec) mysql> repair
local table db extended; +----------+--------+----------+----------+ | Table | Op
| Msg_type | Msg_text | +----------+--------+----------+----------+ | mysql.db |
repair | status | OK | +----------+--------+----------+----------+ 1 row in set
(0.00 sec) mysql> repair
local table db use_frm; +----------+--------+----------+------------------------------------+ | Table | Op
| Msg_type | Msg_text | +----------+--------+----------+------------------------------------+ | mysql.db |
repair | warning | Number of rows
changed from 0 to 2 | | mysql.db |
repair | status | OK | +----------+--------+----------+------------------------------------+ 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) Copyright (c)
2000, 2010, 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
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) Copyright (c)
2000, 2010, 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
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 | |
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> 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 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 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) Copyright (c)
2000, 2010, 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
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 도 이상없이 조회되는 것을 확인하였다.
