MySQL

지각생 연습장

목차

기본

  • 구동 스크립트(데비안)
/etc/init.d/mysql (start|stop|restart ..)
  • 터미널 클라이언트 (접속 명령)
    • -p 다음에 공백으로 두면, 암호를 물어본다(입력이 화면에 표시되지 않음 - 더 안전)
mysql -u<user> -p<pass> [DB]
  • DB 추가하기
    1. 터미널 클라이언트 접속후, create <DB name>
    2. 혹은, mysqladmin 명령어 활용
# mysqladmin create <DB name>
안되면 -uroot -p 포함

계정

  • mysql DB의 user, db 테이블에 레코드 등록
    1. 사용자 추가 : user 테이블에 추가. 보안상 기본적으로 모든 권한을 뺌(대부분 "N"으로 설정)
    2. db 에 접근 권한 설정. 사용자가 사용할 DB에만 한정해서 접근 권한 줌(대부분 "Y"로 설정)
    • 한번에 처리하기 : grant 명령 이용
mysql> grant all privileges to 'user'@'localhost' on <DB>.* identified by 'password';
  • DB 상태 갱신 : 마지막에 꼭 해줘야 함
mysql> flush privileges;

Dump 받기

  • 시놉시스
    mysqldump -h <host> -u <user> -p<pass> [options] DB > output_file
  • 유용한 옵션
    • -t
      테이블 생성 구문 뺌(데이터만 덤프)
    • --no-data
      구조만
    • --skip-add-drop-table
      테이블이 있으면 삭제하고 만들라는 말을 없앤다.
  • 데이터만 받기 : Insert 구문만
    mysqldump --skip-triggers --compact --no-create-info

Dump 파일로부터 DB 복원

mysql -u<user> -p<pass> DB < dump_file

캐릭터셋 문제

[mysql] 
default-character-set = utf8
 
[mysqld] 
character-set-client-handshake=FALSE
init_connect="SET collation_connection = utf8_general_ci"
init_connect="SET NAMES utf8"
default-character-set = utf8
character-set-server = utf8
collation-server = utf8_general_ci
 
[client]  
default-character-set = utf8
 
[mysqldump]  
default-character-set = utf8

MySQL 4 to 5

  • mysql5 부터는 "CREATE TABLE "구문에서 테이블 이름을 꼭 ``로 묶어줘야 된다. 혹 그렇지 않은게 있다면 아래 명령으로 바꿔버려~
# sed -i 's/CREATE TABLE \(.*\) /CREATE TABLE `\1` /' *.sql

에러 코드

성능 튜닝

Connection 과 Memory

  • 출처 : MySQL DataBase 서버 튜닝 - Connection과 Memory
    • 계산법 1
      Cache Miss Rate(%) = Threads_created / Connections * 100
      Connection Miss Rate(%) = Aborted_connects / Connections * 100
      Connection Usage(%) = Threads_connected / max_connections * 100
    • Connection Usage(%)가 100% 라면 max_connections 수를 증가시켜 주십시요. Connection 수가 부족할 경우 Too Many Connection Error 가 발생합니다.
    • DB 서버의 접속이 많은 경우는 wait_timeout 을 최대한 적게 (10~20 정도를 추천) 설정하여 불필요한 연결을 빨리 정리하는 것이 좋습니다. 그러나 Connection Miss Rate(%) 가 1% 이상이 된다면 wait_timeout 을 좀 더 길게 잡는 것이 좋습니다.
    • Cache Miss Rate(%) 가 높다면 thread_cache_size를 기본값인 8 보다 높게 설정하는 것이 좋습니다. 일반적으로 threads_connected 가 Peak-time 시 보다 약간 낮은 수치로 설정하는 것이 좋습니다.
    • MySQL 서버는 외부로 부터 접속 요청을 받을 경우 인증을 위해 IP 주소를 호스트네임으로 바꾸는 과정을 수행하여 접속시에 불필요한 부하가 발생하게 됩니다. skip-name-resolve를 설정하시고 접속시에 IP 기반으로 접속을 하게 되면 hostname lookup 과정을 생략하게 되어 좀 더 빠르게 접속을 하실 수 있습니다.
    • 계산법 2
      Key Buffer Usage = 1 - ((Key_blocks_unused × key_cache_block_size) / key_buffer_size)
      Key_reads/Key_read_requests Rate(%) = Key_reads/Key_read_requests * 100
      Key_reads/Key_read_requests Relative Rate(%) = (1- ^Key_reads/^Key_read_requests) * 100
      * ^Key_Reads = Current Key_Rreads - Previous Key_Reads
    • key_buffer_size는 총 메모리 크기의 25% 정도의 크기로 설정하는 것이 좋습니다.
    • Key_reads/Key_read_requests Rate(%)은 일반적으로 1%보다 적습니다. 1% 보다 높다면 Key Cache가 아닌 디스크를 읽은 경우가 많다고 판단할 수 있습니다. 또한 Key_reads/Key_reads_requests Relative Rate(%) 값이 지속적으로 90% 이상일 경우는 key_buffer_size가 효율적으로 설정되어 있다고 생각하시면 됩니다. 하지만 데이터베이스가 엄청나게 크거나 여러 데이터를 골고루 많이 읽는 데이터베이스라면 아무리 많은 양의 키 캐시를 설정해도 90% 이상의 적중률을 얻을 수는 없습니다.

설정 예

open 서버 설정 파일 내용. 이게 최선이란 뜻은 아님.

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /NodongDB
tmpdir          = /tmp
language        = /usr/share/mysql/english
skip-external-locking
skip-name-resolve
old_passwords   = 1

wait_timeout = 40
connect_timeout = 25

key_buffer              = 256M
max_allowed_packet      = 12M
table_cache             = 256
thread_stack            = 128K

max_connections         = 150
query_cache_limit       = 1048576
query_cache_size        = 26214400
query_cache_type        = 1
log             = /var/log/mysql/mysql.log
log-slow-queries        = /var/log/mysql/mysql-slow.log
max_binlog_size         = 104857600
skip-bdb

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]

[isamchk]
key_buffer              = 16M

테이블 최적화

문제해결

mysql 의 root 패스워드 분실

  1. mysql 데몬 중지
  2. safe_mysqld -Sg & 옵션으로 시작 => 루트 패스워드 없이 mysql에 접속할 수 있다
  3. 접속후 root 패스워드를 변경
mysql> use mysql
mysql> update user set password=password('패스워드‘) where user ="root";
mysql> flush privileges;
  • --skip-grant-tables 옵션(mysqld)
    This option causes the server not to use the privilege system at all, which gives anyone with access to the server unrestricted access to all databases. You can cause a running server to start using the grant tables again by executing mysqladmin flush-privileges or mysqladmin reload command from a system shell, or by issuing a MySQL FLUSH PRIVILEGES statement after connecting to the server. This option also suppresses loading of user-defined functions (UDFs).
개인 도구