MySQL 암호화

MySQL을 이용하는 대부분의 사이트에서 사용자의 정보를 암호화 하는데 MD5(SHA1)를 사용합니다. 하지만 MD5(SHA1)는 암호화가 아닌 해시 값 입니다. 해시 알고리즘 MD5(SHA1)에 의한 문자열입니다. 사용자 개인정보를 취급하는데 있어 DB전체 데이터를 분실한다고 했을때 많은 손실을 받을 수 있습니다. 물론 한개의 비밀번호를 알아내는데 약간의 시간이 필요하지만 그렇다고 해서 못알아내는 것은 아닙니다.

완벽한 보안이 아닌 그나마 차선책으로라도 암호화를 해 저장을 한다면 이러한 방식은 어떻까 합니다. MySQL 내부의 암호화 방식인 AES/DES를 사용해보는것입니다. AES(Advenced Encryption Standard) 는 미국 정부 표준으로 지정이된 블록암호화 알고리즘입니다 PHP 에서도 libmcrypt (mcrypt) 로 지원을 합니다. MySQL 에서는 128bit를 기본적으로 지원을 하고 있습니다.

이제 MySQL 에서 AES(Rijndael)를 사용하는 방법을 알아보겠습니다. 암호화는 암호화/복호화를 하기때문에 키관리가 중요합니다. 저의 생각으로는 암호화에서 키를 사용자에게 맏기는 방법은 어떠한가 생각을 해봅니다. 지금도 그렇지만 예전에도 키 관리는 사용자의 암호를 이용해 키를 관리하는 방식이 좋다고 생각합니다.

일단 예제를 보겠습니다.
[MD5 예]

-- 사용자 테이블
CREATE TABLE User
(
seq INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
userId CHAR(20) NOT NULL,
passWd CHAR(32) NOT NULL,
PRIMARY KEY(`seq`),
UNIQUE INDEX (`userId`)
)
ENGINE=MyISAM;
-- 사용자 정보 입력

mysql> INSERT INTO User (userId, passWd) VALUES ('lovelgw', MD5('123456'));
Query OK, 1 row affected (0.00 sec)
-- 사용자 로그인

mysql> SELECT * FROM User WHERE userId = 'lovelgw' AND passWd = MD5('123456');
+-----+---------+----------------------------------+
| seq | userId | passWd |
+-----+---------+----------------------------------+
| 1 | lovelgw | e10adc3949ba59abbe56e057f20f883e |
+-----+---------+----------------------------------+
1 row in set (0.00 sec)

이렇게 관리를 하겠죠, 보안상 취약점을 모르시겠다고요? 음. 그렇지 않습니다. DB파일 전체가 노출되거나 또는 내부 직원의 실수등으로 DB전체가 노출이 된상태라면 사용자의 개인정보는 공개 정보가 되는것입니다. 암호화 방식 을 AES로 변경해보겠습니다. 키를 내부적으로 관리하지 않는다는 가정하에 아래와 같이 작성 할 수 있습니다.

[AES 예]

-- 사용자 테이블
CREATE TABLE User
(
seq INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
userId CHAR(20) NOT NULL,
-- HEX함수를 사용하지 않으려면 필드 타입을 BINARY 로 해주시면 됩니다.
passWd CHAR(40) NOT NULL,
PRIMARY KEY(`seq`),
UNIQUE INDEX (`userId`)
)
ENGINE=MyISAM;
-- 사용자 정보 입력

mysql> INSERT INTO User (userId, passWd) VALUES ('lovelgw', HEX(AES_ENCRYPT('123456', MD5('123456'))));
Query OK, 1 row affected (0.00 sec)
mysql> select * from User;
+-----+---------+----------------------------------+
| seq | userId | passWd |
+-----+---------+----------------------------------+
| 1 | lovelgw | 59B6FF82CD62F195F55DD3A4BB2E1D70 |
+-----+---------+----------------------------------+
1 row in set (0.00 sec)
-- 사용자 로그인 (사용자 정보조회)

mysql> SELECT * FROM User WHERE userId = 'lovelgw' AND AES_DECRYPT(UNHEX(passWd), MD5('123456'));
+-----+---------+----------------------------------+
| seq | userId | passWd |
+-----+---------+----------------------------------+
| 1 | lovelgw | 59B6FF82CD62F195F55DD3A4BB2E1D70 |
+-----+---------+----------------------------------+
1 row in set (0.00 sec)

위와 같이 AES예를 보았습니다. 사용자의 암호가 암호화 방식의 키가 되겠습니다. 평문길이가 약 20Byte정도 일때 암호화 문자열의 길이는 약 32Byte정도 입니다. (암호화 키는 사용자 암호의 MD5이용, AES_ENCRYPT 결과는 HEX함수를 이용했을 경우) 필드 데이터 타입을 바이너리로 정의를 하면 HEX를 사용하지 않아도 됩니다. 하지만 기존 테이블 규칙을 사용한다는 가정하에 위와 같이 적용해보았습니다.

사용자 정보의 암호화를 이용해서 보안에서의 최소안의 피해 및 누출을 막음으로서 더욱 안전하게 관리를 할 수 있습니다.구지 사용자의 암호를 관리적 측면에서 알 필요가 없으니 사용자가 입력한 암호로 사용자의 데이터를 암호화 하는데 사용합니다. 관리적측면에서의 보안적인 편리함이 더욱더 늘어 난것이죠

이제 MD5가 얼마나 위험하고 손쉽게 추적이 가능한지 알아보도록 하겠습니다.

