JJON's StorY

mysql에 테이블 생성하는데 에러가 발생 했다.

PK제약인가? 

다시 생성

여전히 안되는군.

인덱스 만들때 key 사이즈가 767바이트를 넘으면 안되는데,

utf8mb4는 1글자당 4바이트.

그럼 191글자 까지 가능.

인덱스 한번 만들기 힘드네.

mysql을 사용하면 안되는 이유...

자세한 설명은 생략한다.

다운로드는 요기 ↓↓↓ 

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

MySQL load data 예제

---------- aaa.txt -------------

100004,3611509,91643,"홍길동"

1000123,3,0,"이순신"

100022,996705,26469,"장보고"

100059,28456594,736957,"김유신"

100103,0,1,"최영"

100176,30570326,503211,"이성계"

100202,53,3,"유재석"

1002239,1,0,"박명수"

1002275,6,0,"하하"

100257,4031,117,"광희"

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

CREATE TABLE `bbb` (

  `col1` int(11) DEFAULT NULL,

  `col2` bigint(20) DEFAULT NULL,

  `col3` int(11) DEFAULT NULL,

  `col4` varchar(20) DEFAULT NULL,

  `col5` datetime DEFAULT NULL

) ;

load data local infile 'aaa.txt' 

into TABLE bbb

FIELDS TERMINATED BY ',' 

ENCLOSED BY '"' 

LINES TERMINATED BY '\n' 

(col1, col2, col3, col4, @col5) set col5=date_format(now(),'%Y%m%d%H%i%s');

MariaDB [MP_POC]> select * from bbb;

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

| col1    | col2     | col3   | col4      | col5                |

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

|  100004 |  3611509 |  91643 | 홍길동    | 2016-10-27 11:10:02 |

| 1000123 |        3 |      0 | 이순신    | 2016-10-27 11:10:02 |

|  100022 |   996705 |  26469 | 장보고    | 2016-10-27 11:10:02 |

|  100059 | 28456594 | 736957 | 김유신    | 2016-10-27 11:10:02 |

|  100103 |        0 |      1 | 최영      | 2016-10-27 11:10:02 |

|  100176 | 30570326 | 503211 | 이성계    | 2016-10-27 11:10:02 |

|  100202 |       53 |      3 | 유재석    | 2016-10-27 11:10:02 |

| 1002239 |        1 |      0 | 박명수    | 2016-10-27 11:10:02 |

| 1002275 |        6 |      0 | 하하      | 2016-10-27 11:10:02 |

|  100257 |     4031 |    117 | 광희      | 2016-10-27 11:10:02 |

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

10 rows in set (0.00 sec)

insert into bbb values (1,2,3,"aaa",date_format('2016-01_01','%y%m%d'));

MariaDB [MP_POC]> select * from bbb;

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

| col1    | col2     | col3   | col4      | col5                |

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

|  100004 |  3611509 |  91643 | 홍길동    | 2016-10-27 11:10:02 |

| 1000123 |        3 |      0 | 이순신    | 2016-10-27 11:10:02 |

|  100022 |   996705 |  26469 | 장보고    | 2016-10-27 11:10:02 |

|  100059 | 28456594 | 736957 | 김유신    | 2016-10-27 11:10:02 |

|  100103 |        0 |      1 | 최영      | 2016-10-27 11:10:02 |

|  100176 | 30570326 | 503211 | 이성계    | 2016-10-27 11:10:02 |

|  100202 |       53 |      3 | 유재석    | 2016-10-27 11:10:02 |

| 1002239 |        1 |      0 | 박명수    | 2016-10-27 11:10:02 |

| 1002275 |        6 |      0 | 하하      | 2016-10-27 11:10:02 |

|  100257 |     4031 |    117 | 광희      | 2016-10-27 11:10:02 |

|       1 |        2 |      3 | aaa       | 2016-01-01 00:00:00 |

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

11 rows in set (0.00 sec)

1. MySQL에 새로운 사용자 계정 추가하기

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'

    ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'

    ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';

mysql> GRANT USAGE ON *.* TO 'dummy'@'localhost';

GRANT 명령문을 사용해서 생성된 계정은 아래와 같은 특성을 가지게 된다:

- 생성된 계정 중에 두 개는 사용자 이름 monty와 패스워드 some_pass를 가진다. 두 계정 모두 전체 권한을 가지고 있는 슈퍼유저 (superuser) 계정이다. 하나의 계정 ('monty'@'localhost')은 로컬 호스트에서 접속을 할 경우에만 사용된다. 또 다른 계정 ('monty'@'%')은 다른 호스트에서 접속을 하기 위해 사용된다. monty라는 이름으로 어디에서든지 접속을 할 수 있기 위해서는 monty에 대해 두 개의 계정을 모두 가지고 있는 것이 필요하다는 것을 알아 두기 바란다. localhost 계정이 없다면, monty가 로컬 호스트에서 접속을 할 때 mysql_install_db가 생성한 localhost에 대한 익명 사용자 계정이 우선적으로 사용된다. 그 결과로, monty는 하나의 익명 사용자처럼 취급을 받게 된다. 이렇게 되는 이유는 익명 사용자 계정이 'monty'@'%' 계정보다 더 구체적인 (more-specific) Host 컬럼 값을 가지기 때문이며 따라서 user 테이블 정렬 순서에서 보다 앞서 나오기 때문이다.

- admin이라는 이름의 패스워드가 없는 계정이 하나 있다. 이 계정은 로컬 호스트에서 접속하기 위해서만 사용된다. 이 계정은 RELOAD 와 PROCESS 관리 권한을 가지고 있다. 이러한 권한은 admin 사용자가 mysqladmin reload, mysqladmin refresh, 그리고 mysqladmin flush-xxx 명령문 뿐만 아니라, mysqladmin processlist도 실행할 수 있도록 해 준다. 어떤 데이터 베이스에 대해서도 접근할 수 있는 권한은 승인되지 않았다. 여러분은 나중에 GRANT 명령문을 입력해서 이러한 권한을 추가할 수가 있다.

