Author: plavin
Date: 2007-05-15 02:21:15 +0200 (Tue, 15 May 2007)
New Revision: 6452
Log:
Add section on the MySQL client
Added:
trunk/userguide/mysql-client.xml
Added: trunk/userguide/mysql-client.xml
===================================================================
--- trunk/userguide/mysql-client.xml (rev 0)
+++ trunk/userguide/mysql-client.xml 2007-05-15 00:21:15 UTC (rev 6452)
Changed blocks: 1, Lines Added: 507, Lines Deleted: 0; 16511 bytes
@@ -0,0 +1,507 @@
+<?xml version="1.0" encoding="utf-8"?>
+<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.3//EN"
+"http://www.oasis-open.org/docbook/xml/4.3/docbookx.dtd"
+[
+<!ENTITY % all.entities SYSTEM "all-entities.ent">
+%all.entities;
+]>
+<chapter id="mysql-client">
+
+ <title>The MySQL Client</title>
+
+ <para>
+ To interact with a database server you need a client program.
+ The most useful client for interacting with a MySQL server is
+ <command>mysql</command>, the command-line, client program. Use
+ <command>mysql</command> to:
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ create a database or database objects
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ query a database
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ perform administration tasks
+ </para>
+ </listitem>
+
+ </itemizedlist>
+ </para>
+
+ <para>
+ Most often databases are integrated into applications and most of the
+ interaction with the database server happens through the
+ program's user interface. However,
+ this is usually not a convenient way to create databases or database objects.
+ Nor is it a convenient or conventional way of
+ creating users or changing their privileges.
+ Likewise, it is always wise to tes any SQL statements that are
+ issued by a program. A convenient way to do this is to use the
+ MySQL client.
+ </para>
+
+ <para>
+ However, we won't be discussing SQL commands here
+ Next to the MySQL server, the <command>mysql</command> program, is
+ the most important program that ....
+ The purpose of this section is to explain the options and commands
+ most commonly used with the MySQL client. Using these options and commands
+ is essential
+ </para>
+
+<!-- start essential options -->
+
+ <section id="mysql-client-essential-options">
+
+ <title>Essential Options</title>
+
+ <para>
+ Options passed to the <command>mysql</command> command make
+ connecting to a MySQL server possible and also change the way that
+ the <command>mysql</command> client program behaves. Some of these
+ options are absolutely essential, some are nice to know, and
+ others are used infrequently. We will deal with the essential
+ and nice-to-know options. Essential options are as follows:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <option>--host=<replaceable>hostname</replaceable>, -h
+ <replaceable>hostname</replaceable> </option> – the host
+ to connect to
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--password=<replaceable>[user_password]</replaceable>,
+ -p<replaceable>[user_password]</replaceable></option> –
+ password for connecting to the server
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>port=<replaceable>port_number</replaceable>, -P
+ <replaceable>port_number</replaceable></option> – the
+ TCP/IP port number
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--user=<replaceable>user_name</replaceable>, -u
+ <replaceable>user_name</replaceable></option> – the
+ MySQL username to use when connecting to the server
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <note>
+ <para>
+ The commonly used options of any MySQL program typically have a
+ long and a short form. When an option is first introduced, using
+ the long form is helpful for reasons of clarity. Once an option
+ becomes familiar, the short form is preferred for the sake
+ of brevity.
+ </para>
+ </note>
+
+ <para>
+ A MySQL server runs on a specific host and listens on a specific
+ port. The MySQL client connects to the server using TCP/IP and for
+ this reason you must provide <option>--host</option> and
+ <option>--port</option> options.
+ </para>
+
+ <para>
+ Since the <command>mysql</command> client program gives access to
+ a specific MySQL server, you must have credentials on that server;
+ you must provide a <option>--user</option> and
+ <option>--password</option>.
+ </para>
+
+ <para>
+ Any of the utilities that require a connection to the MySQL
+ server, will require <option>host, port, user</option> and
+ <option>password</option> options. For this reason, these options
+ will be required by many of the utility programs discussed here.
+ </para>
+
+ <para>
+ The basic syntax for using the <command>mysql</command> to open a
+ console window and communicate with the MySQL server is as
+ follows:
+ </para>
+
+<programlisting>
+shell> <userinput>mysql <option>--user=<replaceable>user_name</replaceable> --password=<replaceable>user_password</replaceable> --host=<replaceable>localhost</replaceable> --port=<replaceable>3306</replaceable></option></userinput>
+</programlisting>
+
+ <note>
+ <para>
+ If you have only just installed MySQL and have not yet defined
+ any MySQL users then specify a user name,
+ <literal>root</literal>. There is no password for this default
+ user. For information on creating additional users see
+ <xref>.
+ </para>
+ </note>
+
+ <para>
+ The same effect can be achieved using the short forms of the above
+ options. Starting <command>mysql</command> using short forms is as follows:
+ </para>
+
+<programlisting>
+shell> <userinput>mysql <option>-u <replaceable>user_name</replaceable> -p<replaceable>user_password</replaceable> -h<replaceable>localhost</replaceable> -P <replaceable>3306</replaceable></option></userinput>
+</programlisting>
+
+ <para>
+ Fortunately, both the <option>host</option> and
+ <option>port</option> options have default values so you need not
+ supply them every time you connect to a MySQL server. The default
+ value for the port is <literal>3306</literal>, and for the host
+ <literal>localhost</literal>. Most MySQL servers listen on port
+ <literal>3306</literal> and typically you will connect to a server
+ running on the same machine as the MySQL client.
+ </para>
+
+ <para>
+ If the server you wish to connect to is running on port
+ <literal>3306</literal> on the same machine as the MySQL client
+ then you need not specify either of the port or the hostname. The
+ <command>mysql</command> program will also check for the
+ environment variable <literal>USER</literal>, if no user name is
+ provided at the command line. To check the value of this variable
+ under Windows go to the command line and type:
+ </para>
+
+<programlisting>
+shell> echo %user
+</programlisting>
+
+ <para>
+ Under Linux or Mac OS X type:
+ </para>
+
+<programlisting>
+shell> echo $USER
+</programlisting>
+
+ <warning>
+ <para>
+ On any operating system (OS) the value of the variable
+ <literal>user</literal> is typically the name of the current
+ user — there is no requirement that there also be a MySQL
+ user with the same name, though this may often in fact be the
+ case.
+ </para>
+ </warning>
+
+ <para>
+ If this user name is a valid user name for the MySQL server then
+ you need not specify the <option>--user</option> option in order
+ to connect.
+ </para>
+
+ <para>
+ Specifying a password immediately after the <option>-p</option> option is not
+ a requirement but if you do so no space is permitted between the
+ option <option>-p</option> and the password. Omitting the password
+ value following the password option is considered more secure. If
+ you do this, you are prompted for a password and asterisks replace
+ any letters typed.
+ </para>
+
+ </section>
+
+ <section id="mysql-client-other-options">
+
+ <title>Other Options</title>
+
+ <para>
+ The following list of options are useful to know and can
+ appreciably improve your efficiency when using
+ <command>mysql</command>.
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <option>--help, -?</option> – show the available
+ options and their default values and close the MySQL client
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--auto-rehash</option> – enable table name and
+ column name completion
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--database-name=<replaceable>db_name</replaceable>,
+ -D <replaceable>db_name</replaceable>,
+ <replaceable>db_name</replaceable></option> – the
+ databse to use on start up
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--execute=<replaceable>statement</replaceable>, -e
+ <replaceable>statement</replaceable></option> –
+ execute the specified statement and close the MySQL client
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--html, -H</option> – output in HTML format
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--no-tee</option> – do not copy output to file
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--prompt=<replaceable>opt_string</replaceable></option>
+ – configure the prompt
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>tee=<replaceable>outfile</replaceable>, -T
+ <replaceable>outfile</replaceable></option> – copy
+ output to the specified file
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--xml, -X</option> – output in XML format
+ </para>
+ </listitem>
+
+ </itemizedlist>
+ </para>
+
+ <para>
+ The <option>--help</option> option is especially useful should you
+ forget what options are available. Execute the
+ <command>mysql</command> command with this option in order to
+ display all available options and their default values. The
+ interactive MySQL shell does not open when you use this option.
+ Most of the MySQL programs have <option>--help</option> as an
+ option.
+ </para>
+
+ <para>
+ The <option>--auto-rehash</option> option is on by default. It
+ enables completion of table and column names, like most Unix
+ command shells and only works on Unix operating systems. For
+ performance purposes you can turn this option off by specifying
+ <option>--skip-auto-rehash</option>.
+ </para>
+
+ <para>
+ If you wish to start the MySQL client using a specific database,
+ use the
+ <option>--database=<replaceable>dbname</replaceable></option>
+ option. In addition to using the short form, you can start up
+ using a specific database simply by specifying the database name
+ at the command line. This option is equivalent to opening the
+ MySQL client and then issuing a <command>use
+ <replaceable>dbname</replaceable></command> command.
+ </para>
+
+ <para>
+ To issue a single SQL command and the exit the client shell, use
+ the
+ <option>--execute=<replaceable>statement</replaceable></option>
+ option. For example, the following command shows all the records
+ in a specific table:
+ </para>
+
+<programlisting>
+shell> mysql -u <option>user_name</option> -p -e "SELECT * FROM <replaceable>dbname.table_name</replaceable>;"
+</programlisting>
+
+ <para>
+ The ‘<literal>;</literal>’ terminating the
+ <literal>SELECT</literal> statement is optional.
+ </para>
+
+ <para>
+ The <option>--html</option> and <option>--xml</option> options
+ format all output as HTML or XML. This can be especially useful
+ and time-saving if you need to dump the contents of a table in
+ HTML or XML format. To get maximum benefit from these options you
+ need to be familiar with the
+ <option>--tee=<replaceable>file_name</replaceable></option> option
+ — an option that copies all the output of
+ <command>mysql</command> to the specified file name. To create an
+ HTML file of all statements issue the following command:
+ </para>
+
+<programlisting>
+shell> <userinput>mysql -u <option>user_name</option> -p --tee=outfile.html --html</userinput>
+</programlisting>
+
+ <para>
+ <emphasis>Note</emphasis>: There is no short form for the
+ <option>--tee=<replaceable>file_name</replaceable></option> option.
+ </para>
+
+ <para>
+ The <option>--tee=<replaceable>file_name</replaceable></option>
+ option is also especially useful if you want to keep a record of
+ the SQL statements that you have issued. This is also an excellent
+ way to begin creating a script file. Script files are dealt with
+ in more detail in <xref>.
+ </para>
+
+ <para>
+ prompt
+ </para>
+
+ <para>
+ For a complete list of all the available options see
+ <ulink url="&base-url-refman;5.0/en/mysql.html"/>.
+ </para>
+
+ <para>
+ it can be quite cumbersome to specify options at the command line.
+
+ </para>
+
+ </section>
+
+ <section id="mysql-client-commands">
+
+ <title><command>mysql</command> Commands</title>
+
+ <para>
+ Once the MySQL client is open
+ </para>
+
+
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <command>help, \?, ?</command> – display the available commands
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <command>exit, quit, \q</command> – exit the MySQL shell
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <command>notee</command> – stop capturing output to file
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <command>source, \.</command> –
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <command>tee <replaceable>file_name</replaceable>, \T <replaceable>file_name</replaceable></command> –
+ copy output to the specified file
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <command>use <replaceable>dbname</replaceable>, \u <replaceable>dbname</replaceable></command> –
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+
+ <para>
+ the <command>mysql</command> client is interactive The long forms
+ of commands can be issued by typing the command name with or
+ without a <literal>‘;’</literal>. For example, the commands
+ <command>help;</command> and <command>help</command> produce the
+ same output.
+ </para>
+
+ <para>
+ Some of the commands are identical to the options shown in
+ <xref linkend="mysql-client-other-options"/>. For
+ example issuing the <command>tee
+ <replaceable>outfile.txt</replaceable></command> command is
+ identical to using the start-up option <option>--tee
+ <replaceable>outfile.txt</replaceable></option>. Being able to
+ redirect output to a file after starting up the MySQL client can
+ be very convenient when you only want to capture some and not all
+ output to file. When you no longer wish to capture output,
+ issue the <command>no-tee</command> command.
+ </para>
+
+ <para>
+ On the other hand, the <command>help</command>
+ command, though it shares the same name as the <option>--help</option>
+ option, outputs commands only.
+ </para>
+
+ <para>
+
+ </para>
+
+
+ <para>
+ For a complete list of all the available commands see
+ <ulink url="&base-url-refman;5.0/en/mysql-commands.html"/>.
+ </para>
+
+ </section>
+
+ <section id="using-mysql-client">
+
+ <title>Using <command>mysql</command></title>
+
+ <para>
+
+ </para>
+
+ <para>
+ redirection operator to accept input from file
+ </para>
+
+ </section>
+
+</chapter>
+<!-- END mysql client CHAPTER -->
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r6452 - trunk/userguide | plavin | 15 May |
