Ubuntu 18.04 : MariaDB 10.3.12 컴파일, 설치

Database 2019.01.22 15:53 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

MySQL 계정 만들기

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

cmake 설치

$ sudo apt-get install cmake

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

$ wget https://downloads.mariadb.org/interstitial/mariadb-10.3.12/source/mariadb-10.3.12.tar.gz
$ tar xvf mariadb-10.3.12.tar.gz
$ cd mariadb-10.3.12/
$ sudo cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mariadb103 \
-DMYSQL_DATADIR=/usr/local/mariadb103/data \
-DMYSQL_UNIX_ADDR=/usr/local/mariadb103/mysql.sock \
-DSYSCONFDIR=/usr/local/mariadb103 \
-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/mariadb103/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

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

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

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

$ cd /usr/local/mariadb103
$ scripts/mysql_install_db --no-defaults --user=mysql --datadir=/usr/local/mariadb103/data --basedir=/usr/local/mariadb103 -v
Installing MariaDB/MySQL system tables in '/usr/local/mariadb103/data' ...
OK
 
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
 
 
PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following commands:
 
'/usr/local/mariadb103/bin/mysqladmin' -u root password 'new-password'
'/usr/local/mariadb103/bin/mysqladmin' -u root -h bluesanta-desktop password 'new-password'
 
Alternatively you can run:
'/usr/local/mariadb103/bin/mysql_secure_installation'
 
which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.
 
See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.
 
You can start the MariaDB daemon with:
cd '/usr/local/mariadb103' ; /usr/local/mariadb103/bin/mysqld_safe --datadir='/usr/local/mariadb103/data'
 
You can test the MariaDB daemon with mysql-test-run.pl
cd '/usr/local/mariadb103/mysql-test' ; perl mysql-test-run.pl
 
Please report any problems at http://mariadb.org/jira
 
The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Consider joining MariaDB's strong and vibrant community:
https://mariadb.org/get-involved/

root 암호 초기화

$ bin/mysqld_safe --skip-grant-tables &
[1] 11396
mysql@bluesanta-desktop:/usr/local/mariadb103$ 190122 23:11:49 mysqld_safe Logging to '/usr/local/mariadb103/data/bluesanta-desktop.err'.
190122 23:11:49 mysqld_safe Starting mysqld daemon with databases from /usr/local/mariadb103/data

mysql@bluesanta-desktop:/usr/local/mariadb103$ bin/mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.12-MariaDB-log Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> 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
MariaDB [mysql]> update user set authentication_string = password('sqldba') where user = 'root';
Query OK, 4 rows affected (0.001 sec)
Rows matched: 4  Changed: 4  Warnings: 0

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.000 sec)

MariaDB [mysql]> quit
Bye

mariadb 서비스 등록

서비스 mariadb 파일 복사

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

mariadb 수정

$ sudo vi /etc/init.d/mariadb

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

basedir=/usr/local/mariadb103
datadir=/usr/local/mariadb103/data

  # Try to find basedir in /etc/my.cnf
  conf=/usr/local/mariadb103/my.cnf

mariadb 서비스 등록

$ sudo update-rc.d mariadb defaults

mariadb 서비스 실행

$ sudo systemctl start mariadb.service
$ sudo systemctl status mariadb.service
● mariadb.service - LSB: start and stop MariaDB
   Loaded: loaded (/etc/init.d/mariadb; generated)
   Active: active (running) since Tue 2019-01-22 22:59:51 KST; 11s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 10984 ExecStart=/etc/init.d/mariadb start (code=exited, status=0/SUCCESS)
    Tasks: 32 (limit: 4915)
   CGroup: /system.slice/mariadb.service
           ├─11058 /bin/sh /usr/local/mariadb103/bin/mysqld_safe --datadir=/usr/local/mariadb103/data --pid-file=/us
           └─11191 /usr/local/mariadb103/bin/mysqld --basedir=/usr/local/mariadb103 --datadir=/usr/local/mariadb103/
 
 1월 22 22:59:50 bluesanta-desktop systemd[1]: Starting LSB: start and stop MariaDB...
 1월 22 22:59:50 bluesanta-desktop mariadb[10984]: Starting MariaDB
 1월 22 22:59:50 bluesanta-desktop mariadb[10984]: .190122 22:59:50 mysqld_safe Logging to '/usr/local/mariadb103/da
 1월 22 22:59:50 bluesanta-desktop mariadb[10984]: 190122 22:59:50 mysqld_safe Starting mysqld daemon with databases
 1월 22 22:59:51 bluesanta-desktop mariadb[10984]:  *
 1월 22 22:59:51 bluesanta-desktop systemd[1]: Started LSB: start and stop MariaDB.

mariadb 서비스 종료

$ sudo systemctl stop mariadb.service

사용자 추가

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

DB 종속적 사용자 추가

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

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;