원문 : lovelgw
MD5해시 : 84cefd52366042c03c1bf9e47b1409a1
nVidia GeForce 8500 GT 으로 2분 34초 만에 값을 알아내었습니다.
MD4/MD5/SHA1 GPU Password Recovery v0.62 (http://www.golubev.com/)

X:\>ighashgpu.exe -h:84cefd52366042c03c1bf9e47b1409a1 -t:md5 -hm:0 -max:7
****************************************************************
*** MD4/MD5/SHA1 GPU Password Recovery v0.62 ***
*** For ATI RV 7X0 cards and nVidia 'CUDA' ones (G80+) ***
*** (c) 2009 Ivan Golubev, http://golubev.com ***
*** see "readme.htm" for more details ***
****************************************************************
*** Any commercial use of this program is strictly forbidden ***
****************************************************************
Found 1 CUDA device(s)
Starting brute-force attack, Charset Len = 26, Min passlen = 4, Max passlen = 7
Charset (unicode -> 0) [abcdefghijklmnopqrstuvwxyz]
Charset in HEX: 61 62 63 64 65 66 67 68 69 6a 6b 6c 6d 6e 6f 70 71 72 73 74 75 76 77 78 79 7a
Starting from [aaaa]
Hash type: MD5, Hash: 84cefd52366042c03c1bf9e47b1409a1
Device #0: [GeForce 8500 GT] 918.00 Mhz 16 SP
Hardware monitoring disabled.
CURPWD: ghlzlhm DONE: 43.52% ETA: 2m 34s AVRSPD: 29.4M
Found password: [lovelgw], HEX: 6c 6f 76 65 6c 67 77
Processed 3 816 816 640 passwords in 2m 11s.
Thus, 29 339 139 password(s) per second in average.

위 처럼 손쉽게 MD5 해시 값을 쉽게 찾아 낼 수 있습니다. GPU를 이용하는 프로그램이라 더욱 좋은 그래픽 카드가 있다면 더욱 빨리 값을 찾아내겠죠. 만약 DB전체가 노출된 경우라면 비밀번호는 쉽게 알아낼 수 있습니다.

하지만 위 방식(AES)으로 암호화를 한다면 개인의 데이터의 암호화 한 키가 전부 다르면 데이터가 노출이 된 후 복호화를 시도한다고 하면 매우 오랜 시간이 걸립니다. 약간(?)의 보안성이 더해진것이죠~ DB전체가 노출되더라도 중요한 데이터는 막혀 있는 샘이죠~ 위 방식으로 다양한 활용 및 보안에 더욱더 완벽해 질 수 있는 방법을 찾을 수 있습니다. ^^

6200558_my.txt

지금부터는 db 서버를 설치하겠습니다. mysql은 많은 프로그램과 연동이 가능하며, linux에서 유용하게 사용할 수 있는 dbms입니다. 아직 안정화된 버전에는 dbms의 중요 기능인 트랜젝션 처리 및 사용자정의 함수 등 부족한 점이 있지만 ( 이 부분은 db의 기능보다는 성능과 가볍게 만든다고 뺏다고 하더군요^^) 대부분 잘~~ 사용하지 않는 기능들입니다. mysql은 안정성 및 성능면에서 우수하죠^^;

(1) 설치
설치는 성능을 약 10% 정도 끌어올려준다는 static 모드로 설치하겠습니다.

[root@localhost local]# wget http://ftp.superuser.co.kr/pub/mysql/mysql-4.0.20.tar.gz
[root@localhost local]# tar xvfz mysql-4.0.20.tar.gz
[root@localhost local]# cd mysql-4.0.20
[root@localhost mysql-2.0.20]# CFLAGS="-static -O2 -march=i686 -funroll-loops" \
CXXFLAGS="-static -O2 -march=i686 -funroll-loops -felide-constructors -fno-exceptions -fno-rtti" \
./configure \
--prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/data \
--disable-shared --enable-assembler \
--with-thread-safe-client --with-mysqld-user="mysql" \
--with-client-ldflags=-all-static \
--with-mysqld-ldflags=-all-static \
--with-readline --without-debug \
--without-docs --without-bench \
--with-charset=euc_kr

MySQL 4.1.X 에서는 --with-charset=euckr 으로 해야 합니다.
위 설정에서 static 모드로 설치하며, 언어는 많이 사용하는 euc_kr로 설치합니다.
데이터 디렉토리는 /usr/local/mysql/data 에 저장하게 설정합니다.

[root@localhost mysql-2.0.20]# make && make install
[root@localhost mysql-2.0.20]# cd ..
[root@localhost local]# rm -rf mysql-4.0.20.tar

이렇게 한방에 컴파일 및 설치까지 합니다.

(2) 설정 파일 복사
메모리에 따라서 환경 설정 파일들을 복사해 줍니다.
my-huge.cnf 1~2G
my-large.cnf 512M
my-medium.cnf 128M~ 256M
my-small.cnf 64M 이하
위와같이 나와있지만 위 설정은 db 서버 전용으로 사용했을 때 설정입니다. 기본적을 my-medium.cnf를 복사한 다음 시스템에 맞게 설정해서 사용해야 합니다 .(모든게 그렇듯 절대적인 것이 아닙니다.)
[root@localhost local]# cp /usr/local/mysql/share/mysql/my-medium.cnf /etc/my.cnf

(3) 기본 db 생성
[root@localhost local]# /usr/local/mysql/bin/mysql_install_db

(4) mysql 운영 사용자 생성
홈 디렉토리는 필요 없기 때문에 -M 옵션을 주어서 사용자를 생성합니다.
[root@localhost local]# useradd -M mysql

(5) data 디렉토리를 mysql이라는 사용자 권한으로 바꾸어 주어야 합니다.
[root@localhost local]# chown -R mysql:mysql /usr/local/mysql/data
mak
(6) 아무곳에서나 mysql 및 mysqldump 명령어를 실행가능하게 심볼릭 링크를 걸어줍니다.
[root@localhost local]# ln -s /usr/local/mysql/bin/mysql /usr/bin/
[root@localhost local]# ln -s /usr/local/mysql/bin/mysqldump /usr/bin/

(7) mysql 데몬을 실행시킵니다.
[root@localhost local]# /usr/local/mysql/bin/mysqld_safe &

(8) mysql root 비밀번호를 설정합니다.
[root@localhost local]# /usr/local/mysql/bin/mysqladmin -u root password "암호"

(9) 운영중에 조취법&^^
- mysql root 비밀번호 잊어 먹었을 때
[root@localhost local]# killall mysqld
[root@localhost local]# /usr/local/mysql/bin/mysqld_safe --skip-grant-tables &
[root@localhost local]# mysql
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 2 to server version: 4.0.20-log

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql> use mysql
Database changed
mysql> update user set password=password('비밀번호') where user='root';
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> exit
Bye
[root@localhost local]# killall mysqld
[root@localhost local]# /usr/local/mysql/bin/mysqld_safe &

(9) MySQL 4.0.X 에서 MySQL 4.1.X 달라진점
4.0.X 에선 4.1.X 는 많은 변화들이 있습니다. 모두 기술하기는 힘들고, 중효한 몇가지만 기술하도록 하겠습니다.
1. mysql 4.1의 table 이름은 utf8으로 저장(한글 테이블명을 사용할수 없습니다.)
2. mysql db 에 user 테이블의 password 필드가 더 길어졌습니다.(16->41)
3. password 다루는 방식이 달라졌습니다. 보안을 강하게 하기 위해서 hashing 메커니즘이 바뀌었습니다.
- hashing 이 16바이트에서 41바이트바뀌었습니다. 보이는건 45바이트. 4바이트는? 소금^^;
- 새로운 포멧은 *부터 시작. 예전포멧은 그렇지 않았습니다.
- 예전 포멧을 사용하려면, old_password()함수를 사용해야 합니다.
4. configure 할때 --with-charset=euc_kr 에서 --with-charset=euckr 으로 바뀌었습니다.
*. 더 자세한 것은. ... http://dev.mysql.com/doc/mysql/en/Upgrading-from-4.0.html 참조하세요

mysqldump {-h 호스트} -u 사용자 -p DB명 > 백업파일
mysql {-h 호스트} -u 사용자 -p DB명 < 백업파일

mysqldump -u root -p --opt lottenew ROAD_NAME2 --default-character-set=euckr > 20130603_ROAD_NAME2.sql

mysqldump -u root -p --opt lottenew ROAD_NAME1 --default-character-set=euckr > 20130603_ROAD_NAME1.sql

mysql -u dbakorea -p db_dbakorea < dbakorea.sql

LOAD DATA LOCAL INFILE '/home/sysoper/zipcode.sql' INTO TABLE ZIPCODE FIELDS TERMINATED BY ',';

Warning 메시지 발생시

show warnings;

show errors;

LOAD DATA LOCAL INFILE '/home/sysoper/redkite_work/20130603/ROAD_NAME1_INSERT.txt' INTO TABLE ROAD_NAME1_NEW  FIELDS TERMINATED BY '|';

HeartBeat를 이용한 MySQL HA 구성

인터넷이 발전하고, 인터넷을 사용한 서비스가 발전함에 따라 24시간 서비스를 운영하기 위한 각 서비스 제공 회사들의 노력은 증가하고 있다. 이에 발맞춰 애플리케이션뿐 아니라 DB 레벨에서도 더 좋은 성능으로 24시간 운영하기 위한 여러 가지 솔루션이 발표되고 있다. MySQL AB도 이런 요구에 부응하기 위해 HA를 위한 솔루션을 개발 중이며, HA 구성법도 여러 가지로 다양해지고 있다. MySQL Cluster를 이용해 구성할 수도 있고, MySQL Proxy, DRBD 등을 이용해 구성할 수도 있다. 이 글에서는 MySQL HA 구성 중에 MySQL Replication과 HeartBeat를 이용해 구성하는 방법을 설명한다.

윤은경 joanney1@daum.net, mysqldba.tistory.com, blog.daum.net/joanney｜다음커뮤니케이션 DB기술팀 MySQL Cell 소속. 대학 졸업 후 5년 동안 자바 개발자로 살다가, 갑자기 맘이 바뀌어 그 후 5년간 DBA로 살고 있는, 10년 차 직장인이 됐다는 게 여전히 믿겨지지 않는 IT 종사자이다.

MySQL HA 구성
HeartBeat를 이용한 HA 구성에는 다음과 같은 모듈이 필요하다.

● HeartBeat - 두 서버 사이에 Fail-Over를 가능하게 하는 모듈
● Mon - MySQL Instance의 서비스 가능 여부를 체크하는 체크 모듈
● MySQL Dual Replication - 원활한 Fail-Over 및 Fail-Back을 위한 Replication 구성

HeartBeat
HeartBeat는 Linux-HA 프로젝트 그룹에서 만든 모듈로서 리눅스 운영체제에서 고가용성을 제공한다. HeartBeat는 위에서 간단히 설명한 대로 두 서버 사이에 Fail-Over를 가능하게 하는 모듈로서 서버 사이의 Fail-Over 기능을 제공하고자 할 때 사용한다.

HeartBeat의 구성
HeartBeat를 설치하면 두 서버 사이에 즉, Primary로 설정된 서버와 Standby로 설정된 서버 사이에 주기적으로 메시지를 교환해 서버의 상태를 확인한다. 이때 주고받는 메시지를 'heartbeat' 즉 한글로 '심장박동'이라고 한다.

HeartBeat는 Active로 사용 중인 Primary 서버와 Passive 상태인 Standby 서버 사이에 IP 관리 기능의 일환으로, VIP를 사용해 Active 상태인 Primary 서버에 장애가 일어나더라도 Passive 상태인 Standby 서버를 이용해 서비스가 진행될 수 있도록 한다.

애플리케이션에서는 VIP를 사용해 서버에 접근한다. 이때 VIP로 접근하는 애플리케이션은 HeartBeat 구성에서 Active로 설정된 Primary 서버에 자동으로 접근하게 된다. 만약 서버가 reboot되거나 문제가 발생해 사용하지 못하는 상태가 되는 경우 HeartBeat이 감지해 현재 문제가 발생한 Primary 서버가 아닌 Standby 서버를 Active로 전환해 서비스가 계속 이어지게 한다.

HeartBeat의 특성 및 제약 사항

HeartBeat는 다음과 같은 특성 및 제약 사항을 가진다.

- 리눅스 서버상의 Fail-Over 기능을 제공하고 리눅스 운영체제에서만 사용할 수 있다. HeartBeat 모듈은 리눅스-HA 프로젝트 그룹에서 만든 모듈이기 때문에 리눅스 운영체제에서만 동작하는 모듈이다. 그렇기 때문에 다른 운영체제를 사용하는 시스템에서는 사용이 불가능하다.
- 간단한 설치로 사용이 용이하다.
- 시스템 레벨(System Level)이 아닌 애플리케이션 레벨(Application Level)에서의 Fail-Over는 할 수 없다. HeartBeat는 VIP를 이용해 두 개의 서버 사이의 특정 서버만 접근 가능하게 하는 모듈이기 때문에 시스템 레벨만 가능할 뿐 HeartBeat 자체적으로는 애플리케이션 레벨의 Fail-Over가 불가능하다. 즉, 애플리케이션 레벨의 Fail-Over를 위해서는 추가적인 모듈이 필요하다.
- HeartBeat 구성을 위한 추가적인 장비가 필요하지 않다. HeartBeat은 Active와 Passive를 사용하는 두 서버 사이의 설정으로 사용하는 모듈이기 때문에 추가적인 장비가 필요하지 않다.

Mon
Mon은 이벤트를 이용해 서비스에 대한 사용을 모니터링하는 모듈이다. Mon은 시스템을 ping 프로그램을 이용해 모니터링하는 것과 같은 간단한 경우에서부터 DB 서버에 대한 모니터링까지 가능한 모니터링 모듈로서 HeartBeat과 같이 쓰여서 리눅스 상에서 Fail-Over를 구현할 수 있는 모듈이다.

Mon의 구성 및 동작 프로세스

MySQL 프로세스의 감지를 위해 Mon을 사용하는 경우 <그림 3>과 같은 프로세스로 모니터링이 진행된다.

먼저 Mon 실행 스크립트를 작성해 MON 프로세스를 띄운다. MON은 설정 파일(mon.cf)에 저장된 값을 이용해 주기적으로 mysql.monitor를 실행한다(mysql.monitor는 MySQL과 MSQL을 모니터링하기 위해 제공되는 스크립트인 msql-mysql.monitor의 이름을 수정한 것이다). mysql.monitor는 주기적으로 호출될 때마다 MySQL 프로세스를 체크하고, mysql.monitor가 체크시 MySQL 프로세스의 문제점을 확인하게 되면 bring-ha-down.alert를 호출하게 된다. bring-ha-down.alert는 MySQL 프로세스 문제 시 동작하는 스크립트 파일로서 이 파일에서 Fail-Over를 위해 HeartBeat 모듈을 호출하게 된다. 즉, bring-ha-down.alert은 HeartBeat를 호출하는 스크립트로서 이 스크립트를 통해 HeartBeat와 연결되어 동작한다. 이 스크립트는 Mon 모듈에 속한 게 아니라 사용자가 직접 생성해야 한다.

Mon의 특성 및 제약 사항
Mon은 다음과 같은 특성 및 제약 사항을 가진다.

- 여러 가지 애플리케이션 및 하드웨어의 장애를 감지하는 모니터링 툴이다. Mon은 다방면에서 사용할 수 있는 모니터링 모듈로서 MySQL 프로세스뿐 아니라 MSQL 프로세스 모니터링, 그리고 각종 하드웨어 모니터링까지 가능한 모듈이다. 여기에서 우리는 MySQL에 대한 모니터링 기능 부분만 사용한다.
- 간단한 스크립트 작성으로 사용하기가 간편하다. 위에서 설명한 것과 같이 간단한 스크립트 작성으로 모니터링 시 문제 해결을 위한 여러 기능을 구현할 수 있고, 원하는 대로 사용할 수 있다.
- 실질적인 Fail-Over를 발생시키는 모듈이 필요하다. 앞서 설명한 대로 Mon은 모니터링 모듈이기 때문에 Fail-Over를 위해서는 Mon뿐 아니라 추가적인 모듈이 필요하고, 그래서 여기서는 Fail-Over를 위한 모듈, 즉 HeartBeat과 같이 사용한다.

MySQL Replication
MySQL Replication은 MySQL Server 4.0에서부터 사용 가능한 HA 솔루션으로 가장 쉽게 구성할 수 있고, 가장 많이 사용하고 있는 솔루션이다. MySQL Replication은 다른 특별한 설치 과정이 필요 없이 MySQL 서버의 설치만으로 사용할 수 있고 구성하기 쉽기 때문에 누구나 쉽게 설치해 사용할 수 있다. 또한 제약사항이 적고 구성하는 프로세스가 간단하기 때문에 여러 구성으로 사용하는 것이 가능하다.

Replication의 구성

MySQL은 <표 1>과 같은 Thread를 통해 Replication을 운영한다.

MySQL은 <표 1>의 세 가지 프로세스를 비동기적으로 실행해 Replication이 진행되도록 한다. Replication은 이 세 개의 Thread만으로 동작하며 다른 임의의 프로세스가 존재하지 않는다. MySQL Replication의 동작은 <그림 4>와 같이 설명할 수 있다.

Slave에서 start slave 명령어가 실행되면 Master와 Slave에는 각각 Binlog Dump Thread와 I/O Thread, SQL Thread가 생성된다. 그리고 Binary log가 생성될 때마다 Binlog Dump Thread는 Binary Log를 Slave로 보낸다. 이때 Slave의 I/O Thread는 받은 Binary Log를 Relay Log로 기록하게 되고, SQL Thread는 이렇게 새롭게 기록된 내용을 읽어서 Slave에서 실행하게 된다. HeartBeat를 이용한 MySQL Replication은 일반적인 Replication이 아닌 Dual-Master 구성을 사용한다.

Dual-Master Replication
두 대의 서버가 동시에 Master/Slave의 역할을 수행하는 구성을 의미한다.

이 구성은 두 대의 서버가 인스턴스로 Replicate되는 구성으로 동시에 두 대의 서버가 Master/Slave로서의 역할을 수행하는 구성이다.

● 특징

- 두 서버가 서로 Replication으로 연결된 구조로서 어느 서버에서든지 DML을 발생시켜도 다른 서버에 Replicate되어 전달된다. 두 서버가 동시에 Master이자 Slave이기 때문에 각 서버에서 발생된 DML은 다른 서버로 이동하게 되고, 결국 같은 DML이 두 서버에서 다 발생하게 된다.
- 두 대의 서버가 같은 스키마와 데이터를 가진 구조이다. 앞에서 설명한 대로 두 서버의 DML이 Replicate되어 양 서버에서 다 발생하기 때문에 같은 스키마와 데이터를 가진 구조가 된다.

● 장점

- HeartBeat/Mon을 이용한 HA 구성으로 사용할 경우 Fail-Over, Fail-Back에 유연한 대처가 가능하다. 이론적으로 두 서버가 동시에 Master와 Slave로 연결된 구조이기 때문에 단 방향으로 연결된 Replication 구조에 비해 Fail-Over, Fail-Back에 유연하게 대처할 수 있다. 물론, 안전한 Fail-Over, Fail-Back을 위해서는 Fail-Over, Fail-Back에 대한 프로세스는 간단하지 않다. 하지만, 단 방향 구조에 비해 그 프로세스는 간단하게 된다. 또한, HeartBeat에 의해 두 서버 중 하나의 서버만 실질적으로 서비스에 사용되기 때문에 뒤에서 설명할 Dual-Master 구조의 치명적인 단점에서 자유롭다.
- 어느 서버에 DML이 발생해도 다른 서버로 Replicate가 가능하다. 앞서 설명한 대로 양 방향으로 연결된 구조이기 때문에 두 서버 중 어느 서버에 DML이 발생해도 다른 서버에 Replicate되어 DML이 전달된다.

● 단점

- 두 서버에서 동시에 같은 데이터에 DML이 발생하는 경우 데이터의 불일치 발생 가능성이 높다. Dual-Master 구조의 특징으로 두 서버가 같은 스키마와 데이터를 가진다고 설명했다. 하지만 Dual-Master 구조는 그 구성에 치명적인 문제를 가지고 있다. 바로 DML 발생에 따라 데이터 불일치가 발생할 수 있다는 것이다.
- DML 분산이 되는 것처럼 착각할 수 있으나, DML 분산은 실질적으로 발생하지 않는다. Replication 동작 프로세스를 통해 알 수 있는 것처럼 Master에서 실행된 Query를 Slave에서도 똑같이 동작하게 해 데이터를 일치화시키는 솔루션이므로, 실질적으로 두 서버의 DML이 분산되는 것은 아니다.

Configuration
이제 앞에서 설명한 모듈들을 사용한 전체 구성에 대해 알아보도록 하자. 여기서는 Replication을 이용해 HA 구성을 하게 되는데, Replication 중 앞에서 설명한 Dual-Master Replication 구성을 사용한다.

HeartBeat + Mon
HeartBeat와 Mon은 모두 무료로 사용할 수 있는 모듈이다. HeartBeat은 서버의 Fail-Over 기능을 제공하고 Mon은 MySQL 프로세스를 모니터링하는 기능을 제공하기 때문에 두 개의 모듈을 함께 사용해야만 완전한 Fail-Over 기능을 구현할 수 있다.

● 특징

- 다른 특별한 기기 없이 모듈 설치만으로 Fail-Over가 가능하다.
- 안전한 구성을 위해 추가적인 스크립트 생성이 필요하다.
- 리눅스 운영체제에서만 사용이 가능하다.

Dual Master Replication + HeartBeat + Mon
MySQL은 Dual Master 구조로 데이터를 실시간으로 복제한다. Mon은 실시간으로 MySQL의 상태를 체크해 모니터링하고 만약 MySQL 서버에 문제가 발생하면 그것을 감지하고 HeartBeat를 호출해 Fail-Over가 이뤄지게 한다. Fail-Over가 일어난다 할지라도 애플리케이션에서는 VIP를 통해 접근하기 때문에 문제없이 서비스를 지속할 수 있다.

이 구조에서 두 서버의 MySQL은 Dual Master 구조로 되어 있지만, HeartBeat 설정을 통해 애플리케이션은 VIP를 이용해 두 서버 중 하나의 서버에만 접근할 수 있으므로 구조적으로 가지는 Dual-Master의 문제점을 피할 수 있다. 여기서 설명한 HA 구성에서 문제 발생시 동작하는 프로세스를 정리하면 <그림 9>와 같다.

여기서 살펴본 HA 구성의 특징을 정리하면 다음과 같다.

● 특징

- Fail-Over를 하기 위한 별도의 장비가 필요하지 않다.
- Replication이 가능한 어떤 스토리지 엔진을 사용하든 구성할 수 있다.
- MySQL Community 버전 설치만으로 사용할 수 있다.
- 구성할 수 있는 두 대 중 한 대는 VIP를 사용해 서비스용으로 사용할 수 없다.
- Passive 상태의 Standby 서버를 물리적인 IP로 접근해 사용할 수는 있지만, 앞서 설명했던 Dual-Master의 데이터 불일치 가능성으로 인해 권장하지는 않는다.
- Fail-Over된 원인에 따라 Active로 사용하던 서버의 변경내역이 Replicate 되지 않을 수 있기 때문에, Replication에 대한 분석 프로세스가 추가되어야 한다.
- 리눅스 운영체제에서만 구성할 수 있다.
- HeartBeat 구성의 제약으로 인해 Fail-Over가 발생하면 꼭 Fail-Back 진행을 해줘야 한다.
- DB 레벨의 Fail-Over는 Heart-Beat/Mon 조합으로 가능하게 되었지만, 세션 레벨의 Fail-Over는 구현되지 않았기 때문에, Fail-Over 발생 시 애플리케이션의 DB Connection을 refresh하는 작업을 해야 한다.

맺음말
여기서 설명한 내용은 MySQL에서 구현할 수 있는 HA 구성 방법 중 하나로서 현재 필자는 이와 같은 구성으로 구현해 서비스에 투입한 후 사용하고 있다. 여기에서는 자세한 설치법보다는 각각 모듈의 개념 및 프로세스 설명에 주안점을 두어 설명했다. 이와 같은 내용을 먼저 알아두고 실제 설치 작업을 진행한다면 훨씬 더 쉽게 구현할 수 있다.

6187952_my.txt

${MYSQL_HOME}/bin/mysqld --verbose --help | grep -A 1 'Default options'

/usr/local/mysql/bin/mysql --verbose --help |grep -A 1 'Default options'

innodb_additional_mem_pool_size = 20M

innodb_buffer_pool_size = 1G

innodb_data_file_path = ibdata1:1024M;ibdata2:1024M;ibdata3:1024M:autoextend

#innodb_data_home_dir = ""

innodb_flush_log_at_trx_commit = 1

innodb_log_buffer_size = 8M

innodb_log_file_size = 2000M

innodb_log_files_in_group = 2

#innodb_flush_method=O_DSYNC

innodb_file_format = Barracuda

innodb_file_format_max = Barracuda

일반적으로 위에 파라미터를 신경쓰시면 도움이 되실겁니다.

ib데이터는 미리 만들어 두고

innodb_log_file 은 트랜잭션 로그 파일이거든요

최대 4G 까지 만들수 있어요

innodb_log_file_size * innodb_log_files_in_group

MySQL Innodb Settings 

The most important ones are:

innodb_buffer_pool_size 70-80% of memory is a safe bet. I set it to 12G on 16GB box.

UPDATE: If you’re looking for more details, check out detailed guide on tuning innodb buffer pool

innodb_log_file_size – This depends on your recovery speed needs but 256M seems to be a good balance between reasonable recovery time and good performance

innodb_log_buffer_size=4M 4M is good for most cases unless you’re piping large blobs to Innodb in this case increase it a bit.

innodb_flush_log_at_trx_commit=2 If you’re not concern about ACID and can loose transactions for last second or two in case of full OS crash than set this value. It can dramatic effect especially on a lot of short write transactions.

innodb_thread_concurrency=8 Even with current Innodb Scalability Fixes having limited concurrency helps. The actual number may be higher or lower depending on your application and default which is 8 is decent start

innodb_flush_method=O_DIRECT Avoid double buffering and reduce swap pressure, in most cases this setting improves performance. Though be careful if you do not have battery backed up RAID cache as when write IO may suffer.

innodb_file_per_table – If you do not have too many tables use this option, so you will not have uncontrolled innodb main tablespace growth which you can’t reclaim. This option was added in MySQL 4.1 and now stable enough to use.

Also check if your application can run in READ-COMMITED isolation mode – if it does – set it to be default as transaction-isolation=READ-COMMITTED. This option has some performance benefits, especially in locking in 5.0 and even more to come with MySQL 5.1 and row level replication.

The slow query log

- Logs all queries that take longer than log_query_time

- Can also log all queries that don't use indexes with
  --log-queries-not-using-indexes

- To log slow administrative commands use
  --log-slow-admin-statements

0015. InnoDB 테이블스페이스 관리

1. What is Innodb Table space ?

- 그 동안 Myisam 방식으로 테이블 스페이스를 사용하던 mysql은 rollback 이 되지 않는다.

다른 데이터베이스 와 달리 autocommit 이 자동으로 실행 하여

데이터 베이스의 트렌젝션시 발생되는 lock을 처리 하기 힘들었다.

- innodb는 오라클 처럼 shared tablespace ( ibdataN) 와 로그파일 (ib_logfileN ) 로 구성된다.

로그파일은 트랜젝션들을 처리하는 오라클의 아카이브 로그 같이 생각 하면 된다.

2. Mysql의 주요 특징

참고 : Mysql Technical Reference fo Ver 5.0 : 16장 InnodB Configureation

- rollback 을 구현 할수 있다.

- transaction isolation Level

: READ-UNCOMMITED, READ-COMMITED, REPEATABLE-READ, SERIALIZABLE

- innodb_buffer_pool_size

: MyIsam 방식과 달리 buffer poll을 이용하여 인덱스와 raw data를 캐싱한다.

따라서 사용량이 많아 지면 tablespace Disk I/O 가 그만큼 줄어 든다

pool의 용량은 real memory의 80% 까지 줄수 있으나, 다른 어플리 케이션과 사용시

너무 많이 주게 되면 운영 체제의 paging을 일으키게 된다.

( 참고로 오라클은 SGA 를 50%로 권고 한다)

또 32bit 시스템의 경우 2 ~ 3.5G의 제한되므로 너무 많이 주면 안된다고 한다.

- innodb_data_file_path

아직 오라클 같이 Shared Table Space 를 온라인상에서 추가 하기 힘들다.

오직 my.cnf 환경 파일로 생성이 되고 변경 된다.

형식

innodb_data_file_path = [파일위치1/]파일명1:파일1사이즈:autoextend:max:MAX파일 사이즈

파일 간의 구분자는 ";" 이고 파일의 속성 구분자는 ":" 이며

autoextend:max:2000M 는 맨마지막 파일에만 해당된다.

innodb_file_per_table

를 사용하면 이전 isam 같이 데이터 베이스 폴더 안에 table_name.ibd 라는 파일이 생긴다.

사용예는 다음과 같다.

innodb_data_home_dir=/export/DATABASE/MyData

innodb_data_file_path = ibdata1:500M;ibdata2:500M;ibdata3:500M;ibdata4:200M:autoextend:max:2000M

3. Innodb 관리

3.1 테이블 rename

RENAME TABLE old_db_name.tbl_name TO new_db_name.tbl_name;

3.2 데이블 복구

이전 백업 된 데이터 파일을 이용하여 복구 하는방법

가) ALTER TABLE tbl_name DISCARD TABLESPACE;

