MyPage is a personalized page based on your interests.The page is customized to help you to find content that matters you the most.


I'm not curious

How to Tweak the Production Database for Short Term Magento Projects

Published on 07 May 15
495
0
2
How to Tweak the Production Database for Short Term Magento Projects - Image 1
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.




















































































































































How to Tweak the Production Database for Short Term Magento Projects - Image 1

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.

This blog is listed under Development & Implementations and E-Commerce Community

Related Posts:
Post a Comment

Please notify me the replies via email.

Important:
  • We hope the conversations that take place on MyTechLogy.com will be constructive and thought-provoking.
  • To ensure the quality of the discussion, our moderators may review/edit the comments for clarity and relevance.
  • Comments that are promotional, mean-spirited, or off-topic may be deleted per the moderators' judgment.
You may also be interested in
Awards & Accolades for MyTechLogy
Winner of
REDHERRING
Top 100 Asia
Finalist at SiTF Awards 2014 under the category Best Social & Community Product
Finalist at HR Vendor of the Year 2015 Awards under the category Best Learning Management System
Finalist at HR Vendor of the Year 2015 Awards under the category Best Talent Management Software
Hidden Image Url

Back to Top