맛깔나는 SE 레시피 콘서트 -- SEROOT.COM :)

하나
    백업
    #mysqldump -u root(사용자) -p seroot(디비명) > seroot.sql(디비파일명)
    복구
    #mysql -u root -p seroot < seroot.sql

여러개 동시
    백업
    #mysqldump -u root -p --databases nforce fish test mysql > dbs.sql
    복구
    #mysql -u root -p < ./dbs.sql

전체 데이터베이스 백업
    mysql 재설치나 서버이전 정규백업 경우
    #mysqldump -u root -p --all-databases > all.sql

기존테이블 삭제하고 난후  백업하기
    #mysqldump -u root -p --add-drop-table seroot > seroot.sql

디비백업시 에러 무시 하고 진행
    #mysqldump -u root -p -f seroot > seroot.sql

원격지 db 백업하기
    #mysqldump -u root -p -h 192.168.0.1 seroot > seroot.sql

원격지 다른포트 db백업
    #mysqldump -u root -p -h 192.168.0.1 -P 22 seroot > seroot.sql

백업시 create database 생략하고 백업하기
    #mysqldump -u root -p -n --databases seroot fish > dbs.sql

백업시 create table 생략하고 백업하기
    #mysqldump -u root -p -t seroot > seroot.sql

테이블 스키마만 백업
    #mysqldump -u root -p -d seroot > seroot.sql

MYSQL 깨진 DB 복구

       ** 반드시 MYSQL을 종료하고 나서 실행하라

mysql3.23 이전버전 isamchk (인덱스파일 *.ISM)
mysql3.23 이후버전 myisamchk 사용 (인덱스파일 *.MYI)

테이블 이상 점검 모드
    #./myisamchk  /usr/local/mysql/data/seroot/member_table.MYI
    #./myisamchk -c /usr/local/mysql/data/seroot/member_table.MYI

테이블점검시 이상만 알려주기
    #./myisamchk -s /usr/local/mysql/data/seroot/member_table.MYI

점검 상세 메세지 출력
    #./myisamchk -v /usr/local/mysql/data/seroot/member_table.MYI

상세 종합
    #./myisamchk -i /usr/local/mysql/data/seroot/member_table.MYI

특정 테이블 이상유무 가장 정밀하게 점검
    #./myisamchk -ev /usr/local/mysql/data/seroot/member_table.MYI

깨진 테이블파일 복구(정형적인 방법)
    #./myisamchk -rv /usr/local/mysql/data/seroot/member_table.MYI

안전모드 복구(r 로 복구가 안될때)
    #./myisamchk -o /usr/local/mysql/data/seroot/member_table.MYI

최후의 수단 복구방법
    테이블 파일이 완전히 깨지거나 완전히 망가진 상태가 아니라면 사용하지말것.
    #./myisamchk -re /usr/local/mysql/data/seroot/member_table.MYI

/usr/local/mysql/bin/mysqlcheck -u root PASSWORD --auto-repair -B

제   목 : 쉘 프롬프트에서 SELECT 쿼리로 DB 데이터 추출후 UPDATE 쿼리문 파일 만들기

