MySQL 설치 (5.6 or 5.7)

자세한 설명은 생략한다.

다운로드는 요기 ↓↓↓ 

MySQL_install_5717.log

--=========================================================================================================

-- 001 OS 지표 확인 및 변경

--=========================================================================================================

su - 

[root@mpmykpop01 ~]# df -hT

Filesystem           Type   Size  Used Avail Use% Mounted on

/dev/sda3            ext4   9.8G  833M  8.5G   9% /

tmpfs                tmpfs   32G     0   32G   0% /dev/shm

/dev/sda1            ext4   976M  150M  776M  17% /boot

/dev/mapper/vg01-lvol_home

                     ext4   856G  5.5G  807G   1% /home

/dev/sda5            ext4    20G  6.0G   13G  33% /usr

/dev/sda6            ext4    15G  705M   14G   5% /var

/dev/sdb1            ext4   1.8T   68M  1.7T   1% /data

[root@mpmykpop01 ~]# cat /etc/fstab

#

# /etc/fstab

# Created by anaconda on Mon Feb 27 17:31:10 2017

#

# Accessible filesystems, by reference, are maintained under '/dev/disk'

# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info

#

UUID=7bcbafa0-e25c-42a1-8f15-ad0398297d40 /                       ext4    defaults        1 1

UUID=c6175772-9ce3-40d7-873d-555af5e9dbbc /boot                   ext4    defaults        1 2

/dev/mapper/vg01-lvol_home /home                   ext4    defaults        1 2

UUID=6a40c82c-5308-46bd-8057-587efaf7ab96 /usr                    ext4    defaults        1 2

UUID=4a47848b-9227-4cc6-bd84-876974e6654e /var                    ext4    defaults        1 2

UUID=58f9fea7-400a-401d-9b79-9a704d66269a swap                    swap    defaults        0 0

tmpfs                   /dev/shm                tmpfs   defaults        0 0

devpts                  /dev/pts                devpts  gid=5,mode=620  0 0

sysfs                   /sys                    sysfs   defaults        0 0

proc                    /proc                   proc    defaults        0 0

## DATA MOUNT

LABEL=/data             /data                   ext4    defaults        0 0

[root@mpmykpop01 ~]# free -g

             total       used       free     shared    buffers     cached

Mem:            62          7         55          0          0          5

-/+ buffers/cache:          1         61

Swap:           15          0         15

[root@mpmykpop01 ~]# lsblk

NAME                      MAJ:MIN RM   SIZE RO TYPE MOUNTPOINT

sda                         8:0    0 931.5G  0 disk 

|-sda1                      8:1    0     1G  0 part /boot

|-sda2                      8:2    0    16G  0 part [SWAP]

|-sda3                      8:3    0    10G  0 part /

|-sda4                      8:4    0     1K  0 part 

|-sda5                      8:5    0    20G  0 part /usr

|-sda6                      8:6    0    15G  0 part /var

`-sda7                      8:7    0 869.5G  0 part 

  `-vg01-lvol_home (dm-0) 253:0    0 869.5G  0 lvm  /home

sdb                         8:16   0   1.8T  0 disk 

