mysql dump 옵션

2009/07/24 14:04 / Linux/Mysql

mysqldump로  백업하기



mysqldump는  MySQL 백업 프로그램 이며 이는 MySQL에서 MySQL뿐만 아니라
다른 이기종 간의 SQL전달할때 역시 사용된다.


그리고 MySQL의 버전간 이동에도 유용하게 사용된다.


만약 단순히 백업 용도로 사용하기를 원한다면 mysqldump보다는 mysqlhotcopy가
보다 빠른 백업과 복원을 해준다 .


물론 백업 데이터를 어떻게 사용하느나에 따라 mysqldump가 보다 더유용하게 사용되기도하지만
이는 백업 사용 방법에따라 직접 결정해야한다.




1. mysqldump의 사용법에 대해 알아보자.


사용방법은 간단하다 .


mysqldump u<사용자 계정명> -p[사용자패스워드] [option] dbname [tablename]


위와 같이 사용하며  mysqldump에서 사용되는 옵션에 대해 알아보자


--skip-extended-insert
해당옵션은 MySQL덤프를 뜨는경우 VALUES뒤에 여러 개의 데어터셋이 붙어있는 다중열 신텍스 형태로
덤프 뜨지않아 구문어서 해당라인을 찾기가 용의


--allow-keywords
MySQL예약어와 동일한 필드명이 있을경우 복원시에 에러가 발생시
이옵션을 이용하여 덤프를 받으면 테이블명와 필드명에 ``해당 기호가 삽입되어 정상적으로 인식


--complet-insert -c
완전한 인서트문을 구성한다 .
 ex) insert into table_name(field1,field2) values(value1,value2)


--no-data
덤프시 데이터를 구성하지 말라는 옵션이다 .
이는 스키마 만 덤프를 받아 개발이나 구성서시 주로이용


--no-create-db
테이블 생성 쿼리를 구성하지 말라는 옵선


--quick, -q
대용량 데이타베이스를 덤프뜰때 유용
한번에 한열씩 축출하며 쓰기전에 메로리에 버퍼링을 하록 하는옵션


--xml -x
XML형태로 덤프 받을시 이용


--default-chareter-set
덤프받을시 지정하는게릭터셋으로 연결하여 덤프 받는 옵션
하지만 테이블의 언어셋을 변경하여 받아주지는않음.



실제 mysqldump의 옵션은 많지만 주로사용하는것만 기술하였다.




2. 간단한 사용법에 대해 알아보자


분명히 euc_kr 데이터가 쌓여있는데 덤프를 받으면 깨지는경우


     -  이는 MySQL 서버가 다른언어셋으로 구성되어 있어 이중으로 인코딩되어
        한글이나 다른언어들이 깨지는 현상이 발생한다 .
 이경우는 다음고 같이 덤프 받는다.


 mysqldump --default-chareter-set=euckr -u user -p  dbname > dbname.sql



버전이 달라  mysql디비가 인서트가안될경우
     - 메이져 버전이 다를경우 MySQL데이터베이스안의 user,db,hot등의 테이블 구조가 달라
       일반적으로 덤프를든경우  정상적으로 인서트되니않는다.
 이경우는 다음고 같이 덤프 받는다.


 mysqldump --complet-insert -u user -p  dbname > dbname.sql



예약어와 동일한 필드명 때문에 인서트가안될경우
     - 필드명으로 인식하기전에 예약어로 인식하여 에러를 발생시킨다.
 이경우는 다음고 같이 덤프 받는다.


 mysqldump --allow-keywords -u user -p  dbname > dbname.sql




보통 덤프를 받을때
 mysqldump --allow-keywords --default-chareter-set=euckr --skip-opt  -u user -p  dbname > dbname.sql
        위와 같은 형테로 받는다면 큰무리없이 복원이 가능할것이다.

========================================================================
출처 : http://www.linux.co.kr/home/lecture/index.php?cateNo=3&secNo=16&theNo=45&leccode=10956

2009/07/24 14:04 2009/07/24 14:04
샤이 이 작성.

당신의 의견을 작성해 주세요.

변수의 의미는 다음과 같다.
back_log                TCP/IP의 접속을 대기하는 listen queue의 크기

basedir                 --basedir 옵션으로 지정한 위치

bdb_cache_size          BDB용 캐시 메모리 크기

bdb_log_buffer_size     BDB용 인덱스와 row를 캐시할 버퍼 크기

bdb_home               --bdb-home 옵션으로 지정한 위치

bdb_max_lock           BDB 테이블을 lock 할  수 있는 최대  값(디폴트:10000)
                        bdb:Lock table is out...이나 Got error 12 from...과 같은 에러가 발생하면 이 값을 증가해야 함

