Linux에 MySql 설치하기(Multi)

Linux에 자동 installer로 Mysql 설치가 안되어서

수동으로 진행된다.

아래에서 숫자만 제거 혹은 변경하면 mulit 설치 가능하다.

기본정보	설치파일 위치	/usr/local/src
	설치위치	/home/mysql3
	데이터 위치	/block/mysql3/data
	로그 위치	/block/mysql3/log
1	다운로드/업로드	MySQL - 최신버전 : 5.6.17 - 다운로드 페이지 : http://dev.mysql.com/downloads/mysql/#downloads - Select Platform 의 콤보박스에서 Source Code 를 선택하여 가장 마지막 항목을 다운로드 한다. (Generic Linux (Architecture Independent), Compressed TAR Archive)
		filezilla 이용하여 ftp로 업로드 (/usr/local/src)
2	압축해제	# tar zxvf mysql-5.6.17.tar.gz
3	디렉토리생성	# mkdir /home/mysql3
		# mkdir /home/mysql3/conf
		# mkdir /block/mysql3
		# mkdir /block/mysql3/data
		# mkdir /block/mysql3/log
		# mkdir /block/mysql3/log/glog
		# mkdir /block/mysql3/log/blog
		# mkdir /block/mysql3/tmpdir
4	계정생성	# groupadd mysql3
		# useradd -g mysql3 mysql3
5	권한변경	# chown -R mysql3.mysql3 /home/mysql3
		# chown -R mysql3.mysql3 /home/mysql3/conf
		# chown -R mysql3.mysql3 /block/mysql3
		# chown -R mysql3.mysql3 /block/mysql3/data
		# chown -R mysql3.mysql3 /block/mysql3/log
		# chown -R mysql3.mysql3 /block/mysql3/log/glog
		# chown -R mysql3.mysql3 /block/mysql3/log/blog
		# chown -R mysql3.mysql3 /block/mysql3/tmpdir
6	의존패키지 설치
	CD롬으로 부터 설치	/media 에 필요한 파일들 CD롬으로 마운트 요청(서버클라우드)
		# vi /etc/yum.repos.d/DVD.repo (아래내용 확인) [DVD] name=Cent baseurl=file:///media enabled=1 gpgcheck=0
		# yum install --enablerepo=DVD wget
		# yum install --enablerepo=DVD cmake
		# yum install --enablerepo=DVD ncurses-devel
		# yum install --enablerepo=DVD libtool-ltdl
		# yum install --enablerepo=DVD expat-devel
		# yum install --enablerepo=DVD db4-devel
		# yum install --enablerepo=DVD pcre-devel
		# yum install --enablerepo=DVD openssl-devel
		# yum groupinstall --enablerepo=DVD "Development Tools"
7	설치폴더 이동	# cd  /usr/local/src/mysql-5.6.17
		cmake  -DCMAKE_INSTALL_PREFIX=/home/mysql3  -DMYSQL_UNIX_ADDR=/home/mysql3/conf/mysql3.sock  -DWITH_TCP_PORT=3308  -DDEFAULT_CHARSET=utf8  -DDEFAULT_COLLATION=utf8_general_ci  -DWITH_EXTRA_CHARSETS=all  -DMYSQL_DATADIR=/block/mysql3/data  -DSYSCONFDIR=/home/mysql3/conf  -DENABLED_LOCAL_INFILE=1  -DWITH_INNOBASE_STORAGE_ENGINE=1  -DWITH_ARCHIVE_STORAGE_ENGINE=1  -DWITH_BLACKHOLE_STORAGE_ENGINE=1  -DWITH_MYISAM_STORAGE_ENGINE=1  -DWITH_MEMORY_STORAGE_ENGINE=1
		# cmake \
	//MySQL 디렉토리 지정	-DCMAKE_INSTALL_PREFIX=/home/mysql3 \
	//소켓 디렉토리 지정	-DMYSQL_UNIX_ADDR=/home/mysql3/conf/mysql3.sock \
	// tcp Port	-DWITH_TCP_PORT=3308 \
	//문서의 기본 언어셋	-DDEFAULT_CHARSET=utf8 \
	//DB의 언어셋	-DDEFAULT_COLLATION=utf8_general_ci \
	//name 추가로 지정할 언어로 기본값은 all	-DWITH_EXTRA_CHARSETS=all \
	//DB(data) 디렉토리 지정	-DMYSQL_DATADIR=/block/mysql3/data \
		-DSYSCONFDIR=/home/mysql3/conf \
		-DENABLED_LOCAL_INFILE=1 \
		-DWITH_INNOBASE_STORAGE_ENGINE=1 \
		-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
		-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
		-DWITH_MYISAM_STORAGE_ENGINE=1 \
		-DWITH_MEMORY_STORAGE_ENGINE=1
8	컴파일 및 설치	# make
		# make install
