How to Tweak the Production Database for Short Term Magento Projects

There will be times when you have to deal with short term projects. These projects don’t utilize the database extensively, and don’t need all the elements present within a database. Before you learn on how to tweak the database, let’s try and understand what happens when a project is handed over to you. You will need to perform these four steps compulsorily 

• Transfer the source code for the source from the production environment into the local environment
• Download the media files (if any) and share it
• Create a database dump and share it
• Generate the code repository and an issue tracking system

You will see that, when dumping the database, there are a lot of elements that are not required for short projects. How can you avoid dumping them? How will you know which ones are not useful to your project? You will need to consult the so-called ignore list that comes with the database to know which ones need to be ignored

•  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_compared_product_index
•  report_viewed_product_index
•  catalog_compare_item
•  catalogindex_aggregation
•  catalogindex_aggregation_tag
•  catalogindex_aggregation_to_tag

With this list in hand, you can easily figure out the functions that you wish to ignore. Here’s a code that will help you create the dump database for smaller projects i.e. this will teach you in detail how to tweak a database

define('DS', DIRECTORY_SEPARATOR);

function _getExtractSchemaStatement($sqlFileName, $db)
{
    $dumpSchema = 'mysqldump' . ' ';
    $dumpSchema .= '--no-data' . ' ';
    $dumpSchema .= '-u ' . $db['user'] . ' ';
    $dumpSchema .= '-p' . $db['pass'] . ' ';
    $dumpSchema .= '-h ' . $db['host'] . ' ';
    $dumpSchema .= $db['name'] .' > ' . $sqlFileName;

    return $dumpSchema;
}

function _getExtractDataStatement($sqlFileName, $db)
{
    $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'
    );

    $ignoreTables = ' ';
    foreach($tables as $table) {
        $ignoreTables .= '--ignore-table=' . $db['name'] . '.' . $db['pref'] . $table . ' ';
    }

    $dumpData = 'mysqldump' . ' ';
    $dumpData .= $ignoreTables;
    $dumpData .=  '-u ' . $db['user'] . ' ';
    $dumpData .= '-p' . $db['pass'] . ' ';
    $dumpData .= '-h ' . $db['host'] . ' ';
    $dumpData .= $db['name'] .' >> ' . $sqlFileName;

    return $dumpData;
}

function export_tiny()
{
    $configPath = '.' . DS . 'app' . DS . 'etc' . DS . 'local.xml';
    $xml = simplexml_load_file($configPath, NULL, LIBXML_NOCDATA);

    $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;

    $sqlFileName =  'var' . DS . $db['name'] . '-' . date('j-m-y-h-i-s') . '.sql';

    //Extract the DB schema
    $dumpSchema = _getExtractSchemaStatement($sqlFileName, $db);
    exec($dumpSchema);

    //Extract the DB data
    $dumpData = _getExtractDataStatement($sqlFileName, $db);
    exec($dumpData);
}

export_tiny();

With this script added, you will get a dump of the original database minus the elements marked in ignore list.

Using the Script

Now, let’s learn how to use the script to generate the dump database for short projects. Here’s a step by step method for the same

First you will need to copy the script to the Magento root directory. In case, you have been given the SSH access, you will need to execute the path php tinydump.php. In case, you don’t have SSH access then you can run this script using the following path: http://mymagentostore.comtinydump.php. Once the script has performed its task, you will find an sql file which appears as follows:

{DB NAME}-{date(‘j-m-y-h-i-s’)}.sql

With this, the database file size is tweaked, and your download time is reduced

In case, your host restricts the functions, system() and exec() php which have been used in this code, then you will need to use the following method to tweak the database. To export the database, you will need to perform the following steps from the phpmyadmin path

Export Structure of Database

Select the following options

•  Tables (select all)
•  Disable foreign key checks
•  Dump table

Export the Data

To export the data from the database, you will need to select the following options

•  Tables (all except ignore list)
•  Disable foreign key checks
•  Dump table

With this, you will get two files in the output- structure of the database and the data from within the database. To ensure local installation, you need to install the structure and then the data of the database.


Conclusion

Sometimes, you don’t need the whole database, especially when working with small projects. There will be many functions and elements within a database that you can safely ignore. Try tweaking your database structure before downloading the data.

Author Bio:

Deepa is an online writer who writes for magento blogs. She recommends checking out Magento Development Company UK at https://www.silvertouchtech.co.uk in case if you are looking for high quality and reliable Magento Development Services at an affordable price.