[mysql] about with linux

접속 오류 관련

1. ERROR 2002: Can`t connect to local MySQL server through socket '/tmp/mysql.sock' (2) 

 

MySQL의 소켓파일인 mysql.sock 파일이 없거나 정확하지 않아서 발생하는 에러로

MySQL이 실행이 되면 MySQL의 소켓파일이 /tmp/디렉토리에 생성되고 MySQL이 종료되면 이 파일은 자동 삭제된다.

 

따라서, 이 에러가 발생하는 경우 그 원인은 MySQL이 종료되어 있거나 또는 mysql.sock 소켓파일의 위치가 다르기 때문에 발생

 

해결 방법:

1) MySQL이 종료되어 있을 경우

 

MySQL 데몬 실행: mysqld_safe &

 

 

2) MySQL의 소켓파일 mysql.sock의 위치가 다를 경우

 

사용 형식: mysql -u root -p mysql -S /var/lib/mysql/myql.sock (S는 대문자 S이며 디렉토리 경로는 따로 지정하지 않았을 경우 기본 경로)

 

 

2. ERROR 2002: Can`t connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)

 

이유는 모름

 

해결 방법: 1) /usr/bin/mysqld_safe --user=root & (/etc/rc.d/rc.local 파일에 적어서 시작시 자동 적용되도록 기재) 2) killall mysqld 3) mysqld_safe --skip-grant & (비밀번호 입력하지 않는 모드로 접속) 4) mysql  - mysql> use mysql (아마 mysql 테이블을 쓴다는 듯)  - mysql> update user set password=password('123456') where user='root'; (update문으로 비밀번호 변경)  - mysql> flush privileges (테이블에 적용) 5) 끗

 

 

3. Starting MySQL. ERROR! Manager of pid-file quit without updating file.

 

/etc/my.cnf 파일이 잘못 수정되어 발생하는 오류

 

해결 방법: 1) /etc/my.cnf 삭제 2) /usr/local/mysql/bin/mysql_install_db --user=mysql (데이터베이스 새로 생성)

 

 

오류 발생하는 상황이 생길때마다 추가 등록 예정

[출처] MySQL_(3) 접속 오류 관련|작성자 듀폰

mysqladmin 활용

mysqladmin은 MySQL의 관리전용 유틸리로서 할 수 있는 대표적인 작업들은 다음과 같다(mysql 설치시 함께 설치된다)

 

1) MySQL의 root 패스워드 변경하기 2) MySQL의 일반계정 사용자 패스워드 변경하기 3) 새로운 데이터베이스 생성하기 4) 사용중인 데이터베이스 삭제하기 5) MySQL의 현재상황 살펴보기 6) MySQL에 접속한 클라이언트(threads)리스트 확인하기 7) MySQL 캐시 데이터 동기화하기 8) MySQL 종료하기 9) MySQL 실행 환경변수 확인하기 10) MySQL에 접속한 사용자 접속끊기 11) MySQL의 버전 및 여러가지 실행 정보들 확인하기 12) MySQL이 정상적으로 살아있는지 죽었는지 확인하기 13) 기타 MySQL관리에 필요한 유용한 설정 및 정보확인

 

사용 형식: mysqladmin -u root -p 명령어

 

1. 새로운 데이터베이스 생성하기

사용 형식: mysqladmin -u root -p create 새로운데이터베이스명

1) 'newdatabase'란 이름의 데이터베이스 생성

-> mysqladmin -u root -p create newdatabase

2) 확인

-> show databases;

+--------------------+

| Database                |

+--------------------+

| information_schema  |

| mysql                      |

| newdatabase        |

| performance_schema|

| temp_db2                 |

| test                         |

+--------------------+

6 rows in set (0.00 sec)

2. 특정 데이터베이스 삭제하기

 

사용 형식: mysqladmin -u root -p drop 삭제할 데이터베이스명

 

1) 'newdatabase' 삭제

-> mysqladmin -u root -p drop newdatabase

Enter password: 

Dropping the database is potentially a very bad thing to do.

Any data stored in the database will be destroyed.

 

Do you really want to drop the 'newdatabase' database [y/N] y

Database "newdatabase" dropped

2) 확인

-> show databases;

+---------------------+

| Database                 |

+---------------------+

| information_schema   |

| mysql                       |

| performance_schema |

| temp_db2                  |

| test                          |

+---------------------+

5 rows in set (0.00 sec)

3. MySQL의 권한테이블 갱신하기

 

사용 형식: mysqladmin -u root -p reload

 

4. MySQL에 접속한 사용자 확인하기

 

사용 형식: mysqladmin -u root -p processlist

 

5. 접속되어 있는 MySQL사용자(threads)의 접속 끊기

 

사용 형식: mysqladmin -u root -p kill 스레드번호

 

6. MySQL의 간단한 실행정보 확인

 

사용 형식: mysqladmin -u root -p status

 

