본문으로 건너뛰기

"mariadb" 태그로 연결된 10개 게시물개의 게시물이 있습니다.

모든 태그 보기

utf8 charset에서 emoji 필터링하기

· 약 2분

utf8 charset 에서는 이모지를 처리할 수 없다. utf8mb4 언어셋 소개 및 표현범위 포스팅에 따르면 이모지가 mysql 또는 maria 의 utf8 셋의 가변공간을 사용하려 들려 하는 문제인데, utf8mb4 셋으로 변경하면 해결 된다.

mysql 이거나 maria 의 charset 을 변경하기 힘들 때 text 를 모두 replace 치면 된다. 시작해보자

php

writing a simple removeEmoji function stackoverflow 글에 좋은 함수가 있다.

소스

<?php
// https://stackoverflow.com/questions/12807176/php-writing-a-simple-removeemoji-function
function removeEmoji($text) {
$clean_text = "";
// Match Emoticons
$regexEmoticons = '/[\x{1F600}-\x{1F64F}]/u';
$clean_text = preg_replace($regexEmoticons, '', $text);
// Match Miscellaneous Symbols and Pictographs
$regexSymbols = '/[\x{1F300}-\x{1F5FF}]/u';
$clean_text = preg_replace($regexSymbols, '', $clean_text);
// Match Transport And Map Symbols
$regexTransport = '/[\x{1F680}-\x{1F6FF}]/u';
$clean_text = preg_replace($regexTransport, '', $clean_text);
// Match Miscellaneous Symbols
$regexMisc = '/[\x{2600}-\x{26FF}]/u';
$clean_text = preg_replace($regexMisc, '', $clean_text);
// Match Dingbats
$regexDingbats = '/[\x{2700}-\x{27BF}]/u';
$clean_text = preg_replace($regexDingbats, '', $clean_text);
// Match Flags
$regexDingbats = '/[\x{1F1E6}-\x{1F1FF}]/u';
$clean_text = preg_replace($regexDingbats, '', $clean_text);
// Others
$regexDingbats = '/[\x{1F910}-\x{1F95E}]/u';
$clean_text = preg_replace($regexDingbats, '', $clean_text);
$regexDingbats = '/[\x{1F980}-\x{1F991}]/u';
$clean_text = preg_replace($regexDingbats, '', $clean_text);
$regexDingbats = '/[\x{1F9C0}]/u';
$clean_text = preg_replace($regexDingbats, '', $clean_text);
$regexDingbats = '/[\x{1F9F9}]/u';
$clean_text = preg_replace($regexDingbats, '', $clean_text);
return $clean_text;
}
<?php
$textWithEmoji = 'thumbs up👍👍';
$text = removeEmoji($textWithEmoji);

// text => thumbs up

node

emoji-regex 라이브러리를 쓰면 된다.

$ yarn add emoji-regex

소스

const emojiRegex = require("emoji-regex");

// const regex = emojiRegex()
const textWithEmoji = "thumbs up👍👍";
const text = textWithEmoji.replace(emojiRegex(), "");

// text => thumbs up

여담

이모지처리 (붙히고 제거하고), linkify 를 합친 통합 모듈이 있다면 다운로드 수가 좀 될 것 같은데.

Docker로 LEMP Stack 구축하기

· 약 2분

이 포스트 전에 웹서버 세팅을 하나씩 설치해서 띄워보는 걸 권장하고 Docker, SSH Login, LetsEncrypt, sed 명령어의 사용법을 알고 있어야 한다. 구성할 서버 스택은 다음과 같다.

  • Docker
  • Docker-compose
  • Host 에 사용될 Linux (Centos7)
  • Alpain Linux
  • Nginx ^1.13
  • MariaDB ^10.2
  • PHP ^7.1
  • Laravel =5.4
  • LetsEncrypt
  • HTTP2
  • Redis

이전 포스트를 참조하자.

Container 쇼핑

Docker Hub에서 마음에 드는 Container 를 사용해도 되지만, 생각처럼 돌아가는 Container 는 다음과 같았다.

Laradock을 안 썼죠?

  1. Laradock 에서 caddy 를 사용하지 않고 nginx 와 certbot 만을 이용해 http2 환경을 구성하는 예제가 없었다.
  2. 그래도 시도해봤으나 certbot 인증시에 DocumentRoot 를 잡지 못하는 현상을 삽질로 매꿀 시간이 없었다.
  3. Git repo 를 Clone 받아서 Docker-compose 로 Container 를 구동하기 때문에 추후 ECS 에 적용할 수가 없는 구조였다.
  4. 직접 구축해보고 싶었다.

