By aPOST —

[sql] MySQL 데이터베이스 내보내기와 가져오기 - 백업과 복원

MySQL 데이터베이스 내보내기

MySQL의 커맨드라인 백업 프로그램인 mysqldump 프로그램을 사용해서 데이터베이스를 SQL 파일로 내보낼 수 있습니다.

백업 순서는 다음과 같습니다.

  1. 데이터베이스 서버가 실행중인 호스트로 로그인합니다. 루트 권한이 있는 계정을 제공하는 웹 호스팅 서비스를 이용하고 있다면 SSH로 터미널 로그인을 합니다.
  2. 다음 명령을 실행해 데이터베이스를 SQL 파일로 덤프합니다. username은 DB계정, dbname은 내보낼 데이터베이스의 이름으로 대체합니다.
mysqldump --routines -u username -p dbname > dbbackup.sql

dbbackup.sql 파일에 데이터베이스의 전체 구조 생성 정보와 데이터가 저장됩니다. 파일 이름은 원하는대로 변경할 수 있습니다.

MySQL 데이터베이스 복원

mysql 프로그램을 사용하여 명령 줄에서 MySQL 데이터베이스를 가져올 수 있습니다. 이를 위해 다음 단계를 따르세요.

  1. SCP, SFTP 또는 FTP를 사용하여 dbbackup.sql 파일을 데이터베이스를 복원할 MySQL DB가 있는 호스트로 전송합니다.
  2. 대상 호스트에 로그인합니다.
  3. dbbackup.sql 파일을 업로드한 디렉토리로 이동한 후, 다음 명령을 입력해 백업 데이터를 복원합니다. username은 복원하는 데이터베이스에 대한 접근 권한이 있는 DB 계정으로, dbname은 데이터를 가져올 데이터베이스의 이름으로 대체합니다.
mysql -u username -p dbname < dbexport.sql

MySQL 데이터베이스 복원할 때 발생하는 에러 메시지들

  • ERROR 1044: Access denied for user 'dbuser1'@'localhost' to database 'new_database'

이 에러 메시지는 dbuser1 사용자가 복원하려는 로컬호스트 DB서버의 "new_database"에 대한 접근 권한이 없음을 나타냅니다. 가져오기 SQL 파일에 권한이 없는 DB 계정으로 데이터베이스에 액세스하는 SQL 문을 포함하고 있을 때 발생합니다. 이 경우 가져오는 sql 파일을 텍스트 편집기에서 열어 dbuser1 계정을 접근 권한이 있는 다른 로컬호스트 DB 계정으로 변경 후 다시 복원 작업을 진행해야 합니다.

또는, 반대로 dbuser1 계정이 없으면 계정을 추가하고 new_database에 대한 접근 권한을 부여해도 됩니다.

  • ERROR 1049: Unknown database 'new_database'

이 오류 메시지는 대상 데이터베이스가 존재하지 않을 때 발생합니다. 데이터베이스를 생성했는지 먼저 확인한 후 없으면 데이터베이스를 먼저 생성해야 합니다. 또는 복원하려는 MySQL서버가 맞는지 확인해야 합니다.

  • ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'username_database' at line x.

이 오류 메시지는 가져오기 SQL 파일이 데이터베이스의 백업 데이터를 포함하지 않거나 파일에 MySQL 구문 오류가 있는 경우에 발생합니다. 가져오기 SQL 파일이 변경되었거나 손상되었거나 지원되지 않는 형식일 수도 있습니다. (가져오기 파일은 SQL 문을 포함해야 하며 CSV와 같은 다른 파일 형식은 mysql 프로그램으로 처리할 수 없습니다.)

  • ERROR 1227: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

이 오류 메시지는 가져오기 SQL 파일에 root 사용자 권한이 필요한 SQL 문(SET GLOBAL 또는 CREATE DEFINER 문과 같은)이 하나 이상 포함되어 있을 때 발생합니다. 경우에 따라서 이러한 문을 SQL 파일에서 삭제하고 가져오기 작업을 다시 진행해야 합니다.