'MySQL'에 해당되는 글 13건

  1. 2018.06.12 [MySQL] mysql.user 테이블에 계정 추가하기.
  2. 2016.07.21 [MySQL] JSON Type 사용하기
  3. 2016.04.30 [MySQL] root password 리셋하기.
  4. 2016.03.09 [Mysql] create database syntax example.
  5. 2016.02.17 [MySQL] SQL JOINS 그림 설명.
  6. 2014.03.07 11.6 Data Type Storage Requirements
  7. 2013.08.22 [Mysql] 5.6.1 source install.. ncurses error...
  8. 2013.04.30 [MySQL] Mac OSX 에서 MySQL 설치하기.
  9. 2013.04.26 [Hadoop] hadoop + hive + mysql + haproxy 를 이용한 클러스터 구성하기 (2)
  10. 2009.05.13 [링크] MySQL 에서 SELECT ... IN () 크기 제한

[MySQL] mysql.user 테이블에 계정 추가하기.

ITWeb/서버관리 2018.06.12 13:21

[MySQL 5.7 에서 User 등록]

INSERT INTO `user` (`Host`, `User`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Reload_priv`, `Shutdown_priv`, `Process_priv`, `File_priv`, `Grant_priv`, `References_priv`, `Index_priv`, `Alter_priv`, `Show_db_priv`, `Super_priv`, `Create_tmp_table_priv`, `Lock_tables_priv`, `Execute_priv`, `Repl_slave_priv`, `Repl_client_priv`, `Create_view_priv`, `Show_view_priv`, `Create_routine_priv`, `Alter_routine_priv`, `Create_user_priv`, `Event_priv`, `Trigger_priv`, `Create_tablespace_priv`, `ssl_type`, `ssl_cipher`, `x509_issuer`, `x509_subject`, `max_questions`, `max_updates`, `max_connections`, `max_user_connections`, `plugin`, `authentication_string`, `password_expired`, `password_last_changed`, `password_lifetime`, `account_locked`)



- 아래는 아마도 MySQL 5.6 이하 버전에서 동작 했던 것 같습니다.

SQL 문으로 계정 추가하기 위한 쿼리 입니다.

로컬 개발서버 기준으로 작성 된 것이기 때문에 보안에 문제가 될 수도 있으니 주의 하시기 바랍니다.

grant all 입니다.

mysql> INSERT INTO user

VALUES ('localhost', 'cloumon', password('cloumon'), 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', 0, 0, 0, 0, '', '', 'N');


Trackback 0 : Comment 0

[MySQL] JSON Type 사용하기

ITWeb/개발일반 2016.07.21 10:12

elasticsearch에서 문서 데이터를 처리 하다 보니 json format을 많이 사용하게 됩니다.

그래서 색인을 위한 데이터중 일부는 mysql 에 json 형태로 저장할 필요가 있는데요. mysql 5.7 부터 data type 으로 json 을 지원하고 있습니다.







    doc JSON NOT NULL,

    PRIMARY KEY (seq)



COLLATE utf8_general_ci;







# 등록

PrepareStatement.setObject(1, OBJECT);

# 조회

ResultSet.getObjecrt(1, "doc");

- JDBC 를 통해 DB 에 등록 할 때 value 값에 대한 Object를 Array 인지 Object 인지 확인해서 넣어 주어야 값이 원하는 형태로 저장 됩니다.

Elasticsearch Mapping)

"FIELD_NAME": { "type":"object", "enabled":false }

Trackback 0 : Comment 0

[MySQL] root password 리셋하기.

ITWeb/개발일반 2016.04.30 01:30

mysql 설치 후 root 암호가 기억나지 않을 경우 리셋 할때 사용하시면 됩니다.

mysql 데몬을 떠 있으면 중지 하시고 아래 옵션을 주고 실행해 준 후 root 로 접속해서 암호를 다시 변경 하시면 됩니다.

sudo mysqld_safe --skip-grant-tables &

Trackback 0 : Comment 0

[Mysql] create database syntax example.

ITWeb/개발일반 2016.03.09 15:30

뭘 이런걸 기록하는지 모르겠지만 나이 먹으면 이런것도 자꾸 까먹습니다.


기본 Syntax)

    [create_specification] ...

    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name


mysql> create database 생성할데이터베이스명 default character set utf8 collate utf8_general_ci;

tags : Create, DATABASE, MySQL
Trackback 0 : Comment 0

[MySQL] SQL JOINS 그림 설명.

ITWeb/개발일반 2016.02.17 12:05

RDBMS를 최근 잘 사용 안 했더니 join이 다시 궁금해져서 구글링 했습니다.

이미지로 설명이 잘 나와 있내요. :)

tags : Join, MySQL
Trackback 0 : Comment 0

11.6 Data Type Storage Requirements

ITWeb/개발일반 2014.03.07 17:27


11.6 Data Type Storage Requirements

The storage requirements for data vary, according to the storage engine being used for the table in question. Different storage engines use different methods for recording the raw data and different data types. In addition, some engines may compress the information in a given row, either on a column or entire row basis, making calculation of the storage requirements for a given table or column structure.

However, all storage engines must communicate and exchange information on a given row within a table using the same structure, and this information is consistent, irrespective of the storage engine used to write the information to disk.

This sections includes some guideliness and information for the storage requirements for each data type supported by MySQL, including details for the internal format and the sizes used by storage engines that used a fixed size representation for different types. Information is listed by category or storage engine.

The internal representation of a table has a maximum row size of 65,535 bytes, even if the storage engine is capable of supporting larger rows. This figure excludes BLOB or TEXT columns, which contribute only 9 to 12 bytes toward this size. For BLOB and TEXT data, the information is stored internally in a different area of memory than the row buffer. Different storage engines handle the allocation and storage of this data in different ways, according to the method they use for handling the corresponding types. For more information, see Chapter 14, Storage Engines, and Section E.7.4, “Limits on Table Column Count and Row Size”.

Storage Requirements for InnoDB Tables

See Section, “Physical Row Structure” for information about storage requirements for InnoDB tables.

Storage Requirements for NDBCLUSTER Tables


For tables using the NDBCLUSTER storage engine, there is the factor of 4-byte alignment to be taken into account when calculating storage requirements. This means that all NDB data storage is done in multiples of 4 bytes. Thus, a column value that would take 15 bytes in a table using a storage engine other than NDB requires 16 bytes in an NDB table. This requirement applies in addition to any other considerations that are discussed in this section. For example, in NDBCLUSTER tables, the TINYINT, SMALLINT, MEDIUMINT, and INTEGER (INT) column types each require 4 bytes storage per record due to the alignment factor.

An exception to this rule is the BIT type, which is not 4-byte aligned. In MySQL Cluster tables, a BIT(M) column takes M bits of storage space. However, if a table definition contains 1 or more BIT columns (up to 32 BIT columns), then NDBCLUSTER reserves 4 bytes (32 bits) per row for these. If a table definition contains more than 32 BIT columns (up to 64 such columns), then NDBCLUSTER reserves 8 bytes (that is, 64 bits) per row.

In addition, while a NULL itself does not require any storage space, NDBCLUSTER reserves 4 bytes per row if the table definition contains any columns defined as NULL, up to 32 NULL columns. (If a MySQL Cluster table is defined with more than 32 NULL columns up to 64 NULL columns, then 8 bytes per row is reserved.)

When calculating storage requirements for MySQL Cluster tables, you must also remember that every table using the NDBCLUSTER storage engine requires a primary key; if no primary key is defined by the user, then a hidden primary key will be created by NDB. This hidden primary key consumes 31-35 bytes per table record.

You may find the ndb_size.pl utility to be useful for estimating NDB storage requirements. This Perl script connects to a current MySQL (non-Cluster) database and creates a report on how much space that database would require if it used the NDBCLUSTER storage engine. See Section 17.4.23, “ndb_size.pl — NDBCLUSTER Size Requirement Estimator”, for more information.

Storage Requirements for Numeric Types

Data TypeStorage Required
BIGINT8 bytes
FLOAT(p)4 bytes if 0 <= p <= 24, 8 bytes if 25 <= p <= 53
FLOAT4 bytes
DECIMAL(M,D), NUMERIC(M,D)Varies; see following discussion
BIT(M)approximately (M+7)/8 bytes

Values for DECIMAL (and NUMERIC) columns are represented using a binary format that packs nine decimal (base 10) digits into four bytes. Storage for the integer and fractional parts of each value are determined separately. Each multiple of nine digits requires four bytes, and the leftover digits require some fraction of four bytes. The storage required for excess digits is given by the following table.

Leftover DigitsNumber of Bytes

Storage Requirements for Date and Time Types

Data TypeStorage Required
DATE3 bytes
TIME3 bytes
YEAR1 byte

For details about internal representation of temporal values, see MySQL Internals: Important Algorithms and Structures.

Storage Requirements for String Types

In the following table, M represents the declared column length in characters for nonbinary string types and bytes for binary string types. L represents the actual length in bytes of a given string value.

Data TypeStorage Required
CHAR(M)M × w bytes, 0 <= M <= 255, where w is the number of bytes required for the maximum-length character in the character set. See Section, “Physical Row Structure” for information about CHAR data type storage requirements for InnoDB tables.
BINARY(M)M bytes, 0 <= M <= 255
VARCHAR(M), VARBINARY(M)L + 1 bytes if column values require 0 – 255 bytes, L + 2 bytes if values may require more than 255 bytes
TINYBLOB, TINYTEXTL + 1 bytes, where L < 28
BLOB, TEXTL + 2 bytes, where L < 216
MEDIUMBLOB, MEDIUMTEXTL + 3 bytes, where L < 224
LONGBLOB, LONGTEXTL + 4 bytes, where L < 232
ENUM('value1','value2',...)1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum)
SET('value1','value2',...)1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum)

Variable-length string types are stored using a length prefix plus data. The length prefix requires from one to four bytes depending on the data type, and the value of the prefix is L (the byte length of the string). For example, storage for a MEDIUMTEXT value requires L bytes to store the value plus three bytes to store the length of the value.

To calculate the number of bytes used to store a particular CHAR, VARCHAR, or TEXT column value, you must take into account the character set used for that column and whether the value contains multi-byte characters. In particular, when using the utf8 Unicode character set, you must keep in mind that not all characters use the same number of bytes and can require up to three bytes per character. For a breakdown of the storage used for different categories of utf8 characters, see Section 10.1.10, “Unicode Support”.

VARCHAR, VARBINARY, and the BLOB and TEXT types are variable-length types. For each, the storage requirements depend on these factors:

  • The actual length of the column value

  • The column's maximum possible length

  • The character set used for the column, because some character sets contain multi-byte characters

For example, a VARCHAR(255) column can hold a string with a maximum length of 255 characters. Assuming that the column uses the latin1 character set (one byte per character), the actual storage required is the length of the string (L), plus one byte to record the length of the string. For the string 'abcd', L is 4 and the storage requirement is five bytes. If the same column is instead declared to use the ucs2 double-byte character set, the storage requirement is 10 bytes: The length of 'abcd' is eight bytes and the column requires two bytes to store lengths because the maximum length is greater than 255 (up to 510 bytes).

The effective maximum number of bytes that can be stored in a VARCHAR or VARBINARY column is subject to the maximum row size of 65,535 bytes, which is shared among all columns. For a VARCHAR column that stores multi-byte characters, the effective maximum number of characters is less. For example, utf8 characters can require up to three bytes per character, so a VARCHAR column that uses the utf8 character set can be declared to be a maximum of 21,844 characters. See Section E.7.4, “Limits on Table Column Count and Row Size”.

The NDBCLUSTER storage engine in MySQL 5.1 supports variable-width columns. This means that a VARCHAR column in a MySQL Cluster table requires the same amount of storage as it would using any other storage engine, with the exception that such values are 4-byte aligned. Thus, the string 'abcd' stored in a VARCHAR(50) column using the latin1 character set requires 8 bytes (rather than 6 bytes for the same column value in a MyISAM table). This represents a change in behavior from earlier versions of NDBCLUSTER, where a VARCHAR(50) column would require 52 bytes storage per record regardless of the length of the string being stored.

TEXT and BLOB columns are implemented differently in the NDB Cluster storage engine, wherein each row in a TEXT column is made up of two separate parts. One of these is of fixed size (256 bytes), and is actually stored in the original table. The other consists of any data in excess of 256 bytes, which is stored in a hidden table. The rows in this second table are always 2,000 bytes long. This means that the size of a TEXT column is 256 if size <= 256 (where size represents the size of the row); otherwise, the size is 256 + size + (2000 – (size – 256) % 2000).

The size of an ENUM object is determined by the number of different enumeration values. One byte is used for enumerations with up to 255 possible values. Two bytes are used for enumerations having between 256 and 65,535 possible values. See Section 11.4.4, “The ENUM Type”.

The size of a SET object is determined by the number of different set members. If the set size is N, the object occupies (N+7)/8 bytes, rounded up to 1, 2, 3, 4, or 8 bytes. A SET can have a maximum of 64 members. See Section 11.4.5, “The SET Type”.

tags : data, MySQL, size, type
Trackback 0 : Comment 0

[Mysql] 5.6.1 source install.. ncurses error...

ITWeb/개발일반 2013.08.22 14:52

ncurses-devel 설치를 하시면 되구요.

설치 후에도 에러가 나면 cmake 시에 path 를 잡아 주시면 됩니다.


tags : MySQL, ncurses
Trackback 0 : Comment 0

[MySQL] Mac OSX 에서 MySQL 설치하기.

ITWeb/서버관리 2013.04.30 11:01

맥에서 mysql 설치는 쉽게 되어 있더군요.

저는 아래 처럼 설치 했습니다.

- 다운로드(MAC) : http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.11-osx10.7-x86_64.tar.gz/from/http://cdn.mysql.com/

- 설치 위치 : ~/server/app/mysql

- cd ~/server/app/

- tar -xvzf mysql-5.6.11-osx10.7-x86_64.tar.gz

- ln -s mysql-5.6.11-osx10.7-x86_64 mysql

- cd mysql


- scripts/mysql_install_db --user=실행계정명

- sudo cp support-files/my-default.cnf /etc/my.cnf

- bin/mysqld_safe --user=실행계정명 &

tags : Mac OSX, MySQL
Trackback 0 : Comment 0

[Hadoop] hadoop + hive + mysql + haproxy 를 이용한 클러스터 구성하기

ITWeb/Hadoop일반 2013.04.26 15:29

hadoop, hive 에 대해서는 우선 책이나 다른 사이트들을 통해서 충분히 이해하고 구성을 하시는게 좋습니다.

저 역시 아무런 사전 지식 없이 웹에 나와 있는 문서들만 보고 설치를 해보고 돌려 보았지만, 이런게 크게 도움이 되지 않습니다.

적어도 책 두권 이상은 정독하시고 샘플 코드들도 돌려 보시면 많은 도움이 됩니다.

또한 소스코드 까지 볼수 있다면 더욱 좋습니다.

아래는 일반 웹에 문서들과 제가 이해한 내용을 바탕으로 재구성한 내용입니다.

hadoop, hive, mysql, haproxy 등 모두 서버 설정 정보가 상당 합니다.

저 역시 모든 설정에 대해서 알고 있지 않기 때문에 사이트를 참고 하여 작성 하였습니다.


- virtual box

version 4.2.12


- centos

version 6.4



- hadoop

version : 1.0.4



- mysql

version : 5.6.11


source code 선택


- hive

version : 0.9.0



- haproxy

version : 1.4.23



- jdk

version : 1.6.0_45


[VirtualBox 이미지생성 및 CentOS 설치]

- http://jmnote.com/wiki/VirtualBox_CentOS_6_%EC%84%A4%EC%B9%98

- http://wiki.centos.org/HowTos/Virtualization/VirtualBox

- VM


hive1 (mysql master)

hive2 (mysql slave)






- VM 이미지 생성 후 각 이미지의 /etc/hosts 에 DNS 등록

[VirtualBox Network 설정]

- 브릿지, nat, 호스트전용 : http://searchme.tistory.com/16

- 브릿지, 호스트전용 : http://blog.naver.com/PostView.nhn?blogId=icysword&logNo=140135319512

- 첫번째와 같이 해도 되지만 개발환경에서 약간 통신 상에 문제가 있는 듯 하여 순서를 아래와 같이 변경 합니다.

- 1. NAT / 2. 브릿지 / 3. 호스트전용

- VM 이미지를 하나 만들어서 설치에 필요한 파일과 환경 구성을 한 다음 복제(완전복제)를 하십시오.

[Hadoop 설치 - namenode]

- hadoop user/group 생성

. https://www.google.co.kr/url?sa=t&rct=j&q=&esrc=s&source=web&cd=8&cad=rja&ved=0CFoQtwIwBw&url=http%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DNKxjmDQYfJk&ei=tQh6UeyWJ-eOigeG4oCADw&usg=AFQjCNFJjajIFd7SrrgNuG5UdW0L7OECLQ&sig2=-28omSER_InldSy-yyIqaw&bvm=bv.45645796,d.aGc

- sudo groupadd hadoop

- sudo useradd hadoop -m -b /home/hadoop -d /home/hadoop -s /bin/bash

- sudo vi /etc/passwd 

        - 아래 설정의 경우 shadow password 에 대한 내용으로 계정 자체에 암호 생성을 하지 않을 경우 아래 설정은 진행 하지않아도 무방 함.


hadoop:*:1:1:hadoop Services:/home/hadoop:/bin/bash


hadoop::1:1:hadoop Services:/home/hadoop:/bin/bash

- ssh 연결을 위한 keygen (hadoop 계정)


- hadoop 압축해제

. 설치경로 : /home/hadoop/app/hadoop


vi conf/hadoop-env.sh

export JAVA_HOME=/home/hadoop/app/jdk

- core-site.xml 설정





- hdfs-site.xml

























- masters

. secondarynamenode 서버 정보를 작성한다.


- slaves

. datanode 서버 정보를 작성한다.




- mapred-site.xml





















[Hadoop 설치 - secondarynamenode + datanode]

- namenode 에 구성한 모든 정보를 그대로 복사한다.

- VM 이미지 완전복제를 통해서 그대로 구성해도 됨.

[Hadoop 실행 - namenode]

- 스크립트 실행은 namenode 에서만 수행 한다.

- (최초 실행 시) bin/hadoop namenode -format

- bin/start-all.sh

- 모든 구성 서버들이 정상적으로 실행 되었는지 로그 확인.

- namenode, secondarynamenode, datanode, jobtracker, tasktracker 데몬이 떠야 함.

- http://namenode:50070/dfshealth.jsp

- http://namenode:50030/jobtracker.jsp

- bin/stop-all.sh

[MySQL 설치]

- 설치 경로와 mysql user 만 변경해서 설치 (아래 링크 참고)

- http://www.yongbok.net/blog/how-to-install-mysql-5-5-x-from-source/

- http://blog.beany.co.kr/archives/2648

- 설치 경로 : /home/hadoop/app/mysql

- mysql user : hadoop

[MySQL - cmake/make/install]

cmake \

-DCMAKE_INSTALL_PREFIX=/home/hadoop/app/mysql \

-DMYSQL_UNIX_ADDR=/home/hadoop/app/mysql/mysql.sock \


-DDEFAULT_COLLATION=utf8_general_ci \


-DMYSQL_DATADIR=/home/hadoop/app/mysql/db \









-DMYSQL_USER=hadoop \



make && make install

[MySQL - my.cnf 수정]

- 위치 : /home/hadoop/app/mysql/my.cnf

- vi my.cnf




- sudo cp my.cnf /etc/my.cnf

[MySQL - 기본DB 생성]

- scripts/mysql_install_db --user=hadoop --defaults-file=//home/hadoop/app/mysql/my.cnf

- defaults-file 을 지정하지 않을 경우 /etc/my.cnf 파일 참조함.

[MySQL 실행]

- bin/mysqld_safe &

- bin/mysql -uroot

- use mysql;

- insert into user values(....);

- update user set password=password('....') where user='xxxx' and host='xxxx'

- flush privileges;

[Hive 설치]

- http://blog.beany.co.kr/archives/1519

- hive 설치는 hadoop 이 설치된 서버와 별도로 설치 해도 됨.

- 단, hadoop 과 통신이 가능한 network 상에 있어야 함.

- 설치 경로 : /home/hadoop/app/hive

[Hive 용 HDFS 생성]

- namenode 에서 shell 로 실행

- bin/hadoop fs -mkdir /tmp

- bin/hadoop fs -mkdir /user/hive/warehouse

- bin/hadoop fs -chmod g+w /tmp

- bin/hadoop fs -chmod g+w /user/hive/warehouse

[Hive hive-env.sh 설정]

- cp conf/hive-env.sh.template conf/hive-env.sh


[Hive metastore 설정]

- conf/hive-site.xml




<description>hdfs path for hive metastore</description>









<description>JDBC connect string for a JDBC metastore</description>





<description>Driver class name for a JDBC metastore</description>





<description>username to use against metastore database</description>





<description>password to use against metastore database</description>










[Hive JDBC Driver 등록]

- http://www.mysql.com/downloads/connector/j/

- mysql-connector-java-5.1.24.jar 를 hive/lib 아래로 복사

[Hive 실행]

- bin/start-hive.sh



/usr/bin/nohup $HIVE_HOME/bin/hive --service hiveserver > $HIVE_HOME/logs/hive.log 2>&1 < /dev/null &

echo $! > $HIVE_HOME/bin/hive-server.pid

- bin/stop-hive.sh


/bin/cat /home/hadoop/app/hive/bin/hive-server.pid | xargs kill -9

[Hive CLI]

- bin/hive

hive> quit;

[Hive Metastore DB 구성]

- mysql -uroot

mysql> create database metastore_db;

mysql> use metastore_db;

mysql> source /home/hadoop/app/hive/scripts/metastore/upgrade/mysql/hive-schema-0.9.0.mysql.sql

mysql> show tables;

[Hive 데이터 등록]

- vi textfile




- bin/hive

hive> create table citi (citing int, cited int) row format delimited fields terminated by ',' stored as textfile;

- mysql -uroot

mysql> use metastore_db;

mysql> select * from TBLS\G;

[Hive HA 구성]

- hive 1번 서버 구성과 같은 구성으로 hive 2번 서버를 구성한다.

- 구성된 서버들을 haproxy 로 묶는다.

[HAProxy 설치]

- http://haproxy.1wt.eu/

- https://github.com/langpavel/haproxy-doc

- http://linuxadminzone.com/install-and-configure-haproxy-the-software-based-loadbalancer-in-ubuntu/

- http://www.sestory.net/73

- http://helloworld.naver.com/helloworld/284659

[HAProxy /etc/haproxy/haproxy.cfg 샘플]


        log  local0

        maxconn 1024

        uid 99

        gid 99




        log    global

        mode    http

        option httplog

        option dontlognull

        retries 3

        option redispatch

        maxconn 1024

        contimeout      5000

        clitimeout      50000

        srvtimeout      50000


listen  hive-service

        option forwardfor

        option httpchk OPTIONS /health_check.html

        stats enable

        stats refresh 10s 

        stats hide-version

        stats scope   .   


        server  web1 www.naver.com:80 check inter 2000 fall 3

        server  web2 www.daum.net:80 check inter 2000 fall 3

tags : ha, hadoop, haproxy, HIVE, MySQL
Trackback 0 : Comments 2

[링크] MySQL 에서 SELECT ... IN () 크기 제한

ITWeb/서버관리 2009.05.13 20:03
Database 간 차이가 있지요.
MySQL 에서는 packet size 로 처리를 하고 있군요.

Trackback 0 : Comment 0