sentimental programmer: sql

Sentimental Programmer | ysoftman

레이블이 sql인 게시물을 표시합니다. 모든 게시물 표시

use rqlite

# rqlite(분산 sqlite database)

# https://rqlite.io/docs/cli/

# k8s 로 운영중인 경우 local로 port-forwarding

kubectl port-forward service/rqlite 4001:4001 -n rqlite

# 접속

rqlite -H localhost -p 4001

# usage hints

.help

# 분산 노드 확인

.nodes

# 테이블 리스트

.tables

# 테이블 조회

# 쿼리는 일반적인 sql 사용하면 된다.

select * from 테이블;

# sql 문으로 dump

.dump ysoftman.dump

# db 백업(sqlite format 3)

.backup ysoftman.db

# exit

.exit or .quit

# mysql 쿼리문에서 사용하려면

# 필드명을 " 대신 `로 감싸야 한다.

# primary 에 있는 AUTOINCREMENT 는 제거하고 fileld 선언시 auto_increment 로 선언해야 한다.

# cat ysoftman.dump | sed -e "s/\"/\`/g" -e "s/AUTOINCREMENT//g" | pbcopy

# 이렇게 일일히 변경하는것 보다 툴을 사용하면 편하다.

brew tap techouse/sqlite3-to-mysql

brew install sqlite3-to-mysql

sqlite3mysql --help

# sqlite3 db 파일을 mysql 특정 db 에 import

sqlite3mysql -f ysoftman.db -h localhost -P 3306 -u root -d ysoftmantest --mysql-password aaa111

install mysql8 in rocky8

# rocky8 에서 mysql8.0 설치하기 위해선 다음과 같이 외부 저장소로 부터 설치해야 했다.

# 참고로 프록시가 필요한 경우 sudo 실행시 -E 로 proxy 설정 유지가 필요하다.

(프록시설정) sudo -E yum -y install https://dev.mysql.com/get/mysql80-community-release-el8-9.noarch.rpm

# 혹시나 잘못되면 다시 지우고 설치하자!!!(요거 몰라서 삽질)

sudo yum erase -y mysql80-community-release-el8-9.noarch

# mysql80 저장소가 보인다.

yum repolist enabled | grep mysql.*-community

# 기존 mysql 모듈은 비활성화 처리해야 mysql-community-8.0 패키지를 사용할 수 있다.

sudo yum module reset -y mysql

sudo yum module disable -y mysql

# 패지키 확인, 버전이 8.0으로 보인다.

sudo yum info mysql-community-devel mysql-community-server

# 이제 설치하면 된다.

(프록시설정) sudo -E yum install -y mysql-community-devel mysql-community-server

#####

# ansible 사용시

- block

- name: "Install MySQL8 remote repository for Rocky 8"

yum:

# redirect url 사용시 에러 발생

#name: "https://dev.mysql.com/get/mysql80-community-release-el8-9.noarch.rpm"

state: installed

sslverify: false

disable_gpg_check: true

update_cache: true

when: ansible_distribution == "Rocky" and ansible_distribution_major_version == "8"

- name: "Install MySQL8 devel package for Rocky 8"

yum:

state: installed

when: ansible_distribution == "Rocky" and ansible_distribution_major_version == "8"

- name: "Install MySQL8 server package for Rocky 8"

yum:

state: installed

when: ansible_distribution == "Rocky" and ansible_distribution_major_version == "8"

become: true

environment: "{{ my_proxy }}"

drone ci 사용하기

# golang 으로 만들고 docker 환경으로 실행되는 drone ci 를 사용해보자.

# https://docs.drone.io/server/provider/github/

# 우선 로컬에서 테스트하기 때문에

# (reverse proxy 로 ngrok 도메인 -> localhost 로 터널링 해주는) ngrok 설치

brew install ngrok

# http://fe9b41d98488.ngrok.io -> http://localhost:80 포워딩을 위해 실행

# 참고 https (tls) 터널링은 유료 버전만 가능하고, 그외 동시접속 제한이 있다.

ngrok http 80

# github oauth 로 drone 앱(서버)를 등록

# github -> settings -> developers -> oauth apps 에서 신규 생성한다.

# homepage url : http://dd441e3e9f16.ngrok.io

# callback url : http://dd441e3e9f16.ngrok.io/login

https://github.com/settings/developers

# oauth app 생성후

# client -> DRONE_GITHUB_CLIENT_ID

# client secret -> DRONE_GITHUB_CLIENT_SECRET 로 사용한다.

# generate a new client secret(생성후 한번 볼 수 있어 복붙해둬야 한다) -> DRONE_GITHUB_CLIENT_SECRET 값으로 사용

