mysqldump 중 에러 발생. ERROR 1102 (42000): Incorrect database name '#mysql50#.gnome2'

아래와 같이 mysql 전체 백업을 진행하려고 하였다.

mysqldump -uroot -p'daims102938!' --all-databases > mysqldump.sql

에러가 발생.

ERROR 1102 (42000): Incorrect database name '#mysql50#.gnome2'

mysql data path로 가서 ls -al 로 전체 디렉토리를 확인하면 .gnome2와 .mozilla 디렉토리가 숨어있다.

mysql은 data path에 있는 디렉토리를 database로 인식한다. 그래서 .gnome2와 .mozilla를 database로 인식하였기 때문에 발생한 문제다.

원인은 리눅스를 gui로 설치하고 mysql계정을 만든 후 해당 계정을 mysql의 data path로 등록했기 때문이다.

mysql 계정으로 gui환경으로 접속 할 일이 없기 때문에 해당 디렉토리를 삭제하여 정상 처리 하였다.

mysql federated 트래픽 증가 문제.

어느 날 갑자기 트래픽이 약 2배이상 증가한 것을 확인 해 보니 nethogs 로 확인 해 보니
 mysql 프로세스가 문제 였다.

federated 를 사용하여 간단한 상태체크를 하는 테이블을 하나 만들어 사용중이였는 데, 이게 문제 였다.

원인은 federated 테이블을 select 해 올 때 index를 타지 않으면 모든 데이터를 가져온 후 처리 하는 방식이였다.

약 22,000건 정도의 데이터를 가져오는 데, 약 1.5M 정도의 데이터를 사용하고 있었다.

         SELECT
        *
       FROM CHARGER_STATUS
       WHERE send = 'N'
       AND createdAt > @intervalTime

createdAt(timestamp)에 index를 설정하고 실행 하니 2K정도로 줄어든 것을 확인 할 수 있었다.

mysql http post json 전송 트리거

mysql http post json 전송 트리거


테이블에 값이 insert 되면 trigger를 이용하여 http post를 전송하도록 한다.
mysql 자체적으로는 해당 기능을 지원하지 않는다.

검색해 보니 mysql의 UDF(user define function)를 이용하여 만들어진 mysql-udf-httpmysql-udf-http lib가 있다.


근데 최근 업데이트 2013년이다. 찜찜한 면이 있지만, 소스를 살펴보니 별 거 없다.
libcurl을 이용하여 http 전송해 주는 c로 짠 플러그인이다.



소스를 서버에 받은 후 압출을 풀고 컴파일 하면 된다.
문서상으로 되어 있는 설치가이드는 mysql을 소스 설치 했을 때 상황으로 보인다.
yum을 이용하여 설치 하였기 때문에 config 설정이 조금 다르다.
config 시 mysql_config 명령어를 사용하는 데, mariadb-devel를 설치해야 한다.

sudo yum install mariadb-devel -y

sudo wget https://github.com/y-ken/mysql-udf-http/archive/master.zip

unzip master.zip

or
(git 명령어가 있으면)

git clone https://github.com/y-ken/mysql-udf-http.git

