MySQL
지각생 연습장
목차 |
기본
- 구동 스크립트(데비안)
/etc/init.d/mysql (start|stop|restart ..)
- 터미널 클라이언트 (접속 명령)
- -p 다음에 공백으로 두면, 암호를 물어본다(입력이 화면에 표시되지 않음 - 더 안전)
mysql -u<user> -p<pass> [DB]
- DB 추가하기
- 터미널 클라이언트 접속후, create <DB name>
- 혹은, mysqladmin 명령어 활용
# mysqladmin create <DB name> 안되면 -uroot -p 포함
계정
- mysql DB의 user, db 테이블에 레코드 등록
- 사용자 추가 : user 테이블에 추가. 보안상 기본적으로 모든 권한을 뺌(대부분 "N"으로 설정)
- 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
- 테이블이 있으면 삭제하고 만들라는 말을 없앤다.
- -t
- 데이터만 받기 : 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% 이상의 적중률을 얻을 수는 없습니다.
- 계산법 1
설정 예
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
테이블 최적화
- optimize table
- mysqlcheck -o -A -p
- Converting MyISAM Tables to MyISAM
- Tuning MySQL for Drupal
문제해결
mysql 의 root 패스워드 분실
- mysql 데몬 중지
- safe_mysqld -Sg & 옵션으로 시작 => 루트 패스워드 없이 mysql에 접속할 수 있다
- 접속후 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).