오늘날짜의 00시 구하기

 SELECT SUBTIME( NOW(), TIMEDIFF( NOW(), CAST(DATE(NOW()) AS DATETIME) ) )
 ;

 

오늘날짜의 00시 구하기

(UTC인 시간을 우리나라 시간으로)

 SELECT SUBTIME( NOW()
               , TIMEDIFF( CONVERT_TZ(NOW(), @@system_time_zone, 'Asia/Seoul')
                         , CAST(DATE( CONVERT_TZ(NOW(), @@system_time_zone, 'Asia/Seoul') ) AS DATETIME)
                         )
               )
 ; 

 

 

DB 시간이 어느 TimeZone인가 확인할 때

 SELECT @@system_time_zone

 ;

 

DB 시간이 우리나라 시간이 아니라면

DB의 현재 시간을, 우리나라 시간으로 바꿔서 조회하고 싶다면

 SELECT NOW(), CONVERT_TZ(NOW(), @@system_time_zone, 'Asia/Seoul')

 ;

 

 

시스템 운영시점에서는 설정된 기본값이나

조정된 설정값을 사용하면 되지만

 

DB에 실행시간이 길게 소요되는 SQL들을 날리며 작업해야하는

DB Setting, Migration등의 경우

DB 자체에서 관리하는 timeout 값들을 변경한 후 작업을 수행하고

작업이 완료되면, 원래 설정값들로 원복하는 것이

정신건강에 좋을 듯 하다.

 

그렇지 않으면

아래 Error를 작업이 끝날 때까지 만나게 될테니 말이다.

