# wget http://ftp.gnu.org/gnu/m4/m4-1.4.16.tar.bz2 # tar xvfj m4-1.4.16.tar.bz2 # cd m4-1.4.16 # ./configure # make # make install
# wget http://ftp.gnu.org/gnu/bison/bison-2.5.tar.bz2 # tar xvfj bison-2.5.tar.bz2 # cd bison-2.5 # ./configure # make # make installMySQL 소스 컴파일시 sql_yacc.yy 으로 sql_yacc.cc 을 생성하는데, 이때 Bison이 필요하다. (솔라리스의 lex(1), yacc(1)은 안되는것으로 보인다) Bison과 M4를 설치하지 않아도 MySQL 5.5 를 설치할 수 있다. (sql_yacc.cc는 번들되어있다)
root@wl ~/src # wget "http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.20.tar.gz/from/http://ftp.iij.ad.jp/pub/db/mysql/" root@wl ~/src # tar xvfz mysql-5.5.20.tar.gz root@wl ~/src # cd mysql-5.5.20 root@wl ~/src/mysql-5.5.20 # cmake . \ -DDEFAULT_CHARSET=utf8 \ -DDEFAULT_COLLATION=utf8_general_ci \ -DWITH_READLINE=1 \ -DWITH_ZLIB=system 1) root@wl ~/src/mysql-5.5.20 # make 2) root@wl ~/src/mysql-5.5.20 # make test Running tests... Test project /root/src/mysql-5.5.20 Start 1: pfs_instr_class 1/12 Test #1: pfs_instr_class .................. Passed 0.01 sec Start 2: pfs_instr_class-oom 2/12 Test #2: pfs_instr_class-oom .............. Passed 0.01 sec Start 3: pfs_instr 3/12 Test #3: pfs_instr ........................ Passed 0.01 sec Start 4: pfs_instr-oom 4/12 Test #4: pfs_instr-oom .................... Passed 0.01 sec Start 5: pfs 5/12 Test #5: pfs .............................. Passed 0.61 sec Start 6: bitmap 6/12 Test #6: bitmap ........................... Passed 3.82 sec Start 7: base64 7/12 Test #7: base64 ........................... Passed 0.06 sec Start 8: my_vsnprintf 8/12 Test #8: my_vsnprintf ..................... Passed 0.01 sec Start 9: my_atomic 9/12 Test #9: my_atomic ........................ Passed 2.04 sec Start 10: my_rdtsc 10/12 Test #10: my_rdtsc ......................... Passed 0.14 sec Start 11: lf 11/12 Test #11: lf ............................... Passed 2.25 sec Start 12: my_malloc 12/12 Test #12: my_malloc ........................ Passed 0.01 sec 100% tests passed, 0 tests failed out of 12 Total Test time (real) = 8.99 sec root@wl ~/src/mysql-5.5.20 # make install root@wl ~/src/mysql-5.5.20 # vi /etc/profile ... LD_LIBRARY_PATH=/usr/local/mysql/lib:$LD_LIBRARY_PATH; export LD_LIBRARY_PATH1) MySQL Source-Configuration Options에 더 많은 옵션이 나와있다.
root@wl ~ # groupadd -g 201 dba root@wl ~ # useradd -d /export/home/mysql -g dba -m -u 202 -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 # ZFS 를 사용하는 경우 아래의 zfs 명령을 실행하자 root@wl /usr/local/mysql # zfs create rpool/mysqldata root@wl /usr/local/mysql # zfs set mountpoint=/usr/local/mysql/data rpool/mysqldata root@wl /usr/local/mysql # zfs set primarycache=metadata rpool/mysqldata root@wl /usr/local/mysql # zfs set recordsize=16k rpool/mysqldata root@wl /usr/local/mysql # scripts/mysql_install_db --user=mysql 4) root@wl /usr/local/mysql # chown -R mysql data root@wl /usr/local/mysql # support-files/mysql.server start 5) root@wl /usr/local/mysql # bin/mysql_secure_installation 6) NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MySQL to secure it, we'll need the current password for the root user. If you've just installed MySQL, and you haven't set the root password yet, the password will be blank, so you should just press enter here. Enter current password for root (enter for none): OK, successfully used password, moving on... Setting the root password ensures that nobody can log into the MySQL root user without the proper authorisation. Set root password? [Y/n] New password:패스워드입력 Re-enter new password:패스워드입력 Password updated successfully! Reloading privilege tables.. ... Success! By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? [Y/n] ... Success! Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? [Y/n] ... Success! By default, MySQL comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? [Y/n] - Dropping test database... ... Success! - Removing privileges on test database... ... Success! Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? [Y/n] ... Success! Cleaning up... All done! If you've completed all of the above steps, your MySQL installation should now be secure. Thanks for using MySQL! root@wl /usr/local/mysql #1) MySQL을 실행할 계정인 [mysql] 사용자를 생성한다. 패스워드를 지정해줄 필요는 없다.
root@wl /usr/local/mysql # support-files/mysql.server start
root@wl /usr/local/mysql # support-files/mysql.server stop
root@wl /usr/local/mysql/mysql-test # ./mysql-test-run실패해도 서버의 문제가 아니라, 테스트 슈트의 문제가 있을 수 있기 때문에 메시지를 보고 판단한다.
root@wl ~ # vi /etc/profile ... PATH=/usr/local/mysql/bin:$PATH; export PATH root@wl ~ # . /etc/profile
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.11-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 ~ #
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 계정으로 하면 된다.
root@wl ~ # mysql -u windy -p12345 windytest ... mysql> status -------------- ./mysql Ver 14.14 Distrib 5.5.11, for solaris10 (i386) using readline 5.1 ... 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(42) 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 | date | 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', sha1(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 | 43d0010fd46d062064a6e7b52adb16ba0cb0acbb | 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 sha1('123455xdx');
+------------------------------------------+
| sha1('123455xdx') |
+------------------------------------------+
| 43d0010fd46d062064a6e7b52adb16ba0cb0acbb |
+------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from authinfo where userid = 'windy' and pswd = sha1(concat('12345', salt));
+-----+--------+------------------------------------------+------+---------------------+---------------------+---------------------+
| seq | userid | pswd | salt | ldate | sdate | pdate |
+-----+--------+------------------------------------------+------+---------------------+---------------------+---------------------+
| 1 | windy | 43d0010fd46d062064a6e7b52adb16ba0cb0acbb | 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)
root@wl ~ # wget http://downloads.mysql.com/docs/sakila-db.tar.gz root@wl ~ # tar xvfz sakila-db.tar.gz root@wl ~ # cd sakila-db root@wl ~/sakila-db # mysql -u root mysql> source sakila-schema.sql mysql> source sakila-data.sql mysql> use sakila; mysql> show tables; +----------------------------+ | Tables_in_sakila | +----------------------------+ | actor | | actor_info | | address | | category | | city | | country | | customer | | customer_list | | film | | film_actor | | film_category | | film_list | | film_text | | inventory | | language | | nicer_but_slower_film_list | | payment | | rental | | sales_by_film_category | | sales_by_store | | staff | | staff_list | | store | +----------------------------+ 23 rows in set (0.00 sec)
root@wl ~ # wget http://downloads.mysql.com/docs/world_innodb.sql.gz
root@wl ~ # gunzip world_innodb.sql.gz
root@wl ~ # mysql -u root
mysql> create database world;
mysql> use world;
mysql> source world_innodb.sql
mysql> desc City;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> desc Country;
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Code | char(3) | NO | PRI | | |
| Name | char(52) | NO | | | |
| Continent | enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') | NO | | Asia | |
| Region | char(26) | NO | | | |
| SurfaceArea | float(10,2) | NO | | 0.00 | |
| IndepYear | smallint(6) | YES | | NULL | |
| Population | int(11) | NO | | 0 | |
| LifeExpectancy | float(3,1) | YES | | NULL | |
| GNP | float(10,2) | YES | | NULL | |
| GNPOld | float(10,2) | YES | | NULL | |
| LocalName | char(45) | NO | | | |
| GovernmentForm | char(45) | NO | | | |
| HeadOfState | char(60) | YES | | NULL | |
| Capital | int(11) | YES | | NULL | |
| Code2 | char(2) | NO | | | |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
15 rows in set (0.01 sec)
mysql> desc CountryLanguage;
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| CountryCode | char(3) | NO | PRI | | |
| Language | char(30) | NO | PRI | | |
| IsOfficial | enum('T','F') | NO | | F | |
| Percentage | float(4,1) | NO | | 0.0 | |
+-------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
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 | +-----------------------+--------+--------------------+---------+---------+
아래의 예제는 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-2012 Jo HoSeok. All rights reserved. |