Ubuntu 18.04 : MySQL 5.7.23 컴파일, 설치

Database 2018.10.08 03:09 Posted by 파란크리스마스

출처

Ubuntu server에 mysql 5.5.x 설치하기 -pupustory@- - Tistory
[Linux/CentOS] Mysql을 컴파일해서 설치해보자 - AT BLOG

리눅스 정보 조회

출처 : 리눅스 종류 확인, 리눅스 버전 확인 - 제타위키

$ cat /etc/*-release | uniq
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=14.04
DISTRIB_CODENAME=trusty
DISTRIB_DESCRIPTION="Ubuntu 14.04.3 LTS"
NAME="Ubuntu"
VERSION="14.04.3 LTS, Trusty Tahr"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 14.04.3 LTS"
VERSION_ID="14.04"
HOME_URL="http://www.ubuntu.com/"
SUPPORT_URL="http://help.ubuntu.com/"
BUG_REPORT_URL="http://bugs.launchpad.net/ubuntu/"

리눅스 비트 확인

출처 : 리눅스 32비트 64비트 확인 - 제타위키

$ getconf LONG_BIT
64

관련패키지 설치

$ sudo apt-get update
$ sudo apt-get install gcc g++ libncurses5-dev libxml2-dev openssl libssl-dev curl libcurl4-openssl-dev libjpeg-dev libpng-dev libfreetype6-dev libsasl2-dev autoconf libncurses5-dev

MySQL 계정 만들기

$ sudo groupadd mysql
$ sudo useradd -g mysql -s /bin/bash -m mysql

cmake 설치

$ sudo apt-get install cmake

MySQL 소스 다운로드, 압축해제, 컴파일, 설치

$ wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-boost-5.7.23.tar.gz
$ tar xvf mysql-boost-5.7.23.tar.gz
$ cd mysql-5.7.23
$ sudo cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql57 \
-DMYSQL_DATADIR=/usr/local/mysql57/data \
-DMYSQL_UNIX_ADDR=/usr/local/mysql57/mysql.sock \
-DSYSCONFDIR=/usr/local/mysql57 \
-DMYSQL_TCP_PORT=3306 \
-DMYSQL_USER=mysql \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DDOWNLOAD_BOOST=0 -DWITH_BOOST=./boost
$ sudo make
$ sudo make install

환경설정

$ sudo vi /usr/local/mysql57/my.cnf

[mysqld]
# port=3306
# basedir=/usr/local/mysql57
# datadir=/usr/local/mysql57/data
# pid-file=/usr/local/mysql57/mysqld.pid
# log_error=/usr/local/mysql57/mysql_error.log
# lc-messages-dir=/usr/local/mysql57/share

init_connect=SET collation_connection = utf8_general_ci
init_connect=SET NAMES utf8
character-set-server=utf8
collation-server=utf8_general_ci
# table_cache=1024
max_connections=2048
max_user_connections=500
max_connect_errors=10000
wait_timeout=300
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 5M
slow_query_log
long_query_time=3
max_allowed_packet=16M
sort_buffer_size = 2M
# skip-innodb
skip-name-resolve
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

[mysql]
default-character-set=utf8

[client]
default-character-set=utf8

MySQL 설치 디렉토리 mysql 계정으로 권한 수정

$ sudo chown -R mysql:mysql /usr/local/mysql57

MySQL 데이터베이스 초기화 (mysql 계정으로 실행)

$ cd /usr/local/mysql57
$ bin/mysql_install_db --no-defaults --user=mysql --datadir=/usr/local/mysql57/data --basedir=/usr/local/mysql57 -v
2018-10-03 15:26:47 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
2018-10-03 15:26:47 [NOTE]    Creating data directory /usr/local/mysql57/data
2018-10-03 15:26:47 [NOTE]    Generating random password to /home/mysql/.mysql_secret...done.
2018-10-03 15:26:47 [NOTE]    Executing /usr/local/mysql57/bin/mysqld --no-defaults --bootstrap --datadir=/usr/local/mysql57/data --lc-messages-dir=/usr/local/mysql57/share --lc-messages=en_US --basedir=/usr/local/mysql57
2018-10-03 15:26:48 [NOTE]    Creating system tables...done.
2018-10-03 15:26:48 [NOTE]    Filling system tables with data...done.
2018-10-03 15:26:49 [NOTE]    Filling help table with data...done.
2018-10-03 15:26:49 [NOTE]    Creating user for internal session service...done.
2018-10-03 15:26:49 [NOTE]    Creating default user root@localhost
2018-10-03 15:26:49 [NOTE]    Creating default proxy root@localhost
2018-10-03 15:26:49 [NOTE]    Creating sys schema
2018-10-03 15:26:49 [NOTE]    done.
2018-10-03 15:26:51 [WARNING] The bootstrap log isn't empty:
2018-10-03 15:26:51 [WARNING] 2018-10-03T06:26:47.586650Z 0 [Warning] --bootstrap is deprecated. Please consider using --initialize instead

2018-10-03 15:26:51 [NOTE]    Generating SSL Certificates

MySQL 서비스 등록

서비스 mysqld 파일 복사

$ sudo cp /usr/local/mysql57/support-files/mysql.server /etc/init.d/mysqld57

mysqld 수정

$ sudo vi /etc/init.d/mysqld57

mysqld파일을 열어서 basedir에 mysql이 설치된 디렉토리와 데이터 디렉토리(datadir)를 설정한다.

basedir=/usr/local/mysql57
datadir=/usr/local/mysql57/data

mysqld 서비스 등록

$ sudo update-rc.d mysqld57 defaults

mysqld 서비스 실행

$ sudo systemctl start mysqld57.service
$ sudo systemctl status mysqld57.service
● mysqld57.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/init.d/mysqld57; generated)
   Active: active (running) since Wed 2018-10-03 15:37:07 KST; 6min ago
     Docs: man:systemd-sysv-generator(8)
  Process: 1042 ExecStart=/etc/init.d/mysqld57 start (code=exited, status=0/SUCCESS)
    Tasks: 28 (limit: 4915)
   CGroup: /system.slice/mysqld57.service
           ├─1071 /bin/sh /usr/local/mysql57/bin/mysqld_safe --datadir=/usr/local/mysql57/data --pid-file=/usr/local/mysql57/data/bluesa
           └─1469 /usr/local/mysql57/bin/mysqld --basedir=/usr/local/mysql57 --datadir=/usr/local/mysql57/data --plugin-dir=/usr/local/m

10월 03 15:37:06 bluesanta-ubuntu systemd[1]: Starting LSB: start and stop MySQL...
10월 03 15:37:06 bluesanta-ubuntu mysqld57[1042]: Starting MySQL
10월 03 15:37:07 bluesanta-ubuntu mysqld57[1042]: . *
10월 03 15:37:07 bluesanta-ubuntu systemd[1]: Started LSB: start and stop MySQL.
$ sudo systemctl stop mysqld57.service

root 암호 초기화

$ bin/mysqld_safe --skip-grant-tables &
[1] 21756
mysql@bluesanta-ubuntu:/usr/local/mysql57$ 2018-10-03T06:32:29.844112Z mysqld_safe Logging to '/usr/local/mysql57/data/bluesanta-ubuntu.err'.
2018-10-03T06:32:29.873395Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql57/data

mysql@bluesanta-ubuntu:/usr/local/mysql57$ bin/mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.23-log Source distribution

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set authentication_string = password('sqldba') where user = 'root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

ERROR 1820 (HY000) 오류 해결

mysql> select 1;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> SET PASSWORD = PASSWORD('sql');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql$gt; select 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql>

사용자 추가

mysql> create user 'user1'@'%' identified by 'userpw';
mysql> grant all privileges on *.* to 'user1'@'%' with grant option;
mysql> flush privileges;

사용자 추가

create user 'terecal'@'%' identified by '****';
GRANT ALL privileges ON terecal_db.* TO 'terecal'@'%';
flush privileges;

Ubuntu 18.04 : MySQL 8.0.12 컴파일, 설치

Database 2018.10.08 03:09 Posted by 파란크리스마스

출처

리눅스 정보 조회

출처 : 리눅스 종류 확인, 리눅스 버전 확인 - 제타위키

$ cat /etc/*-release | uniq
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=18.04
DISTRIB_CODENAME=bionic
DISTRIB_DESCRIPTION="Ubuntu 18.04.1 LTS"
NAME="Ubuntu"
VERSION="18.04.1 LTS (Bionic Beaver)"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 18.04.1 LTS"
VERSION_ID="18.04"
HOME_URL="https://www.ubuntu.com/"
SUPPORT_URL="https://help.ubuntu.com/"
BUG_REPORT_URL="https://bugs.launchpad.net/ubuntu/"
PRIVACY_POLICY_URL="https://www.ubuntu.com/legal/terms-and-policies/privacy-policy"
VERSION_CODENAME=bionic
UBUNTU_CODENAME=bionic

리눅스 비트 확인

출처 : 리눅스 32비트 64비트 확인 - 제타위키

$ getconf LONG_BIT
64

관련패키지 설치

$ sudo apt-get update
$ sudo apt-get install gcc g++ libncurses5-dev libxml2-dev openssl libssl-dev curl libcurl4-openssl-dev libjpeg-dev libpng-dev libfreetype6-dev libsasl2-dev autoconf libncurses5-dev

cmake 설치

$ sudo apt-get install cmake

MySQL 계정 만들기

$ sudo groupadd mysql
$ sudo useradd -g mysql -s /bin/bash -m mysql

MySQL 소스 다운로드

$ wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.12.tar.gz

MySQL 소스 압축풀기

$ tar xvf mysql-8.0.12.tar.gz

MySQL 컴파일 및 설치

$ cd mysql-8.0.12/
$ sudo cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DSYSCONFDIR=/usr/local/mysql \
-DMYSQL_TCP_PORT=3306 \
-DMYSQL_USER=mysql \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/mysql/boost
$ sudo make
$ sudo make install

MySQL 설치 디렉토리 mysql 계정으로 권한 수정

$ sudo chown -R mysql:mysql /usr/local/mysql

환경설정

$ vi /usr/local/mysql/my.cnf

[mysqld]
bind-address=0.0.0.0
port=3306
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
# pid-file=/usr/local/mysql/mysqld.pid
# log_error=/usr/local/mysql/mysql_error.log
# lc-messages-dir=/usr/local/mysql/share

init_connect=SET collation_connection = utf8_general_ci
init_connect=SET NAMES utf8
character-set-server=utf8
collation-server=utf8_general_ci
# table_cache=1024
max_connections=2048
max_user_connections=500
max_connect_errors=10000
wait_timeout=300
# query_cache_type = 1
# query_cache_size = 128M
# query_cache_limit = 5M
slow_query_log
long_query_time=3
max_allowed_packet=16M
sort_buffer_size = 2M
# skip-innodb
skip-name-resolve
# sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

[mysql]
default-character-set=utf8

[client]
default-character-set=utf8

MySQL 데이터베이스 초기화 (mysql 계정으로 실행)

$ cd /usr/local/mysql
$ bin/mysqld --defaults-file=/usr/local/mysql/my.cnf --initialize --console --user=mysql --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql
2018-10-03T03:17:05.650419Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.12) initializing of server in progress as process 5644
2018-10-03T03:17:08.797874Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: q<chLu;Cy2yY
2018-10-03T03:17:10.426427Z 0 [System] [MY-013170] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.12) initializing of server has completed

MySQL 종료 (프로세스가 존재하는 경우)

$ bin/mysqld stop
2018-10-03T01:59:45.345444Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.12) starting as process 3662
2018-10-03T01:59:45.756978Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2018-10-03T01:59:45.761934Z 0 [ERROR] [MY-010147] [Server] Too many arguments (first extra is 'stop').
2018-10-03T01:59:45.761967Z 0 [Warning] [MY-010952] [Server] The privilege system failed to initialize correctly. If you have upgraded your server, make sure you're executing mysql_upgrade to correct the issue.
2018-10-03T01:59:45.762056Z 0 [ERROR] [MY-010119] [Server] Aborting
2018-10-03T01:59:47.444146Z 0 [System] [MY-010910] [Server] /usr/local/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.12)  Source distribution.

root 암호 초기화

$ bin/mysqld_safe --skip-grant-tables &
[1] 3745
mysql@bluesanta-ubuntu:/usr/local/mysql$ 2018-10-03T03:20:12.286866Z mysqld_safe Logging to '/usr/local/mysql/data/bluesanta-ubuntu.err'.
2018-10-03T03:20:12.311924Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

mysql@bluesanta-ubuntu:/usr/local/mysql$ bin/mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.12 Source distribution

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.03 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'sql';
Query OK, 0 rows affected (0.08 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql> quit
Bye

MySQL 서비스 등록

서비스 mysqld 파일 복사

$ sudo cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

mysqld 수정

$ sudo vi /etc/init.d/mysqld

mysqld파일을 열어서 basedir에 mysql이 설치된 디렉토리와 데이터 디렉토리(datadir)를 설정한다.

basedir=/usr/local/mysql
datadir=/usr/local/mysql/data

mysqld 서비스 등록

$ sudo update-rc.d mysqld defaults
 Adding system startup for /etc/init.d/mysqld ...
   /etc/rc0.d/K20mysqld -> ../init.d/mysqld
   /etc/rc1.d/K20mysqld -> ../init.d/mysqld
   /etc/rc6.d/K20mysqld -> ../init.d/mysqld
   /etc/rc2.d/S20mysqld -> ../init.d/mysqld
   /etc/rc3.d/S20mysqld -> ../init.d/mysqld
   /etc/rc4.d/S20mysqld -> ../init.d/mysqld
   /etc/rc5.d/S20mysqld -> ../init.d/mysqld

mysqld 서비스 실행

$ sudo systemctl start mysqld.service
$ sudo systemctl status mysqld.service
● mysqld.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/init.d/mysqld; generated)
   Active: active (running) since Wed 2018-10-03 11:58:26 KST; 32s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 4863 ExecStop=/etc/init.d/mysqld stop (code=exited, status=0/SUCCESS)
  Process: 4887 ExecStart=/etc/init.d/mysqld start (code=exited, status=0/SUCCESS)
    Tasks: 38 (limit: 4915)
   CGroup: /system.slice/mysqld.service
           ├─4895 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/bluesanta-ubuntu.pid
           └─4982 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysq
 
10월 03 11:58:25 bluesanta-ubuntu systemd[1]: Starting LSB: start and stop MySQL...
10월 03 11:58:25 bluesanta-ubuntu mysqld[4887]: Starting MySQL
10월 03 11:58:26 bluesanta-ubuntu mysqld[4887]: . *
10월 03 11:58:26 bluesanta-ubuntu systemd[1]: Started LSB: start and stop MySQL.
$ sudo systemctl stop mysqld.service

ERROR 1820 (HY000) 오류 해결

mysql> select 1;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> SET PASSWORD = PASSWORD('sql');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql$gt; select 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql>

사용자 추가

mysql> create user 'user1'@'%' identified by 'userpw';
mysql> grant all privileges on *.* to 'user1'@'%' with grant option;
mysql> flush privileges;

사용자 추가

create user 'terecal'@'%' identified by '****';
GRANT ALL privileges ON terecal_db.* TO 'terecal'@'%';
flush privileges;

Ubuntu 리눅스 서버에 MySQL 5.7.x 설치

Database 2017.04.18 13:01 Posted by 파란크리스마스

Ubuntu 리눅스 서버에 MySQL 5.7.x 설치

출처 : Ubuntu server에 mysql 5.5.x 설치하기 -pupustory@- - Tistory
[Linux/CentOS] Mysql을 컴파일해서 설치해보자 - AT BLOG

리눅스 정보 조회

출처 : 리눅스 종류 확인, 리눅스 버전 확인 - 제타위키

$ cat /etc/*-release | uniq
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=14.04
DISTRIB_CODENAME=trusty
DISTRIB_DESCRIPTION="Ubuntu 14.04.3 LTS"
NAME="Ubuntu"
VERSION="14.04.3 LTS, Trusty Tahr"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 14.04.3 LTS"
VERSION_ID="14.04"
HOME_URL="http://www.ubuntu.com/"
SUPPORT_URL="http://help.ubuntu.com/"
BUG_REPORT_URL="http://bugs.launchpad.net/ubuntu/"

리눅스 비트 확인

출처 : 리눅스 32비트 64비트 확인 - 제타위키

$ getconf LONG_BIT
64

관련패키지 설치

$ sudo apt-get update
$ sudo apt-get install gcc g++ libncurses5-dev libxml2-dev openssl libssl-dev curl libcurl4-openssl-dev libjpeg-dev libpng-dev libfreetype6-dev libsasl2-dev autoconf libncurses5-dev

MySQL 계정 만들기

$ sudo groupadd mysql
$ sudo useradd -g mysql -s /bin/bash -m mysql

cmake 설치

$ sudo apt-get install cmake

cmake 수동 설치(option)

$ wget http://www.cmake.org/files/v2.8/cmake-2.8.4.tar.gz
$ tar xvf cmake-2.8.4.tar.gz 
$ cd cmake-2.8.4
$ ./bootstrap
$ make all
$ sudo make install

MySQL 소스 다운로드

$ wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.20.tar.gz

MySQL 소스 압축풀기

$ tar xvf mysql-5.7.20.tar.gz

MySQL 컴파일 및 설치

$ cd mysql-5.7.20
$ sudo cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DSYSCONFDIR=/etc \
-DMYSQL_TCP_PORT=3306 \
-DMYSQL_USER=mysql \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/mysql/boost
$ sudo make
$ sudo make install

MySQL 데이터베이스 초기화

$ cd /usr/local/mysql
$ echo "./bin/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data -v"
$ sudo ./bin/mysql_install_db --no-defaults --user=mysql --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql -v
2017-09-20 20:17:58 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
2017-09-20 20:17:58 [NOTE]    Using existing directory /usr/local/mysql/data
2017-09-20 20:17:58 [NOTE]    Generating random password to /root/.mysql_secret...done.
2017-09-20 20:17:58 [NOTE]    Setting file ownership to mysql
2017-09-20 20:17:58 [NOTE]    Executing /usr/local/mysql/bin/mysqld --no-defaults --bootstrap --datadir=/usr/local/mysql/data --lc-messages-dir=/usr/local/mysql/share --lc-messages=en_US --basedir=/usr/local/mysql
2017-09-20 20:18:00 [NOTE]    Creating system tables...done.
2017-09-20 20:18:00 [NOTE]    Filling system tables with data...done.
2017-09-20 20:18:01 [NOTE]    Filling help table with data...done.
2017-09-20 20:18:01 [NOTE]    Creating user for internal session service...done.
2017-09-20 20:18:01 [NOTE]    Creating default user root@localhost
2017-09-20 20:18:01 [NOTE]    Creating default proxy root@localhost
2017-09-20 20:18:01 [NOTE]    Creating sys schema
2017-09-20 20:18:02 [NOTE]    done.
2017-09-20 20:18:03 [WARNING] The bootstrap log isn't empty:
2017-09-20 20:18:03 [WARNING] 2017-09-20T11:17:58.125569Z 0 [Warning] --bootstrap is deprecated. Please consider using --initialize instead
2017-09-20T11:17:58.126616Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2017-09-20T11:17:58.126627Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)

2017-09-20 20:18:03 [NOTE]    Generating SSL Certificates

MySQL 설치 디렉토리 mysql 계정으로 권한 수정

$ sudo chown -R mysql:mysql /usr/local/mysql

환경설정

$ vi /usr/local/mysql/my.cnf

[mysqld]
# port=3306
# basedir=/usr/local/mysql
# datadir=/usr/local/mysql/data
# pid-file=/usr/local/mysql/mysqld.pid
# log_error=/usr/local/mysql/mysql_error.log
# lc-messages-dir=/usr/local/mysql/share

init_connect=SET collation_connection = utf8_general_ci
init_connect=SET NAMES utf8
character-set-server=utf8
collation-server=utf8_general_ci
# table_cache=1024
max_connections=2048
max_user_connections=500
max_connect_errors=10000
wait_timeout=300
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 5M
slow_query_log
long_query_time=3
max_allowed_packet=16M
sort_buffer_size = 2M
# skip-innodb
skip-name-resolve
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

[mysql]
default-character-set=utf8

[client]
default-character-set=utf8

MySQL 서비스 등록

서비스 mysqld 파일 복사

$ sudo cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

mysqld 수정

$ sudo vi /etc/init.d/mysqld

mysqld파일을 열어서 basedir에 mysql이 설치된 디렉토리와 데이터 디렉토리(datadir)를 설정한다.

basedir=/usr/local/mysql
datadir=/usr/local/mysql/data

mysqld 서비스 등록

$ sudo update-rc.d mysqld defaults
 Adding system startup for /etc/init.d/mysqld ...
   /etc/rc0.d/K20mysqld -> ../init.d/mysqld
   /etc/rc1.d/K20mysqld -> ../init.d/mysqld
   /etc/rc6.d/K20mysqld -> ../init.d/mysqld
   /etc/rc2.d/S20mysqld -> ../init.d/mysqld
   /etc/rc3.d/S20mysqld -> ../init.d/mysqld
   /etc/rc4.d/S20mysqld -> ../init.d/mysqld
   /etc/rc5.d/S20mysqld -> ../init.d/mysqld

mysqld 서비스 실행

$ sudo service mysqld start
Starting MySQL
. * 
$ sudo service mysqld status
 * MySQL running (29565)
$ sudo service mysqld stop
Shutting down MySQL
. * 

root 암호 초기화

$ sudo ./bin/mysqld_safe --skip-grant-tables &
[1] 31008
mysql@localhost:/usr/local/mysql$ 2017-04-18T09:21:34.035860Z mysqld_safe Logging to '/usr/local/mysql/data/localhost.err'.
2017-04-18T09:21:34.050764Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

mysql@localhost:/usr/local/mysql$ ./bin/mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18-log Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set authentication_string = password('sql') where user = 'root';

Query OK, 1 row affected, 1 warning (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

ERROR 1820 (HY000) 오류 해결

mysql> select 1;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> SET PASSWORD = PASSWORD('sql');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql$gt; select 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql>

사용자 추가

mysql> create user 'user1'@'%' identified by 'userpw';
mysql> grant all privileges on *.* to 'user1'@'%' with grant option;
mysql> flush privileges;

사용자 추가

create user 'terecal'@'%' identified by '****';
GRANT ALL privileges ON terecal_db.* TO 'terecal'@'%';
flush privileges;

MySQL 레코드 자동 생성

Database 2017.04.16 19:04 Posted by 파란크리스마스

MySQL 레코드 자동 생성

출처 : MySQL Row Generator - Use The Index, Luke
[MySQL] 무작위 샘플 데이터 만들기

View 생성

CREATE OR REPLACE VIEW generator_16
AS SELECT 0 n UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL 
   SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL
   SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL
   SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
   SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL 
   SELECT 15;

CREATE OR REPLACE VIEW generator_256
AS SELECT ( ( hi.n << 4 ) | lo.n ) AS n
     FROM generator_16 lo, generator_16 hi;

CREATE OR REPLACE VIEW generator_4k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
     FROM generator_256 lo, generator_16 hi;

CREATE OR REPLACE VIEW generator_64k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
     FROM generator_256 lo, generator_256 hi;

CREATE OR REPLACE VIEW generator_1m
AS SELECT ( ( hi.n << 16 ) | lo.n ) AS n
     FROM generator_64k lo, generator_16 hi;

0부터 30까지 레코드 만들기

select *
  from generator_256
 where n < 31;

해당 월의 마지막 날까지 레코드 생성

MySQL의 내장함수 current_date로 오늘날짜를 가져와서 날짜함수 LAST_DAY로 해당 월의 마지막 일을 얻어 오고, DATE_FORMAT 함수의 %d로 일수를 조회 한 후에 generator_256 뷰를 이용해서 일수만큼 레코드를 생성해서 INTERVAL 을 이용해서 마지막 일자에서 n 필드의 값으로 빼면서 해당 월의 일수 만큼 레코드를 생성한다.

SELECT LAST_DAY(current_date) - INTERVAL n DAY dt
  FROM generator_256
 WHERE n < DATE_FORMAT(LAST_DAY(current_date), '%d');

해당 월의 통계 데이터 생성

select a.dt, IFNULL(SUM(b.level_request), 0)
  from (  SELECT LAST_DAY(current_date) - INTERVAL n DAY dt
            FROM generator_256
           WHERE n < DATE_FORMAT(LAST_DAY(current_date), '%d')
       ) a LEFT OUTER JOIN group2 b
       ON a.dt = DATE(b.date_request)
 group by a.dt
 order by a.dt

MySQL - 일별통계, 주간통계, 월간통계

Database/SQL 2017.04.09 18:11 Posted by 파란크리스마스

MySQL - 일별통계, 주간통계, 월간통계

출처 : Error related to only_full_group_by when executing a query in MySql - Stack Overflow
[Mysql] DB일간 주간 월간 년간 통계 | database

sample 테이블 생성

CREATE TABLE `test_st` (
  `reg_date` date NOT NULL,
  `value` int(11) NOT NULL
);

INSERT INTO `test_st` (`reg_date`, `value`) VALUES
  ('2017-03-01', 4),
  ('2017-03-08', 2),
  ('2017-03-15', 4),
  ('2017-03-22', 4),
  ('2017-04-01', 9),
  ('2017-04-02', 1),
  ('2017-04-04', 2),
  ('2017-04-09', 4),
  ('2017-04-11', 2),
  ('2017-04-16', 4),
  ('2017-04-18', 5),
  ('2017-04-23', 3),
  ('2017-04-25', 4),
  ('2017-04-30', 4),
  ('2017-04-01', 5);

MySQL - 일별통계

SELECT DATE(`reg_date`) AS `date`, 
       sum(`value`)
  FROM test_st
 GROUP BY `date`;

MySQL - 주간통계

SELECT DATE_FORMAT(DATE_SUB(`reg_date`, INTERVAL (DAYOFWEEK(`reg_date`)-1) DAY), '%Y/%m/%d') as start,
       DATE_FORMAT(DATE_SUB(`reg_date`, INTERVAL (DAYOFWEEK(`reg_date`)-7) DAY), '%Y/%m/%d') as end,
       DATE_FORMAT(`reg_date`, '%Y%U') AS `date`, 
       sum(`value`)
  FROM test_st
 GROUP BY date;

MySQL - 월간통계

SELECT MONTH(`reg_date`) AS `date`, 
       sum(`value`)
  FROM test_st
 GROUP BY `date`;

기간별 통계

SELECT DATE(`reg_date`) AS `date`,
       sum(`value`)
  FROM test_st
 WHERE DATE(`reg_date`) >= STR_TO_DATE('2017-04-01', '%Y-%m-%d')
   AND DATE(`reg_date`) <= STR_TO_DATE('2017-04-10', '%Y-%m-%d')
 GROUP BY `date`;

MySQL 5.7.17 - 수동설치

Database 2017.03.04 19:40 Posted by 파란크리스마스

MySQL 5.7.17 - 수동설치

출처 : 취향창고 :: [윈도우10]mysql 5.7.10 수동 설치기

환경파일 생성(mysql.ini)

[mysqld]
# port
# port=3306
# set basedir to your installation path
basedir=C:/mysql/mysql-5.7.17-winx64
# set datadir to the location of your data directory
datadir=C:/mysql/mysql-5.7.17-winx64/data

character-set-server=utf8 
collation-server=utf8_general_ci 

#default-storage-engine = InnoDB
#default-table-type = InnoDB
transaction-isolation = READ-COMMITTED

[mysql] 
no-auto-rehash
default-character-set=utf8 

[client] 
default-character-set=utf8

초기화

bin\mysqld --initialize

root 계정 암호 초기화

권한 없이 mysql 서버 실행

bin\mysqld --skip-grant-tables

root 계정 암호 설정

C:\mysql\mysql-5.7.17-winx64>bin\mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql
Database changed
mysql> update user set authentication_string = password('sql') where user = 'root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

localhost용 root 계정 등록

C:\mysql\mysql-5.7.17-winx64>bin\mysql -u root -p
Enter password: ***
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.17

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> alter user 'root'@'localhost' identified by 'sqldba';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> quit
Bye

mysql 서비스 등록

bin\mysqld.exe --install "MySQL 5.7.17" --defaults-file=C:\mysql\mysql-5.7.17-winx64\mysql.ini

mysql 서비스 삭제

bin\mysqld.exe --remove "MySQL 5.7.17"

SQL 학습

Database/SQL 2017.02.25 20:50 Posted by 파란크리스마스

Sample 데이터 다운로드

출처 : MySQL Sample Database - MySQL Tutorial

Sample 데이터 import

mysql -h localhost -P 5614 -u root -p < mysqlsampledatabase.sql

Select 문장

출처 : Using MySQL SELECT Statement to Query Data - MySQL Tutorial
SQL Syntax - TutorialsPoint

SELECT 컬럼명1, 컬럼명2
  FROM 테이블명

Select 예

select customerNumber, customerName
  from customers;

DISTINCT 예약어

출처 : SQL 자습서 - 1Keydata

SELECT 문장에 나열된 컬럼의 데이터가 중복행이 있으면 종복행을 제외하고 조회하는 예약어

SELECT DISTINCT 컬럼명1, 컬럼명2
  FROM 테이블명

Select 예

select DISTINCT city
  from customers;

-

-

MySQL - 테이블 스토리지 엔진을 MyISAM에서 InnoDB로 수정

Database 2016.12.12 00:23 Posted by 파란크리스마스

InnoDB 가능 여부 확인

출처 : Linux DB - innodb 설치 및 옵션 - FAQ.호스트웨이

mysql> show variables like 'have_innodb';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_innodb   | DISABLED |
+---------------+----------+
1 row in set (0.01 sec)

MySQL 옵션 변경

MySQL 환경 변수 파일을 열어서 skip-innodb 부분에 샵(#)을 추가해서 주석으로 만들고 MySQL 재시작 한다.

# cd /etc
# cp my.cnf my.cnf.old
# vi my.cnf

my.cnf 수정

# skip-innodb

MySQL 재시작

# service mysql restart
Shutting down MySQL... SUCCESS! 
Starting MySQL. SUCCESS!

MySQL 옵션 변경 확인

mysql> show variables like 'have_innodb';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_innodb   | YES   |
+---------------+-------+
1 row in set (0.02 sec)

테이블 스토리지 엔진을 MyISAM에서 InnoDB로 수정

출처 : Mysql 테이블 InnoDB 방식으로 변경하기(How to change Mysql table information MyISAM to InnoDB)

mysql> alter table `test_table1` engine=InnoDB;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

테이블 스토리지 엔진을 MyISAM만 조회해서 InnoDB로 수정 스크립트 추출

SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` ENGINE=InnoDB;') 
  FROM INFORMATION_SCHEMA.TABLES 
 WHERE TABLE_SCHEMA = 'bluexmas_db' 
   AND ENGINE='MyISAM'
Records: 0 Duplicates: 0 Warnings: 0

Auto Commit 설정 변경

출처 : MySQL - AUTOCOMMIT 여부 확인하기 - 민서네집 - Tistory

-- AUTOCOMMIT 여부 확인
SELECT @@AUTOCOMMIT;

MySQL - Tip

Database 2015.12.02 11:09 Posted by 파란크리스마스

MySQL - Update 질의문 사용시 조회된 결과로 반영

출처 : MySQL - UPDATE query based on SELECT Query

update item_code a
       left join item_type b on a.type_name like ( concat('%', b.type_name , '%') )
   set a.type_id = b.id
UPDATE code_type a
       LEFT OUTER JOIN code_group b 
       ON a.g_id = b.id AND b.category_id in ('M', 'E')
   SET a.t_code = CONCAT(b.g_code, SUBSTR(a.t_code, 4, 2))
 WHERE a.g_id = b.id AND b.category_id in ('M', 'E')

MySQL - 날짜 연산

출처 : 12.7 Date and Time Functions

mysql> select now(), date_sub(now(), interval 1 DAY);
+---------------------+---------------------------------+
| now()               | date_sub(now(), interval 1 DAY) |
+---------------------+---------------------------------+
| 2015-12-03 23:44:52 | 2015-12-02 23:44:52             |
+---------------------+---------------------------------+
1 row in set (0.00 sec)

MySQL - DATETIME 타입을 DATE 타입으로 형변환

select *
  from memo1
 where survey_date = cast(now() as DATE)

-

로컬 PC에서 MySQL 접속이 되지 않을때

Database 2014.01.21 15:55 Posted by 파란크리스마스

출처 : 리눅스 종류 확인, 리눅스 버전 확인
MySQL: "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)"

리눅스버전확인

[root@testserver init.d]# cat /etc/issue
CentOS release 5.9 (Final)
Kernel \r on an \m

로컬 PC에서 접속이 되지 않을때

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

MySQL 서비스 종료

[root@testserver init.d]# service mysqld stop
mysqld 를 정지 중:                                         [  OK  ]

권한 테이블 없이 MySQL 실행하기

[root@testserver init.d]# mysqld_safe --skip-grant-tables &
[1] 4712
[root@testserver init.d]# Starting mysqld daemon with databases from /var/lib/mysql

MySQL 접속

[root@testserver init.d]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.95 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

기존 root@localhost 사용자가 존재하고 있으면 패스워드만 변경 (패스워드만 분실했을 경우)

mysql> update user set `Password`=PASSWORD('root12');
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6  Changed: 6  Warnings: 0

사용자 추가 (권한 변경은 MySQL 재시작후 실행)

mysql> INSERT INTO `user` (`host`, `user`, `password`) VALUES('localhost','root',PASSWORD('root12'));
Query OK, 1 row affected, 3 warnings (0.01 sec)

mysql> grant all privileges on *.* to 'root'@'localhost';
Query OK, 0 rows affected (0.00 sec)

MySQL 반영 (내장 캐쉬를 소거)

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye

MySQL 서비스 다시 종료

[root@testserver init.d]# /etc/init.d/mysqld stop
STOPPING server from pid file /var/run/mysqld/mysqld.pid
140121 16:02:23  mysqld ended

mysqld 를 정지 중:                                         [  OK  ]
[1]+  Done                    mysqld_safe --skip-grant-tables

MySQL 서비스 시작

[root@testserver init.d]# /etc/init.d/mysqld start
mysqld (을)를 시작 중:                                     [  OK  ]

TAG MySQL

Oracle XE 사용하기

Database 2013.11.21 17:09 Posted by 파란크리스마스
출처 : [ORACLE] 11g XE 버전 설치 후에 샘플 사용자/DB 설정하기

Oracle 서버 실행

oradim -startup -sid xe

Oracle 서버 종료

oradim -shutdown -sid xe

접속확인

tnsping xe

sqlplus 접속하기

sqlplus system/pass@xe

Demo Database(scott) 만들기

CREATE USER scott IDENTIFIED BY tiger DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
@C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\scott.sql

오라클 scott 계정 활성화 하기

alter user scott identified by tiger account unlock;

Oracle Character set 변경

Database 2013.07.22 16:23 Posted by 파란크리스마스

출처 : Oracle Character set 변경
오라클 캐릭터셋 확인및 변경

캐릭터 셋 설정 확인

select name, value$ from props$ where name in ('NLS_LANGUAGE', 'NLS_TERRITORY', 'NLS_CHARACTERSET');

SQL> select name, value$ from props$ where name in ('NLS_LANGUAGE', 'NLS_TERRITORY', 'NLS_CHARACTERSET');

NAME                              VALUE$
--------------------------------- ---------------------------------
NLS_LANGUAGE                      AMERICAN
NLS_TERRITORY                     AMERICA
NLS_CHARACTERSET                  AL32UTF8

KO16MSWIN949 으로 변경

SQL> UPDATE SYS.PROPS$ SET VALUE$='KOREAN' WHERE NAME='NLS_LANGUAGE';

1 row updated.

SQL> UPDATE SYS.PROPS$ SET VALUE$='KOREA' WHERE NAME='NLS_TERRITORY';

1 row updated.

SQL> UPDATE SYS.PROPS$ SET VALUE$='KO16MSWIN949' WHERE NAME='NLS_CHARACTERSET';

1 row updated.

SQL> commit;

Commit complete.

오라클 다시 시작

C:\>oradim -shutdown -sid xe

C:\>oradim -startup -sid xe

클라이언트의 캐릭터셋 변경 방법

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\오라클홈이름\NLS_LANG의 값을 KOREAN_KOREA.KO16MSWIN949로 변경