본문 바로가기
MYSQL / R

쿼리 사용방법

본문

MySQL 실행과 종료
- MySQL 실행
사용형식 : /usr/local/mysql/bin/mysqld_safe [각종 옵션들] &
- MySQL 종료
사용형식 : /usr/local/mysql/bin/mysqladmin -u root -p shutdown

* MySQL 기본관리
- MySQL 접속
사용형식 : /usr/local/mysql/bin/mysql -u MySQL_계정명 -p 접속할_데이터베이스명 -h 접속할 호스트이름 -P 접속포트번호 -S /tmp/mysql.sock
          mysql                : MySQL 데이터베이스로 접속하기 위한 MySQL 클라이어트 프로그램
    -u                    : 접속할 MySQL 계정(사용자)명을 지정하기 위한 옵션
    MySQL_계정명          : 데이터베이스로 접속할 MySQL의 사용자(계정)명
    -p                    : 패스워드를 입력하기 위한 옵션(패스워드가 없을 경우에 생략가능함)
    접속할_데이터베이스명 : 접속 후 사요할 데이터베이스명
    -h                    : 접속할 호스트이름
    -P                    : 접속할 포트번호
    -S                    : 접속할 MySQL 소켓파일 위치 지정
# /usr/local/mysql/bin/mysql -u root -p mysql
Enter password: ********
# /usr/local/mysql/bin/mysql -u picasso -p picasso_db
Enter password: ********
# /usr/local/mysql/bin/mysql -u root -p'12345678' mysql
# /usr/local/mysql/bin/mysql -u user -p'11111111' user_db
# /usr/local/mysql/bin/mysql -u ruser -p ruser_db -h 192.168.0.111 -P 3306
Enter password: ********
- 패스워드 재설정
사용형식 : /usr/local/mysql/bin/mysqladmin -u 계정명 password 변경할_패스워드
# /usr/local/mysql/bin/mysqladmin -u root password 12345678 -> 기존에 패스워드가 존재하지 않을 때
Enter password: ********
# /usr/local/mysql/bin/mysqladmin -u root -p password 12345678 -> 기존에 패스워드가 존재할 때
Enter password: ********
# /usr/local/mysql/bin/mysqladmin -u user -p password 11111
Enter password: ********
# /usr/local/mysql/bin -u root -p'12345678' mysql
mysql> update user set password = password('12345678') where user = 'root';
mysql> set password for root = password('2345');
- 쉘상태에서 특정파일에 저장된 SQL문 실행하기
# /usr/local/mysql/bin -u user -p user_db < /home/user/user.sql
Enter password: ********
- 새로운 MySQL 사용자를 위한 MySQL 설정
mysql> create database user_db;
mysql> grant all on user_db.* to user@'localhost' identified by 'password';
## mysql> revoke all on user_db.* from user@'localhost';
## mysql> revoke all on user_db.* from user;
## mysql> delete from mysql.user where user = 'user';

* MySQL 패스워드 복구
1. 실행중인 MySQL을 종료한다.
# ps -ef | grep mysqld
# killall mysqld
2. table grant 권한없이 MySQL을 실행한다.
# /usr/local/mysql/bin/mysqld_safe --skip-grant-table &
3. 패스워드없이 MySQL root 계정으로 MySQL에 접속한다.
# /usr/local/mysql/bin/mysql -u root mysql -> 패스워드 지정 안함
4. UPDATE 문으로 MySQL root사용자의 패스워드를 변경한다.
mysql> update user set password = password('12345678') where user = 'root';
5. "flush privileges"로 MySQL데이터들을 동기화한다.
mysql> flush privileges;
6. MySQL을 빠져나와서 실행했던 MySQL을 종료한다.
# killall mysqld
# /usr/local/mysql/bin/mysqladmin -u root shutdown
7. 일반적인 방법으로 MySQL을 실행한다.
# /usr/local/mysql/bin/mysqld_safe --skip-grant-table &
8. 패스워드를 사용하여 MySQL의 root계정으로 접속하여 확인한다.

