wrnly2

MySQL에 대한 전반적인 사항을 정리한다.
일반적으로 널리 사용되는 관계형 데이터베이스이다.

• 홈페이지 : http://www.mysql.com/, http://www.mysqlkorea.co.kr/
• 다운로드 : http://dev.mysql.com/downloads/
• Driver  : http://www.mysql.com/products/connector/
• 라이센스 : GNU General Public License 2.0 (GPL), MySQL Commercial License
• 플랫폼  : Windows, Unix, Linux, IBM AIX, ...

설치 가이드

아래 설치 가이드는 Windows Server 2003을 기준으로 작성한 것 입니다. 설치 환경이 틀릴 경우에는 별도의 주석을 달아 주세요.

MYSQL 서버 설치

• http://dev.mysql.com/downloads/에서 MySQL 설치 프로그램을 다운로드 한다.

• MySQL Community Server가 오픈소스로 무료이므로 이를 다운로드 한다.
• "Windows MSI Installer (x86)"를 선택하여 다운로드를 시작 한다.

• 다운로드한 설치 프로그램(mysql-5.1.34-win32.msi)을 실행한다.

• Setup Type을 Custom으로 선택한다.

• "Change.." 버튼을 눌러 설치할 디렉토리를 설정 한다.

• MySQL의 설치 디렉토리와 MySQL의 Data가 저장될 디렉토리를 설정 한다.

• 등록된 정보를 확인하고 설치한다.

• MySQL에서 제공하는 기본 정보를 확인한다.

• MySQL에서 제공하는 기본 정보를 계속 확인한다.

• "Configure the MySQL Server now"를 체크하여 설치 후 바로 서버 설정을 진행한다.

• MySQL 서버 환경 설정은 $MYSQL_HOME/bin/MySQLInstanceConfig.exe 파일을 관리자 권한으로 실행하여 설정이 가능 하다.

MySQL 서버 환경 설정

• MySQL 서버 환경 설정은 $MYSQL_HOME/bin/MySQLInstanceConfig.exe 파일을 관리자 권한으로 실행하여 설정이 가능 하다.

• MySQL에서 제공하는 정보를 확인한다.

• "Detailed Configuration"을 선택한다.

• "Server Machine"을 선택한다.

• 트랜잭션 처리를 위해 "Transactional Database Only"를 선택한다.

• 테이블을 저장할 테이블 스페이스의 위치를 선택한다.

• "Decision Support (DSS)/OLAP"을 선택한다.

• 사용할 Port를 지정한다. 디폴트로 3306 port를 사용한다.

• "Add firewall exception for this port" 링크를 선택하여 방화벽에서 3306 포트를 연다.

• "Best Support For Multilingualism"을 선택하여 다중 언어 지원을 하도록 한다.

• 윈도우의 서비스로써 등록한다.

• MySQL의 root 사용자의 암호를 설정한다.

• MySQL의 환경 설정을 실행한다.

• "could not start the service mysql.error 0" 오류가 발생할 경우

• Windows 레지스트리 편집기(regedit)에서 MySQL 관련 정보를 삭제한 후 재 설치 한다.
• 이지클린 프로그램 등을 사용하여 레지스트리를 청소하는 것도 좋은 방법 이다.

• Vista에서 Apply Security Setting에서 보안 오류로 1045번 오류가 발생할 경우

• MySQL의 root 암호가 재대로 설정이 되지 않았을 경우에 이런 오류가 발생할 수 있으므로, $MYSQL_HOME/bin 폴더에서 "mysqladmin -u root -p password 새_암호" 명령을 실행하여 암호를 설정 한다.
• 비스타에 MySQL 설치하기에서 MySQLInstanceConfig.exe 파일을 받아 $MYSQL_HOME/bin 에 복사 한다.
• MySQLInstanceConfig.exe 파일을 관리자 권한으로 실행하여 MySQL의 서버 환경 설정을 완료 한다.

• MySQL의 환경 설정을 완료한다.

MySQL의 추가 환경 설정

• MySQL 설치 후 사용하기 위해서 필요한 환경을 설정한다.
  • PATH=d:/OpenBiz/31_application/MySQL5051/bin (기존 PATH에 추가)

• 다중 언어를 지원하기 위해서 UTF-8 charset을 설정한다.

• MySQL이 설치된 홈디렉토리에 있는 my-medium.ini 파일을 복사하여 my.ini 파일을 생성한다.
• my.ini 파일에 아래 사항을 추가 또는 수정한다.

[client]    
default-character-set=utf8  

