mysql multiport

[설치 위치]
/usr/local/mysql

/etc/my.cnf
[mysqld]
user       = mysql
basedir    = /usr/local/mysql
log-error  = /var/log/mysqld.err
socket     = /var/run/mysqld/mysql.sock
pid-file   = /var/run/mysqld/mysql.pid

[mysqld_multi]
mysqld     = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin

[mysqld1]
socket     = /var/run/mysqld/mysql1.sock
port       = 3306
pid-file   = /var/run/mysqld/mysql1.pid
datadir    = /home/mysql
ledir      = /usr/local/mysql/bin

[mysqld2]
socket     = /var/run/mysqld/mysql2.sock
port       = 3307
pid-file   = /var/run/mysqld/mysql2.pid
datadir    = /home/mysql2
ledir      = /usr/local/mysql/bin

[mysqld3]
socket     = /var/run/mysqld/mysql3.sock
port       = 3308
pid-file   = /var/run/mysqld/mysql3.pid
datadir    = /home/mysql3
ledir      = /usr/local/mysql/bin

[데이터 디렉토리 생성]
# mkdir /home/mysql; mkdir /home/mysql2; mkdir /home/mysql3;
# chown -R mysql:mysql /home/mysql
# chown -R mysql:mysql /home/mysql2
# chown -R mysql:mysql /home/mysql3


[socket, pid 디렉토리 생성]
# mkdir /var/run/mysqld; chown -R mysql:mysql /var/run/mysqld


[데이터 DB 파일 생성]
# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/home/mysql
# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/home/mysql2
# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/home/mysql3


[데몬 실행]
mysqld1 ~ 3 모두 기동
# /usr/local/mysql/bin/mysqld_multi start

특정 포트만 기동|종료
# /usr/local/mysql/bin/mysqld_multi 번호 start|stop


[서버 접속]
# /usr/local/mysql/bin/mysql -u root -h 127.0.0.1 -P 3306
# /usr/local/mysql/bin/mysql -u root -h 127.0.0.1 -P 3307
# /usr/local/mysql/bin/mysql -u root -h 127.0.0.1 -P 3308

redis

 Redis 설치
 EPEL 저장소 필요

# yum install redis

 설정파일
# vi /etc/redis.conf
#replication setting
#slaveof 마스터IP 마스터Port
slaveof 192.168.1.1 6379

#bind 127.0.0.1 (주석)

webserver handlersocket

CentOS 64bit

EPEL 저장소 설치
# rpm --import http://dl.fedoraproject.org/pub/epel/RPM-GPG-KEY-EPEL-5
# rpm -ivh http://dl.fedoraproject.org/pub/epel/5/x86_64/epel-release-5-4.noarch.rpm

REMI 저장소 설치
# rpm --import http://rpms.famillecollet.com/RPM-GPG-KEY-remi
# rpm -ivh http://rpms.famillecollet.com/enterprise/5/remi/x86_64/remi-release-5-8.el5.remi.noarch.rpm

RPMFORGE
# rpm -ivh http://apt.sw.be/redhat/el5/en/x86_64/rpmforge/RPMS/rpmforge-release-0.5.2-2.el5.rf.x86_64.rpm


[apache php 설치]
# yum --enablerepo=remi install httpd php php-common php-mbstring php-devel php-pear php-mysqlnd re2c zlib-devel

[ memcached 설치]
# yum --enablerepo=remi install memcached

[handler socket php lib 설치]
http://code.google.com/p/php-handlersocket/
wget http://code.google.com/p/php-handlersocket/downloads/detail?name=php-handelrsocket-0.3.1.tar.gz
# phpize
# ./configure --disable-handlersocket-hsclient
# make;make install
# echo "extension=handlersocket.so" > /etc/php.d/handlersocket.ini

webgrind xdebug

[webgrind]
http://code.google.com/p/webgrind/

PHP에 xdebug 모듈 설치

# pecl install xdebug