세팅

nginx-php-fpm

Laravel 용 및 튜닝을 위해 Docker hub 의 이미지 대신 Git repo 의 이미지를 Clone 해서 세팅을 해보자.

내용 추가 중..

CentOS7 LEMP Stack 설치하기 (HTTP2, PHP7.1, Maria, Letsencrypt)

· 약 9분

Linux, Nginx, MariaDB, PHP

Why LEMP instead of LNMP? We go with LEMP due to the pronunciation for Nginx: Engine-X (en-juhn-ecks). Think of how in English, the article an is used instead of a for hour even though it begins with a consonant. The importance is the sound of the first letter rather than its written representation. Besides, LEMP is actually pronounceable and doesn’t sound like reciting the alphabet.

PHP

repo 를 등록하고 설치하는 방법이 있지만 php71 등의 이름으로 설정해야되서 번거롭다. yum 명령어의 --enablerepo 옵션을 사용해 php71 repository 를 가져오자.

# php repo를 가져오기
$ yum -y install http://rpms.famillecollet.com/enterprise/remi-release-7.rpm

$ vi /etc/yum.repos.d/remi-php71.repo

[remi-php71]
name=Remi\'s PHP 7.1 RPM repository for Enterprise Linux 7 - $basearch
#baseurl=http://rpms.remirepo.net/enterprise/7/php71/$basearch/
mirrorlist=http://rpms.remirepo.net/enterprise/7/php71/mirror
# 이 값을 1로 바꿔준다.
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-remi

$ yum -y install php php-fpm php-devel php-curl php-mcrypt php-mysql php-xmlrpc php-mbstring php-zip 등등

$ systemctl start php-fpm
$ systemctl enable php-fpm

Maria

예전 포스팅으로 대체한다. 설치방법은 똑같고 버전만 다르게 해주면 된다. 설치 후 서비스 실행만 systemctl 명령어로 해주자.

Xtrabackup

이제 mysqldump 를 좀 놓아주고 Incremental Backup 을 해주자. 이게 왜 좋은지는 공홈이나 이나 다른 블로그에 자세히 설명되어있다.

# xtrabackup 설치
$ yum -y install xtrabackup

# 증분백업을 위해 전체 백업이 한 번 필요하다.
# root가 아닌 유저일 경우 해당 DB를 백업하기 위해 PROCESS 등 여러 권한을 추가해야한다.
# (로그 메세지로 추가하라고 나온다.)
$ innobackupex --user='root' --password='암호' --databases='DB' --no-timestamp /백업위치

# 증분백업
$ innobackupex --user='root' --password='암호' --incremental --incremental-basedir='/백업위치/xtrabackup_checkpoints' --no-timestamp /증분백업위치

# 증분백업을 전체백업 위에 얹기
$ innobackupex --apply-log --redo-only --incremental-dir='/증분백업위치' /백업위치

# 증분백업 삭제
$ rm -rf /증분백업위치


# 복원
$ systemctl stop mariadb