* "too many connections" 에러 해결방법
원인: 이에 대한 원인을 설명하기 위해서는 php에서 사용하는 MySQL 연결함수인 mysql_connect()와 mysql_pconnect의 차이점 그리고, mysql_close()함수에 대한 정확한 이해가 필요하다.
      mysql_connect()함수를 이용하여 MySQL에 연결하였다면 해당 스크립트가 종료됨과 동시에 mysql_close()함수를 호출하지 않았다 하더라도 자동으로 연결이 종료된다.
      하지만, mysql_pconnect()함수는 해당 스크립트가 종료된 후 mysql_close()함수가 호출되어도 연결이 끊어지지 않은 채로 계속 연결을 유지하고 있다.
      따라서, 얼핏보기에는 "too many connections"라는 에러메시지는 mysql_pconnect()라는 함수의 사용때문에 발생하는 것 같지만 (물로 그런 이유도 있을 수 있지만) 근본적인 원인은 이와 다를 수 있다.
      결론적으로 "mysqladmin -u -p variables"의 결과로서 볼 수 있었던 MySQL의 환경변수값들 가운데 "wait_timeout"의 값만큼 서버에 그대로 연결을 유지한 채로 남아있는 것이다.
      따라서, 이것이 "too many connections" 에러가 발생하는 긍극적인 원인이 되는 것이다.
      이를 해겨하면 MySQL이 빠른 응답을 할 수 있도록 서버 하드웨어 사양(CPU, 메모리 등)을 높이는 방법도 있겠지만 급하게 조치해야하는 경우에는 MySQL의 필요한 변수의 값을 보다 높게 설정하는 방법이 현명할 것이다.
      "mysqladmin -u root -p variables"라고 하면 MySQL의 전체 환경변수의 값을 확인할 수 있다.
      이들 MySQL 환경변수 가운데 다음 3가지의 값을 현재보다 높게 설정함으로서 "too many connection" 에러를 응급초지할 수 있다.
      - max_connection : MySQL에 connect할 수 있는 최대 개수
      - table_cache    : MySQL의 테이블 캐쉬크기
      - wait_timeout  : MySQL에 connect하여 쿼리를 지속할 수 있는 시간
      # /usr/local/mysql/bin/mysqladmin -u root -p variables | grep max_connections
      | max_connections                | 100                                  |
      # /usr/local/mysql/bin/mysqladmin -u root -p variables | grep table_cache
      | table_cache                    | 64                                    |
      # /usr/local/mysql/bin/mysqladmin -u root -p variables | grep wait_timeout
      | innodb_lock_wait_timeout        | 50                                    |
      | table_lock_wait_timeout        | 50                                    |
      | wait_timeout                    | 28800                                |
      # /usr/local/bin/mysqld_safe -O max_connections=500 -O table_cache=256 -O wait_timeout=57600 &

* MySQL 관리자 전용 유틸리티 mysqladmin
사용형식 : /usr/local/mysql/bin/mysqladmin -u root -p 명령어
- 새로운 데이터베이스 생성하기
사용형식 : /usr/local/mysql/bin/mysqladmin -u root -p create 새로운_데이터베이스명
# /usr/local/mysql/bin/mysqladmin -u root -p create user_db2
Enter password: ********
- 특정 데이터베이스 삭제하기
사용형식: /usr/local/mysql/bin/mysqladmin -u root -p drop 삭제할_데이터베이스명
# /usr/local/mysql/bin/mysqladmin -u root -p drop user_db2
Enter password: ********
- MySQL 권한테이블 갱신하기
사용형식 : /usr/local/mysql/bin/mysqladmin -u root -p reload
# /usr/local/mysql/bin/mysqladmin -u root -p reload
Enter password: ********
- MySQL에 접속한 사용자 확인하기
사용형식: /usr/local/mysql/bin/mysqladmin -u root -p processlist
# /usr/local/mysql/bin/mysqladmin -u root -p processlist
- 접속되어 있는 MySQL 사용자(thread) 접속 끊기
사용형식 : /usr/local/mysql/bin/mysqladmin -u root -p kill 쓰레드번호
# /usr/local/mysql/bin/mysqladmin -u root -p processlist -> 종료시킬 쓰레드 확인
# /usr/local/mysql/bin/mysqladmin -u root -p kill 8  -> Id가 8인 쓰레드 종료
- MySQL의 간단한 실행정보 확인하기
사용형식 : /usr/local/mysql/bin/mysqladmin -u root -p status
# /usr/local/mysql/bin/mysqladmin -u root -p status
Uptime: 2095884  Threads: 12  Questions: 2578614  Slow queries: 0  Opens: 0  Flush tables: 1  Open tables: 138  Queries per second avg: 1.230
- MySQL의 현재 상황 자세히 살펴보기
사용형식 : /usr/local/mysql/bin/mysqladmin -u root -p extended-status
# /usr/local/mysql/bin/mysqladmin -u root -p extended-status
- MySQL의 환경변수를 확인하기
사용형식 : /usr/local/mysql/bin/mysqladmin -u root -p variables
# /usr/local/mysql/bin/mysqladmin -u root -p variables
- 현재 MySQL의 정확한 버전과 여러 가지 실행정보 확인하기
사용형식 : /usr/local/mysql/bin/mysqladmin -u root -p version
# /usr/local/mysql/bin/mysqladmin -u root -p version
- MySQL이 죽었는지 살았는지 확인하기
사용형식 : /usr/local/mysql/bin/mysqladmin -u root -p ping
# /usr/local/mysql/bin/mysqladmin -u root -p ping