# drone <-> runner 간 RPC 통신을 위한 secret 생성 -> DRONE_RPC_SECRET 값으로 사용

openssl rand -hex 16

d087c94b8155367b1982238df930c2f2

# drone 도커 이미지 다운로드

docker pull drone/drone:1

# 실행

# github enterprise 는 깃헙 서버 환경 변수 설정 필요

# --env=DRONE_GITHUB_SERVER=https://github.ysoftman.com \

# 볼륨 연결로 /data/database.sqlite -> 호스트의 /var/lib/drone/database.sqlite 로 저장

docker run \

--volume=/var/lib/drone:/data \

--env=DRONE_DATABASE_DRIVER=sqlite3 \

--env=DRONE_DATABASE_DATASOURCE=/data/database.sqlite \

--env=DRONE_GITHUB_CLIENT_ID=깃헙_클라이언트_id \

--env=DRONE_GITHUB_CLIENT_SECRET=깃헙_클라이언트_secret \

--env=DRONE_RPC_SECRET=d087c94b8155367b1982238df930c2f2 \

--env=DRONE_SERVER_HOST=127.0.0.1 \

--env=DRONE_SERVER_PROTO=http \

--env=DRONE_WEBHOOK_ENDPOINT=http://dd441e3e9f16.ngrok.io/hook \

--env=DRONE_WEBHOOK_SECRET=d087c94b8155367b1982238df930c2f2 \

--env=DRONE_REPOSITORY_FILTER=ysoftman,bill \

--publish=80:80 \

--publish=443:443 \

--restart=always \

--detach=true \

--name=drone \

drone/drone:1

# 이제 다음 url 로 github authorize 하면 깃헙으로 로그인상태로 drone ci 를 사용할 수 있다.

http://dd441e3e9f16.ngrok.io

# DRONE_REPOSITORY_FILTER 으로 특정 저장소에만 drone과 연결 할 수 있다.

# 설정 변경 후 사용자별로 로그아웃 후 다시 로그인 해야 반영된다.

https://docs.drone.io/server/reference/drone-repository-filter/

#####

# 상황에 맞는 여러 runner(러너)가 있다.

# docker(임시 컨테이너를 생성해 실행할때 사용) 러너 설치

docker pull drone/drone-runner-docker:1

docker run -d \

-v /var/run/docker.sock:/var/run/docker.sock \

-e DRONE_RPC_PROTO=http \

-e DRONE_RPC_HOST=dd441e3e9f16.ngrok.io \

-e DRONE_RPC_SECRET=d087c94b8155367b1982238df930c2f \

-e DRONE_RUNNER_CAPACITY=2 \

-e DRONE_RUNNER_NAME=${HOSTNAME} \

-e DRONE_DEBUG=true \

-e DRONE_TRACE=true \

-p 3000:3000 \

--restart always \

--name runner \

drone/drone-runner-docker:1

# exec(docker 와 같은 고립된 환경이 아닌 drone 서버 장비에서 쉘 환경에서 명령을 실행할 때 사용) 러너를 설치

curl --proxy "필요한 경우 프록시 설정" -L https://github.com/drone-runners/drone-runner-exec/releases/latest/download/drone_runner_exec_linux_amd64.tar.gz | tar zx

sudo install -t /usr/local/bin drone-runner-exec

mkdir -p /home/ysoftman/.drone-runner-exec

touch /home/ysoftman/.drone-runner-exec/config

cat > /etc/drone-runner-exec/config << eof

DRONE_RPC_PROTO=http

DRONE_RPC_HOST=dd441e3e9f16.ngrok.io

DRONE_RPC_SECRET=d087c94b8155367b1982238df930c2f

DRONE_LOG_FILE=/home/ysoftman/.drone-runner-exec/log.txt

eof

drone-runner-exec service install

drone-runner-exec service start

# drone-runner-exec 서비스 상태 확인

systemctl status drone-runner-exec.service

# docker 환경 변수 확인

docker inspect -f "{{.Config.Env}}" drone | tr " " "\n"

# ssh(ssh 로 서버에 접속해 명령을 실행하는 용도) 러너 설치

docker pull drone/drone-runner-ssh

docker run -d \

-e DRONE_RPC_PROTO=http \

-e DRONE_RPC_HOST=dd441e3e9f16.ngrok.io \

-e DRONE_RPC_SECRET=d087c94b8155367b1982238df930c2f \

-p 3000:3000 \

--restart always \

--name runner \

drone/drone-runner-ssh

# 루트 경로에 .drone.yml 를 작성한다.

