Centos에서 MySQL을 사용하려니,, 걸리는게 많다. 속성 부터 권한. 폴더 구조까지..
필요한 부분은 설정하기 위해서,
Source code로 build 하는 방법을 알아보자
0. OS 계정 추가
(root 계정으로 설치 준비하고, mysql 계정으로 DB 구동)
# adduser mysql
세션 Limit 설정
# vi /etc/security/limits.conf
# 하단에 내용추가
mysql soft nproc 8192 mysql hard nproc 16384 mysql soft nofile 8192 mysql hard nofile 65536 |
1. MySql 설치 (Site build)
1) download
http://dev.mysql.com/downloads/mysql/ 접속
looking for previous GA version? 선택
version 선택
select Platform : " Source Code" 선택
Generic Linux (Architecture Independent), Compressed TAR Archive
항목 선택 download
2) 설치
# tar -zxvf mysql-x.x.x.tar.gz
# adduser mysql
( su - mysql )
# mkdir /home/mysql/mysql-5.6.31
# mkdir /home/mysql/mysql-5.6.31/conf
# mkdir /home/mysql/mysql-5.6.31/data
# mkdir /home/mysql/mysql-5.6.31/log
# mkdir /home/mysql/mysql_-5.6.31/log/glog
# mkdir /home/mysql/mysql-5.6.31/log/blog
# mkdir /home/mysql/mysql-5.6.31/tmpdir
# chown -R mysql.mysql /home/mysql/mysql-5.6.31
# yum install cmake ncurses-devel ncurses-devel libtool-ltdl expat-devel db4-devel pcre-devel openssl-devel
# cd ./mysql-x.x.x/
아래 내용 실행
# cmake -DCMAKE_INSTALL_PREFIX=/home/mysql/mysql-5.6.31 -DMYSQL_UNIX_ADDR=/home/mysql/mysql-5.6.31/conf/mysql.sock -DWITH_TCP_PORT=3306 -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DMYSQL_DATADIR=/home/mysql/mysql-5.6.31/data -DSYSCONFDIR=/home/mysql/mysql-5.6.31/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 |
# make
# make install
3) Configure file 설정
# vi /home/mysql/mysql-5.6.31/conf/my.cnf
################################################################################ # Development DB(Master) # DATE: 2016-06-18 # DESCRIPTION: MySQL 5.6.6.31 # Server Spec (OS,CPU,Mem,OS Storage,Block Storage) : S-CentOS_6.7_64bit 4Core # Max-Connections: 2000 ################################################################################ [client] port = 3306 socket = /home/mysql/mysql-5.6.31/conf/mysql.sock [mysqld] datadir = /home/mysql/mysql-5.6.31/data/ basedir = /home/mysql/mysql-5.6.31 user = mysql port = 3306 socket = /home/mysql/mysql-5.6.31/conf/mysql.sock pid-file = /home/mysql/mysql-5.6.31/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=/home/mysql/mysql-5.6.31/log/glog/error.log log-warnings = 1 log-bin=/home/mysql/mysql-5.6.31/log/blog/mysql-bin log-output = FILE general_log = 0 general_log_file = /home/mysql/mysql-5.6.31/log/glog/query.log slow_query_log = 0 slow_query_log_file = /home/mysql/mysql-5.6.31/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 = /home/mysql/mysql-5.6.31/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 = /home/mysql/mysql-5.6.31/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 = /home/mysql/mysql-5.6.31/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 |
4) 기본 DB 생성
# cd /home/mysql/mysql-5.6.31/
# ./scripts/mysql_install_db --defaults-file=/home/mysql/mysql-5.6.31/conf/my.cnf --basedir=/home/mysql/mysql-5.6.31 --datadir=/home/mysql/mysql-5.6.31/data --ldata=/home/mysql/mysql-5.6.31/data --user=mysql |
※ 중요 !!
기본 DB 생성전에 mysqld start를 실행하면, 모든 설정이 기본(default)로 설정되고,
재 변경이 안된다. (my.cnf 설정 안됨!! 본인은 이 때문에, 다 지우고 다시 시작함)
따라서, mysql_install_db 실행 후 5), 6) 실행하고, mysqld 실행..
5) 기존 my.cnf 삭제
# rm /home/mysql/mysql-5.6.31/my-new.cnf
6) 서비스 설정
# cd /home/mysql/mysql-5.6.31
# cp support-files/mysql.server /etc/init.d/mysqld
# chmod 755 /etc/init.d/mysqld
# vi /etc/init.d/mysqld
아래내용 설치경로 설정
basedir= datadir= mysqld_pid_file_path= |
# service mysqld start
2. DB 스키마 생성 및 권한 설정
1) root 비밀번호 생성
# cd /home/mysql/mysql-5.6.31/bin
# root 계정 비밀번호 생성
# mysqladmin -u root password ******
2) 관리 계정 생성
# ./mysql -u root -p
# database 생성
mysql> create database aaaa;
mysql> grant all on *.* TO 'ID'@'%' identified by 'password' with grant option;
mysql> flush privileges;
mysql> exit;
[설정내용 확인]
mysql > show databases;
mysql > select user,host from mysql.user;
------------------------------------
[ error 관련 ]
./mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)' Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists! |
이런 에러 발생시 다음과 같이 처리 해준다.
경로에 .sock file 안생겨서 발생하는 에러로, 아래와 같이 링크를 만들어준다.
ln -s /home/mysql/mysql-5.6.31/conf/mysql.sock /tmp/mysql.sock |
동작 체크 ㅋㅋ