* MySQL 로그
- 쿼리로그 옵션 형식 : --log=쿼리로그파일명
                      이 형식을 사용하면 MySQL 데이터 디렉토리에 "쿼리로그파일명"으로 지정된 로그파일이 생성되며 SQL 쿼리로그를 기록한다.
- 바이너리로그 옵션 형식 : --log-bin=바이너리로그파일명
                          바이너리로그를 보기위해서는 /usr/local/mysql/bin/mysqlbinlog 명령어를 사용해야 한다.
      이 형식을 사용하면 MySQL 데이터디렉토리에 "바이너리로그파일명"으로 지정된 바이너리로그파일이 생성되면 MySQL데이터의 변경내역을 기록한다.
- SLOW쿼리 옵션 형식 : --log-slow-queries=SLOW쿼리파일명
                      이 형식을 사용하면 MySQL 환경변수 "long_query_time"에 초단위로 지정된 시간보다 초과하는 쿼리문만을 기록한다.

* 텍스트파일의 데이터를 MySQL의 특정 데이터베이스로 입력하기
사용형식 : /usr/local/mysql/bin/mysqlimport -u MySQL사용자 -p 데이터베이스명 텍스트파일명
          이 형식으로 실행하면 "텍스트파일명"에 저장되어 있는 데이터가 "데이터베이스명"의 특정 "테이블"의 데이터로 입력된다.
    위의 형식에서 데이터가 저장될 테이블이름은 "텍스트파일명"과 동일한 테이블이 된다.
    위의 형식에서 "텍스트파일명"은 "테이블명"과 동일한 이름이어야 한다.
    또한 저장될 테이블은 이미 생성되어 있어야 한다.
# cat addressbook
PARKSUNGSOO 011-111-2222 SEOUL A+
JUNGWOOYOUNG 011-222-3333 SEOUL B+
LEEJAESUK 016-222-1111 BUSAN C+
CHOYUEJIN 019-333-4444 BUSAN D+
# /usr/local/mysql/bin/mysqlimport -u root -p user_db2 addressbook
Enter password: ********
user_db2.addressbook: Records: 4 Deleted: 0 Skipped: 0 Warning: 0

* MySQL 데이터베이스 스키마 확인
사용형식 : /usr/local/mysql/bin/mysqlshow [옵션] [데이터베이스 [테이블 [컬럼]]]
          1. 위의 형식에서 특정 데이터베이스명이 주어지지 않는다면 MySQL내에 존재하는 모든 데이터베이스들을 나열한다.
    2. 위의 형식에서 특정 테이블명이 주어지지 않는다면 지정된 데이터베이스내의 모든 테이블들을 나열한다.
    3. 위의 형식에서 특정 컬럼이 주어지지 않는다면 지정된 테이블내에 존재하는 모든 컬럼들과 컬럼타입들을 나열한다.