-> 현재 tbl_name.ibd 를 삭제한다.

나) 백업된 tbl_name.ibd 를 복사해온다.

다) ALTER TABLE tbl_name IMPORT TABLESPACE;

3.3 SHOW INNODB STATUS

3.4 Adding and Removing InnoDB Data and Log Files

>my.cnf<

innodb_data_home_dir=/export/DATABASE/MyData

innodb_data_file_path = ibdata1:500M:ibdata2:500M:ibdata3:500M:ibdata4:200M:autoextend:max:2000M

3.5 Shared Table Space 용량 조정

가) mysqldump로 innodb table을 백업 받는다.

나) 서버를 중지

다) 현재 사용중인 ibdata1 .. ibdataN 을 삭제 한다.

라) 위의 환경 파일을 수정한다.

마) 서버를 재구동 한다.

바) dump 파일을 import 한다.

3.6 로그파일 관리

보통 다음 과 같은 디폴트 값을 사용하며

# Set buffer pool size to 50-80% of your computer's memory
set-variable = innodb_buffer_pool_size=70M
set-variable = innodb_additional_mem_pool_size=10M

# Set the log file size to about 25% of the buffer pool size
set-variable = innodb_log_file_size=20M
set-variable = innodb_log_buffer_size=8M

3.2 테이블 스페이스 관리

아직 오라클 같이 Shared Table Space 를 온라인상에서 추가 하기 힘들다.

오직 my.cnf 환경 파일로 생성이 되고 변경 된다.

대량의 데이터 베이스 의 경우 3 ~ 4G 이상이 되는경우

디폴트로 ibdata1:10M:autoextend 를 쓴다면 곤욕을 치르게 될것이다.

다음과 같이 여러개의 파일을 이용하여 분산 하는 것을 추천 합니다.

파일은 path을 이용 할수 도 있고 raw 디바이스를 이용 할수도 있습니다.

/data1 .. /data4 를 각각 다른 디바이스라고 한다면 아래와 같이 사용하면 된다.

innodb_data_home_dir=

innodb_data_file_path = /data1/ibdata1:500M;/data2/ibdata2:500M;/data3/ibdata3:500M;/data4/ibdata4:200M:autoextend:max:2000M

보통 백업을 위해서 오라클의 경우 500MB ~ 1GB로 데이터 파일 을 생성 하였다.

그리고 200MB는 현재 사용량이 얼마인지 모르기 때문에 200MB -> 300MB가 되면

다시 환경 파일을 수정하고 데이터 파일을 추가 하기 위한 방법이다.

그러나 이것도 문제 가 많다는 것을 독자들도 알것이다.

암튼 아직은 미약한 부분이 많다는 것을 착안 하기 바란다.

데이처 파일을 500MB ~ 1GB로 생성 하는이유는

예전에 OS에서 제한이 있어서 그런 적도 있었지만 , 파일이 크면 그 만큼 I/O분산이 안된다.

그리고 파일당 백업 시간이 증가하거나 문제가 발생 하면 복구도 그만큼 힘들다.

예로 500MB데이터 파일 하나가 사라진 경우와 1GB 데이터 파일 1개가 사라진 경우

그안에 속한 데이터의 량은 곱절이 된다.

즉 재난 을 최소화 하기 위해서라고 1GB이하로 쪼개는 것이 낳기 때문이다.

그렇다고 3GB 되는 것을 100MB로 쪼개면 어떨까 performance에서 문제가 될것이다.

그러므로 DBA의 적절한 선택이 필요하다.

== SYSIA01 테이블 상태 확인

[sysoper@mysql_test btproworks]$ myisamchk --extend-check SYSIA01

Checking MyISAM file: SYSIA01

Data records: 14981 Deleted blocks: 0

myisamchk: warning: 1 client is using or hasn't closed the table properly

- check file-size

- check record delete-chain

- check key delete-chain

- check index reference

- check records and index references

MyISAM-table 'SYSIA01' is usable but should be fixed

== 테이블 복구

[sysoper@mysql_test btproworks]$ myisamchk -r SYSIA01

-- recovering (with keycache) MyISAM-table 'SYSIA01'

Data records: 14981