1) status 명령어로 확인할 수 있는 실행정보

- MySQL의 총 실행시간

- 현재 처리중인 스레드(threads) 수

- 오픈된 데이터베이스 및 테이블 수

- 초당 평균 처리속도

- 기타 실행정보들(스로우쿼리, Flush tables 등)

 

-> mysqladmin -u root -p status

Enter password: 

Uptime: 476840  Threads: 1  Questions: 150  Slow queries: 0  Opens: 48  Flush tables: 1  Open tables: 41  Queries per second avg: 0.000

- MySQL의 총 실행시간(Uptime) : 476840

- 현재 처리중인 스레드(Threads)수 :1

- 오픈된 데이터베이스(Open) 및 테이블(Open tables) 수 : 48 / 41

- 초당 평균 처리속도(Queries per second avg) : 0.000

- 기타 실행정보들(스로우쿼리, Flush tables 등)

 

7. MySQL의 현재 상황 자세히 살펴보기

 

사용 형식: mysqladmin -u root -p extended-status (mysql에 접속한 상태에서 "show status" 명령어를 입력하면 위의 명령결과와 동일한 결과를 얻을 수 있다)

 

8. MySQL의 환경변수를 확인하기

 

사용 형식: mysqladmin -u root -p variables (mysql에 접속한 상태에서 "show variables" 명령어를 입력하면 위의 명령결과와 동일한 결과를 얻을 수 있다)

 

9. 현재 MySQL의 정확한 버전과 여러가지 실행정보 확인하기

 

사용 형식: mysqladmin -u root -p version

버전 이외에 아래와 같은 정보들을 확인할 수 있다

- MySQL의 버전

- mysqladmin 유틸리티의 버전

- MySQL의 저작권정보

- 프로토콜 버전정보

- MySQL 소켓파일 위치정보

- MySQL의 총 실행시간 정보

- MySQL의 총 스레드(threads) 수

- MySQL의 오픈된 데이터베이스와 테이블 수

- MySQL의 초당 응답완료 시간

- 기타 정보들

 

10. MySQL이 죽었는지 살았는지 확인하기

 

사용 형식: mysqladmin -u root -p ping

Enter password: 

mysqld is alive

[출처] MySQL_(9) mysqladmin 활용|작성자 듀폰

MySQL 로그를 통한 MySQL 튜닝

[출처] MySQL_(10) MySQL 로그를 통한 MySQL 튜닝|작성자 듀폰

1. MySQL 주로그(에러로그) 파일 관리하기

 - MySQL의 주로그는 특별한 옵션없이 그냥 기본으로 생성되어 기록되므로 로그기록을 위한 옵션이 존재하지 않는다

 

140302 17:48:10 mysqld_safe Logging to '/var/lib/mysql/localhost.localdomain.err'. 140302 17:48:10 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

 

위는 mysqld_safe & 명령어를 실행 후 발생되는 메시지로 위의 경로에 "호스트명.err"으로 주로그가 기록되며 

"tail -f /설치 경로/data/localhost.localdomain.err" 명령어로 실시간 감시를 할 수 있다.

 

2. MySQL의 쿼리로그 남기기

 - MySQL의 주로그(에러로그)에는 웹프로그램이나 MySQL접속후에 사용되는 SQL쿼리(Query)에 대한 로그는 기록되지 않는다.

 

쿼리로그옵션형식: --log=쿼리로그파일명 -> mysqld_safe --log=MYSQL_QUERY_LOG & -> mysqld_safe --log=/디렉토리경로/MYSQL_QUERY_LOG &

 

2번째와 같이 하면 원하는 경로에 로그파일을 생성할 수 있다.

 

3. MySQL 바이너리(BINARY)로그 파일 활용

 - MySQL의 바이너리(BINARY) 로그파일은 MySQL의 변경된 데이터내역을 기록하기 위한 목적으로 사용된다 

 - BINARY로 기록하는 이유는?

로그를 기록하는 속도를 향상시켜 실행속도를 빠르게 하기위한 것과 데이터의 변경기록을 아무나 볼 수 없도록하여 자체 보안을 위한 것이 그 목적이다

 

바이너리로그 옵션형식: --log-bin=바이너리로그파일명 -> mysqld_safe --log-bin=MYSQL_BINARY_LOG & -> mysqld_safe --log-bin=/디렉토리경로/MYSQL_BINARY_LOG & 바이너리로그 보는 방법: /설치경로/bin/mysqlbinlog 명령어 사용

 

4. SLOW로그파일을 활용한 지정된 시간이상 질의를 계속하는 질의문 잡아내기

 - SLOW로그란 MySQL의 환경변수인 "long_query_time"의 설정시간(초단위) 이상의 쿼리시간을 가지는 특정SQL 쿼리문에 대한 로그만을 기록하는 로그파일

 

따라서, SLOW로그파일을 활용한다면 어떤 웹프로그램이 쿼리시간(실행시간)을 많이 차지하는가를 확인 할 수 있다

