Developments

MySQL Stored Procedure for CRUD Dynamic Queries

Posted on

The new MySQL 5.7 version has some great features. One of them is the ‘JSON data’ type. We can use it for column of table like ‘Virtual Colunm’ with Indexing. But, ‘JSON data’ is also useful for stored procedure. Because we can compact parameters and the procedure could be more flexible.I made the procedures for […]

System Engineering

Mysql Backup Script

Posted on
MySQL

Features Full Backup (Mysqldump) Incremental Backup (Bin-log) Local Backup & Remote(AWS S3) Upload Sample Environments MySQL bin log prefix : mysql-bin BINLOG_PATH=/home/mysql/data BACKUP_ROOT=/home/backup/mysql S3_PATH=s3://system-backup/{SyatemName}/mysql #!/bin/bash #—————————————- # MySQL Backup Script # Revision 20160531 # Copyright (c) Aiden Kim # http://www.daemon.pe.kr #—————————————- ARCHIVE_PERIOD=60 BINLOG_PATH=/home/mysql/data BASE_PATH=/home/backup/mysql SYSNAME=$(echo $HOSTNAME | tr “[:lower:]” “[:upper:]”) S3_PATH=s3://system-backup/${SYSNAME}/mysql STATUS_PATH=${BASE_PATH}/mysql-backup-status ## prepare path if ! […]

Developments

Don’t use utf8 encoding in MySQL (>= 5.5.3)

Posted on
MySQL

오늘날 한국어/중국어/일본어등 2바이트 문자권에서의 SW개발 및 다국어를 대비한 문자열 인코딩은 거의 UTF-8로 통일화 되고 있다. 그런데, MySQL의 문자인코딩 중 utf8인코딩은 ‘진짜 UTF-8’이 아니다 ! 원래 UTF-8은 규약상 한 문자가 4바이트까지 사용하는데 MySQL의 utf8인코딩은 3바이트까지만 입력이 가능하기 때문이다. 대부분의 한중일 문자는 3바이트 코드로  표현이 가능한데, 특히 특수문자나 한자를 많이 사용하는 일본어 등에서는 심심치 않게 4바이트 문자에 의한 […]

Developments

MySQL Procedure Pattern for Nested Transaction

Posted on
MySQL

The MySQL dose not supports ‘Nested Transaction’. Transactions cannot be nested. This is a consequence of the implicit commit performed for any current transaction when you issue a START TRANSACTION statement or one of its synonyms. (https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html) But I want to use ‘Stored Procedure’ what work well with a transaction independently and with nested calling. […]

Developments

MySQL, Checking the IP address in a range

Posted on

fn_ipv4_match( needle, haystack ) Returns 1 if haystack contains needle. needle : IPv4 address haystack : IPv4 address, IPv4 address with subnet mask, IPv4 address with netmask bits DELIMITER $$ DROP FUNCTION IF EXISTS `fn_ipv4_match`$$ CREATE FUNCTION `fn_ipv4_match`( needle VARCHAR(15), haystack VARCHAR(31) ) RETURNS TINYINT BEGIN /* * Checking the IP address in the range * 2014-09-30 * Aiden Kim */ DECLARE start_ip […]