bdb_logdir              --bdb-logdir 옵션으로 지정한 위치

bdb_shared_data         --bdb-shared-data 옵션을 사용하면 ON

bdb_tmpdir              --bdb-tmpdir 옵션으로 지정한 위치

binlog_cache_size        binlog을 위해 SQL 문이 사용할 수 있는 캐시의 크기

bulk_insert_buffer_size   bulk insert에 쓰일 캐시 크기(디폴트:8M)
                           INSERT ... SELECT, 
                           INSERT...VALUES(...),(...),....
                           LOAD DATA INFILE 등을 bulk insert라 함

character_set            디폴트 character set

character_sets           지원되는 character set

concurrent_inserts       MyISAM 테이블에서 select 문을 실행하면서 동시에 insert 문도 실행(디폴트:ON),
                        이의 해제는 --safe나 --skip-new로 mysqld를 시작함

connect_timeout         서버가 접속 패킷을 기다리는 시간(초), 경과되면 Bad handshake를 출력

datadir                  --data 옵션으로 지정한 위치

delay_key_write         MyISAM 테이블에서 다음 중 하나로 지정한다.






OFF
CREATE TABLE...DELAYED_KEY_WRITE를 무시
ON
CREATE TABLE...DELAYED_KEY_WRITE를 허용(디폴트)
ALL
열린 테이블은 모두 DELAYED_KEY_WRITE로 취급


delayed_insert_limit      delayed_insert_limit를 경과한 row 삽입에서, INSERT DELAYED 핸들
                        러가 SELECT의 실행을 체크하여 이미 SELECT가 실행 중이면, 이를 먼저 실행하도록 한다. 

delayed_insert_timeout   INSERT DELAYED 스레드가 INSERT 문을 종료하기 전에 기다리는 시간

delayed_queue_size      INSERT DELAYED를 위한 queue의 크기, 이 큐가  꽉 차있으면, 큐에 
                        여백이 날 때가지 INSERT DELAYED를 기다린다.

flush                    --flush 옵션으로 mysql을 시작하면 ON임

flush_time               0이 아니면, sync하기 위하여 모든 테이블을 닫음

ft_boolean_syntax        MATCH...AGAINST(...IN BOOLEAN MODE)를 지원하는 연산자들

ft_min_word_len         FULLTEXT 인덱스에 포함할 단어의 최소길이

ft_max_word_len        FULLTEXT 인덱스에 포함할 단어의 최대길이

ft_max_word_len_for_sort   REPAIR, CREATE INDEX, ALTER TABLE로 FULLTEXT 인덱스를 다시 만
                        들 때 사용될 단어의 최대 길이

ft_stopword_file         fulltext 찾기에서 정지될 단어가  있는 파일(myisam/ft_static.c를 참조)

have_bdb               mysqld가 BDB를 지원하면 YES임

have_innodb             mysqld가 InnoDB를 지원하면 YES임

have_raid               mysqld가 RAID를 지원하면 YES임

have_openssl            mysqld가 SSL를 지원하면 YES임

init_file                 --init-file로 지정한 SQL 문으로 된 파일로 데몬이 시작할 때 실행됨

interactive_timeout       대화식 접속에서 서버가 접속을 닫을 때까지 기다리는 시간(초)

join_buffer_size          두 테이블을 join할 때 사용할 버퍼의 크기

key_buffer_size          인덱스 블록에서 사용할 버퍼의 크기 (인덱스 블록은 모든 스레드가 공유함)]

language                에러 메시지를 출력할 언어

large_file_support        mysqld가 big file support 옵션으로 컴파일된 경우

locked_in_memory       mysqld가 --memlock으로 lock된 경우

log                     모든 쿼리의 등록(logging)이 허용 여부

log_update              log의 update에 대한 허용 여부

log_bin                 binary log의 허용 여부

log_slave_updates        slave로부터의 update가 등록(log)되는지 여부

long_query_time         지정한 초단위 시간보다 더 걸리면, slow_queries 계수기가 증가됨,
                        --log-slow-queries를 사용하면 질의는 slow query log 파일에 기록된다.

lower_case_table_names 1이면, 소문자로 디스크에 파일이름이 저장되고, 테이블 이름의 비
                        교에서는 대소문자 구분이 없음

max_allowed_packet     패킷 하나의 최대 크기

max_binlog_cache_size   multi-statement transaction의 상한 메모리 크기

max_binlog_size         지정한 log 크기를  초과하면 log는  rotate함, 1KB이상이어야 함 (디폴트 1GB)

max_connections        동시에 허용 클라이언트 수

