The essential options are as follows:

A MySQL server runs on a specific host and listens on a specific port. The MySQL client can connect to the server using TCP/IP and for this reason you must provide --host and --port options.

Since the mysql client program gives access to a specific MySQL server, you must have credentials on that server; you must provide values for the --user and --password options.

Any of the utilities that require a connection to the MySQL server, will have host, port, user, and password options. The syntax for using these options is the same for all the various MySQL programs so you must be familiar with these options.

To open a mysql console window and communicate with a MySQL server, type the following:

shell> mysql --user=user_name --password=user_password --host=localhost --port=3306 

The same effect can be achieved using the short forms of the above options. Starting mysql using short forms is done as follows:

shell> mysql -u user_name -puser_password -h localhost -P 3306 

Specifying a password immediately after the -p option is not a requirement but if you do so no space is permitted between the option -p 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.

Fortunately, both the host and port 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 3306, and for the host, localhost. Most MySQL servers listen on port 3306 and typically you will connect to a server running on the same machine as the MySQL client.

If the server you wish to connect to is running on port 3306 on the same machine as the MySQL client then you need not specify either the port or the hostname. The mysql program will also check for the environment variable USER, 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:

shell> echo %USER%

Under Linux or Mac OS X type:

shell> echo $USER

If this user name is a valid user name for the MySQL server then you need not specify the --user option in order to connect. Connecting to a MySQL server can be as simple as:

shell> mysql -p

With the use of a configuration file, even this option need not be specified at the command line. If you typically start up mysql using a number of options, then storing these options in a configuration file is a good way to simplify things. Configuration files are discussed in detail in <xref>.

The following list of options are useful to know and can appreciably improve your efficiency when using mysql.

The --help option is especially useful should you forget what options are available. Execute the mysql 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 --help as an option.

The --auto-rehash option is on by default. It enables automatic completion of table and column names, in the way that most Unix command shells complete file names. Unfortunately, this option only works on Unix operating systems. For performance purposes you can turn this option off by specifying --skip-auto-rehash.

If you wish to start the MySQL client using a specific database, use the --database=dbname option. In addition to using the short form, -D dbname, you can also start the MySQL client 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 use dbname command.

To execute a single SQL statement and then exit the client shell, use the --execute=statement option. For example, the following command shows all the records in a specific table:

  shell> mysql -u user_name -p --execute="SELECT * FROM dbname.table_name;"

The “;” terminating the SELECT statement is optional.

The --html and --xml 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 --tee=file_name option — an option that copies all the output of mysql to a text file. To create an HTML file of all statements issue the following command:

shell> mysql -u user_name -p --tee=outfile.html --html

The --tee=file_name 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 detail in <xref>.

The --prompt option allows you to customized the prompt that the MySQL console displays. The prompt can be configured in a variety of ways; to show the current date and time, to display the default database, and the current server version, for example. This topic will be dealt with in detail in <xref>.

As noted earlier, only selected mysql options are discussed here. For a complete list see Section C.1, “mysql Options” or, at the command line simply type mysql -?.

The mysql client is an interactive shell. Once you have opened it you can use the mysql commands. A shortlist of the most useful commands follows:

The long forms of commands can be issued by typing the command name with or without a “;”. For example, the commands help; and help produce the same output.

To quit the MySQL shell use one of the forms of the quit command.

The ego command can be especially useful when issuing a select statement that returns one record with numerous columns. For example issuing the select statement, SELECT * FROM mysql.user WHERE User=`root`\G, produces much more readable output than the same statement terminated by a “;”.

To execute a script file use the source command. You can also execute script commands by redirecting a file to the mysql command. This is done on all operating systems by using the redirection operator like so; mysql -u user_name -p < script.sql. Using script files is especially useful when you have repetitive tasks to perform. Script files are discussed in detail in <xref>.

An alternative to the source command is to redirect a script file to the mysql command from the command prompt. You can do this in the following way: ...

To avoid having to fully qualify a table name by preceding it with the database name, use the use dbname command. This command makes the specified database the default database.

Some of the commands are identical to the options shown in Section 4.2.2, “Other Options”. For example issuing the tee outfile.txt command is identical to using the start-up option --tee outfile.txt. 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 no-tee command.

On the other hand, the help command, though it shares the same name as the --help option, when issued without an argument, outputs a list of all the commands but no options. For a complete list of all the available commands see Section C.2, “mysql Commands”, or from the mysql shell, issue the command ?.

However, the help command does a lot more than list available commands. For this reason the next section is devoted entirely to this command.

It is very easy to overlook the usefulness of the help command. Firstly, it is easily confused with the --help option. Secondly, you may mistakenly believe that it only displays a list of the mysql commands.

To see just how helpful this command can be, from the mysql shell, issue the command help followed by the argument, contents. Doing this results in the following display:

  You asked for help about help category: "Contents"
  For more information, type 'help <item>', where <item> is one of the following
  categories:
  Account Management
  Administration
  Data Definition
  Data Manipulation
  Data Types
  Functions
  Functions and Modifiers for Use with GROUP BY
  Geographic Features
  Language Structure
  Storage Engines
  Stored Routines
  Table Maintenance
  Transactions
  Triggers 

Now try issuing the ? Functions command. You should see a listing of all the function categories. To see the date and time functions type ? Date and Time Functions. This displays the names of all functions in this category.

To drill down even further, specify a function name in the following way; ? DATE_FORMAT. Issuing this command displays the function prototype and gives examples of how this function is used. This is very useful given the many and various format specifiers that can be used with this function.

The help topic command only works if the help_* tables in have been installed in the mysql database. Most recent binary releases come with these tables installed but if you find that they are missing, go to http://dev.mysql.com/doc/ and locate the MySQL Help Tables section. Find the help file for the MySQL server version 5.0 and download it. Decompress it and install it in the following way:

shell> mysql -u root -p mysql < file_name  

The help command is especially useful if you are new to MySQL but even experienced users will find it helpful on many occasions.

Unlike other utilities, mysqladmin supports both commands and options. This section identifies the most commonly used options and commands and briefly describes each one. Examples of using these options and commands are given in subsequent sections.

The essential commands are as follows:

Since the mysqladmin utility gives access to a specific MySQL server, you must have credentials on that server; you must explicitly or implicitly provide a --user and --password. Likewise you must provide --host and --port options. In this respect, mysqladmin does not differ from the MySQL client program, mysql.

Essential commands are shown above; the essential mysqladmin options are as follows:

Find a complete list of all the options see Section C.3, “mysqladmin Options”.

This section is concerned with the mysqladmin commands and options listed in the previous section, discussing them in more detail with the exception of the --help option since this option functions in exactly the same way for all the MySQL programs.

shell> mysqladmin shutdown -u user_name -p      

shell> mysqladmin create db_name -u user_name -p      

shell> mysqladmin ping -u user_name -p      

ERROR 2003 (HY000) Can't connect to MySQL server on 'localhost' (10061)

ERROR 2002 (HY000): Can't connect to local MySQL server through socket 
'/var/lib/mysql/mysql.sock' (2)  

mysqladmin: connect to server at 'localhost' failed
error: Can't connect to MySQL server  ...
Check that mysqld is running