[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  

[mysqldump]    
default-character-set=utf8

MySQL Tools for 5.0 설치

• MySql Tools for 5.0을 설치한다.

Linux용 설치 가이드

Ubuntu Server 8.10에서 MySQL을 설치 한다.

• MySQL 5.1.30 설치

• Ubuntu에 root로 로그인한다.

apt-get install mysql-server
#--- 중간에 mysql DB의 root 사용자 암호를 물어 보면 입력해 준다.

• UTF-8 환경 설정

vi /etc/mysql/my.cnf
    [client]    
    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  
    #--- localhost (127.0.0.1)외에서도 접속하려면 아래와 같이 주석 처리 한다.
    ### bind-address = 127.0.0.1

    [mysqldump]    
    default-character-set=utf8

    [mysql]   
    default-character-set=utf8

• 설치 정보

• 설치 폴더 : /usr/share/mysql/
• 환경 폴더 : /etc/mysql/

vi /etc/mysql/my.cnf

• 데이터 폴더 : /var/lib/mysql
• 작업 폴더 : /var/lib/mysql
• 기동 종료 : /etc/init.d/mysql start, stop, restart, reload, status
• 서비스 확인 : ps -ef | grep mysql
• 로그 정보 : /var/log/mysql/mysql-slow.log

CentOS용 설치 가이드

MySQL 설치

• MySQL 현황 확인

yum list | grep mysql               #--- 설치 가능한 package 확인
yum list installed | grep mysql     #--- 설치된 package 확인

• CentOS에서 MySQL 설치

#--- CentOS에 root로 로그인 한다.
yum -y install mysql mysql-server mysql-connector-odbc mysql-devel
rpm -qa | grep mysql    #--- mysql 설치 확인

• mysql의 root 암호 설정

/etc/init.d/mysqld start
mysqladmin -u root password '비밀번호'

• root 사용자로 mysql에 접속 하기

mysql -uroot -p
    quit

• 문자셋 설정

MySQL/CentOS에서 UTF-8 설정

• 부팅시 자동 실행 설정

chkconfig --level 2345 mysqld on
chkconfig --list | grep mysqld

• 설치 정보

• 설치 폴더 : /usr/share/mysql/
• 데이터 폴더 : /var/lib/mysql/
• 실행 파일 : /usr/bin/
• 환경 파일 : /etc/my.cnf
• 기동 종료 : /etc/init.d/mysqld start, stop, restart 또는 service mysqld start, stop, restart
• 서비스 확인 : ps -ef | grep mysqld | grep -v grep
• 로그 정보 : /var/log/mysqld.log
• PID 파일 : /var/run/mysqld/mysqld.pid

• showinfo_mysql.bash

#!/bin/bash
echo 'MySQL 버전     : MySQL 5.0.77'
echo 'MySQL 문자셋   : UTF-8'
echo '설치 폴더      : /usr/share/mysql/'
echo '데이터 폴더    : /var/lib/mysql/'
echo '환경 파일      : vi /etc/my.cnf'
echo '기동 종료      : service mysqld restart'
echo '서비스 확인    : ps -ef | grep mysqld | grep -v grep'
echo '로그 파일      : tail -f /var/log/mysqld.log'

• 참고 문헌

• CentOS 5.5 mysql 설치, 2010.8

phpMyAdmin 설치

• MySQL을 관리하기 위해 phpMyAdmin을 설치한다.

MySQL Port 변경

• Port 사용 여부 확인

netstat -an | grep 7306 | grep LISTEN

• vi /etc/my.cnf

[client]
port=7306

[mysqld]
port=7306

• service mysqld restart

• SELinux 보안 설정이 되어 있을 경우

#--- SELinux 보안 설정을 변경할 수 있는 명령어
system-config-securitylevel

#--- 시스템에서 예약되어 사용되는 포트인지 확인
vi /etc/services      

• SELinux 보안 기능이 설정되어 해당 포트로 start되지 않을 경우

#--- 사용 포트 확인
semanage port -l | grep mysql

#--- 아래 명령을 사용하여 추가 포트 지정
semanage port -a -t mysqld_port_t -p tcp 7306
semanage port -a -t mysqld_port_t -p udp 7306

사용자 가이드

MySQL 시작 및 종료

• MySQL을 시작/종료하는 방법

"시작" 메뉴에서 "설정" 메뉴를 선택한 후 "제어판"을 선택한다.

"관리 도구"를 더블 클릭한 후 "서비스"를 더블 클릭한다.

MySQL을 선택한 후 오른쪽 마우스를 클릭하여 MySQL을시작하거나 중지(종료)할 수 있다.

• MySQL 시작 스크립트를 만들어 사용하면 편리 합니다. (start_mysql.cmd)

cd 31_application/MySQL5051/bin
mysqld --console --max_allowed_packet=32M

• MySQL 종료 스크립트를 만들어 사용하면 편리 합니다. (stop_mysql.cmd)

cd 31_application/MySQL5051/bin
mysqladmin -u root -p shutdown

Foreign 키

• 참조

• FOREIGN 키 제한

관리자 가이드

DB Schema와 사용자 추가 설정

• MySQL에 TestAppl에서 사용할 Schmea를 등록한다.

• MySQL Administrator에 root 관리자로 로그인 한다.
• 좌측 상단 창에서 "Catalogs"를 선택한다.
• 좌측 하단의 "Schemata" 창에서 우측 마우스를 누른다.
• "Create New Schema" 메뉴를 선택하여 Schema를 생성한다.
• Schema 생성화면에서 "Schema name"으로 "testappldb"를 입력한다.

• MySQL에 TestAppl에서 사용할 사용자를 등록한다.

• 좌측 상단 창에서 "User Administration"을 선택한다.
• 좌측 하단의 "Users Accounts" 창에서 우측 마우스를 누른다.
• "Add new user" 메뉴를 선택하여 사용자를 생성한다.
• "User Information" 탭에서 "MySQL User"("testappl"), "Password", "Confirm Password" 등을 등록한다.

• MySQL에 TestAppl에서 사용할 사용자의 권한을 설정한다.

• "Schema Privileges" 탭에서 사용자가 사용할 Schema("testappldb")를 선택한다.
• "Available Privileges"의 모든 권한을 "Assigned Privileges"로 이동한다.
• "Apply changes" 버튼을 눌려 등록된 정보를 저장한다.

• MySQL Query Browser에 접속한다.

• 상단의 "Tools" 메뉴에서 "MySQL Query Browser" 메뉴를 선택한다.

• 사용자의 MySQL 데이터베이스 접속 정보를 추가한다.

• 우측의 "Schemata" 탭에서 "mysql"을 더블 클릭하여 선택한다.
• grant all privileges on testappldb.* to testappl@localhost identified by '암호'; (사용자에게 Schmea에 대한 권한을 추가)
• flush privileges; (위 명령을 통해 설정한 권한을 데이터베이스에 반영)
• select host, user, password from user order by user, host; (사용자의 접속 정보 확인)

• MySQL Query Browser가 아니라 DOS 창에서 접속 정보를 추가하는 방법

mysql -uroot -pxxx mysql
    grant all privileges on testappldb.* 
        to testappl@localhost identified by '암호';
    flush privileges;
    select host, user, password from user order by user, host;
    exit

• 참조 : phpMyAdmin에서 DB Schema와 사용자 추가 설정

CentOS에서 DB Schema 추가 설정

#--- useridDB : 데이터베이스명
#--- userid : 데이터베이스의 사용자 아이디
#--- userpassword : userid의 암호
mysql -u root -p
    create database useridDB default character set utf8 collate utf8_general_ci;
    grant all privileges on useridDB.* to userid@localhost identified by 'userpassword';
    quit

백업 및 복구

오픈소스 DBMS중에서 가장 유명한 것은 MySQL과 PostgreSQL 입니다.
DBMS를 관리할 때 가장 신경을 많이 쓰는 부분이 튜닝과 백업/복구 입니다. 여기서는 MySQL을 각 단위별로 백업과 복구하는 방법을 알아 봅시다.

• Binary 파일 백업, 전체 DB 백업

• $MYSQL_HOME/my.ini에서 datadir에 지정된 디렉토리를 통채로 백업한다.

• Binary 파일 백업, DB 별 백업

• $MYSQL_HOME/my.ini에서 datadir에 지정된 디렉토리에 데이터베이스별로 폴더가 만들어져 있다.
• 해당 데이터베이스에 해당하는 폴더를 통째로 백업한다.
• 저장 방식이 MYISAM가 아니라 INNODB인 경우 인덱스는 별도로 백업을 하여야 한다. (아직 방법은 모름)

• mysqldump 명령어를 통한 전체 DB 백업 및 복구

• 백업 : mysqldump --user=root --password=암호 --all-databases > AllDatabaseBackup.sql
• 복구 : mysql --user=root --password=암호 < AllDatabaseBackup.sql

• mysqldump 명령어를 통한 DB별 백업 및 복구

• 백업 : mysqldump --user=아이디 --password=암호 DB명 > DatabaseBackup.sql
• 복구 : mysql --user=아이디 --password=암호 -D DB명 < DatabaseBackup.sql

• mysqldump 명령어를 통한 DB별 Table별 백업 및 복구

• 백업 : mysqldump --user=아이디 --password=암호 DB명 Table명 > DatabaseTableBackup.sql
• 복구 : mysql --user=아이디 --password=암호 -D DB명 < DatabaseTableBackup.sql

모니터링 및 튜닝

• 참고 문헌

• MySQL Database 서버 튜닝

데이터 등록 오류 in PHP

• PHP 버전 4.x와 달리 PHP 버전 5.x에서는 숫자 항목에 빈문자열을 등록할 때 오류가 발생한다. 따라서, 숫자 항목에는 반드시 숫자를 등록하여야 한다.

• MySQL의 $MYSQL_HOME/my.ini 설정 파일의 sql-mode에서 NO_AUTO_CREATE_USER 항목을 제거하면, PHP 프로그램을 수정하지 않더라도 오류가 발생하지 않는다.

외부 접속 권한 설정

• MySQL Administrator 등에서 접속할 때, 권한이 없는 PC (IP)에서 접속할 경우 접속이 되지 않는다.

• 오류 메시지

Could not connect to the specified instance.
MySQL Error Number 1130
Host '192.168.0.11' is not allowed to connect to this MySQL server

If you want to check the network connection, please click the Ping button.

• 다음과 같이 설정하여 해당 IP에서 접속 권한을 부여 한다.

• MySQL 접속 DB : mysql
• MySQL 로그인 아이디 : zzuser
• 접속하는 PC의 IP : 192.168.0.11
• MySQL 로그인 암호 : zzpassword

mysql -uroot -p
   connect mysql
   grant all privileges on mysql.* to zzuser@192.168.0.11 identified by 'zzpassword';
   update user
      set select_priv = 'Y', 
          insert_priv = 'Y', 
          update_priv = 'Y', 
          delete_priv = 'Y', 
          create_priv = 'Y', 
          drop_priv = 'Y', 
          reload_priv = 'Y', 
          shutdown_priv = 'Y', 
          process_priv = 'Y', 
          file_priv = 'Y', 
          grant_priv = 'Y', 
          references_priv = 'Y', 
          index_priv = 'Y', 
          alter_priv = 'Y', 
          show_db_priv = 'Y', 
          super_priv = 'Y', 
          create_tmp_table_priv = 'Y', 
          lock_tables_priv = 'Y', 
          execute_priv = 'Y', 
          repl_slave_priv = 'Y', 
          repl_client_priv = 'Y', 
          create_view_priv = 'Y', 
          show_view_priv = 'Y', 
          create_routine_priv = 'Y', 
          alter_routine_priv = 'Y', 
          create_user_priv = 'Y'
    where host = '192.168.0.11' and user = 'zzuser';
   commit;
   flush privileges;
   select host, user, password from user order by user, host;
   exit;

• 권한 설정 후 2분정도 기다려야 실제로 접속할 수 있다.

• 모든 IP에 대해서 접속 권한을 부여 하는 방법

mysql -uroot -p
   connect mysql
   update user set host='%' where host='127.0.0.1';
   commit;
   grant all privileges on mysql.* to zzuser@'%' identified by 'zzpassword';
   flush privileges;
   select host, user, password from user order by user, host;

1045 오류가 나면서 접속이 되지 않을 경우

• MySQL을 종료 한다.

service mysqld stop

• 안전 모드로 접속하여 암호를 새로 설정 한다.

mysqld_safe --skip-grant-tables
mysql -u root
    UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';
    FLUSH PRIVILEGES;
    exit;
service mysqld start

MySQL 역공학

• Table 목록 가져오기

SELECT table_name, table_rows, avg_row_length, table_collation 
  FROM information_schema.`TABLES` T
where table_schema = '테이블명';

• Table 정보 가져오기

SELECT column_name, column_default, is_nullable, data_type, character_maximum_length,
      numeric_precision, numeric_scale, collation_name
 FROM information_schema.`COLUMNS` C
where table_schema = '스키마명'
  and table_name = '테이블명'
order by ordinal_position;

• Procedure 정보 보기

#--- Procedure 목록
SHOW PROCEDURE STATUS where db = 'DB_명';
#--- Procedure 소스
SHOW CREATE PROCEDURE DB_명.Procedure_명;

참고 문헌

• phpMyAdmin
• drizzle : MySQL에서 분기된 Cloud와 웹을 위한 가벼운 DBMS
• Mysql 백업
• Mysql 백업 및 복구

테이블 목록보기

> show tables;

테이블 이름 변경

> alter table 테이블명 rename 변경할테이블명;

테이블 삭제

> drop table 테이블명;

테이블 구조 확인

> desc 테이블명;

> explain 테이블명;

테이블 컬럼(필드) 추가

> alter table 테이블명 add 추가할필드명 타입 조건 first 또는 after 필드명;

ex) alter table test add name varchar(20) after age; // test 테이블에 name 필드를 age뒤에 생성한다.