== 테이블 조회

mysql> select * from SYSIA01 limit 1;

+--------------+--------+------------+---------+-------------+------------+---------+----------+-----------+-----------+----------------+--------------+-----------+

| nComponentID | nStage | nProjectID | sTypeID | sCategoryID | sProgramID | nStatus | sVersion | sFileName | sFilePath | sComponentName | sDescription | nFileSize |

+--------------+--------+------------+---------+-------------+------------+---------+----------+-----------+-----------+----------------+--------------+-----------+

| 1 | 0 | 1 | A08 | | | 1 | 2 | | | | | NULL |

+--------------+--------+------------+---------+-------------+------------+---------+----------+-----------+-----------+----------------+--------------+-----------+

1 row in set (0.00 sec)

ERROR 1194 (HY000); Table 'guess' is marked as crashed and should be repaired

이는 데이터가 깨져서 복구돼야 된다는 것을 의미한다. 이럴 때는 다음과 같은 방법으로 데이터를 복구한다.

check table SYSDB10;

그러면 에러의 내용을 표시한다. 그리고 다음과 같이 에러를 복구한다.

repair table SYSDB10;

그러면 데이터가 수정되면서 그 수정된 내용이 무엇인지 출력될 것이다.

명렁어 자체가 자연어에 유사해서 더 이상의 설명이 필요없을 것이다.

-- 스키마 테이블 용량 조회

SELECT CONCAT(table_schema,'.',table_name),

CONCAT(ROUND(table_rows/1000000,2),'M') ROWS,

CONCAT(ROUND(data_length/(1024*1024),2),'M') DATA,

CONCAT(ROUND(index_length/(1024*1024),2),'M') idx, 

CONCAT(ROUND((data_length+index_length)/(1024*1024),2),'M') total_size, 

ROUND(index_length/data_length,2) idxfrac 

FROM

information_schema.TABLES 

WHERE

Table_schema='cheju_ib20' 

-- 스키마 테이블 건수 조회

SELECT COUNT(*) FROM information_schema.TABLES WHERE Table_schema='cheju_ib20' 

-- 스키마 테이블 용량만 조회

SELECT CONCAT(table_schema,'.',table_name),

CONCAT(ROUND((data_length+index_length)/(1024*1024),2),'M') total_size

FROM

information_schema.TABLES 

WHERE

Table_schema='cheju_ib20'

MySQL의 MyISAM과 InnoDB 엔진 활용

데이터베이스 부문에 몸담은 사람이라면 누구나 알고 있듯이 MySQL은 무료로 다운로드해 설치 가능하면서 성능 면에서도 좋은 평가를 받아 세계적으로 가장 많이 쓰이는 DBMS 중 하나다. 그런데 막상 MySQL을 설치해 이용하는 경우라도 그 기능의 많은 부분을 놓치고 있는 경우가 많은 게 사실이다. 따라서 이 글에서는 MySQL의 몇 가지 기능들 가운데 MySQL의 기본 엔진이 되는 MyISAM과 InnoDB라는 엔진의 활용법에 대해 이야기해 보고자 한다.

현재 MySQL에서 제공하는 엔진 타입은 약 10가지 종류며 그 중에서 가장 대중적으로 쓰이는 것은 아마도 MyISAM, InnoDB, ndbcluster가 아닐까라고 생각해 본다. 참고로 ndbcluster는 MySQL Cluster(이중화)에서 사용하는 엔진으로 이해하면 된다. MySQL을 사용하는 사람들 중에는 자신이 무슨 엔진을 쓰는지조차 모르고 사용하는 경우가 적지 않다. 또는 당연한 듯이 자신의 시스템은 MyISAM 타입의 엔진을 쓰는 것으로 착각하고 있을지도 모른다. 그러나 자신이 MySQL을 설치하고 TABLE 생성 시 엔진타입에 아무런 설정을 하지 않았다면 MyISAM 타입이 된다. 그러면 어떻게 엔진 타입을 선택하고 MyISAM과 InnoDB를 사용하는지 알아보도록 하겠다.
MySQL의 독특한 특징 가운데 하나는 각각의 TABLE마다 엔진타입을 생성 또는 변경할 수 있다는 것이다. 생성 방법은 TABLE 생성 시 마지막에 ENGINE=MYISAM 또는 ENGINE=InnoDB로 옵션을 사용해 엔진을 정의하는 것이다. 즉, 하나의 데이터베이스 안에 있는 여러 TABLE의 엔진타입을 임의대로 선정할 수 있고 사용할 수 있다는 의미다. 그럼 MyISAM과 InnoDB의 선택 기준은 어떻게 될까?

트랜잭션의 사용 여부에 따른 선택

트랜잭션은 어떤 작업을 수행할 때 작업에 실패하더라도 일부 잘못된 작업이 실질적으로 수행해야 할 작업에는 영향을 미치지 않도록 하는 기능이라 볼 수 있다. 어찌 보면 DBMS의 가장 중요한 기능으로 여겨지는 트랜잭션 기능이 MySQL의 MyISAM 엔진에는 누락되어 있다. 왜 그럴까?
MySQL의 개발 목적은 아마도 다양한 유저들에게 입맛에 맞는 엔진을 선택하게 함으로써 다양하게 쓸 수 있는 기회를 제공하는 데 있는 것 같다. 그래서 MyISAM은 트랜잭션이 필요 없는 사이트에 사용되며 DML 중 Select가 주인 업무에 적합하다고 보면 된다. 어떻게 보면 데이터 실무에서 트랜잭션을 지원하지 않는 것은 말이 되지 않는 상황이지만 나름 필요한 용도가 있고 또한 현재까지 MySQL에서 이 엔진을 기본 엔진으로 고집하는 이유가 있기 때문에 사용하는 것 아닐까 생각된다.
트랜잭션을 지원하지 않는 것은 곧 데이터의 일관성이 무시된다는 뜻이 되기도 한다. 한마디로 MySQL의 MyISAM 엔진에 대한 생각은 이런 것 같다. 많은 트랜잭션은 시스템 자원을 많이 소모해 전체 성능을 떨어뜨릴 수 있기 때문에 이런 로직을 거부함으로써 다른 어떤 DB보다도 빠른 조회를 할 수 있도록 하는 부분을 강점으로 내세운 것이다. 그 대신 다음에 설명할 LOCK TABLES/UNLOCK TABLES를 이용해 트랜잭션과 같은 일을 할 수 있도록 구현되었다.
결과적으로 보면, MyISAM은 트랜잭션의 무 지원으로 인해 트랜잭션을 요하는 은행, 쇼핑몰 등의 금융 거래가 오가는 사이트에 부적합하므로 이와 같은 사이트에는 InnoDB 또는 트랜잭션 기능이 있는 엔진을 이용하는 방향으로 고려해야 할 것이다.

LOCKING 방식에 따른 선택

MyISAM은 LOCKING 타입이 TABLE 단위 LOCKING이고, InnoDB는 오라클과 유사한 ROW 단위의 LOCKING 방식 로직을 사용하고 있다. MyISAM 엔진은 INSERT, UPDATE, DELETE 수행 시 해당 액세스하는 TABLE 전체에 LOCKING이 걸리는 방식이다. 이 말은 TABLE에서의 Row 수가 증가하면 증가할수록 다른 TABLE을 조인해 데이터 수정 작업을 하는 경우 수행 결과가 느려질 것이고 기다리는 시간만큼 TABLE Lock이 걸려있기 때문에 다음 수행을 진행할 수 없는 결과를 초래한다. 이를 해결하기 위해서는 MySQL 역시 쿼리에 대한 튜닝을 진행해 각 SQL이 1초 이내로 수행될 수 있도록 해야 할 것이다.
반면에 InnoDB는 ROW 단위의 LOCKING 방식을 사용하고 있으므로 MyISAM의 LOCKING 방식을 보안할 수 있는 엔진임에 틀림없다. 물론 MySQL은 공개 DBMS라는 특징이 있고 오라클처럼 우수한 LOCKING 아키텍처를 가지고 있지는 않으므로 동시에 몇 백 개의 트랜잭션이 들어왔을 때 성능이 급격히 떨어지는 것을 주변에서 흔히 볼 수 있다. 이 부분은 구조적으로 보완해야 할 부분으로 판단되며 데이터량이 급증하는 시대적 흐름을 따라가기 위해서는 구조 개선이 필요하지 않을까 생각된다.

메모리 관리 방식에 따른 선택

무엇보다 InnoDB는 별도의 Buffer Pool을 가지고 있기 때문에 MyISAM 엔진처럼 OS 자체의 Buffer Cache 사이즈를 OS가 자동으로 동적 할당하지 않고 별도의 데이터를 위한 쓰기 버퍼 메모리를 가지고 있어 디스크를 직접 쓰지 않아도 되는 메모리에서 액세스되는 논리적인 읽기를 하게 된다.
MyISAM에서 사용하는 Buffer Cache 중 Key Buffer Cache는 TABLE 중 인덱스만을 키 버퍼(메모리)에 캐시하게 된다. 그래서 TABLE의 데이터를 가져오기 위해서는 OS에서 캐시 된 데이터를 가져와야 하기 때문에 값비싼 시스템 콜을 호출해야 한다. 반면 InnoDB 엔진은 오라클처럼 별도의 데이터를 위한 읽기 쓰기 버퍼 캐시 메모리를 가지고 있으므로 인덱스와 데이터에 대한 캐시 데이터를 가지고 있다. 이미 알고 있겠지만 쿼리로 질의하면 대부분의 DBMS는 Buffer Pool에서 데이터를 검색하고 없을 경우 디스크에서 읽어서 Buffer Pool에 올려놓으며 이것을 Physical Read라 한다. 반대로 이미 Buffer Pool에 데이터가 존재해 디스크를 직접 읽지 않아도 되는 경우를 Logical Read라 한다. 즉, Logical Read가 Physical Read에 비해 읽기 성능이 빠르기 때문에 OS의 메모리 상태 비율에 맞춘 적절한 Buffer Pool을 잡아준다면 성능 향상에 도움을 줄 것이다.

MySQL 운영에서 발생하는 장애 복구 방법에 따른 선택

우선 MySQL의 장애 원인을 들어보면 다섯 가지 정도로 정리할 수 있다. 1) 데이터가 Write되는 중간에 MySQL 프로세스가 죽었을 때 2) 의도하지 않은 시스템의 다운 시 3) 하드웨어적인 오류 4) 외부 프로그램에 의해 TABLE을 변경하는 시점에 서버에서 변경 작업이 이뤄졌을 때 5) MySQL의 버그 등이다.
MySQL의 데이터 깨짐 현상은 다른 상용 DBMS보다 흔하게 발생되지만 데이터가 없어지거나 복구가 불가능하지는 않다는 것은 MySQL의 공식입장이나 필자의 경험에 비춰 보면 충분히 완전복구가 가능하다는 것으로 받아들여진다. 그러나 완전복구가 가능하더라도 복구방법이 어렵기만 하다면 사용자들은 장애 시 크나큰 어려움을 겪게 될 것이다. 그렇다면 각 엔진의 복구방법에 대해 간단히 살펴보자.
MyISAM은 TABLE 손상 시 myisamchk, repair table, check table 같은 명령어를 사용해 TABLE을 대부분 수동 복구시키는 반면에 InnoDB는 MyISAM TABLE에 비해 Double Write, Checksum, Validation 로직들과 버그 보안으로 TABLE 손상이 거의 발생되지 않는 장점이 있어 별도의 복구 도구를 제공하지 않고 자동복구 모드를 사용해 관리된다. 물론 MyISAM에서의 자동복구 옵션이 존재하긴 하지만 2차 데이터 손상에 대한 위험이 있을 수 있으므로 자동복구 옵션을 권장하고 싶지는 않다.

지금껏 얘기한 네 가지 차이점은 두 가지 엔진이 가지고 있는 독특한 특징이라고 볼 수 있다. MySQL은 5.5 버전부터 InnoDB 엔진타입을 기본 스토리지 엔진으로 채택해서 배포 중이다. 그만큼 InnoDB의 기능이 스토리지, 관리, 효율성 측면에서 많이 향상됐음을 보여주는 결과다. 특히 오라클의 MySQL 인수 이후 발표된 5.5 버전의 스레드 동시성 처리, 메모리 할당 제어, LOCKING 알고리즘, 복구 성능 등의 향상은 이용자들의 호평을 받았다. 5.5 버전이 출시되면서 InnoDB가 재설계되어 그 동안 MySQL의 기본 엔진이었던 MyISAM이 InnoDB로 변경되어 등장한 것은 특히 눈여겨볼 부분. 이는 앞으로 오라클에서 InnoDB를 기본 스토리지 엔진으로 사용할 것이고 MyISAM도 여전히 이용은 가능하지만 앞으로는 MyISAM 엔진의 획기적인 성능 향상이 없을 것임을 내비치는 암시일지도 모른다. 현재 자신의 시스템이 MyISAM이고 성능상에 다소 문제가 있다고 판단된다면 InnoDB를 한번쯤 고려해 보길 권한다.

set session wait_timeout = 30;
set session interactive_timeout = 30;

show variables like '%timeout%';
show global variables like '%timeout%';