여기서 소스를 조금 수정 해야 된다.
실제 테스트 해보면, http_post로 json 전송하면 받는 server에서 json 타입으로 인식을 하지 못한다.
json으로 post 전송하기 때문에 header에 "Content-Type: application/json"이 필요하지만 그런 설정 부분이 없다.
워낙 소스가 간단해서 http_post function을 복사해서 http_post_json을 하나 만들고
해더만 아래처럼 추가 했다.
...
  if (curl)
  {
    struct curl_slist *hs=NULL;
hs = curl_slist_append(hs, "Content-Type: application/json");
curl_easy_setopt(curl, CURLOPT_HTTPHEADER, hs);
...

소스 수정 파일


재컴파일 후 mysql 재시작 후 function 등록.
create function http_post_json returns string soname 'mysql-udf-http.so';

테스트 해보면 정상적으로 인식 된다.

cd mysql-udf-http-master/

sudo CPPFLAGS="-I/usr/include/mysql" sh ./configure --with-mysql=/etc/my.cnf   --enable-shared --libdir=/usr/lib64/mysql/plugin && sudo make && sudo make install

설치가 정상적으로 완료 되었으면 /usr/lib64/mysql/plugin 디렉토리에 mysql-udf-http.* 파일들이 생긴다.
동적으로 인식이 되기 때문에 mysql 재시작은 안해도 된다. (*** 같은 이름으로 다시 넣으면 인식 안된다. 그럴 때는 재시작하자)

mysql console 상에서 function을 등록해 준다.

create function http_get returns string soname 'mysql-udf-http.so';
create function http_post returns string soname 'mysql-udf-http.so';
create function http_put returns string soname 'mysql-udf-http.so';
create function http_delete returns string soname 'mysql-udf-http.so';


사용법
SELECT http_get('<url>');
SELECT http_post('<url>', '<data>');
SELECT http_put('<url>', '<data>');
SELECT http_delete('<url>');

테스트로 가져오면 잘 가져온다.
select http_get('http://example.com/');



lib_mysqludf_json을 설치해서 json 타입을 리턴 받을 수 있다.


소스를 다운받은 후 so 파일로 만들어 주면 된다.
이건 make를 지원하지 않아 다른 방식으로 만들어야 된다.
같이 다운받아지는 so 파일을 사용하려고 봤는데, 32bit이다.
[root@VM1554776686489 lib_mysqludf_json]# file lib_mysqludf_json.so
lib_mysqludf_json.so: ELF 32-bit LSB shared object, Intel 80386, version 1 (SYSV), dynamically linked, not stripped

삭제 하자.

git clone https://github.com/mysqludf/lib_mysqludf_json.git

cd lib_mysqludf_json/

gcc -fPIC -shared -o lib_mysqludf_json.so -I/usr/include/mysql/  lib_mysqludf_json.c

만들어진 lib_mysqludf_json.so 파일을 plugin 폴더로 넣자.
mv lib_mysqludf_json.so /usr/lib64/mysql/plugin/

.sql 파일을 보면 function 만드는 법이 나온다. 복사해서 쓰자.


mariadb 기반으로 설치 하였다.


https://github.com/y-ken/mysql-udf-http
https://github.com/mysqludf/lib_mysqludf_json

Mysql 암호화, 복호화

 

#암호화
INSERT INTO 테이블명 (컬럼1) VALUES (HEX(AES_ENCRYPT('문자열', SHA2('My secret passphrase',512))));

#복호화
SELECT CAST(AES_DECRYPT(UNHEX(컬럼1), SHA2('My secret passphrase',512)) as char) FROM 테이블명;
OR
#복호화
SELECT CONVERT(AES_DECRYPT(UNHEX(컬럼1), SHA2('My secret passphrase',512)) USING utf8) FROM 테이블명;

 

CAST나 CONVERT으로 형변환 하는 부분 때문에 한참 해맸다.
대부분의 블로그가 형변환을 안 써 놨다.(심지어 공식 사이트 에서도...)

암복호화 함수의 리턴값은 BINARY 이기 때문에 HEX를 사용하지 않으면 필드 타입을 BINARY 저장이 가능한 타입으로 지정해야 된다. 공식문서는 BLOB을 추천한다고...

문제는 BINARY타입으로 지정시 일반 문자열 저장이 안되는 문제가 있기 때문에 필드타입을 VARCAHR로 사용하고 HEX를 사용하여 저장이 가능 하도록 한다.

 

MySQL 5.6이상부터 변경 가능.
암호화 모드 확인
select @@block_encryption_mode

암호화 모드 변경
SET block_encryption_mode = 'aes-256-cbc';#기본값 aes-128-ecb

암호화 모도는 처음부터 확인. 중간에 바뀌면 골치 아파진다.

mysql(mariadb) 테이블별 mysqldump 백업

디비별 디렉토리를 만들고, 테이블별 .sql 파일을 만든다.

[root@localhost cron.daily]# vim sqlBackup.sh 
#!/bin/bash

_DB_PASS='password'
_BACKUP_DIR='/backup/sqlBackup/'

find $_BACKUP_DIR* -mtime +7 -exec rm -fr {} \; > /dev/null 2>&1

db_list=`echo "show databases;" | mysql -N -uroot -p${_DB_PASS}`
eval `date "+day=%d; month=%m; year=%Y"`

INSTD="$_BACKUP_DIR/sql-backup-$year-$month-$day"
mkdir -p $INSTD

for db in $db_list; do
        if [ ! -d $INSTD/$db ]
        then
                mkdir $INSTD/$db
        fi
        table_list=`echo "show tables" | mysql -N -uroot -p${_DB_PASS} $db`
        for table in $table_list; do
                mysqldump -uroot -p${_DB_PASS}  --lock-all-table --opt --quick --quote-names $db $table  > $INSTD/$db/${table}.sql
        done
done

mariadb 실행시 Failed to issue method call: No such file or directory. 오류 발생.

systemctl start mariadb 실행시 아래와 같은 오류 발생.
Failed to issue method call: No such file or directory.

아래와 같이 /usr/lib/systemd/system/mariadb.service의
소프트 링크가 /etc/systemd/system/multi-user.target.wants/mariadb.service 위치에
있어야 하나, 해당 파일이 없음.

-bash-4.2# systemctl status mariadb
mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled)
   Active: inactive (dead)

