서버 운영 관리 [ Tip ]

차동훈 에반젤리스트

MySQL 백업 및 복원

MySQL을 이용하여 DB 관리와 서비스를 하고 있다면 중요한 데이터를 주기적으로 백업할 필요가 있습니다. 

데이터가 삭제되거나 변조되면 서비스에 치명적인 영향을 주기 때문에 백업본을 보관하는것은 필수입니다.

약간의 데이타가 변조되거나 테이블이 깨지는 등 간단한 쿼리로 복구가 가능하다면 상관없지만 예전 상태로

데이터 복구가 힘들다면 최근에 백업된 데이터를 덮어씌우는 방법을 사용하기도 합니다. 

∗ 물론 백업파일이 생성된 시점과 현재 사이의 데이터 유실은 있을 수 있습니다.

 

 

MYSQL 덤프 백업하기


 

• mysqldump 명령어 위치 확인

# ps -ef | grep mysqld

/usr/bin/mysqld ~ 

-> 이런식으로 나온다면 패키지 설치 입니다.

/usr/local/mysql/bin/mysqld ~ 

-> 이런식으로 나온다면 소스 설치 입니다.

 

혹시나 mysqldump 명령어가 정확하게 어디서 불러오는지 확인을 해야합니다.

 

# which mysqldump               

MySQL 소스설치라면 결과값이 /usr/local/mysql/bin/mysqldump 

MySQL 패키지 설치라면 /usr/sbin/mysqldump 

로 나와야합니다. 만약 그게 아니라면 환경변수를 설정하시길 바랍니다.

 * https://help.iwinv.kr/manual/read.html?idx=232     환경변수를 참조하십시오

 

• my.cnf 확인

[mysqld]

max_allowed_packet = 128M

[mysqldump]

max_allowed_packet = 128M

-> 무조건 my.cnf 값에서 max_allowed_packet 값이 동일해야 합니다. 

max_allowed_packet 값이 16M, 32M, 64M, 1024M, 1G 등 여러 값으로 설정해도되나, mysqld 와 mysqldump 의 서로의 값은 일치해야 정상적으로 백업 복원이 됩니다.

일치하지 않으면, 데이터가 제대로 백업이 안되어 기존 데이터들이 복원할때 유실될 수 있습니다.

 

• 특정 데이터베이스 내의 테이블만 백업하고자 할때  

(데이터베이스 용량이 아주 큰 경우가 아니라면 그냥 DB 전체를 하는것을 추천합니다)

# mysqldump -u 아이디 -p DB명 테이블명 > 파일명.sql

 

• 특정 데이터베이스 하나만을 백업하고자 할때

# mysqldump -u 아이디 -p DB명 > 파일명.sql

 

• mysql 전체 데이터베이스 백업

# mysqldump -u root -p  --all-databases > 파일명.sql

  

• 파일명 생성 원칙

DB 파일이라는것을 표기하기 위해 파일 확장자는 .sql 로 정합니다.

파일명은 DB명.테이블명.날짜 조합으로 어떤 DB, TABLE 이며 언제 백업된것인지 파일로 명시해주면 파일을 찾을때 유용합니다.

ex) xe.table-2016-04-12-1300.sql (한눈에 의미를 파악하기 좋습니다)

   

• vi 편집기를 통해 백업된 파일을 열어봐도 sql 백업파일이라는것이 확인됩니다.

-- MySQL dump 10.13 Distrib 5.1.73, for redhat-linux-gnu (x86_64)

--
-- Host: localhost Database: xe
-- ------------------------------------------------------
-- Server version 5.1.73

 

• 복원은 비교적 간단합니다

">" 되어 있는것을 반대로 "<" 이렇게 하면 되며 mysqldump 를 mysql 로 바꿔서 실행하면 됩니다.

백업 명령어

mysqldump -u root -p > alldump_2022_12_02_1030.sql

복원 명령어

mysql -u root -p < alldump_2022_12_02_1030.sql

∗ 유의할점은 현재 운영중인 문제없는 서버에서는 복원하면 안된다는것이며 자료가 날라갈수도 있으니 유의하자.

 

 

MYSQL 덤프 복원


 

• 특정 DB의 테이블을 복구

$ mysql -u 아이디 -p DB명 테이블명 < 파일명.sql

 

• 특정 데이터베이스 하나만을 복원하고자 할때

$ mysql -u 아이디 -p DB명 < 파일명.sql

 

• mysql 전체 데이터베이스 복원

$ mysql -u root -p < 파일명.sql

 

복원할때는 아래와 같이 아무것도 출력 안되면 정상적으로 된것 입니다.

$ mysql -u root -p xe < xe.sql
Enter password: (패스워드 입력)
$

 

∗ mysqldump --help 혹은 man mysqldump 명령어를 입력해보면 보다 자세한 사용법과 옵션등을 볼 수 있습니다.

 

tar 를 이용한 mysql data 파일 백업


 

그외 방법으로는 간단하게 mysql 의 데이터 디렉토리를 통째로 백업하는 방법도 있습니다

 

1. 'ps -ef | grep mysql' 명령어를 실행하면 mysql data 저장 경로를 확인할 수 있습니다.

• /var/lib/mysql 은 대부분 패키지 MySQL의 데이터베이스가 저장되는 디렉토리 공간 입니다.

--datadir=/var/lib/mysql

∗ mysql 설치환경에 따라 디렉토리 위치는 바뀔 수 있습니다.

 

2. tar 압축 명령어를 사용하여 위 데이터 디렉토리를 압축하면 된다.

$ tar cvfz 압축할파일이름.tar.gz /var/lib/www

 

tar 를 이용한 mysql data 파일 복원


 

1. 복원하려면 압축을 풀어야합니다.

$ tar xvfz 압축한파일이름.tar.gz

 

2. 복원후에 mysql 서비스를 재시작 합니다

$ service mysqld restart

$ /etc/init.d/mysqld restart

 

∗ 복원하는것 또한  현재 운영중 (서비스중) 인 서버에는 하지 않습니다. (현재 자료에 덮어씌우기 때문입니다.)

NOTE

DB는 데이터가 가장 중요합니다.

  • 처음에 소개된 mysqldump 를 통한 백업과 tar 압축명령어를 이용하여 백업하고
  • 백업된 파일은 서버와 별도의 저장장치에 각각 이중 보관하는게 안전합니다.

 

 

관련링크