mysqldump 백업 M [출처] mysqldump 백업|작성자 마루아라

mysqldump를 이용한 백업하는 법입니다.

1) 전체 DB를 백업
mysqldump -u myhome -p  [Option] -A > mysql_backup_all.sql

mysqldump -u myhome -p [Option] --all-databases > mysql_backup_all.sql

2) 데이타베이스별로 백업 (--databases 뒤에 myhome mysql 등 줄줄이 나열함)
mysqldump -u myhome -p  [Option] --databases myhome mysql > mysql_backup.sql

3) 특정 테이블 백업 (myhome의 tablea만 백업)
mysqldump -u myhome -p  [Option] --myhome tablea > mysql_backup_tablea.sql

 

[Option] 설명
--quick : C API의 mysql_use_result()함수를 사용하므로 속도가 빠르다.
--add-drop-table : 덤프 결과에서 create table 앞에 drop table 절을 추가합니다.

-A, --all-databases : DB전체 백업
--add-locks : 덤프하기 전에 LOCK table을 이용하여 테이블을 잠그고 덤프후 UNLOCK table을 합니다.
-e, --extended-insert : insert 문 하나에서 모든 레코드를 삽입하는 insert 문을 생성합니다.
--opt : 위에 나온 옵션을 동시에 사용한 것과 같은 결과를 가져옵니다.  opt옵션을 사용해서 백업을 하면 완전한 insert문이 만들어지지 않

          고, 메모리를 많이 차지하지 않기때문에 대용량 디비를 백업하는데는 보다 안정적이라 할 수 있다.

           --opt 옵션은 디폴트로 정해져 있으므로 이 옵션을 끄기위해서는 --skip-opt 옵션을 걸어줘야 한다
--no-data(-d) : 테이블의 스키마만 덤프합니다.
--no-create-info(-t) : create table을 출력하지 않고 데이터만 덤프합니다.

-c, --complete-insert : 필드명까지 완벽한 insert 문을 만들어 줍니다.

--default-character-set=utf8 , euckr(euc_kr:옛버전), latin1... : 기본 언어셋 지정

가장 일반적인 명령...
shell>mysql -u chang -p --add-drop-table -e kimch > table_name.sql

shell>mysql -u chang -p kimch > table_name.sql
[ 결과 ]
INSERT INTO zipcode VALUES ('100011','100011','서울','중구','충무로1가','','',NULL,NULL),('100012','100012','서울','중구','충무로2가','','',NULL,NULL) ~~

shell>mysql -u chang -p --skip-opt kimch > table_name.sql
[ 결과 ]
INSERT INTO zipcode VALUES ('100011','100011','서울','중구','충무로1가','','',NULL,NULL);
INSERT INTO zipcode VALUES ('100012','100012','서울','중구','충무로2가','','',NULL,NULL);
줄 단위로 insert문을 만들어주지만 필드명은 생략한다.

shell>mysql -u chang -p --skip-opt -e kimch > table_name.sql
[ 결과 ]
INSERT INTO zipcode VALUES ('100011','100011','서울','중구','충무로1가','','',NULL,NULL),('100012','100012','서울','중구','충무로2가','','',NULL,NULL) ~~
아무 옵션을 주지 않은 것과 같은 결과를 보여준다..

shell>mysql -u chang -p --skip-opt -c kimch > table_name.sql
[ 결과 ]
INSERT INTO `zipcode` (`ZIP`, `SIDO`, `GUGUN`, `DONG`, `RI`, `ST_BUNJI`, `ED_BUNJI`, `SEQ`) VALUES ('135806','서울','강남구','개포1동','경남아파트','','',1);
INSERT INTO `zipcode` (`ZIP`, `SIDO`, `GUGUN`, `DONG`, `RI`, `ST_BUNJI`, `ED_BUNJI`, `SEQ`) VALUES ('135807','서울','강남구','개포1동','우성3차아파트','1','6',2);
INSERT INTO `zipcode` (`ZIP`, `SIDO`, `GUGUN`, `DONG`, `RI`, `ST_BUNJI`, `ED_BUNJI`, `SEQ`) VALUES ('135806','서울','강남구','개포1동','우성9차아파트','901','902',3);
필드명까지 포함된 완벽한 인서트문을 생성해준다..

shell>mysql -u chang -p --skip-opt -c -e kimch > table_name.sql
[ 결과 ]
INSERT INTO `zipcode` (`ZIP`, `SIDO`, `GUGUN`, `DONG`, `RI`, `ST_BUNJI`, `ED_BUNJI`, `SEQ`) VALUES ('135806','서울','강남구','개포1동','경남아파트','','',1),('135807','서울','강남구','개포1동','우성3차아파트','1','6',2),('135806','서울','강남구','개포1동','우성9차아파트','901','902',3);
처음에는 필드명까지 포함 시키나 완벽한 인서트문을 생성하지 않고 같은 라인에 연속해서 필드값만을 생성해준다..

 

개인적으로는 –opt 옵션을 주로 사용합니다.

mysqldump -u myhome -p –opt support test > /PATH/support_test.dump
이 구문은 support 데이터베이스 안에 있는 test 테이블을 지정한 경로에 백업 받는 명령입니다

 

-------------------------------------------------------

다음으로 복구하는 법입니다.
mysql -u myhome -p myhome < 백업한파일명.sql

간단하죠?

-------
추가답변입니다. 테이블이 이미 있다고 하는건

테이블이 있는 상태에서 복구를 했기 때문입니다.

만들어진 스크립트 보시면 아시겠지만,

create table 문부터 들어있습니다.

복구시 원래의 데이타를 drop 하신뒤에 복구 스크립트를 돌리세요.

참고로 create table 같은거 빼고 data만 백업하실때는 mysqldump시

옵션을 --no-create-info를 추가로 주시면 됩니다.

==========  관리자 비밀번호 변경  ==========

set password = password('1234');

or

update user set password = password('1234') where user='root';

 

==========  root 비밀번호 분실시 초기화  ========== 

1. mysql 데몬 kill

     # ps aux | grep mysql

     # killall mysqld_safe

     # killall mysqld

2. mysqld_safe 를 skip 모드로 가동

     # mysql_safe --skip-grant-tables &

3. mysql 접속 및 비밀번호 변경

     # mysql

     mysql> use mysql;

     mysql> update user set password=password('1234') where user='root';

     mysql> flush privileges;

     mysql> quit

4. mysql  재시작 및 접속

     # /etc/init.d/mysqld restart

     # mysql password;

 

==========  사용자 생성  ==========

use mysql

grant all privileges on 디비명.* to 사용자명@localhost identified by '비밀번호';

flush privileges;

quit;

mysqladmin -u root -p reload

 

 

===========   DB , Table 생성시 언어셋 설정 ===========

=> DB 생성시

mysql> create database DB_NAME character set euckr collate euckr_korean_ci;

 

이미 생성한 DB or Table 은 다음과 같이 수정한다.

ALTER DATABASE <db_name> CHARACTER SET euckr COLLATE euckr_korean_ci;

ALTER TABLE <table_name> CONVERT TO CHARACTER SET euckr;