-----------------------------------------------------------------
wait_timeout은 연결을 끊기 전 서버에서 대기하는 시간을 말합니다.
-----------------------------------------------------------------
connect_timeout은 MySQL 서버 연결 실패 메세지를 보내는 시간을 말합니다.

delayed_insert_timeout은 insert delayed 쿼리가 끝나기 전에 insert 쿼리가 기다리는 시간 다시 말해
정해진 시간이 넘도록 insert를 하지 않으면 삭제되는 것을 의미합니다.

interactive_timeout은 활동하는 클라이언트를 서버가 기다려 주는 시간을 말합니다.

net_read_timeout은 읽기를 중지하기 전에 다음 읽기를 기다리는 시간을 말합니다.

slave_net_timeout 읽기를 중지하기 전에 master/slave 연결에서 데이터를 기다리는 시간을 말합니다.

wait_timeout이 interactive_timeout의 변수와 같이 설정을 해주시면 원하시는 결과를 얻을 수 있을 것 같네요

초기 mysql root 패스워드 설정 및 비번 변경과 root 비번 잃어버렸을 경우 변경 하는방법



1. 초기 mysql root 패스워드 설정하기
mysql 서버에 패스워드 없이 로그인 하게되면 서버에 만들어진 데이터베이스나 테이블
기타 이곳에 저장된 자료가 외부인에게 노출될 수 있다. 따라서 이러한 보안을 목적으로
root 패스워드를 지정하면 mysql 서버를 안전하게 보호할 수 있다.

mysql> use mysql;
mysql> update user set password=password('myadmin') where user='root';
Query OK, 2 rows affected (0.03 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> flush privileges; <--- 적용
flush privileges; 를 하지 않으면 mysql에서 빠져나와서 root 로그인이 안된다.
확인한다.
mysql> select host, user, password from user;
+----------------------------------------------------------------+
| Host user password |
+----------------------------------------------------------------+
| localhost root 2e01146f5c065853 |
| localhost.localdomain root 2e01146f5c065853 |
+----------------------------------------------------------------+

mysql> \q
Bye
------------------------------------------------------------------------------------
[주의]# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
그대는 mysql 을 shutdown 하거나 reload 하면 된다.
# /usr/local/mysql/bin/mysqladmin -u root -p shutdown
하면 패스워드를 묻는데 이때 입력하면 설정한 mysql이 적용이된다.
# /usr/local/mysql/bin/mysqladmin -u root -p reload;
------------------------------------------------------------------------------------

# mysql -u root -p [Enter] // root 패스워드로 로그인
Enter password: ******

※ Mysql 서버에 root 패스워드가 설정되면 이제 부터는 mysql 서버에 접속하기 위해서는
위와 같이 -u 뒤에 root입력하고 -p 옵션을 사용하여 패스워드를 사용한다는 것을 명시해야 한다.
그리고 엔터를 치면 root 패스워드를 입력받기 위해 커서가 멈춘다.
(mysql 명령어만 입력하고 엔터를 치면 패스워드를 묻지 앟고 바로 mysql> 프롬프트가 나온 옛 시절을 생각해 보면 금방 차이를 느낄수 있음)

2. 루트 비번 변경
mysql> use mysql;
mysql> update user set password=password('123456') where user='root';
mysql> flush privileges;

3. root 패스워드를 분실한 경우(응급조치)

mysql을 오랫동안 사용하지 않았을 경우에 간혹 root패스워드가 기억나질않아서 당황할 때가 있습니다. 시스템관리자라면 시스템의 root나 MySQL의 root의 암호를 잊어 버렸을 때를 대비해서 패스워드를 새로 설정하는 방법을 반드시 숙지하고 있어야 할 것입니다.

① 실행중인 msyql 종료

# ps -ef | grep mysqld
root 9567 1 0 Mar16 ? 00:00:00 sh ./mysqld_safe
root 9576 9567 0 Mar16 ? 00:00:00 /usr/local/mysql/libexec/mysqld
root 9578 9576 0 Mar16 ? 00:00:00 /usr/local/mysql/libexec/mysqld
root 9579 9578 0 Mar16 ? 00:00:00 /usr/local/mysql/libexec/mysqld

# killall mysqld

② grant-table 미사용모드로 mysql시작(권한 테이블을 사용하지 않는 옵션으로 데몬 실행)

nt일 경우 :
c:\program files\mysql\mysql server 4.1\bin>mysqld-nt --standalone --skip-grant-tables
만약 Error 2003 Hy000 Can't connect to MySQL server on 'localhost' 와 같은 메세지가 나온다면

c:\program files\mysql\mysql server 4.1\bin>mysqld --defaults-file="c:\program files\mysql\mysql server 4.1\my.ini" --consol --skip-grant-tables
따로 프롬프트는 떨어지지 않으므로 새로 cmd창을 열어서 접속해 본다.


# ./safe_mysqld --skip-grant-tables &
[1] 12084
# Starting mysqld daemon with databases from /usr/local/mysql/data
#

# ./mysql -u root mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 3.22.24
Type 'help' for help.
mysql>

※ mysqld_safe 명령어는 mysql 데몬을 실행시킨다.

③ update문으로 root사용자 패스워드 갱신

mysql> update user set password=password('123') where user = 'root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> \q
Bye

④ 실행중인 mysql 다시 종료(권한 테이블을 사용하지 않는 데몬을 종료)

# ps -ef | grep mysqld
root 12084 11558 0 20:10 pts/2 00:00:00 sh ./mysqld_safe --skip-grant-ta
root 12090 12084 0 20:10 pts/2 00:00:00 /usr/local/mysql/libexec/mysqld
root 12092 12090 0 20:10 pts/2 00:00:00 /usr/local/mysql/libexec/mysqld
root 12093 12092 0 20:10 pts/2 00:00:00 /usr/local/mysql/libexec/mysqld
#
# killall mysqld
mysqld daemon ended
[1]+ Done ./mysqld_safe --skip-grant-tables
#

⑤ Mysql 데몬 재 실행 후 갱신된 패스워드로 로그인

# ./safe_mysqld &
[1] 12102
# Starting mysqld daemon with databases from /usr/local/mysql/data
#

# ps -ef | grep mysql
root 12102 11558 0 20:13 pts/2 00:00:00 sh ./mysqld_safe
root 12108 12102 0 20:13 pts/2 00:00:00 /usr/local/mysql/libexec/mysqld
root 12110 12108 0 20:13 pts/2 00:00:00 /usr/local/mysql/libexec/mysqld
root 12111 12110 0 20:13 pts/2 00:00:00 /usr/local/mysql/libexec/mysqld

# mysql -u root -p
Enter Password: ***************
-->

 6184902_my.txt

6184904_my(1).txt

이번 5.5 버전의 특징은 기존 버전에 비해 많my.txt 이 개선된 처리능력과 안정성 보여준다. (어느 정도의 퍼포먼스를 보여줄지 참 기대됩니다)

다양한 멀티 코어 환경에서의 안정성도 많이 높아졌으며, 이번 버전부터 InnoDB가 기본 스토리지 엔진으로 자리를 잡았다.

대부분 작은규모의 사이트들은 MyISAM 스토리엔진을 많이 이용한다. 체감속도도 빠르고 트랜젝션 사용할 일이 거의 없기때문이 아닐까?

InnoDB가 기본스토리지 엔진으로 자리 잡았다는것은 퍼포먼스 속도면에서 이전보다 많은 향상이 있지않았나 싶다. 결국 트랜젝션 처리를 이용한 보다 안정적인 서비스 운영이 가능해졌다는 결론? 써보자…백문이 불여일견

mysql 5.5.8 install

]# useradd -M -s /bin/false mysql

1단계:마이그레이션 && 컴파일 설치

기존 mysql은 깨끗히 삭제한다.(mysql stop->pid kill->삭제)

5.5 버전부터는 소스컴파일시 cmake가 필요하단다

wget http://www.cmake.org/files/v2.6/cmake-2.6.4.tar.gz

cd cmake-2.6.4

./bootstrap

make && make install

cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DINSTALL_DATADIR=/usr/local/mysql/data -DWITH_INNOBASE_STORAGE_ENGINE=1 -DDEFAULT_CHARSET=euckr -DDEFAULT_COLLATION=euckr_korean_ci -DENABLED_LOCAL_INFILE=1 -DWITH_EXTRA_CHARSETS=all -DWITH_SSL=system -DWITH_ZLIB=system

** cmake 로 설치시에 아래와 같이 옵션
-DDEFAULT_COLLATION=euckr_korean_ci \
-DDEFAULT_CHARSET=euckr \

컴파일시 에러가 발생하는 경우가 있다. 메시지를 잘 보고 필요한 패키지를 설치한다.(rm CMakeCache.txt —- cmake로 생성된 설정파일 삭제)

make && make install

컴파일 완료 (여기까진 무지쉽다. -,-)

2단계 : 바이너리 파일 받아서 설치하고자 하는 디렉토리에 풀어 넣는다.

[바이너리 버전 설치 순서]
shell> groupadd mysql
shell> useradd -g mysql mysql
shell> cd /usr/local
shell> gunzip < /path/to/mysql-VERSION-OS.tar.gz | tar xvf -
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> chown -R mysql .
shell> chgrp -R mysql .
shell> scripts/mysql_install_db --user=mysql
shell> chown -R root .
shell> chown -R mysql data
shell>

bin/mysqld_safe --user=mysql &

## CNF 파일 적용 후 에러 발생 시(/usr/local/mysql/bin/mysqld: Table 'mysql.plugin' doesn't exist)

./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/fsb

* 패스워드 설정
/usr/local/mysql/bin/mysqladmin -u root password 'myadmin'
/usr/local/mysql/bin/mysqladmin -u root -h 127.0.0.1 password 'new-password'

3단계 : 환경설정및 데몬띄우기

#mysql-5.5.8]

#cp support-files/my-medium.cnf /etc/my.cnf

#vi /etc/my.cnf
innodb 환경설정
innodb_data_home_dir = /usr/local/mysql-5.5.8/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql-5.5.8/

innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqld]
port = 3306
socket = /tmp/mysql.sock
basedir = /usr/local/mysql-5.5.8
max_connections = 10000
….

#scripts/mysql_install_db –user=mysql
# vi /root/.bash_profile
PATH=$PATH:$HOME/bin:/usr/local/mysql-5.5.8/bin:/usr/local/mysql5.5.8/lib
#source /root/.bash_profile
#./bin/mysqld_safe –user=mysql &
#mysqladmin -u root -p 비밀번호
#ln -s /usr/local/mysql-5.5.8/support-files/mysql.server /etc/init.d/mysqld
# cd /etc/init.d
# chkconfig –add mysql
# chkconfig –list |grep mysql

#ps aux|grep mysql (데몬 확인)

필요한 라이브러리 설치

sudo apt-get install gcc g++ libncurses5-dev libxml2-dev openssl libssl-dev curl libcurl4-openssl-dev libjpeg-dev libpng-dev libfreetype6-dev libsasl2-dev autoconf libncurses5-dev

다운로드

wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.3-m3.tar.gz/from/http://mysql.he.net/

MySQL 계정 만들기

sudo groupadd mysql
sudo useradd -g mysql mysql
sudo passwd mysql
sudo mkdir /home/mysql
sudo chown mysql:mysql /home/mysql
tar zxvf mysql-5.5.3-m3.tar.gz
cd mysql-5.5.3-m3/

MySQL 소스 컴파일

./configure --prefix=/opt/mysql --localstatedir=/opt/mysql/data --enable-assembler --enable-static --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static --with-unix-socket-path=/usr/local/mysql/tmp/mysql.sock --without-innodb --without-isam --without-ndb-debug --without-debug --with-big-tables --with-extra-charsers=gbk,gb2312,utf8 --with-pthread --with-plugins=partition,innobase,myisammrg --with-charset=euckr --with-readline

sudo make
sudo make install

디렉토리 생성

sudo mkdir -p /data/mysql/data/
sudo mkdir -p /data/mysql/log/
sudo mkdir /var/log/mysql
sudo chown mysql:mysql /var/log/mysql
sudo chmod 750 /var/log/mysql

my.cof 복사

참조 :
http://blog.naver.com/iamfreeman/50006780132
http://blog.dasom.pe.kr/35

sudo cp /usr/local/mysql/share/mysql/my-medium.cnf /data/mysql/my.cnf

디렉토리 권한 변경

sudo chown -R mysql:mysql /usr/local/mysql
sudo chown -R mysql:mysql /data/mysql/

MySQL Database 생성

user1@ubuntu:~/mysql-5.5.3-m3$ sudo /usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql/data --user=mysql
Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/local/mysql/bin/mysqladmin -u root password 'new-password'
/usr/local/mysql/bin/mysqladmin -u root -h ubuntu password 'new-password'

Alternatively you can run:
/usr/local/mysql/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr/local/mysql ; /usr/local/mysql/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/local/mysql/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/local/mysql/scripts/mysqlbug script!

user1@ubuntu:~/mysql-5.5.3-m3$

부팅시 자동 시작 설정