즉, 갑자기 MySQL의 응답속도가 현저하게 떨어진다면 SLOW로그로 쿼리응답속도가 일정시간 이상 지속되는 쿼리문을 찾아서 조치하면 될 것이다

 

SLOW쿼리옵션형식: --log-slow-queries=슬로우쿼리파일명 -> mysqld_safe --log-slow-queries=SLOW_QUERY_LOG &

 

위와 같이 MySQL을 실행하면 SLOW_QUERY_LOG파일 생성되고 SLOW쿼리로그를 기록할 것이다

 

MySQL의 환경변수 중 "long_query_time" 값 확인하는 방법 -> mysqladmin -u root -p variables | grep long_query_time |    long_query_time    |    10    |

 

단위는 (초)이다 즉, 이 설정을 해석하면 MySQL의 쿼리문이 10초이상 지속될 때에는 그 쿼리문의 내역을 /설치경로/data/ 디렉토리에 있는 SLOW_QUERY_LOG파일에 기록하게 된다.

[출처] MySQL_(10) MySQL 로그를 통한 MySQL 튜닝|작성자 듀폰

[출처] MySQL_(10) MySQL 로그를 통한 MySQL 튜닝|작성자 듀폰

원격지서버와 게시판(DB)공유를 위한 MySQL설정법

DB를 원격지에서 조작 가능하도록 하고자 할  경우에는 다음과 같은 3가지 설정이 되어 있어야 한다

 

1) mysql의 db테이블의 host 컬럼이 '%' 로 설정되어 있어야 한다

(특별히 지정된 한 곳에서만 접속한다면 이곳에 ip주소를 주어도 된다 // "192.168.0.%"와 같이 네트워크로 지정할 수 있다)

 -> insert into db values('%','dupont','dupont','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

 -> 원격지에서 접속할 것이므로 DB테이블의 host필드에는 'localhost' 대신 '%'로 설정 함

 

2) mysql의 user테이블의 host컬럼이 '%' 으로 설정되어야 한다

(특별히 지정된 한 곳에서만 접속한다면 이곳에 ip주소를 주어도 된다 // "192.168.0.%"와 같이 네트워크로 지정할 수 있다)

-> insert into user values('%','dupont',password('123456'),'Y','Y','Y','Y','Y','Y','N','N','N','N','N','N','N','N');

-> 원격지에서 접속할 것이므로 DB테이블의 host필드에는 'localhost' 대신 '%'로 설정 함

 

3) 특별한 접속권한을 다음과 같이 주어야 한다

-> Grant all on dupont.* to dupont@192.168.227.129;

-> 이 설정의 의미는 다음과 같다

    192.168.227.129이라는 곳에서 dupont라는 MySQL사용자를 dupont라는 데이터베이스로 접속하는데 관한 모든 권한을 

    부여한 것이다

 

원격서버의 MYSQL로 접속

mysql -u root -p mysql -h 192.168.227.129 -P 3306 (P는 포트번호 지정 // 3306은 MySQL 기본 포트) [출처] MySQL_(11) 원격지서버와 게시판(DB)공유를 위한 MySQL설정법|작성자 듀폰

[출처] MySQL_(11) 원격지서버와 게시판(DB)공유를 위한 MySQL설정법|작성자 듀폰

my.cnf파일을 이용한 MySQL 성능향상 튜닝 방법

[출처] MySQL_(12) my.cnf파일을 이용한 MySQL 성능향상 튜닝 방법|작성자 듀폰

mysql을 설치한 후 /설치경로/share/mysql/ 디렉토리에 있는 몇가지의 *.cnf 파일을 통한 성능향상방법은 다음과 같다

 

1) /etc/my.cnf : MySQL에 관련된 모든 옵션들을 지정할 수 있는 파일(Global option)   2) DATADIR/my.cnf : MySQL의 데이터디렉토리(/설치경로/data/)내에서 사용하는 것으로 MySQL 데몬(server)와 관련된 옵션들만 지정하는 파일   3) $HOMEDIR/.my.cnf : MySQL사용자들의 개인 홈디렉토리내에 지정되는 것으로 개별 사용자별로 적용하기 위한 파일 (파일명에 .(점)주의)

 

/설치경로/share/mysql/ 디렉토리에는 다음과 같은 파일들이 있다

 

[root@dupont mysql]# ls -l *.cnf -rw-r--r--  1 root root   4474 Feb   6 13:12 my-huge.cnf -rw-r--r--  1 root root   4896 Feb   6 13:12 my-large.cnf -rw-r--r--  1 root root   4214 Feb   6 13:12 my-medium.cnf -rw-r--r--  1 root root   2651 Feb   6 13:12 my-small.cnf -rw-r--r--  1 root root 21580 Feb   6  13:12 my-innodb-heaby-4G.cnf

 