구   분 : 운영 tip
작성자 : 이선규 (neosky) (neosky(at)smileserv.com, admin(at)nple.com, http://seroot.com)
작성일 : 2010년 8월 18일

주로 자동화된 스크립트에서 쓰이는 것으로
특정시간에 특정쿼리로 추출한 데이터를 UPDATE나 INSERT 쿼리문으로 만들때 쓰입니다.


[명령형식]

1. SELECT 쿼리로 추출한 데이터 저장

mysql -B -u[아이디] -p[비밀번호] [DB이름] -e "[SELECT 쿼리문]" | perl -pe 's\t/,/g' > [추출 파일 이름]


2. SELECT 쿼리로 1차 추출한 데이터를 UPDATE/INSERT 쿼리문에 대입하여 쿼리 파일 생성

mysql -B -u[아이디] -p[비밀번호] [DB이름] -e "[SELECT 쿼리문]" | perl -pe 's\t/,/g' | awk -F ',' '{printf "[UPDATE/INSERT 쿼리문];\n", $1, $2, ... $n}' > [추출 파일 이름]


[사용예]

mysql -B -uroot -pserootpassword shop -e "SELECT id, sale_quantity, view FROM shop_products" | perl -pe 's/\t/,/g' |awk -F ','  '{printf "UPDATE shop_products SET id='"'"'%s'"'"',sale_quantity='"'"'%s'"'"',view='"'"'%s'"'"' WHERE shop_products.id='"'"'%s'"'"';\n", $1, $2, $3, $1}' > update_query.sql

* UPDATE 또는 WHERE 절 이후 컬럼명= 이후는  ' " ' " ' %s  ' " ' " ' 입니다.
* SELECT 명령으로 컬럼 3개의 값을 DB테이블로 부터 추출한 다음 WHERE 절 이후 조건에 의한 UPDATE문을 만드는 쿼리입니다.

제   목 : mysql 바이너리 로그 리스트 표시, 삭제, 변환

구   분 : 운영 tip
작성자 : 이선규 (neosky)  (neosky(at)smileserv.com, admin(at)nple.com, http://seroot.com)
작성일 : 2010년 7월 13일



평소 잘 안쓰지만, 긴박할때 요긴하게 쓰이는 명령어이다.

바이너리 로그는 rm -f 으로 삭제할 수도 있지만, index 로 관리해주는 파일이므로
반드시 mysql shell 에서 확인 및 삭제해주도록 한다.

바이너리 로그 리스트 표시
mysql > show binary logs;
mysql > show master logs;

바이너리 로그 삭제
mysql > purge master logs to 'mysql-bin.000001';

지정된 바이너리 로그 보다 작은 숫자 파일이 제거된다.
즉, 000025번으로 한 경우 000001 ~ 000025 까지의 번호 파일이 삭제된다.

바이너리 로그 변환
/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000001 > /tmp/1.txt

보통 바이너리 로그는 용량이 큰 경우도 있으므로, 변환하여 저장될 곳은 넉넉한 곳을 지정해주도록 한다

MySQL 부하 체크시 사용하는 Slow Query
작성자 : neosky (seroot.com, nple.com)
작성일자 : 2010. 05. 04.




MySQL 서버 운영시 대량 데이터를 조회하거나 잘못된 인덱스 사용으로 인해
장시간 MySQL 프로세스가 몰리는 현상이 발생하여,
어떤 쿼리문이 이런 현상을 초래시켰는지 분석하고자 할때
Slow Query 를 이용하면 손쉽게 부하 증가의 원인이 되는 쿼리를 찾을 수 있다.


다음과 같이 CPU 자원을 많이 자치하게 됨




Slow Query 를 남기게하는 설정방법은 간단합니다.

1) /etc/my.cnf  환경설정 파일을 열어 관련 환경변수를 추가합니다.

[mysqld]
log-slow-queries=/usr/local/mysql/data/slow_query.log  <== Slow Query 가 기록될 로그 파일명
long_query_time=5  <== 쿼리타임 제한시간 (초 단위)

즉, 위 내용은 지정한 시간(초 단위) 5초를 초과하는 쿼리에 대해 /usr/local/mysql/data/slow_query.log 파일에
로그를 남기라는 의미가 됩니다.

2) 또는, mysql 데몬 구동시 적용하는 방법이 있는데
# /etc/init,d/mysqld --log-slow-queriy=Slow Query 로그 파일명 &


Slow Query 로그에 남겨지는 내용은
다음과 같이 남겨지는데...

# Time: 100504  9:45:00
# User@Host: xxxxxxx[xxxxxxxx] @ localhost [127.0.0.1]
# Query_time: 115.619313  Lock_time: 0.000089 Rows_sent: 755  Rows_examined: 3328233
SET timestamp=1272933900;
SELECT * from nextline