max_connect_errors      한 호스트에서 인터럽트로 접속할 수 있는 최대 횟수로 이를 초과
                        하면, 그 호스트는 block되며, FLUSH HOSTS로 unblock함

max_delayed_threads     INSERT DELAYED 문에 쓰일 스레드의 상한  수치로 '0'은 사용하지 않음을 의미

max_heap_table_size     heap 테이블에 쓰일 상한 크기

max_join_size           join에 쓰일 상한 크기

max_sort_length         BLOB, TEXT의 소팅에 사용될 상한 바이트 크기

max_user_connections    한 유저가 접속할 수 있는 상한 값(0=무제한)

max_tmp_tables         한 클라이언트가 동시에 열어  놓을 수 있는 임시  테이블의 최대 수치

max_write_lock_count    write lock의 최대 수치로 이 수치이상은 read lock만 가능.

myisam_recover_options --myisam-recover 옵션의 지정 여부

myisam_sort_buffer_size
                        REPAIR로 인덱스를 소팅하거나,  CREATE INDEX, ALTER TABLE로  인덱스를 만들 때 허용되는 버퍼의 크기

myisam_max_extra_sort_file_size
                        대용량 테이블에서 긴 문자 key로 인덱스를 만들 때 유용함

myisam_max_sort_file_size 
                        REPAIR, ALTER TABLE, LOAD DATA INFILE를 하는 동안 만들어지는 임시 파일의 최대 크기

net_buffer_length        쿼리 사이에 쓰일 communication  버퍼의 크기이며, 문자의  길이
                        가 이 범위를 벗어나면,  max_allowed_packet 범위까지 자동으로 커진다.

net_read_timeout         read에서 다음 data까지의 최대 접속 허용시간(초)으로  데이터가 
                        기대되지 않을 경우에는 write_timeout이 적용됨

net_retry_count          read가 안될 때 반복 허용되는 횟수

net_write_timeout        block되어 쓰기가 안될 때 기다리는 허용시간(초)

open_files_limit          0이 아니면,   setrlimit()의 파일   descriptor로 사용하고,  
                          0이면, (connections*5) 또는 (max_connections + table_cache *  2) 중에
                          서 큰 수만큼의 파일을 사용할 수 있음

pid_file                  --pid-file 옵션으로 지정한 위치

port                    --port 옵션으로 지정한 포트

protocol_version         서버의 프로토콜 버전

read_buffer_size         각 스레드가 차지할 버퍼 크기

record_rnd_buffer_size   소팅한 row가 읽혀지기 위해 사용할 버퍼의 크기

query_cache_limit        쿼리한 결과가 사용할 캐시의 크기(디폴트 1M)

query_cache_size        이전 쿼리의 결과를 저장할 메모리 크기(0은 디폴트로 사용 안 함을 의미)

query_cache_type        다음 표 참조







value
Alias
Comment
0
OFF
캐시나 결과물을 처리하지 안음
1
ON
모든 결과물을 캐시함(예외:SELECT SQL_NO_CACHE...)
2
DEMAND
SELECT SQL_CACHE... 만 캐시함


safe_show_database      show databases의 권한 여부(skip_show_database)

server_id                --server-id 옵션으로 지정한 값

skip_locking             OFF이면, 외부 locking을 사용할 수 있음

skip_networking         ON이면, 오직 local 접속만 허용

skip_show_database      PROCESS권한 없으면, SHOW DATABASES를 금함

slave_net_timeout        master/slave 간에 read의 기다림 허용시간(초)

slow_launch_time        스레드를 만드는 시간(초)이 경과되면, slow_launch_threads  계수기가 증가된다.

socket                  서버의 소켓번호가 있는 파일

sort_buffer_size          각 스레드가 소팅할 때 사용할 버퍼의 크기

table_cache              모든 스레드에게 열어 놓을 수 있는 테이블의 수

table_type               디폴트 테이블 타입

thread_cache_size        재사용을 위해 캐시 내에 남아 있을 스레드의 수

thread_concurrency      thr_setconcurrency()가 동시에 수행할 수 있는 스레드  수

thread_stack             각 스레드에 대한 stack의 크기
                        참조:http://www.mysql.com/information/crash-me.php

timezone                서버의 시간 대역(KST)

tmp_table_size           메모리에 있는 임시 테이블의 크기, 임시 테이블이 이  크기를 벗
                        어나면 자동으로  디스크에 MyISAM   테이블로 전환된다. 대개 
                        GROUP BY 질의 때 많은 메모리를 차지한다.

tmpdir                  임시 파일이나 임시 테이블에 사용될 디렉토리. 경로를 ':'으로  분
                        리하며, round-robin 방식을 사용한다.

version                 서버의 버전 번호

