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"

      name: "https://repo.mysql.com/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:

      name: mysql-community-devel

      state: installed

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

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

    yum:

      name: mysql-community-server

      state: installed

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

  become: true

  environment: "{{ my_proxy }}"

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 사용

# 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()

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;

MySQL / Oracle 다음 시퀀스 값 가져오기

db 테이블에서 Oracle sequence 나 MySql auto_increment 속성으로 설정된 컬럼에서 다음 값을 파악해야 하는 경우가 있다.

[Oracle]
# 시퀀스 생성
create sequence id_seq start with 1 increment by 1 maxvalue 9999999999;
# 다음 시퀀스 파악
select nextval() from table_a;

nextval() 할 때마다 자동 증가된 값을 파악할 수 있다.
멀티쓰레드 환경에서 다수의 db 커넥션으로 수행해도 겹치지 않는 증가 값을 가져올 수 있다.

[MySQL]
# 테이블 생성시 컬럼 속성을 auto_increment 로 지정
create table table_a (
seq_id bigint(19) not null AUTO_INCREMENT, # (자동증가)
col1 int(9) default null,
col2 int(9) default null,
PRIMARY KEY (seq_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# 최근에 inset 된 후 id 값 파악
# last_insert_id() 를 통해 가장 최근에 insert id 를 파악할 수 있다.
select last_insert_id();


하지만 mysql 의 경우 db 커넥션 마다 독립된 id 값을 별도로 관리하기 때문에
다수의 db 커넥션에서 last_insert_id() 시 같은 값을 가질 수 있다.
예를 들면...
A, B 가 각각 다른 db 커넥션을 가지고 같은 테이블에 insert 를 한다고 했을때
A가 insert 후 last_insert_id() 하면 1
B가 insert 후 last_insert_id() 하면 1
즉 세션마다 유니크한 값을 유지할 수는 있지만 모든 세션에서 유닉크한 값은 될 수 없다.

실제 테스트 해보니 커넥션마다 독립적으로 마지막으로 insert 한 id(auto increment)값을 파악할 수 있었다.
다음과 같은 순서로 쿼리했을때
커넥션1로 insert 3번
커넥션2로 insert 1번
커넥션1에서 select last_insert_id();  -> 3
커넥션2에서 select last_insert_id();  -> 1
와 같이 원하는 값을 얻을 수 있었다.


다음과 같은 다른 방법을 생각해 볼 수 있다.

# 자동 커밋을 안되게 하여 트랜잭션 처리를 가능하게 한다.
set autocommit=0;

# InnoDB 에서의 락 방법
# for update 는 쿼리(트랜잭션)를 실행한 세션을 제외한 모든 세션은 row 에 대한 접근 대기 상태가 된다. (for update 외에 select 는 가능한 lock in share mode 가 있다.)
# 쿼리가 종료되면 락은 풀린다.
# insert 하기전 미리 자동 증가 값을 파악할 수 있다.
select ifnull(max(seq_id)+1, 1) from table_a for update;

# insert 하면 자동증가 값은 위에서의 max(seq_id)+1 이 된다.
insert into tabe_a(col1, col2) values(1, 2);

MySQL 반복 프로시저 작성 예

/* 같은 이름의 프로시저가 있다면 삭제 */
drop procedure if exists mysql_loop_insert;
delimiter $$
/* 반복 insert 프로시저 */
create procedure mysql_loop_insert(in num bigint)
begin
declare llNo bigint(19) default 0;
declare nIdx1 int default 0;
declare nIdx2 int default 0;
set llNo = num;
set nIdx1 = 1;
set nIdx2 = 1;
while nIdx1 <= 10
do
set nIdx2 = 1;

while nIdx2 <= 5
do
insert into tb_ysoftman values(llNo, nIdx1, nIdx2, 'A',1,1,now())
on duplicate key update col1 = 'A', col2 = 1, col3 = 1, last_date = now();
set nIdx2 = nIdx2 + 1;
end while;

set nIdx1 = nIdx1 + 1;
end while;
end $$
delimiter ;
/* 반복 insert 프로시저 실행 */
call mysql_loop_insert(1234567);

RDB(relational database) 로 NoSQL 흉내내기

[Microsoft SQL 을 -> NoSQL 처럼]
-XML 형식으로 데이터를 지원, XML 형식의 데이터를 찾고, 입력하고, 수정하고, 삭제 가능
-SQL Server 2000 이후 부터 지원
-XML 데이터는 내부적으로 Binary로 저장되며 일반적인 경우 약간의 압축이 제공되어 인스턴스당 2GB 저장 가능
-참고
 http://technet.microsoft.com/ko-kr/library/ms177454.aspx
-예제
/*
테이블 생성 시 XML 형식의 열을 포함하도록 한다.
XML 키워드로 만든 xmldata 로 여러가지 쿼리를 수행한다.
*/
create table YsoftmanTable(id int not null, xmldata xml)

/* id가 1인 행에 XML 형식의 데이터 입력하기 */
insert into YsoftmanTable select 1, '
<?Employee Info?>
<Employee>
 <Name>YoonByoungHoon</Name>
 <Sex>Male</Sex>
 <Phone>123456789</Phone>
</Employee>
'
/* id가 2인 행에 XML 형식의 데이터 입력하기 */
insert into YsoftmanTable select 2, '
<?Employee Info?>
<Employee>
 <Name>BillGates</Name>
 <Sex>Male</Sex>
 <Phone>55555</Phone>
</Employee>
'

/* 내용 확인 */
select * from YsoftmanTable

/* id가 2인 행에서 Employee 하위 요소인 Name 요소의 모든 내용 찾기 */
select xmldata.query('/Employee/Name') from YsoftmanTable where id=2

/* id가 2인 행에서 Employee 하위에 Address 요소를 첫번째 위치로 추가 */
update YsoftmanTable set xmldata.modify('insert <Address>Seattle</Address> as first into (/Employee)[1]') where id=2

/* id가 2인 행에서 Employee 하위에 Mobile 요소를 Phone 요소 앞에 추가 */
update YsoftmanTable set xmldata.modify('insert <Mobile>2222</Mobile> before (/Employee/Phone)[1]') where id=2

/* id가 2인 행에서 Employee 하위에 Mobile 요소의 값을 변경 */
update YsoftmanTable set xmldata.modify('replace value of (/Employee/Mobile/text())[1] with "333"') where id=2

/* id가 2인 행에서 Employee 하위 요소인 Name 요소의 모든 내용 삭제 */
update YsoftmanTable set xmldata.modify('delete /Employee/Name') where id=2

/* id가 2인 행 삭제 */
delete from YsoftmanTable where id=2

/* 테이블 삭제 */
drop table "YsoftmanTable"



[MySQL 을 -> NoSQL 처럼]
-TPS(Transation Per Second) 성능의 대부부능 DataBase 층이 아닌 SQL 층에서 발생
 : MySQL 의 대부분의 오버헤드는 SQL 을 파싱하고 처리하는데 있고, InnoDB 접근하는 시간은 그리 길지 않다.
-InnoDB 에 접근할 수 있도록 해주는 HandlerSocket를 사용하여 NoSQL 로 접근(SQL과 NoSQL 로 동시 접근 가능)
-벤치마크 결과 750,000 QPS(Query Per Second) 처리
-참고
 원문 : http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html
 한글 : http://note.oiko.cc/post/2937725236/handlersocket-for-mysql