MySQL을 설치한 후에 시스템사양(특히 메모리용량)에 맞는 파일을 선택하여 /etc/my.cnf파일이름으로 복사하고 mysld데몬을 실행하면 이 파일의 옵션들이 모두 적용되어 실행된다

 

- 시스템 메모리가 4G이상일 때                : my-innodb-heavy-4G.cnf - 시스템 메모리가 1G~2G일 때                : my-huge.cnf - 시스템 메모리가 512MB정도 일 때         : my-large.cnf - 시스템 메모리가 32MB~64MB정도 일 때 : my-medium.cnf - 시스템 메모리가 64MB이하일 때            : my-small.cnf

 

다음은 각 파일의 경로 및 설정시 설정 내용이다

 

my-innodb-heavy-4G.cnf	my-huge.cnf	my-large.cnf	my-medium.cnf	my-small.cnf
위치: /share/mysql/	위치: /share/mysql/	위치: /share/mysql/	위치: /share/mysql/	위치: /share/mysql/
[clientl port = 3306 socket = /tmp/mysql.sock   [mysqld] port = 3306 socket = /tmp/mysql.sock back_log = 50 max_connections = 100 max_connect_errors = 10 table_cache = 2048 max_allowed_packet = 16M binlog_cache_size = 1M max_heap_table_size = 64M sort_buffer_size = 8M join_buffer_size = 8M thread_cache = 8 thread_concurrency = 8 query_cache_size = 64M query_cache_limit = 2M ft_min_word_len = 4 default_table_type = MYISAM thread_stack = 192K transaction_isolation = REPEATABLE-READ tmp_table_size = 64M log_bin log_slow_queries long_query_time = 2 log_long_format server-id = 1 key_buffer_size = 32M read_buffer_size = 2M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_max_extra_sort_file_size = 10G myisam_repair_threads = 1 mylsam_recover skip-bdb innodb_additional _mem_pool_size = 16M innodb~buffer _pool_size = 2G innodb_data_file_path = ibdata1:10M:autoextend innodb file io threads = 4 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 3M innodb_log_file_size = 256M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120   [mysqldump] quick max_allowed_packet = 16M   [mysql] no-auto-rehash [ isamc버d key_buffer = 512M sort_buffer_size = 512M read buffer = 3M write_buffer = 3M   [myisamchk] key_buffer = 512M sort_buffer_size = 512M read_buffer = 3M write_buffer = 3M   [mysqlhotcopy] interactive-timeout   [mysqld_safe] open-files-limit = 8192	[client] port = 3306 socket = /tmp/mysql.sock   [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 1M table cache = 512 sort buffer size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread cache = 8 query_cache_size = 32M thread_concurrency = 8 log-bin server-id = 1   [mysqldump] quick max_allowed_packet = 16M   [mysql] no-auto-rehash   [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M   [myisamc] key_buffer = 256M sort_buffer_size = 256M read buffer = 2M write_buffer = 2M   [mysqlhotcopy] interactive-timeout	[client] port = 3306 socket = Itmp/mysql.sock   [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 256M max_allowed_packet = 1M table_cache = 256 sort buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size= 16M thread_concurrency = 8 log-bin server-id = 1   [mysqldump] quick max_allowed_packet = 16M   [mysql] no-auto-rehash   [isamchk] key_buffer = 128M sort_buffer_size = 128M read_buffer = 2M write buffer = 2M   [myisamchk] key_buffer = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M   [mysqlhotcopy] interactive-timeout	[client] port = 3306 socket = /tmp/mysql.sock   [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 16M max_allowed_packet = 1M table cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M log-bin server-id = 1   [mysq1 dump] quick max_allowed_packet = 16M   [mysql] no-auto-rehash   [isamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M   [myisamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M   [mysqlhotcopy] interactive-timeout	[client] port = 3306 socket = Itmp/mysql.sock   [mysqld] port = 3306 socket = Itmp/mysql.sock skip-Iocking key_buffer = 16K max_allowed_packet = 1M table cache = 4 sort_buffer_size = 64K read_buffer_size = 256K read_rnd_buffer_size = 256K net_buffer_length = 2K thread_stack = 64K server-id = 1   [mysqldump] quick max_allowed_packet = 16M   [mysql] no-auto-rehash   [isamchk] key_buffer = 3M sort_buffer_size = 3M   [myisamchk] key_buffer = 3M sort_buffer_size = 3M   [mysqlhotcopy] interactive-timeout

[출처] MySQL_(12) my.cnf파일을 이용한 MySQL 성능향상 튜닝 방법|작성자 듀폰

데이터베이스의 깨진 테이블파일 복구

[출처] MySQL_(16) 데이터베이스의 깨진 테이블파일 복구|작성자 듀폰

1. 데이터베이스 테이블파일 복구를 위한 myisamchk(isamchk)

 -> MySQL의 데이터가 실제 저장되는 위치는 "/설치경로/mysql/data/" 이다. 그리고 이 디렉토리를 살펴보면 MySQL에 설치되어 있는 데이터베이스명과 동일한 이름의 디렉토리가 각각 존재한다. 그리고 이 디렉토리내에는 다음과 같은 형태의 파일들이 존재하고 있다.

 

*.MYD : 해당테이블의 데이터가 저장되는 테이블 데이터파일이다

*.MYI : 해당테이블의 인덱스정보가 저장되는 테이블 인덱스파일이다

*.frm : 해당테이블의 테이블구조가 저장되는 테이블스키마파일이다

 

위의 파일들은 테이블 하나에 3개씩 생성되어 그 테이블의 데이터를 실제로 저장하는 용도로 사용된다

즉, 위 3개의 파일형식과 용도를 이해하여야 myisamchk(isamchk)로 MySQL 복구를 할 수 있어 이 3개 파일의 용도를 정확하게 하는 것이 매우 중요하다

 

	myisamchk	isamchk
대상 형식	MYISAM테이블파일	ISAM테이블파일
복구대상 인덱스 테이블파일 형식	*.MYI	*.ISM

(MySQL 버전 3.23이전에는 isamchk를 사용했고 3.23 이후에는 myisamchk를 사용하고 있다) 

 

 

사용 형식: isamchk [옵션] 점검복구대상테이블인덱스파일(*.ISM)

 

 

사용 형식: myisamchk [옵션] 점검복구대상테이블인덱스파일(*.MYI)

 

주의사항 1) 단, 이 명령어는 반드시 MySQL 종료 후에 사용해야한다

              2) myisamchk는 복구시 두가지 모드가 있다. 

-> 첫번째는 점검모드로 에러발견시 보고만 할 뿐 어떠한 수정작업도 하지 않는 모드이며

-> 두번째는 복구모드로서 테이블파일을 직접 수정하여 에러부분을 수정하는 모드이다

-> 이 두가지 모드를 구분할 때는 복구모드 사용시 -r 또는 -o 옵션을 사용한다는 점을 기준으로 구분하면 된다

-> 위 옵션을 사용하지 않을 때는 모두 점검모드이다

 

2. MySQL 테이블파일의 이상유무 점검하기

 -> 테이블의 이상유무를 가장 간단히 점검하려면 myisamchk사용시 아무런 옵션없이 그냥 테이블파일만 지정해 주면 된다

 -> myisamchk의 위치는 "/설치경로/mysql/bin/myisamchk이다

 

 

사용 형식: myisamchk /home/mysql/data/dupont/testtable.MYI -> /home/mysql/data/dupont에 위치해 있는 테이블 인덱스파일(*.MYI)의 이상유무를 간단히 점검한 예

위의 명령어 대신 -c 혹은 --check 옵션을 사용하여도 동일한 결과를 얻을 수 있다

 

3. MySQL 테이블 점검시 이상발견시만 알려주기

 -> myisamchk로 테이블파일을 점검(복구)하다보면 가끔씩 불필요한 메시지가 발생할 때가 있다 이 때 "-s" 옵션을 사용하면 에러발생시에만 에러내용을 출력하여 불필요한 메시지를 보지 않을 수 있다

 

 

사용 형식: myisamchk -s /home/mysql/data/dupont/testtable.MYI

위의 명령어 대신 --silent 옵션을 사용하여도 동일한 결과를 얻을 수 있다

 

4. MySQL의 테이블 점검(복구)시 가능한 상세하게 메시지 출력하기

 -> -s 옵션과는 반대로 -v 옵션을 사용하면 상세한 메시지를 출력한다

 

 

사용 형식: myisamchk -v /home/mysql/data/dupont/testtable.MYI

위의 명령어 대신 --verbose를 사용하여도 동일한 결과를 얻을 수 있다

 

5. MySQL의 테이블 이상유무 점검시 결과를 상세히 종합하여 보여주기

 -> -i 옵션을 사용하여 특정 테이블을 점검한 결과를 가장 상세하게 종합하여 보여준다

 

 

사용 형식: myisamchk -i /home/mysql/data/dupont/testtable.MYI

위의 명령어 대신 --information을 사용하여도 동일한 결과를 얻을 수 있다

 

6. MySQL의 특정테이블 이상유무를 가장 정밀하게 점검하기

 -> 가장 완전하고 정밀한 결과를 원한다면 -e 옵션을 사용한다

 -> 참고로 -r이나 -o옵션을 사용하여 테이블을 복구할 때에 -e옵션을 사용할 때에는 주의를 요한다

 -> 또한, -e옵션을 사용할 때에는 -v옵션과 함께 사용하는것이 좋다

 

 

사용 형식: myisamchk -ev /home/mysql/data/dupont/testtable.MYI

위의 명령어 대신 --extend-check를 사용하여도 동일한 결과를 얻을 수 있다

 

7. 정형적인 방법으로 MySQL의 깨진 테이블파일 복구하기

 -> -r 옵션을 사용한다 단, 유일(unique)하지않은 unique키값을 가진 데이터는 -r 옵션으로 복구하는 대상에서 제외된다

 -> -r 옵션을 사용할 때에는 복구내역의 자세한 메시지를 보기 위하여 -v옵션과 함께 사용한다

 

 

사용 형식: myisamchk -rv /home/mysql/data/dupont/testtable.MYI

위의 명령어 대신 --recover옵션을 사용하여도 동일한 결과를얻을 수 있다

 

8. MySQL의 깨진 테이블파일 안전모드로 복구하기

 -> -r 옵션으로 복구하지 못하는 데이터파일의 복구방법으로 -o 옵션을 사용하여 복구할 수 있다

 

 

사용 형식: myisamchk -o /home/mysql/data/dupont/testtable.MYI

위의 명령어 대신 --safe-recover옵션을 사용하여도 동일한 결과를 얻을 수 있다

 

9. MySQL 테이블이 완전히 깨졌을 때의 최후의 복구방법

 -> -e옵션을 사용하면 복구대상 데이터파일로부터 가능한 모든 레코드들을 복구하려고 시도한다

 -> 단, 복구 대산 테이블파일이 완전히 깨지거나 완전히 망가진 상태가 아니라면 이 -e 옵션을 사용하면 안된다. 가장 복구율이 높기도

     하지만 가장 안전하지 못한 방법이기 때문에 완전히 깨진 테이블파일과 같은 복구하기 힘든 경우에 마지막 방법으로 사용한다

 -> 단, -e 옵션으로 테이블파일을 복구할 때에는 -r 옵션을 함께 사용하여 -re와 같이 사용하든가 아니면 -o옵션과 함계 -oe와 같은 형태

     로 사용해야 한다. 왜냐하면, -e 옵션만을 사용하면 복구모드가 아니라 점검모드로 인식되기 때문이다

 

 

사용 형식: myisamchk -re /home/mysql/data/dupont/testtable.MYI

 

10. myisamchk(isamchk)로 테이블파일 복구가 되지 않을 경우

 -> myisamchk(isamchk)유틸리티로 깨진 테이블을 100%복구하기란 거의 어렵다 이런 경우, 수작업으로 복구를 해주어야 한다

 

1) 테이블 파일 중 테이블구조파일(*.FRM파일)로 인해 복구가 불가능할 때

 -> 복사해둔 *.frm파일이 없다면 테이블 구조를 다시 만들어 주면 된다. 즉, 최악의 경우에는 MySQL에 직접 접속하여 "create table"문으로 테이블 구조를 다시 만들어 주면 된다

 -> 이 때 주의할 점은 "create table"문으로 테이블구조를 재생성하기 전에 반드시 기존의 *.MYI파일과 *.MYD파일의 원본은 백업을 해두어야 한다는 점이다. 혹시 잘못되었을 경우를 대비하여 실제 데이터가 저장된 *.MYD와 테이블 인덱스정보가 저장된 *.MYI파일의 원본이라도 그대로 보존되어야하기 때문이다

 -> 이와 같이 *.FRM파일을 재생생하였다면 다시 myisamchk(isamchk)로 복구시도를 해보자 만약 *.FRM이 원인이 되어 복구되지 않았을 경우 이와 같은 방법으로 거의 90% 복구 가능하다

 

2) 테이블파일 중 테이블 인덱스파일(*.MYI파일)로 인해 복구가 불가능할 때

 -> 이 역시 작업하기 전에 반드시 원본 *.MYD와 *.FRM파일은 백업을 해둬야 한다

 -> 그 다음 MySQL에 접속하여 "delete from 테이블명"과 같은 SQL명령문으로 해당 테이블의 모든 레코드(데이터)들을 삭제한다

 -> 그 다음 MySQL을 종료하고 백업해둔 *.MYD와 *.FRM파일을 다시 원래 위치로 복사한다

 -> 그 후 myisamchk로 다시 복구시도 한다

 -> 이와 같이 *.MYI파일의 원인으로 복구가 되지 않을 경우 이와 같은 방법으로 80%정도 복구할 수 있다

 

참고로 MySQL유틸리티를 이용한 방법 이외에도 MySQL에 접속하여 "mysql> repair table 테이블명"을 테이블을 점검&복구할 수 있다

[출처] MySQL_(16) 데이터베이스의 깨진 테이블파일 복구|작성자 듀폰

MySQL의 데이터베이스 데이터 백업과 복구

[출처] MySQL_(15) MySQL의 데이터베이스 데이터 백업과 복구|작성자 듀폰

MySQL 데이터베이스를 백업하는 방법은 다음과 같이 두가지로 분류할 수 있다

 

첫번째 백업방법: /설치경로/mysql/ 전체를 매일 압축백업한다 두번째 백업방법: mysqldump 명령어로 MySQL스키마와 데이터만 백업한다

 

1) /설치경로/mysql/ 디렉토리전체를 압축백업하기

 

tar cvfz /backup/mysql.tar.gz /home/mysql

백업할 경로와 파일명은 원하는 대로 지으면 된다

 

2) mysqldump 명령어를 이용하여 백업하기

 

사용 형식1: mysqldump [옵션] DB [TABLES...] > 파일명

-> DB는 백업대상이 되는 데이터베이스명이고 TABLES는 지정한 백업대상 데이터베이스내에 존재하는 테이블명이다. 따라서 이 백업의 의미는 DB의 데이터베이스내에 존재하는 테이블의 내용을 백업하여 "파일명"에 저장하라는 의미이다

 

사용 형식2: mysqldump [옵션] --databases [옵션] DB1 [DB2 DB3...] > 파일명

-> --databases라는 옵션에 의해 DB1 DB2 DB3... 의 데이터베이스들을 배업하여 "파일명"에 저장한다. 즉 백업대상이 되는 데이터베이스가 2개이상이 될 때에는 --databases라는 옵션을 사용하고 그 뒤에 백업할 데이터베이스를 지정하면 된다

 

사용 형식3: mysqldump [옵션] --all-databases [옵션] > 파일명

-> --all-databases라는 옵션은 MySQL내에 존재하는 모든 데이터베이스를 백업 대상으로한다는 의미이다. 따라서 MySQL내의 모든 데이터베이스들을 백업하여 "파일명"에 저장한다라는 의미가 된다

 

이 외에도 "mysqldump --help" 명령어를 통해 다양한 백업옵션들을 확인할 수 있다

 

(1) 특정 데이터베이스 데이터 백업과 복구

 -> 특정 데이터베이스 하나만을 백업하는 방법

 

백업 형식: mysqldump -u DB 계정명 -p 백업대상데이터베이스명 > 저장할파일명 -> mysqldump -u root -p template_db > temp.sql

 

복구 형식: mysql -u DB 계정명 -p 복구할데이터베이스명 < 저장한파일명 -> mysql -u root -p mysql < mysql.sql

 

(2) 특정 데이터베이스의 특정 테이블의 백업과 복구

 

 

백업 형식: mysqldump -u DB 계정명 -p 데이터베이스명 테이블명 > 저장할파일명 -> mysqldump -u root -p template testtable > test.sql

 

 

복구 형식: mysql -u DB 계정명 -p 데이터베이스명 < 저장한파일명 -> mysql -u root -p template < test.sql

 

(3) 여러개의 데이터베이스 한번에 백업과 복구

 

 

사용 형식: mysqldump -u root -p --databases [옵션] DB1 [DB2 DB3...] > 파일명 -> mysqldump -u root -p --databases template_db template2_db > test1.sql

 

 

복구 방법: mysql -u root -p < 파일명 -> mysql -u root -p < test1.sql

 

(4) MySQL의 전체 데이터베이스 데이터 백업하기

 

 

사용 형식: mysqldump -u root -p --all-databases > 파일명 -> mysqldump -u root -p --all-databases > ALLDATA.sql

 

(5) 기존 테이블을 삭제후 백업된 파일로 복구하기 위한 백업 방법

 -> mysqldump문으로 데이터베이스 백업시에 각각의 create table문 앞에 drop table문 삽입하기

 -> mysqldump로 백업된 SQL파일을 이용하여 복구를 할 때에는 복구하고자하는 데이터베이스에 스키마(테이블구조)가 이미 존재할 경우에는 에러가 발생한다. 이 때, --add-drop-table옵션을 사용하면 생성되는 백업파일내의 "create table"이라는 문장 바로 앞에 "drop table"문을 추가하게되어 백업된 파일로 복구를 할 때에 기존의 테이블을 완전히 삭제하고 테이블을 다시 만들어서 데이터를 입력하는 방법으로 복구를 하게 된다

 

 

사용 형식: mysqldump -u root -p --add-drop-table 데이터베이스명 > 파일명 -> mysqldump -u root -p --add-drop-table template_db > temp_db.sql

 

복구 방법: mysql -u root -p 데이터베이스명 < 파일명 -> mysql -u root -p template_db < temp_db.sql

 

(6) 데이터베이스 백업시 에러발생을 무시하고 계속 진행하기

 

 

사용 형식: mysqldump -u 사용자명 -p -f DB명 > 파일명 -> mysqldump -u root -p -f template_db > temp_db.sql

 

(7) 원격서버의 MySQL 데이터베이스 백업하기 #1 (기본포트사용)

 -> -h 옵션 다음에 원격서버의 IP주소나 호스트명 또는 도메인명을 입력하면 원격서버의 MySQL데이터를 백업할 수 있다

 

 

사용 형식: mysqldump -u 사용자명 -p -h 호스트명(IP주소) DB명 > 파일명 -> mysqldump -u root -p -h 192.168.227.128 template_db > temp_db.sql

 

 

단, 원격지 서버에 접속허용 설정이 되어 있어야 한다. 원격지에서 MySQL 접속허용을 하려면 MySQL의 관리데이터베이스인 mysql데이터베이스의 db 테이블과 user 테이블의 host 필드값을 '%' 또는 접속을 허용할 IP주소등을 설정함으로서 원격서버의 접속을 허용할 수 있다.

 

(8) 원격서버의 MySQL 데이터베이스 백업하기 #2 (특정포트번호지정)

 -> MySQL 자체 보안을 위하여 기본포트 3306번 대신 다른 포트번호를 사용한다

 

 

사용 형식: mysqldump -u 사용자명 -p -h 호스트명(IP주소) -P 포트번호 DB명 > 파일명 -> mysqldump -u root -p -h 192.168.227.128 -P 13824 template_db > temp_db.sql

 

(9) 데이터 백업시에 create databases문을 생략하여 백업하기

 -> "--databases"옵션이나 "--all-databases"옵션을 사용하여 백업할 때 "CREATE DATABASE"라는 SQL문이 추가로 저장되게 된다. 이 때 이 SQL문이 추가되지 않도록 하려면 "-n" 옵션 또는 "--no-create-db"라는 옵션을 사용하면 된다

 

 

사용 형식: mysqldump -u DB사용자명 -p -n [옵션] DB명 > 파일명 -> mysqldump -u root -p -n --databases template_db > temp_db.sql -> mysqldump -u root -p -n --all-databases template_db > temp_db.sql

 

단, 이 경우에도 백업된 파일을 이용하여 해당 데이터베이스를 복구해야하기 때문에 백업된 파일내에 "USE DB명" 문은 여전히 존재한다

여기서 "CREATE DATABASE"라는 SQL문은 백업된 파일로 복구를 할 때 데이터베이스까지 생성하면서 복구하기 위한것으로 필요에 따라 -n 옵션을 사용할지 말지를 결정하면 된다

 

(10) 데이터 백업시에 create table문을 생략하여 백업하기

 -> mysqldump 명령어로 백업을 하면 기본값으로 백업대상 데이터베이스의 스키마(테이블 구조)를 생성하는 "CREATE TABLE"문과 데이터를 입력하는 INSERT문이 백업 결과파일에 기본적으로 저장된다 이 때, 데이터베이스의 스키마를 제외한 데이터복구 SQL문만을 백업하려면 -t 옵션을 사용하면 된다

 

 

사용 형식: mysqldump -u DB사용자명 -p -t DB명 > 파일명 -> mysqldump -u root -p -t template_db > temp_db.sql

위의 형식에서 -t 대신 --no-create-info를 사용할 수도 있다

 

(11) 데이터는 백업하지 않고 테이블 스키마만 백업하기

 -> (10)번과 반대되는 내용으로 데이터값(레코드값)을 제외한 데이터베이스 스키마(테이블구조)만을 백업하는 방법

 

 

사용 형식: mysqldump -u DB사용자명 -p -d DB명 > 파일명 -> mysqldump -u root -p -d template_db > temp_db.sql

위의 형식에서 -d 대신 --no-data를 사용할 수도 있다

 

(12) 특정 데이터베이스의 조건에 맞는 데이터만 백업하기

 

사용 형식: mysqldump -u root -p -where= "WHERE조건문" DB명 테이블명 > 파일명 -> mysqldump -u root -p -where="name='dupont'" template_db testtable > temp_db.sql -> template_db 데이터베이스 내의 testtable 테이블의 데이터 중 name 필드값이 dupont 인 레코드만 백업

위의 형식에서 --where대신에 -w를 사용할 수도 있다

또한, 위의 방법에서 -t옵션을 추가하면 데이터베이스 스키마까지 제외한 순수한 데이터만을 저장하기 위한 백업이 된다

 

(13) select 문을 이용한 데이터 백업방법

 -> mysqldump외에 mysql에 접속하여 select문을 이용한 백업 방법 

 

 

사용 형식: select * into outfile './backup.sql' from temp01; -> outfile 다음에는 테이블의 데이터가 저장될 파일명이며, from 뒤에는 백업을 원하는 테이블의 이름을 적어주면 된다. 이와같이 백업된 데이터는 /설치경로/data 디렉토리내에 backup.sql이라는 파일명으로 생성된다

 

 

복구 방법: load data infile './backup.sql' into table temp01; -> infile 다음에 테이블의 데이터가 저장되어 있는 파일명을 주고 into table뒤에는 데이터가 저장될 테이블명을 주면 된다

 

이런 방법은 주로 PHP등의 프로그램으로 데이터베이스의 데이터를 백업하는 프로그램을 만들고자 할 때에 많이 사용한다

 

[출처] MySQL_(15) MySQL의 데이터베이스 데이터 백업과 복구|작성자 듀폰