function _table_hours_perform($tablename) { if (!isset($GLOBALS["Q"])) { $GLOBALS["Q"] = new mysql_squid_builder(); } if (!preg_match("#ngixattck_([0-9]+)#", $tablename, $re)) { writelogs_squid("NOT AN HOUR TABLE `{$tablename}`", __FUNCTION__, __FILE__, __LINE__, "stats"); return; } $q = new mysql_squid_builder(); $time = $q->TIME_FROM_HOUR_TEMP_TABLE($tablename); if ($GLOBALS["VERBOSE"]) { echo "{$tablename} - {$time} - " . date("Y-m-d", $time) . "\n"; } $sql = "SELECT HOUR(zDate) as `hour`,COUNT(keyr) as hits,`ipaddr`,`familysite`,`hostname`,`country`,`servername`\n\tFROM `{$tablename}` GROUP BY `hour`,`ipaddr`,`familysite`,`hostname`,`country`,`servername`"; if ($GLOBALS["VERBOSE"]) { echo $sql . "\n"; } $results = $GLOBALS["Q"]->QUERY_SQL($sql); if (!$GLOBALS["Q"]->ok) { writelogs_squid("Fatal: {$GLOBALS["Q"]->mysql_error} on `{$tablename}`\n" . @implode("\n", $GLOBALS["REPAIR_MYSQL_TABLE"]), __FUNCTION__, __FILE__, __LINE__, "stats"); if (strpos(" {$GLOBALS["Q"]->mysql_error}", "is marked as crashed and should be repaired") > 0) { $q1 = new mysql(); writelogs_squid("try to repair table `{$tablename}`", __FUNCTION__, __FILE__, __LINE__, "stats"); $q1->REPAIR_TABLE("squidlogs", $tablename); writelogs_squid(@implode("\n", $GLOBALS["REPAIR_MYSQL_TABLE"]), __FUNCTION__, __FILE__, __LINE__, "stats"); } return false; } if (mysql_num_rows($results) == 0) { return true; } $timekey = date('Ymd', $time); $tabledest = "ngixattckd_{$timekey}"; if (!$q->check_nginx_attacks_DAY($timekey)) { return false; } $prefix = "INSERT IGNORE INTO {$tabledest} (zmd5,`hour`,`ipaddr`,`familysite`,`hostname`,`country`,`servername`,`hits`) VALUES "; $d = 0; while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) { $zmd = array(); while (list($key, $value) = each($ligne)) { $ligne[$key] = mysql_escape_string2($value); $zmd[] = $value; } $zMD5 = md5(@implode("", $zmd)); $f[] = "('{$zMD5}','{$ligne["hour"]}','{$ligne["ipaddr"]}','{$ligne["familysite"]}','{$ligne["hostname"]}','{$ligne["country"]}','{$ligne["servername"]}','{$ligne["hits"]}')"; if (count($f) > 500) { $q->QUERY_SQL($prefix . @implode(",", $f)); $f = array(); if (!$q->ok) { writelogs_squid("Fatal: {$q->mysql_error} on `{$tabledest}`", __FUNCTION__, __FILE__, __LINE__, "stats"); return; } } } if (count($f) > 0) { $q->QUERY_SQL($prefix . @implode(",", $f)); $f = array(); if (!$q->ok) { writelogs_squid("Fatal: {$q->mysql_error} on `{$tabledest}`", __FUNCTION__, __FILE__, __LINE__, "stats"); return; } } return true; }
function _repair_database($database) { $q = new mysql(); $sql = "SHOW TABLES"; $results = $q->QUERY_SQL($sql, "squidlogs"); $unix = new unix(); $MYSQL_DATA_DIR = $unix->MYSQL_DATA_DIR(); $time1 = time(); $myisamchk = $unix->find_program("myisamchk"); $mysqlcheck = $unix->find_program("mysqlcheck"); $myisamchk = $unix->find_program("myisamchk"); $pgrep = $unix->find_program("pgrep"); exec("{$pgrep} -l -f \"{$myisamchk}\"", $results); while (list($index, $line) = each($results)) { if (preg_match("#pgrep#", $line)) { continue; } if (preg_match("#^[0-9]+\\s+#", $line)) { writelogs("{$line} already executed", @implode("\r\n", $results), __FUNCTION__, __FILE__, __LINE__); return; } } $mysqlcheck_logs = null; $sql = "SHOW TABLES"; $results = $q->QUERY_SQL($sql, $database); if (!$q->ok) { system_admin_events("Maintenance on database {$database} failed {$q->mysql_error}", __FUNCTION__, __FILE__, __LINE__, "mysql-error"); return; } if (mysql_num_rows($results) == 0) { system_admin_events("Maintenance on database {$database} aborting, no table stored", __FUNCTION__, __FILE__, __LINE__, "mysql-error"); return; } $user = $q->mysql_admin; $ty[] = " --user={$user}"; if ($q->mysql_password != null) { $ty[] = "--password={$q->mysql_password}"; } $BLACKS["events_waits_current"] = true; $BLACKS["events_waits_history"] = true; $BLACKS["events_waits_history_long"] = true; $BLACKS["cond_instances"] = true; $BLACKS["events_waits_summary_by_instance"] = true; $BLACKS["events_waits_summary_by_thread_by_event_name"] = true; $BLACKS["events_waits_summary_global_by_event_name"] = true; $BLACKS["file_instances"] = true; $BLACKS["file_summary_by_event_name"] = true; $BLACKS["file_summary_by_instance"] = true; $BLACKS["mutex_instances"] = true; $BLACKS["performance_timers"] = true; $BLACKS["rwlock_instances"] = true; $BLACKS["setup_consumers"] = true; $BLACKS["setup_instruments"] = true; $BLACKS["setup_timers"] = true; $BLACKS["threads"] = true; $BLACKS["schema"] = true; $credentials = @implode(" ", $ty); while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) { $table = $ligne["Tables_in_{$database}"]; if (isset($BLACKS[$table])) { continue; } $tt = time(); if (is_file($mysqlcheck)) { exec("{$mysqlcheck}{$credentials} -r {$database} {$table} 2>&1", $mysqlcheck_array); $mysqlcheck_logs = $mysqlcheck_logs . "\n{$mysqlcheck} on {$table}:\n" . @implode("\n", $mysqlcheck_array); unset($mysqlcheck_array); } echo $table . "\n"; if (is_file($myisamchk)) { shell_exec("{$myisamchk} --safe-recover --force {$MYSQL_DATA_DIR}/{$database}/{$table}"); } else { $q->REPAIR_TABLE($database, $table); } $q->QUERY_SQL("OPTIMIZE table {$table}", "{$database}"); $time_duration = distanceOfTimeInWords($tt, time()); $p[] = "{$database}/{$table} {$time_duration}"; } $t2 = time(); $time_duration = distanceOfTimeInWords($time1, $t2); system_admin_events("Maintenance on database {$database} done: took {$time_duration}\nOperations has be proceed on \n" . @implode("\n", $p) . "\nmysqlchecks results:\n{$mysqlcheck_logs}", __FUNCTION__, __FILE__, __LINE__, "mysql"); }
function maintenance() { return null; $unix = new unix(); $time = $unix->file_time_min("/etc/artica-postfix/mysql.optimize.time"); $time1 = time(); $myisamchk = $unix->find_program("myisamchk"); $mysqlcheck = $unix->find_program("mysqlcheck"); if (!$GLOBALS["VERBOSE"]) { if ($time < 1440) { $unix->events(__FILE__ . "::" . __FUNCTION__ . " {$time}Mn wait 1440Mn, aborting"); return; } } $mysqlcheck_logs = ""; @unlink("/etc/artica-postfix/mysql.optimize.time"); @file_put_contents("/etc/artica-postfix/mysql.optimize.time", "#"); if (is_file($mysqlcheck)) { exec("{$mysqlcheck} -A -1 2>&1", $mysqlcheck_array); $mysqlcheck_logs = $mysqlcheck_logs . "\n" . @implode("\n", $mysqlcheck_array); unset($mysqlcheck_array); } $q = new mysql(); $sql = "SHOW TABLES"; $results = $q->QUERY_SQL($sql, "artica_backup"); while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) { $table = $ligne["Tables_in_artica_backup"]; $tt = time(); if (is_file($mysqlcheck)) { exec("{$mysqlcheck} -r artica_backup {$table} 2>&1", $mysqlcheck_array); $mysqlcheck_logs = $mysqlcheck_logs . "\n" . @implode("\n", $mysqlcheck_array); unset($mysqlcheck_array); } echo $table . "\n"; if (is_file($myisamchk)) { shell_exec("{$myisamchk} -r --safe-recover --force /var/lib/mysql/artica_backup/{$table}"); } else { $q->REPAIR_TABLE("artica_backup", $table); } $q->QUERY_SQL("OPTIMIZE table {$table}", "artica_backup"); $time_duration = distanceOfTimeInWords($tt, time()); $p[] = "artica_backup/{$table} {$time_duration}"; } $sql = "SHOW TABLES"; $results = $q->QUERY_SQL($sql, "artica_events"); while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) { $table = $ligne["Tables_in_artica_events"]; $tt = time(); echo "Repair & optimize {$table}\n"; if (is_file($mysqlcheck)) { exec("{$mysqlcheck} -r artica_events {$table} 2>&1", $mysqlcheck_array); $mysqlcheck_logs = $mysqlcheck_logs . "\n" . @implode("\n", $mysqlcheck_array); unset($mysqlcheck_array); } if (is_file($myisamchk)) { shell_exec("{$myisamchk} -r --safe-recover --force /var/lib/mysql/artica_events/{$table}"); } else { $q->REPAIR_TABLE("artica_events", $table); } $q->QUERY_SQL("OPTIMIZE table {$table}", "artica_events"); $time_duration = distanceOfTimeInWords($tt, time()); $p[] = "artica_events/{$table} {$time_duration}"; } $t2 = time(); $time_duration = distanceOfTimeInWords($time1, $t2); send_email_events("Maintenance on databases artica_backup & artica_events done {$time_duration}", "Operations has be proceed on \n" . @implode("\n", $p) . "\nmysqlchecks results:\n{$mysqlcheck_logs}", "system"); }
function _table_hours_perform($tablename) { if (!isset($GLOBALS["Q"])) { $GLOBALS["Q"] = new mysql_squid_builder(); } if (!preg_match("#squidhour_([0-9]+)#", $tablename, $re)) { writelogs_squid("NOT AN HOUR TABLE `{$tablename}`", __FUNCTION__, __FILE__, __LINE__, "stats"); return; } $hour = $re[1]; $year = substr($hour, 0, 4); $month = substr($hour, 4, 2); $day = substr($hour, 6, 2); $compressed = false; $f = array(); $dansguardian_table = "dansguardian_events_{$year}{$month}{$day}"; $accounts = $GLOBALS["Q"]->ACCOUNTS_ISP(); if (!$GLOBALS["Q"]->Check_dansguardian_events_table($dansguardian_table)) { return false; } if (!$GLOBALS["Q"]->FIELD_EXISTS("{$tablename}", "category")) { $GLOBALS["Q"]->QUERY_SQL("ALTER TABLE `{$tablename}` ADD `category` VARCHAR( 60 ) NOT NULL ,ADD INDEX ( `category` )"); } $sql = "SELECT COUNT(ID) as hits,SUM(QuerySize) as QuerySize,DATE_FORMAT(zDate,'%Y-%m-%d %H:00:00') as zDate,sitename,uri,TYPE,REASON,CLIENT,uid,remote_ip,country,cached,MAC,hostname,category FROM {$tablename} GROUP BY sitename,uri,TYPE,REASON,CLIENT,uid,remote_ip,country,cached,MAC,zDate,hostname,category"; if ($GLOBALS["VERBOSE"]) { echo $sql . "\n"; } $results = $GLOBALS["Q"]->QUERY_SQL($sql); if (!$GLOBALS["Q"]->ok) { stats_admin_events(1, "Fatal: {$GLOBALS["Q"]->mysql_error} on `{$tablename}`", @implode("\n", $GLOBALS["REPAIR_MYSQL_TABLE"]), __FILE__, __LINE__, "stats"); if (strpos(" {$GLOBALS["Q"]->mysql_error}", "is marked as crashed and should be repaired") > 0) { $q1 = new mysql(); $q1->REPAIR_TABLE("squidlogs", $tablename); stats_admin_events(2, "try to repair table `{$tablename}`", $GLOBALS["REPAIR_MYSQL_TABLE"], __FILE__, __LINE__, "stats"); } return false; } $prefix = "INSERT IGNORE INTO {$dansguardian_table} (sitename,uri,TYPE,REASON,CLIENT,MAC,zDate,zMD5,uid,remote_ip,country,QuerySize,hits,cached,hostname,account,category) VALUES "; $ip = new IP(); $SUM = mysql_num_rows($results); $d = 0; while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) { $zmd = array(); while (list($key, $value) = each($ligne)) { $ligne[$key] = mysql_escape_string2($value); $zmd[] = $value; } $zMD5 = md5(@implode("", $zmd)); $accountclient = null; if (isset($accounts[$ligne["CLIENT"]])) { $accountclient = $accounts[$ligne["CLIENT"]]; } $uid = $ligne["uid"]; if ($uid == null) { $uid = $GLOBALS["Q"]->MacToUid($ligne["MAC"]); if (is_numeric($uid)) { $uid = null; } } if ($uid == null) { $uid = $GLOBALS["Q"]->IpToUid($ligne["CLIENT"]); if (is_numeric($uid)) { $uid = null; } } $uid = mysql_escape_string2($uid); $hostname = $ligne["hostname"]; if ($hostname == null) { $hostname = $GLOBALS["Q"]->MacToHost($ligne["MAC"]); if (is_numeric($uid)) { $uid = null; } } if ($hostname == null) { $hostname = $GLOBALS["Q"]->IpToHost($ligne["CLIENT"]); if (is_numeric($uid)) { $uid = null; } } if ($ip->isValid($hostname)) { $hostname = null; } $hostname = mysql_escape_string2($hostname); $d++; $f[] = "('{$ligne["sitename"]}','{$ligne["uri"]}','{$ligne["TYPE"]}','{$ligne["REASON"]}','{$ligne["CLIENT"]}','{$ligne["MAC"]}','{$ligne["zDate"]}','{$zMD5}','{$uid}','{$ligne["remote_ip"]}','{$ligne["country"]}','{$ligne["QuerySize"]}','{$ligne["hits"]}','{$ligne["cached"]}','{$hostname}','{$accountclient}','{$ligne["category"]}')"; if (count($f) > 500) { ToSyslog("{$dansguardian_table}: {$d}/{$SUM}"); $GLOBALS["Q"]->UncompressTable($dansguardian_table); $GLOBALS["Q"]->QUERY_SQL($prefix . @implode(",", $f)); $f = array(); if (!$GLOBALS["Q"]->ok) { stats_admin_events(1, "MySQL Error ", "{$GLOBALS["Q"]->mysql_error} on `{$dansguardian_table}`", __FILE__, __LINE__, "stats"); return; } } } if (count($f) > 0) { $GLOBALS["Q"]->UncompressTable($dansguardian_table); $GLOBALS["Q"]->QUERY_SQL($prefix . @implode(",", $f)); if (!$GLOBALS["Q"]->ok) { stats_admin_events(1, "MySQL Error ", "{$GLOBALS["Q"]->mysql_error} on `{$dansguardian_table}`", __FILE__, __LINE__, "stats"); return; } $squid_stats_tools = new squid_stats_tools(); $squid_stats_tools->NoCategorize = true; $squid_stats_tools->check_table_days(); } stats_admin_events(2, "Success updating {$dansguardian_table} with {$d} events", __FILE__, __LINE__); return true; }