# 파일명은 drone ci settings 에서 변경 가능하다.

---

kind: pipeline

type: exec

platform:

os: linux

arch: amd64

trigger:

event:

- push

steps:

- name: 작업1

commands:

- echo hello world1

- name: 작업2

commands:

- echo hello world2

- name: 슬랙 알림

# environment:

# noti_msg: "Branch: ${DRONE_BRANCH}\nAuthor: ${DRONE_COMMIT_AUTHOR}\nLink: ${DRONE_COMMIT_LINK}"

# build_status: "✅ build ${DRONE_BUILD_NUMBER} succeeded. Good job."

commands:

- echo "파이프 종료"

- export HTTP_PROXY="http://프록시서버"

- export noti_msg="Branch ${DRONE_BRANCH}\nAuthor ${DRONE_COMMIT_AUTHOR}\nLink ${DRONE_COMMIT_LINK}"

- export build_status="✅ build ${DRONE_BUILD_NUMBER} succeeded. Good job."

- echo $DRONE_BUILD_STATUS

- if [[ $DRONE_BUILD_STATUS == "failure" ]]; then build_status="❌ build ${DRONE_BUILD_NUMBER} failed. Fix me please."; fi

- |

curl https://hooks.slack.com/services/..... -d "payload={\"channel\": \"#billtest\", \"text\": \"파이프 라인 시작\n$noti_msg\n$build_status\"}"

# ---

# kind: pipeline

# type: ssh

# name: default

# # 접속할 대상 서버

# server:

# host: ysoftman-server.com

# user: deploy

# password:

# from_secret: password

# steps:

# - name: 작업1

# commands:

# - echo hello world1

# - name: 작업2

# commands:

# - echo hello world2

# ---

# kind: pipeline

# type: docker

# name: slack notification

# # 사내환경등에서 외부 접속이 안되는 경우 프록시 환경 변수 설정

# environment:

# HTTP_PROXY: "http://프록시주소"

# HTTPS_PROXY: "http://프록시주소"

# steps:

# - name: 슬랙 알림

# # 사내환경등에서 외부 이미지 다운로드가 안되는 경우 알맞게 변경

# image: plugins/slack

# settings:

# # https://my.slack.com/services/new/incoming-webhook 참고

# webhook: https://hooks.slack.com/services/.....

# channel: ysoftman-test

# template: >

# {{#success build.status}}

# build {{build.number}} succeeded. Good job.

# {{else}}

# build {{build.number}} failed. Fix me please.

# {{/success}}

# Branch: {{ build.branch }}

# Author: {{ build.author }}

# Link: {{ build.link }}

# 이제 drone DRONE_WEBHOOK_ENDPOINT 변수로 설정된 url 을

# github 저장소 webhook url 을 추가해 push 되면 drone 에 알리도록 한다.

http://dd441e3e9f16.ngrok.io/hook

# 참고로 위의 HTTP_PROXY 와 같은 환경변수를 사용할때

# ${HTTP_PROXY} 는 안되고 {} 를 제거한 $HTTP_PROXY 로 사용해야 한다.

# https://docs.drone.io/pipeline/environment/syntax/

#####

# drone cli 툴 사용 https://docs.drone.io/cli/install/

# 설치

brew install drone-cli

# aaaaabbbbb11111 는 drone > User Settings > token 로 확인

# 접속할 drone 서버 환경 변수 설정

export DRONE_SERVER=http://dd441e3e9f16.ngrok.io

export DRONE_TOKEN=aaaaabbbbb11111

# 접속 여부 확인

drone info

# .drone.yml 작업 수행(커밋 전에 미리 로컬에서 테스트할 수 있다.)

# drone exec 현재 type: docker 만 실행된다.

# .drone.yml 을 다음과 같이 만들고

kind: pipeline

type: docker

steps:

- name: build

image: golang:1.13

commands:

- echo "aaa"

# 실행

# .drone.yml syntax 체크등에 활용할 수 있다.

drone exec

mysql utf8mb4 설정

# 기본적으로 utf8 인코딩은 총 4바이트로 문자를 표현하는데,

# mysql 에서는 utf8 문자셋은 utf8mb3의 alias 로 총 3바이트를 사용한다.

https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8.html

# 실제 utf8(4byte) 사용을 위해선 utf8mb4 로 명시해야 한다.

https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb4.html

# 스키마(db) 문자셋을 utf8mb4로 변경

# collate 는 order by(정렬)에 사용, 이것도 utf8mb4_bin 로 변경

alter schema `ysoftman_db` default character set utf8mb4 default collate utf8mb4_bin ;