확인 결과 mariadb 가 disable 되어 있음.

 

아래 명령 실행으로 생성.

-bash-4.2# systemctl enable mariadb
ln -s '/usr/lib/systemd/system/mariadb.service' '/etc/systemd/system/multi-user.target.wants/mariadb.service'

 

enable 후 다시 확인.

-bash-4.2# systemctl status mariadb
mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled)
Active: inactive (dead)

위와 같이 나오는 데, 원인은 systemctl 으로  mariadb를 실행하지 않아서임.
/etc/init.d/mysqld start << 이 명령어로 실행 되고 있음.

/etc/init.d/mysqld stop 으로 mysql shutdown 후
systemctl start mariadb 실행. 에러 없이 실행됨.

 

확인.

-bash-4.2# systemctl status mariadb
mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled)
Active: active (running) since 목 2015-12-03 13:24:56 KST; 28s ago
Process: 35223 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
Process: 35194 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
Main PID: 35222 (mysqld_safe)
CGroup: /system.slice/mariadb.service
├─35222 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
└─35559 /usr/libexec/mysqld --basedir=/usr --datadir=/usr/local/mariadb/data --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/tmp/mysql.sock

12월 03 13:24:54 localhost.localdomain systemd[1]: Starting MariaDB database server...
12월 03 13:24:54 localhost.localdomain mysqld_safe[35222]: 151203 13:24:54 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
12월 03 13:24:54 localhost.localdomain mysqld_safe[35222]: 151203 13:24:54 mysqld_safe Starting mysqld daemon with databases from /usr/local/mariadb/data
12월 03 13:24:56 localhost.localdomain systemd[1]: Started MariaDB database server.

 

ps. 잘 되던게 갑자기 왜 이런지...

모든 쿼리 로그 기록.

마리아  db, mysql 의 모든 쿼리를 쌓아놓고 분석에 이용.

 

my.cnf 에 추가

#log
general_log=1
#log_output을 FILE 로 할경우의 경로.
#general_log_file = /var/log/mysql/general_query.log
log_output=TABLE

아래의 스크립트를 하루에 한번 실행하다록 크론에 등록.

general log가 계속 쌓이는 것을 방지. 7일마다 삭제.

#!/bin/sh

id="root"
pass="PASS"

mysql -u ${id}  -p${pass} -e "SET GLOBAL general_log = 'OFF'";
mysql -u ${id}  -p${pass} -e "RENAME TABLE mysql.general_log TO mysql.general_log2;";
mysql -u ${id}  -p${pass} -e "DELETE FROM mysql.general_log2 WHERE event_time <= NOW()-INTERVAL 7 DAY";
mysql -u ${id}  -p${pass} -e "OPTIMIZE TABLE general_log2";
mysql -u ${id}  -p${pass} -e "RENAME TABLE mysql.general_log2 TO mysql.general_log";
mysql -u ${id}  -p${pass} -e "SET GLOBAL general_log = 'ON'";

http://stackoverflow.com/questions/9343001/is-my-mysql-general-log-table-getting-too-big

 

general_log 의 엔진이 CSV라서 해당 파일 자체를 cat, vim 등으로 그냥 볼 수 있음.

마리아DB 소켓 변경시 systemd-private-xxxx 디렉토리 안에 생성되는 문제.

마리아DB 의 socket 위치를 /tmp/mysql.sock 로 변경 하였습니다.
그랬더니, /tmp/systemd-private-xxxx/tmp/mysql.sock 으로 생성이 된다.
뭐 이렇게 해도 상관이 없으나, 클라이언트 접속시 접속이 되지 않는다.

[client]
socket=/tmp/mysql.sock

위와 같이 처리 하면 될 것 같았으나, 위처럼 하면 그냥 /tmp/mysql.sock 으로 인식된다.

centos 7버젼으로 설치 했더니, 조금 바뀐게 많다.
tmp 디렉토리의 보안이 추가 된것으로 보인다.

 

vim /lib/systemd/system/mariadb.service 
PrivateTmp=true 에서 PrivateTmp=false 로 변경.
systemctl daemon-reload
service mariadb restart