• MysqlDump
• 데이터베이스와 테이블 정보 조회
• 데이터베이스 백업/복구
• bin 로그를 저장하도록 설정
• 데이터베이스 복구
• 참고 문헌

MysqlDump

---

 

데이터베이스와 테이블 정보 조회

-- scadadb 데이터베이스에 포함된 테이블별 크기
SELECT TABLE_NAME, round(((data_length + index_length) / 1024 / 1024), 2) "MB"
  FROM information_schema.TABLES
 WHERE table_schema = "scadadb"
 ORDER BY (data_length + index_length) DESC;

-- scadadb 데이터베이스에 포함된 테이블별 레코드 수
SELECT TABLE_NAME, TABLE_ROWS
  FROM information_schema.TABLES
 WHERE table_schema = "scadadb"
 ORDER BY TABLE_ROWS DESC;

 

데이터베이스 백업/복구

#--- Database 스키마 backup
mysqldump  -u${USER}  -p${PASSWD}  --no-data  ${DATABASE}  >  backup_schema.sql

#--- Database backup
mysqldump  -u${USER}  -p${PASSWD}  ${DATABASE}  >  backup_scada.sql
# mysqldump  -u${USER}  -p${PASSWD}  --all-databases  >  backup_scada.sql

#--- Database restore
mysql  -u${USER}  -p${PASSWD}  -D  ${DATABASE}  <  backup_scada.sql
# mysql  -u${USER}  -p${PASSWD}  ${DATABASE}  -e  "source  backup_scada.sql;"

#--- Table backup
mysqldump  -u${USER}  -p${PASSWD}  ${DATABASE}  ${TABLE}  > backup_scada_${TABLE}.sql
mysqldump  -u${USER}  -p${PASSWD}  ${DATABASE}  ${TABLE1}  ${TABLE2}  ${TABLE3}  > backup_scada_tables.sql

#--- Table restore
mysql  -u${USER}  -p${PASSWD}  -D  ${DATABASE}  <  backup_scada_${TABLE}.sql

#--- Table의 partition별 백업
mysqldump  -u${USER}  -p${PASSWD}  --no-create-info --where="statusDatetime < '20199'"  ${DATABASE}  ${TABLE}  > backup_scada_${TABLE}_p2019.sql
mysqldump  -u${USER}  -p${PASSWD}  --no-create-info --where="statusDatetime >= '20199' and statusDatetime < '20209'"  ${DATABASE}  ${TABLE}  > backup_scada_${TABLE}_p20209.sql
mysqldump  -u${USER}  -p${PASSWD}  --no-create-info --where="statusDatetime >= '20209' and statusDatetime < '20219'"  ${DATABASE}  ${TABLE}  > backup_scada_${TABLE}_p20219.sql

​

bin 로그를 저장하도록 설정

데이터베이스 복구시 사용할 binlog를 다음 설정으로 저장할 수 있다.

#--- 복구를 위한 bin 로그 생성
#---     binlog.000001, ~.000002, ~.000003 형태로 저장됨
vi  /etc/my.cnf
    log-bin="binlog"
    max_binlog_size=1G                  #--- 로그 파일 하나당 크기
    expire_logs_days=30                 #--- 30일간 보관

mysql  -uroot  -p  mysql
    show  variables  like  'expire%';
    show  binary  logs;
    purge  master  logs  to  'binlog.000050';   #--- 50 이전 로그 삭제
    
    set  global  expire_logs_days=30;
    show  variables  like  'expire%';
    flush  privileges;

 

데이터베이스 복구

#--- bin 로그 삭제
mysql  -uroot  -p${PASSWD}  mysql
    SHOW  VARIABLES  LIKE  'expire%';
    # SET  GLOBAL  expire_logs_days=3;
    
    SHOW  BINARY  LOGS;
    SHOW  BINLOG  EVENTS  IN  'binlog.000020';              #--- 로그에 저장된 query 보기
    
    PURGE  MASTER  LOGS  To  'binlog.000020';              #--- 20 이전 로그 모두 삭제
    purge  binary  logs  to  'binlog.000020';
    purge  binary  logs  before  '2020-02-03  15:00:00';

#--- bin 로그를 사용하여 복구
mysqlbinlog  --database=${DATABASE}  /var/lib/mysql/binlog.000020  > binlog_000020.sql
# mysqlbinlog  --no-defaults  --database=${DATABASE}  /var/lib/mysql/binlog.000020  > binlog_000020.sql

mysqlbinlog  --database=${DATABASE}  --start-datetime="2020-02-02 01:00:00"  --stop-datetime="2020-02-02 16:00:00"  /var/lib/mysql/binlog.000020  >  binlog_000020.sql
# mysqlbinlog  --database=${DATABASE}  --start-position=1  --stop-position=100000  /var/lib/mysql/binlog.000020  >  binlog_000020.sql

mysql  -u${USER}  -p${PASSWD}  -D  ${DATABASE}  <  binlog_000020.sql
# mysql  -u${USER}  -p${PASSWD}  ${DATABASE}  -e  "source  binlog_000020.sql;"
# mysqlbinlog  --database=${DATABASE}  --start-datetime="2020-02-02 01:00:00"  --stop-datetime="2020-02-02 16:00:00"  /var/lib/mysql/binlog.[0-9]*  |  mysql  -u${USER}  -p${PASSWD}  ${DATABASE}



#--- ibdata1, ib_logfile0, ib_logfile1을 사용하여 복구
#---     ibdata1 : 시스템 tablespace
#---     데이터 사전(테이블 구조를 기록하고 테이블 ID를 ~.ibd 파일과 일치 시킴) 포함
vi  /etc/my.cnf
    innodb_force_recovery=1
    innodb_purge_threads=0

systemctl  restart  mariadb.service
mysqldump  -u${USER}  -p${PASSWD}  ${DATABASE}  >  backup_scada.sql

vi  /etc/my.cnf
    # innodb_force_recovery=1
    # innodb_purge_threads=0

systemctl  restart  mariadb.service
mysql  -u${USER}  -p${PASSWD}  -D  ${DATABASE}  <  backup_scada.sql



#--- ~.frm, ~.ibd 복구
#---     innodb_file_per_table=1 인경우, 테이블당 하나의 ~.ibd 파일이 생성됨
#---     ~.frm : 테이블의 구조
#---     ~.ibd : 테이블의 데이터

 

참고 문헌

• https://engineerhistory.tistory.com/138

• ibdata1 파일 복구 : https://indienote.tistory.com/170

Last modified, 2020.02.02 ~ 2020.02.03, version 0.02

---