- MySQL에 존재하는 모든 데이터베이스들 확인하기
# /usr/local/mysql/bin/mysqlshow -u root -p
mysql> show databases;
- 특정 데이터베이스의 테이블들 확인하기
# /usr/local/mysql/bin/mysqlshow -u root -p user_db
mysql> use user_db;
mysql> show tables;
- 특정 테이블의 컬럼정보들 확인하기
# /usr/local/mysql/bin/mysqlshow -u root -p user_db addressbook
mysql> use user_db;
mysql> show columns from addressbook;
mysql> desc addressbook;
- 특정 테이블의 하나의 컬럼만 확인하기
# /usr/local/mysql/bin/mysqlshow -u root -p user_db addressbook name

* MySQL 데이터베이스 데이터 백업과 복구
- /usr/local/mysql 디렉토리 전체를 압축백업하기
# cd /usr/local
# tar cvfpz /backup/mysql.tar.gz mysql
- MySQL 데이터 백업하기
사용형식1 : /usr/local/mysql/bin/mysqldump [옵션] DB [TABLES ..] > 파일명
            DB는 백업대상이 되는 데이터베이스명이고 TABLES는 지정한 백업대상 데이터베이스내에 존재하는 테이블명이다.
    이 백업의 의미는 DB의 데이터베이스내에 존재하는 테이블의 내용을 백업해서 "파일명"에 저장하라는 의미이다.
사용형식2 : /usr/local/mysql/bin/mysqldump [옵션] --databases [옵션] DB1 [DB2 ..] > 파일명
            --databases라는 옵션에 의해 DB1 DB2 의 데이터베이스들을 백업하여 "파일명"에 저장한다.
    즉 백업대상이 되는 데이터베이스가 2개 이상이 될 때에는 --databases 옵션을 사용하고 그 뒤에 백업할 데이터베이스를 지정한다.
사용형식2 : /usr/local/mysql/bin/mysqldump [옵션] --all-databases [옵션] > 파일명
            --all-databases라는 옵션은 MySQL내에 존재하는 모든 데이터베이스를 백어밷상으로 한다는 의미이다.
- 특정 데이터베이스 데이터 백업과 복구
백업형식 : /usr/local/mysql/bin/mysqldump -u DB계정명 -p 백업대상_데이터베이스명 > 저장할_파일명
복구형식 : /usr/local/mysql/bin/mysql -u DB계정명 -p 복구할_데이터베이스명 < 저장한_파일명
# /usr/local/mysql/bin/mysqldump -u root -p mysql > mysql.sql
# /usr/local/mysql/bin/mysql -u root -p mysql < mysql.sql
# /usr/local/mysql/bin/mysqldump -u user -p user_db > user_db.sql
# /usr/local/mysql/bin/mysql -u user -p user_db < user_db.sql
- 특정 데이터베이스의 특정 테이블의 백업과 복구
백업형식 : /usr/local/mysql/bin/mysqldump -u DB계정명 -p 데이터베이스명 테이블명 > 저장할_파일명
복구형식 : /usr/local/mysql/bin/mysql -u DB계정명 -p 데이터베이스명 < 저장한_파일명
# /usr/local/mysql/bin/mysqldump -u user -p user_db addressbook > addressbook.sql
# /usr/local/mysql/bin/mysql -u user -p user_db < addressbook.sql
- 여러 개의 데이터베이스 한번에 백업과 복구
백업형식 : /usr/local/mysql/bin/mysqldump -u DB계정명 -p --databases [옵션] DB1 [DB2 ..] > 저장할_파일명
          CREATE DATABASE문과 USE문이 추가되어 있다.
복구형식 : /usr/local/mysql/bin/mysql -u DB계정명 -p < 저장한_파일명
# /usr/local/mysql/bin/mysqldump -u user -p --databases user_db user_db2 > user_dbs.sql
# /usr/local/mysql/bin/mysql -u user -p < user_dbs.sql
-- MySQL 전체 데이터베이스 백업하기
사용형식 : /usr/local/mysql/bin/mysqldump -u DB계정명 -p --all-databases > 저장할_파일명
# /usr/local/mysql/bin/mysqldump -u root -p --all-databases > all_dbs.sql
- 기존 테이블을 삭제후 백업된 파일로 복구하기 위한 백업방법
  mysqldump문으로 데이터베이스 백업시에 각각의  CRETE TABLE문 앞에 DROP TABLE문 삽입하기