/etc/php.d/xdebug.ini
zend_extension=/usr/lib64/php/modules/xdebug.so
xdebug.default_enable = On
xdebug.show_exception_trace = On
xdebug.show_local_vars = 1
xdebug.max_nesting_level = 50
xdebug.var_display_max_depth = 6
xdebug.dump_once = On
xdebug.dump_globals = On
xdebug.dump_undefined = On
xdebug.dump.REQUEST = *
xdebug.dump.SERVER = REQUEST_METHOD,REQUEST_URI,HTTP_USER_AGENT,scRIPT_NAME
xdebug.remote_enable=true
xdebug.remote_host=127.0.0.1
xdebug.remote_port=9000
xdebug.remote_handler=dbgp
;모든 파일에 대하여 자동으로 프로파일링할때
xdebug.profiler_enable = 1
; 특정파일만 프로파일링 하고 싶을때 XDEBUG_PROFILE 을 get으로 실행한다.
; ex : http://example.com/sample.php?XDEBUG_PROFILE
xdebug.profiler_enable_trigger = 1
xdebug.profiler_output_dir =""/tmp""
;cachegrind 포멧으로 설정
xdebug.profiler_output_name = cachegrind.out.%t.%p
xdebug.var_display_max_data = 2048

mysql - spider engines

URL http://spiderformysql.com
참고 http://ryooo321.blogspot.kr/2012/05/macmysql5514spiderhandlersocket.html
바이너리 설치 /usr/local/mysql 에 압축 해제

[기존 환경설정파일 제거]
mv /etc/my.cnf /etc/my.cnf.backup

[데몬 (경로)설정]
# vi /usr/local/mysql/support-files/mysql.server

basedir=/usr/local/mysql
datadir=/home/mysql


[데이터 디렉토리 생성]
# mkdir /home/mysql;mkdir /var/run/mysqld;

[기본DB설치]
# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/home/mysql

[DB 디렉토리에 권한설정]
# chown -R mysql:mysql /home/mysql;chown -R mysql:mysql /var/run/mysqld

[데몬 시작]
# /usr/local/mysql/support-files/mysql.server start

[엔진 설치]

spider 엔진 설치
# /usr/local/mysql/bin/mysql -uroot < /usr/local/mysql/share/install_spider.sql
Handlersocket Plugin 설치
# /usr/local/mysql/bin/mysql -uroot < /usr/local/mysql/share/install_handlersocket.sql

[데몬 내리기]
/usr/local/mysql/support-files/mysql.server stop

[환경설정 추가]
vi /etc/my.cnf
spider_internal_xa=1
spider_semi_trx_isolation=2
spider_use_handler=1
spider_use_hs_read=1
spider_use_hs_write=1

[데몬 시작]
/usr/local/mysql/support-files/mysql.server start

[MySQL INNODB 테이블 생성]
1번서버 192.168.0.101
2번서버 192.168.0.102
위 서버에 각각 테이블 생성
CREATE DATABASE DATA;
CREATE TABLE `spider` (
  `id` INT(10) UNSIGNED NOT NULL,
  `val` VARCHAR(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;"

[SPIDER 엔진이 설치된 MySQL]
192.168.0.201
SPIDER엔진이 설치된 DB에서 계정 생성 후 테이블을 만들어 준다
SPIDER로 해시할 원격지 계정 정보 설정
CREATE SERVER n1 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'spider', PASSWORD 'spider', HOST '192.168.0.101', PORT 3306);
CREATE SERVER n2 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'spider', PASSWORD 'spider', HOST '192.168.0.101', PORT 3306);"

[SPIDER 엔진이 설치된 MySQL에 테이블 생성]
CREATE DATABASE DATA;
CREATE TABLE `spider` (
  `id` INT(10) UNSIGNED NOT NULL,
  `val` VARCHAR(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=SPIDER DEFAULT CHARSET=utf8
PARTITION BY HASH(`id`) (
  PARTITION p1 COMMENT 'user "spider", password "spider",  host "127.0.0.1", port "3306", table "spider",',
  PARTITION p2 COMMENT 'user "spider", password "spider",  host "127.0.0.1", port "3306", table "spider",'
);