sudo cp /usr/local/mysql/share/mysql/mysql.server /etc/init.d/mysqld
sudo ln -s /etc/init.d/mysqld /etc/rc2.d/S99mysql
sudo ln -s /etc/init.d/mysqld /etc/rc2.d/K99mysql
sudo ln -s /etc/init.d/mysqld /etc/rc3.d/S99mysql
sudo ln -s /etc/init.d/mysqld /etc/rc3.d/K99mysql

mysqld 수정

sudo vi /etc/init.d/mysqld

1. parse_server_arguments 주석 처리 한다.
=> #parse_server_arguments

2. $bindir/mysqld_safe 수정

#$bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null 2>&1 &

기존 내용은 주석으로 하고 아래와 같이 추가 한다.

$bindir/mysqld_safe --defaults-file=/data/mysql/my.cnf 2>&1 > /dev/null &

재부팅

sudo sync
sudo sync
sudo init 6

실행확인

user1@ubuntu:~$ ps -ef | grep mysql
root 2506 1 0 12:09 pts/1 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/my.cnf
mysql 2738 2506 0 12:09 pts/1 00:00:00 /usr/local/mysql/libexec/mysqld --defaults-file=/data/mysql/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql/data --plugin-dir=/usr/local/mysql/lib/mysql/plugin --user=mysql --log-error=/data/mysql/data/ubuntu.err --pid-file=/data/mysql/data/ubuntu.pid --socket=/usr/local/mysql/tmp/mysql.sock --port=3306
user1 2751 2104 0 12:09 pts/1 00:00:00 grep --color=auto mysql

user1@ubuntu:~$

root 암호 설정

/usr/local/mysql/bin/mysqladmin -u root password 'myadmin' -S /usr/local/mysql/tmp/mysql.sock

MySQL 외부에서 접속하기

user1@ubuntu:~$ /usr/local/mysql/bin/mysql -u root -p -S /usr/local/mysql/tmp/mysql.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.3-m3-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant all privileges on *.* to root@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql>

ssh 데몬 설치

sudo apt-get install openssh-server
sudo init 6 (재부팅)

MySQL - SQLGate로 SSH 터널링을 통해 연결하기

MySQL의 기본 명령어와 SQL문 정리. 내가 RDBMS에 완전 문외한이기 때문에 기본 SQL쿼리까지...
Redhat Linux 7.3, MySQL 3.23.58 에서 테스트함.
설치는 Redhat 기본 제공 RPM으로 했다.

데이터베이스 접속

$ mysql -u 사용자명 -p dbname

설치 직후에는 root 사용자에 비밀번호가 없으므로 다음과 같이 접속하여 MySQL을 관리할 수 있다.

$ mysql -u root mysql

비밀번호 변경
MySQL을 설치한 직후에는 root 계정에 암호가 지정되어 있지 않다.
다음 세가지 방법으로 비밀번호를 변경 할 수 있다.

• mysqladmin이용

  $ mysqladmin -u root password 새비밀번호

   

• update문 이용

  $ mysql -u root mysql
  
  mysql> UPDATE user SET password=password('새비밀번호') WHERE user='root';
  mysql> FLUSH PRIVILEGES;

   

• Set Password 이용

  SET PASSWORD FOR root=password('새비밀번호');

   

일단 root 비밀번호가 설정된 상태에서는 mysql이나 mysqladmin 명령을 실행할 때 -p 옵션을 붙여주고 기존 비밀번호를 입력해야만 한다.

사용자 추가/삭제

mysql> GRANT ALL PRIVILEGES ON dbname.* TO username@localhost IDENTIFIED BY 'password';

username 이라는 사용자를 password라는 비밀번호를 갖도록 하여 추가한다. username은 dbname이라는
데이타베이스에 대해 모든 권한을 가지고 있다.
username 사용자는 로칼 호스트에서만 접속할 수 있다. 다른 호스트에서 접속하려면

GRANT ALL PRIVILEGES ON dbname.* TO username@'%' IDENTIFIED BY 'password';

위를 또한 번 실행한다. '%'에서 홑따옴표를 주의한다.
추가 : '%'를 호스트네임으로 지정해도 모든 호스트에서 접속할 수 없었다. 각 호스트별로 다 지정해야 했다.
아래 댓글에서 rukikuki님이 알려주셨습니다.
GRANT ALL PRIVILEGES ON testdb.* TO 'username'@'%' IDENTIFIED BY 'password' ;
처럼 'username' 이런식으로 홑따옴표를 username 에도 붙여야 하더군요..

불필요한 사용자 삭제는

mysql> DLETE FROM user WHERE user='username';
mysql> FLUSH PRIVILEGES;

데이터베이스 생성/보기

• 데이터베이스를 생성하고,

  mysql> CREATE DATABASE dbname;

   

• 현재 존재하는 데이터베이스 목록을 보여준다.

  mysql> SHOW DATABASES;

   

• 특정 데이타베이스를 사용하겠다고 선언한다.

  mysql> USE dbname;

   

• 쓸모 없으면 과감히 삭제한다.

  mysql> DROP DATABASE [IF EXISTS] dbname;

  
  IF EXISTS 옵션은 비록 데이타베이스가 없더라도 오류를 발생시키지 말라는 의미이다.

테이블 생성/보기

• 테이블을 생성하고,

  mysql> CREATE TABLE tablename (
  column_name1 INT,
  column_name2 VARCHAR(15),
  column_name3 INT );

   

• 현재 데이타베이스의 테이블 목록을 보고

  mysql> SHOW TABLES;

   

• 테이블 구조를 살펴본다.

  mysql> EXPLAIN tablesname;
  혹은
  mysql> DESCRIBE tablename;

   

• 이름을 잘못 지정했으면 이름을 변경할 수도 있다.

  mysql> RENAME TABLE tablename1 TO tablename2[, tablename3 TO tablename4];

   

• 필요 없으면 삭제한다.

  mysql> DROP TABLE [IF EXISTS] tablename;

   

현재 상태 보기

mysql> status

--------------
mysql Ver 11.18 Distrib 3.23.58, for pc-linux (i686)

Connection id: 26
Current database: study
Current user: study@localhost
Current pager: stdout
Using outfile: ''
Server version: 3.23.58
Protocol version: 10
Connection: Localhost via UNIX socket
Client characterset: latin1
Server characterset: euc_kr
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 2 hours 9 min 59 sec

Threads: 1 Questions: 160 Slow queries: 0 Opens: 28 Flush tables: 1
Open tables: 1 Queries per second avg: 0.021
--------------

INSERT

mysql> INSERT INTO tablename VALUES(값1, 값2, ...);

혹은

mysql> INSERT INTO tablename (col1, col2, ...) VALUES(값1, 값2, ...);

SELECT

mysql> SELECT col1, col2, ... FROM tablename;

컬럼명을 *로 하면 모든 컬럼 의미.

mysql> SELECT col1 AS '성명', col2 AS '국어점수' FROM grade;

컬럼의 이름을 바꿔서 출력.

mysql> SELECT * FROM tablename ORDER BY col1 DESC;
mysql> SELECT col1, korean + math english AS '총점' FROM tablename ORDER BY '총점' ASC;

DESC는 내림차순 ASC는 오름차순.

mysql> SELECT * FROM grade WHERE korean < 90;

조건줘서 SELECT.

mysql> SELECT * FROM grade LIMIT 10;

결과중 처음부터 10개만 가져오기

mysql> SELECT * FROM grade LIMIT 100, 10;

결과중 100번째부터 10개만 가져오기. 첫번째 레코드는 0번 부터 시작한다.

UPDATE

mysql> UPDATE tablename SET col1=새값 WEHER 조건

DELETE

mysql> DELETE FROM tablename WEHRE 조건

mysql에서 쿼리 결과 세로로 보기
-E 옵션을 줘서 실행한다.

$ mysql -E -u root -p mysql

mysql에서 발생한 오류나 경고 다시 보기

mysql> show errors;
mysql> show warnings;

# root암호설정 - root로 로그인하여 해야함

% mysqladmin -u root password '변경암호'

% mysqladmin -u root -p기존암호 password '변경암호'

root암호변경설정
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
This is done with:
/usr/bin/mysqladmin -u root -p password 'new-password'
/usr/bin/mysqladmin -u root -h ns.dbakorea.pe.kr -p password 'new-password'

DB작업
DB생성: mysql> create database DB명 ( or % mysqladmin -u root -p create DB명 )
DB삭제: mysql> drop database DB명
DB사용: mysql> use DB명 (엄밀히 말하자면, 사용할 'default database'를 선택하는 것이다.)
DB변경: mysql> alter database db명 DEFAULT CHARACTER SET charset (4.1이상에서만 available)

MySQL 연결
mysql -u 사용자 -p DB명 ( or % mysqladmin -u root -p drop DB명 )

데이터파일 실행(sql*loader기능)
mysql>load data infile "데이터파일" into table 테이블명 ;
데이터파일에서 컬럼구분은 탭문자, Null값은 /n로 입력
데이터파일의 위치는 /home/kang/load.txt 와 같이 절대경로로 지정할것.

질의 파일 실행
쉘프롬프트상에서
mysql -u 사용자 -p DB명 < 질의파일
or
mysql프롬프트상에서
mysql> source 질의파일

쉘프롬프트상에서 질의 실행
dbakorea@lion board]$ mysql mysql -u root -pxxxx -e \\
> "INSERT INTO db VALUES(
> 'localhost', 'aaa', 'aaa',
> 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y')"

사용자 생성 & 사용자에게 DB할당
shell> mysql --user=root -p mysql