# utf8mb4 로 변경된 스키마는 이제 새로운 테이블 생성시 기본 utf8mb4 로 설정한다.

# 하지만 이미 존재하는 테이블은 여전히 utf8 문자셋이다.

# 테이블 문자셋을 utf8mb4 로 변경

alter table `ysoftman_table1` default character set utf8mb4 default collate utf8mb4_bin;

# 컬럼도 필요시 변경

alter table `ysoftman_table1`

change column `name` `name` varchar(100) character set 'utf8mb4',

change column `desc` `desc` varchar(100) character set 'utf8mb4';

# 보통 emoji 나 기타 특수한 문자를 db 에 저장하지 않아

# 대부분은 utf8mb3 로 둬도 상관은 없다.

# 참고로 기존 테이블, 컬럼에 인코딩 문자셋을 한번에 변경하는 쿼리는 없어

# 스크립트로 쿼리문을 생성해 실행해야 한다.

gorm 구조체 필드 default 태그 유의사항

// gorm 으로 다음과 같이 test_info 테이블에 들어가는 구조체를 만들고

type TestInfo struct {

ID int64

LastDate time.Time

Age int `gorm:"default:99"`

Name string `gorm:"default:lemon"`

Enable bool `gorm:"not null;default:1"`

}

// 해당 레코드를 삭제 후 Save() 로 저장하면

// 아래 설정된 값이 아닌 default 값이 설정돼

// Name = lemon, Age = 99, Enable = true 값으로 저장된다.

db.Delete(TestInfo{}, "id = ?", user.ID)

user.Name = ""

user.Age = 0

user.Enable = false

db.Save(&user)

// 원인 파악 및 해결방법

// Save() 는 update 후 select 로 레코드를 확인하는데

// Delete() 로 해당 레코드가 없으면 insert(gorm create함수)를 수행한다.

// 이때 만약 구조체 필드 중 `gorm:"default:xxx"` 값이 설정되어 있고

// 그 필드가 초기값 bool = false, int = 0, string = "" 등인 경우

// Save() -> Create() -> Execute() -> f() ->

// callbacks/create.go Create() ->

// ConvertToCreateValues() -> case reflect.Struct ->

// isZero() 로 해당 필드가 값이 설정되지 않은 것으로 판단해

// 다음과 같이 default 값으로 insert 구문을 생성해 실행한다.

// INSERT INTO `test_info` (`age`,`name`,`last_date`,`enable`,`id`) VALUES (99,'lemon','2020-10-20 23:37:54',true,89)

// 만약 bool = false, int = 0, string = "" 인 상태로 저장하고 싶다면

// default:xxx 태그를 명시하면 안된다.

// 사실 Name="", Age=0, Enable=false 자체가 값을 설정한것이지만

// gorm 에서는 go reflect IsZero() 로 타입별 값 설정이 안되었다고 판단한다.

// IsZero 는 값 존재가 아니라 그 타입의 값이 0(false)인지를 파악한다.

// 테스트 코드

https://github.com/ysoftman/test_code/tree/master/golang/sql_db

#####

// gorm 버전업에 따른 repo 및 연결/초기 설정 api 변경사항

// gorm v1.9.16 이전 방식

import github.com/jinzhu/gorm v1.9.13

db, err := gorm.Open("mysql", DSN)

// 테이블명이 a_b_xxs 처럼 복수형 이름을 사용하지 않게 설정

db.SingularTable(true)

// 수행한 쿼리 stdout 출력

db.LogMode(true)

// gorm v1.9.16 이후 방식

import gorm.io/driver/mysql v1.0.2

import gorm.io/gorm v1.20.2

db, err := gorm.Open(mysql.Open(DSN),

&gorm.Config{

NamingStrategy: schema.NamingStrategy{SingularTable: true},

Logger: logger.Default.LogMode(logger.Info)

})

# mysqldump 로 db 를 파일로 덤프 받을 수 있다.
# 덤프를 시작하기전 각 db별 크기를 참고 하자.
select table_schema "database", round(sum(data_length+index_length)/1024/1024,1) "mb" from information_schema.tables group by 1;

# mydb1 db 만 덤프
mysqldump -h ysoftman-server -u ysoftman -pPassword123 --databases mydb1 > mydb1_dump.sql

# --all-databases 모든 db 덤프
mysqldump -h ysoftman-server -u ysoftman -pPassword123 --all-databases > all_db_dump.sql

# 신규 mysql 서버로 복원시
# mydb1 db 는 생성해 놓아야 한다.
mysql -h ysoftman-server -u ysoftman -pPassword123 mydb1 < mydb1_dump.sql

