MySQL User Guide Part I Getting Started

Back to MySQL User Guide

Contents

[edit] Part I. Getting Started

[edit] Introduction

[edit] What is MySQL?

[edit] Client and Server

[edit] MySQL Applications

[edit] MySQL On the Web

[edit] MySQL Enterprise Applications

[edit] MySQL Desktop Applications

[edit] Main Features

[edit] Installing MySQL

[edit] Introduction *Complete*

Unless you have been provided with a working copy of MySQL from your ISP or employer, the first step in using MySQL is to install MySQL on your local machine or server.

MySQL is available pre-compiled and packaged for a wide variety of platforms including Microsoft Windows, Linux, Solaris, FreeBSD, and Mac OS X to name a few.

The installation process varies by platform but generally involves downloading an installer or compressed archive, extracting or executing the downloaded file, and then configuring and starting the MySQL server. Once the MySQL server is successfully installed and configured, you can download and install the MySQL GUI tools to manage and query your new server.

In the sections that follow we will explain the process described above with regards to the more popular platforms used with MySQL, namely Microsoft Windows, Linux, and Mac OS X.

[edit] Downloading MySQL

All binary and source versions of MySQL are available at http://dev.mysql.com/downloads/. We strongly recommend you download the latest stable version of MySQL that is available for your platform (currently MySQL 5.0.X, which this userguide is based on).

This chapter will deal with installation from pre-compiled binaries only and it is recommended that you download the same. If you want information on compiling and installing MySQL from source code, please refer to the MySQL Installation Using a Source Distribution section of the MySQL Reference Manual.

The binaries you download will depend on the platform you intend to use. For specific information please refer to the appropriate section that follows.

[edit] Installation

[edit] Installing MySQL on <operating system>

[edit] MySQL Client Programs

[edit] What are the Client Programs?

A number of client programs are packaged with the MySQL RDMS. There are administrative programs, utilities to assist in backing up data, utilities for repairing tables, and numerous other tools.

[edit] The MySQL Client, mysql *Complete*

MySQL Contributor. This section was contributed by MySQL staff. For more information see http://mysql.com.

The most important client tool is the mysql program, usually referred to as the MySQL client. This is a command-line program for interacting with a MySQL server. Use mysql to:

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 way of creating users or changing their privileges. Likewise, it is often helpful to test any SQL statements that are issued by a program. An easy way to perform all these tasks is to use the MySQL client.

However, we won't be discussing SQL commands here. 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 an essential part of mastering MySQL. Options passed to the mysql command make connecting to a MySQL server possible and also change the way that the mysql 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.

[edit] Essential Options

The essential options are as follows:

Note: The commonly used options of any MySQL program typically have a long and a short form. The long form is always a full word preceded by two dashes and followed by an equals sign and a value, if a value is required. The short form is a single letter, upper or lower case, preceded by one dash and followed by a space and a value if necessary. (The short form of the password option is the only exception to this rule and will be dealt with shortly.) When an option is first introduced, using the long form is helpful for reasons of clarity. Afterwards, the short form is preferred for the sake of brevity.

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
Note: If you have only just installed MySQL and have not yet defined any MySQL users then specify the default, root, as the user name. There is no password for this default user. For information on creating additional users see <xref>.

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

[edit] Warning

On any operating system (OS) the value of the variable USER is typically the name of the current OS 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.

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>.

[edit] Other Options

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
Note: There is no short form for the --tee=file_name option.

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 "mysql Options" or, at the command line simply type mysql -?.