mysql> INSERT INTO user VALUES('localhost','사용자',PASSWORD('비밀번호'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user VALUES('%','사용자',PASSWORD('비밀번호'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

mysql> INSERT INTO db(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('localhost','DB명','사용자','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES('%','DB명','사용자','Y','Y','Y','Y','Y','Y');

mysql> FLUSH PRIVILEGES; (or shell prompt: mysqladmin -u root -pxxxx reload)

CASE 2: GRANT명령을 이용한 사용자 생성(이 방법이 권장된다)
kang이라는 DB를 만들고, 이 DB를 아래에서 나열된 권한을 가진 kang이라는 사용자를 생성
create database kang;
grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP on kang.* tokang@localhostidentified by 'kang';
grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP on kang.* tokang@'%'identified by 'kang';

mysql> create database kang;
Query OK, 1 row affected (0.00 sec)

mysql> grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP on kang.* tokang@localhost identified by 'kang';
Query OK, 0 rows affected (0.00 sec)

mysql> grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP on kang.* tokang@'%'identified by 'kang';
Query OK, 0 rows affected (0.01 sec)

mysql>

여러가지 명령정리
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

테이블이 존재여부 파악
DROP TABLE IF EXISTS 테이블명;
CREATE TABLE 테이블명 (...);
프로그래밍 언어에서 COUNT(*)를 사용하여 질의가 성공하면 테이블이 존재함을 파악할 수 있다.
ISAM, MyISAM의 경우 COUNT(*)가 최적화되어 상관없으나, BDB, InnoDB의 경우 full scan이 발생하므로 사용하지 마라.
대신 select * from 테이블명 where 0; 을 사용하라. 질의가 성공하면 테이블이 존재하는 것이고, 아니면 존재하지 않는 것이다.

접속
mysql {-h 접속호스트} -u 사용자 -p 사용DB
-h로 다른 서버에 존재하는 MySQL접속시 다음과 같이 MySQL DB에 설정해줘야 한다.
mysql> INSERT INTO user VALUES('접근을 허용할 호스트ip','사용자',PASSWORD('비밀번호'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y', 'Y','Y','Y','Y');
mysql> INSERT INTO db(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES('접근을 허용할 호스트ip','사용DB','사용자','Y','Y','Y','Y','Y','Y');
mysql> FLUSH PRIVILEGES; or 쉴프롬프트상에서 % mysqladmin -u root -p flush-privileges

검색조건(where)
regular expression을 지원하다니 신기하군..
mysql> select * from work where 열명 regexp "정규표현식";

백업 & 복구
mysqldump {-h 호스트} -u 사용자 -p DB명 > 백업파일
mysql {-h 호스트} -u 사용자 -p DB명 < 백업파일

mysqldump -u root -p --opt db_dbakorea > dbakorea.sql
mysqldump -u root -p --opt db_board | mysql ---host=remote-host -C database (상이한 머쉰)
mysql -u dbakorea -p db_dbakorea < dbakorea.sql

mysqldump -u root -p --opt db_dbakorea | mysql ---host=ns.dbakorea.pe.kr -C db_dbakorea

테이블 생성구문만을 화면에서 보려면 다음과 같이 --no-data를 사용한다. 테이블명을 생략하면 모든 테이블 출력
mysqldump -u 유저명 -p --no-data db명 테이블명

테이블 검사
isamchk

오라클 sysdate와 동일
insert into test values('12', now());

유닉스 time()함수 리턴값 사용
FROM_UNIXTIME(954788684)
UNIX_TIMESTAMP("2001-04-04 :04:04:04")

MySQL 디폴트 DB&로그파일 위치
/var/lib/mysql
/var/lib디렉토리는 여러 프로세스들이 사용하는 데이터를 저장하는 일종의 파일시스템상의 데이터베이스라고 볼 수 있다.

replace
해당 레코드 존재하면 update하고, 존재하지 않는다면 insert한다.(insert문법과 동일)
replace into test values('maddog','kang myung gyu')'

explain
explain 질의문: 지정한 질의문이 어떻게 실행될 건지를 보여줌
mysql> explain select u.uid, u.name, a.name from sm_user u, sm_addr a where u.uid=a.uid;
+-------+------+-----------------+-----------------+---------+-------+------+-------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+-----------------+-----------------+---------+-------+------+-------+
| u | ALL | PRIMARY | NULL | NULL | NULL | 370 | |
| a | ref | sm_addr_uid_idx | sm_addr_uid_idx | 11 | u.uid | 11 | |
+-------+------+-----------------+-----------------+---------+-------+------+-------+
2 rows in set (0.01 sec)

temporary table
크기가 큰 테이블에 있는 subset에 대한 질의라면 subset을 temporary table에 저장한 후 질의하는 것이 더 빠를 경우가 있다.
temporary table는 세션내에서만 유효하고(현재 사용자만이 볼수 있다는 뜻), 세션종료시 자동적으로 drop된다.

create temporary table (...);
create temporary table (...) type=heap; 디스크가 아닌 메모리에 테이블 생성

존재하는 permanent table의 테이블명과 동일하게 생성할 수 있으며,
temporary table은 permanent table보다 우선시되어 처리된다.
4.0.7의 감마버전에서 테스트하면 결과는 약간 달라진다. 버그인건지..

mysql> create table test (id varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values('dbakorea');
Query OK, 1 row affected (0.00 sec)

mysql> create temporary table test(id varchar(10));
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
Empty set (0.00 sec)

mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+----------+
| id |
+----------+
| dbakorea |
+----------+
1 row in set (0.00 sec)

Table Type에 다른 Files on Disk

ISAM .frm (definition) .ISD (data) .ISM (indexes)
MyISAM .frm (definition) .MYD (data) .MYI (indexes)
MERGE .frm (definition) .MRG (list of constituent MyISAM table names)
HEAP .frm (definition)
BDB .frm (definition) .db (data and indexes)
InnoDB .frm (definition)

보통 mysqldump를 사용하여 백업을 수행하여 다른 DB서버에 데이터를 restore하면 된다.
MySQL은 별다른 작업없이 데이터파일을 단순히 복사(copy)하는 것만으로도 다른 서버에
DB을 이동시킬 수 있다. 하지만, 이런 방식이 지원되지 않는 table type도 있다.

ISAM: machine-dependent format하기때문에..
BDB : .db파일에 이미 테이블위치가 encode되어 있기때문에..
MyISAM, InnoDB, MERGE :가능(machine-independent format)

별다른 지정을 하지 않았다면 디폴트 TABLE type이 MyISAM이므로, 무난히 migration할 수 있다.
floating-point컬럼(FLOAT,DOUBLE)이 있다면 이러한 방식이 실패할 수 도 있다.

쉘에서는 mysql이 되는데 PHP에서 mysql.sock error를 내면서 MySQL이 안되는 경우
mysql.sock은 /tmp 아니면 /var/lib/mysql에 생기게 된다.
나의 경우, /var/lib/mysql에 mysql.sock파일이 있는데 PHP에서는 /tmp에서 찾으려하면서 에러를 발생했다.
/usr/bin/safe_mysqld파일에서 다음과 같이 수정한다.
주석(#)이 달린 것이 원래것이고 그 밑에 있는것이 수정한 것이다.

# MYSQL_UNIX_PORT=${MYSQL_UNIX_PORT:-/var/lib/mysql/mysql.sock}
MYSQL_UNIX_PORT=${MYSQL_UNIX_PORT:-/tmp/mysql.sock}

위와 같이 하니 /usr/bin/mysql이 /var/lib/mysql/mysql.sock에서 소켓파일을 찾으려 했다.
socket file을 지정하는 --socket이라는 옵션으로 다음과 같이 지정하면 된다.

mysql --socket=/tmp/mysql.sock -u dbakorea -p db_test

하지만 mysql실행시마다 이렇게 써줘야한다는 것이 상당히 귀찮다. 옵션이 바로 적용되게 설정하자.
mysql은 설정사항을 다음 3가지 파일에서 검색한다.

/etc/my.cnf global options(MySQL 전체적으로 사용되는 옵션 정의)
mysql-data-dir/my.cnf 특정 DB에 적용되는 option (/var/lib/mysql/my.cnf)
~/.my.cnf 사용자 각각의 설정('~'문자는 사용자의 홈디렉토리는 의미)

/usr/share/mysql디렉토리에 예제가 있으므로 참고한다.
소켓파일의 지정은 다음줄을 넣어주면 된다.

socket = /tmp/mysql.sock

== /etc/my.cnf예 ==
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock

MySQL에서 통계처리시
orderby, groupby 는 sort_buffer를 늘여준다.(show variables)

live table(smslog)에서 모든 질의를 처리하지 말고 summary table에 질의결과를 저장해 재질의 처리한다.
summary table이 heap-type table가 가능한지 확인할 것.

INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE
tblTemp1.fldOrder_ID > 100;

join이 subselect보다 빠르다.
join시 사용되는 컬럼은 동일한 column type과 길이를 가져야만 최적의 속도를 보장한다.
즉, 동일 column type이지만 길이가 다르다면(char(11), char(10)), 동일한 컬럼도메인으로 변경해주는 것이 좋다.
where의 in은 optimize되어 있으므로 빠르다
insert,select는 동시에 수행가능하다.(어떻게?)
explain으로 질의과정 점검

varchar to/from char
conversion varchar를 char로 변경할 경우 모든 컬럼타입을 동시에 변경해야 한다.
반대의 경우, 하나만 char->charchar변경시 다른 모든 컬럼도 varchar로 변경됨
참.. 특이하구만..

mysql> CREATE TABLE chartbl (name VARCHAR(40), address VARCHAR(80));
Query OK, 0 rows affected (0.05 sec)

mysql> desc chartbl;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(40) | YES | | NULL | |
| address | varchar(80) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.03 sec)

mysql> alter table chartbl modify name char(40);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc chartbl;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(40) | YES | | NULL | |
| address | varchar(80) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table chartbl modify name char(40), modify address char(80);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc chartbl;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| name | char(40) | YES | | NULL | |
| address | char(80) | YES | | NULL | |
+---------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

"For each article, find the dealer(s) with the most expensive price."

표준안
SELECT article, dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article);

수정안(최적화)
CREATE TEMPORARY TABLE tmp (
article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
price DOUBLE(16,2) DEFAULT '0.00' NOT NULL);

LOCK TABLES shop read;

INSERT INTO tmp SELECT article, MAX(price) FROM shop GROUP BY article;

SELECT shop.article, dealer, shop.price FROM shop, tmp
WHERE shop.article=tmp.article AND shop.price=tmp.price;

UNLOCK TABLES;

DROP TABLE tmp;

==============================================================================
MySQL 특성정리
==============================================================================
primary key, foreign key지원
index 지원(15개컬럼, 256byte까지)
MySQL에서의 Stored Script개념 => SQL server language
commit-rollback개념 => lock tables(lock table test write -> 트랜잭션.. -> unlock tables)
컬럼명길이: 64자까지, 컬럼 Alias: 256자까지
not case-sensitive: keywords, functions, column, index명
case-sensitive: database, table, alias명
키워드,함수명은 대소문자구별이 없지만, db명과 table명은 Unix계열이라면 case-sensitive하다.
(이는 오브젝트명이 OS의 fs에 따라 저장되기 때문이다. 서버의 lower_case_table_names 변수를
1로 설정하면 오브젝트명은 모두 소문자로 저장되므로 유닉스-윈도간 호환성을 높일 수 있다.

지원되지 않는 부분:
Stored Procedure(5.0이상부터 지원된다고 함)
View(5.0이상부터 지원된다고 함)
Trigger(5.0이상부터 지원된다고 함)
subquery(4.1이상부터 지원된다고 함)
union, union all(4.0이상부터 지원됨)

[테이블 type에 따른 인덱스 특성]
Index Characteristic ISAM MyISAM HEAP BDB InnoDB
NULL values allowed No Yes As of 4.0.2 Yes Yes
Columns per index 16 16 16 16 16
Indexes per table 16 32 32 31 32
Maximum index row size (bytes) 256 500 500 500/1024 500/1024
Index column prefixes allowed Yes Yes Yes Yes No
BLOB/TEXT indexes allowed No Yes(255 bytes max) No Yes (255 bytes max) No

인덱스 생성
- alter table을 이용한 인덱스 생성이 더 flexible함
- 인덱스명은 생략가능

ALTER TABLE 테이블명 ADD INDEX 인덱스명 (인덱스컬럼);
ALTER TABLE 테이블명 ADD UNIQUE 인덱스명 (인덱스컬럼);
ALTER TABLE 테이블명 ADD PRIMARY KEY (인덱스컬럼);
ALTER TABLE 테이블명 ADD FULLTEXT (인덱스컬럼);

CREATE INDEX 인덱스명 ON 테이블명 (인덱스컬럼);
CREATE UNIQUE INDEX 인덱스명 ON 테이블명 (인덱스컬럼);
CREATE FULLTEXT INDEX 인덱스명 ON 테이블명 (인덱스컬럼);

unique인덱스와 primary key인덱스와의 차이
unique은 null허용하지만, primary key는 null허용 안함
unique은 하나의 테이블에 여러개 올 수 있지만, primary key는 하나만 존재

테이블생성시 지정
CREATE TABLE 테이블명
(
... column declarations ...
INDEX 인덱스명 (인덱스컬럼),
UNIQUE 인덱스명 (인덱스컬럼),
PRIMARY KEY (인덱스컬럼),
FULLTEXT 인덱스명 (인덱스컬럼),
...

);

index prefix 생성
- 컬럼의 전체길이중 일부만 인덱스로 사용
- supported for ISAM, MyISAM, HEAP, and BDB tables, but not for InnoDB tables
- 지정되는 길이는 byte단위가 아닌 charater단위이므로, multi-byte character일 경우 주의
- blob, text 컬럼타입일 경우, index prefix 가 유용(255 길이까지 가능)

CREATE TABLE 테이블명
(
name CHAR(30) NOT NULL,
address CHAR(60) NOT NULL,
INDEX (name(10),address(10))
);

인덱스 삭제
DROP INDEX 인덱스명 ON 테이블명;
ALTER TABLE 테이블명 DROP INDEX 인덱스명;
ALTER TABLE 테이블명 DROP PRIMARY KEY;

outer join

[MySQL]
left outer joing : SELECT t1.*, t2.* FROM t1 LEFT OUTER JOIN t2 ON t1.i1 = t2.i2;
right outer joing: SELECT t1.*, t2.* FROM t1 RIGHT OUTER JOIN t2 ON t1.i1 = t2.i2;

[Oracle]
left outer joing : SELECT t1.*, t2.* FROM t1, t2 where t1.i1 = t2.i2(+);
right outer joing: SELECT t1.*, t2.* FROM t1, t2 where t1.i1(+) = t2.i2;

SELECT
student.name, student.student_id,
event.date, event.event_id, event.type
FROM
student, event
LEFT JOIN score ON student.student_id = score.student_id
AND event.event_id = score.event_id
WHERE
score.score IS NULL
ORDER BY
student.student_id, event.event_id;

:= 문장을 이용한 변수의 설정

현재 dbakorea의 데이터베이스강좌게시판에 등록된 총 게시물은 43개이다. 43개의 강좌를 읽은 수(hit수)는 각각 다르다.
평균 hit수를 구해 보자.

mysql> select @total_hit := sum(hit), @total_record := count(*) from zetyx_board_database;
+------------------------+---------------------------+
| @total_hit := sum(hit) | @total_record := count(*) |
+------------------------+---------------------------+
| 3705 | 43 |
+------------------------+---------------------------+
1 row in set (0.00 sec)

mysql> select @total_hit/@total_record as 평균HIT;
+-----------------+
| 평균HIT |
+-----------------+
| 86.162790697674 |
+-----------------+
1 row in set (0.00 sec)

select substring(subject from 9) from zetyx_board_database where substring(subject, 1, 8) = '[ORACLE]';

보통 상용DBMS들이 row-level locking을 지원한다. 쉽게 말해 레코드단위로 락킹한다는 말이다.
반면, MySQL의 MyISAM 테이블타입은 table-level locking을 사용한다.
쉽게 말하면, insert, update, delete작업은 전체 테이블에 락을 걸고 처리된다는 것이다.
row-level락보다 비효율적이지만,.. MySQL은 빠르기 때문에 이 단점이 상쇄된다.

Compressed MyISAM(packed MyISAM)
정적인 테이블데이터는 압축하여 20-60%정도의 공간을 절약할 수 있다.
Production데이터를 CD로 받아서 차후 디스크에 풀지 않고 CD자체로 바로 사용할 수도 있다.
gzip등으로 백업받으면 이를 푸는 과정이 필요할 것이다.
% myisampack dbakorea.myi

데이터베이스 게시판의 Merge Table에 좀 더 자세한 내용을 적어 두었다.

RAID Table
1개의 테이블은 OS상에 3개의 파일로 구성된다.
스키마파일(.frm), data파일(.myd), index파일(.myi)
MySQL의 RAID테이블은 데이터파일(.myd)을 여러개의 파일들로 구성하는 것이다.

create table raid_test (...)
type=myisam raid_type=striped raid_chunks=4 raid_chunsize=8

테이블을 4개의 데이터파일로 나누고, 8kb단위로(8kb stripe) 라운드로빈 방식으로 write가 이루어진다.

This article comes from dbakorea.pe.kr (Leave this line as is)

데이터베이스에서 데이터를 백업을 받거나 데이터를 텍스트 형태로 저장하기 위해서는, 일반적으로 spool을 많이 사용합니다.

그런데 MySQL은 Oracle과 다르게 아래와 같이 select문을 이용하면 데이터베이스의 내용을 텍스트 파일로 내려 받을 수 있습니다.

[문법]

select (추출할 필드) into outfile (저장할 파일명) fields terminated by (필드간 구분자) lines terminated by (열과 열 사이의 구분자) from (대상 테이블);

추출할 필드 : 테이블의 구성요소인 필드 중에, 추출하기 원하는 필드를 나열하면 됨.
저장할 파일명 : spool 결과로 저장할 파일명을 절대경로 혹은 상대경로로 지정 함.
필드간 구분자 : 추출할 필드와 필드 간에 사용할 구분자를 지정 함. (예. csv 파일은 ; 을 사용함)
열과 열 사이의 구분자 : 테이블에 저장된 레코드와 레코드 사이을 구분하기 위한 구분자를 지정 함.
대상 테이블 : 추출할 대상 테이블 이름을 지정 함.

[예제]

select * into outfile "./output.txt" fields terminated by "|" lines terminated by "\r\n" from tbl_test;

-> tlb_test 테이블의 모든 필드를 output.txt 파일로 저장을 하는데, 각 필드의 구분은 "|" 문자를 사용하고, 열과 열 사이는 줄바꿈으로 저장하시오.

select field1, field2 into outfile "./output.txt" fields terminated by ";" lines terminated by "\r\n" from tbl_test where field3 > 100;

-> tmlb_test 테이블의 field1, field2 필드를 조건이 field3>100 인 경우에 대해서 output.txt 파일에 저장하시오. 단 각 필드의 구분자는 ";" 문자를 사용하고, 열과 열사이는 줄바꿈으로 저장하시오.

1. Source 서버 백업 받기

## DB 접속 후

SHOW TABLE STATUS FROM `o2bank` WHERE ENGINE IS NOT NULL; -- 테이블 storage engine 확인

SHOW TABLE STATUS FROM `primestock` WHERE ENGINE IS NOT NULL; -- 테이블 storage engine 확인

SHOW TABLE STATUS FROM `aceibank` WHERE ENGINE IS NOT NULL; -- 테이블 storage engine 확인

## 명령어 라인에서 선택적으로 사용 : (1)--> 테이블 구조 백업 , 데이터 백업 따로 / (2) --> 테이블 구조 및 데이터 백업

(1)

mysqldump -uroot -pmyadmin o2bank --no-data > createtable.sql -- 테이블 구조 백업

mysqldump -uroot -pmyadmin o2bank --default-character-set=euckr --no-create-info --tab=/home/sysoper/redkite --fields-terminated-by=',' --lines-terminated-by='\r\n' --fields-enclosed-by='"' -- 테이블별 데이터 백업

mysqldump -uroot -pmyadmin primestock --no-data > createtable.sql -- 테이블 구조 백업

mysqldump -uroot -pmyadmin primestock --default-character-set=euckr --no-create-info --tab=/home/sysoper/redkite --fields-terminated-by=',' --lines-terminated-by='\r\n' --fields-enclosed-by='"' -- 테이블별 데이터 백업

(2)

mysqldump -uroot -pmyadmin o2bank --default-character-set=euckr --tab=/home/sysoper/redkite --fields-terminated-by=',' --lines-terminated-by='\r\n' --fields-enclosed-by='"' -- 테이블 구조 및 데이터 백업

## 혹시 모를 FULL 백업

mysqldump -uroot -pmyadmin o2bank --default-character-set=euckr > o2bank.sql

mysqldump -uroot -pmyadmin o2bank --default-character-set=euckr > o2bank.sql

2. Target 서버 백업 받기

## DB 접속 후

SHOW TABLE STATUS FROM `o2bank` WHERE ENGINE IS NOT NULL; -- 테이블 storage engine 확인

## 작업 전 FULL 백업

mysqldump -uroot -pwjcnrdb** o2bank --default-character-set=euckr > o2bank.sql

mysqldump -uroot -pwjcnrdb** primestock --default-character-set=euckr > primestock.dmp

## DB 접속 후 TABLE DROP

DROP TABLE DISCLOSURE;

DROP TABLE ESTATE_INFO;

DROP TABLE MULTIBOARD;

DROP TABLE MULTIBOARD_REPLY;

DROP TABLE POPUP_MNG;

DROP TABLE SCHEDULE;

DROP TABLE TOLPL01;

DROP TABLE TOLXA01;

DROP TABLE TOLXD01;

DROP TABLE TOLXD02;

DROP TABLE TOLXD03;

DROP TABLE TOLXD04;

DROP TABLE TOLXE01;

DROP TABLE TOLXL01;

DROP TABLE TOLXL02;

DROP TABLE TOLXL03;

DROP TABLE TOLXX01;

DROP TABLE USERINFO_MNG;

DROP TABLE ZIPCODE;

## 테이블 생성

ls -al *sql |awk '{print$9}' > tablecreate.out ## 테이블 구조 생성 파일 리스트 추출

sed '1,$s/^/mysql -uroot -pwjcnrdb** redkite < /g' tablecreate.out > createtable.sh ## 테이블 생성 스크립트 작성

sh -x createtable.sh ## 테이블 생성 스크립트 수행

## 데이터 insert

ls -al *txt |awk '{print$9}' > datainsert.out

sed '1,$s/^/mysqlimport -uroot -pwjcnrdb** redkite --fields-terminated-by=',' /home/sysoper/redkite/ /g' datainsert.out > datainsert.sh -- 보완해야 함..에러발생

sh -x datainsert.sh

## 접속 후 데이터 확인 및 스토리지 확인

select * from TABLE_NAME; -- 테이블 데이터 확인

SHOW TABLE STATUS FROM `o2bank` WHERE ENGINE IS NOT NULL; -- 테이블 storage engine 확인

## 스토리지 옵션이 다를 경우 아래의 명령으로 수정

ALTER TABLE o2bank.ZIPCODE ENGINE = INNODB;

ALTER TABLE hwstock.MULTIBOARD ENGINE = INNODB;
ALTER TABLE hwstock.TOLPA01 ENGINE = INNODB;
ALTER TABLE hwstock.TOLPL01 ENGINE = INNODB;
ALTER TABLE hwstock.TOLPM01 ENGINE = INNODB;
ALTER TABLE hwstock.TOLPP01 ENGINE = INNODB;
ALTER TABLE hwstock.TOLXA01 ENGINE = INNODB;
ALTER TABLE hwstock.TOLXD01 ENGINE = INNODB;
ALTER TABLE hwstock.TOLXD02 ENGINE = INNODB;
ALTER TABLE hwstock.TOLXD03 ENGINE = INNODB;
ALTER TABLE hwstock.TOLXD04 ENGINE = INNODB;
ALTER TABLE hwstock.TOLXE01 ENGINE = INNODB;
ALTER TABLE hwstock.TOLXL01 ENGINE = INNODB;
ALTER TABLE hwstock.TOLXL02 ENGINE = INNODB;
ALTER TABLE hwstock.TOLXL03 ENGINE = INNODB;
ALTER TABLE hwstock.TOLXX01 ENGINE = INNODB;
ALTER TABLE hwstock.T_LOANRATE_INFO ENGINE = INNODB;
ALTER TABLE hwstock.USERINFO_MNG ENGINE = INNODB;
ALTER TABLE hwstock.ZIPCODE ENGINE = INNODB;
ALTER TABLE hwstock.zipcode_prime ENGINE = INNODB;

## OPTION

## DB 생성

create database redkite DEFAULT CHARACTER SET = euckr;

root암호변경설정

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

This is done with:

/usr/bin/mysqladmin -u root -p password 'new-password'

/usr/bin/mysqladmin -u root -h ns.dbakorea.pe.kr -p password 'new-password'

쉘에서는 mysql이 되는데 PHP에서 mysql.sock error를 내면서 MySQL이 안되는 경우

mysql.sock은 /tmp 아니면 /var/lib/mysql에 생기게 된다.

나의 경우, /var/lib/mysql에 mysql.sock파일이 있는데 PHP에서는 /tmp에서 찾으려하면서 에러를 발생했다.

/usr/bin/safe_mysqld파일에서 다음과 같이 수정한다.

주석(#)이 달린 것이 원래것이고 그 밑에 있는것이 수정한 것이다.

# MYSQL_UNIX_PORT=${MYSQL_UNIX_PORT:-/var/lib/mysql/mysql.sock}

MYSQL_UNIX_PORT=${MYSQL_UNIX_PORT:-/tmp/mysql.sock}

위와 같이 하니 /usr/bin/mysql이 /var/lib/mysql/mysql.sock에서 소켓파일을 찾으려 했다.

socket file을 지정하는 --socket이라는 옵션으로 다음과 같이 지정하면 된다.

mysql --socket=/tmp/mysql.sock -u dbakorea -p db_test

하지만 mysql실행시마다 이렇게 써줘야한다는 것이 상당히 귀찮다. 옵션이 바로 적용되게 설정하자.

mysql은 설정사항을 다음 3가지 파일에서 검색한다.

/etc/my.cnf global options(MySQL 전체적으로 사용되는 옵션 정의)

mysql-data-dir/my.cnf 특정 DB에 적용되는 option (/var/lib/mysql/my.cnf)

~/.my.cnf 사용자 각각의 설정('~'문자는 사용자의 홈디렉토리는 의미)

/usr/share/mysql디렉토리에 예제가 있으므로 참고한다.

소켓파일의 지정은 다음줄을 넣어주면 된다.

socket = /tmp/mysql.sock

== /etc/my.cnf예 ==

# The following options will be passed to all MySQL clients

[client]

#password = your_password

port = 3306

socket = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server

[mysqld]

port = 3306

socket = /tmp/mysql.sock

# root암호설정

% mysqladmin -u root password '변경암호' root로 로그인하여 해야함

or

% mysqladmin -u root -p기존암호 password '변경암호' root로 로그인하여 해야함

DB생성

mysql> create database DB명 ( or % mysqladmin -u root -p create DB명 )

DB삭제

mysql> drop database DB명

MySQL 연결

mysql -u 사용자 -p DB명 ( or % mysqladmin -u root -p drop DB명 )

데이터파일 실행(sql*loader기능)

mysql>load data local infile "데이터파일" into table 테이블명 ;

데이터파일에서 컬럼구분은 탭문자, Null값은 /n로 입력

질의 파일 실행

쉘프롬프트상에서

mysql -u 사용자 -p DB명 < 질의파일

or

mysql프롬프트상에서

mysql> source 질의파일

쉘프롬프트상에서 질의 실행

dbakorea@lion board]$ mysql mysql -u root -pxxxx -e

> "INSERT INTO db VALUES(

> 'localhost', 'aaa', 'aaa',

> 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y')"

사용자 생성 & 사용자에게 DB할당

shell> mysql --user=root -p mysql

mysql> INSERT INTO user VALUES('localhost','사용자',PASSWORD('비밀번호'),'Y','Y','Y','Y','Y','Y','Y',

'Y','Y','Y','Y','Y','Y','Y');

mysql> INSERT INTO user VALUES('%','사용자',PASSWORD('비밀번호'),'Y','Y','Y','Y','Y','Y','Y','Y','Y',

'Y','Y','Y','Y','Y');

mysql> INSERT INTO db(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv)

VALUES ('localhost','DB명','사용자','Y','Y','Y','Y','Y','Y');

mysql> INSERT INTO db(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv)

VALUES('%','DB명','사용자','Y','Y','Y','Y','Y','Y');

mysql> FLUSH PRIVILEGES; (or shell prompt: mysqladmin -u root -pxxxx reload)

DB, 테이블목록보기

mysql> show databases;

mysql> show tables;

사용 DB선택

mysql> use DB명

테이블구조

mysql> describe 테이블명

인덱스 보기

show index from 테이블명

테이블명 변경

mysql> rename table test to test2;

접속

mysql {-h 접속호스트} -u 사용자 -p 사용DB

-h로 다른 서버에 존재하는 MySQL접속시 다음과 같이 MySQL DB에 설정해줘야 한다.

mysql> INSERT INTO user VALUES('접근을 허용할 호스트ip','사용자',PASSWORD('비밀번호'),'Y','Y','Y',

'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

mysql> INSERT INTO db(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv)

VALUES('접근을 허용할 호스트ip','사용DB','사용자','Y','Y','Y','Y','Y','Y');

mysql> FLUSH PRIVILEGES; or 쉴프롬프트상에서 % mysqladmin -u root -p flush-privileges

버전체크

mysql> select version();