`-sdb1                      8:17   0   1.8T  0 part /data

[root@mpmykpop01 ~]# cat /etc/sysctl.conf

# Kernel sysctl configuration file for Red Hat Linux

#

# For binary values, 0 is disabled, 1 is enabled.  See sysctl(8) and

# sysctl.conf(5) for more details.

# Controls IP packet forwarding

net.ipv4.ip_forward = 0

# Controls source route verification

net.ipv4.conf.default.rp_filter = 1

# Do not accept source routing

net.ipv4.conf.default.accept_source_route = 0

# Controls the System Request debugging functionality of the kernel

kernel.sysrq = 1

# Controls whether core dumps will append the PID to the core filename.

# Useful for debugging multi-threaded applications.

kernel.core_uses_pid = 1

# Controls the use of TCP syncookies

net.ipv4.tcp_syncookies = 1

# Disable netfilter on bridges.

net.bridge.bridge-nf-call-ip6tables = 0

net.bridge.bridge-nf-call-iptables = 0

net.bridge.bridge-nf-call-arptables = 0

# Controls the default maxmimum size of a mesage queue

kernel.msgmnb = 65536

# Controls the maximum size of a message, in bytes

kernel.msgmax = 65536

# Controls the maximum shared segment size, in bytes

kernel.shmmax = 68719476736

# Controls the maximum number of shared memory segments, in pages

kernel.shmall = 4294967296

#Melon Service

net.ipv4.tcp_max_syn_backlog = 32767

net.ipv4.tcp_fin_timeout = 30

net.ipv4.tcp_keepalive_time = 180

net.ipv4.tcp_retries2=5

net.ipv4.tcp_syn_retries=2

net.core.wmem_max=33554432

net.core.rmem_max=33554432

net.core.rmem_default = 33554432

net.core.wmem_default = 33554432

net.ipv4.tcp_rmem=4096 87380 33554432

net.ipv4.tcp_wmem=4096 87380 33554432

net.ipv4.tcp_mem=12582912 12582912 12582912

net.ipv4.conf.all.log_martians=1

#By Kwon _ Dos

net.core.netdev_max_backlog = 150000

net.core.somaxconn=32767

net.ipv4.tcp_max_orphans=262144

net.ipv4.tcp_synack_retries=2

net.core.optmem_max = 12582912

#vm.swappiness=20

vm.swappiness=0

--==============================================================================

-- 001_02 - IO Elevator 확인

--==============================================================================

[root@mpmykpop01 ~]# cat /sys/block/sdb/queue/scheduler

[noop] anticipatory deadline cfq 

-- cd /sys/block/sdb/queue

-- cat scheduler

-- echo "noop" > scheduler

--==============================================================================

-- 001_03 - Disk Cache ON 및 Cacheratio 변경

--==============================================================================

-- 생략

--==============================================================================

-- 001_04 - /etc/security/limits.conf 변경 (Openfiles 수 영향)

--==============================================================================

[root@mpmykpop01 ~]# cat /etc/security/limits.conf

# /etc/security/limits.conf

#

#Each line describes a limit for a user in the form:

#

#<domain>        <type>  <item>  <value>

#

#Where:

#<domain> can be:

#        - an user name

#        - a group name, with @group syntax

#        - the wildcard *, for default entry

#        - the wildcard %, can be also used with %group syntax,

#                 for maxlogin limit

#

#<type> can have the two values:

#        - "soft" for enforcing the soft limits

#        - "hard" for enforcing hard limits

#

#<item> can be one of the following:

#        - core - limits the core file size (KB)

#        - data - max data size (KB)

#        - fsize - maximum filesize (KB)

#        - memlock - max locked-in-memory address space (KB)

#        - nofile - max number of open files

#        - rss - max resident set size (KB)

#        - stack - max stack size (KB)

#        - cpu - max CPU time (MIN)

#        - nproc - max number of processes

#        - as - address space limit (KB)

#        - maxlogins - max number of logins for this user

#        - maxsyslogins - max number of logins on the system

#        - priority - the priority to run user process with

#        - locks - max number of file locks the user can hold

#        - sigpending - max number of pending signals

#        - msgqueue - max memory used by POSIX message queues (bytes)

#        - nice - max nice priority allowed to raise to values: [-20, 19]

#        - rtprio - max realtime priority

#

#<domain>      <type>  <item>         <value>

#

#*               soft    core            0

#*               hard    rss             10000

#@student        hard    nproc           20

#@faculty        soft    nproc           20

#@faculty        hard    nproc           50

#ftp             hard    nproc           0

#@student        -       maxlogins       4

root            soft    nofile          8192

root            hard    nofile          8192

root            soft    nproc           135167

root            hard    nproc           135167

###########################################################

# mysql settings

###########################################################

mysql        hard    nofile       131072

mysql        soft    nofile       131072

mysql        hard    nproc        65535

mysql        soft    nproc        65535

# End of file

--==============================================================================

-- 001_05 - /etc/hosts 변경

--==============================================================================

vi /etc/hosts

127.0.0.1       mpmykpop01 localhost

--================================================================================================

-- 002 MySQL 인스턴스 설치

--================================================================================================

--==============================================================================

-- 002_01 OS계정 설정 등

--==============================================================================

cat /etc/passwd | grep mysql

cat /etc/group | grep mysql

userdel mysql

cat /etc/passwd | grep mysql

cat /etc/group | grep mysql

groupadd -g 2000 mysql

useradd -g mysql -u 2000 -s /bin/bash -d /home/mysql mysql

chmod 750 /home/mysql

chage -l mysql

chage -E -1 -I 0 -m 0 -M 99999 mysql

chage -l mysql

passwd mysql

"xxxxxxxx"

su - mysql

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

################################################################################

# User specific environment and startup programs

################################################################################

LANG=ko_KR.UTF-8

export LANG

#######################################################

# MySQL #export

#######################################################

export DB_DIR_BASE="/home/mysql/MySQL"

export DB_DIR_DATA="/home/mysql/DBSpace/data"

export DB_DIR_BINLOG="/home/mysql/DBSpace/binlog"

export DB_DIR_IBLOG="/home/mysql/DBSpace/iblog"

export DB_DIR_RELLOG="/home/mysql/DBSpace/data"

export DB_DIR_TMP="/home/mysql/DBSpace/tmp"

export DB_DIR_BACKUP="/home/mysql/DBBackup"

export MYSQL_PS1="\R:\m:\s \u@"$HOSTNAME" [\d]> "

#######################################################

# MySQL #alias

#######################################################

alias 3306=$DB_DIR_BASE"/bin/mysql -uroot -p -c -S /tmp/mysql.sock"

alias dir_dbbase="cd "$DB_DIR_BASE

alias dir_dbdata="cd "$DB_DIR_DATA

alias dir_dbbinlog="cd "$DB_DIR_BINLOG

alias dir_dbiblog="cd "$DB_DIR_IBLOG

alias dir_dbbackup="cd "$DB_DIR_BACKUP

alias tail_errlog="tail -f "$DB_DIR_DATA"/"$HOSTNAME".err"

--==================================================================================================

-- 002_02 mysql 5.7.17 설치

--==================================================================================================

su - 

mkdir /data/DBSpace

mkdir /data/DBSpace/data

mkdir /data/DBSpace/iblog

mkdir /data/DBSpace/tmp

mkdir /data/DBSpace/binlog

mkdir /data/DBBackup

mkdir /home/mysql/DBSpace

ln -s /data/DBSpace/data /home/mysql/DBSpace

ln -s /data/DBSpace/iblog /home/mysql/DBSpace

ln -s /data/DBSpace/tmp /home/mysql/DBSpace

ln -s /data/DBSpace/binlog /home/mysql/DBSpace

ln -s /data/DBBackup /home/mysql/DBBackup

mkdir /home/mysql/DBA

mkdir /home/mysql/DBA/programs

chown -R mysql:mysql /data/DBBackup

chown -R mysql:mysql /data/DBSpace

chown -R mysql:mysql /home/mysql/DBSpace

chown -R mysql:mysql /home/mysql/DBBackup

chown -R mysql:mysql /home/mysql/DBA

chmod -R 750 /data/DBBackup

chmod -R 750 /data/DBSpace

su - mysql

cd /home/mysql/DBA/programs

ls -al 

scp 192.168.100.123:/home/oracle/DBA/hoon/tmp/mysql-5.7.17-linux-glibc2.5-x86_64.tar ./

ls -al 

tar xvzf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz

mv mysql-5.7.17-linux-glibc2.5-x86_64  /home/mysql/MySQL_5717

ln -s /home/mysql/MySQL_5717 /home/mysql/MySQL

cd /home/mysql

ls -al

chown -R mysql:mysql /home/mysql/MySQL

chown -R mysql:mysql /home/mysql/MySQL_5717

chmod -R 750 /home/mysql/MySQL

chmod -R 750 /home/mysql/MySQL_5717

ls -al

cd /home/mysql/MySQL/bin/

ls -al | grep mysqld

su - 

echo /home/mysql/MySQL/lib >> /etc/ld.so.conf && ldconfig

echo /usr/lib64 >> /etc/ld.so.conf && ldconfig

echo PATH=/home/mysql/MySQL/bin:$PATH >> /etc/profile && source /etc/profile

vi /etc/profile

..........

PATH=/home/mysql/MySQL/bin:/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin

cd /etc/

ls -al | grep my.cnf

rm -rf my.cnf

ls -al | grep my.cnf

cat /home/mysql/MySQL/support-files/my-default.cnf

cp /home/mysql/MySQL/support-files/my-default.cnf ./my.cnf

chmod 640 my.cnf

chown root:mysql my.cnf

ls -al | grep my.cnf

vi my.cnf

.............

########################################################################################################

# MySQL 5.6.28 Global Variables

# - OS Spec :

# - DB Spec :

########################################################################################################

[client]

##################################################################################################

# Client Options

##################################################################################################

port                          = 3306

socket                        = /tmp/mysql.sock

[mysqld]

##################################################################################################

# MySQLD Options

##################################################################################################

#############################################################################

# MySQLD - Connection Info

#############################################################################

port                            = 3306

socket                          = /tmp/mysql.sock

#############################################################################

# MySQLD - Directory Path Info

#############################################################################

basedir                    = /home/mysql/MySQL

datadir                    = /home/mysql/DBSpace/data

tmpdir                     = /home/mysql/DBSpace/tmp

log-bin                    = /home/mysql/DBSpace/binlog/mysql-bin

relay-log                  = /home/mysql/DBSpace/data/relay-log

#############################################################################

# MySQLD - Normal Options

#############################################################################

skip-character-set-client-handshake

skip-host-cache

skip-name-resolve

skip-external-locking

sysdate-is-now

init_connect                    = "set collation_connection=utf8mb4_general_ci"

init_connect                    = "set names utf8mb4"

character-set-server            = utf8mb4

collation-server                = utf8mb4_general_ci

transaction_isolation           = REPEATABLE-READ

sql_mode                        = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

event_scheduler                 = 0

log_bin_trust_function_creators = 1

ft_min_word_len                 = 2

default-storage-engine          = innodb

lower_case_table_names          = 1

query_cache_type                = 0

query_cache_size                = 0

query_cache_limit               = 0

back_log                        = 50

slow_query_log                  = 1

long_query_time                 = 1

log_output                      = FILE

log-warnings                    = 2

max_connections                 = 3000

sort_buffer_size                = 1M

read_buffer_size                = 1M

join_buffer_size                = 1M

read_rnd_buffer_size            = 2M

max_heap_table_size             = 64M

tmp_table_size                  = 64M

thread_cache_size               = 200

wait_timeout                    = 28800

max_connect_errors              = 99999

table_open_cache                = 4096

max_allowed_packet              = 1G

group_concat_max_len            = 10485760

open-files-limit                = 40960

#############################################################################

# MySQLD - InnoDB Info

#############################################################################

innodb_print_all_deadlocks

innodb_buffer_pool_dump_at_shutdown   = 1

innodb_buffer_pool_load_at_startup    = 1

innodb_data_home_dir             = /home/mysql/DBSpace/data

innodb_log_group_home_dir        = /home/mysql/DBSpace/iblog

innodb_buffer_pool_instances     = 8

innodb_buffer_pool_size          = 30GB

innodb_log_buffer_size           = 16M

innodb_data_file_path            = ibdata1:10M:autoextend

innodb_flush_method              = O_DIRECT

innodb_flush_log_at_trx_commit   = 1

innodb_doublewrite               = 0

innodb_support_xa                = 1

innodb_open_files                = 20480

innodb_sort_buffer_size          = 8M

innodb_log_file_size             = 2048M

innodb_log_files_in_group        = 3

innodb_max_dirty_pages_pct       = 90

innodb_lock_wait_timeout         = 60

innodb_file_per_table            = 1

innodb_stats_on_metadata         = 0

#innodb_read_io_threads          = 12

#innodb_write_io_threads         = 12

#innodb_force_recovery           = 0

#innodb_thread_concurrency       = 24

#innodb_fast_shutdown            = 0

performance_schema

#innodb_status_file              = 1

#innodb_adaptive_flushing        = 1

#innodb_use_native_aio           = 1

#innodb_purge_thread             = 1

#############################################################################

# MySQLD - Replication Info

#############################################################################

server-id                        = 15206  

binlog_format                    = MIXED

expire_logs_days                 = 8

max_binlog_size                  = 300M

binlog_cache_size                = 1M

read_only

log_slave_updates                = 1

sync_binlog                      = 0

relay_log_purge                  = 0

skip_slave_start

replicate-ignore-db              = mysql

replicate-ignore-db              = information_schema

replicate-ignore-db              = performance_schema

replicate-ignore-db              = test

replicate-ignore-db              = sysbench

replicate-ignore-db              = mp_member

replicate-ignore-db              = mp_cms

#############################################################################

# MySQLD - MyISAM Info

#############################################################################

key_buffer_size                  = 32M

bulk_insert_buffer_size          = 64M

myisam_sort_buffer_size          = 128M

myisam_max_sort_file_size        = 1G

myisam-recover-options

#############################################################################

# ETC Options

#############################################################################

[mysqldump]

quick

max_allowed_packet = 32M

[mysql]

no-auto-rehash

[myisamchk]

key_buffer_size      = 256M

sort_buffer_size     = 256M

read_buffer          = 4M

write_buffer         = 4M

[mysqlhotcopy]

interactive-timeout

[mysqld_safe]

open-files-limit     = 40960

cd /etc/init.d/

ls -al | grep mysql

rm -rf mysqld

ls -al | grep mysql

cat /home/mysql/MySQL/support-files/mysql.server

cp /home/mysql/MySQL/support-files/mysql.server ./mysqld

ls -al | grep mysql

chmod 750 mysqld

chown root:mysql mysqld

ls -al | grep mysql

vi mysqld

...........

basedir=/home/mysql/MySQL

datadir=/home/mysql/DBSpace/data

...........

su - mysql

cd /home/mysql/MySQL/

-------------------------- ver. 5.6 ----------------------------------

./scripts/mysql_install_db --user=mysql --defaults-file=/etc/my.cnf

----------------------------------------------------------------------

-------------------------- ver. 5.7 --------------------------------

./bin/mysqld --defaults-file=/etc/my.cnf --verbose --help

./bin/mysqld --defaults-file=/etc/my.cnf --initialize

--------------------------------------------------------------------

################################################# 중요 #####################################################

# ./bin/mysqld --defaults-file=/etc/my.cnf --initialize 5.7 버젼은 수행시 로그 중간에 패스워드 생성됨            #

# 2017-03-09T01:21:45.975514Z 1 [Note] A temporary password is generated for root@localhost: z.#6tEKklAma  #

############################################################################################################

cp /home/mysql/MySQL/bin/mysqld_safe /home/mysql/MySQL/bin/mysqld_safe_20170307

vi /home/mysql/MySQL/bin/mysqld_safe

........

 426   MY_PWD=`dirname $MY_PWD`

 427   ;;

 428 esac

 429 MY_PWD=`pwd`   << 추가

 430 # Check for the directories we would expect from a binary release install

 431 if test -n "$MY_BASEDIR_VERSION" -a -d "$MY_BASEDIR_VERSION"

.........

/etc/init.d/mysqld start

cat /home/mysql/DBSpace/data/mpmykpop01.err

--------------------- ver. 5.6 -----------------------------

/home/mysql/MySQL/bin/mysql -uroot --socket=/tmp/mysql.sock

set names utf8;

use mysql;

select user, host from user order by user, host;

delete from user where user<>'root' or host<>'localhost';

select user, host from user order by user, host;

flush privileges;

set password for 'root'@'localhost'=password('XXXXXXXX');

flush privileges;

------------------------------------------------------------

------------------------ ver. 5.7 ----------------------------------

/home/mysql/MySQL/bin/mysql -uroot -p -c -S /tmp/mysql.sock

패스워드 입력 : z.#6tEKklAma

set names utf8;

ALTER USER 'root'@'localhost' IDENTIFIED BY 'XXXXXXXX';

flush privileges;

mysql> use mysql;

mysql> select user, host from user;

select user, host, authentication_string from user;

select user, host, HEX(authentication_string) as password from user;

---------------------------------------------------------------------

show databases;

drop database test;

show databases;

show global variables like '%innodb_buffer_pool_size%';

+-------------------------+-------------+

| Variable_name           | Value       |

+-------------------------+-------------+

| innodb_buffer_pool_size | 32212254720 |

+-------------------------+-------------+

3306

/etc/init.d/mysqld stop

cat /home/mysql/DBSpace/data/mpmykpop01.err

/etc/init.d/mysqld start

cat /home/mysql/DBSpace/data/mpmykpop01.err