ICT Network Project

Requerimientos

Server Linux MySQL

Configuracion en el server de MySQL

slow_query_log
innodb_file_per_table
tmp_table_size=2G
max_heap_table_size=2G
max_allowed_packet=40M
log_warnings
innodb_buffer_pool_size=2G
innodb_flush_log_at_trx_commit=2
key_buffer_size=2G

Dump base de datos

-- phpMyAdmin SQL Dump
-- version 4.2.12deb2+deb8u2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 07, 2017 at 05:14 PM
-- Server version: 5.5.55-0+deb8u1
-- PHP Version: 5.6.30-0+deb8u1

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `squid_log_db`
--
CREATE DATABASE IF NOT EXISTS `squid_log_db` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `squid_log_db`;

DELIMITER $$
--
-- Procedures
--
DROP PROCEDURE IF EXISTS `create_access_log_table`$$
CREATE DEFINER=`root`@`%` PROCEDURE `create_access_log_table`()
BEGIN




DROP TABLE IF EXISTS `access_log`;
CREATE TABLE IF NOT EXISTS `access_log` (
  `time_since_epoch` decimal(15,3) DEFAULT NULL,
  `response_time` int(11) DEFAULT NULL,
  `client_src_ip_addr` char(15) DEFAULT NULL,
  `squid_request_status` varchar(40) DEFAULT NULL,
  `http_status_code` varchar(10) DEFAULT NULL,
  `reply_size` int(11) DEFAULT NULL,
  `request_method` varchar(20) DEFAULT NULL,
  `request_url` varchar(1000) DEFAULT NULL,
  `username` varchar(20) DEFAULT NULL,
  `squid_hier_status` varchar(20) DEFAULT NULL,
  `server_ip_addr` char(15) DEFAULT NULL,
  `mime_type` varchar(50) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1;

END$$

DROP PROCEDURE IF EXISTS `squid_log_main`$$
CREATE DEFINER=`squid_log`@`%` PROCEDURE `squid_log_main`()
BEGIN 


DECLARE fecha_actual decimal(15,3); 
DECLARE dias char(10); 
DECLARE ahora datetime; 
DECLARE ciclo TINYINT(1);
DECLARE valor_dias char(11); 
DECLARE done TINYINT(1) DEFAULT 0; 
DECLARE q_full_log_create text; 
DECLARE q_full_log_insert text; 

DECLARE dias_en_el_log CURSOR FOR SELECT from_unixtime(time_since_epoch,'%Y_%m_%d') as dias  from full_access_log group by dias; 

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; 

SET done = 0; 
SET ciclo = 0;
SET ahora = now(); 
SET fecha_actual = UNIX_TIMESTAMP(); 

SET max_heap_table_size = 1024*1024*1024*6; 


select now(), "00 - Version 2017-10-10.1"; 

select now(), "01 - Creando tablas necesarias si no existen"; 

CREATE TABLE IF NOT EXISTS `totales_10` ( 
 `usuario` varchar(32) NOT NULL, 
 `tiempo` date NOT NULL, 
 `consumo` int(15) NOT NULL, 
 `tadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP 
) ENGINE=INNODB DEFAULT CHARSET=latin1; 


CREATE TABLE IF NOT EXISTS `totales_full` ( 
 `usuario` varchar(32) NOT NULL, 
 `tiempo` date NOT NULL, 
 `consumo` int(15) NOT NULL, 
 `tadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP 
) ENGINE=INNODB DEFAULT CHARSET=latin1; 


CREATE TABLE IF NOT EXISTS `access_log` ( 
 `time_since_epoch` decimal(15,3) DEFAULT NULL, 
 `response_time` int(11) DEFAULT NULL, 
 `client_src_ip_addr` char(15) DEFAULT NULL, 
 `squid_request_status` varchar(40) DEFAULT NULL, 
 `http_status_code` varchar(10) DEFAULT NULL, 
 `reply_size` int(11) DEFAULT NULL, 
 `request_method` varchar(20) DEFAULT NULL, 
 `request_url` varchar(1000) DEFAULT NULL, 
 `username` varchar(20) DEFAULT NULL, 
 `squid_hier_status` varchar(20) DEFAULT NULL, 
 `server_ip_addr` char(15) DEFAULT NULL, 
 `mime_type` varchar(50) DEFAULT NULL 
) ENGINE=MEMORY DEFAULT CHARSET=latin1; 


CREATE TABLE IF NOT EXISTS `usuarios` ( 
 `usuario` varchar(32) NOT NULL, 
 `cuota` int(20) NOT NULL DEFAULT '0', 
 `total` bigint(20) NOT NULL DEFAULT '0', 
 `ldap` varchar(256) NOT NULL, 
 `actualizado` datetime NOT NULL, 
 `total30` bigint(20) NOT NULL DEFAULT '0', 
 `cuota2` int(20) NOT NULL DEFAULT '0', 
 PRIMARY KEY (`usuario`) 
) ENGINE=MyISAM DEFAULT CHARSET=latin1; 

CREATE TABLE IF NOT EXISTS full_log_10 ( 
               `response_time` int(11) DEFAULT NULL, 
               `client_src_ip_addr` char(15) DEFAULT NULL, 
               `squid_request_status` varchar(40) DEFAULT NULL, 
               `http_status_code` varchar(10) DEFAULT NULL, 
               `reply_size` int(11) DEFAULT NULL, 
               `request_method` varchar(20) DEFAULT NULL, 
               `request_url` varchar(1000) DEFAULT NULL, 
               `username` varchar(20) DEFAULT NULL, 
               `squid_hier_status` varchar(20) DEFAULT NULL, 
               `server_ip_addr` char(15) DEFAULT NULL, 
               `mime_type` varchar(50) DEFAULT NULL, 
               `network_id` int(11) DEFAULT NULL, 
               `domain_id` int(11) DEFAULT 0, 
               `request_date` DATETIME NOT NULL 
               ) ENGINE=INNODB DEFAULT CHARSET=latin1;
               

CREATE TABLE IF NOT EXISTS `sess` (
  `dir_ip` varchar(15) NOT NULL,
  `usuario` varchar(32) NOT NULL,
  `tiempo` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=MEMORY DEFAULT CHARSET=latin1;


select now(), "02 - Creando full_access_log en memoria si no existe y limpiandola de datos anteriores"; 

DROP TABLE IF EXISTS `full_access_log`; 
CREATE TABLE IF NOT EXISTS `full_access_log` ( 
 `time_since_epoch` decimal(15,3) DEFAULT NULL, 
 `response_time` int(11) DEFAULT NULL, 
 `client_src_ip_addr` char(15) DEFAULT NULL, 
 `squid_request_status` varchar(40) DEFAULT NULL, 
 `http_status_code` varchar(10) DEFAULT NULL, 
 `reply_size` int(11) DEFAULT NULL, 
 `request_method` varchar(20) DEFAULT NULL, 
 `request_url` varchar(1000) DEFAULT NULL, 
 `username` varchar(20) DEFAULT NULL, 
 `squid_hier_status` varchar(20) DEFAULT NULL, 
 `server_ip_addr` char(15) DEFAULT NULL, 
 `mime_type` varchar(50) DEFAULT NULL, 
 `network_id` int(11) DEFAULT NULL, 
 `domain_id` int(11) DEFAULT 0, 
 `domain_name` varchar(255) DEFAULT NULL, 
 `request_date` DATETIME NOT NULL, 
 `network_name` char(15) DEFAULT NULL 
) ENGINE=MEMORY DEFAULT CHARSET=latin1; 

TRUNCATE full_access_log; 



select now(), "03 - Pasando desde access_log a full_access_log para analisis posterior"; 

INSERT INTO full_access_log(time_since_epoch,response_time,client_src_ip_addr,squid_request_status,http_status_code, 
           reply_size,request_method,request_url,username,squid_hier_status,server_ip_addr,mime_type) 
  select * from access_log where time_since_epoch < fecha_actual; 




select now(), "04 - Cantidad de records en access_log      :", COUNT(*) FROM access_log; 



select now(), "05 - Cantidad de records en full_access_log :", COUNT(*) FROM full_access_log; 



select now(), "06 - Fecha actual", fecha_actual; 



select now(), "07 - Borrando lo que ya se paso desde access_log"; 

-- select count(*) from access_log where time_since_epoch < fecha_actual;
delete from access_log where time_since_epoch < fecha_actual; 
  

select now(), "08 - actualizando full_access_log para el nombre de la red"; 

update full_access_log set network_name=SUBSTRING_INDEX(full_access_log.client_src_ip_addr, '.', 3); 



select now(), "09 - actualizando networks base"; 
insert ignore into networks(base) select network_name as base from full_access_log; 



select now(), "10 - actualizando full_access_log y networks donde los nombres y las bases coinciden"; 

update full_access_log,networks set full_access_log.network_id=networks.id where full_access_log.network_name = networks.base; 



select now(), "11 - actualizando full_access_log con los nombres de dominios"; 

update full_access_log set domain_name=getDomainName(request_url); 



select now(), "12 - actualizando la tabla domains"; 
insert ignore into domains(domain) select domain_name as domain from full_access_log; 



select now(), "13 - actualizando los grupos de dominios"; 

update domains set domains.group_id=domains.id where domains.group_id=0; 



select now(), "14 - actualizando full_access_log con los nuevos grupos de dominios"; 

update full_access_log, domains set domain_id = domains.group_id where full_access_log.domain_name = domains.domain ; 



select now(), "15 - actualizando  full_access_log campo request_date desde formato unix a normal"; 

update full_access_log set request_date=FROM_UNIXTIME(time_since_epoch); 



select now(), "16 - Buscando dias en el log"; 

OPEN dias_en_el_log; 
REPEAT 
   FETCH dias_en_el_log INTO valor_dias; 
   IF NOT done THEN
    set @q_full_log_create = CONCAT("
    CREATE TABLE IF NOT EXISTS full_log_",valor_dias," ( 
               `response_time` int(11) DEFAULT NULL, 
               `client_src_ip_addr` char(15) DEFAULT NULL, 
               `squid_request_status` varchar(40) DEFAULT NULL, 
               `http_status_code` varchar(10) DEFAULT NULL, 
               `reply_size` int(11) DEFAULT NULL, 
               `request_method` varchar(20) DEFAULT NULL, 
               `request_url` varchar(1000) DEFAULT NULL, 
               `username` varchar(20) DEFAULT NULL, 
               `squid_hier_status` varchar(20) DEFAULT NULL, 
               `server_ip_addr` char(15) DEFAULT NULL, 
               `mime_type` varchar(50) DEFAULT NULL, 
               `network_id` int(11) DEFAULT NULL, 
               `domain_id` int(11) DEFAULT 0, 
               `request_date` DATETIME NOT NULL 
               ) ENGINE=INNODB DEFAULT CHARSET=latin1;"); 
     PREPARE stmt1 FROM @q_full_log_create;
     EXECUTE stmt1 ; 
     DEALLOCATE PREPARE stmt1; 
    set @q_full_log_insert = CONCAT("
     INSERT INTO          full_log_",valor_dias,"(response_time,client_src_ip_addr,`squid_request_status`,`http_status_code`,`reply_size`,`request_method`,`request_url`, 
                                      `username`,`squid_hier_status`,`server_ip_addr`,`mime_type`,`network_id`,`domain_id`,`request_date`) 
        SELECT response_time,client_src_ip_addr,`squid_request_status`,`http_status_code`,`reply_size`,`request_method`,`request_url`, 
                                      `username`,`squid_hier_status`,`server_ip_addr`,`mime_type`,`network_id`,`domain_id`,`request_date` 
        FROM full_access_log; 
    "); 
   PREPARE stmt2 FROM @q_full_log_insert; 
   EXECUTE stmt2; 
   DEALLOCATE PREPARE stmt2;
   END IF; 
UNTIL done END REPEAT ;
CLOSE dias_en_el_log;


-- empezar limpieza de full_access_log para quitar todo lo que no se desea contar
select now(), "17 - Limpiando full_access_log", count(*) from full_access_log;
delete from full_access_log where squid_request_status = "TAG_NONE";
delete from full_access_log where squid_request_status = "TAG_NONE_ABORTED";
delete from full_access_log where squid_request_status = "TAG_NONE_TIMEDOUT";
delete from full_access_log where squid_request_status = "TCP_DENIED";
delete from full_access_log where squid_request_status = "TCP_DENIED_ABORTED";
delete from full_access_log where squid_request_status = "TCP_DENIED_TIMEDOUT";
delete from full_access_log where squid_request_status = "TCP_DENIED_TIMEDOUT";
-- no contrar lo que se pide desde las casas
delete from full_access_log where instr(client_src_ip_addr,'10.71.54.') = 1;
-- no contar lo que se pide local
delete from full_access_log where instr(server_ip_addr,'10.') = 1;
-- no contar lo que se pida antes de las 8 y depsues de las 5
-- delete from full_access_log where hour(request_date) < 8 ;
-- delete from full_access_log where hour(request_date) >= 17 ;
select now(), "17a - Limpiando full_access_log", count(*) from full_access_log;

select now(), "18 - Calculando los totales desde full_access_log y almacenando en totales_10";

insert into totales_10
 select username as usuario, DATE_FORMAT(request_date, '%Y-%m-%d' ) as tiempo, sum(reply_size) as consumo , now() as tadded 
   from full_access_log  group by username, tiempo;
   

-- select now(), "19 - borrando de totales_full los datos con mas de 10 dias";
-- se mantienen 10 dias para estudios de traficos pero en realidad no hace falta tanto
-- delete from totales_full where datediff(now(), tiempo) > 10; 

-- no voy a guardar mas de 10 dias
-- select now(), "20 - pasando a totales_full lo que este en totales_10 desde hace mas de ",ciclo,"dias"; 
-- insert into totales_full 
-- (select * from totales_10 where datediff(ahora, tiempo) > ciclo); 

select now(), "21 - borrar de totales_10 lo que tenga mas de 10 dias"; 
delete from totales_10 where datediff(ahora, tiempo) > 10; 

select now(), "22 - borrando de usuarios los que no se han actualizado en 30 dias:", count(*) from usuarios where datediff(now(), actualizado) > 30;
delete from usuarios where datediff(ahora, actualizado) > 30; 

select now(), "23 - limpiando total y total30 en tabla usuarios"; 
update usuarios set total=0; 

select now(), "24 - insertar en usuarios total el consumo desde totales_10"; 
insert into usuarios(usuario,total) 
 (select usuario, sum(consumo) as total from totales_10  where datediff(ahora, tiempo) <= ciclo group by usuario) 
 on duplicate key update total=values(total); 

select now(), "25 - insertar en usuarios total30 el consumo desde totales_full"; 
update usuarios set total30=0; 
insert into usuarios(usuario,total30) 
 (select usuario, sum(consumo) as total30 from totales_10 where datediff(ahora, tiempo) <= (ciclo+1)*4 group by usuario) 
 on duplicate key update total30=values(total30); 

select now(), "26 - actualizar consumo grande con el valor de los ultimos dias"; 
-- update usuarios set total30=total30+total; 
update usuarios set cuota2=cuota*5; 

-- select now(), "27 - pasando datos desde full_access_log para full_log_10   DISABLE";

-- INSERT INTO full_log_10 
--    (response_time,client_src_ip_addr, 
--  `squid_request_status`,`http_status_code`, 
--   `reply_size`,`request_method`,`request_url`, 
--   `username`,`squid_hier_status`, 
--   `server_ip_addr`,`mime_type`, 
--   `network_id`,`domain_id`,`request_date`) 
-- SELECT 
--   response_time,client_src_ip_addr,`squid_request_status`, 
--   `http_status_code`,`reply_size`,`request_method`,`request_url`, 
--   `username`,`squid_hier_status`, 
--   `server_ip_addr`,`mime_type`,`network_id`,`domain_id`,`request_date` 
--   FROM full_access_log; 


-- delete FROM full_log_10 WHERE datediff(now(), request_date) > (ciclo*4)+1; 

-- select now(), "28 - setting done 0"; 
-- SET done = 0; 

select now(), "29 - limpiando sesiones mayores de 2 horas";
delete from sess where now() - tiempo > 20000;


select now(), "29 - cantidad de pasados semanales", count(*) from usuarios where total > cuota ; 
select now(), "30 - cantidad de pasados mensuales", count(*) from usuarios where total30 > cuota2 ; 

SET @bytes := (SELECT SUM(consumo) FROM totales_10 where tadded > curdate()); 

SELECT now(), "31 - cantidad de bytes contados hoy",curdate(), 
   CASE 
WHEN ABS(@bytes) < 1024 THEN CONCAT( ROUND( @bytes, 3 ), ' Bytes') 
     WHEN ABS(@bytes) < 1048576 THEN CONCAT( ROUND( (@bytes/1024), 3 ), ' KB') 
     WHEN ABS(@bytes) < 1073741824 THEN CONCAT( ROUND( (@bytes/1048576), 3 ), ' MB') 
     WHEN ABS(@bytes) < 1099511627776 THEN CONCAT( ROUND( (@bytes/1073741824), 3 ), ' GB' ) 
     WHEN ABS(@bytes) < 1125899906842624 THEN CONCAT( ROUND( (@bytes/1099511627776), 3 ), ' TB') 
     WHEN ABS(@bytes) < 1152921504606846976 THEN CONCAT( ROUND( (@bytes/1125899906842624), 3 ), ' PB' ) 
     WHEN ABS(@bytes) < 1180591620717411303424 THEN CONCAT( ROUND( (@bytes/1152921504606846976) ,2), ' EB' ) 
     WHEN ABS(@bytes) < 1208925819614629174706176 THEN CONCAT( ROUND( (@bytes/1180591620717411303424), 3), ' ZB' ) 
     WHEN ABS(@bytes) < 1237940039285380274899124224 THEN CONCAT( ROUND( (@bytes/1208925819614629174706176), 3), ' YB' ) 
     WHEN ABS(@bytes) < 1267650600228229401496703205376 THEN CONCAT( ROUND( (@bytes/1237940039285380274899124224), 3), ' BB' ) 
   END 
   as totales; 
    


END$$

DROP PROCEDURE IF EXISTS `updateDomains`$$
CREATE DEFINER=`squid_log`@`%` PROCEDURE `updateDomains`()
BEGIN

 
        select * from domains where group_id <> getDomainNameId( getDomainName( domain ) );
 
        update domains set  group_id = getDomainNameId( getDomainName( domain ) );
 
END$$

DROP PROCEDURE IF EXISTS `update_domains_group_id`$$
CREATE DEFINER=`squid_log`@`%` PROCEDURE `update_domains_group_id`()
BEGIN

  
  
  insert ignore into domains(domain) select getDomainName(request_url) as domain from access_log_tmp;
  update domains set domains.group_id=domains.id where domains.group_id=0;
 
  
END$$

--
-- Functions
--
DROP FUNCTION IF EXISTS `getDomainName`$$
CREATE DEFINER=`squid_log`@`%` FUNCTION `getDomainName`(url char(255)) RETURNS char(255) CHARSET latin1
BEGIN
  DECLARE cadena char(255);
  DECLARE posicion int;
  DECLARE dominio_buscado char(255);
 
  SET cadena = url;
 
 
  SET posicion = INSTR( cadena, "://");
  IF (posicion > 0) THEN
    SET cadena = SUBSTRING(cadena, posicion + 3);
  END IF;
 
 
  SET posicion = INSTR(cadena, ":");
  IF (posicion > 0) THEN
    SET cadena = SUBSTRING(cadena, 1, posicion-1);
  END IF;
 
 
  SET cadena = TRIM(LEADING "www." FROM cadena );
 
 
  SET cadena = SUBSTRING_INDEX(cadena,"/",1);
  
  RETURN cadena;
END$$

DROP FUNCTION IF EXISTS `getDomainNameId`$$
CREATE DEFINER=`squid_log`@`%` FUNCTION `getDomainNameId`(`dominio` char(255)) RETURNS int(11)
BEGIN
      DECLARE RESULTADO int ;
 
       select group_id into RESULTADO from domains where domain=dominio;
 
 
       RETURN RESULTADO;
END$$

DROP FUNCTION IF EXISTS `getDomainNameTest`$$
CREATE DEFINER=`squid_log`@`%` FUNCTION `getDomainNameTest`(url char(255)) RETURNS char(255) CHARSET latin1
BEGIN
  DECLARE cadena char(255);
  DECLARE posicion int;
  DECLARE dominio_buscado char(255);
 
  SET cadena = url;
 
  SET posicion = INSTR( cadena, "://");
  IF (posicion > 0) THEN
    SET cadena = SUBSTRING(cadena, posicion + 3);
  END IF;
 
 
  SET posicion = INSTR(cadena, ":");
  IF (posicion > 0) THEN
    SET cadena = SUBSTRING(cadena, 1, posicion-1);
  END IF;
 
 
  SET cadena = TRIM(LEADING "www." FROM cadena );
 
 
  SET cadena = SUBSTRING_INDEX(cadena,"/",1);
 
 
  SET dominio_buscado = "";
 
  SELECT domain_bases.domain INTO dominio_buscado
      FROM domain_bases
      WHERE INSTR(cadena,domain_bases.domain_base) > 0;
 
 
  IF (LENGTH(dominio_buscado)>0) THEN
    SET cadena = dominio_buscado;
  END IF;
 
 
RETURN cadena;
END$$

DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `access_log`
--
-- Creation: Oct 06, 2017 at 12:02 AM
--

DROP TABLE IF EXISTS `access_log`;
CREATE TABLE IF NOT EXISTS `access_log` (
  `time_since_epoch` decimal(15,3) DEFAULT NULL,
  `response_time` int(11) DEFAULT NULL,
  `client_src_ip_addr` char(15) DEFAULT NULL,
  `squid_request_status` varchar(40) DEFAULT NULL,
  `http_status_code` varchar(10) DEFAULT NULL,
  `reply_size` int(11) DEFAULT NULL,
  `request_method` varchar(20) DEFAULT NULL,
  `request_url` varchar(1000) DEFAULT NULL,
  `username` varchar(20) DEFAULT NULL,
  `squid_hier_status` varchar(20) DEFAULT NULL,
  `server_ip_addr` char(15) DEFAULT NULL,
  `mime_type` varchar(50) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `admins`
--
-- Creation: May 02, 2017 at 10:43 AM
-- Last update: May 02, 2017 at 10:43 AM
--

DROP TABLE IF EXISTS `admins`;
CREATE TABLE IF NOT EXISTS `admins` (
  `usuario` varchar(32) NOT NULL,
  `cuota` int(12) NOT NULL DEFAULT '450000000',
  `total` bigint(20) NOT NULL DEFAULT '0',
  `ldap` varchar(256) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `arp_actual`
--
-- Creation: May 02, 2017 at 10:43 AM
-- Last update: May 02, 2017 at 10:43 AM
--

DROP TABLE IF EXISTS `arp_actual`;
CREATE TABLE IF NOT EXISTS `arp_actual` (
  `mac` char(17) NOT NULL,
  `ip` char(17) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `computadoras`
--
-- Creation: May 02, 2017 at 10:43 AM
--

DROP TABLE IF EXISTS `computadoras`;
CREATE TABLE IF NOT EXISTS `computadoras` (
  `ip` char(15) NOT NULL,
  `fac` text NOT NULL,
  `dpto` text NOT NULL,
  `responsable` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `domains`
--
-- Creation: May 02, 2017 at 10:43 AM
--

DROP TABLE IF EXISTS `domains`;
CREATE TABLE IF NOT EXISTS `domains` (
`id` int(11) NOT NULL,
  `domain` char(255) NOT NULL,
  `info` text,
  `group_id` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB AUTO_INCREMENT=2365147 DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `domain_bases`
--
-- Creation: May 02, 2017 at 10:43 AM
--

DROP TABLE IF EXISTS `domain_bases`;
CREATE TABLE IF NOT EXISTS `domain_bases` (
  `domain_base` char(255) NOT NULL,
  `domain` char(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `full_access_log`
--
-- Creation: Oct 07, 2017 at 09:14 PM
--

DROP TABLE IF EXISTS `full_access_log`;
CREATE TABLE IF NOT EXISTS `full_access_log` (
  `time_since_epoch` decimal(15,3) DEFAULT NULL,
  `response_time` int(11) DEFAULT NULL,
  `client_src_ip_addr` char(15) DEFAULT NULL,
  `squid_request_status` varchar(40) DEFAULT NULL,
  `http_status_code` varchar(10) DEFAULT NULL,
  `reply_size` int(11) DEFAULT NULL,
  `request_method` varchar(20) DEFAULT NULL,
  `request_url` varchar(1000) DEFAULT NULL,
  `username` varchar(20) DEFAULT NULL,
  `squid_hier_status` varchar(20) DEFAULT NULL,
  `server_ip_addr` char(15) DEFAULT NULL,
  `mime_type` varchar(50) DEFAULT NULL,
  `network_id` int(11) DEFAULT NULL,
  `domain_id` int(11) DEFAULT '0',
  `domain_name` varchar(255) DEFAULT NULL,
  `request_date` datetime NOT NULL,
  `network_name` char(15) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `full_log_10`
--
-- Creation: May 02, 2017 at 10:43 AM
--

DROP TABLE IF EXISTS `full_log_10`;
CREATE TABLE IF NOT EXISTS `full_log_10` (
  `response_time` int(11) DEFAULT NULL,
  `client_src_ip_addr` char(15) DEFAULT NULL,
  `squid_request_status` varchar(40) DEFAULT NULL,
  `http_status_code` varchar(10) DEFAULT NULL,
  `reply_size` int(11) DEFAULT NULL,
  `request_method` varchar(20) DEFAULT NULL,
  `request_url` varchar(1000) DEFAULT NULL,
  `username` varchar(20) DEFAULT NULL,
  `squid_hier_status` varchar(20) DEFAULT NULL,
  `server_ip_addr` char(15) DEFAULT NULL,
  `mime_type` varchar(50) DEFAULT NULL,
  `network_id` int(11) DEFAULT NULL,
  `domain_id` int(11) DEFAULT '0',
  `request_date` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `full_log_mes`
--
-- Creation: May 02, 2017 at 10:43 AM
--

DROP TABLE IF EXISTS `full_log_mes`;
CREATE TABLE IF NOT EXISTS `full_log_mes` (
  `suma` int(11) DEFAULT NULL,
  `domain_id` int(11) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `mac`
--
-- Creation: May 02, 2017 at 10:43 AM
-- Last update: May 02, 2017 at 10:43 AM
--

DROP TABLE IF EXISTS `mac`;
CREATE TABLE IF NOT EXISTS `mac` (
  `mac` varchar(17) NOT NULL,
  `ip` varchar(15) NOT NULL,
  `descrption` varchar(128) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `networks`
--
-- Creation: May 02, 2017 at 10:43 AM
--

DROP TABLE IF EXISTS `networks`;
CREATE TABLE IF NOT EXISTS `networks` (
`id` int(11) NOT NULL,
  `base` char(11) NOT NULL,
  `info` text
) ENGINE=InnoDB AUTO_INCREMENT=322028 DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `query_cache`
--
-- Creation: May 02, 2017 at 10:43 AM
--

DROP TABLE IF EXISTS `query_cache`;
CREATE TABLE IF NOT EXISTS `query_cache` (
  `query` char(250) NOT NULL,
  `resultado` longtext NOT NULL,
  `ttl` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `sess`
--
-- Creation: May 02, 2017 at 10:43 AM
-- Last update: Oct 07, 2017 at 09:14 PM
-- Last check: Sep 23, 2017 at 11:09 AM
--

DROP TABLE IF EXISTS `sess`;
CREATE TABLE IF NOT EXISTS `sess` (
  `dir_ip` varchar(15) NOT NULL,
  `usuario` varchar(32) NOT NULL,
  `tiempo` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `totales_10`
--
-- Creation: May 02, 2017 at 10:43 AM
--

DROP TABLE IF EXISTS `totales_10`;
CREATE TABLE IF NOT EXISTS `totales_10` (
  `usuario` varchar(32) NOT NULL,
  `tiempo` date NOT NULL,
  `consumo` int(15) NOT NULL,
  `tadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `totales_full`
--
-- Creation: May 02, 2017 at 10:43 AM
--

DROP TABLE IF EXISTS `totales_full`;
CREATE TABLE IF NOT EXISTS `totales_full` (
  `usuario` varchar(32) NOT NULL,
  `tiempo` date NOT NULL,
  `consumo` int(15) NOT NULL,
  `tadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `usuarios`
--
-- Creation: Oct 07, 2017 at 09:04 PM
-- Last update: Oct 07, 2017 at 09:14 PM
--

DROP TABLE IF EXISTS `usuarios`;
CREATE TABLE IF NOT EXISTS `usuarios` (
  `usuario` varchar(32) NOT NULL,
  `cuota` int(20) NOT NULL DEFAULT '150000000',
  `total` bigint(20) NOT NULL DEFAULT '0',
  `ldap` varchar(256) NOT NULL,
  `actualizado` datetime NOT NULL,
  `total30` bigint(20) NOT NULL DEFAULT '0',
  `cuota2` int(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `admins`
--
ALTER TABLE `admins`
 ADD PRIMARY KEY (`usuario`);

--
-- Indexes for table `arp_actual`
--
ALTER TABLE `arp_actual`
 ADD PRIMARY KEY (`ip`);

--
-- Indexes for table `computadoras`
--
ALTER TABLE `computadoras`
 ADD PRIMARY KEY (`ip`);

--
-- Indexes for table `domains`
--
ALTER TABLE `domains`
 ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `domain` (`domain`);

--
-- Indexes for table `domain_bases`
--
ALTER TABLE `domain_bases`
 ADD PRIMARY KEY (`domain_base`);

--
-- Indexes for table `mac`
--
ALTER TABLE `mac`
 ADD PRIMARY KEY (`ip`);

--
-- Indexes for table `networks`
--
ALTER TABLE `networks`
 ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `base` (`base`);

--
-- Indexes for table `query_cache`
--
ALTER TABLE `query_cache`
 ADD PRIMARY KEY (`query`);

--
-- Indexes for table `sess`
--
ALTER TABLE `sess`
 ADD PRIMARY KEY (`dir_ip`,`usuario`);

--
-- Indexes for table `usuarios`
--
ALTER TABLE `usuarios`
 ADD PRIMARY KEY (`usuario`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `domains`
--
ALTER TABLE `domains`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=2365147;
--
-- AUTO_INCREMENT for table `networks`
--
ALTER TABLE `networks`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=322028;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Permisos para el usuario que inserta los logs

GRANT SELECT, INSERT ON `squid_log_db`.`access_log` 
  TO 'squid_process'@'10.12.1.77'
  IDENTIFIED BY PASSWORD 'XXXXX';

Permisos para el usuarios que ejecuta el procedimiento almacenado

GRANT ALL PRIVILEGES ON `squid_log_db`.* 
  TO 'squid_log'@'%' 
  IDENTIFIED BY PASSWORD 'XXXXX';

Forma de ejecucion del procedimiento

Hay varias formas para lanzar el procedimiento almacenado. Lo mas importante es que no se pueden solapar dos procesos.

Una variante muy sencilla es usando el screen dentro de un –/etc/rc.local–

screen -d -m /root/squid-db-updater.sh

El codigo de este archivo puede ser algo tan sencillo como:

#!/bin/bash

while sleep 5
do
 date
 echo "call squid_log_main " | mysql -h 127.0.0.1 -uUSER -pPASSWORD -s BASE_DB
 echo "----------WATTINGGGGG------------"
done

Otra variante puede ser programar el evento dentro del propio MySql

DROP EVENT IF EXISTS clean_squid_log;
DELIMITER |
CREATE EVENT clean_squid_log ON SCHEDULE EVERY 1 MINUTE DO
BEGIN
SELECT GET_LOCK('temp.squid_log_main', 0) INTO @got_lock;

IF @got_lock = 1 THEN
CALL squid_log_main();

SELECT RELEASE_LOCK('temp.squid_log_main');
END IF;
END|
DELIMITER ;

Más información en: https://www.swapbytes.com/category/mysql/page/2/

ICT Network Project

User Tools

Site Tools

Requerimientos

Configuracion en el server de MySQL

Dump base de datos

Forma de ejecucion del procedimiento

Page Tools