How to Tweak the Production Database for Short Term Magento Projects
Published on 07 May 15
Silvertouch0
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
• 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();
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:
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.
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