테이블 컬럼(필드) 삭제

> alter table 테이블명 drop 삭제할필드명;

데이터 입력

> inset into 테이블명 values('필드값','필드값','필드값');

데이터 수정

> update 테이블명 set 수정할필드명='수정할필드값' where 조건필드명='조건데이터값';

ex) where 조건식에 의한 테이터를 지정하지 않을 경우  해당 필드의 수정값이 모든 데이터에 반영되니 주의.

ex) update test set age='35' where name='최민호'; //test 테이블의 name값이 최민호인 데이터의 age 값을 35로 수정한다.

데이터 삭제

> delete from 테이블명 where 조건검색필드명='조건검색데이터값';

데이터 검색

> select * from 테이블명; // 모든 필드 데이터를 검색

> select 필드명,필드명 from 테이블명; // 지정한 필드의 데이터만 검색

데이터 검색 조건

> select * from 테이블명 where 조건;

ex) select * from test where age='35'; // test 테이블에서 age가 35인 데이터를 검색한다.

ex) select * from test where age<'35'

ex) select * from test where age >'25' and age<'35';

ex) select * from test where age='35' and name='최민호' and sex='남';

ex) select * from test where name like '민%'; // '민'으로 시작하는 데이터를 검색

ex) select * from test where name like '%민'; // '민'으로 끝나는 데이터를 검색