- dummy라는 이름의 패스워드가 없는 계정이 하나 있다. 이 계정은 로컬 호스트에서 접속을 할 때에만 사용된다. 아무런 권한도 승인되지 않았다. GRANT 명령문에 있는 USAGE 권한은 여러분이 아무런 권한이 없는 계정을 만들 수 있게 해 준다. 이것은 모든 글로벌 권한을 'N'으로 설정하면 된다. 이 계정에 여러분이 특정 권한을 나중에 승인할 것이라는 가정을 한다.

2. MySQL에 새로운 사용자 비밀번호 변경

mysql> SET PASSWORD FOR 'jeffrey'@'%' = PASSWORD('biscuit');

혹은

mysql> INSERT INTO user (Host,User,Password)

    -> VALUES('%','jeffrey',PASSWORD('biscuit'));

mysql> FLUSH PRIVILEGES;

flush privileges; --  서버가 FLUSH PRIVILEGES를 실행해서 그랜트 테이블을 다시 읽어 옴으로써 변경된 권한이 적용되도록 해야 한다.

글로벌 레벨(ON *.*)에서 GRANT USAGE 명령문을 사용해서 한 계정의 현재 권한에는 영향을 미치지 않고 패스워드를 할당할 수가 있다:

mysql> GRANT USAGE ON *.* TO 'jeffrey'@'%' IDENTIFIED BY 'biscuit';

3. 계정 자원 제한하기

MySQL 5.0에서는, 각 개별 계정에서 대해 아래와 같이 서버 자원을 한정할 수가 있다:

한 계정이 시간 당 입력할 수 있는 쿼리의 숫자

한 계정이 시간 당 입력할 수 있는 업데이트의 숫자

한 계정이 시간 당 접속할 수 있는 숫자

mysql> GRANT ALL ON customer.* TO 'francis'@'localhost'

    ->     IDENTIFIED BY 'frank'

    ->     WITH MAX_QUERIES_PER_HOUR 20

    ->          MAX_UPDATES_PER_HOUR 10

    ->          MAX_CONNECTIONS_PER_HOUR 5

    ->          MAX_USER_CONNECTIONS 2;

제한 타입을 WITH 구문에 모두 거론할 필요는 없으나, 제한 타입의 이름들은 일정 순서로 표시될 수는 있다. 각각의 시간당 제한치 값은 시간당 카운트 되는 정수 값이 되어야 한다. 만약에 GRANT 명령문이 WITH 구문을 가지고 있지 않다면, 제한치는 각각 디폴트 0으로 설정된다 (즉, 제한 없음). MAX_USER_CONNECTIONS의 경우, 제한치는 그 계정이 어느 한 시점에 동시에 접속을 할 수 있는 최대 값을 나타내는 정수 값이 된다. 만일 이 값이 디폴트 0으로 설정되면, max_user_connections 시스템 변수 값이 그 계정의 동시 접속 숫자가 된다.

현재 존재하는 계정의 제한치를 설정 또는 변경하기 위해서는, 글로벌 레벨 (ON *.*)에서 GRANT USAGE 명령문을 사용한다. 아래의 명령문은 francis의 쿼리 한계를 100으로 변경한다:

mysql> GRANT USAGE ON *.* TO 'francis'@'localhost'

    ->     WITH MAX_QUERIES_PER_HOUR 100;

이 명령문은 계정의 현재 권한을 변경하지 않는 상태에서 지정된 제한 값만을 수정한다.

현재의 제한 값을 제거하기 위해서는, 그 값을 0으로 설정한다. 예를 들면, francis가 한 시간에 접속할 수 있는 제한 값을 제거하기 위해서는, 아래의 명령문을 사용한다:

mysql> GRANT USAGE ON *.* TO 'francis'@'localhost'

    ->     WITH MAX_CONNECTIONS_PER_HOUR 0;

현재의 시간당 자원 사용 카운트는 모든 계정에 대해 전체적으로, 또는 지정한 계정에 대해서만 개별적으로 리셋 시킬 수가 있다:

모든 계정의 현재 카운트 값을 0으로 리셋 시키기 위해서는, FLUSH USER_RESOURCES 명령문을 사용한다. 이 카운트 값은 그랜트 테이블을 다시 읽어 와서 리셋 시킬 수도 있다 (예를 들면, FLUSH PRIVILEGES 명령문 또는 mysqladmin reload 명령어를 사용해서).

개별 계정 카운트 값은 그것을 제한 값 중의 하나로 재 승인하면 0으로 설정된다. 이렇게 하기 위해서는, 앞에서 설명한 방식으로 GRANT USAGE를 사용하고 제한 값을 그 계정이 현재 가지고 있는 값과 동일하게 지정 한다.

카운터를 리셋하더라도 MAX_USER_CONNECTIONS 제한 값에는 영향을 주지 않는다.

서버가 시작될 때 모든 카운트는 0으로 리셋 된다.

4. 사용자 삭제

drop user admin@localhost;

flush privileges;

혹은

mysql> DLETE FROM USER WHERE USER='username';

mysql> FLUSH PRIVILEGES;

5. 사용자 권한 조회

select a.Db, b.* 

from db a, user b 

where a.user=b.user and a.host=b.host

order by 1,2,3

\G;

참조 : http://www.dbguide.net/db.db?mobile&cmd=view&boardUid=146586&boardConfigUid=9&boardIdx=127