MySQL

오픈소스 비즈니스 컨설팅
(MySQL 5.0.51에서 넘어옴)
둘러보기로 가기 검색하러 가기

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


설치 가이드

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

MYSQL 서버 설치

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


  • 다운로드한 설치 프로그램(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"을 선택한다.
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
  • vi /etc/my.cnf 샘플
[client]
default-character-set = utf8

[mysqld]
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 

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

default-storage-engine=innodb
innodb_file_per_table
bind-address = 192.168.70.151 

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqldump]
default-character-set = utf8

[mysql]
default-character-set = utf8

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에 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 mysql
    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'
  • 참고 문헌

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 Data folder 변경

MySQL Replication

  • 참고 문헌

사용자 가이드

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 키

  • 참조

관리자 가이드

데이터베이스와 사용자 생성

create database langpack;
show databases;

grant all privileges on langpack.* to langpack@localhost identified by '비밀번호';
flush privileges;

select Host, User, Password from user;
select Host, Db, User from db;

#--- 모든 곳에서 접속 허용
grant all privileges on langpack.* to langpack@'%' identified by '비밀번호';
flush privileges;


사용자 생성

  • 사용자 생성
  • Database : hivedb
  • 사용자 : hive / demo1234
mysql -uroot -p??? mysql
   create database hivedb;
   insert into user (Host, User, Password)
          values ('localhost', 'hive', password('demo1234'));
   insert into db
          values ('localhost', 'hivedb', 'hive', 
	            'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',
	            'Y','Y','Y','Y','Y','Y','Y','Y','Y');
   commit;
   flush privileges;

   grant all privileges on hivedb.* to hive@localhost identified by 'demo1234';
   flush privileges;
   exit

mysql -uhive -pdemo1234 hivedb
   exit

MySQL JDBC Driver

wget http://cdn.mysql.com/Downloads/Connector-J/mysql-connector-java-5.1.27.tar.gz
tar zxvf mysql-connector-java-5.1.27.tar.gz
cp mysql-connector-java-5.1.27/mysql-connector-java-5.1.27-bin.jar ~

DB Schema와 사용자 추가 설정

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


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


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


  • MySQL Query Browser에 접속한다.
  • 상단의 "Tools" 메뉴에서 "MySQL Query Browser" 메뉴를 선택한다.
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



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
  • 옵션 : --lock-tables=false, --single-transaction
  • 복구 : mysql --user=아이디 --password=암호 -D DB명 < DatabaseBackup.sql
  • mysqldump 명령어를 통한 DB별 Table별 백업 및 복구
  • 백업 : mysqldump --user=아이디 --password=암호 DB명 Table명 > DatabaseTableBackup.sql
  • 복구 : mysql --user=아이디 --password=암호 -D DB명 < DatabaseTableBackup.sql

mysqldump 오류

  • mysqldump: Got error: 145: Table './mediawikidb/jopensearchindex' is marked as crashed and should be repaired when using LOCK TABLES
mysql -uroot -p mysql
   connect mediawikidb;
   repair table jopensearchindex;

모니터링 및 튜닝

MySQL 튜닝

데이터 등록 오류 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 mysql
    select host, user, password from user order by user, host;    #--- 현재 설정된 권한 확인
    grant all privileges on mysql.* to zzuser@192.168.0.11 identified by 'zzpassword';
    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;

root 접속 권한 설정

  • root 접속 권한 설정
service mysqld stop
mysqld_safe --skip-grant-tables
  • 이제 mysql에 접속하여 권한을 변경 합니다.
ps -ef | grep mysql
kill -9 6982 7099
service mysqld start

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_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE 
 from information_schema.tables 
where TABLE_SCHEMA = 'DatabaseName'; 
//--- TABLE_TYPE : BASE TABLE, VIEW

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_명;

MySQL 명령어 실행

mysql -uuserName -puserPassword dbName -e 'show tables'

MySQL 날자 함수

  • Sample 1
update cases a, cases_cstm b 
   set duration_sf_c = datediff(date_add(date_closed_sf_c, INTERVAL 9 hour) , date_add(date_opened_sf_c, INTERVAL 9 hour))
where a.id = b.id_c
    and status = 'Closed'
    and date_closed_sf_c is not null
    and date_opened_sf_c is not null
    and duration_sf_c = 0
;

오류 : mysql.servers' doesn't exist

  • mysql.servers 테이블을 생성 합니다.
CREATE TABLE `servers` (
`Server_name` char(64) NOT NULL,
`Host` char(64) NOT NULL,
`Db` char(64) NOT NULL,
`Username` char(64) NOT NULL,
`Password` char(64) NOT NULL,
`Port` int(4) DEFAULT NULL,
`Socket` char(64) DEFAULT NULL,
`Wrapper` char(64) NOT NULL,
`Owner` char(64) NOT NULL,
PRIMARY KEY (`Server_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
COMMENT='MySQL Foreign Servers table';


MySQL error 2006

wait_timeout=2880000 
interactive_timeout = 2880000 

또는

max_allowed_packet = 64M

참고 문헌

원본 주소 "https://www.jopenbusiness.com/mediawiki/index.php?title=MySQL&oldid=767910"