# 데이터 폴더를 날려야한다. (만약을 대비해 백업)
$ rm -rf /var/lib/mysql/*
$ innobackupex --copy-back /백업위치

# 권한부여
$ chown -R mysql:mysql /var/lib/mysql
$ systemctl start mariadb

Nginx

HTTP2 를 적용하기 위해서는 Nginx 1.9 버전과 Openssl 1.0.2 버전 이상이 필요한데, 기본 repository 에는 이 버전이 적용되어있지 않다. 따라서 검색해보면 컴파일 설치를 해야 된다는 글이 다수다. 모든 패키지는 패키지 매니져로 관리하는게 좋다고 생각하는 나로썬 그냥 넘어갈 수 없다. Nginx 최신 repository 를 관리해주는 나이스한 곳을 찾았다.

$ yum -y install yum-utils
$ yum-config-manager --add-repo https://brouken.com/brouken.repo

# 기존 nginx repo를 사용하지 않는다는 옵션
$ yum-config-manager --save --setopt=epel.exclude=nginx*;
$ yum -y install nginx

$ systemctl start nginx
$ systemctl enable nginx

# nginx 버전 확인
$ Nginx -V
nginx version: nginx/1.13.1
built with OpenSSL 1.0.2k 26 Jan 2017

setopt 가 오류가 날 경우

ngixn.repo 를 만들어주고 연결하자.

$ vi /etc/yum.repos.d/nginx.repo

# vim /etc/yum.repos.d/nginx.repo

[nginx]
name=nginx repo
baseurl=http://nginx.org/packages/mainline/centos/7/$basearch/
gpgcheck=0
enabled=1

# 저장하고 설치 시작

$ yum update
$ yum -y install nginx
$ systemctl start nginx
$ systemctl enable nginx

LetsEncrypt

공짜 SSL 인 LetsEncrypt 의 이름이 certbot 으로 바뀌었다. 공홈을 참조해도 좋다. 인증을 받기위해 먼저 도메인을 따야한다.

$ yum -y install certbot

# 인증
$ certbot certonly --standalone -d example.com -d www.example.com

# 갱신 확인
$ certbot renew --dry-run

# 갱신하면서 nginx 재부팅
# 이 명령어를 적절히 cron에 넣어주자.
$ certbot renew --pre-hook="systemctl stop nginx" --post-hook="systemctl start nginx"

인증이 성공하면 /etc/letsencrypt/live/example.com/ 경로 아래에 키가 떨어질 것이다. 자세한 명령어 옵션은 Docs 참조.

nginx 연동

ssl 을 적용하면서 HTTP2 도 붙혀보자. HTTP2 에서는 bundling 보다 파일을 쪼개서 보내는게 더 효율적이라고 한다. (non-blocking 이니까)

# HTTP
server {
listen 80;
server_name example.com www.example.com;

# certbot --webroot 인증을 받기위한 설정
#location ^~ /.well-known/acme-challenge/ {
# default_type "text/plain";
# root /var/www/letsencrypt;
#}

# 80 접속시 443으로 redirect
location / {
return 301 https://$server_name$request_uri;
}
}

# HTTPS
server {
listen 443 ssl http2;
listen [::]:443 ssl http2;
server_name example.com www.example.com;
root /var/nginx/www/public;
index index.php index.html;

# 지저분한 보안 옵션은 추천옵션이니 넣어주자.
ssl on;
ssl_certificate /etc/letsencrypt/live/example.com/fullchain.pem;
ssl_certificate_key /etc/letsencrypt/live/example.com/privkey.pem;
ssl_trusted_certificate /etc/letsencrypt/live/example.com/fullchain.pem;
ssl_session_timeout 1d;
ssl_session_cache shared:SSL:50m;
ssl_session_tickets off;

#ssl_protocols TLSv1 TLSv1.1 TLSv1.2;
ssl_protocols TLSv1.2;
ssl_ciphers EECDH+AESGCM:EECDH+AES;
ssl_ecdh_curve secp384r1;
ssl_prefer_server_ciphers on;

ssl_stapling on;
ssl_stapling_verify on;

location / {
try_files $uri $uri/ /index.php?$query_string;
}

location ~ \.php {
fastcgi_split_path_info ^(.+\.php)(/.+)$;
include fastcgi_params;
fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
fastcgi_param SCRIPT_NAME $fastcgi_script_name;
fastcgi_index index.php;
fastcgi_pass 127.0.0.1:9000;
}

location ~ /\.ht {
deny all;
}

# 캐싱할 데이터가 있다면 추가
location ~* \.(?:jpg|jpeg|gif|png|ico|cur|gz|svg|svgz|mp4|ogg|ogv|webm|htc|woff)$ {
expires 1M;
add_header Cache-Control "public";
}

location ~* \.(?:css|js)$ {
expires 7d;
add_header Cache-Control "public";
}
}

해당 세팅은 Laravel5.4 용이다.

nginx.conf 에는 해당 옵션들도 추가해주자.

nginx.conf
  # CPU 물리 코어에 따라 설정한다.
# 1코어에선 1로 나머지는 auto로 설정하면 된다.
# grep ^processor /proc/cpuinfo | wc -l 로 확인 가능
worker_processes 1;

events {
# 각 worker process에서 한 번에 처리할 수 있는 최대 연결 수
# ulimit -n의 값과 같게 설정하자.
worker_connections 1024;
# I/O event 노티 방식을 epoll로 사용. (poll보다 발전한 방식)
use epoll;
# worker가 한 번에 모든 연결을 수용할 수 있도록 설정
multi_accept on;
}

http {
# access_log 제거
access_log off;
server_tokens off;

# iframe 보안 이슈 DENY
add_header X-Frame-Options DENY;
add_header X-Content-Type-Options nosniff;
add_header X-XSS-Protection "1; mode=block";

# PHP 서버라는 header 제거
fastcgi_hide_header X-Powered-By;
# static file 제공 최적화
sendfile on;
# TCP multiple buffer를 individual packet으로 전송하게 변경
tcp_nopush on;
# TCP에서 TCP_CORK 옵션을 활성화
# MTU에서 IP 헤더의 40-60 Byte를 뺀값과 같다는데 뭐라는건지 모르겠다.
tcp_nodelay on;
# keep alive 설정
keepalive_timeout 65;
# keepalive_requests 100000;

# gzip 압축설정
gzip on;
gzip_min_length 1000;
gzip_types application/x-javascript text/css application/javascript text/javascript text/plain text/xml application/json application/vnd.ms-fontobject application/x-font-opentype application/x-font-truetype application/x-font-ttf application/xml font/eot font/opentype font/otf image/svg+xml image/vnd.microsoft.icon;
gzip_disable "MSIE [1-6]\.";
}

적용 후에 HTTP2 확인 확장프로그램 설치 후 이 프로그램에 파란불이 들어오면 성공이다.

파일 백업

소스는 git 으로 관리가 되는데 static 파일은 주기적으로 백업이 필요하다.

file_backup.sh
NOW_DATE=`date`
BACKUP_DATE=`date +"%Y%m%d"`
FILE_DIR=백업할 폴더 경로
BACKUP_DIR=백업된 파일 경로

# gz 압축
tar zcvf ${BACKUP_DIR}/${BACKUP_DATE}.tar.gz ${FILE_DIR}

# 3일 이상된 백업파일은 제거
find ${BACKUP_DIR}/ -mtime +3 -exec rm -f {} \;
# find ${BACKUP_DIR}/ -mtime 3 -delete

여담

다음부터 Ubuntu 쓸까 생각했는데, Nginx 최신 Repo 를 찾은게 컸다. 하지만 빨리 Docker 책 읽자. Nginx, Reverse Proxy, Redis, PHP, Jenkins 를 한 번에 해결해주겠지. 이제 DockerFile 이 잘 읽히긴 하는데, 언제쯤 갈아탈 수 있으려나

mysql, maria 등의 서비스 포트 변경이 안 될때

· 약 3분

포트 확인

# grep 뒤에 확인할 포트를 적으면 된다.
$ netstat -lp | grep 3307

iptable 수정

$ vi /etc/sysconfig/iptables

# 해당 포트를 방화벽에서 열어준다.
-A INPUT -p tcp -m state --state NEW -m tcp --dport 3307 -j ACCEPT

database 포트 변경

$ vi /etc/my.cnf

# 포트가 설정되있는 부분을 찾아 변경한다.
port = 3307

서비스 재시작

$ service mysql restart

잘 했는데 서비스가 올라가지 않는다!!!

원인

로그 확인

mysql 설치 경로로 이동하여 로그를 살펴보자.

$ cd /var/lib/mysql/ # basedir 설정이 되어있다면 해당경로를 덧붙힌다.

$ cat {hostname}.err

[ERROR] Cant start server: Bind on TCP/IP port. Got error: 13: Permission denied
[ERROR] Do you already have another mysqld server running on port: 3307 ?
[ERROR] Aborting

해당 포트에 권한이 없다. 해당 포트를 다른 mysqld 서비스가 사용하는가? 라고 로그가 남겨져있다.

SELinux 확인

$ sestatus

SELinux status: enabled

혹시나 역시나 SELinux가 활성화 되어있다. SELinux에서 포트가 서비스용으로 활성화가 되지 않았기 때문에 계속 거절당한 것이였다.

해결

SELinux 설정 확인

semanage 명령어를 사용해서 확인하고 변경할 수 있다. 명령어 실행이 안될 경우 policycoreutils-python 패키지를 설치해주면 된다.

# semanage 설치
$ yum install -y policycoreutils-python

# 포트 확인
# mysql
$ semanage port -l | grep mysqld_port_t
# http
$ semanage port -l | grep http_port_t

mysqld_port_t tcp 1186, 3306, 63132-63164

변경하려는 3307 포트는 등록되어 있지 않다.

SELinux에 포트 등록

$ semanage port -a -t mysqld_port_t -p tcp 3307

명령어가 iptables에 등록하는 것과 유사하다.

확인

$ semanage port -l | grep mysqld_port_t

mysqld_port_t tcp 3307, 1186, 3306, 63132-63164

이제 mysql restart를 하면 정상적으로 구동된다.

여담

리눅스에서 정상적으로 진행했는데 뭔가 안된다면 SELinux부터 의심해보자.

Windows10에서 NodeJS MariaSQL 모듈 설치

· 약 3분

mariasql package 설치 명령어 실행시 오류를 내뿜으며 node-debug.log 를 확인하라고 명령어가 나올 경우 아래와 같이 하면 된다.

debug log 에는 node-gyp rebuild 를 하라고 나오는데, 이 오류메세지와는 아무 관련이 없다.

해결

Python 2.7 설치

윈도우에 Python 2.7 버전이 설치되어있지 않으면 설치해야한다. 파이썬 2 이상 3 미만 버전을 쓰면 되는데, 2.7 을 강조하니 쓰자.

여기서 다운로드 한다. 설치시 Window PATH 등록 옵션을 꼭 선택해야한다.

Python 경로를 npm 에 등록

$ npm config set python "/the/python/path" --global

the/python/path 에 자신의 python 설치 경로를 넣어주자.

Microsoft Visual Studio Community 2015 설치

왜 VS 2015 를 설치해야하지? 라고 생각이 들텐데, **Visual C++**을 사용하기 때문이다.

여기서 다운로드한다. 설치시 프로그래밍언어 탭에서 Visual C++을 꼭 선택해서 설치해야한다.

기존에 VS2015 가 설치되어있는 경우, 프로그램 추가/삭제에서 선택 후 수정 메뉴를 눌러 Visual C++ 옵션을 추가한 뒤 업데이트해준다.

2017 년 기준 위 링크가 만료되어 cpp-build-tools를 설치해야한다

Visual Studio 버전을 npm 에 등록

$ npm config set msvs_version "2015" --global

MariaSQL Package 설치

$ npm install mariasql --save

여담

Windows10 에서 mariasql package 설치시 C++ 컴파일이 필요하니, VS2015 로 C++ 컴파일러를 설치해야된다라는 걸 msdn 이나 npm 에 친절히 남겨줬으면 이렇게까지 시간을 날리지 않았을텐데...

최근 해결방법

빠르고 위의 오류가 절대 발생하지 않는 mysql2 패키지를 사용하면 된다.

image from hexo

CentOS Maria DB 설치

· 약 4분

Maria DB 를 써야하는 이유는 MySQL 이 지원이 끝났기 때문이고, 오라클이 소유하고 있어 언제 유료화가 될지 모르고, Thread Pool, 강화된 스토리지 엔진 (InnoDB -> XtraDB), 새로운 스토리지 엔진 (Aria, Cassandra)과 HandlerSocket, Virtual Column 등의 새로운 기능이 있기 때문이다.

설치를 시작해보자!

버전 및 bit 확인

# get version
$ cat /etc/*release*
# get bit
$ getconf LONG_BIT

yum repo 복사

maria로 이동해 맞는 버전을 추가한다.

image from hexo

yum repo 추가

$ vi /etc/yum.repos.d/MariaDB.repo

명령어로 MariaDB repository 를 생성한 뒤 복사한 내용을 붙히고 저장한다.

설치

$ yum install -y MariaDB-server MariaDB-client

혹여 설치가 안되면 maria 문서를 참고해서 따라해보자 (영어)

부팅 서비스 등록

$ chkconfig mysql on
# 또는
$ chkconfig --add mysql
$ chkconfig --level 345 mysql on

# 등록 확인
$ chkconfig --list mysql

maria에서는 345 레벨을 on 하라고 했는데, chkconfig mysql on 으로 실행시켜 2345 레벨을 모두 on 시켰다. 2 레벨은 not networking 이라 DB 의 원격지 접속이 안될테니 off 시켜도 무관하다.

서비스 실행

$ service mysql start

Starting MySQL.... [ OK ]

Maria 의 서비스명은 MySQL 로 뜬다.

Config 파일 수정 및 통합

처음 설치시 my.cnf 에서 my-server, my-client 등의 파일을 임포트해 분할 관리하게 되어있는데, 하나로 합쳐보자.

MariaDB 설치 폴더를 들어가면 My innoDB Huge 라는 config 파일이 존재한다. 이걸 그대로 사용해도 되고, 사용자 환경에 맞게 커스터마이징해서 사용해도 된다.

서버의 메모리가 8 GB 이상, innoDB 환경에서 사용가능한 config 파일을 첨부하니 이걸 써도 된다.

Thread Pool 기능을 사용하기 위해 extra_port 를 3307 로 줬다.

소스

my.cnf
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock

character-set-server = utf8mb4
collation_server = utf8mb4_general_ci

back_log = 100
max_connections = 100
max_connect_errors = 10
table_open_cache = 2048
max_allowed_packet = 16M

binlog_cache_size = 4M
binlog_format = row

max_heap_table_size = 16M

read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M

query_cache_type = 0
default-storage-engine = InnoDB
thread_stack = 256K
tmp_table_size = 16M

secure_auth =1

skip_external_locking
skip_symbolic_links

# Replication related settings
server-id = 1
expire_logs_days = 3
log_slave_updates

# MyISAM Specific options
key_buffer_size = 32M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 16M
myisam_repair_threads = 1
myisam_recover = FORCE,BACKUP

# INNODB Specific options
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2G
innodb_data_file_path = ibdata1:10M:autoextend
#innodb_data_home_dir = <directory>
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 0

innodb_log_buffer_size = 32M
innodb_log_file_size = 1024M
innodb_log_files_in_group = 2
#innodb_log_group_home_dir

innodb_max_dirty_pages_pct = 75

[mysqldump]
quick
max_allowed_packet = 16M
default-character-set = utf8

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 8192

[mariadb]
# thread pool
thread_handling=pool-of-threads
thread_pool_idle_timeout = 3600
thread_pool_stall_limit = 100
extra_port = 3307
extra_max_connections=10

Database 유저 생성 및 권한 설정

· 약 1분
CREATE USER '유저명'@'%' IDENTIFIED BY '비밀번호'; -- 모든 아이피 접근
CREATE USER '유저명'@'localhost' IDENTIFIED BY '비밀번호'; -- 로컬호스트 접근
CREATE USER '유저명'@'127.0.0.1' IDENTIFIED BY '비밀번호'; -- 로컬호스트 접근

데이터베이스에 모든 권한 부여

-- 해당 디비명의 유저의 모든권한 설정
GRANT ALL PRIVILEGES ON 디비명.* TO '유저명'@'%' WITH GRANT OPTION;

권한 적용

FLUSH PRIVILEGES;

권한 확인

SHOW GRANTS FOR '유저명'@'%';

여담

권한 부여는 쉘상에서 접근해야지 오류가 없다.

부분 권한 또는 부분 테이블로 권한을 제한하고 싶다면, MySQL Grant Syntax를 참조한다.

MySQL에서 MariaDB 이관시 서브쿼리의 정렬이 바뀔 때

· 약 2분

@Rownum 변수를 사용해 페이징 쿼리를 구현했는데, MySQL 에서는 정상적으로 정렬이 되다가 Maria 로 이관 후에 정렬이 반대로 나오는 경우가 있다.

쿼리

SELECT SQL_CALC_FOUND_ROWS @RNUM:=@RNUM+1 AS ROWNUM, R.* FROM (
SELECT @RNUM:=0, Q.* FROM (

SELECT * FROM TABLE1
WHERE ID = '1'
ORDER BY REG DESC
) Q
) R
LIMIT 0,10

설명

Rownum 변수를 사용해 TABLE1 에서 최신 날짜 순으로 데이터를 가지고 온다.

해결

LIMIT 2^64-1를 서브쿼리 내에 추가해준다.

LIMIT 의 최댓값은 unsigned 64bit-1 이므로 해당 값을 날려준다.

쿼리

SELECT SQL_CALC_FOUND_ROWS @RNUM:=@RNUM+1 AS ROWNUM, R.* FROM (
SELECT @RNUM:=0, Q.* FROM (

SELECT * FROM TABLE1
WHERE ID = '1'
ORDER BY REG DESC

LIMIT 18446744073709551615
) Q
) R
LIMIT 0,10

원인

Optimizer 가 임시테이블을 만든 후 filesort 하기 때문에 발생한다.

MySQL Lock 해제

· 약 1분

잘못된 업데이트 쿼리를 돌리다가 테이블이 잠길 경우 Lock을 해제해줘야 한다.

show processlist

kill ${processid}

설명

show processlist 쿼리를 날리면 현재 database를 사용 중인 프로세스의 목록이 나온다. 목록에서 해당 쿼리를 찾은 뒤 process id를 가지고 kill 쿼리를 실행하면 된다.