뭐 사람이 실수를 할수도 있다고 하지만 운영중인 데이터베이스가 innodb로 운영되고 있다
다행히 innodb_file_per_table 옵션이 활성화 되어 있어서 각 테이블당 ibd 파일이 있다
근데 내가 모르고 ibdata1 파일을 삭제 해 버렸다. 젠장 ㅠㅠ / 즉 ibdata1으로 모든 파일이 저장되고 있었는데 이것을 모르고
내가 삭제한 것이다. 왜? 각 데이터베이스 폴더아래 ibd 파일이 존재하니 당연 없어도 된다고 생각했지..
좀 복잡하게 꼬였는데 원래 처음에 innodb_file_per_table 이 기본 0 으로 운영되고 있었다는 것
근데 이것을 옵션을 innodb_file_per_table=1 로 변경해서 사용했던 것이다. 그래서 테이블당 ibd 파일이 생성되었고
innodb_file_per_table=1 로 운영하면 ibdata1에서 데이터를 가져오는게 아니고 ibd에서 가져와야 하는데 초기 ibdata1에서 가져와서
옵션을 변경해도 처음 ibdata1 파일을 참고하나 보다.
ibdata1 : 일반적으로 system tablespace
frm : 각 테이블의 테이블 구조를 보관
ibd : 각 테이블당 데이터가 저장되는 파일
옵션에 따라 데이터가 저장되는 방식이 다름
innodb_file_per_table=0 : ibdata1 파일 하나로 데이터 저장
innodb_file_per_table=1 : 데이터베이스 내 테이블당 ibd 파일로 데이터 저장
조회
|
1 2 3 4 5 6 |
mysql> show global variables like 'innodb_file_per_table'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ 1 row in set (0.08 sec) |
데이터베이스 들어가서 쿼리를 실행하면 에러가 발생한다
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
MariaDB [ksms]> show tables; +----------------------+ | Tables_in_ksms | +----------------------+ | agent_conf | | agent_list | | agent_update | | cpu_info | | default_server | | hdd_info | | hdd_iops_info | | hdd_smartctl | | load_info | | mysql_default | | mysql_info | | network_info | | nobody_check | | nobody_check_history | | nobody_keyword | | process_info | | service_status | +----------------------+ 17 rows in set (0.001 sec) MariaDB [ksms]> select count(*) from servcie_status; ERROR 1146 (42S02): Table 'ksms.servcie_status' doesn't exist |
이렇게 테이블 에러가 발생한다. 왜 ? ibdata1 파일에 정보가 담겨져 있었는데 삭제가 되었으니. 그럼 난 이제 끝난건가?
아니다 나에게는 ibd 파일이 존재한다. 이 파일에 데이터가 저장되어 있기 때문에 복구가 가능하다 그럼 복구해볼까나.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
[root@localhost ~]# ls -lShr /var/lib/mysql/ksms/ 합계 1019M -rw-rw---- 1 mysql mysql 61 12월 19 16:03 db.opt -rw-rw---- 1 mysql mysql 1017 12월 19 17:56 agent_conf.frm -rw-rw---- 1 mysql mysql 1.9K 12월 19 17:56 hdd_info.frm -rw-rw---- 1 mysql mysql 2.0K 12월 19 17:59 network_info.frm -rw-rw---- 1 mysql mysql 2.0K 12월 19 17:56 cpu_info.frm -rw-rw---- 1 mysql mysql 2.0K 12월 19 17:58 mysql_default.frm -rw-rw---- 1 mysql mysql 2.1K 12월 19 18:00 process_info.frm -rw-rw---- 1 mysql mysql 2.1K 12월 19 17:57 hdd_iops_info.frm -rw-rw---- 1 mysql mysql 2.2K 12월 19 18:00 nobody_check_history.frm -rw-rw---- 1 mysql mysql 2.3K 12월 19 17:58 mysql_info.frm -rw-rw---- 1 mysql mysql 2.4K 12월 19 17:57 hdd_smartctl.frm -rw-rw---- 1 mysql mysql 2.4K 12월 19 17:57 load_info.frm -rw-rw---- 1 mysql mysql 2.6K 12월 19 18:00 service_status.frm -rw-rw---- 1 mysql mysql 2.7K 12월 19 18:00 nobody_check.frm -rw-rw---- 1 mysql mysql 2.8K 12월 19 17:56 default_server.frm -rw-rw---- 1 mysql mysql 3.5K 12월 19 17:56 agent_update.frm -rw-rw---- 1 mysql mysql 5.8K 12월 19 17:56 agent_list.frm -rw-rw---- 1 mysql mysql 31K 12월 19 18:00 nobody_keyword.frm -rw-rw---- 1 mysql mysql 96K 12월 19 18:00 nobody_keyword.ibd -rw-rw---- 1 mysql mysql 96K 12월 19 17:56 agent_conf.ibd -rw-rw---- 1 mysql mysql 112K 12월 19 18:00 process_info.ibd -rw-rw---- 1 mysql mysql 112K 12월 19 18:00 nobody_check_history.ibd -rw-rw---- 1 mysql mysql 112K 12월 19 17:58 mysql_default.ibd -rw-rw---- 1 mysql mysql 112K 12월 19 17:56 cpu_info.ibd -rw-rw---- 1 mysql mysql 160K 12월 19 17:56 agent_update.ibd -rw-rw---- 1 mysql mysql 176K 12월 19 18:00 service_status.ibd -rw-rw---- 1 mysql mysql 192K 12월 19 17:57 hdd_smartctl.ibd -rw-rw---- 1 mysql mysql 208K 12월 19 17:56 default_server.ibd -rw-rw---- 1 mysql mysql 208K 12월 19 17:56 agent_list.ibd -rw-rw---- 1 mysql mysql 256K 12월 19 18:00 nobody_check.ibd -rw-rw---- 1 mysql mysql 13M 12월 19 17:57 hdd_iops_info.ibd -rw-rw---- 1 mysql mysql 124M 12월 19 17:59 mysql_info.ibd -rw-rw---- 1 mysql mysql 220M 12월 19 18:00 network_info.ibd -rw-rw---- 1 mysql mysql 300M 12월 19 17:58 load_info.ibd -rw-rw---- 1 mysql mysql 360M 12월 19 17:57 hdd_info.ibd |
그럼 먼저 ibd frm 파일이 있는 데이터베이스 파일을 백업하자 경로는 우선 임시로 otp 폴더로
|
1 |
[root@localhost ~]# cp -a /var/lib/mysql/ksms /opt/ |
frm 파일이 테이블 구조를 가진 정보인데 이게 테이블 구조를 그냥은 확인이 안되기 때문에 dbsake라는 프로그램을 이용해서 테이블 구조를 확인해보자
dbsake 아닌 mysqlfrm 명령어를 이용해서 가능한데 dbsake가 훨씬 편함
|
1 2 3 4 5 6 |
[root@localhost ~]# curl -s http://get.dbsake.net > dbsake [root@localhost ~]# chmod 700 dbsake [root@localhost ~]# ./dbsake --version dbsake, version 2.1.2 (git: 50de953) |
간단하게 1개의 테이블만 테이블 구조 확인
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
[root@localhost ~]# ./dbsake frmdump /var/lib/mysql/ksms/load_info.frm -- -- Table structure for table `load_info` -- Created with MySQL Version 10.4.7 -- CREATE TABLE `load_info` ( `m_idx` bigint(100) unsigned NOT NULL AUTO_INCREMENT, `m_uuid` varchar(50) DEFAULT NULL, `m_host` varchar(50) DEFAULT NULL, `m_ip` varchar(20) DEFAULT NULL, `load1` float DEFAULT NULL, `load5` float DEFAULT NULL, `load15` float DEFAULT NULL, `m_time` timestamp DEFAULT NULL, PRIMARY KEY (`m_idx`), KEY `m_uuid` (`m_uuid`,`m_host`,`m_ip`,`m_time`), KEY `m_host` (`m_host`,`m_ip`,`m_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
ㅎㅎ 테이블 구조를 확인이 가능하네
근데 이런식으로 여러개 있는 테이블을 만드려면 힘들기 때문에 * 찍어서 sql 파일로 만들자
|
1 |
[root@localhost ~]# ./dbsake frmdump /var/lib/mysql/ksms/*.frm > ksms_frm_data.sql |
그리고 기존 데이터베이스를 삭제하는데 drop database ksms 먹히지 않기 때문에 폴더를 삭제해준다
|
1 |
[root@localhost ~]# rm -rf /var/lib/mysql/ksms/ |
그리고 다시 데이터베이스를 mysql에서 생성
|
1 2 3 4 5 |
MariaDB [(none)]> create database ksms; Query OK, 1 row affected (0.000 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.001 sec) |
이제 dbsake 를 이용해사 만든 전체 테이블 구조를 mysql에서 import 진행한다
|
1 |
[root@localhost ~]# mysql -u root -p ksms < ksms_frm_data.sql |
데이터베이스 들어가서 확인해 보자
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
MariaDB [ksms]> desc load_info; +--------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+----------------------+------+-----+---------+----------------+ | m_idx | bigint(100) unsigned | NO | PRI | NULL | auto_increment | | m_uuid | varchar(50) | YES | MUL | NULL | | | m_host | varchar(50) | YES | MUL | NULL | | | m_ip | varchar(20) | YES | | NULL | | | load1 | float | YES | | NULL | | | load5 | float | YES | | NULL | | | load15 | float | YES | | NULL | | | m_time | timestamp | YES | | NULL | | +--------+----------------------+------+-----+---------+----------------+ 8 rows in set (0.002 sec) MariaDB [ksms]> select count(*) from load_info; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.001 sec) |
테이블 구조만 만들어졌고 자료는 없기 때문에 데이터가 0으로 출력 그럼 이제 ibd 백업 파일로 복원 해보자
우선 현재 ibd 파일이 새롭게 생성되어 있다
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
[root@localhost ~]# ls -lShr /var/lib/mysql/ksms/ 합계 2.1M -rw-rw---- 1 mysql mysql 61 12월 20 11:21 db.opt -rw-rw---- 1 mysql mysql 1017 12월 20 11:22 agent_conf.frm -rw-rw---- 1 mysql mysql 1.9K 12월 20 11:22 hdd_info.frm -rw-rw---- 1 mysql mysql 2.0K 12월 20 11:22 network_info.frm -rw-rw---- 1 mysql mysql 2.0K 12월 20 11:22 cpu_info.frm -rw-rw---- 1 mysql mysql 2.0K 12월 20 11:22 mysql_default.frm -rw-rw---- 1 mysql mysql 2.1K 12월 20 11:22 process_info.frm -rw-rw---- 1 mysql mysql 2.1K 12월 20 11:22 hdd_iops_info.frm -rw-rw---- 1 mysql mysql 2.2K 12월 20 11:22 nobody_check_history.frm -rw-rw---- 1 mysql mysql 2.3K 12월 20 11:22 mysql_info.frm -rw-rw---- 1 mysql mysql 2.4K 12월 20 11:22 hdd_smartctl.frm -rw-rw---- 1 mysql mysql 2.4K 12월 20 11:22 load_info.frm -rw-rw---- 1 mysql mysql 2.6K 12월 20 11:22 service_status.frm -rw-rw---- 1 mysql mysql 2.7K 12월 20 11:22 nobody_check.frm -rw-rw---- 1 mysql mysql 2.8K 12월 20 11:22 default_server.frm -rw-rw---- 1 mysql mysql 3.5K 12월 20 11:22 agent_update.frm -rw-rw---- 1 mysql mysql 5.8K 12월 20 11:22 agent_list.frm -rw-rw---- 1 mysql mysql 31K 12월 20 11:22 nobody_keyword.frm -rw-rw---- 1 mysql mysql 96K 12월 20 11:22 nobody_keyword.ibd -rw-rw---- 1 mysql mysql 96K 12월 20 11:22 agent_conf.ibd -rw-rw---- 1 mysql mysql 112K 12월 20 11:22 process_info.ibd -rw-rw---- 1 mysql mysql 112K 12월 20 11:22 nobody_check_history.ibd -rw-rw---- 1 mysql mysql 112K 12월 20 11:22 network_info.ibd -rw-rw---- 1 mysql mysql 112K 12월 20 11:22 mysql_info.ibd -rw-rw---- 1 mysql mysql 112K 12월 20 11:22 mysql_default.ibd -rw-rw---- 1 mysql mysql 112K 12월 20 11:22 hdd_smartctl.ibd -rw-rw---- 1 mysql mysql 112K 12월 20 11:22 hdd_iops_info.ibd -rw-rw---- 1 mysql mysql 112K 12월 20 11:22 hdd_info.ibd -rw-rw---- 1 mysql mysql 112K 12월 20 11:22 default_server.ibd -rw-rw---- 1 mysql mysql 112K 12월 20 11:22 cpu_info.ibd -rw-rw---- 1 mysql mysql 128K 12월 20 11:22 service_status.ibd -rw-rw---- 1 mysql mysql 128K 12월 20 11:22 nobody_check.ibd -rw-rw---- 1 mysql mysql 128K 12월 20 11:22 load_info.ibd -rw-rw---- 1 mysql mysql 160K 12월 20 11:22 agent_update.ibd -rw-rw---- 1 mysql mysql 176K 12월 20 11:22 agent_list.ibd |
기존에 연동된 데이터를 해제한다 샘플로 1개 테이블
|
1 2 |
MariaDB [ksms]> alter table load_info discard tablespace; Query OK, 0 rows affected (0.129 sec) |
백업해둔 ibd 파일을 복사 및 권한 변경
|
1 2 3 |
[root@localhost ~]# cp -a /opt/ksms/load_info.ibd /var/lib//mysql/ksms/ [root@localhost ~]# chown mysql:mysql /var/lib/mysql/ksms/load_info.ibd |
백업해둔 ibd 파일은 현재 데이터베이스로 연동(매칭작업)
|
1 2 |
MariaDB [ksms]> alter table load_info import tablespace; Query OK, 0 rows affected, 1 warning (4.080 sec) |
그럼 데이터가 있는지 확인해볼까나.
|
1 2 3 4 5 6 7 |
MariaDB [ksms]> select count(*) from load_info; +----------+ | count(*) | +----------+ | 1018486 | +----------+ 1 row in set (0.906 sec) |
ㅎㅎ 자료가 있다. 그럼 실제 데이터베이스의 자료 데이터가 어떻게 되어있나 자료가 들어갔기 때문에 128k에서 300M 변경이 되었다
|
1 2 3 |
[root@localhost ~]# ls -lShr /var/lib/mysql/ksms/load_info.* -rw-rw---- 1 mysql mysql 2.4K 12월 20 11:22 /var/lib/mysql/ksms/load_info.frm -rw-rw---- 1 mysql mysql 300M 12월 20 11:28 /var/lib/mysql/ksms/load_info.ibd |
그럼 이제 테이블 1개 복원했네. 근데 내가 복원 할 테이블은 20~30여개.. 이거 언제 노가다 작업 하니
이럴때 쓰는게 스크립트지
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
[root@localhost ~]# vi ibd_recover.sh #!/bin/bash echo "show tables " | mysql -u root -pqqq123##$$ ksms | grep -v "Tables_in_" > /tmp/table_list.txt for i in `cat /tmp/table_list.txt` do echo "alter table $i discard tablespace;" | mysql -u root -pqqq123##$$ ksms cp -a /opt/ksms/${i}.ibd /var/lib/mysql/ksms/ chown mysql:mysql /var/lib/mysql/ksms/${i}.ibd echo "alter table $i import tablespace;" | mysql -u root -pqqq123##$$ ksms done |
스크립트 실행
|
1 |
[root@localhost ~]# sh ibd_recover.sh |
이제 파일들을 확인해보자 / 정상적으로 ibd 파일의 용량이 늘어난것을 확인 할 수있다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
[root@localhost ~]# ls -lShr /var/lib/mysql/ksms/ 합계 1019M -rw-rw---- 1 mysql mysql 61 12월 20 11:21 db.opt -rw-rw---- 1 mysql mysql 1017 12월 20 11:22 agent_conf.frm -rw-rw---- 1 mysql mysql 1.9K 12월 20 11:22 hdd_info.frm -rw-rw---- 1 mysql mysql 2.0K 12월 20 11:22 network_info.frm -rw-rw---- 1 mysql mysql 2.0K 12월 20 11:22 cpu_info.frm -rw-rw---- 1 mysql mysql 2.0K 12월 20 11:22 mysql_default.frm -rw-rw---- 1 mysql mysql 2.1K 12월 20 11:22 process_info.frm -rw-rw---- 1 mysql mysql 2.1K 12월 20 11:22 hdd_iops_info.frm -rw-rw---- 1 mysql mysql 2.2K 12월 20 11:22 nobody_check_history.frm -rw-rw---- 1 mysql mysql 2.3K 12월 20 11:22 mysql_info.frm -rw-rw---- 1 mysql mysql 2.4K 12월 20 11:22 hdd_smartctl.frm -rw-rw---- 1 mysql mysql 2.4K 12월 20 11:22 load_info.frm -rw-rw---- 1 mysql mysql 2.6K 12월 20 11:22 service_status.frm -rw-rw---- 1 mysql mysql 2.7K 12월 20 11:22 nobody_check.frm -rw-rw---- 1 mysql mysql 2.8K 12월 20 11:22 default_server.frm -rw-rw---- 1 mysql mysql 3.5K 12월 20 11:22 agent_update.frm -rw-rw---- 1 mysql mysql 5.8K 12월 20 11:22 agent_list.frm -rw-rw---- 1 mysql mysql 31K 12월 20 11:22 nobody_keyword.frm -rw-rw---- 1 mysql mysql 96K 12월 20 11:37 nobody_keyword.ibd -rw-rw---- 1 mysql mysql 96K 12월 20 11:37 agent_conf.ibd -rw-rw---- 1 mysql mysql 112K 12월 20 11:37 process_info.ibd -rw-rw---- 1 mysql mysql 112K 12월 20 11:37 nobody_check_history.ibd -rw-rw---- 1 mysql mysql 112K 12월 20 11:37 mysql_default.ibd -rw-rw---- 1 mysql mysql 112K 12월 20 11:37 cpu_info.ibd -rw-rw---- 1 mysql mysql 160K 12월 20 11:37 agent_update.ibd -rw-rw---- 1 mysql mysql 176K 12월 20 11:37 service_status.ibd -rw-rw---- 1 mysql mysql 192K 12월 20 11:37 hdd_smartctl.ibd -rw-rw---- 1 mysql mysql 208K 12월 20 11:37 default_server.ibd -rw-rw---- 1 mysql mysql 208K 12월 20 11:37 agent_list.ibd -rw-rw---- 1 mysql mysql 256K 12월 20 11:37 nobody_check.ibd -rw-rw---- 1 mysql mysql 13M 12월 20 11:37 hdd_iops_info.ibd -rw-rw---- 1 mysql mysql 124M 12월 20 11:37 mysql_info.ibd -rw-rw---- 1 mysql mysql 220M 12월 20 11:37 network_info.ibd -rw-rw---- 1 mysql mysql 300M 12월 20 11:37 load_info.ibd -rw-rw---- 1 mysql mysql 360M 12월 20 11:37 hdd_info.ibd |
다른 테이블도 확인 해 볼까
|
1 2 3 4 5 6 7 |
MariaDB [ksms]> select count(*) from hdd_info; +----------+ | count(*) | +----------+ | 581451 | +----------+ 1 row in set (0.693 sec) |
ibdata1 파일의 용량도 체크
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
[root@localhost ~]# ls -lShr /var/lib//mysql 합계 471M srwxrwxrwx 1 mysql mysql 0 12월 19 18:26 mysql.sock -rw-rw---- 1 mysql mysql 0 12월 19 16:01 multi-master.info -rw-rw---- 1 mysql mysql 5 12월 19 18:26 localhost.pid drwx------ 2 mysql mysql 20 12월 19 16:00 test drwx------ 2 mysql mysql 20 12월 19 16:00 performance_schema -rw-rw---- 1 mysql mysql 52 12월 19 18:25 aria_log_control -rw-rw---- 1 mysql mysql 860 12월 19 18:25 ib_buffer_pool drwx------ 2 mysql mysql 4.0K 12월 19 18:00 mysql drwx------ 2 mysql mysql 4.0K 12월 20 11:37 ksms -rw-rw---- 1 mysql mysql 56K 12월 19 18:28 aria_log.00000001 -rw-rw---- 1 mysql mysql 12M 12월 19 18:26 ibtmp1 -rw-rw---- 1 mysql mysql 74M 12월 20 11:37 ibdata1 -rw-rw---- 1 mysql mysql 128M 12월 19 18:02 ib_logfile2 -rw-rw---- 1 mysql mysql 128M 12월 19 18:02 ib_logfile1 -rw-rw---- 1 mysql mysql 128M 12월 20 11:37 ib_logfile0 |
이제 1개의 데이터베이스 작업완료 했다. 여러개의 데이터베이스면 노가다 하던다 아니면 스크립트로 짜셔
작업하면 된다
결론 : 절대 파일은 함부로 삭제하거나 지우지 말고 백업해 두고 사용하자.