Query_time : 쿼리 처리 시간
Lock_time : loock 이 걸린 횟수
Row_sent : 조회 결과 Row 수
Rows_examined : 조회 대상 Row 수

Slow log 로 남은 쿼리들은 explain 을 이용하여
정상적인 index를 타고 있는지 체크해보는 방법으로 문제점을 찾가 가면 된다.

예를 들어 mysql 명령창에서 mysql > explain 문제시 되는 쿼리;

※ 결과값에 대한 상세한 분석은 소개하지 않는다.  그 몫은 각자에게 맡기며, 여기서는 프로그램에 대한
    소개와 실행방법, 그리고 결과값 출력에 대해서만 언급하고자 한다.


MySQL 튜닝값을 탐지해주는 유용한 툴이 있어 같이 공유하고자 소개합니다.
두 가지 툴 모두 간단하게 다운로드 받아 설치과정 없이 실행만 하면, 결과 값을 얻을 수가 있습니다.


1) tuning-primer.sh

쉘 스크립트 소스로 구성되어 있음.

# wget http://www.day32.com/MySQL/tuning-primer.sh
# chmod +x tuning-primer.sh  또는 sh tuning-primer.sh 로 바로 실행
# ./tuning-primer.sh 


Using login values from ~/.my.cnf

- INITIAL LOGIN ATTEMPT FAILED -

Testing for stored webmin passwords: None Found

Could not auto detect login info!

Found Sockets:
/tmp/mysql.sock

Using: /tmp/mysql.sock
Would you like to provide a different socket?: [y/N]   ----------> enter 로 skip 진행함.
Do you have your login handy ? [y/N] :  -----------> 로그인이 걸려 있으므로 y 로 진행함.
User: 계정정보 입력
Password: 계정정보 입력


Would you like me to create a ~/.my.cnf file for you? [y/N] :    ------------> 위 정보값을 .my.cnf 에 넣어두겠느냐?  enter 로 skip 진행
           
y 로 진행하는 경우 .my.cnf 파일에 다음 내용이 기록되니 보안을 위해서는 skip 하도록  함.

[client]
user=계정id
password=계정pw
socket=/tmp/mysql.sock



이후 아래와 같이 결과값 출력
> 현재 값이 괜찮은 항목에 대해서는 연한녹색으로 표시
> 현재 값보다 튜닝이 필요한 항목에 대해서는 붉은색 계열로 표시

=================================================

        -- MYSQL PERFORMANCE TUNING PRIMER --
             - By: Matthew Montgomery -

MySQL Version 4.0.27-log  is EOL please upgrade to MySQL 4.1 or later

Uptime = 70 days 5 hrs 7 min 11 sec
Avg. qps = 5
Total Questions = 32071286
Threads Connected = 1

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10 sec.
You have 2889 out of 32071307 that take longer than 10 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is enabled
tuning-primer.sh: line 541: [: : integer expression expected

WORKER THREADS
Current thread_cache_size = 8
Current threads_cached = 7
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 100
Current threads_connected = 1
Historic max_used_connections = 17
The number of used connections is 17% of the configured maximum.
Your max_connections variable seems to be fine.

MEMORY USAGE
Max Memory Ever Allocated : 504 M
Configured Max Per-thread Buffers : 458 M
Configured Max Global Buffers : 426 M
Configured Max Memory Limit : 884 M
Physical Memory : 1.96 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 98 M
Current key_buffer_size = 384 M
Key cache miss rate is 1 : 488
Key buffer free ratio = 75 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 32 M
Current query_cache_used = 21 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 68.09 %
%yellow
No query_cache_min_res_unit is defined.  Using MySQL < 4.1 cache fragmentation can be inpredictable
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 132.00 K
You have had 669 queries where a join could not use an index properly
You should enable "log-long-format"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 1134 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
You currently have open more than 75% of your open_files_limit
You should set a higher value for open_files_limit in my.cnf

TABLE CACHE
Current table_cache value = 512 tables
You have a total of 6623 tables
You have 512 open tables.
Current table_cache hit rate is 0%, while 100% of your table cache is in use
You should probably increase your table_cache

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 32 M
Of 100119 temp tables, 4% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 1 M
Current table scan ratio = 253 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 2166
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'

=================================================


2) mysqltuner.pl