mysql Prepared_stmt_count 줄이기

# mariadb(또는mysql) 에서 stmt 가 정상종료되지 않고 있다.
# https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html#statvar_Prepared_stmt_count
# Com_stmt_prepare, Com_stmt_close, Com_stmt_fetch 는
# 각각 PREPARE, EXECUTE, DEALLOCATE PREPARE 를 실행한 카운트이다.
# Prepared_stmt_count 는 현재 prepared statement 사용중인 개수이다.
show global status like '%stmt%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 0 |
| Com_stmt_execute | 18116 |
| Com_stmt_close | 12216 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 33749 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_stmt_reprepare | 0 |
| Prepared_stmt_count | 9991 |
+----------------------------+-------+

# 테스트를 위해서 db 를 재시작해서 variable 를 초기화 한다.
sudo systemctl restart mariadb

# 로컬에서 root 로 접속(root 패스워드 설정은 mariadb 설치 포스팅 참고)
mysql -u root -pysoftman

# 다음과 같이 prepare stmt1, stmt2, ... stmtN 로 이름을 달리 계속 생성할 수 있다.
# 같은 stmt 이름을 사용하게 이전 stmt 를 닫고(Com_stmt_close 증가) 새로 만든다.
# Com_stmt_prepare, Prepared_stmt_count 증가
prepare stmt1 from 'select * from mysql.help_category where name = ?';

# stmt1 에 name 으로 전달된 파라미터 설정
set @help_name = 'functions';

# prepare stmt1 를 실행한다.
# Com_stmt_execute 증가
execute stmt1 using @help_name;

# prepare stmt1 를 종료한다.
# Com_stmt_close 증가, Prepared_stmt_count 감소
deallocate prepare stmt1;

# Prepared_stmt_count 줄이기
# flush 를 사용해봤지만 영향이 없다.
# flush 열려 있는 테이블들을 닫는다.(캐시된 쿼리를 삭제하는데 사용할 수는 있다.)
# https://dev.mysql.com/doc/refman/5.5/en/flush.html#flush-tables
flush tables;

# 현재 db 커넥션을 종료하거나 사용중인 프로세스를 종료시켜도
# 해당 프로세스와 연결된 Prepared_stmt_count 줄어든다.
# 다음 쿼리로 KILL id; 들로 실행할 쿼리를 만든다.
select concat('KILL ',id,';') from information_schema.processlist where db='ysoftman';

# 해당 프로세스 종료
KILL 1;
KILL 2;
KILL 3;

# 참고
# max_prepared_stmt_count 설정 값은 파악해본다.
show variables like '%stmt%';

# max_prepared_stmt_count 를 2로 줄여준다.
set global max_prepared_stmt_count=2;

# Prepared_stmt_count 는 max_prepared_stmt_count 변수로 설정된 개수를
# 넘어가면 더이상 stmt 를 생성할수 없다는 에러가 발생한다.
prepare stmt3 from 'select * from mysql.help_category where name = ?';
ERROR 1461 (42000): Can't create more than max_prepared_stmt_count statements (current value: 2)

golang mysql 8 접속 에러

# 최신 mysql 8 db 접속시 다음과 같은 에러가 발생한다.
this authentication plugin is not supported

# 해당 이슈는 mysql 패키지에서 2018-05-20 해결했다.
https://github.com/go-sql-driver/mysql/issues/785

# mysql 패키지를 최신으로 업데이트 하면 접속이 잘된다.ㅎ
go get -u github.com/go-sql-driver/mysql

mysql 테이블 컬럼명이 예약어와 같을때

# mysql(또는 mariadb)의 테이블의 컬럼(필드)명이 mysql 에서 사용하는 예약어라면
# sql 쿼리를 실행할때 syntax 에러를 뱉어낸다.

# 예시
# 다음처럼 db 테이블을 만들었다면
create database mydb;
use mydb;
MariaDB [mydb]> desc fruits;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| type | int(11) | YES | | NULL | |
| order | varchar(255) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+

# 데이터를 추가할때 컬럼명을 명시해 insert 하는 경우
insert into fruits(type, order, name) values(1,'1','lemon');

# 다음과 같은 에러를 발생한다.
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'order, name) values(1,'1','lemon')' at line 1

# order mysql 명령어라 `` (grave, 따옴표 아님)로 다음과 같이 명시해야 한다.
insert into fruits(type, `order`, name) values(1,'1','lemon');

# 다음과 같이 모든 컬럼을 일괄 `` 적용해주는것이 좋다.
insert into fruits(`type`, `order`, `name`) values(1,'1','lemon');

