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


설치 가이드

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


MYSQL 서버 설치

  • http://dev.mysql.com/downloads/에서 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](client.md)    
 default-character-set=utf8  
 
 [mysql](mysql.md)   
 default-character-set=utf8
 
 [mysqld](mysqld.md)   
 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](mysqldump.md)    
 default-character-set=utf8
  • vi /etc/my.cnf 샘플
 [client](client.md)
 default-character-set = utf8
 
 [mysqld](mysqld.md)
 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](mysqld_safe.md)
 log-error=/var/log/mysqld.log
 pid-file=/var/run/mysqld/mysqld.pid
 
 [mysqldump](mysqldump.md)
 default-character-set = utf8
 
 [mysql](mysql.md)
 default-character-set = utf8

MySQL Tools for 5.0 설치

  • [MySql Tools for 5.0](MySql Tools for 5.0.md)을 설치한다.

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](client.md)    
     default-character-set=utf8  
 
     [mysqld](mysqld.md)   
     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](mysqldump.md)    
     default-character-set=utf8
 
     [mysql](mysql.md)   
     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 설정](문자셋과 인코딩.md#MySQL/CentOS에서 UTF-8 설정.md)

  • 부팅시 자동 실행 설정

 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](client.md)
 port=7306
 
 [mysqld](mysqld.md)
 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 Data folder 변경](UCloud.md#MySQL Data folder 변경.md)

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;
  
1. --- 모든 곳에서 접속 허용  
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 DB 설정|TestAppl|testappldb|testappl}}

  • 참조 : [phpMyAdmin에서 DB Schema와 사용자 추가 설정](PhpMyAdmin.md#DB Schema와 사용자 추가 설정.md)

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.

[외부 접속 권한 설정](File:Mysql connect.png.md)

{{MySQL 접속 권한|mysql|zzuser|192.168.0.11|zzpassword|}}

  • 모든 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 날자 함수

 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


참고 문헌

최종 수정일: 2022-10-24 19:17:28
이전글 :
다음글 :
상단 menu
arrow_back_ios
arrow_forward_ios