OSMC kodi
Install Korean language Install Korean Nanum font
|
1 |
sudo apt-get install fonts-nanum |
Download Noto-CJK fonts from here
|
1 2 |
cd ~osmc unzip NotoSansCJKtc_ttf.zip |
Copy fonts to skin directory
|
1 2 3 4 |
sudo cp /usr/share/fonts/truetype/nanum/NanumGothic.ttf /usr/share/kodi/media/Fonts/ sudo cp /usr/share/fonts/truetype/nanum/NanumGothic.ttf /usr/share/kodi/addons/skin.osmc/fonts/ sudo cp ~osmc/NotoSansCJKtc-Thin.ttf /usr/share/kodi/media/Fonts/ sudo cp ~osmc/NotoSansCJKtc-Thin.ttf /usr/share/kodi/addons/skin.osmc/fonts/ |
Change font in skins (example for osmc default skin)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
sudo cp /usr/share/kodi/addons/skin.osmc/16x9/Font.xml /usr/share/kodi/addons/skin.osmc/16x9/Font.xml.otg sudo vi /usr/share/kodi/addons/skin.osmc/16x9/Font.xml :%s/SourceSansPro-Regular.ttf/NotoSansCJKtc-Thin.ttf/g :%s/NotoSans-Regular.ttf/NotoSansCJKtc-Thin.ttf/g :%s/LiberationMono-Regular.ttf/NotoSansCJKtc-Thin.ttf/g :%s/Arial.ttf/NotoSansCJKtc-Thin.ttf/g :%s/OpenSans-Regular.ttf/NotoSansCJKtc-Thin.ttf/g :%s/Roboto-Light.ttf/NotoSansCJKtc-Thin.ttf/g :%s/Roboto-Thin.ttf/NotoSansCJKtc-Thin.ttf/g :%s/Roboto-Bold.ttf/NanumGothicBold.ttf/g :%s/NotoSans-Bold.ttf/NanumGothicBold.ttf/g :%s/NanumGothicBold.ttf/NotoSansCJKtc-Black.ttf/g |
Install hangul input
|
1 |
sudo apt-get install ibus-hangul |
Install Daum scraper Download addon from here
|
1 |
wget https://xbmc-korea-addons.googlecode.com/files/repository.xbmc-korea.com-1.0.7.zip |
Install addon at menu of KODI Measure temperature
|
1 |
sudo vcgencmd measure_temp |
Start/Stop kodi
|
1 |
sudo systemctl restart/stop/start mediacenter |
Install SRSd with Postfix on OpenSUSE
Postfix Sender Rewriting Scheme(SRS) daemon
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
zypper install postsrsd vi /etc/default/postsrsd SRS_DOMAIN=xxx.com vi /etc/postfix/main.cf inet_interfaces = all inet_protocols = ipv4 myhostname = smtp.xxx.com mynetworks_style = subnet mynetworks = 10.3.0.0/16, 192.168.1.0/24 relay_domains = $mydestination, hash:/etc/postfix/relay virtual_alias_domains = hash:/etc/postfix/virtual virtual_alias_maps = hash:/etc/postfix/virtual # Aiden: PostSRSd settings. sender_canonical_maps = tcp:localhost:10001 sender_canonical_classes = envelope_sender recipient_canonical_maps = tcp:localhost:10002 recipient_canonical_classes= envelope_recipient,header_recipient # Aiden: Optimizing maximal_queue_lifetime = 1d bounce_queue_lifetime = 1d maximal_backoff_time = 30m minimal_backoff_time = 5m queue_run_delay = 5m vi /etc/postfix/virtual @sss.com webmaster@company.com alert@ttt.com aiden@company.com,tom@company.com owner@ttt.com jane@company.com vi /etc/postfix/relay xxx.com OK sss.com OK ttt.com OK systemctl restart postsrsd systemctl restart postfix |
자체적으로 rotate되는 로그파일에 logrotate 적용하기
tomcat 기본설정 처럼 로그파일 이름이 자체적으로 rotate되어 버리는 경우, logrotate 로의 통합이 어렵다. 네이밍은 자체룰을 이용하고 압축을 하는 등 후작업을 logrotate에 넣는 방식으로 통합하는 방법이다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
vi /etc/logrotate.d/tomcat /var/log/tomcat-access-log-dummy { su root root rotate 0 create 664 tomcat root daily ifempty lastaction /usr/bin/find /home/www/*/logs/*.txt -mtime +30 -delete /usr/bin/find /home/www/*/logs/*.txt -mtime +1 -exec gzip -q {} \; endscript } touch /var/log/tomcat-access-log-dummy |
Apache 2.4 IP-ACL for ELB (also any other Proxy/LB)
As you all know, we use the ‘X-Forwarded-For’ header to determine the IP of client when the HTTPD is behind the ELB (or other Proxy/LB). However it is very difficult to set the IP-ACL in Apache configuration files common to the cases with and without LB. (due to the Apache environment variables) So I have been operating under this settings for some services.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
<IfModule mod_setenvif.c> SetEnvIf Server_Addr "(.+)" z_local_addr=$1 SetEnvIf Remote_Addr "^([0-9\.]+)" z_remote_addr=$1 SetEnvIf X-Forwarded-For "^([0-9\.]+)" z_remote_addr=$1 SetEnvIfNoCase Request_Protocol "^([a-z]+)" z_service_proto=$1 SetEnvIfNoCase X-Forwarded-Proto "^([a-z]+)" z_service_proto=$1 SetEnvIf z_remote_addr "^127\.0\.0\.1$" z_remote_trusted SetEnvIf z_remote_addr "^10\.1\.3\." z_remote_trusted SetEnvIf z_remote_addr "^192\.168\.1\." z_remote_trusted SetEnvIf z_remote_addr "^123\.123\.123\.254$" z_remote_trusted </IfModule> <Location /> <RequireAny> Require env z_remote_trusted Require expr "env('z_remote_addr') -ipmatch '22.33.44.0/24'" Require expr "env('z_remote_addr') in { '11.22.33.44', '11.22.33.55' }" </RequireAny> </Location> |
If you see “syntax error, unexpected $end”, just remove queotes of expressions. This is a bug of apache httpd.
|
1 2 3 4 5 6 7 |
<Location /> <RequireAny> Require env z_remote_trusted Require expr env('z_remote_addr') -ipmatch '22.33.44.0/24' Require expr env('z_remote_addr') in { '11.22.33.44', '11.22.33.55' } </RequireAny> </Location> |
Mysql General Procedures for DML using JSON data type
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 table insert/update via JSON parameter. When I apply them to my project, data-handling codes in the application decreased dramatically. (These procedures impliment ‘MySQL Procedure Pattern for Nested Transaction‘)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 |
DELIMITER $$ CREATE PROCEDURE `sp_do_insert_table_by_json`( IN `i_columns` JSON, IN `i_table` VARCHAR(64), IN `i_schema` VARCHAR(64), OUT `o_result` JSON ) body:BEGIN /** General Procedure for Table Inserting from JSON 2016-06 Aiden Kihyun Kim Parameters IN `i_columns` JSON : Columns {"col1_name":col1_value, "col2_name":"col2_value", ... } IN `i_table` VARCHAR(64) : Table name IN `i_schema` VARCHAR(64) : Database name OUT `o_result` JSON : {"return":return_code, "row_count":row_count, "last_insert_id":last_insert_id} **/ DECLARE v_autocommit BIT DEFAULT @@AUTOCOMMIT; DECLARE v_schema VARCHAR(64) DEFAULT NULL; DECLARE c_done BIT DEFAULT FALSE; DECLARE v_column VARCHAR(64) DEFAULT NULL; DECLARE v_column_key VARCHAR(64) DEFAULT NULL; DECLARE v_extra VARCHAR(64) DEFAULT NULL; DECLARE v_type VARCHAR(64) DEFAULT NULL; DECLARE v_query1 TEXT CHARSET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL; DECLARE v_query2 TEXT CHARSET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL; DECLARE require_rollback CONDITION FOR SQLSTATE '45000'; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SET o_result = JSON_SET(o_result, '$.return', -999); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF; END; DECLARE EXIT HANDLER FOR 1062 BEGIN SET o_result = JSON_SET(o_result, '$.return', -901); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF; END; DECLARE EXIT HANDLER FOR 1216,1452 BEGIN SET o_result = JSON_SET(o_result, '$.return', -902); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF; END; DECLARE EXIT HANDLER FOR 1217,1451 BEGIN SET o_result = JSON_SET(o_result, '$.return', -903); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF; END; DECLARE EXIT HANDLER FOR require_rollback BEGIN IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF; END; SET o_result = JSON_OBJECT('return', -1, 'row_count', -1); SET v_schema = IF(i_schema IS NULL, database(), v_schema); BEGIN DECLARE c_column CURSOR FOR SELECT COLUMN_NAME, COLUMN_KEY, EXTRA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=v_schema AND TABLE_NAME=i_table AND EXTRA<>'VIRTUAL GENERATED' ORDER BY ORDINAL_POSITION; DECLARE CONTINUE HANDLER FOR NOT FOUND SET c_done = TRUE; OPEN c_column; cursor_loop: LOOP FETCH c_column INTO v_column, v_column_key, v_extra; IF c_done THEN LEAVE cursor_loop; END IF; IF (v_column_key='PRI' AND v_extra='auto_increment') THEN SET o_result = JSON_SET(o_result, '$.last_insert_id', NULL); ELSEIF JSON_CONTAINS_PATH(i_columns,'one',CONCAT('$.',v_column)) THEN SET v_query1 = CONCAT(IF(v_query1 IS NULL,'',CONCAT(v_query1,', ')), '`', v_column, '`'); SET v_query2 = IF(v_query2 IS NULL,'',CONCAT(v_query2,', ')); SET v_type = JSON_TYPE(JSON_EXTRACT(i_columns,CONCAT('$.',v_column))); IF v_type='NULL' THEN SET v_query2 = CONCAT(v_query2, 'NULL'); ELSEIF v_type IN ('INTEGER','DOUBLE','DECIMAL','BIT') THEN SET v_query2 = CONCAT(v_query2, JSON_EXTRACT(i_columns, CONCAT('$.',v_column))); ELSE SET v_query2 = CONCAT(v_query2, '''', REPLACE(JSON_UNQUOTE(JSON_EXTRACT(i_columns, CONCAT('$.',v_column))),'''',''''''), ''''); END IF; END IF; END LOOP; CLOSE c_column; END; IF (v_query1 IS NULL) THEN SET o_result = JSON_SET(o_result, '$.return', -101); SIGNAL require_rollback; END IF; IF (v_query2 IS NULL) THEN SET o_result = JSON_SET(o_result, '$.return', -102); SIGNAL require_rollback; END IF; IF (v_autocommit = 1) THEN SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SET SESSION autocommit = 0; START TRANSACTION; END IF; SET @query = CONCAT('INSERT INTO `', v_schema, '`.`', i_table, '` (', v_query1, ') VALUES (', v_query2, ')'); PREPARE stmt FROM @query; EXECUTE stmt; SELECT ROW_COUNT(), LAST_INSERT_ID() INTO @row_count, @last_insert_id; DEALLOCATE PREPARE stmt; IF (v_autocommit = 1) AND (@@AUTOCOMMIT = 0) THEN COMMIT; SET SESSION autocommit = 1; END IF; IF JSON_CONTAINS_PATH(o_result,'one','$.last_insert_id') THEN SET o_result = JSON_SET(o_result, '$.return', 0, '$.row_count', @row_count, '$.last_insert_id', @last_insert_id); ELSE SET o_result = JSON_SET(o_result, '$.return', 0, '$.row_count', @row_count); END IF; END$$ DELIMITER ; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 |
DELIMITER $$ CREATE PROCEDURE `sp_do_update_table_by_json`( IN `i_columns` JSON, IN `i_conditions` JSON, IN `i_table` VARCHAR(64), IN `i_schema` VARCHAR(64), OUT `o_result` JSON ) body:BEGIN /** General Procedure for Table Updating from JSON 2016-06 Aiden Kihyun Kim Parameters IN `i_columns` JSON : Columns {"col1_name":col1_value, "col2_name":"col2_value", ... } IN `i_conditions` JSON : Search conditions {"col1_name":col1_value, "col2_name":"col2_value", ... } IN `i_table` VARCHAR(64) : Table name IN `i_schema` VARCHAR(64) : Database name OUT `o_result` JSON : {"return":return_code, "row_count":row_count} **/ DECLARE v_autocommit BIT DEFAULT @@AUTOCOMMIT; DECLARE v_schema VARCHAR(64) DEFAULT NULL; DECLARE c_done BIT DEFAULT FALSE; DECLARE v_column VARCHAR(64) DEFAULT NULL; DECLARE v_column_key VARCHAR(64) DEFAULT NULL; DECLARE v_extra VARCHAR(64) DEFAULT NULL; DECLARE v_type VARCHAR(64) DEFAULT NULL; DECLARE v_array JSON DEFAULT NULL; DECLARE v_query1 TEXT CHARSET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL; DECLARE v_query2 TEXT CHARSET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL; DECLARE require_rollback CONDITION FOR SQLSTATE '45000'; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SET o_result = JSON_SET(o_result, '$.return', -999); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF; END; DECLARE EXIT HANDLER FOR 1062 BEGIN SET o_result = JSON_SET(o_result, '$.return', -901); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF; END; DECLARE EXIT HANDLER FOR 1216,1452 BEGIN SET o_result = JSON_SET(o_result, '$.return', -902); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF; END; DECLARE EXIT HANDLER FOR 1217,1451 BEGIN SET o_result = JSON_SET(o_result, '$.return', -903); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF; END; DECLARE EXIT HANDLER FOR require_rollback BEGIN IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF; END; SET o_result = JSON_OBJECT('return', -1, 'row_count', -1); SET v_schema = IF(i_schema IS NULL, database(), v_schema); BEGIN DECLARE c_column CURSOR FOR SELECT COLUMN_NAME, COLUMN_KEY, EXTRA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=v_schema AND TABLE_NAME=i_table AND EXTRA<>'VIRTUAL GENERATED' ORDER BY ORDINAL_POSITION; DECLARE CONTINUE HANDLER FOR NOT FOUND SET c_done = TRUE; OPEN c_column; cursor_loop: LOOP FETCH c_column INTO v_column, v_column_key, v_extra; IF c_done THEN LEAVE cursor_loop; END IF; IF JSON_CONTAINS_PATH(i_columns,'one',CONCAT('$.',v_column)) THEN SET v_type = JSON_TYPE(JSON_EXTRACT(i_columns,CONCAT('$.',v_column))); SET v_query1 = CONCAT(IF(v_query1 IS NULL,'',CONCAT(v_query1,', ')), '`', v_column, '`='); IF v_type='NULL' THEN SET v_query1 = CONCAT(v_query1, 'NULL'); ELSEIF v_type IN ('INTEGER','DOUBLE','DECIMAL','BIT') THEN SET v_query1 = CONCAT(v_query1, JSON_EXTRACT(i_columns, CONCAT('$.',v_column))); ELSE SET v_query1 = CONCAT(v_query1, '''', REPLACE(JSON_UNQUOTE(JSON_EXTRACT(i_columns, CONCAT('$.',v_column))),'''',''''''), ''''); END IF; END IF; IF JSON_CONTAINS_PATH(i_conditions,'one',CONCAT('$.',v_column)) THEN SET v_type = JSON_TYPE(JSON_EXTRACT(i_conditions,CONCAT('$.',v_column))); SET v_query2 = CONCAT(IF(v_query2 IS NULL,'',CONCAT(v_query2,' AND ')), '`', v_column, '`'); IF v_type='NULL' THEN SET v_query2 = CONCAT(v_query2, '=NULL'); ELSEIF v_type IN ('INTEGER','DOUBLE','DECIMAL','BIT') THEN SET v_query2 = CONCAT(v_query2, '=', JSON_EXTRACT(i_conditions, CONCAT('$.',v_column))); ELSEIF v_type IN ('ARRAY','OBJECT') THEN SET v_array = JSON_EXTRACT(i_conditions,CONCAT('$.',v_column)); IF v_type = 'OBJECT' THEN IF JSON_CONTAINS_PATH(v_array,'one',CONCAT('$."2"')) THEN SET v_array = JSON_ARRAY(JSON_EXTRACT(v_array,'$."0"'),JSON_EXTRACT(v_array,'$."1"'),JSON_EXTRACT(v_array,'$."2"')); ELSE SET v_array = JSON_ARRAY(JSON_EXTRACT(v_array,'$."0"'),JSON_EXTRACT(v_array,'$."1"')); END IF; END IF; IF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'BETWEEN' THEN SET v_query2 = CONCAT(v_query2, ' BETWEEN ', JSON_EXTRACT(v_array,'$[1]'), ' AND ', JSON_EXTRACT(v_array,'$[2]')); ELSEIF JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]')) IN ('<','<=','>','>=') THEN SET v_query2 = CONCAT(v_query2, JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]')), JSON_EXTRACT(v_array,'$[1]')); ELSEIF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'LIKE' THEN SET v_query2 = CONCAT(v_query2, ' LIKE ''', REPLACE(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[1]')),'''',''''''), ''''); ELSEIF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'IN' THEN SET v_query2 = CONCAT(v_query2, ' IN (', JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[1]')), ')'); ELSEIF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'IS' THEN SET v_query2 = CONCAT(v_query2, ' IS ', JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[1]'))); END IF; ELSE SET v_query2 = CONCAT(v_query2, '=''', REPLACE(JSON_UNQUOTE(JSON_EXTRACT(i_conditions, CONCAT('$.',v_column))),'''',''''''), ''''); END IF; END IF; END LOOP; CLOSE c_column; END; IF (v_query1 IS NULL) THEN SET o_result = JSON_SET(o_result, '$.return', -101); SIGNAL require_rollback; END IF; IF (v_query2 IS NULL) THEN SET o_result = JSON_SET(o_result, '$.return', -102); SIGNAL require_rollback; END IF; IF (v_autocommit = 1) THEN SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SET SESSION autocommit = 0; START TRANSACTION; END IF; SET @query = CONCAT('UPDATE `', v_schema, '`.`', i_table, '` SET ', v_query1, ' WHERE ', v_query2); PREPARE stmt FROM @query; EXECUTE stmt; SELECT ROW_COUNT() INTO @row_count; DEALLOCATE PREPARE stmt; IF (v_autocommit = 1) AND (@@AUTOCOMMIT = 0) THEN COMMIT; SET SESSION autocommit = 1; END IF; SET o_result = JSON_SET(o_result, '$.return', 0, '$.row_count', @row_count); END$$ DELIMITER ; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 |
DELIMITER $$ CREATE PROCEDURE `sp_do_delete_table_by_json`( IN `i_conditions` JSON, IN `i_table` VARCHAR(64), IN `i_schema` VARCHAR(64), OUT `o_result` JSON ) body:BEGIN /** General Procedure for Table Row Deleting from JSON 2016-06 Aiden Kihyun Kim Parameters IN `i_conditions` JSON : Search conditions {"col1_name":col1_value, "col2_name":"col2_value", ... } IN `i_table` VARCHAR(64) : Table name IN `i_schema` VARCHAR(64) : Database name OUT `o_result` JSON : {"return":return_code, "row_count":row_count} **/ DECLARE v_autocommit BIT DEFAULT @@AUTOCOMMIT; DECLARE v_schema VARCHAR(64) DEFAULT NULL; DECLARE c_done BIT DEFAULT FALSE; DECLARE v_column VARCHAR(64) DEFAULT NULL; DECLARE v_column_key VARCHAR(64) DEFAULT NULL; DECLARE v_extra VARCHAR(64) DEFAULT NULL; DECLARE v_type VARCHAR(64) DEFAULT NULL; DECLARE v_array JSON DEFAULT NULL; DECLARE v_query TEXT CHARSET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL; DECLARE require_rollback CONDITION FOR SQLSTATE '45000'; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SET o_result = JSON_SET(o_result, '$.return', -999); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF; END; DECLARE EXIT HANDLER FOR 1062 BEGIN SET o_result = JSON_SET(o_result, '$.return', -901); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF; END; DECLARE EXIT HANDLER FOR 1216,1452 BEGIN SET o_result = JSON_SET(o_result, '$.return', -902); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF; END; DECLARE EXIT HANDLER FOR 1217,1451 BEGIN SET o_result = JSON_SET(o_result, '$.return', -903); IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF; END; DECLARE EXIT HANDLER FOR require_rollback BEGIN IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF; END; SET o_result = JSON_OBJECT('return', -1, 'row_count', -1); SET v_schema = IF(i_schema IS NULL, database(), v_schema); BEGIN DECLARE c_column CURSOR FOR SELECT COLUMN_NAME, COLUMN_KEY, EXTRA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=v_schema AND TABLE_NAME=i_table AND EXTRA<>'VIRTUAL GENERATED' ORDER BY ORDINAL_POSITION; DECLARE CONTINUE HANDLER FOR NOT FOUND SET c_done = TRUE; OPEN c_column; cursor_loop: LOOP FETCH c_column INTO v_column, v_column_key, v_extra; IF c_done THEN LEAVE cursor_loop; END IF; IF JSON_CONTAINS_PATH(i_conditions,'one',CONCAT('$.',v_column)) THEN SET v_type = JSON_TYPE(JSON_EXTRACT(i_conditions,CONCAT('$.',v_column))); SET v_query = CONCAT(IF(v_query IS NULL,'',CONCAT(v_query,' AND ')), '`', v_column, '`'); IF v_type='NULL' THEN SET v_query = CONCAT(v_query, '=NULL'); ELSEIF v_type IN ('INTEGER','DOUBLE','DECIMAL','BIT') THEN SET v_query = CONCAT(v_query, '=', JSON_EXTRACT(i_conditions, CONCAT('$.',v_column))); ELSEIF v_type IN ('ARRAY','OBJECT') THEN SET v_array = JSON_EXTRACT(i_conditions,CONCAT('$.',v_column)); IF v_type = 'OBJECT' THEN IF JSON_CONTAINS_PATH(v_array,'one',CONCAT('$."2"')) THEN SET v_array = JSON_ARRAY(JSON_EXTRACT(v_array,'$."0"'),JSON_EXTRACT(v_array,'$."1"'),JSON_EXTRACT(v_array,'$."2"')); ELSE SET v_array = JSON_ARRAY(JSON_EXTRACT(v_array,'$."0"'),JSON_EXTRACT(v_array,'$."1"')); END IF; END IF; IF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'BETWEEN' THEN SET v_query = CONCAT(v_query, ' BETWEEN ', JSON_EXTRACT(v_array,'$[1]'), ' AND ', JSON_EXTRACT(v_array,'$[2]')); ELSEIF JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]')) IN ('<','<=','>','>=') THEN SET v_query = CONCAT(v_query, JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]')), JSON_EXTRACT(v_array,'$[1]')); ELSEIF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'LIKE' THEN SET v_query = CONCAT(v_query, ' LIKE ''', REPLACE(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[1]')),'''',''''''), ''''); ELSEIF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'IN' THEN SET v_query = CONCAT(v_query, ' IN (', JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[1]')), ')'); ELSEIF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'IS' THEN SET v_query = CONCAT(v_query, ' IS ', JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[1]'))); END IF; ELSE SET v_query = CONCAT(v_query, '=''', REPLACE(JSON_UNQUOTE(JSON_EXTRACT(i_conditions, CONCAT('$.',v_column))),'''',''''''), ''''); END IF; END IF; END LOOP; CLOSE c_column; END; IF (v_query IS NULL) THEN SET o_result = JSON_SET(o_result, '$.return', -101); SIGNAL require_rollback; END IF; IF (v_autocommit = 1) THEN SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SET SESSION autocommit = 0; START TRANSACTION; END IF; SET @query = CONCAT('DELETE FROM `', v_schema, '`.`', i_table, '` WHERE ', v_query); PREPARE stmt FROM @query; EXECUTE stmt; SELECT ROW_COUNT() INTO @row_count; DEALLOCATE PREPARE stmt; IF (v_autocommit = 1) AND (@@AUTOCOMMIT = 0) THEN COMMIT; SET SESSION autocommit = 1; END IF; SET o_result = JSON_SET(o_result, '$.return', 0, '$.row_count', @row_count); END$$ DELIMITER ; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 |
DELIMITER $$ CREATE PROCEDURE `sp_do_search_table_by_json`( IN `i_conditions` JSON, IN `i_table` VARCHAR(64), IN `i_schema` VARCHAR(64), OUT `o_result` JSON ) body:BEGIN /** General Procedure for Table Searching from JSON 2016-06 Aiden Kihyun Kim Parameters IN `i_conditions` JSON : Search conditions {"col1_name":col1_value, "col2_name":"col2_value", ... } IN `i_table` VARCHAR(64) : Table name IN `i_schema` VARCHAR(64) : Database name OUT `o_result` JSON : {"return":return_code, "row_count":row_count} **/ DECLARE v_schema VARCHAR(64) DEFAULT NULL; DECLARE c_done BIT DEFAULT FALSE; DECLARE v_column VARCHAR(64) DEFAULT NULL; DECLARE v_column_key VARCHAR(64) DEFAULT NULL; DECLARE v_extra VARCHAR(64) DEFAULT NULL; DECLARE v_type VARCHAR(64) DEFAULT NULL; DECLARE v_array JSON DEFAULT NULL; DECLARE v_query1 TEXT CHARSET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL; DECLARE v_query2 TEXT CHARSET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL; DECLARE v_query3 TEXT CHARSET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL; DECLARE EXIT HANDLER FOR SQLEXCEPTION SET o_result = JSON_SET(o_result, '$.return', -999); SET o_result = JSON_OBJECT('return', -1, 'row_count', -1); SET v_schema = IF(i_schema IS NULL, database(), v_schema); BEGIN DECLARE c_column CURSOR FOR SELECT COLUMN_NAME, COLUMN_KEY, EXTRA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = v_schema AND TABLE_NAME = i_table ORDER BY ORDINAL_POSITION; DECLARE CONTINUE HANDLER FOR NOT FOUND SET c_done = TRUE; OPEN c_column; cursor_loop: LOOP FETCH c_column INTO v_column, v_column_key, v_extra; IF c_done THEN LEAVE cursor_loop; END IF; SET v_query1 = IF(v_query1 IS NULL,'',CONCAT(v_query1,', ')); IF (v_data_type = 'json') THEN SET v_query1 = CONCAT(v_query1, 'CAST(`', v_column, '` AS CHAR) AS ', v_column); ELSEIF (INSTR(v_data_type,'binary')>0) THEN SET v_query1 = CONCAT(v_query1, 'HEX(`', v_column, '`) AS ', v_column); ELSEIF (INSTR(v_data_type,'point')>0 OR INSTR(v_data_type,'linestring')>0 OR INSTR(v_data_type,'polygon')>0 OR INSTR(v_data_type,'geometry')>0) THEN SET v_query1 = CONCAT(v_query1, 'ST_AsText(`', v_column, '`) AS ', v_column); ELSE SET v_query1 = CONCAT(v_query1, '`', v_column, '`'); END IF; IF JSON_CONTAINS_PATH(i_conditions,'one',CONCAT('$.',v_column)) THEN SET v_type = JSON_TYPE(JSON_EXTRACT(i_conditions,CONCAT('$.',v_column))); IF v_type='NULL' THEN SET v_query3 = CONCAT('`', v_column, '`=NULL'); ELSEIF v_type IN ('INTEGER','DOUBLE','DECIMAL','BIT') THEN SET v_query3 = CONCAT('`', v_column, '`=', JSON_EXTRACT(i_conditions, CONCAT('$.',v_column))); ELSEIF v_type IN ('ARRAY','OBJECT') THEN SET v_array = JSON_EXTRACT(i_conditions,CONCAT('$.',v_column)); IF v_type = 'OBJECT' THEN IF JSON_CONTAINS_PATH(v_array,'one',CONCAT('$."2"')) THEN SET v_array = JSON_ARRAY(JSON_EXTRACT(v_array,'$."0"'),JSON_EXTRACT(v_array,'$."1"'),JSON_EXTRACT(v_array,'$."2"')); ELSE SET v_array = JSON_ARRAY(JSON_EXTRACT(v_array,'$."0"'),JSON_EXTRACT(v_array,'$."1"')); END IF; END IF; IF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'BETWEEN' THEN SET v_query3 = CONCAT('`', v_column, '` BETWEEN ', JSON_EXTRACT(v_array,'$[1]'), ' AND ', JSON_EXTRACT(v_array,'$[2]')); ELSEIF JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]')) IN ('<','<=','>','>=') THEN SET v_query3 = CONCAT('`', v_column, '`', JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]')), JSON_EXTRACT(v_array,'$[1]')); ELSEIF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'LIKE' THEN SET v_query3 = CONCAT('`', v_column, '` LIKE ''', REPLACE(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[1]')),'''',''''''), ''''); ELSEIF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'IN' THEN SET v_query3 = CONCAT('`', v_column, '` IN (', JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[1]')), ')'); ELSEIF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'IS' THEN SET v_query3 = CONCAT('`', v_column, '` IS ', JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[1]'))); ELSEIF UPPER(JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[0]'))) = 'MATCH' THEN SET v_query3 = CONCAT('MATCH (', v_column, ') AGAINST (''"', JSON_UNQUOTE(JSON_EXTRACT(v_array,'$[1]')), '"'' IN BOOLEAN MODE)' ); END IF; ELSE SET v_query3 = CONCAT('`', v_column, '`=''', REPLACE(JSON_UNQUOTE(JSON_EXTRACT(i_conditions, CONCAT('$.',v_column))),'''',''''''), ''''); END IF; SET v_query2 = CONCAT(IF(v_query2 IS NULL,' ',CONCAT(v_query2,' AND ')), v_query3); END IF; END LOOP; CLOSE c_column; END; IF (v_query1 IS NULL) THEN SET o_result = JSON_SET(o_result, '$.return', -101); LEAVE body; END IF; IF (v_query2 IS NULL) THEN SET v_query2 = '1=1'; END IF; SET @query = CONCAT('SELECT SQL_CALC_FOUND_ROWS ', v_query1, ' FROM `', v_schema, '`.`', i_table, '` WHERE ', v_query2); IF JSON_CONTAINS_PATH(i_conditions,'one','$.sort_field') AND JSON_TYPE(JSON_EXTRACT(i_conditions,'$.sort_field'))<>'NULL' THEN SET @query = CONCAT(@query, ' ORDER BY ', JSON_UNQUOTE(JSON_EXTRACT(i_conditions,'$.sort_field'))); IF JSON_CONTAINS_PATH(i_conditions,'one','$.sort_order') AND JSON_TYPE(JSON_EXTRACT(i_conditions,'$.sort_order'))<>'NULL' THEN SET @query = CONCAT(@query, ' ', JSON_UNQUOTE(JSON_EXTRACT(i_conditions,'$.sort_order'))); END IF; END IF; IF JSON_CONTAINS_PATH(i_conditions,'one','$.page') AND (JSON_TYPE(JSON_EXTRACT(i_conditions,'$.page'))='INTEGER' OR JSON_UNQUOTE(JSON_EXTRACT(i_conditions,'$.page')) REGEXP '[0-9]+') AND JSON_CONTAINS_PATH(i_conditions,'one','$.page_size') AND (JSON_TYPE(JSON_EXTRACT(i_conditions,'$.page_size'))='INTEGER' OR JSON_UNQUOTE(JSON_EXTRACT(i_conditions,'$.page_size')) REGEXP '[0-9]+') THEN SET @query = CONCAT(@query, ' LIMIT ', ((CAST(JSON_EXTRACT(i_conditions,'$.page') AS UNSIGNED)-1)*CAST(JSON_EXTRACT(i_conditions,'$.page_size') AS UNSIGNED)), ',', JSON_UNQUOTE(JSON_EXTRACT(i_conditions,'$.page_size'))); END IF; PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt; SELECT FOUND_ROWS() INTO @row_count; SET o_result = JSON_SET(o_result, '$.return', 0, '$.row_count', @row_count); END$$ DELIMITER ; |
AWS Solutions Architect Associate
I Passed the AWS Solutions Architect Associate Exam. The test is a quite difficult if you do not know exactly about the overall AWS. I’ll think about challenging professional qualifications the next step, if this certification useful.
Mysql Backup Script
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 |
#!/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 ! [ -d ${BASE_PATH} ]; then /bin/mkdir -p ${BASE_PATH} fi # Full backup if [[ "$1" == "full" ]]; then echo "MySQL Full Backup started `date`..." # prepare directory TSTR=$(date +"%Y%m%d_%H%M%S") BACKUP_PATH=${BASE_PATH}/${TSTR} if ! [[ -d ${BACKUP_PATH} ]]; then /bin/mkdir -p ${BACKUP_PATH} fi /bin/rm -rf ${BACKUP_PATH}/* # get old full backup information if [[ -r ${STATUS_PATH} ]]; then OLD_BACKUP_PATH=`/bin/cat ${STATUS_PATH} | /bin/sed -n '1p'` OLD_START_BINLOG=`/bin/cat ${STATUS_PATH} | /bin/sed -n '2p'` fi # mysqldump /usr/local/mysql/bin/mysqldump --all-databases --routines --events --hex-blob --single-transaction --flush-logs --master-data=2 --include-master-host-port > ${BACKUP_PATH}/full_backup-${TSTR}.sql START_BINLOG=`/bin/sed -rn "1,50 s/^.+ MASTER_LOG_FILE\='(mysql-bin\.[0-9]+)'.+$/\1/p" ${BACKUP_PATH}/full_backup-${TSTR}.sql | /bin/sed -n '1p'` echo "${BACKUP_PATH}" > ${STATUS_PATH} echo "${START_BINLOG}" >> ${STATUS_PATH} /bin/gzip ${BACKUP_PATH}/full_backup-${TSTR}.sql # finalize previous full backup data if [[ -n ${OLD_BACKUP_PATH} ]] && [[ -n ${OLD_START_BINLOG} ]] && [[ -d ${OLD_BACKUP_PATH} ]]; then for FILE in `ls ${BINLOG_PATH}/mysql-bin.?????? | sort -g` do if [[ "${FILE}" < "${BINLOG_PATH}/${START_BINLOG}" ]]; then if [[ "${FILE}" == "${BINLOG_PATH}/${OLD_START_BINLOG}" ]] || [[ "${FILE}" > "${BINLOG_PATH}/${OLD_START_BINLOG}" ]]; then /bin/cp -afpu ${FILE} ${OLD_BACKUP_PATH}/ fi fi done fi # Incremental backup else echo "MySQL Incremental Backup started..." # get previous full backup data if ! [[ -r ${STATUS_PATH} ]]; then echo "Could not find full backup status file." exit 1 fi BACKUP_PATH=`/bin/cat ${STATUS_PATH} | /bin/sed -n '1p'` START_BINLOG=`/bin/cat ${STATUS_PATH} | /bin/sed -n '2p'` TSTR=`/bin/echo ${BACKUP_PATH} | sed -rn "s/^.+\/(20[0-9]{6}_[0-9]{6})$/\1/p"` if ! [[ -d ${BACKUP_PATH} ]]; then echo "Could not find last full backup path." exit 2 fi if ! [[ -d ${BINLOG_PATH} ]]; then echo "Could not find mysql bin-log path." exit 2 fi # flush logs & copy bin-logs /usr/local/mysql/bin/mysqladmin flush-logs BINLOG_CURR=`ls -d ${BINLOG_PATH}/mysql-bin.?????? | sed 's/^.*\.//' | sort -g | tail -n 1` for FILE in `ls ${BINLOG_PATH}/mysql-bin.?????? | sort -g` do if [[ "${BINLOG_PATH}/mysql-bin.${BINLOG_CURR}" != "${FILE}" ]]; then if [[ "${FILE}" == "${BINLOG_PATH}/${START_BINLOG}" ]] || [[ "${FILE}" > "${BINLOG_PATH}/${START_BINLOG}" ]]; then /bin/cp -afpu ${FILE} ${BACKUP_PATH}/ fi fi done fi # sync to s3 bucket /usr/bin/aws s3 sync ${BACKUP_PATH}/ ${S3_PATH}/${TSTR}/ # remove old backups oldDirs=($(/bin/find ${BASE_PATH} -type d -mtime +${ARCHIVE_PERIOD})) oldDirLen=${#oldDirs[@]} for (( i=0; i<${oldDirLen}; i++ )); do /bin/rm -rf ${oldDirs[$i]} done exit 0 |
Executing
|
1 2 3 4 5 |
# Full Backup ./backup.mysql.sh full # Incremental Backup ./backup.mysql.sh |
Subversion Repogitories Backup Script
Features Full Backup (Dump) Incremental Backup (Dump) Local Backup & Remote(AWS S3) Upload Sample Environments REPOS_ROOT=/home/svn/repos BACKUP_ROOT=/home/backup/svn S3_PATH=s3://svn-backup/svn
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
#!/bin/bash #---------------------------------------- # Subversion Repogitories Backup Script # Revision 20160531 # Copyright (c) Aiden Kim # http://www.daemon.pe.kr #---------------------------------------- DSTR=$(date +"%Y%m%d") ARCHIVE_PERIOD=30 REPOS_ROOT=/home/svn/repos BACKUP_ROOT=/home/backup/svn S3_PATH=s3://svn-backup/svn # Each repogitories REPOS_DIRS=`ls -t ${REPOS_ROOT} 2>/dev/null` for DIR in ${REPOS_DIRS}; do # Create directory BACKUP_PATH=${BACKUP_ROOT}/repo-${DIR} if ! [ -d ${BACKUP_PATH} ]; then /bin/mkdir -p ${BACKUP_PATH} fi # Get lastest revision CURR_VER=`/usr/bin/svnlook youngest ${REPOS_ROOT}/${DIR} 2>/dev/null` if ! [[ ${CURR_VER} =~ ^[0-9]+$ ]] ; then echo "Could not get revision of repogitory ${DIR}. Skipped..." continue fi if [[ "$1" == "full" ]]; then # Full dump /usr/bin/svnadmin dump -q -r0:${CURR_VER} ${REPOS_ROOT}/${DIR} > ${BACKUP_PATH}/${DIR}-${DSTR}-full-${CURR_VER}.dump && /usr/bin/gzip -f ${BACKUP_PATH}/${DIR}-${DSTR}-full-${CURR_VER}.dump else # Find last full dump LAST_VER=`/usr/bin/ls -d ${BACKUP_PATH}/*-full-*.dump.gz | sed -r 's/^.*-full-([0-9]+)\.dump\.gz$/\1/' | sort -g | tail -n 1 2>/dev/null` if [[ ${LAST_VER} =~ ^[0-9]+$ ]] ; then if [ ${CURR_VER} -gt ${LAST_VER} ] ; then /usr/bin/svnadmin dump -q --incremental -r${LAST_VER}:${CURR_VER} ${REPOS_ROOT}/${DIR} > ${BACKUP_PATH}/${DIR}-${DSTR}-incremental-${LAST_VER}-${CURR_VER}.dump && /usr/bin/gzip -f ${BACKUP_PATH}/${DIR}-${DSTR}-incremental-${LAST_VER}-${CURR_VER}.dump else echo "Revision of ${DIR} was not changed. Current ${CURR_VER}, Last full-dump ${LAST_VER}. Skipped..." fi else echo "Could not file previous full-dump of ${DIR}. Skipped..." fi fi # Garbage rm -f ${BACKUP_PATH}/*.dump done # Sync to s3 bucket /usr/bin/aws s3 sync ${BACKUP_ROOT}/ ${S3_PATH}/ # Remove old files oldFiles=($(/bin/find ${BACKUP_ROOT} -type f -mtime +${ARCHIVE_PERIOD})) oldFilesLen=${#oldFiles[@]} for (( i=0; i<${oldFilesLen}; i++ )); do /bin/rm -rf ${oldFiles[$i]} done exit 0 |
Executing
|
1 2 3 4 5 |
# Full Backup ./backup.svn.sh full # Incremental Backup ./backup.svn.sh |
Don’t use utf8 encoding in MySQL (>= 5.5.3)
오늘날 한국어/중국어/일본어등 2바이트 문자권에서의 SW개발 및 다국어를 대비한 문자열 인코딩은 거의 UTF-8로 통일화 되고 있다. 그런데, MySQL의 문자인코딩 중 utf8인코딩은 ‘진짜 UTF-8’이 아니다 ! 원래 UTF-8은 규약상 한 문자가 4바이트까지 사용하는데 MySQL의 utf8인코딩은 3바이트까지만 입력이 가능하기 때문이다. 대부분의 한중일 문자는 3바이트 코드로 표현이 가능한데, 특히 특수문자나 한자를 많이 사용하는 일본어 등에서는 심심치 않게 4바이트 문자에 의한 문제가 나타난다. ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ‥‥‥
When Nagios send alert mails with attachement…
When Nagios send alert mails with attachement… Perhaps this problem is that if a device name or warning message include non-Latin characters. 1. Add new command to nagios configuration Original commands
|
1 2 3 4 5 6 7 8 9 10 11 |
# 'notify-host-by-email' command definition define command{ command_name notify-host-by-email command_line /usr/bin/printf "%b" "***** Nagios *****\n\nNotification Type: $NOTIFICATIONTYPE$\nHost: $HOSTNAME$\nState: $HOSTSTATE$\nAddress: $HOSTADDRESS$\nInfo: $HOSTOUTPUT$\n\nDate/Time: $LONGDATETIME$\n" | /usr/bin/mail -s "** $NOTIFICATIONTYPE$ Host Alert: $HOSTNAME$ is $HOSTSTATE$ **" $CONTACTEMAIL$ } # 'notify-service-by-email' command definition define command{ command_name notify-service-by-email command_line /usr/bin/printf "%b" "***** Nagios *****\n\nNotification Type: $NOTIFICATIONTYPE$\n\nService: $SERVICEDESC$\nHost: $HOSTALIAS$\nAddress: $HOSTADDRESS$\nState: $SERVICESTATE$\n\nDate/Time: $LONGDATETIME$\n\nAdditional Info:\n\n$SERVICEOUTPUT$\n" | /usr/bin/mail -s "** $NOTIFICATIONTYPE$ Service Alert: $HOSTALIAS$/$SERVICEDESC$ is $SERVICESTATE$ **" $CONTACTEMAIL$ } |
New commands
|
1 2 3 4 5 6 7 8 9 |
define command { command_name notify-host-by-email-encodingsafe command_line /usr/bin/printf "%b" "***** Nagios *****\n\nNotification Type: $NOTIFICATIONTYPE$\nHost: $HOSTNAME$\nState: $HOSTSTATE$\nAddress: $HOSTADDRESS$\nInfo: $HOSTOUTPUT$\n\nDate/Time: $LONGDATETIME$\n" | /usr/bin/env php /usr/local/bin/convert-encoding.php | /usr/bin/mail -s "** $NOTIFICATIONTYPE$ Host Alert: $HOSTNAME$ is $HOSTSTATE$ **" $CONTACTEMAIL$ } define command { command_name notify-service-by-email-encodingsafe command_line /usr/bin/printf "%b" "***** Nagios *****\n\nNotification Type: $NOTIFICATIONTYPE$\n\nService: $SERVICEDESC$\nHost: $HOSTALIAS$\nAddress: $HOSTADDRESS$\nState: $SERVICESTATE$\n\nDate/Time: $LONGDATETIME$\n\nAdditional Info:\n\n$SERVICEOUTPUT$\n" | /usr/bin/env php /usr/local/bin/convert-encoding.php | /usr/bin/mail -s "** $NOTIFICATIONTYPE$ Service Alert: $HOSTALIAS$/$SERVICEDESC$ is $SERVICESTATE$ **" $CONTACTEMAIL$ } |
2. Message filter script (PHP)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 |
<?php /* +-------------------------------------------------------------------------+ | Copyright (C) 2016 Aiden Kim | +-------------------------------------------------------------------------+ */ /* do NOT run this script through a web browser */ if (!isset($_SERVER['argv'][0]) || isset($_SERVER['REQUEST_METHOD']) || isset($_SERVER['REMOTE_ADDR'])) { die('<br><strong>This script is only meant to run at the command line.</strong>'); } /* We are not talking to the browser */ $no_http_headers = true; /* Supress all internal error */ error_reporting(0); /* process calling arguments */ $parms = $_SERVER['argv']; array_shift($parms); /* stdin */ $handle = fopen ("php://stdin","r"); $stdin = stream_get_contents($handle); if (sizeof($parms) || !empty($stdin)) { $input_string = ''; $output_string = ''; $input_file = ''; $output_file = ''; $input_encoding = ''; $output_encoding = 'UTF-8'; $detection_order = 'SJIS-win,SJIS,eucJP-win,EUC-JP,ISO-2022-JP-MS,ISO-2022-JP,CP51932,CP932,EUC-KR,ISO-2022-KR,GB18030,BIG-5,EUC-CN,EUC-TW,CP950,UTF-8,UTF-16,UCS-2,UCS-4,8bit,ASCII,7bit'; for ($i=0; $i<count($parms); $i++) { @list($arg, $value) = @explode('=', $parms[$i]); switch ($arg) { case '--input-file': $input_file = trim($value); break; case '--output-file': $output_file = trim($value); break; case '--input-encoding': $input_encoding = trim($value); break; case '--output-encoding': $output_encoding = trim($value); break; case '--detection-order': $detection_order = trim($value); break; default: if ( $i < count($parms)-1 ) { echo 'ERROR: Invalid Argument: ('.$arg.')'.PHP_EOL.PHP_EOL; exit(1); }else{ $input_string = trim($parms[$i]); } } } if (!empty($stdin)) { $input_string = $stdin; } if (empty($input_string)) { if (empty($input_file)) { echo 'ERROR: No Input String (Requiring at least one of [STRING] and [--input-file])'.PHP_EOL.PHP_EOL; display_help(); exit(1); }else{ $input_string = @file_get_contents($input_file); if ($input_string===false) { echo 'ERROR: Could Not Read Input File ('.$input_file.')'.PHP_EOL.PHP_EOL; exit(1); } } } if (empty($input_encoding)) { $input_encoding = @mb_detect_encoding($input_string, $detection_order); } $output_string = @mb_convert_encoding($input_string, $output_encoding, $input_encoding); if (!empty($output_file)) { $ret = @file_put_contents($output_file, $output_string); if ($ret===false) { echo 'ERROR: Could Not Write Output File ('.$output_file.')'.PHP_EOL.PHP_EOL; exit(1); } }else{ echo $output_string; } exit(0); } else { echo 'ERROR: no parameters given'.PHP_EOL.PHP_EOL; display_help(); exit(1); } function display_help() { echo 'Usage: convertcharset.php [OPTION]... [STRING]'.PHP_EOL; echo 'STRING is the source string will be encoded.'.PHP_EOL; echo 'OPTION:'.PHP_EOL; echo ' --input-file=FILE Path to the file where to read source data.'.PHP_EOL; echo ' --input-encoding=ENCODING The character code name of source data.'.PHP_EOL; echo ' --output-file=FILE Path to the file where to write converted data. '.PHP_EOL; echo ' --output-encoding=ENCODING The character code name of converted data. Default UTF-8.'.PHP_EOL; echo ' --detection-order=ENCODING[,ENCODING]... Encoding order list of character encoding. List may be comma separated list string. '.PHP_EOL; } ?> |
Actually I wanted to make a filter by Python. But it was less accurate than PHP to determine the encoding of the input string.