# 사실 테이블을 생성할때도 필드명은 ``로 감싸준다.
create table fruits (
`type` int,
`order` varchar(255),
`name` varchar(255)
);

mariadb 설치

# mysql 이 오라클로 넘어가고 나서 부터 무료로 사용하기 힘들어졌다.(비영리 목적은 가능)
# 그래서 mysql 과 호환되는 mariadb(mysql 만들 사람들이 나와서 만든 db) 를 많이 사용하는 추세다.

# centos 에서 설치
sudo yum install mariadb-server

# ubuntu 에서 설치
sudo apt-get install mariadb-server

# 버전 확인
mysql -V

# 설정
vi /etc/my.cnf 또는 /etc/mysql/my.cnf
...
[mysqld]
port = 13306
...
# db 실행
sudo systemctl start mariadb

# db 재시작
sudo systemctl restart mariadb

# db 상태확인
sudo systemctl status mariadb

# db 중지
sudo systemctl stop mariadb

# root 암호 설정
mysql_secure_installation
...
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n] y

New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!

# db 접속
mysql -u root -p
Enter password:

# 다음과 같이 접속 허용이 되지 않는다면 sudo 로 실행하자.
sudo mysql -u root -p

# 접속후 db 확인
MariaDB [(none)]> show databases;

# mysql db 의 user 테이블에서 사용자 정보 확인
MariaDB [mysql]> select host, user from mysql.user;

# 원격에서 접속시 다음과 같은 에러가 발생하면
# 1.1 대역 IP 는 허용하도록 설정
# Host '1.1.1.1' is not allowed to connect to this MySQL server
insert into mysql.user (host,user,authentication_string,ssl_cipher, x509_issuer, x509_subject) values ('1.1.%','root',password('xxxxx'),'','','');
grant all privileges on *.* to 'root'@'172.26.%';
flush privileges;

Python3 + Mysql 사용하기

# -*- coding: utf-8 -*-

# author: ysoftman

# python version : 3.x

# desc : mysql db test

# mysql-python 설치 (2.x 버전)

# pip install mysql-python

# mysql-python 설치 (3.x 버전)

# pip install pymysql

# mysql-python 설치 확인

# pip list

# 접속 정보

import pymysql

import sys

db_host = "127.0.0.1"

db_port = 13306

db_user = "root"

db_passwd = "ysoftman"

db_dbname = "test"

db_charset = "utf8"

def db_test():

# db 연결

conn = pymysql.connect(host=db_host, port=db_port, user=db_user, passwd=db_passwd, database=db_dbname, charset=db_charset)

# 커서 준비

cursor = conn.cursor()

# 쿼리 설정

query = "select curdate();"

# 쿼리 실행하기

cursor.execute(query)

# 쿼리 결과 가져오기

result = cursor.fetchone()

# 결과 출력

print("query = %s , result = %s" % (query, result))

# 테이블 상태 파악 해보기

query = "show table status;"

cursor.execute(query)

print("query = %s" % query)

for out in cursor:

print("name = %s , engine = %s , version = %s , row_foramt = %s , rows = %s " % (out[0], out[1], out[2], out[3], out[4]))

# 연결 종료

conn.close()

# mysql db test

db_test()

JSON document DB

JSON document 를 저장하고 싶은데 CouchDB, Elasticsearch, MongoDB 어떤것을 선택해야 할까?
https://db-engines.com/en/system/CouchDB%3BElasticsearch%3BMongoDB

mysql 사용자 관리

# 일반 사용자 암호 수정
# mysql 접속 사용자는 mysql db 의 user 테이블로 관리된다.
select * from mysql.user;

# user 테이블에 사용자를 추가하거나 수정한다.
# 아래 명시된 쿼리 대신 쿼리 브라우저를 사용해서 gui 환경에서 추가 수정하면 편하다.
# 사용자 추가 및 암호 설정
create user 'ysoftman'@'localhost' identified by 'ysoftman123';

# 사용자 암호 수정 쿼리
update mysql.user set password='password123' where user = 'ysoftman';

# 사용자 삭제 쿼리
delete from mysql.user where user = 'ysoftman';

# 이제 mysql 재시작하면 적용된다.
sudo service mysql restart

#####

# 암호 정책(policy)
# 암호 관련 변수들 확인
show variables like 'validate_password%';

# password policy 낮추기
set global validate_password_policy='LOW';

#####

# root 암호 수정
# mysqld_safe 실행(윈도우 버전은 myslqd.exe 를 사용하면 된다.)
sudo /usr/bin/mysqld_safe --skip-grant-tables &

