Differences

This shows you the differences between two versions of the page.

--- quota [2018/05/16 18:37]
moliver@uclv
+++ quota [2020/04/10 17:38]
@@ Line 1: / Line 1: @@
-=== 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 ===
-<code>
--- 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 */;
-</code>
-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 '*0FA27A617EFEAA7FB28C1829C581BC7902B306A2';
-Permisos para el usuarios que ejecuta el procedimiento almacenado
-  GRANT ALL PRIVILEGES ON `squid_log_db`.*
-    TO 'squid_log'@'%'
-    IDENTIFIED BY PASSWORD '*3619196BECCB2DE880F591CC8D552269E30DE687';
-=== 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--
-<code>
-screen -d -m /root/squid-db-updater.sh
-</code>
-El codigo de este archivo puede ser algo tan sencillo como:
-<code>
-#!/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
-</code>
-Otra variante puede ser programar el evento dentro del propio MySql
-<code>
-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 ;
-</code>
-Más información en: https://www.swapbytes.com/category/mysql/page/2/

ICT Network Project

User Tools

Site Tools

Differences

Page Tools