[edit] The mysql Commands

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 ";</span>". 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 <code>user_name -p < script.sql</code>. 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 Other Options. For example issuing the tee outfile.txt command is identical to using the start-up option --tee <code>outfile.txt</code>. 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 [userguide-options-tables.html#userguide-mysql-commands-table 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.

[edit] Using the help 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.

[edit] The mysqladmin Client Program *Complete*

MySQL Contributor. This section was contributed by MySQL staff. For more information see http://mysql.com.

The mysqladmin program is a client utility for administering a MySQL server. As is the case with most MySQL utilities, there are often alternatives to using mysqladmin. The GUI Tool, MySQL Administrator, for example, can do most of the tasks performed by mysqladmin and is a very useful administrative tool especially if you are new to MySQL. This application is examined in detail in <xref>. Likewise, many of the capabilities of mysqladmin are also available when using the MySQL client program.

However, the MySQL server may be running on a machine that does not have a GUI; for instance, most web servers would fall into this category. mysqladmin offers a convenient alternative that allows you to perform common tasks quickly from the command line without starting up a GUI application or the MySQL client. This chapter shows how to use mysqladmin for these kinds of tasks. It is not meant as a definite treatment of mysqladmin; for complete coverage of this utility see http://dev.mysql.com/doc/refman5.0/en/mysqladmin.html. The more advanced commands and options of mysqladmin will be discussed in more detail in <xref>.

[edit] Options and Commands

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 [userguide-options-tables.html#userguide-mysqladmin-options-table Section C.3, "mysqladmin Options"].

[edit] Using mysqladmin

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.

[edit] Shutting Down the Server

The most common use for mysqladmin is to shut down the MySQL server. This is done in the following way: 

shell> mysqladmin shutdown -u user_name -p
Note: This is a command rather than an option so do not precede shutdown with "--". The reasons for shutting down a MySQL server are various:

This command is used extensively in <xref>. In that section we examine the configuration file (my.ini under Windows, my.cnf under Unix and Mac OS X). In order for configuration changes to have effect the server must be stopped and restarted.

[edit] Creating and Dropping Databases

Instead of issuing an SQL command to create a database, you can create one using mysqladmin in the following way: 

shell> mysqladmin create db_name -u user_name -p

You can just as easily remove a database using the drop db_name command. To avoid confirming your action use this command with the -f option.

[edit] Checking that the Server is Running

The ping command is an easy way to determine if your MySQL server is running. To test this command, do the following: 

shell> mysqladmin ping -u user_name< -p

This should result in the message, mysqld is alive, confirming that your MySQL server is running.

If you are working in a development environment, shut down the server using mysqladmin and the shutdown command. Try connecting to the server using the MySQL client mysql. On Windows you should see the error message: 

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

The message on Unix systems is slightly different: 

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

Familiarize yourself with these error messages because you will see them again -- but probably by accident and not, as in this case, by design. In most situations, these errors indicate that the MySQL server is not running.

Execute mysqladmin once more with the ping command, to see the message displayed when the server is down. You should see: 

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

To restart the server on Unix [and Mac] systems issue the command, mysqld_safe and on Windows, mysqld (?). For more information ...

[edit] Basic Administration

[edit] Introduction

[edit] Using MySQL Administrator

[edit] Starting and Stopping the MySQL Server

[edit] Administering Users

[edit] MySQL Server Programs

[edit] The MySQL Server

[edit] Essential Server Options

The essential options and system variables are as follows:

To see the default format that MySQL uses for dates issue the statement SELECT NOW(); from the MySQL client. You should see output like the following: 

+---------------------+
 | NOW()               |
 +---------------------+
 | 2007-06-27 16:19:37 |
 +---------------------+

If you don't find this date/time format suitable, you can change it by using the --date-format and --datetime-format options. For example, to change the date format to a two digit month followed by a two digit day of the month and a four digit year separated by a forward slash use the following option: --date-format=`%m/%d/%Y`. For all the legal formats see [Date Format Specifiers Table-- not yet complete].

The --skip-networking option allows only local (non-TCP) connections. On Unix, local connections use a Unix socket file and on Windows, they use a named pipe or shared memory. For a development server you will most likely not want to use this option. However, some distributions come with this option activated so it is useful to know about it so that you can disable it.

Likewise with the --bind-address. This option binds the MySQL server to a specific IP address, typically 127.0.0.1 effectively disabling access from a remote location. It is mentioned here so that you know how to disable it.

[edit] The my.cnf/my.ini File

In the chapter on MySQL client programs we hinted that there was a better way to make use of command options than specifying them at the command line. Entering options from the command line can be both tedious and error-prone. The better way is to store options in a configuration file so that the need not be specified each time you use a specific program.

Under Windows this file is usually found in the C:\Program Files\MySQL\MySQL Server 5.0 directory and on Linux ...

Options in /etc/my.cnf and $MYSQL_HOME/my.cnf are processed before command-line options, so it is recommended that you put a --user option in the configuration file and specify a value other than root. The option in the configuration file is found before any other --user options, which ensures that the server runs as a user other than root, and that a warning results if any other --user option is found.

Changing the configuration file requires stopping the server. The easiest way to start and stop the MySQL server under Windows is from the Microsoft Management Console Services window. To open this window, go to the Control Panel and find Administrative Tools. Double click this icon and then choose Services. Find the MySQL entry, select it, and stop the service. Also menu item ...

To stop the MySQL server under Linux or Mac OS X use mysqladmin with the shutdown command. You may also need to specify other options such as --user and --password.

You are now ready to make changes to the configuration file.

== Changing Your Configuration File ==

Retrieved from "http://forge.mysql.com/wiki/MySQL_User_Guide_Part_I_Getting_Started"

This page has been accessed 4,951 times. This page was last modified 15:24, 29 February 2008.

Find

Browse
MySQLForge
Main Page
Current events
Recent changes
Random page
Help
Edit
Edit this page
Editing help
This page
Discuss this page
Post a comment
Printable version
Context
Page history
What links here
Related changes
My pages
Special pages
New pages
File list
Statistics
Bug reports
More...