9	my.cnf 파일 생성	# vi /home/mysql3/conf/my.cnf ################################################################################ # IOffice Development DB(Master) # DATE: 2014-10-15 # DESCRIPTION: MySQL 5.6 # Server Spec (OS,CPU,Mem,OS Storage,Block Storage) : S-CentOS_6.0_64bit 8Core 64GB 50G 200GB # Max-Connections: 2000 ################################################################################ [client] port = 3308 socket = /home/mysql3/conf/mysql3.sock [mysqld] datadir = /block/mysql3/data/ basedir = /home/mysql3 user = mysql3 port = 3308 socket = /home/mysql3/conf/mysql3.sock pid-file = /home/mysql3/conf/mysqld.pid #default-time-zone = 'UTC' # mysql start 이후 재설정 back_log = 300 skip_external_locking # Query Cache query_cache_limit = 0 query_cache_size = 0 query_cache_type = 0 ## Logging log_error=/block/mysql3/log/glog/error.log log-warnings = 1 log-bin=/block/mysql3/log/blog/mysql-bin log-output = FILE general_log = 0 general_log_file = /block/mysql3/log/glog/query.log slow_query_log = 0 slow_query_log_file = /block/mysql3/log/glog/slowquery.log #log_queries_not_using_indexes long_query_time = 10 max_binlog_size = 1G expire_logs_days = 7 ## Replication #binlog_format = MIXED #server-id = 1 ## Per-Thread Buffers * (max_connections) = total per-thread mem usage thread_stack = 512K sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 2M join_buffer_size = 2M binlog_cache_size = 128K ## total per-thread buffer memory usage: 17664000K = 17.250GB ## Connections max_connections = 2000 max_connect_errors = 100 max_allowed_packet = 32M ## Default Table Settings # sql_mode = ## Table and TMP settings max_heap_table_size = 1G bulk_insert_buffer_size = 1G tmp_table_size = 1G tmpdir = /block/mysql3/tmpdir ## Table cache settings table_open_cache = 2000 ## Thread settings thread_concurrency = 16 thread_cache_size = 100 ## InnoDB innodb_adaptive_hash_index = 1 ##### innodb_additional_mem_pool_size = 8M innodb_buffer_pool_size = 32G innodb_data_home_dir = /block/mysql3/data #innodb_data_file_path = ibdata1:128M;ibdata2:10M:autoextend # Check !!!!!! innodb_doublewrite = 1 innodb_file_per_table = 1 innodb_flush_log_at_trx_commit = 1 innodb_io_capacity = 400 innodb_lock_wait_timeout = 50 innodb_log_buffer_size = 128M innodb_log_file_size = 768M innodb_log_files_in_group = 4 innodb_log_group_home_dir = /block/mysql3/log/blog innodb_read_io_threads = 16 innodb_thread_concurrency = 16 innodb_write_io_threads = 16 ## MyISAM Engine key_buffer_size = 8M ## Binlog sync settings sync_binlog = 0 ## Time out wait_timeout = 1800 interactive_timeout = 1800 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 ## Per-Thread Buffer memory utilization equation: #(read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + binlog_cache_size) * max_connections # = 17.250G ## Global Buffer memory utilization equation: # innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + key_buffer_size + query_cache_size # = 32912M = 32.14G [mysqldump] #quick #max_allowed_packet = 128M
10	기본DB 생성	# cd /home/mysql3
		# ./scripts/mysql_install_db --defaults-file=/home/mysql3/conf/my.cnf --basedir=/home/mysql3 --datadir=/block/mysql3/data --ldata=/block/mysql3/data --user=mysql3
	기존 my.cnf 파일들 삭제	# rm /home/mysql3/my-new.cnf # rm /home/mysql3/my.cnf # rm /etc/my.cnf
11	PATH 추가 및 서비스 등록	# vi ~/.bash_profile export MYSQL_HOME=/home/mysql export MYSQL2_HOME=/home/mysql2 export MYSQL3_HOME=/home/mysql3 PATH=$PATH:$HOME/bin:$MYSQL_HOME/bin export PATH
	바로적용	# source ~/.bash_profile
13	서비스 설정	# cd $MYSQL3_HOME
		# cp support-files/mysql.server /etc/init.d/mysqld3
		# chmod 755 /etc/init.d/mysqld3
		# vi /etc/init.d/mysqld3 #basedir= #datadir= #mysqld_pid_file_path=
14	서비스 시작	# service mysqld3 start
15	TIME Zone 설정(UTC)
	Time Zone 확인	mysql> select @@global.time_zone, @@session.time_zone;
	설치된 Time Zone 확인	mysql> select b.name, a.time_zone_id  from mysql.time_zone a, mysql.time_zone_name b where a.time_zone_id = b.time_zone_id and b.name like '%UTC%';
	Time Zone 정보 Import from OS	shell> mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
		#my.cnf 수정 default-time-zone='UTC'
		#service mysqld restart
		mysql> SELECT @@global.time_zone, @@session.time_zone;
16	스키마 생성
	root 계정 비밀번호 변경	shell> mysqladmin -u root password tlzbflwl11
	관리계정	mysql> grant all on *.* TO 'efss_167'@'%'  identified by 'sec167' with grant option;
	관리계정	mysql> grant all on *.* TO 'efss_167'@localhost  identified by 'sec167' with grant option;
	DB 생성	mysql> create database securagedb;
	계정 생성 및 권한 부여(내부접속) : 개발자 계정	mysql> grant select,insert,update,delete,alter,create view,show view on securagedb.* to efss01@localhost identified by 'efss1234';
	계정 생성 및 권한 부여(외부접속) : 개발자 계정	mysql> grant select,insert,update,delete,alter,create view,show view on securagedb.* to efss01@'%'  identified by 'efss1234';
	검색엔진용 Database/계정	mysql> create database securage_searchdb;
		mysql> grant all on securage_searchdb.* to efss02@localhost identified by 'efss1234';
		mysql> grant all on securage_searchdb.* to efss02@'%' identified by 'efss1234';