# root 암호 변경
update user set password=password('ysoftman') where user='root';
update user set authentication_string=password('ysoftman') where user='root';
set password for 'root'@'localhost' = password('ysoftman');
flush privileges;

# mysql_safe 중지 후 mysqld 시작
# 참고로 사용자 추가시
insert into user(host, user, password) values('localhost', 'ysoftman', PASSWORD('test123'));

#####

# root 암호를 알수 없는 경우
# 우선 mysql 을 중지시킨다.
sudo service mysql stop

# mysqld_safe 실행(윈도우 버전은 myslqd.exe 를 사용하면 된다.)
sudo /usr/bin/mysqld_safe --skip-grant-tables &

# root 로 암호 없이 접속 가능
/usr/bin/mysql -u root

# root 암호 변경
update mysql.user set password='password123' where user = 'ysoftman';

# mysql 재시작(mysqld_safe 는 자동 중단된다)
sudo service mysql start

# root 변경된 암호로 접속
/usr/bin/mysql -u root -ppassword123

MySQL DB 상태 확인하기

# show 커맨드 참고
# https://dev.mysql.com/doc/refman/5.7/en/show.html
# 현재 문자셋 보기
show variables like 'char%';

# 각종 타임 아웃 보기
show variables like '%timeout%';

# DB 목록 보기
show databases;

# 테이블 목록 보기
show tables;

# 등록된 프로시저 목록 보기
show procedure status;

# 등록된 함수 목록 보기
show function status;

# 테이블 상태 보기(용량까지 포함)
show table status;

# 서버 전체 상태 보기

show global status;

# 서버 전체 stmt 관련 현황 보기

show global status like '%stmt%';

# 프로세스 리스트 보기

show processlist;

# 테이블 인덱스,키 보기
show index from ysoftman_table;
show keys from ysoftman_table;

# 권한보기
show privileges;

# error 확인
show errors;

# warning 확인
show warnings;

# mysql 버전 확인

select version();

# 용량확인
select
concat(round(data_length/(1024*1024), 2), "MegaByte") data_size,
concat(round(index_length/(1024*1024), 2), "MegaByte") index_size,
concat(round((data_length+index_length)/(1024*1024), 2), "MegaByte") total_size
from information_schema.Tables where table_name="ysoftman_table";

# 다수의 update 방지
set sql_safe_updates=1;
# 다수의 update 방지 해제
set sql_safe_updates=0;

MySQL string column(field) size

# MySQL 스트링 타입으로 선언된 컬럼(필드)의 경우 크기 제한이 있다.
# TEXT 경우 크기에 따라 타입이 세분화 된다.
varchar(M) -> M 크기내에서 실제 사용한 크기 만큼, M<=65536
tinytext -> 2^8 -> 256 byte
text -> 2^16 -> 65,536 byte
mediumtext -> 2^24 -> 16,777,216 byte (16MB)
longtext -> 2^32 -> 4,294,967,296 byte (4GB)
varchar 는 메모리에 올려 둘 수 있지만 text 부터는 디스크를 매번 액세스 하기 때문 느리다.
참고
http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html
https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html

# varchar 실제 값 길이 파악
# aaa 컬럼이 varchar(10) 이고 '가나다' 라고 했을때
# length: byte 크기로 한글3byte x 3 = 9
# char_length: 글자 크기로 3 이 된다.
select aaa, length(aaa), char_length(aaa) from ysoftman.table1;

# utf8 은 최대 (가변) 3바이트인데,

# utf8mb4 인코딩의 db 는 최대(가변) 4바이트로 표현될 수 있다.(한글은 3바이트)

# 예) varchar, text 는 4바이트 문자로만 되어 있다면 65536 / 4 = 16384 개의 글자만 입력 가능한다.

MySQL ERROR 2013 (HY000) 에러 해결하기

VirtualBox(vb) 에서 게스트OS(Ubuntu)에서 MySQL 을 구동하고 호스트 OS 에서 접속하는경우 발생했다.

# Ubuntu MySql 구동
# my.cnf 수정
sudo vi /etc/mysql/my.cnf
port = 13306
#bind-address = 127.0.0.1 # 127.0.0.1로 접속 가능하도록 주석처리

# mysql 재시작
sudo /etc/init.d/mysql restart

# vb 네트워크 설정에서 13306 포트 포워딩으로 게스트OS 로 접속가능하도록 함
# 접속 시도
mysql -h 127.0.0.1 -P 13306 -u root -p

# mysql 접속시 다음과 같은 에러 발생
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

# hosts.allow에 외부 호스트에서 접속 허용되어 있지 않기 때문에 발생하였다.
# mysqld 가 모든 호스트로 부터 접속을 허용하도록 함
sudo vi /etc/hosts.allow
mysqld: all

