[MySQL] MySQL backup and recovery

운영서버 중에 갑작스레 MySQL을 DB로 사용하는 서비스가 생겨서 공부한 것을 남겨놓습니다.

(그동안엔 Oracle DBMS만 사용했기에 MySQL은 이제 공부하는 중입니다.)

MySQL Backup and Recovery

- MySQL 디렉토리 전체를 압축 백업하기(DB엔진 백업)

MySQL Directory : /var/lib/mysql

# tar cvfpz mysql_dir.tar.gz /var/lib/mysql

- mysqldump 사용법

# mysqldump -u [userid] -p [password] --all-databases > dump.sql

# mysqldump -u [userid] -p [password] --databases [DB명] > dump.sql

# mysqldump -u [userid] -p [password] [DB명] [테이블명]

기타옵션

-A, --all-databases : 모든 DB 덤프

-B, --databases : 특정 DB 덤프

--opt : 버퍼링을 비활성화 하고, 많은 데이터를 덤프에 있는 소량의 SQL구문에 기록. 더 효율적으로 동작.

--allow-keywords, --quote-names : 예약어를 사용하는 테이블을 덤프하고 복원할 수 있게 함.

--lock-alltables : 전역적으로 일관된 백업을 만들도록 "FLUSH TABLES WITH READ LOCK"을 사용

--tab : "SELECT INTO OUTFILE"로 파일을 덤프하여, 덤프 및 복원속도가 매우 빠름.

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

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

- dump 파일 복원 방법

# mysql -u[userid] -p[password] [DB명] < dump.sql

혹은 mysql에 접속한 상태에서 아래의 명령 실행

mysql > source dump.sql

- 특정 데이터베이스 백업과 복구 예제

백업

# mysqldump -u userid -p password --database mysql > mysqldb_20130327.sql

복구 (단, 해당 DB가 존재하고 있어야 함)

# mysql -u userid -p password --database mysql < mysqldb_20130327.sql

- 여러 데이터베이스 일괄 백업 및 복구 예제

백업

# mysqldump -u userid -p password --databases testdb1 testdb2 > multidb_20130327.sql

복구 (단, 해당 DB가 존재하고 있어야 함)

# mysql -u userid -p password < multidb_20130327.sql

- 전체 데이터베이스 백업 및 복구 예제

백업

# mysql -u userid -p password --all-databases > alldb.sql

복구 (단, 해당 DB가 존재하고 있어야 함)

# mysql -u userid -p password < alldb.sql

** 참고 : MySQL DB생성 **

# mysqladmin -u root -p password create mysql