DB crash 확인 및 복구, 백업 및 복원

[MySQL 테이블 손상 확인 및 복구방법]

mysql> use DB명 -> 작업할 DB 선택
mysql> check table 테이블명 -> 확인할 테이블을 체크
mysql> use DB명 -> 작업할 DB 선택
mysql> repair table 테이블명 -> 복구할 테이블 복구
mysql> optimize table [테이블명]; => 최적화할 테이블을 최적화

[MySQL DB 손상 확인 명령어]

mysqlcheck -u root -p --check --database DB명
mysqlcheck -u root -p DB명

[MySQL 특정 DB의 모든 테이블 체크 및 자동 복구]
cd [MySQL_HOME]/bin 
=> MySQL 설치 홈의 bin 폴더로 이동

./mysqlcheck -u [DB계정] -p[패스워드] --auto-repair [DB명] 
=> 해당 DB의 모든 테이블을 체크 및 자동복구

[MySQL 특정 DB의 모든 테이블 최적화]
cd [MySQL_HOME]/bin 
=> MySQL 설치 홈의 bin 폴더로 이동

./mysqlcheck -u [DB계정] -p[패스워드] --optimize [DB명] 
=> 해당 DB의 모든 테이블을 최적화

[crontab을 이용한 주기적 백업]
mkdir -p /root/bin/mysql_backup.sh
chmod 755 /root/bin/mysql_backup.sh

 

vi /root/bin/mysql_backup.sh
========================================================================
#!/bin/bash 

# 필수 변수 지정 
backup_dir="/usr/local/backup/mysql" 
passwd="1234" 

date=`date +%Y%m%d` 
mysqlshow="/usr/local/mysql/bin/mysqlshow" 
mysqldump="/usr/local/mysql/bin/mysqldump" 

# 지난 백업 삭제 , +1 의 경우 3 일 
/usr/bin/find $backup_dir -ctime +7 -exec rm -rf {} \; 

# DB 리스트 추출 db_list=`$mysqlshow -u root -p$passwd | egrep -v "Databases| information_schema|test|^\+" | awk '{print $2}'` 

# 백업 디렉터리 생성 
/bin/mkdir -p $backup_dir/$date 

# 압축 여부 1 은 압축 , 0 은 비압축 
tar="1" 

# 덤프 시작 ~ 
for db_lists in $db_list; do 
$mysqldump -u root -p$passwd --skip-lock-tables $db_lists > $backup_dir/ $date/$db_lists.sql 
done 

# 만약 tar 가 1 ( 압축 ) 이라면 , 
if [ $tar = '1' ]; then 
cd $backup_dir 
/bin/tar zcvfp $backup_dir/$date.tar.gz $date 
/bin/rm -rf $date 
else 
exit 0 
fi
========================================================================
- 백업스크립트 실행권한 주기
chmod 755 /root/bin/mysql_backup.sh

- crontab 설정
/etc/crontab에 해당 부분을 추가해준다. 23시에 백업스크립트 돌아가게 설정하였다.
백업은 시스템의 부하를 줄 수 있으니 새벽 시간에 스크립트를 구동시키는 것을 권장한다.

vi /etc/crontab
00 23 * * * root /bin/sh /root/bin/mysql.sh 

- crond 데몬 재 실행
# /etc/init.d/crond restart

- 확인
[root@localhost mysql]# ll
합계 212
drwxr-xr-x 2 root root 4096 2017-09-24 23:00 ./
drwxr-xr-x 3 root root 4096 2017-09-24 22:18 ../
-rw-r--r-- 1 root root 207785 2017-09-24 23:00 20170924.tar.gz
23시에 mysql 백업이 된 것을 확인할 수 있다.
========================================================================

특정 DB를 백업/복구 방법 - 논리적 백업/복구 

[MySQL 백업] 
mysqldump -u root -p 데이터베이스명 > 백업할파일명.sql 

전체 데이터베이스 백업 
mysqldump -u [userID] -p --all-databases > [dump명].sql

특정 데이터베이스 백업 
mysqldump -u [userID] -p --databases [DB명] > [dump 명].sql

특정 테이블 백업 
mysqldump -u [userID] -p [DB명] [테이블명] > [dump명].sql
mysqldump -u [userID] -p -B DB명 –tables 테이블명1 테이블명2 > [dump명].sql

캐릭터셋 옵션을 이용하여 백업
mysqldump -u root -p –default-character-set=euckr DB명 > 파일명.sql
(euckr, utf8 등이 있음)

테이블 구조만 백업
mysqldump -u root -p –no-data DB명 > 파일명.sql

XML 파일로 백업
mysqldump -u root -p –xml DB명 > 파일명.sql

[MySQL 복원]
전체 데이터베이스 복원 
mysql -u [userID] -p < [dump명].sql

특정 데이터베이스 복원 
mysql -u [userID] -p [DB명] < [dump명].sql

특정 테이블 복원 
mysql -u [userID] -p [DB명] < [테이블dump명].sql

캐릭터셋 옵션을 이용하여 백업 복구
mysqldump -u root -p –default-character-set=euckr DB명 < 파일명.sql
(euckr, utf8 등이 있음)

[source를 이용한 복원]
일단 백업한파일이 있는 디렉토리로 이동 
ex) cd /usr/local/backup
ls
alldatabase.sql (전체)
testdatabase.sql (데이터베이스 1개)
tabledatabase.sql (테이블데이터 1개)

mysql -u root -p

접속후
mysql> show databases; 로 데이터베이스 확인

전체 데이터베이스 소스복원
mysql> source alldatabase.sql

특정 데이터베이스 소스복원
mysql> use funshop_db;
mysql> source testdatabase.sql;

특정테이블 데이터베이스 소스복원
mysql> use funshop_db;
mysql> source tabledatabase.sql;

옵션
-d, --no-data : 데이터는 제외하고 스키마만 덤프
-t, --no-create-info : 스키마는 제외하고