MySQL 5.5 - 윈디하나의 솔라나라
- 분류: 오라클
- 설명: MySQL 5.5 설치및 실행, 파티셔닝, 리플리케이션 (2011-01-31)
- 이 문서는 윈디하나의 솔라나라: MySQL 5.5 에서 최근에 갱신된 문서를 찾을 수 있다.
- 윈디하나의 솔라나라 트위터 @solanara
목차
개요
- MySQL의 설치와 기본적인 운용에 대해 다룬다.
- MySQL 5.x 부터 소스설치시 GNU AutoTools를 사용한 설치에서 CMake 를 사용한 설치로 변경되었다. 따라서 CMake설치가 필요하다.
- MySQL NDB Cluster에 대해서는 윈디하나의 솔라나라: MySQL NDB Cluster를 참고하자.
- How to Build MySQL server with CMake
- Installing MySQL from a Standard Source Distribution
- MySQL Source-Configuration Options
설치
- 윈디하나의 솔라나라: CMake: MySQL 5.5 부터 CMake 를 사용해 빌드하도록 변경되었다. CMake 를 설치한다.
- GNU m4 - GNU macro processor
root@wl ~/src # wget http://ftp.gnu.org/gnu/m4/m4-1.4.15.tar.bz2 root@wl ~/src # tar xvfj m4-1.4.15.tar.bz2 root@wl ~/src # cd m4-1.4.15 root@wl ~/src/m4-1.4.15 # make root@wl ~/src/m4-1.4.15 # make install
- Bison - GNU parser generator sql_yacc.yy 을 sql_yacc.cc 으로 생성하는데 필요. GNU M4가 필요.
wget http://ftp.gnu.org/gnu/bison/bison-2.4.3.tar.bz2 tar xvfj bison-2.4.3.tar.bz2 root@wl ~/src # cd bison-2.4.3 root@wl ~/src/bison-2.4.3 # ./configure root@wl ~/src/bison-2.4.3 # make root@wl ~/src/bison-2.4.3 # make install
- 설치
root@wl ~/src # wget "http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.9.tar.gz/from/http://ftp.iij.ad.jp/pub/db/mysql/" root@wl ~/src # tar xvfz mysql-5.5.9.tar.gz root@wl ~/src # cd mysql-5.5.9 root@wl ~/src/mysql-5.5.9 # vi config.h.cmake 1) /* Character sets and collations */ #cmakedefine MYSQL_DEFAULT_CHARSET_NAME "@MYSQL_DEFAULT_CHARSET_NAME@" #cmakedefine MYSQL_DEFAULT_COLLATION_NAME "@MYSQL_DEFAULT_COLLATION_NAME@" root@wl ~/src/mysql-5.5.9 # vi cmake/character_sets.cmake 1) IF(NOT DEFAULT_COLLATION) root@wl ~/src/mysql-5.5.9 # cmake . \ -DDEFAULT_CHARSET=utf8 \ -DDEFAULT_COLLATION=utf8_general_ci \ -DWITH_READLINE=1 \ -DWITH_SSL=yes \ -DOPENSSL_ROOT_DIR=/usr/local/ssl \ -DOPENSSL_CRYPTO_LIBRARIES=/usr/local/ssl/lib/libcrypto.so \ -DOPENSSL_SSL_LIBRARIES=/usr/local/ssl/lib/libssl.so 2) root@wl ~/src/mysql-5.5.9 # make root@wl ~/src/mysql-5.5.9 # cd /root/src/mysql-5.5.9/mysys && /usr/bin/cc \ -DHAVE_RWLOCK_T -DHAVE_CONFIG_H -g -DDBUG_OFF \ -I/root/src/mysql-5.5.9/include -I/usr/local/include \ -I/root/src/mysql-5.5.9/mysys -KPIC \ /root/src/mysql-5.5.9/mysys/my_timer_cycles.il -o CMakeFiles/mysys.dir/my_rdtsc.c.o \ -c /root/src/mysql-5.5.9/mysys/my_rdtsc.c 3) root@wl ~/src/mysql-5.5.9/mysys # cd .. root@wl ~/src/mysql-5.5.9 # make root@wl ~/src/mysql-5.5.9 # make install
1) MYSQL_DEFAULT_CHARSET_NAME, MYSQL_DEFAULT_COLLATION_NAME 라인을 이와같이 바꾸고, IF(NOT DEFAULT_COLLATIONS) 로 되어있는 부분에서 마지막 'S'를 삭제한다.
2) MySQL Source-Configuration Options
3) 컴파일 시 오류가 나는데, 출력된 명령문에서 -xO2 를 삭제하고 다시 컴파일한다.
설정
-
MySQL 설정
root@wl ~ # useradd -d /export/home/mysql -g staff -m -u 102 -s /bin/false mysql 1) root@wl ~ # cp /usr/local/mysql/support-files/my-medium.cnf /etc/my.cnf 2) root@wl ~ # vi /etc/my.cnf 3) skip-networking root@wl ~ # cd /usr/local/mysql root@wl /usr/local/mysql # scripts/mysql_install_db --user=mysql 4) root@wl /usr/local/mysql # support-files/mysql.server start 5) root@wl /usr/local/mysql # bin/mysql_secure_installation 6)
1) MySQL을 실행할 유닉스 계정을 생성한다.
2) MySQL 설정 파일을 복사한 후 적당히 수정한다. /usr/local/mysql/share/mysql 에 my-innodb-heavy-4G.cnf, my-huge.cnf, my-large.cnf, my-medium.cnf, my-small.cnf 의 예제 설정 파일이 있다. 이를 /etc/my.cnf 으로 이름을 바꿔 복사하면 된다. 리플리케이션을 사용하지 않는다면 [log-bin]을 찾아 주석처리하고 외부에서 접속이 없다면 [skip-networking]의 주석을 푼다.
3) 설정파일을 수정한다.
4) 기본 MySQL DB를 생성한다.
5) MySQL 서버를 시작한다. 종료는 [support-files/mysql.server stop] 커맨드를 사용한다.
6) MySQL의 root 패스워드를 변경하고 몇가지 보안 설정을 한다. 그냥 엔터만 치면 된다. -
MySQL 실행
root@wl /usr/local/mysql # support-files/mysql.server start
-
MySQL 종료
root@wl /usr/local/mysql # support-files/mysql.server stop
사용
-
데이터베이스 생성
서버에 root 계정으로 접속해 서버 상태를 확인하고, windytest 라는 데이터베이스를 생성한다.root@wl ~ # mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.9-log Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> status ... Threads: 1 Questions: 4 Slow queries: 0 Opens: 33 Flush tables: 1 Open tables: 26 Queries per second avg: 0.97 mysql> create database windytest; Query OK, 1 row affected (0.00 sec) mysql> exit Bye root@wl ~ #
-
사용자추가
- windy 라는 사용자를 추가하고, 패스워드는 12345 로 정한다.
- windy 에게 windytest 데이터베이스에 대한 모든 권한을 부여한다.
root@wl ~ # mysql -u root -p Enter password: ... mysql> CREATE USER windy IDENTIFIED BY '12345'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL PRIVILEGES ON windytest.* TO 'windy'@'%' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye root@wl ~ #
이후의 모든 MySQL 작업은 root 계정이 아닌 windy 계정으로 하면 된다. -
테이블 생성
windy 사용자로 접속해 테이블을 생성한다.root@wl ~ # mysql -u windy -p12345 windytest ... mysql> status -------------- ... Current database: windytest Current user: windy@localhost ... Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 ... -------------- mysql> create table if not exists authinfo ( seq integer unsigned auto_increment, constraint authinfo_seq_pk primary key (seq), userid varchar(255) not null, constraint authinfo_userid_uq unique(userid), pswd char(32) not null, salt char(4) not null, ldate datetime not null, sdate date not null, pdate datetime not null ); Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show tables; +---------------------+ | Tables_in_windytest | +---------------------+ | authinfo | +---------------------+ 1 row in set (0.01 sec) mysql> desc authinfo; +--------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+------------------+------+-----+---------+----------------+ | seq | int(10) unsigned | NO | PRI | NULL | auto_increment | | userid | varchar(255) | NO | UNI | NULL | | | pswd | char(32) | NO | | NULL | | | salt | char(4) | NO | | NULL | | | ldate | datetime | NO | | NULL | | | sdate | datetime | NO | | NULL | | | pdate | datetime | NO | | NULL | | +--------+------------------+------+-----+---------+----------------+ 7 rows in set (0.00 sec) mysql> exit Bye root@wl ~ #
-
삽입, 조회, 삭제
mysql> insert into authinfo(userid, pswd, salt, ldate, sdate, pdate) values ('windy', md5(concat('12345', '5xdx')), '5xdx', now(), now(), now()); Query OK, 1 row affected, 1 warning (0.03 sec) mysql> select * from authinfo; +-----+--------+----------------------------------+------+---------------------+---------------------+---------------------+ | seq | userid | pswd | salt | ldate | sdate | pdate | +-----+--------+----------------------------------+------+---------------------+---------------------+---------------------+ | 1 | windy | 233dbb2329ad900d8bf5819376b0d29c | 5xdx | 2010-12-29 20:00:00 | 2010-12-29 20:00:00 | 2010-12-29 20:00:00 | +-----+--------+----------------------------------+------+---------------------+---------------------+---------------------+ 1 row in set (0.00 sec) mysql> select md5('123455xdx'); +----------------------------------+ | md5('123455xdx') | +----------------------------------+ | 233dbb2329ad900d8bf5819376b0d29c | +----------------------------------+ 1 row in set (0.00 sec) mysql>
파티셔닝
-
개요
SQL표준은 데이터를 어떻게 저장할 것인지는 규정하지 않았다. 그렇기 때문에 데이터를 어떻게 저장할 것인가는, DBMS에 달려 있었다. MySQL의 주 저장 엔진인 MyISAM과 InnoDB에 대해 살펴보면, MyISAM은 각각의 테이블을 OS의 파일로써 저장했고, InnoDB는 OS의 파일 시스템에 하나의 큰 파일(Table Space)을 생성하고 이 파일 안에 복수개의 테이블을 저장했다.
두 저장 엔진의 공통점 또는 제약 사항은 하나의 테이블은 OS안의 하나의 파일에 저장된다는 점이다. 이를 극복하기 위해 파티셔닝이 나왔다. 파티셔닝이란, 하나의 테이블 데이터를 '두개 이상의 파일'에 저장하는 것을 의미한다. 그 '두개 이상의 파일'이 위치하는 마운트 지점이 다르거나 물리적으로 디스크가 다르다면 성능향상을 괴할 수 있으며, 하드디스크보다 큰 테이블을 저장할 수 있을 것이다.
이런 개념으로 시작된 기술이 '파티셔닝(Partitioning)'이다. 현재는 여러가지 용도로 사용된다.
-
설정
파티셔닝을 위해 특별히 설정해야할 사항은 없다. MySQL에서 파티셔닝 지원은 아래와 같이 확인할 수 있다.
mysql> SHOW VARIABLES LIKE '%partition%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | have_partitioning | YES | +-------------------+-------+ 1 row in set (0.00 sec) mysql> SHOW PLUGINS; +-----------------------+--------+--------------------+---------+---------+ | Name | Status | Type | Library | License | +-----------------------+--------+--------------------+---------+---------+ ... +-----------------------+--------+--------------------+---------+---------+ | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | +-----------------------+--------+--------------------+---------+---------+
-
종류
파티셔닝을 하려면, 데이터를 어떠한 기준으로 나눌 것인지 결정해야 한다. MySQL에서 제공해주는 파티셔닝 방법은 아래와 같다. 자세한 사항은 MySQL 5.5 Reference Manual :: 18 Partitioning :: 18.2 Partitioning Types을 참조하자.- RANGE 파티셔닝: 주어진 컬럼이 가진 '값의 범위'에 따라 파티셔닝한다. 가장 기본적인 파티셔닝 방법이다.
- LIST 파티셔닝: RANGE와 비슷하지만, '값의 범위'가 아닌 '값'을 가지고 파티셔닝 한다.
- COLUMNS 파티셔닝: RANGE, LIST 파티셔닝의 MySQL 5.5 확장이다. 두개 이상의 컬럼및 다양한 데이터타입에 대해 파티셔닝 가능하게 해준다.
- HASH 파티셔닝: '해시 표현식'을 사용해 생성한 해시값을 통해 파티셔닝. 선형 해시인 LINEAR HASH 확장도 가능하다.
- KEY 파티셔닝: HASH와 비슷하다. 단 표현식이 아닌 MySQL에서 제공하는 해시 함수를 사용한다는 점이 다르다. 선형 키인 LINEAR KEY 확장도 가능하다.
-
사용
아래의 예제는 visitlog 라는 로그 저장 테이블을 파티셔닝하는 예제다. 2000년도 이하 데이터는 p0 파티션에, 2010년도 이하 데이터는 p1 파티션, 2020년도 이하 데이터는 p2파티션에 저장될 것이다. 그외의 데이터는 p3에 저장된다.
이렇게 나누면 2000년과 2010년을 걸쳐서 조회하는 쿼리는, 파티셔닝 하지 않은 경우보다 속도가 느려진다. 운영 정책상 그런 쿼리를 사용하지 않도록 해야한다.
mysql> CREATE TABLE visitlog ( seq INT, loc VARCHAR(255) NOT NULL, rdate DATETIME NOT NULL ); Query OK, 0 rows affected (0.01 sec) mysql> ALTER TABLE visitlog PARTITION BY RANGE(YEAR(rdate)) ( PARTITION p0 VALUES LESS THAN (2000), PARTITION p1 VALUES LESS THAN (2010), PARTITION p2 VALUES LESS THAN (2020), PARTITION p3 VALUES LESS THAN MAXVALUE ); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert into visitlog values (1, 'Solanara Root', '2000-01-01 00:00:00'); Query OK, 1 row affected (0.00 sec) mysql> insert into visitlog values (2, 'Manual', '2010-01-01 00:00:00'); Query OK, 1 row affected (0.00 sec) mysql> insert into visitlog values (3, 'For Test', '2020-01-01 00:00:00'); Query OK, 1 row affected (0.00 sec) mysql> EXPLAIN PARTITIONS SELECT * FROM visitlog WHERE rdate < '2010-01-01'; +----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | visitlog | p0,p1 | ALL | NULL | NULL | NULL | NULL | 2 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> ALTER TABLE visitlog REMOVE PARTITIONING; Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> EXPLAIN PARTITIONS SELECT * FROM visitlog WHERE rdate < '2010-01-01'; +----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | visitlog | NULL | ALL | NULL | NULL | NULL | NULL | 3 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
     |
Copyright © 2004-2011 Jo HoSeok. All rights reserved. |
