Frustrated by Magento? Then you’ll love
This article is part of a longer series covering the n98-magerun power tool
The database: That which, as Magento programmers, we’re not supposed to touch. Use the native objects is a refrain I’ve sung on more than one occasion, and it’s still what I recommend to people starting out with Magento.
Of course, even if we treat the database as a black box datastore, we still need to move that block box datastore around. Also, once you understand the database schemas for a particular Magento sub-system, its often easier to work directly with the database for read operations.
For those intrepid developers willing to brave the wilds of Magento’s database, n98-magerun has a suite of database commands to make things easier.
$ n98-magerun list db
Available commands for the "db" namespace:
db:console Opens mysql client by database config from local.xml
db:drop Drop current database
db:dump Dumps database with mysqldump cli client according to informations from local.xml
db:import Imports database with mysql cli client according to database defined in local.xml
db:info Dumps database informations
db:query Executes an SQL query on the database defined in local.xml
First, the db:info command allows you to extract useful information from Magento’s database configuration.
$ n98-magerun db:info
host : localhost
username : root
password : password
dbname : magento_example_com
initStatements : SET NAMES utf8
model : mysql4
type : pdo_mysql
pdoType :
active : 1
PDO-Connection-String : mysql:host=localhost;dbname=magento_example_com
JDBC-Connection-String : jdbc:mysql://localhost/magento_example_com?username=root&password=ididit27
MySQL-Cli-String : mysql -h'localhost' -u'root' -p'ididit27' 'magento_example_com'
Of particular interest are the PDO-Connection-String, JDBC-Connection-String, and MySQL-Cli-String fields. If you work with external systems that need to connect to Magento’s database, then having these strings prebuilt can save you from all sorts of tedious typo debugging. The db:info command is also a nice sanity check for the inevitable “am I editing the right database” problem.
Taking this laziness a step further, if you’re just looking to connect to Magento’s mysql database via the command line, then the db:console command is just what you’re looking for.
$ n98-magerun db:console
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 12052
Server version: 5.5.29 MySQL Community Server (GPL)
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql>
Similarly, the db-dump command will export your entire mysql database to a text file.
$ n98-magerun db:dump
Dump MySQL Database
Filename for SQL dump: [magento_example_com.sql]
Start dumping database magento_example_com to file magento_example_com.sql
Finished
The db-dump command also has a useful “strip” feature. This allows you to omit INSERTs from your dump file for specific tables, dumping only the structure. For example, if you wanted to export a complete Magento system, but omit all the static blocks (the cms_block and cms_block_store tables), you’d do something like this.
$ n98-magerun db:dump --strip="cms_block cms_block_store"
While useful, this still requires you understand what data can and can’t be dumped. Omit the wrong join table and you might break your entire Magento system. To help with this problem, the n98-magerun authors have created special “table groups” that logically group related tables together. For example, you can strip out customer data using the @customers group with the following
$ n98-magerun db:dump --strip="@customers"
Dump MySQL Database
Filename for SQL dump: [magento_example_com.sql]
No-data export for: customer_address_entity customer_address_entity_datetime customer_address_entity_decimal customer_address_entity_int customer_address_entity_text customer_address_entity_varchar customer_entity customer_entity_datetime customer_entity_decimal customer_entity_int customer_entity_text customer_entity_varchar
Start dumping database magento_example_com to file magento_example_com.sql
Finished
Notice the list of tables listed after No-data export for. These are the tables whose data has been stripped. You can see a list of the table groups by using the built-in help
$ n98-magerun help db:dump
...
Available Table Groups
@log Log tables
@dataflowtemp Temporary tables of the dataflow import/export tool
@stripped Standard definition for a stripped dump (logs and dataflow)
@sales Sales data (orders, invoices, creditmemos etc)
@customers Customer data
@trade Current trade data (customers and orders). You usally do not want those in developer systems.
@development Removes logs and trade data so developers do not have to work with real customer data
Keep in mind – these groups are defined by the creators of n98-magerun. It’s still a good idea to review the dumped data and ensure the information you want stripped is stripped. Even if the n98-magerun list of tables matches up with your own mental model, there’s no way this tool could know about 3rd-party extensions that may be saving sensitive data in other tables.
Once you’ve dumped your data, you’ll probably want to import it at some point. The db:import command does exactly what you’d think. Just pass in the path of your sql file as the first argument and n98-magerun will import the file.
$ n98-magerun db:import magento_example_com.sql
Another useful option supported by both db:import and db-dump is --only-command. You can use this option to spit out the command line that n98-magerun is building to perform the dump/import.
mysql -h'localhost' -u'root' -p'ididit27' 'magento_example_com' < 'magento_example_com.sql'
There’s also a command, db:query, for running raw SQL queries against the database, with results returned as tab separated lines
n98-magerun db:query "SELECT * FROM cms_page"
page_id title root_template meta_keywords meta_description identifier content_heading content creation_time update_time is_active sort_order layout_update_xml custom_theme custom_root_template custom_layout_update_xml custom_theme_from custom_theme_to
12 404 Not Found 1 two_columns_right Page keywords Page description no-route NULL <div class="page-head-alt"><h3>Whoops, our bad...</h3></div>nn<dl>nn<dt>The page you requested was not found, and we have a fine guess why.</dt>nn<dd>nn<ul class="disc">nn<li>If you typed the URL directly, please make sure the spelling is correct.</li>nn<li>If you clicked on a link to get here, the link is outdated.</li>nn</ul></dd>nn</dl>nn<br/>nn<dl>nn<dt>What can you do?</dt>nn<dd>Have no fear, help is near! There are many ways you can get back on track with Magento Demo Store.</dd>nn<dd>nn<ul class="disc">nn<li><a href="#" onclick="history.go(-1);">Go back</a> to the previous page.</li>nn<li>Use the search bar at the top of the page to search for your products.</li>nn<li>Follow these links to get you back on track!<br/><a href="{{store url=""}}">Store Home</a><br/><a href="{{store url="customer/account"}}">My Account</a></li></ul></dd></dl><br/>nn<p><img src="{{skin url='images/media/404_callout1.jpg'}}" style="margin-right:15px;"/><img src="{{skin url='images/media/404_callout2.jpg'}}" /></p> 2007-06-20 18:38:32 2013-03-21 05:04:55 1 0 NULL NULL NULL NULL NULL NULL
13 Home page two_columns_right NULL NULL home NULL <div class="col-left side-col">nn<p class="home-callout">nn<a href="{{store direct_url="apparel/shoes/womens/anashria-womens-premier-leather-sandal.html"}}"><img src="{{skin url='images/ph_callout_left_top.gif'}}" border="0" /></a>nn</p>nn<p class="home-callout">nn<img src="{{skin url='images/ph_callout_left_rebel.jpg'}}" border="0" />nn</p>nn{{block type="tag/popular" template="tag/popular.phtml"}}nn</div>nn<div class="home-spot">nn<p class="home-callout">nn<img src="{{skin url='images/home_main_callout.jpg'}}" border="0" width="470" />nn</p>nn<p class="home-callout">nn<img src="{{skin url='images/free_shipping_callout.jpg'}}" border="0" width="470" />nn</p>nn<div class="box best-selling">nn<h3>Best Selling Products</h3>nn<table border="0" cellspacing="0">nnt<tbody>nntt<tr class="odd">nnttt<td><a href="{{store direct_url="sony-vaio-vgn-txn27n-b-11-1-notebook-pc.html"}}"><img class="product-img" src="{{skin url='images/media/best_selling_img01.jpg'}}" border="0" width="95" /></a>nnttt<div class="product-description">nnttt<p><a href="{{store direct_url="sony-vaio-vgn-txn27n-b-11-1-notebook-pc.html"}}">Sony VAIO VGN-TXN27N/B 11.1" Notebook PC</a></p>nnttt<p>nntttSee all <a href="{{store direct_url="electronics/computers/laptops.html"}}">Laptops</a>nnttt</p>nnttt</div>nnttt</td>nnttt<td><a href="{{store direct_url="nine-west-women-s-lucero-pump.html"}}"><img class="product-img" src="{{skin url='images/media/best_selling_img02.jpg'}}" border="0" width="95" /></a>nnttt<div class="product-description">nnttt<p><a href="{{store direct_url="nine-west-women-s-lucero-pump.html"}}">Nine West Women's Lucero Pump</a></p>nnttt<p>nntttSee all <a href="{{store direct_url="apparel/shoes.html"}}">Shoes</a>nnttt</p>nnttt</div>nnttt</td>nntt</tr>nntt<tr class="even">nnttt<td><a href="{{store direct_url="olympus-stylus-750-7-1mp-digital-camera.html"}}"><img class="product-img" src="{{skin url='images/media/best_selling_img03.jpg'}}" border="0" width="95" /></a>nnttt<div class="product-description">nnttt<p>nnttt<a href="{{store direct_url="olympus-stylus-750-7-1mp-digital-camera.html"}}">Olympus Stylus 750 7.1MP Digital Camera</a>nnttt</p>nnttt<p>nntttSee all <a href="{{store direct_url="electronics/cameras/digital-cameras.html"}}">Digital Cameras</a>nnttt</p>nnttt</div>nnttt</td>nnttt<td><a href="{{store direct_url="acer-ferrari-3200-notebook-computer-pc.html"}}"><img class="product-img" src="{{skin url='images/media/best_selling_img04.jpg'}}" border="0" width="95" /></a>nnttt<div class="product-description">nnttt<p>nnttt<a href="{{store direct_url="acer-ferrari-3200-notebook-computer-pc.html"}}">Acer Ferrari 3200 Notebook Computer PC</a>nnttt</p>nnttt<p>nntttSee all <a href="{{store direct_url="electronics/computers/laptops.html"}}">Laptops</a>nnttt</p>nnttt</div>nnttt</td>nntt</tr>nntt<tr class="odd">nnttt<td><a href="{{store direct_url="asics-men-s-gel-kayano-xii.html"}}"><img class="product-img" src="{{skin url='images/media/best_selling_img05.jpg'}}" border="0" width="95" /></a>nnttt<div class="product-description">nnttt<p><a href="{{store direct_url="asics-men-s-gel-kayano-xii.html"}}">ASICS® Men's GEL-Kayano® XII</a></p>nnttt<p>See all <a href="{{store direct_url="apparel/shoes.html"}}">Shoes</a></p>nnttt</div>nnttt</td>nnttt<td><a href="{{store direct_url="coalesce-functioning-on-impatience-t-shirt.html"}}"><img class="product-img" src="{{skin url='images/media/best_selling_img06.jpg'}}" border="0" width="95" /></a>nnttt<div class="product-description">nnttt<p>nnttt<a href="{{store direct_url="coalesce-functioning-on-impatience-t-shirt.html"}}">Coalesce: Functioning On Impatience T-Shirt</a>nnttt</p>nnttt<p>nntttSee all <a href="{{store direct_url="apparel/shirts.html"}}">Shirts</a>nnttt</p>nnttt</div>nnttt</td>nntt</tr>nnt</tbody>nn</table>nn</div>nn</div> 2007-08-23 10:03:25 2013-03-21 05:04:55 1 0 <!--<reference name="content">nn<block type="catalog/product_new" name="home.catalog.product.new" alias="product_new" template="catalog/product/new.phtml" after="cms_page"><action method="addPriceBlockType"><type>bundle</type><block>bundle/catalog_product_price</block><template>bundle/catalog/product/price.phtml</template></action></block>nn<block type="reports/product_viewed" name="home.reports.product.viewed" alias="product_viewed" template="reports/home_product_viewed.phtml" after="product_new"><action method="addPriceBlockType"><type>bundle</type><block>bundle/catalog_product_price</block><template>bundle/catalog/product/price.phtml</template></action></block>nn<block type="reports/product_compared" name="home.reports.product.compared" template="reports/home_product_compared.phtml" after="product_viewed"><action method="addPriceBlockType"><type>bundle</type><block>bundle/catalog_product_price</block><template>bundle/catalog/product/price.phtml</template></action></block>nn</reference><reference name="right">nn<action method="unsetChild"><alias>right.reports.product.viewed</alias></action>nn<action method="unsetChild"><alias>right.reports.product.compared</alias></action>nn</reference>--> NULL NULL NULL NULL NULL
...
Again, this query is run using the standard mysql client tool — if you want the command line string use the --only-command flag.
$ n98-magerun db:query --only-command "SELECT * FROM cms_page"
mysql -h'localhost' -u'root' -p'ididit27' 'magento_example_com' -e 'SELECT * FROM cms_page'
Finally, a db:drop command is included as well, allowing you to drop the entire database. Given my bias against having such destructive commands at your finger tips, I’ll leave this one as an exercise for the reader.