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 |
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 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_query2 = CONCAT(IF(v_query2 IS NULL,'',CONCAT(v_query2,' AND ')), '`', v_column, '`'); SET v_type = JSON_TYPE(JSON_EXTRACT(i_conditions,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_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); 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' AND JSON_CONTAINS_PATH(i_conditions,'one','$.page_size') AND JSON_TYPE(JSON_EXTRACT(i_conditions,'$.page_size'))='INTEGER' THEN SET @query = CONCAT(@query, ' LIMIT ', ((JSON_EXTRACT(i_conditions,'$.page')-1)*JSON_EXTRACT(i_conditions,'$.page_size')), ',', 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.
AWS CodeDeploy 설정 (S3)
The flow of a typical AWS CodeDeploy deployment 설정 목록 EC2 On-Premises(EC2가 아닌 물리서버) CodeDeploy 서비스용 IAM 권한 생성 서버용 IAM 권한 생성 CodeDeploy Application 생성 EC2 설정 CodeDeploy Agent 설치/설정 On-Premise 서버 설정 배포 파일 준비, appspec.yml 설정 [반복] 배포 [반복] CodeDeploy 서비스용 IAM 권한 생성 [AWS-Console] IAM > Roles : Create New Role Role Type : AWS CodeDeploy Attach Policy : AWSCodeDeployRole 서버용 IAM 권한 생성 [AWS-Console] IAM > Policies : Create Policy
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": [ "s3:Get*", "s3:List*" ], "Resource": [ "*", "arn:aws:s3:::my_source_bucket_name/*" ] } ] } |
[AWS-Console] IAM > Roles : Create New Role Role Type : AWS EC2 Attach Policy : 위 Policy를 선택 CodeDeploy Application ‥‥‥
rsyslog + MySQL + LogAnalyzer on OpenSUSE
1. Install requirements
|
1 |
zypper install rsyslog rsyslog-module-relp rsyslog-module-mysql rsyslog-module-snmp rsyslog-module-mmnormalize |
2. Create RuleBase for PHP log
|
1 2 3 4 5 |
vi /etc/rsyslog.d/phplog.rulebase # PHP Logs rule=: %server:char-to:\x3a%\x3a PHP %priority:char-to:\x3a%\x3a%message:rest% rule=: PHP %priority:char-to:\x3a%\x3a%message:rest% |
3. Prepare 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 |
CREATE DATABASE `syslog`; USE `syslog`; CREATE USER 'syslog'@'localhost' IDENTIFIED BY 'my_password'; GRANT EXECUTE, INSERT, LOCK TABLES, SELECT, SHOW VIEW, UPDATE ON syslog.* TO 'syslog'@'localhost'; FLUSH PRIVILEGES; CREATE TABLE `SystemEvents` ( `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `CustomerID` bigint(20) DEFAULT NULL, `ReceivedAt` datetime DEFAULT NULL, `DeviceReportedTime` datetime DEFAULT NULL, `Facility` smallint(6) DEFAULT NULL, `Priority` smallint(6) DEFAULT NULL, `FromHost` varchar(60) DEFAULT NULL, `Message` text, `NTSeverity` int(11) DEFAULT NULL, `Importance` int(11) DEFAULT NULL, `EventSource` varchar(60) DEFAULT NULL, `EventUser` varchar(60) DEFAULT NULL, `EventCategory` int(11) DEFAULT NULL, `EventID` int(11) DEFAULT NULL, `EventBinaryData` text, `MaxAvailable` int(11) DEFAULT NULL, `CurrUsage` int(11) DEFAULT NULL, `MinUsage` int(11) DEFAULT NULL, `MaxUsage` int(11) DEFAULT NULL, `InfoUnitID` int(11) DEFAULT NULL, `SysLogTag` varchar(60) DEFAULT NULL, `ProcessID` varchar(60) DEFAULT NULL, `EventLogType` varchar(60) DEFAULT NULL, `GenericFileName` varchar(60) DEFAULT NULL, `SystemID` int(11) DEFAULT NULL, `Checksum` int(11) DEFAULT NULL, PRIMARY KEY (`ID`), KEY `ie1_SystemEvents` (`DeviceReportedTime`), KEY `ie2_SystemEvents` (`FromHost`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; DELIMITER $$ ALTER DEFINER=`root`@`%` EVENT `evt_purge_old_events` ON SCHEDULE EVERY 1 DAY STARTS '2010-01-01 06:00:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN DELETE FROM syslog.SystemEvents WHERE DeviceReportedTime < SUBDATE(CURRENT_TIMESTAMP, INTERVAL 3 MONTH); END$$ DELIMITER; |
4. rsyslog Configuration for Log-Servers
|
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 |
###################### # Aiden (server) ###################### module(load="mmnormalize") module(load="imrelp") module(load="imtcp" keepalive="on") module(load="imudp") module(load="ommysql") input(type="imrelp" port="2514" keepalive="on" ruleset="remote") input(type="imtcp" port="1514" ruleset="remote") input(type="imudp" port="514" ruleset="remote") $AllowedSender TCP, 127.0.0.1, 10.0.1.0/24, 192.168.1.0/24 $AllowedSender UDP, 127.0.0.1, 10.0.1.0/24, 192.168.1.0/24 template(name="aidenSQLformat" type="string" option.sql="on" string="insert into SystemEvents (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag, ProcessID) values (TRIM('%msg%'), %syslogfacility%, '%hostname%', %syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', %iut%, '%syslogtag:R,ERE,1,FIELD:([^\\[]+)(\\[[0-9]{1,5}\\])*:--end%', '%syslogtag:R,ERE,1,BLANK:\\[([0-9]{1,5})\\]--end%')") template(name="aidenSQL4PHPformat" type="string" option.sql="on" string="insert into SystemEvents (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag, ProcessID) values ('%$!msg%', %$!facility%, '%hostname%', %$!syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', %iut%, '%$!syslogtag%', '%syslogtag:R,ERE,1,BLANK:\\[([0-9]{1,5})\\]--end%')") ruleset(name="php2db") { if not ($syslogtag contains_i 'suhosin') then { action(type="mmnormalize" rulebase="/etc/rsyslog.d/phplog.rulebase") if ($parsesuccess == 'OK') then { set $!syslogtag = 'php'; set $!facility = 3; set $!msg = 'PHP ' & $!priority & ':' & $!message; if ($!priority contains_i 'error') then { set $!syslogpriority = 3; action(type="ommysql" server="localhost" db="syslog" uid="syslog" pwd="asd34R8SSc32asd" template="aidenSQL4PHPformat" serverport="3306") }else if ($!priority contains_i 'warning') then { set $!syslogpriority = 4; action(type="ommysql" server="localhost" db="syslog" uid="syslog" pwd="asd34R8SSc32asd" template="aidenSQL4PHPformat" serverport="3306") }else if ($!priority contains_i 'notice') then { set $!syslogpriority = 5; action(type="ommysql" server="localhost" db="syslog" uid="syslog" pwd="asd34R8SSc32asd" template="aidenSQL4PHPformat" serverport="3306") } } } if ($fromhost-ip == '127.0.0.1') then action(type="omfile" file="/var/log/php.log") } ruleset(name="remote") { if ($syslogtag contains_i 'php' or $syslogtag contains_i 'httpd2-prefork' or $syslogtag contains_i 'httpd-prefork' or \ $syslogtag contains_i 'ool' or $syslogtag contains_i 'suhosin') then { call php2db }else{ action(type="ommysql" server="localhost" db="syslog" uid="syslog" pwd="asd34R8SSc32asd" template="aidenSQLformat" serverport="3306") } if ($fromhost-ip != '127.0.0.1') then action(type="omfile" file="/var/log/remote") stop } #-------------- if ($syslogpriority <= 4 and ($msg contains_i 'kernel does not support IPv6' or $msg contains_i 'Failed to parse address value')) then { # bug -/var/log/messages stop } if ($syslogpriority <= 4 and ($msg contains_i 'Failed to open private bus connection' or $msg contains_i 'protocol dhcp covered by a ipv4')) then { # bug -/var/log/messages stop } if ($syslogpriority <= 4 and ($msg contains_i 'Could not load host key' or $msg contains_i 'audit_printk_skb')) then { # bug -/var/log/auth.log stop } if ($syslogtag contains_i 'php' or $syslogtag contains_i 'httpd2-prefork' or $syslogtag contains_i 'httpd-prefork' or \ $syslogtag contains_i 'ool' or $syslogtag contains_i 'suhosin') then { call php2db stop } *.=warning;*.=err;*.=crit action(type="ommysql" server="localhost" db="syslog" uid="syslog" pwd="asd34R8SSc32asd" template="aidenSQLformat" serverport="3306") if ($syslogpriority == 5 and ($msg contains_i 'error' or $msg contains_i 'exception' or $msg contains_i 'warning')) then \ action(type="ommysql" server="localhost" db="syslog" uid="syslog" pwd="asd34R8SSc32asd" template="aidenSQLformat" serverport="3306") if ($syslogtag startswith 'kernel' and $msg contains_i 'audit:') then { -/var/log/auth.log stop } auth,authpriv.* -/var/log/auth.log & stop cron.* -/var/log/cron.log & stop if ($msg contains_i 'pam_unix(crond:session)') then { -/var/log/cron.log stop } local5.* -/var/log/sftpd.log & stop if ($syslogtag contains_i 'internal-sftp') then { -/var/log/sftpd.log stop } if ($syslogtag contains_i 'tomcat') then { -/var/log/tomcat.log stop } ###################### |
5. rsyslog Configuration for Log-Client
|
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 |
###################### # Aiden (client) ###################### module(load="mmnormalize") module(load="omrelp") ruleset(name="php2remote") { if not ($syslogtag contains_i 'suhosin') then { action(type="mmnormalize" rulebase="/etc/rsyslog.d/phplog.rulebase") if ($parsesuccess == 'OK') then { if ($!priority contains_i 'error' or $!priority contains_i 'warning' or $!priority contains_i 'notice') then { action(type="omrelp" target="logserver_ip_or_name" port="2514") } } } action(type="omfile" file="/var/log/php.log") } #-------------- if ($syslogpriority <= 4 and ($msg contains_i 'kernel does not support IPv6' or $msg contains_i 'Failed to parse address value')) then { # bug -/var/log/messages stop } if ($syslogpriority <= 4 and ($msg contains_i 'Failed to open private bus connection' or $msg contains_i 'protocol dhcp covered by a ipv4')) then { # bug -/var/log/messages stop } if ($syslogpriority <= 4 and ($msg contains_i 'Could not load host key' or $msg contains_i 'audit_printk_skb')) then { # bug -/var/log/auth.log stop } if ($syslogtag contains_i 'php' or $syslogtag contains_i 'httpd2-prefork' or $syslogtag contains_i 'httpd-prefork' or \ $syslogtag contains_i 'ool' or $syslogtag contains_i 'suhosin') then { call php2remote stop } *.=warning;*.=err;*.=crit action(type="omrelp" target="logserver_ip_or_name" port="2514") if ($syslogpriority == 5 and ($msg contains_i 'error' or $msg contains_i 'exception' or $msg contains_i 'warning')) then \ action(type="omrelp" target="logserver_ip_or_name" port="2514") if ($syslogtag startswith 'kernel' and $msg contains_i 'audit:') then { -/var/log/auth.log stop } auth,authpriv.* -/var/log/auth.log & stop cron.* -/var/log/cron.log & stop if ($msg contains_i 'pam_unix(crond:session)') then { -/var/log/cron.log stop } local5.* -/var/log/sftpd.log & stop if ($syslogtag contains_i 'internal-sftp') then { -/var/log/sftpd.log stop } if ($syslogtag contains_i 'tomcat') then { -/var/log/tomcat.log stop } ###################### |
6. Install LogAnalyzer
|
1 2 3 4 5 |
cd /usr/local/src wget http://download.adiscon.com/loganalyzer/loganalyzer-4.1.3.tar.gz tar -zxvf loganalyzer-4.1.3.tar.gz mkdir -p /srv/www/htdocs/loganalyzer cp /usr/local/src/loganalyzer-4.1.3/src/* /srv/www/htdocs/loganalyzer/ |
7. LogAnalyzer Configuration http://localhost/loganalyzer ♦ References http://www.the-art-of-web.com/system/rsyslog-config/ http://www.liblognorm.com/files/manual/configuration.html
Upload PKCS#12 Server Certificates to AWS
1. Extract RSA Private key
|
1 2 |
openssl pkcs12 -in CERTIFICATE.PFX -nocerts -nodes -out private.key openssl rsa -in private.key -out private-rsa.key |
2. Extract Certificate
|
1 |
openssl pkcs12 -in CERTIFICATE.PFX -clcerts -nokeys -out certificate.crt |
3. Extract Certificate Chains
|
1 |
openssl pkcs12 -in CERTIFICATE.PFX -cacerts -nokeys -out certificate-chain.crt |
4. Trimming Certificates for AWS
|
1 2 |
sed -ne '/-----BEGIN CERTIFICATE-----/,/-----END CERTIFICATE-----/ p' certificate.crt > certificate-trim.crt sed -ne '/-----BEGIN CERTIFICATE-----/,/-----END CERTIFICATE-----/ p' certificate-chain.crt > certificate-chain-trim.crt |
5. Upload Key & Certificates to AWS via CLI
|
1 |
aws iam upload-server-certificate --profile profile --server-certificate-name domain.com --certificate-body file://certificate-trim.crt --private-key file://private-rsa.key --certificate-chain file://certificate-chain-trim.crt |