DB Table Lock 걸기

멀티 쓰레드 환경에서 다수의 DB 커넥션이 테이블 값에 접근하여 쿼리를 수행하려 할때

발생할 수 있는 동시성을 해결하기 위한 방법 중 하나로 "for update" 구문을 사용할 수 있다.

/* 이미 Lock을 걸어 수행 중인 쿼리가 있으면 대기한다. */

select * from table_a for update;

/* 이미 Lock을 걸어 수행 중인 쿼리가 있으면 3초까지만 대기하고 빠져 나온다.(오라클) */

select * from table_a for update wait 3;

/* 이미 Lock을 걸어 수행 중인 쿼리가 있으면 대기하지 않고 바로 빠져 나온다.(오라클) */

select * from table_a for update no wait;

예를 들어 다음 데이터가 들어갈 시퀀스 값을 알고 싶은 경우,

Oracle 의 경우 nextval 기능을 사용하면 다음 시퀀스 값을 유니크하게 얻어 오지만

MySQL 에는 nextval 이 없어 for update 구문을 이용하도록 한다.

select ifnull(max(my_seq+1),1) from table_a for update;

DB 커넥션 끊김 방지하기

보통 DB에서는 쿼리 요청이 없는 Connection 은 일정시간이 지나면 끊어 버린다.

# MySQL 경우 기본 커넥션유지는 interactive_timeout 28800 (8시간)이다.
# https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_interactive_timeout
# 각종 타임 아웃 시간 보기
# 대기시간(연결 유지)은 wait_timeout
show variables like '%timeout%';

따라서 일정 주기만 커넥션을 유지를 위한 더미 쿼리(ex "Select 1;")를 요청해주도록 한다.
아니면 각 DB 마다 연결끊김 에러코드를 인지하여 다시 연결하도록 한다.

oracle
ORA-00028: your session has been killed
ORA-01012: not logged on
ORA-01033: ORACLE initialization or shutdown in progress
ORA-01034: ORACLE not available
ORA-01089: immediate shutdown in progress - no operations are permitted
ORA-03113: end-of-file on communication channel
ORA-03114: not connected to ORACLE
ORA-03127: no new operations allowed until the active operation ends
ORA-12571: TNS:packet writer failure

mssql
10054 : Connection Reset By peer (tcp 의 RST 로 비정상 종료로 연결을 끊는 경우)
10061 : Network Error

mysql
2003 : Can't connect to MySQL server
2013 : Lost connection to MySQL server during query
2006 : MySQL server has gone away

ODBC 사용시 각 DBMS 별 Connection String

ODBC 사용시 각 DBMS 별 Connection String

Oracle
Driver={Microsoft ODBC for Oracle};Server=10.10.10.10;Port=3306;Uid=ysoftman;Pwd=abcd1234;

MySQL
Driver={MySQL ODBC 5.2 ANSI Drive};Server=10.10.10.10;Port=3306;Database=testdb;User=ysoftman;Password=abcd1234

MS-SQL
Driver={SQL Server};Server=10.10.10.10,3306;DSN=dsn_test;Database=testdb;Uid=ysoftman;Pwd=abcd1234;

MySQL 시간관련 함수

# mysql 에서 현재로 부터 1시간뒤, 하루뒤... 등을 계산하려면 date_add() 함수를 사용한다.

# 현재로 부터 1분 뒤
select date_add(now(), interval 1 minute);
# 또는 함수 없이 사용할 수도 있다.

select now() + interval 1 minute;

select now() + interval 1 hour;
select now() + interval 1 day;
select now() + interval 1 month;
select now() + interval 1 year;

# 현재 유닉스타임
select unix_timestamp();

# date -> 유닉스타임
select unix_timestamp('2000-12-31 12:00:00');

# 유닉스 타임 -> date 로 변환
select from_unixtime(unix_timestamp());

# 기타... date 스트링에서 해당 값만 파악하기

select year('2000-12-31 12:00:00');

select month('2000-12-31 12:00:00');

select day('2000-12-31 12:00:00');

select hour('2000-12-31 12:00:00');

select minute('2000-12-31 12:00:00');

select second('2000-12-31 12:00:00');

select monthname('2000-12-31 12:00:00');

select dayname('2000-12-31 12:00:00');

select dayofyear('2000-12-31 12:00:00');

select dayofmonth('2000-12-31 12:00:00');

select dayofweek('2000-12-31 12:00:00');

# 오늘 날짜만 파악하기
select curdate();

# 현재시간
select current_timestamp;