magento数据库日志的清除及优化

magento本身会记录很多日志，
这些日志长年累积会占用非常大的空间。
下面是一些日志表

'dataflow_batch_export',
'dataflow_batch_import',
'log_customer',
'log_quote',
'log_summary',
'log_summary_type',
'log_url',
'log_url_info',
'log_visitor',
'log_visitor_info',
'log_visitor_online',
'report_event',
'report_compared_product_index',
'report_viewed_product_index',

上面这些日志表基本上会占用一半的数据空间

而很多时间这些表数据根本不会用到，
所以定时清理很有必要

清除方法

一：后台清除

1：登入后进入 System > Configuration.
2：左侧菜单 Advanced 下点击 System.
3：在”Log Cleaning”下, 改变”Enable Log Cleaning” 为 YES 并且配置Save Log 为15 days:

二：可以通过Magento自身的cron定时清理日志。
三：执行SQL清理，

truncate dataflow_batch_export;
truncate dataflow_batch_import;
truncate log_customer;
truncate log_quote;
truncate log_summary;
truncate log_summary_type;
truncate log_url;
truncate log_url_info;
truncate log_visitor;
truncate log_visitor_info;
truncate log_visitor_online;
truncate report_viewed_product_index;
truncate report_compared_product_index;
truncate report_event;
set foreign_key_checks = 0;
truncate index_process_event;
truncate index_event;
set foreign_key_checks = 1;

数据库清理数据有风险，请注意备份好数据库

四：代码清除：

/**

 * Magento Maintenance Script

 *

 * @version    3.0.0

 * @author     Crucial Web Hosting <sales@crucialwebhost.com>

 * @copyright  Copyright (c) 2006-2013 Crucial Web Hosting, Ltd.

 * @link       http://www.crucialwebhost.com  Crucial Web Hosting

 */



switch($_GET['clean']) {

    case 'log':

        clean_log_tables();

    break;

    case 'var':

        clean_var_directory();

    break;

}



function clean_log_tables() {

    $xml = simplexml_load_file('./app/etc/local.xml', NULL, LIBXML_NOCDATA);



    if(is_object($xml)) {

        $db['host'] = $xml->global->resources->default_setup->connection->host;

        $db['name'] = $xml->global->resources->default_setup->connection->dbname;

        $db['user'] = $xml->global->resources->default_setup->connection->username;

        $db['pass'] = $xml->global->resources->default_setup->connection->password;

        $db['pref'] = $xml->global->resources->db->table_prefix;



        $tables = array(

            'adminnotification_inbox',

            'aw_core_logger',

            'dataflow_batch_export',

            'dataflow_batch_import',

            'log_customer',

            'log_quote',

            'log_summary',

            'log_summary_type',

            'log_url',

            'log_url_info',

            'log_visitor',

            'log_visitor_info',

            'log_visitor_online',

            'index_event',

            'report_event',

            'report_viewed_product_index',

            'report_compared_product_index',

            'catalog_compare_item',

            'catalogindex_aggregation',

            'catalogindex_aggregation_tag',

            'catalogindex_aggregation_to_tag'

        );



        mysql_connect($db['host'], $db['user'], $db['pass']) or die(mysql_error());

        mysql_select_db($db['name']) or die(mysql_error());



        foreach($tables as $table) {

            @mysql_query('TRUNCATE `'.$db['pref'].$table.'`');

        }

    } else {

        exit('Unable to load local.xml file');

    }

}



function clean_var_directory() {

    $dirs = array(

        'downloader/.cache/',

        'downloader/pearlib/cache/*',

        'downloader/pearlib/download/*',

        'var/cache/',

        'var/locks/',

        'var/log/',

        'var/report/',

        'var/session/',

        'var/tmp/'

    );



    foreach($dirs as $dir) {

        exec('rm -rf '.$dir);

    }

}