펄 소스로 구성되어 있음.

  # wget http://mysqltuner.pl/mysqltuner.pl
  # perl mysqltuner.pl



 >>  MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root계정
Please enter your MySQL administrative password: root패스워드

인증과정 없이는 실행할 수 없다.

이 프로그램 툴 역시 색상으로 구분해주므로, 어디가 그렇고 그런지 파악하기 쉽게 되어 있다.
특히 말머리에 !!, OK 만으로도 상태가 어떤지는 알 것이다.

Recommendations 부분에는 어떻게 튜닝해줘야 하는지 어드바이스까지 해준다.

=================================================

 >>  MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.77-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 493M (Tables: 304)
[--] Data in InnoDB tables: 282M (Tables: 12)
[!!] Total fragmented tables: 5

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 19h 32m 14s (110M q [704.797 qps], 11M conn, TX: 238B, RX: 6B)
[--] Reads / Writes: 79% / 21%
[--] Total buffers: 442.0M global + 16.4M per thread (5000 max threads)
[!!] Maximum possible memory usage: 80.4G (1379% of installed RAM)
[OK] Slow queries: 0% (4K/110M)
[OK] Highest usage of available connections: 15% (781/5000)
[OK] Key buffer size / total MyISAM indexes: 384.0M/163.0M
[OK] Key buffer hit rate: 100.0% (305M cached / 95K reads)
[OK] Query cache efficiency: 74.0% (52M cached / 71M selects)
[!!] Query cache prunes per day: 159148
[OK] Sorts requiring temporary tables: 0% (21K temp sorts / 2M sorts)
[OK] Temporary tables created on disk: 0% (8K on disk / 937K total)
[OK] Thread cache hit rate: 97% (269K created / 11M connections)
[OK] Table cache hit rate: 33% (512 open / 1K opened)
[OK] Open file limit used: 2% (680/25K)
[OK] Table locks acquired immediately: 99% (24M immediate / 24M locks)
[!!] InnoDB data size / buffer pool: 282.4M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    Enable the slow query log to troubleshoot bad queries
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (> 32M)
    innodb_buffer_pool_size (>= 282M)

=================================================

< tuning-primer.sh 실행 화면 >

< mysqltuner.pl 실행 화면 >

100%라고 장담하지 못하지만 다음과 같이 변환 명령을 내려주면,
안전하게 5.0에서 4.1 버전으로 덤프된 데이터를 넣을 수 있다.

원본 MySQL 버전 : 5.0.75  (UTF-8)
대상 MySQL 버전 : 4.1.22  (euckr)

mysqldump --compatible=mysql40 --default-character-set=euckr -u root -p data > data_mysql40.sql 

mysql 3.23.58 버전에서는 /etc/my.cnf 에 값을 넣는 것이 아니라     mysql 데몬 시작시 아래와 같이 옵션 값을 주어 시작하여야 합니다.       /etc/init.d/mysql  134라인에 다음과 같이 max connections 값을 넣어준 다음 재시작 하면 변경된 값을 확인할 수 있다.     $bindir/safe_mysqld --datadir=$datadir -O max_connections=512 --pid-file=$pid_file &

1. MySQL 데몬 실행 중지

# /etc/init.d/mysql stop
또는
# killall -9 mysqld_safe


2. 인증제외하여 MySQL 데몬 실행

# /usr/local/mysql/bin/mysqld_safe --skip-grant &


3. 비밀번호 없이 접속후 mysql DB의 user 테이블에서 root 패스워드 변경

# /usr/local/mysql/bin/mysql -u root mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.45-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> update user set password=password('first11') where user='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye


4. 임시 데몬 내리고 정상적인 MySQL 데몬 실행

# killall -9 mysqld_safe

# /etc/init.d/mysqld start