mysql command lines

September 30th, 2007 mysurface Posted in Admin, mysql | Hits: 51650 | 4 Comments »

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

show tables;

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.

4 Responses to “mysql command lines”

  1. [...] such as plain text without ascii table borders batch mode, html with table tags, such as td,tr,thhttp://linux.byexamples.com/archives/316/mysql-command-lines/ASCII Code – The extended ASCII tableThe following ascii table with hex, octal, html, binary and [...]

  2. mysql

  3. I&apos;ve established I&apos;m going to make a lot of elegant things together with my time off of

  4. A formidable share, I simply given this onto a colleague who was doing a bit of evaluation on this. And he in reality bought me breakfast as a result of I found it for him.. smile. So let me reword that: Thnx for the deal with! However yeah Thnkx for spending the time to discuss this, I really feel strongly about it and love reading more on this topic. If potential, as you become expertise, would you thoughts updating your blog with more details? It’s extremely helpful for me. Huge thumb up for this blog submit!

Leave a Reply