Page tree

Contents


간단한 mysql 백업 솔루션인 mysqldump 주요 옵션과 사용 예제를 정리

용법

Dumping structure and contents of MySQL databases and tables.


Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]

주요 옵션

옵션의미Default비고
-A, --all-databases모든 db dump

--databases

dump 할 db 명 기술하면 해당 db 만 dump
--database 옵션이 주어질 경우 CREATE DATABASE 과 USE 구문이 덤프 파일의 첫 부분에 추가됨.
--add-drop-databaseDROP DATABASE 구문 추가하여 create database 전에 기존 database 삭제(error)기존 데이타가 삭제되니 주의
--add-drop-tableDROP TABLE 구문 추가하여 table 생성전 기존 table 삭제(tick)drop table 을 제외하려면 --skip-add-drop-table 옵션 사용
-l, --lock-tablesLock all tables for read.(tick)--skip-lock-tables 로 제외가능. 이 옵션보다는 아래의 --single-transaction 사용 추천
--single-transactionlock 을 걸지 않고도 dump 파일의 정합성 보장(error)InnoDB 일때만 사용 가능
--ignore-table제외할 테이블명을 주면 dump에서 제외(error)여러개의 테이블을 제외할 경우 테이블명마다 앞에 --ignore-table 옵션을 주어야 함. , 로 여러개의 테이블을 주어도 한 테이블만 처리
-n, --no-create-dbCREATE DATABASE 구문 제외(error)dump한 파일을 load 하려는 환경의 database 명이 다를 경우 유용함
-t, --no-create-infoCREATE TABLE 구문 제외(error)dump한 이후에 load 하려는 환경의 table 스키마가 추가/변경되었을 경우 유용함
-c, --complete-insertINSERT INTO 구문 생성시 컬럼 이름도 포함(error)dump한 이후에 load 하려는 환경의 table 스키마가 추가/변경되었을 경우 유용(Column count doesn't match value count 에러 발생시)
--max_allowed_packet 서버와 주고 받을 최대 패킷 사이즈 설정1M첨부파일등이 DB 에 있을 경우 이 값을 크게 해야 한다. mysqldump: ‘max_allowed_packet’ 에러 처리 참고
--triggerstrigger 도 dump(tick)--skip-triggers 로 제외할 수 있음
--routinesstored procedure 와 function 도 dump(error)MySQL 5 는 trigger는 기본적으로 덤프하나 function, procedure는 덤프하지 않음


사용예

모든 db 및  stored procedure/function 도 백업

mysqldump --single-transaction –-routines --all-databases -h localhost -u root -pmypwd > mydump.sql

db1, db2 만 백업

mysqldump  --single-transaction --databases db1 db2 -h localhost -u root -pmypwd > mydump.sql

db1만 백업하고 CREATE 구문 미포함

--no-create-db 옵션을 사용하면 CREATE SCHEMA 가 제외됨.


mysqldump  --single-transaction db1  --no-create-db -h localhost -u root -pmypwd > mydump.sql


USE `DATABASE` 구문 제외

--databases 옵션을  제거하고 직접 dump 할 데이타베이스 이름(예:EXPORT_DB_NAME )을 입력하면 USE `DATABASE` 구문이 생략되서 덤프됨.


mysqldump  --single-transaction db1 -u root -pmypwd EXPORT_DB_NAME > mydump.sql

CREATE  구문 미포함 + INSERT 에 컬럼명 추가

mysqldump  --single-transaction --no-create-db  --no-create-info --complete-insert -h localhost -u root -pmypwd > mydump.sql

컬럼명을 포함할 경우 primary key 가 auto increment 일 경우 아래 에러가 발생하고 insert 실패함. 이럴 경우 mysqldump 로는 방법이 없고 별도의 VIEW 를 만들고 view 를 export 하거나 SELECT INTO 구문을 사용해야 함

ERROR 1062 (23000) at line 445: Duplicate entry '1' for key 'PRIMARY'


db1만 포함하고 db내 table1, table2만 제외

mysqldump  --single-transaction --databases db1 --ignore-table=db1.table1  --ignore-table=db1.table2 -h localhost -u root -pmypwd > mydump.sql

dump 파일 복구

db2 에 복구

mysql -h dbhost2 -u root -pmypwd db2 <  mydump.sql


cron 으로 자동 백업

mysql_backup.sh
#!/bin/sh

## yyyy-mm-dd_hh:mm format
TIME=$(date "+%Y-%m-%d_%H:%M")
 
mysqldump  --single-transaction –-routines --all-databases -h localhost -u root -pmypwd > ${TIME}-dump.sql


crontab -e 명령어로 스케줄러 등록

## 9시부터 저녁 7시까지 매시 정각에 백업 수행
0 9-19 * * *  ${HOME}/mysql_dump.sh


같이 보기


Ref

  • No labels

2 Comments

  1. Anonymous

    유명한 정보 감사드립니다!

    출처 기재후 주요 옵션 등을 제 블로그에 다시 정리해서 쓰고 싶은데 괜찮을까요??? 

    원치 않으시거나 문제가 있을시 hyunjoon0312@gmail.com 으로 연락부탁드리겠습니다.

    바로 삭제하도록 하겠습니다.

    감사합니다.

  2. 저도 인터넷에 있는 정보를 제 필요에 맞게 정리해서 게재한 것이니 출처를 넣어주신다면 다시 정리하셔서 기재하시는건 당연히 가능합니다.