ex) select * from test where name like '%민%'; // '민'을 포함하고 있는 데이터를 검색

데이터 정렬

> select * from 테이블명 order by 정렬기준필드명

ex) select * from test order by no // no 필드값 기준으로 오름순 정렬

ex) select * from test order by no desc // no 필드값 기준으로 내림순 정렬

검색된 데이터의 일부만 가져오기

> select * from 테이블명 기준필드명 asc limit 시작번호,검색할데이터수;

ex) select * from test num asc limit 0,20; // test 테이블에서  num필드 값을 기준으로 첫번째(0)부터 20개의 데이터를 검색한다.

ex) select * from test were age<'35' order by num desc, num asc limit 0,20

-------------------------------------

여러가지 명령정리
mysql> show variables;      서버의 variables(설정사항)출력
mysql> show variables like 'have_inno%'    조건에 맞는 variables만 출력
mysql> show databases;      database목록
mysql> show tables;       현재DB의 테이블목록(temporary table은 출력하지 않음)
mysql> show tables from db명;     지정된 db명이 소유한 테이블목록
mysql> show tables like 'mem%';     조건에 맞는 테이블목록만 출력
mysql> show index from 테이블명;     인덱스 보기
mysql> show columns from 테이블명;     테이블구조(describe 테이블명, explain 테이블명)
mysql> show table status;      현재 DB의 테이블들의 상태(row수,table type,row길이,..)
mysql> show table status from db명;     지정된 DB의 테이블들의 상태(row수,table type,row길이,..)
mysql> show create table 테이블명;     해당 테이블 생성 SQL문 출력
mysql> rename table 테이블1 to 테이블2;    테이블명 변경(ALTER TABLE 테이블1 RENAME TO 테이블2)
mysql> rename table 테이블1 to 테이블2, 테이블3 to 테이블4; rename multiple tables
mysql> rename table db1명.테이블명 to db2명.테이블명;  테이블을 다른 DB로 이동
mysql> alter table 테이블명 add 컬럼명 데이터타입;  컬럼추가
mysql> alter table 테이블명 del 컬럼명;    컬럼제거
mysql> alter table 테이블명 modify 컬럼명 컬럼타입;  컬럼명에 지정된 컬럼타입의 변경
mysql> alter table 테이블명 change old컬럼명 new컬럼명 컬럼타입 컬럼명 변경
mysql> alter table 테이블명 type=innodb;    테이블type변경
mysql> create table 테이블명(..) type=heap min_rows=10000; 10000row를 수용할 수 있을 만큼 메모리할당(heap type이므로)
mysql> select version();      MySQL서버버전 출력
mysql> create table 테이블2 as select * from 테이블1;  테이블1과 동일한 테이블 생성(with 데이터, as는 생략가능)
mysql> create table 테이블2 as select * from 테이블1 where 1=2; 테이블1과 동일한 구조의 테이블 생성(without 데이터, 1=2는 0으로 할수도 있다.)
mysql> insert into 테이블2 select * from 테이블1;   테이블1의 데이터를 테이블2에 insert