# /usr/local/mysql/bin/mysqldump -u user -p --add-drop-table user_db > user_db.sql
# /usr/local/mysql/bin/mysql -u user -p user_db < user_db.sql
- 데이터베이스 백업시 에러발생율 무시하고 계속 진행하기
사용형식 : /usr/local/mysql/bin/mysqldump -u 사용자명 -p -f DB명 > 파일명
# /usr/local/mysql/bin/mysqldump -u user -p -f user_db > user_db.sql
- 원격서버의 MySQL 데이터베이스 백업하기 #1(기본포트 사용)
사용형식 : /usr/local/mysql/bin/mysqldump -u 사용자명 -p -h 호스트명(IP주소) DB명 > 파일명
# /usr/local/mysql/bin/mysqldump -u user -p -h 192.168.0.111 user_db > user_db.sql
- 원격서버의 MySQL 데이터베이스 백업하기 #2(특정 포트번호 지정)
사용형식 : /usr/local/mysql/bin/mysqldump -u 사용자명 -p -h 호스트명(IP주소) -P 포트번호 DB명 > 파일명
# /usr/local/mysql/bin/mysqldump -u user -p -h 192.168.0.111 -P 22222 user_db > user_db.sql
- 데이터 백업시 CREATE DATABASE 문을 생략하여 백업하기
사용형식 : /usr/local/mysql/bin/mysqldump -u 사용자명 -p -n [옵션] DB명 > 파일명
          /usr/local/mysql/bin/mysqldump -u 사용자명 -p --no-create-db [옵션] DB명 > 파일명
# /usr/local/mysql/bin/mysqldump -u user -p -n --databases user_db user_db2 > user_dbs.sql
# /usr/local/mysql/bin/mysqldump -u root -p -n --all-databases > all_dbs.sql
-- 데이터 백업시에 CREATE TABLE문을 생략하여 백업하기
사용형식 : /usr/local/mysql/bin/mysqldump -u 사용자명 -p -t DB명 > 파일명
          /usr/local/mysql/bin/mysqldump -u 사용자명 -p --no-crate-info DB명 > 파일명
# /usr/local/mysql/bin/mysqldump -u user -p -t user_db > user_db.sql
- 데이터는 백업하지않고 테이블 스키마만 백업하기
사용형식 : /usr/local/mysql/bin/mysqldump -u 사용자명 -p -d DB명 > 파일명
          /usr/local/mysql/bin/mysqldump -u 사용자명 -p --no-data DB명 > 파일명
# /usr/local/mysql/bin/mysqldump -u user -p -d user_db > user_db.sql
-- 특정 데이터베이스의 조건에 맞는 데이터만 백업하기
사용형식 : /usr/local/mysql/bin/mysqldump -u 사용자명 -p -w="WHERE 조건문" DB명 테이블명 > 파일명
          /usr/local/mysql/bin/mysqldump -u 사용자명 -p --where="WHERE 조건문" DB명 테이블명 > 파일명
# /usr/local/mysql/bin/mysqldump -u user -p -w="admin_id = 'admin'" user_db addressbook > admin_id.sql
- select문을 이용한 데이터 백업 및 복구
mysql> select * into outfile './backup.sql' from addressbook;
mysql> load data infile './backup.sql' into table addressbook;

* 데이터베이스의 깨진 테이블파일 복구
- 데이터파일 구성
테이블 하나에 아래의 파일형식을 가진 파일이 3개씩 생성되어 그 테이블의 데이터를 실제로 저장하는 용도로 사용된다.
*.MYD: 해당 테이블의 데이터가 저장되는 테이블 데이터파일이다.
*.MYI: 해당 테이블의 인덱스정보가 저장되는 테이블 인덱스파일이다.
*.frm: 해당 테이블의 테이블구조가 저장되는 테이블 스키마파일이다.
- myisamchk
사용형식 : myisamchk [옵션] 점검복구새상테이블인덱스파일(*.MYI)
          주의할 것은 myisamchk의 복구 대상파일은 반드시 MySQL의 인덱스파일이라는 것이다.
          또한 반드시 MySQL을 종료한 후에 사용하라.
