41.mysql 5.7설치-program.zip
41.mysql 5.7설치.txt안녕하세요. 준경대디 입니다.
"머리만1톤"님도 언급하셨지만,
경우에 따라 기존 설치스크립트대로 MySQL 5.7 설치해보면 여러가지 난관을 경험하게 됩니다.
저도 동일한 어려움을 겪고 고생한 적이 있었는데,
문제없이 설치되는 스크립트 공유해 봅니다.
-- =================================================================================
-- 필수 프로그램 설치
-- =================================================================================
sudo su -
yum install -y gcc automake autoconf libtool make cpan libstdc* libaio*
yum install -y perl perl-devel perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-Params-Validate
yum install -y gzip unzip sysstat* iotop
-- =================================================================================
-- OS 환경 설정
-- =================================================================================
sudo su -
echo ""
echo "* - nofile 65536" >> /etc/security/limits.d/90-nproc.conf
echo "* - nproc unlimited" >> /etc/security/limits.d/90-nproc.conf
cat /etc/security/limits.d/90-nproc.conf
echo /usr1/mysql/5705/lib >> /etc/ld.so.conf && ldconfig
echo /usr/lib64 >> /etc/ld.so.conf && ldconfig
echo PATH=$PATH:/usr1/mysql/5705/bin >> /etc/profile && source /etc/profile
cat /etc/ld.so.conf
. /etc/profile
-- =================================================================================
-- Installing MySQL 5.7.5 Step.1
-- =================================================================================
sudo su -
mkdir -p /usr1/program
rm -rf /usr1/mysql/DBSpace_VM
mkdir -p /usr1/mysql/DBSpace_VM/ADMIN
mkdir -p /usr1/mysql/DBSpace_VM/DATA
mkdir -p /usr1/mysql/DBSpace_VM/BINLOG
mkdir -p /usr1/mysql/DBSpace_VM/RELAYLOG
mkdir -p /usr1/mysql/DBSpace_VM/IBLOG
mkdir -p /usr1/mysql/DBSpace_VM/BACKUP
mkdir -p /usr1/mysql/dba/log
ls -l /usr1/mysql/DBSpace_VM/*
tail -1 /etc/group
tail -1 /etc/passwd
groupadd mysql
useradd super-dba -g mysql -d /home/super-dba
passwd super-dba
tail -1 /etc/group
tail -1 /etc/passwd
cd /usr1/program
ls -l
-- 프로그램 다운로드 & 업로드
# -rw-r--r--. 1 root root 1042165 May 27 2014 common_schema-2.2.sql
# -rw-r--r--. 1 root root 88964 Sep 4 15:10 htop-1.0.3-1.el7.rf.x86_64.rpm
# -rw-r--r--. 1 root root 423605472 Dec 2 22:56 mysql-5.7.5-m15-linux-glibc2.5-x86_64.tar.gz
# drwxr-xr-x. 5 root root 4096 Feb 23 15:56 mysql-sys-master
# -rw-r--r--. 1 root root 52794 Sep 23 22:31 zzdba_objects_v1.2_20140901.sql
# wget https://downloads.mariadb.com/archives/mysql-5.7/mysql-5.7.5-m15-linux-glibc2.5-x86_64.tar.gz
tar zxvf mysql-5.7.5-m15-linux-glibc2.5-x86_64.tar.gz
mv mysql-5.7.5-m15-linux-glibc2.5-x86_64 /usr1/mysql/5705
ls -l
cd /usr1/mysql/
ls -l
cat /etc/ld.so.conf
. /etc/profile
ls -l /etc/my.cnf
mv /etc/my.cnf /etc/my.cnf.old
ln -s /usr1/mysql/DBSpace_VM/ADMIN/my.cnf /etc/my.cnf
ls -l /etc/my.cnf
vi /usr1/mysql/DBSpace_VM/ADMIN/init.sql
================================================================
set global max_connections = 1000;
================================================================
vi /etc/my.cnf
================================================================
... 맨 아래 참고 ...
================================================================
chown -R super-dba:mysql /usr1
ls -l /usr1/mysql
-- =================================================================================
-- Installing MySQL 5.7.5 Step.2
-- =================================================================================
sudo su -
cd /usr1/mysql/5705
ls -l /usr1/mysql/DBSpace_VM/*
./bin/mysql_install_db --no-defaults --insecure -uroot --datadir=/usr1/mysql/DBSpace_VM/DATA --lc-messages-dir=/usr1/mysql/5705/share --lc-messages=en_US -v --defaults-file=/etc/my.cnf --basedir=/usr1/mysql/5705 --user=super-dba
ls -l /usr1/mysql/DBSpace_VM/*
cat ./../DBSpace_VM/ADMIN/error.log
-- =================================================================================
-- Installing MySQL 5.7.5 (3)
-- =================================================================================
sudo su -
ls -l /etc/init.d/mysqld
ln -sv /usr1/mysql/5705/support-files/mysql.server /etc/init.d/mysqld
ls -l /etc/init.d/mysqld
### MySQL 기동 ###
ps -ef | grep mysqld
su - super-dba -c "service mysqld start"
su - super-dba -c "service mysqld restart"
ps -ef | grep mysqld
### MySQL root 유저 암호 설정 ###
su - super-dba -c "/usr1/mysql/5705/bin/mysqladmin -u root password oracle"
su - super-dba -c "/usr1/mysql/5705/bin/mysql -uroot --password=oracle -e \"select version();\""
su - super-dba -c "/usr1/mysql/5705/bin/mysql -uroot --password=oracle"
select user, host, password from mysql.user order by user, host;
delete from mysql.user where user = '';
update mysql.user set password=password('oracle') where user = 'root';
drop user 'root'@'::1';
select user, host, password from mysql.user order by user, host;
flush privileges;
exit;
su - super-dba -c "/usr1/mysql/5705/bin/mysql -uroot --password=oracle -e \"select version();\""
-- =================================================================================
-- OS 유저 (super-dba) 설정
-- =================================================================================
sudo su - super-dba
vi /usr1/mysql/dba/.env.ini
=============================================================
#!/bin/bash
export MySQL_VER=5705
export MySQL_BASE=/usr1/mysql
export MySQL_HOME=$MySQL_BASE/$MySQL_VER
export MySQL_PORT=3336
export MySQL_SID=testdb
=============================================================
chmod +x /usr1/mysql/dba/.env.ini
vi ~/.bash_profile
=============================================================
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
export PS1=`hostname`:'$PWD> '
export LD_LIBRARY_PATH=/usr/lib:/usr/local/lib
export PATH=$PATH:$HOME/bin
export PATH=$PATH:/bin:/usr/bin/java/bin:/bin:/usr/bin:/usr/sbin:/etc:/usr/etc
export PATH=$PATH:/usr1/mysql/5705/bin
export TEMPDIR=/tmp
export EDITOR=vi
alias ll="ls -al --color=auto"
source /usr1/mysql/dba/.env.ini
=============================================================
source ~/.bash_profile
ps -ef | grep mysqld
service mysqld restart
ps -ef | grep mysqld
-- =================================================================================================
-- DB 계정 생성
-- =================================================================================================
mysql -u root --password=oracle
select version();
select user, host, password from mysql.user order by user, host;
GRANT SELECT, UPDATE, INSERT, DELETE, EXECUTE ON *.* TO svc_user@'192.168.86.%' IDENTIFIED BY 'oracle';
GRANT SELECT, EXECUTE ON `test%`.* TO etl_reader@'%' IDENTIFIED BY 'oracle';
GRANT REPLICATION SLAVE ON *.* TO 'repl_dba'@'%' IDENTIFIED BY 'oracle';
GRANT SELECT, PROCESS, SUPER, EVENT ON *.* TO 'percona_cacti'@'%' IDENTIFIED BY 'oracle';
GRANT UPDATE ON `moniter`.* TO 'percona_cacti'@'%' IDENTIFIED BY 'oracle';
GRANT ALL PRIVILEGES ON `sys`.* TO 'percona_cacti'@'%' IDENTIFIED BY 'oracle';
FLUSH PRIVILEGES;
select user, host, password from mysql.user order by user, host;
show databases;
create database test;
show databases;
exit
-- =================================================================================================
-- mysql_config_editor 설정
-- =================================================================================================
mysql_config_editor print --all
mysql_config_editor remove --login-path=dbreplica
mysql_config_editor remove --login-path=dbmon
mysql_config_editor remove --login-path=dbreader
mysql_config_editor print --all
mysql_config_editor set --login-path=dbmon --host=localhost --user=root --password --socket=/tmp/mysql.sock --port=3336
mysql_config_editor set --login-path=dbreplica --host=localhost --user=repl_dba --password --socket=/tmp/mysql.sock --port=3336
mysql_config_editor set --login-path=dbreader --host=localhost --user=etl_reader --password --socket=/tmp/mysql.sock --port=3336
mysql --login-path=dbmon -e "SELECT NOW()";
mysql --login-path=dbreplica -e "SELECT NOW()";
mysql --login-path=dbreader -e "SELECT NOW()";
-- =================================================================================================
-- zzdba Database 설정
-- =================================================================================================
cd /usr1/program/mysql-sys-master
mysql -u root --password=oracle
show databases;
tee /usr1/program/zz_install_zzdba.log
source /usr1/program/zzdba_objects_v1.2_20140901.sql
notee
system cat /usr1/program/zz_install_zzdba.log | grep err
-- system vi /usr1/program/zz_install_zzdba.log
tee /usr1/program/zz_install_common_schema.log
source /usr1/program/common_schema-2.2.sql
show global variables like 'thread_stack';
notee
system cat /usr1/program/zz_install_common_schema.log | grep err
-- system vi /usr1/program/zz_install_common_schema.log
/*
====================================================================
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| thread_stack | 524288 |
+---------------+--------+
-- 최소 thread_stack = 256K 이상 권장함
====================================================================
*/
tee /usr1/program/zz_install_mysql-sys-master.log
show databases;
source sys_56.sql
show databases;
select * from statements_with_full_table_scans limit 1;
notee
select * from zzdba.dba_metadata limit 10;
insert into zzdba.dba_metadata (name, value, reg_date)
select 'BUF_TAB_BIG_SIZE'
, data
, now()
from sys.x$innodb_buffer_stats_by_table
order by data desc limit 1
ON DUPLICATE KEY UPDATE value = values(value), reg_date=values(reg_date);
update zzdba.dba_metadata set svc_name = 'testdb';
select * from zzdba.dba_metadata limit 10;
exit
-- =================================================================================================
-- nmon 설정
-- =================================================================================================
sudo su -
cd /usr1/program
wget http://sourceforge.net/projects/nmon/files/nmon_linux_14i.tar.gz
tar zxvfp nmon_linux_14i.tar.gz
chmod +x nmon*
mv nmon_x86_64_centos6 /usr/bin/nmon
rm -rf nmon*
mkdir -p /usr1/mysql/dba/log/nmon
mkdir -p /usr1/mysql/dba/log/top
mkdir -p /usr1/mysql/dba/cron/
ls -l /usr1/mysql/dba/cron/top.sh
echo '#!/bin/bash' > /usr1/mysql/dba/cron/top.sh
echo '' >> /usr1/mysql/dba/cron/top.sh
echo 'BACKUP_FILE=/usr1/mysql/dba/log/top/top.`date "+%Y%m%d"`' >> /usr1/mysql/dba/cron/top.sh
echo '' >> /usr1/mysql/dba/cron/top.sh
echo '/usr/bin/top -c -b -n720 -d120 > $BACKUP_FILE' >> /usr1/mysql/dba/cron/top.sh
echo '/bin/gzip $BACKUP_FILE' >> /usr1/mysql/dba/cron/top.sh
echo '' >> /usr1/mysql/dba/cron/top.sh
echo '# -c : display full command (not command name only)' >> /usr1/mysql/dba/cron/top.sh
echo '# -b : Batch mode' >> /usr1/mysql/dba/cron/top.sh
echo '# -n : Number of iterations' >> /usr1/mysql/dba/cron/top.sh
echo '# -d : Delay (sec)' >> /usr1/mysql/dba/cron/top.sh
chmod +x /usr1/mysql/dba/cron/top.sh
ls -l /usr1/mysql/dba/cron/top.sh
crontab -e
############################################################
# Monitoring by root
############################################################
00 10 * * * /usr/bin/nmon -f -s 60 -c 1440 -m /usr1/mysql/dba/log/nmon
20 10 * * * sh /usr1/mysql/dba/cron/top.sh
crontab -l
chown -R super-dba:mysql /usr1/
exit
-- =================================================================================================
-- dba 폴더 설정
-- =================================================================================================
....
-- =================================================================================================
-- 참고.my.cnf
-- =================================================================================================
################################################################
## my.cnf #
################################################################
[client]
port = 3336
socket = /tmp/mysql.sock
#password = your_password
[mysqld]
server-id = 9999
user = super-dba
port = 3336
socket = /tmp/mysql.sock
basedir = /usr1/mysql/5705
datadir = /usr1/mysql/DBSpace_VM/DATA
tmpdir = /tmp
pid-file = /tmp/mysqld.pid
init_connect = 'SET NAMES utf8;'
init-file = /usr1/mysql/DBSpace_VM/ADMIN/init.sql
character-set-server = utf8
collation-server = utf8_general_ci
skip-character-set-client-handshake
skip-host-cache
skip-name-resolve
skip-external-locking
skip-slave-start
sysdate-is-now
event_scheduler = ON
general-log = OFF
general-log-file = /usr1/mysql/DBSpace_VM/ADMIN/general.log
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
log_output = FILE
slow-query-log = ON
slow-query-log-file = /usr1/mysql/DBSpace_VM/ADMIN/slowquery.log
long_query_time = 0.2
log-queries-not-using-indexes = OFF
log-bin = /usr1/mysql/DBSpace_VM/BINLOG/mysql_bin
expire_logs_days = 3
log-warnings = 2
log-error = /usr1/mysql/DBSpace_VM/ADMIN/error.log
sync_binlog = 0
back_log = 1024
max_connections = 500
max_connect_errors = 9999999999
table_open_cache = 15000
max_allowed_packet = 4M
max_heap_table_size = 128M
tmp_table_size = 128M
read_rnd_buffer_size = 2M
read_buffer_size = 512K
sort_buffer_size = 512K
join_buffer_size = 512K
thread_cache_size = 2048
query_cache_type = DEMAND
query_cache_size = 64M
query_cache_limit = 512K
thread_stack = 512K
ft_min_word_len = 4
wait_timeout = 300
interactive_timeout = 28800
connect_timeout = 300
net_read_timeout = 172800
net_write_timeout = 172800
default-storage-engine = INNODB
lower_case_table_names = 1
binlog_cache_size = 2M
binlog_format = MIXED
binlog_row_image = MINIMAL
transaction_isolation = READ-COMMITTED
max_binlog_size = 128M
log_bin_trust_function_creators = 1
open-files-limit = 65535
explicit_defaults_for_timestamp = TRUE
########################################
# INNODB Specific options
########################################
innodb_data_home_dir = /usr1/mysql/DBSpace_VM/DATA
innodb_log_group_home_dir = /usr1/mysql/DBSpace_VM/IBLOG
innodb_buffer_pool_size = 1GB
#innodb_buffer_pool_instance = 8
innodb-buffer-pool-instances = 8
innodb_data_file_path = ibdata1:500M:autoextend
innodb_file_per_table = 1
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_force_recovery = 0
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 8M
innodb_log_file_size = 50M
innodb_log_files_in_group = 10
innodb_max_dirty_pages_pct = 30
innodb_flush_method = O_DIRECT
innodb_lock_wait_timeout = 15
innodb_doublewrite = ON
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_status_file = 1
innodb_adaptive_flushing = 1
innodb_use_native_aio = 1
#innodb_purge_thread = 1
innodb-purge-threads = 1
innodb_fast_shutdown = 0
innodb_stats_auto_recalc = OFF
innodb_stats_persistent = OFF
innodb_sort_buffer_size = 2M
innodb_print_all_deadlocks = ON
skip-innodb_adaptive_hash_index
performance_schema = ON
federated
innodb_io_capacity = 2000
innodb_io_capacity_max = 10000
########################################
# Replication related settings
########################################
replicate-ignore-db = perf_mon
replicate-ignore-db = sys
replicate-ignore-db = moniter
replicate-ignore-db = mysql
#replicate-do-db = xxxdb
#
#relay_log_purge = OFF
#relay-log = /usr1/mysql/DBSpace_VM/RELAYLOG/mysql_relay
#log_slave_update
#########################################
########################################
# MyISAM Specific options
########################################
key_buffer_size = 64M
bulk_insert_buffer_size = 16M
myisam_sort_buffer_size = 16M
myisam_max_sort_file_size = 16M
myisam_repair_threads = 1
#myisam_recover
myisam-recover-options
[mysqldump]
quick
max_allowed_packet = 64M
[mysql]
no-auto-rehash
prompt = '[\h] (\d) \R:\m> '
[myisamchk]
key_buffer_size = 64M
sort_buffer_size = 16M
read_buffer = 16M
write_buffer = 16M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 65535
-- =================================================================================================
출처 : http://cafe.naver.com/mysqlpg/838
'연구개발 > MYSQL' 카테고리의 다른 글
| Datetime 필드 기준 파티션 자동 관리 프로시저 (MySQL) (0) | 2015.06.11 |
|---|---|
| federated mysql의 linked server 구현 (0) | 2015.06.10 |
| Avoiding Common MySQL Operations Mistakes.pdf (0) | 2015.04.10 |
| mysql 대용량 백업 빠르게하기. speed up mysqldump (0) | 2015.04.02 |
| slave 데이터 불일치 시 (0) | 2015.04.02 |