You extract info from mysql databases easily by execute the command line mysql -e. The results of sql statements can be save into various format of files, such as plain text without ascii table borders (batch mode), html (with table tags, such as td,tr,th) and XML.
You are able to dump an sql scripts ( list of sql statements ) to mysql command, for examples:
mysql -u username -Ddbname -p < script.sql
-p will prompts you to type your password, and the script.sql may contain sql statement such as
script.sql is allow to contain multiple sql statements, each of the sql statement have to be ended by the terminator symbol (;)
Okay, lets look at how to make use of mysql -e.
The simplest way of executing sql statement with -e is
mysql -Dlbewp -e "show tables;"
By default, the output will be decorate by ascii borders.
+-------------------+ | Tables_in_lbewp | +-------------------+ | wp_categories | | wp_comments | | wp_linkcategories | | wp_links | | wp_mainCount | | wp_options | | wp_post2cat | | wp_postmeta | | wp_posts | | wp_usermeta | | wp_users | +-------------------+
To expect the results without ascii decoration, turns on batch mode.
mysql -Dlbewp -e "select post_title from wp_posts where post_content like '%linux%';" -B
To produce results in html format, -H
mysql -Dlbewp -e "desc wp_posts;" -H > /tmp/table.html
For xml output, -X
mysql -Dlbewp -e "select post_title from wp_posts;" -X > /tmp/table.xml
You can execute multiple sql statements. For example, I can specified the database to use and list all the tables by doing this
mysql -e "use lbewp;show tables;"
There are still more to learn, check out the man page for more details.