- MySQL 테이블파일의 이상유무 점검
# /usr/local/mysql/bin/myisamchk /usr/local/mysql/var/user_db/addressbook.MYI
# /usr/local/mysql/bin/myisamchk -c /usr/local/mysql/var/user_db/addressbook.MYI
# /usr/local/mysql/bin/myisamchk --check /usr/local/mysql/var/user_db/addressbook.MYI
- MySQL 테이블 점검시 이상발견시만 알려주기
# /usr/local/mysql/bin/myisamchk -s /usr/local/mysql/var/user_db/addressbook.MYI
# /usr/local/mysql/bin/myisamchk --silent /usr/local/mysql/var/user_db/addressbook.MYI
- MySQL의 테이블 점검(복구)시 가능한 상세하게 메시지 출력하기
# /usr/local/mysql/bin/myisamchk -v /usr/local/mysql/var/user_db/addressbook.MYI
# /usr/local/mysql/bin/myisamchk --verbose /usr/local/mysql/var/user_db/addressbook.MYI
- MySQL의 테이블 이상유무 점검시 결과를 상세히 종합하여 보여주기
# /usr/local/mysql/bin/myisamchk -i /usr/local/mysql/var/user_db/addressbook.MYI
# /usr/local/mysql/bin/myisamchk --information /usr/local/mysql/var/user_db/addressbook.MYI
-- MySQL의 특정테이블 이상유무를 가장 정밀하게 점검하기
# /usr/local/mysql/bin/myisamchk -ev /usr/local/mysql/var/user_db/addressbook.MYI
# /usr/local/mysql/bin/myisamchk --extend-check --verbose /usr/local/mysql/var/user_db/addressbook.MYI
- 정형적인 방법으로 MySQL의 깨진 테이블파일 복구하기
# /usr/local/mysql/bin/myisamchk -rv /usr/local/mysql/var/user_db/addressbook.MYI
# /usr/local/mysql/bin/myisamchk --recover --verbose /usr/local/mysql/var/user_db/addressbook.MYI
-- MySQL의 깨진 테이블파일 안전모드로 복구하기
# /usr/local/mysql/bin/myisamchk -o /usr/local/mysql/var/user_db/addressbook.MYI
# /usr/local/mysql/bin/myisamchk --safe-recover /usr/local/mysql/var/user_db/addressbook.MYI
  -r옵션을 사용하여 테이블파일을 복구하는 것은 MySQL의 테이블 복구 방법 가운데 가장 정형적인 방법이다.
  하지만 데이터의 안전성을 고려한다면 -o옵션을 사용하라.
  깨진 테이블파일의 오류를 복구할 때에 시간이 좀 더 소요되더라도 데이터의 안전성이 걱정이 된다면 -o옵션을 사용하라.
  -o옵션을 사용하면 전통적인 방법으로 복구를 하며 -r옵션을 사용하는 것보다 속도는 좀 떨어지지만 -r옵션으로 복구하지 못하는 데이터파일을 복구할 수 있다.
- MySQL 테이블이 완전히 깨졌을 때의 최후의 복구방법
# /usr/local/mysql/bin/myisamchk -re /usr/local/mysql/var/user_db/addressbook.MYI
# /usr/local/mysql/bin/myisamchk -oe /usr/local/mysql/var/user_db/addressbook.MYI
- MySQL 내부명령어 사용
사용형식 : repair table 테이블명;
mysql> repair table addressbook;
  • 페이스북으로 보내기
  • 트위터로 보내기
  • 구글플러스로 보내기

페이지 정보

최고관리자 작성일10-03-30 10:07 조회912회 댓글0건

댓글목록

등록된 댓글이 없습니다.

MYSQL / R 목록

게시물 검색

사이트 정보

  • 회사명 주인있소 / 대표 소담
  • 주소 강원도 속초시
  • 사업자 등록번호 123-45-67890
  • 전화 010-2026-0626 / 팩스 없음
  • 통신판매업신고번호 제 OO구 - 123호
  • 개인정보관리책임자 정보책임자명

고객센터

상단으로