wait_timeout            비-대화식 접속에서  서버가 접속을 닫을  때까지 기다리는  시간(초)

===================================================================
출처 : http://radiocom.kunsan.ac.kr/lecture/mysql/show_variables_explain.html
2009/07/24 10:49 2009/07/24 10:49
샤이 이 작성.

당신의 의견을 작성해 주세요.

mysql configure 에서 다음과 같이 에러가 날 시

configure: error: This is a linux system and Linuxthreads was not
found. On linux Linuxthreads should be used.  Please install Linuxthreads
(or a new glibc) and try again.  See the Installation chapter in the
Reference Manual for more information.

/usr/include/pthread.h 에 다음과 같이 추가해 줍니다.

echo '/* Linuxthreads */' >> /usr/include/pthread.h
2009/07/23 12:03 2009/07/23 12:03
샤이 이 작성.

당신의 의견을 작성해 주세요.

mysql의 database 전체 기본 언어셋 변경 시 다음 쿼리문 실행

euckr로 변경
alter database [DB명] default character set euckr collate euckr_korean_ci;

utf8로 변경
alter database [DB명] default character set utf8 collate utf8_general_ci;

2009/04/30 15:22 2009/04/30 15:22
샤이 이 작성.

당신의 의견을 작성해 주세요.

mysql root 비밀번호를 분실 하였을 시 다음 방법으로 변경 합니다.

#killall mysqld                                                                 ## mysql 데몬 강제종료
#/usr/local/mysql/bin/mysqld_safe --skip-grant-tables&         ## passwd 없이 mysql 로그인 가능하도록 가동
#mysql -u root -p                                                            ## mysql 접속


mysql>update user set password=password('패스워드') where user = 'root';    ## root 패스워드 변경
mysql>flush privileges;                                                     ## mysql 재적용
mysql>exit                                                                     ## mysql 종료


#killall mysqld 또는 mysqladmin -p shutdown                      ## mysql 종료
#/usr/local/mysql/bin/mysqld_safe &                                  ## mysql 가동


2009/04/21 13:45 2009/04/21 13:45
샤이 이 작성.

당신의 의견을 작성해 주세요.

mysql binary log 저장되지 않도록 하기 위해서는 /etc/my.cnf 파일의 log-bin 옵션을 제거 합니다.


추가 설정 할수 있는 binary log 와 관련된 옵션


binlog_cache_size =  1M       #binlogchase 사이즈
max_binlog_size =  50M       # bin로그 max 사이즈
expire_logs_days = 3            #보관기간


명령어로 3일 이전의 모든 binary log 지우는 방법


mysql -uroot -p -e "PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);"

2009/04/20 12:08 2009/04/20 12:08
샤이 이 작성.

당신의 의견을 작성해 주세요.

my.cnf의 언어셋 관련 지정이 변경이 되지 않을시

ex)default-character-set = euckr

다음 라인을 삽입 합니다.

skip_character_set_client_handshake

또는 mysql 실행시 mysqld_safe --default-character-set=euckr --skip-character-set-client-handshake &

윈도우 용은 my.ini 다음과 같이 수정

[mysqld]
character-set-client-handshake = FALSE
2009/02/06 18:05 2009/02/06 18:05
샤이 이 작성.

당신의 의견을 작성해 주세요.

환경보기

#mysqladmin -p variable



사용 가능한 언어셋 조회


mysql>show character set;


각 DataBase별 언어셋팅 조회


mysql>use (확인하실 데이터베이스명);
mysql>show variables like 'c%';



DataBase 생성시 기본 언어셋이 아닌 임의의 언어셋 지정하기 (사용 가능한 언어셋 조회하여 참고)


ex)euckr 예
mysql>CREATE DATABASE (디비명) DEFAULT CHARACTER SET euckr COLLATE euckr_korean_ci;

2008/12/15 13:16 2008/12/15 13:16
샤이 이 작성.

당신의 의견을 작성해 주세요.

테이블 생성 예


CREATE TABLE test (
    name varchar(40) default NULL
)ENGINE=MyISAM DEFAULT CHARSET=euckr;



dump 시


./mysqldump -u user -p --default-character-set=euckr DB > FILE


입력시


mysql  -u user -p --default-character-set=euckr DB < FILE



 

2008/11/17 09:05 2008/11/17 09:05
샤이 이 작성.

당신의 의견을 작성해 주세요.

Mysql 4.1 이하의 버전에서 이상의 버전으로 dump 이전후 패스워드 문제 발생시 아래 명령어로 mysql 가동 합니다.

/usr/local/mysql/bin/mysqld_safe --old_password &

2008/11/08 18:24 2008/11/08 18:24
샤이 이 작성.

당신의 의견을 작성해 주세요.