(작업이 수행되다 중간에 끊어져버린 것이라...

 100을 처리해야 하는 데, 60, 70만 처리되고 끝나버리면

 작업을 보정하기 조차 어렵다;;;  (정신건강에 안좋음)

 60, 70이라도 작업을 초기화해야 할 듯...

 Error Code: 2013. Lost connection to MySQL server during query

 

MySQL에는 timeout 관련 설정이 다수 있지만

 SHOW VARIABLES LIKE '%timeout%';

 

 Variable_name Value
 connect_timeout 10
 delayed_insert_timeout 300
 have_statement_timeout YES
 innodb_flush_log_at_timeout 1
 innodb_lock_wait_timeout 50
 innodb_rollback_on_timeout OFF
 interactive_timeout 28800
 lock_wait_timeout 31536000
 net_read_timeout 30
 net_write_timeout 60
 rpl_stop_slave_timeout 31536000
 slave_net_timeout 60
 wait_timeout 28800 

 

오래 걸리는 SELECT를 처리해야 할 때는

 SET GLOBAL net_read_timeout = 30;

 

오래 걸리는 INSERT, UPDATE를 처리해야 할 때는

 SET GLOBAL net_write_timeout = 60;

 

원하는 초단위로 변경하고

재접속하면 된다.

 

작업이 완료되고 난 후에는...

설정값을 원복하는 것을 잊으면 안되겠다...

놀이터가 아니라면...^^

 

 

기타 설정값 관련 참고글 : https://lilymate.tistory.com/640

 

MySQL - Temp Table is full, 크기 조절

CREATE TEMPORARY TABLE xxx ... 임시로 생성한 테이블에서 아래와 같은 Exception 이 발생한다면? java.sql.SQLException: The table 'xxx' is full MySQL 환경에 설정된 heap size와 temp table size를 조절해야..

lilymate.tistory.com

 

 

mysql - DB Link, FEDERATED TABLE 생성 (1/2) - 준비

 


2. DB Link 처리할 TABLE 생성

   그렇다.  MySQL에서는 TABLE을 만들어야 원격 DB의 해당 TABLE을 접근할 수 있다.

              접근할 TABLE을 1:1로 모두 생성시켜야 한다.

 

2.1. 접속정보를 모두 입력해서 TABLE 생성

 

 CREATE TABLE 테이블명 { ... }

 ENGINE=FEDERATED ...

 CONNECTION='mysql://user_name[:password]@host_name[:port_num]/db_name/tbl_name';  ← 접속정보

참고 : https://dev.mysql.com/doc/refman/5.7/en/federated-create-connection.html

 

하지만, 이렇게 생성한 TABLE Script는

아래 명령을 사용해 확인할 수 있으며

CONNECTION에 입력한 접속정보까지 모두 표시된다.

 mysql> SHOW CREATE TABLE 테이블명;

 

(TEST용이 아니라면) 접속정보를 별도로 생성하는 것이 좋을 듯 하다.

 

 

2.2. 접속정보를 별도로 생성해서 TABLE 생성

 

 -- MySQL 접속

 # mysql -u root -p

 

 -- mysql database 사용

 mysql> USE mysql;

 

 -- 접속할 원격서버정보 추가

 mysql> CREATE SERVER 서버명

           FOREIGN DATA WRAPPER mysql

           OPTIONS ( HOST '접속할IP'
                        , DATABASE 'DB Schema명'
                        , USER '아이디'
                        , PASSWORD '비밀번호'
                        , OWNER '아이디'  ← 현재 미적용? 생략해도...
                        , PORT 3306 );

 

 -- 원격서버정보 조회

 mysql> SELECT * FROM servers;

 

 CREATE TABLE 테이블명 { ... }

 ENGINE=FEDERATED ...

 CONNECTION='생성한서버명/tbl_name';  ← 접속정보

참고 : https://dev.mysql.com/doc/refman/5.7/en/federated-create-server.html

 

 

이제 생성한 FEDERATED Table을 조회할 수 있다.

 

 

Oracle의 DB Link와 동일한 것을

MySQL에서 처리하는 방법이

 

DB Link시킬 원격테이블을

Local에 FEDERATED Engine을 사용한 TABLE로 생성하는 것이다.

 


1. 준비

 

*** Local DB ***

1.1. FEDERATED Engine 사용가능하도록 설정

 

1.1.1. MySQL에서 FEDERATED Engine 존재여부/상태 확인

 -- MySQL 접속

 # mysql -u root -p

 

 mysql> SHOW ENGINES;

 +--------------------+---------+
 | Engine               | Support |
 +--------------------+---------+
 | FEDERATED          | YES     |  ← 이렇게 표시되어야 준비된 것이다.
 +--------------------+---------+

 

1.1.2. FEDERATED Engine이 존재하지 않는다면?

 mysql> INSTALL PLUGIN federated SONAME 'ha_federated.so';

 

1.1.3. FEDERATED Support가 NO라면?

 # MySQL 설정파일에 FEDERATED Engine 지원 설정

 

 # sudo vi /etc/mysql/my.cnf

 

 [mysqld] 아래에 아래 단어 추가 후 저장
 federated

 # MySQL 서비스 재기동

 # /etc/init.d/mysql restart

 

※ 여기까지해서 FEDERATED Support가 YES 여야 한다.

 

 

*** (연결할) 원격 DB ***

1.2. 접속시킬 ID 생성/권한 설정

     ※ 다른 System에서 해당 ID를 사용하고 있다면,

        (TEST용이 아니라면) 전체 권한을 주기보다는

        접속할 IP와 접속 ID/비번을 별도로 지정하여 설정하는 것이 좋을 듯 하다.

 

 -- MySQL 접속

 # mysql -u root -p

 

 -- 접속할 ID 생성

 -- (지정한 IP에서만 접속을 사용할 수 있도록 제한한다.)

 mysql> CREATE USER '아이디'@'접속을시도하는DB의IP' IDENTIFIED BY '비밀번호';

 

 -- 생성한 ID에 TABLE 접근 권한 설정

 -- (해당 Schema의 모든 테이블에 접근할 때는 테이블명에 * 를 사용)

 -- (별도의 세부권한은 ALL 대신 쓸 수 있다...별도로 조사해야;;;)

 mysql> GRANT ALL PRIVILEGES ON DBSchema명.테이블명 TO 아이디@'접속을시도하는DB의IP';

 

 

mysql - DB Link, FEDERATED TABLE 생성 (2/2) - 생성

 

 

도대체 어떻게 해야하는거냐...;;;

 

netstat -nlt 를 해보면

tcp6       0      0 :::3306                 :::*                    LISTEN

tcp로 Listen 되는 3306은 어디가고...;;;

 

 

이렇게 되면, 원격에서 아무리 접속하려 해도...

mysql -h ip ...

 

ERROR 2003 (HY000): Can't connect to MySQL server on 'ip' (111)

 

ubuntu에서 IPv6를 비활성화하는 방법을 써봤지만...

(http://blog.saltfactory.net/disable-ipv6-on-ubuntu/)

 

 sudo cat /proc/sys/net/ipv6/conf/all/disable_ipv6
 0 나오면...

 

 sudo vi /etc/sysctl.conf

 내용 추가 후 저장

 net.ipv6.conf.all.disable_ipv6 = 1
 net.ipv6.conf.default.disable_ipv6 = 1
 net.ipv6.conf.lo.disable_ipv6 = 1

 파일을 ReLoad...

 sudo sysctl -p

 sudo cat /proc/sys/net/ipv6/conf/all/disable_ipv6
 1 나오지만...

 

결과적으로...

시스템 재부팅하고

mysql 재시작시켜봐도...

 

tcp 3306 listen 으로 돌아오지 않는다...;;;

 

 

하지만, local에서는 mysql 접속이 잘된다...;;;

mysql -u root -p

 

원격에서만 접속할 수가 없다...;;;

 

 

해결 방법을 아시는 분은 제보해주시기를... T.T

 

 

 추가. 가상머신 2개를 띄웠을 때...

         두번째로 띄운 가상머신에서 mysql을 띄울 때 tcp6 로만 띄워지나???

         → 새로 생성한 가상머신만 띄운 경우,

             그 안에서 띄운 mysql은 tcp 로 띄워져있다...ㅡㅡ;;;

 

 

멀쩡히 잘 되던 MySQL이

MySQL Workbench에서도 접속이 잘 되는데...

 

 

terminal에서 mysql을 실행하니

Error가 똭~

 

 # mysql
 ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

 

ls /var/run/mysqld

해봤더니...

아무것도 없네???  휑~~ ;;;

 

 

/etc/init.d/mysql restart

해봤지만 Fail 이 똭~

 

 

find / -name '*.sock'

로 해당 파일을 찾아봤지만

mysqld.sock은 찾을길이 없...

 

 

이리 저리 수소문 끝에...

stop / start를 하나씩 실행했더니... 정상~

 

 # service mysql stop
mysql stop/waiting

 #

 # service mysql start
mysql start/running, process 7368

 #

 # ps ax | grep mysql
 7368 ?        Ssl    0:00 /usr/sbin/mysqld
 7501 pts/12   S+     0:00 grep --color=auto mysql

 #

 

 

시간을 더할 때는

DATE_ADD( NOW(), INTERVAL 1 DAY )

 

 

시간을 뺄 때는

DATE_SUB( NOW(), INTERVAL 1 DAY )

 

 

DAY 자리에는 초,분,시,일,주,달등의 분류들을 사용할 수 있다.

MICROSECOND, SECOND, MINUTE, HOUR
DAY, WEEK, MONTH, QUARTER, YEAR

 

 

시간의 차이을 계산할 때는

TIMEDIFF( 시간1, 시간2 )

 

 

※ Oracle처럼 시간1 - 시간2 하면?

 

SELECT 시간1 - 시간2 FROM DUAL;

 

숫자가 나오긴 한다.

 

시간1 : 2018-05-01 16:27:11 이고

시간2 : 2018-05-01 11:38:30 일 때

결과 : 48881 이 나온다.

 

이 결과의 의미는?

왜 이런 결과가?

 

시간을 단순 숫자로 변환해서 빼기 시킨 결과이다.

 

162711 - 113830 = 48881

 

 

의도한 경우가 아니라면

단순 빼기 보다는

DATEDIFF 나 TIMEDIFF 를 써야한다.

 

 

CREATE TEMPORARY TABLE xxx ...

 

임시로 생성한 테이블에서 아래와 같은 Exception 이 발생한다면?

java.sql.SQLException: The table 'xxx' is full

 

 

MySQL 환경에 설정된 heap size와 temp table size를 조절해야한다.

 

 

* MySQL 재기동시 반영

 

 /etc/mysql 에 있는 mysql.cnf 파일을 수정한다.

 

 !includedir /etc/mysql/mysql.conf.d/

 

 이런 식으로 환경설정 파일을 나눠놨다면...

 해당 경로에 있는 mysql.cnf 파일을 수정한다.

 

원하는 크기로 설정하면 된다.

 max_heap_table_size = 256000000
 tmp_table_size = 256000000

 

 

* 실시간으로 반영 (재기동 불필요)

 

 root권한으로 접속해서 설정한다.

 

 mysql -u root -p

 

 현재 설정값을 확인해본다.

 

 SHOW VARIABLES LIKE 'max_heap_table_size';
 SHOW VARIABLES LIKE 'tmp_table_size';

 

 원하는 크기로 설정한다.

 

 SET GLOBAL max_heap_table_size = 256000000;
 SET GLOBAL tmp_table_size = 256000000;

 

 ※ 바로 (현재 연결) 반영되지 않는다.

    재 접속(다음 연결부터)하면, 반영된다.

 

 

Oracle의 RANK() OVER 같은 결과를 만들어 낼 수 있는 SQL이다.

 

특정 Key1를 기준으로

해당 Key1내에서 Key2를 기준으로 순위를 만드는 SQL이다.

 

(아래에서는 rnum을 기준으로, created에 따라서 순위를 만들고 있다.)

 

 SELECT *
 FROM ( SELECT a2.*
                    , @rn := CASE WHEN @key1 != rnum THEN 1 ELSE @rn + 1 END AS rank
                    , @rk := CASE WHEN @rn = 1 THEN 1
                                       WHEN @key2 = created THEN @rk
                                       ELSE @rn
                                END AS tmp_val
                    , @key1 := rnum
                    , @key2 := created
        FROM (
                    SELECT ... AS rnum
                            , ... AS created
                    :::
                 ) a2
               , ( SELECT @key1 := '', @key2 := '', @rn := 0, @rk = 0
                 ) b2
 ) x2
 WHERE  rank <= 3
 ORDER BY rnum, rank
 ;

 

 

 SELECT SQL_CALC_FOUND_ROWS *
 FROM ( SELECT @rownum := @rownum + 1 AS RNUM, a2.*
            FROM ( SELECT ...
                       ORDER BY ...
            ) a2
            JOIN ( SELECT @rownum:=0 ) R
 ) x2
 LIMIT 페이지당줄수 OFFSET ((페이지번호-1) * 페이지당줄수)
 ;

 

* 윗 명령에서 LIMIT OFFSET으로 자르기 이전의

  전체 Row의 수를 조회

  (이를 위해 SQL_CALC_FOUND_ROWS를 (반드시 첫줄에) 붙여서 조회하고, LIMIT 해야 한다.)

 

 SELECT FOUND_ROWS() AS RCNT
 ;


 

 

+ Recent posts