Home > Articles

Using MySQL Client Programs

This chapter is from the book

This chapter is from the book

This chapter covers the following exam topics:

  • Invoking command-line client programs

  • Specifying command-line options

  • The mysql client

    • Using mysql interactively

    • Using script files with mysql

    • mysql client commands and SQL statements

    • Using the ---safe-updates option

  • Using mysqlimport

  • Using mysqldump and reloading the dump

  • Checking tables with mysqlcheck and myisamchk

  • Using MySQLCC

  • Using MySQL Connector/ODBC and MySQL Connector/J

Questions on the material in this chapter make up approximately 10% of the exam.

This chapter discusses general principles that are common to most MySQL client programs. It also describes how to use several specific types of clients:

  • The interactive graphical client MySQLCC (MySQL Control Center). This general-purpose client provides a graphical interface to the MySQL server. It can be thought of as an extended graphical version of the character-based mysql client.

  • MySQLCC is still being actively developed at the time of publication of the first Core exam. For that reason, you are expected to be familiar with the general properties and capabilities of MySQLCC, but you are not expected to know all the ins and outs of the program.

  • The character-based client programs. These run either interactively or perform their job based on command-line arguments with no further input from the user. Character-based clients discussed in this chapter include:

    • mysql, a general-purpose client for issuing queries and retrieving their results. It can be used interactively or in batch mode to read queries from a file.

    • n mysqladmin, an administrative client that helps you manage the server.

    • mysqlimport, a program for importing text files into databases. It provides a command-line interface to the LOAD DATA INFILE SQL statement.

    • mysqldump, a program for dumping the contents of databases. It can be used to make database backups or to copy databases to other machines.

    • mysqlcheck and myisamchk, programs for checking and repairing tables. They're useful for checking and maintaining the integrity of certain types of tables.

  • The MySQL Connector drivers. These drivers provide connectivity to the MySQL server for client programs:

    • MySQL Connector/ODBC, the MySQL driver for programs that use the ODBC (Open Database Connectivity) interface.

    • MySQL Connector/J, the MySQL driver for JDBC connectivity to Java programs.

3.1 Invoking Command-Line Client Programs

MySQL client programs can be invoked from the command line; for example, from a Windows console prompt or from a Unix shell prompt. When you invoke a client program, you can specify options to control its behavior. Some options tell the client how to connect to the MySQL server. Other options tell the program what actions to perform.

This section discusses the following option-related topics:

  • The general syntax for specifying options

  • How to use connection parameter options

  • How to specify options in an option file

Most examples in this section use the mysql program, but the general principles apply to other MySQL client programs as well.

To determine the options supported by any MySQL program, invoke the program with the --help option. For example, to find out how to use mysql, use this command:

shell> mysql --help

To determine the version of a program, use the --version option. For example:

shell> mysql --version
mysql Ver 12.22 Distrib 4.0.18, for apple-darwin7.2.0 (powerpc)

This output indicates that the mysql client is from MySQL version 4.0.18.

3.1.1 Specifying Command-Line Options

Typically, you invoke MySQL client programs with options that indicate to the program what you want it to do. This section describes the general syntax for specifying options, as well as some of the options that are common to most MySQL clients.

3.1.1.1 Command Option Syntax

Options to MySQL programs have two general forms:

  • Long options consist of a word preceded by double dashes.

  • Short options consist of a single letter preceded by a single dash.

In many cases, a given option has both a long and a short form. For example, to display a program's version number, you can use the long --version option or the short -V option. These two commands are equivalent:

shell> mysql --version
shell> mysql -V

Options are case sensitive. --version is recognized by MySQL programs, but lettercase variations such as --Version or --VERSION are not. This applies to short options as well. -V and -v are both legal options, but mean different things.

Some options are followed by values. For example, when you specify the --host or -h option to indicate the host machine where the MySQL server is running, you must follow the option with the machine's hostname. For a long option, separate the option and the value by an equal sign (=). For short options, the option and the value can be, but need not be, separated by a space. The following three option formats are equivalent; each one specifies myhost.example.com as the host machine where the MySQL server is running:

--host=myhost.example.com
-h myhost.example.com
-hmyhost.example.com

In most cases, if you don't specify an option explicitly, a program will use a default value. Default values make it easier to invoke MySQL client programs because you need specify only those options for which the defaults are unsuitable. For example, the default server hostname is localhost, so if the MySQL server to which you want to connect is running on the local host, you need not specify --host or -h at all.

Exceptions to these option syntax rules are noted in the following discussion wherever relevant. The most important exception is that password options have a slightly different behavior than other options.

3.1.1.2 Connection Parameter Options

To connect to a server using a client program, the client must know upon which host the server is running. A connection may be established locally to a server running on the same host as the client program, or remotely to a server running on a different host. To connect, you must also identify yourself to the server with a username and password.

Each MySQL client has its own program-specific options, but all clients support a common set of options for making a connection to the MySQL server. This section describes the options that specify connection parameters, and how to use them if the default values aren't appropriate.

For command-line clients, all connection parameters are specified after the command name. The following discussion lists each option's long form and short form, as well as its default value. (You'll need to specify connection parameters for other types of client programs as well, such as the graphical MySQLCC client. Such a client might allow you to specify connection parameters on the command line, but might also provide an additional method of allowing you to indicate them, such as a dialog box.)

There are three options that indicate to the client where the server is running, as well as the type of connection to establish:

  • --host=host_name or -h host_name
  • This option specifies the machine where the MySQL server is running. The value can be a hostname or an IP number. The hostname localhost means the local host (that is, the computer on which you're running the client program). On Unix, localhost is treated in a special manner. On Windows, the value . also means the local host and is treated in a special manner as well. For a description of this special treatment, refer to the discussion of the --socket option.

  • The default host value is localhost.

  • --port=port_number or -P port_number
  • This option indicates the port number to which to connect on the server host; it applies only to TCP/IP connections. TCP/IP is used unless you connect using a hostname value of . on Windows or localhost on Unix.

    The default MySQL port number is 3306.

  • --socket=socket_name or -S socket_name
  • This option's name comes from its original use for specifying a Unix domain socket file. On Unix, for a connection to the host localhost, a client connects to the server using a Unix socket file. This option specifies the pathname of that file.

    On Windows, the --socket option is used for specifying a named pipe. For Windows NT-based systems that support named pipes, a client can connect using a pipe by specifying . as the hostname. In this case, --socket specifies the name of the pipe. Pipe names aren't case sensitive. (Note that NT-specific MySQL servers don't enable named pipe connections by default; the server must be started with the --enable-named-pipe option.)

    If this option is omitted, the default Unix socket file pathname is /tmp/mysql.sock and the default Windows pipe name is MySQL.

Two options provide identification information. These are the username and password of the account that you want to use for accessing the server. The server will reject a connection attempt unless you provide values for these parameters that correspond to an account that the server recognizes:

  • --user=user_name or -u user_name
  • This option specifies the username for your MySQL account. To determine which account applies, the server uses the username value in conjunction with the name of the host from which you connect. This means that there can be different accounts with the same username, which can be used for connections from different hosts.

    On Unix, client programs use your system login name as your default MySQL account username. On Windows, the default MySQL account name is ODBC.

  • --password=pass_value or -ppass_value
  • This option specifies the password for your MySQL account. There is no default password. If you omit this option, your MySQL account must be set up to allow you to connect without a password.

MySQL accounts are set up using the GRANT statement, which is discussed in the "Professional Study Guide."

Password options are special in two ways, compared to the other connection parameter options:

  • You can omit the password value after the option name. This differs from the other connection parameter options, each of which requires a value after the option name. If you omit the password value, the client program will prompt you interactively for a password, as shown here:

  • shell> mysql -p
    Enter password:

    When you see the Enter password: prompt, type in your password and press Enter. The password isn't echoed as you type, to prevent other people from seeing it.

  • If you use the short form of the password option (-p) and give the password value on the command line, there must be no space between the -p and the value. That is, -ppass_val is correct, but -p pass_val is not. This differs from the short form for other connection parameter options, where a space is allowed between the option and its value. (For example, -hhost_name and -h host_name are both valid.) This exceptional requirement that there be no space between -p and the password value is a logical necessity of allowing the option parameter to be omitted.

Another option that affects how the connection setup occurs is --compress (or -C). This option causes data sent between the client and the server to be compressed before transmission and uncompressed upon receipt. The result is a reduction in the number of bytes sent over the connection, which can be helpful on slow networks. The cost is additional computational overhead for both the client and server to perform compression and uncompression. --compress and -C take no value after the option name.

Here are some examples that show how to specify connection parameters:

  • Connect to the server using the default hostname and username values with no password:

  • shell> mysql
  • Connect to the server on the local host with a username of myname, asking mysql to prompt you for a password:

  • shell> mysql --host=localhost --password --user=myname
  • Connect with the same options as the previous example, but using the corresponding short option forms:

  • shell> mysql -h localhost -p -u myname
  • Connect to the server at a specific IP address, with a username of myname and password of mypass:

  • shell> mysql --host=192.168.1.33 --user=myname --password=mypass
  • Connect to the server on the local host, using the default username and password and compressing client/server traffic:

  • shell> mysql --host=localhost --compress

3.1.1.3 Using Option Files

As an alternative to specifying options on the command line, you can place them in an option file. The standard MySQL client programs look for option files at startup time and use any appropriate options they find there. Putting an option in an option file saves you time: You need not specify the option on the command line each time you invoke a program.

Options in option files are organized into groups, with each group preceded by a [group-name] line that names the group. Typically, the group name is the name of the program to which the group of options applies. For example, the [mysql] and [mysqldump] groups are for options to be used by mysql and mysqldump, respectively. The special [client] group can be used to specify options that you want all client programs to use. A common use for the [client] group is to specify connection parameters.

To write an option in an option file, use the long option format that you would use on the command line, but omit the leading dashes. Here's a sample option file:

[client]
host = myhost.example.com
compress


[mysql]
safe-updates

In this example, the [client] group specifies the server hostname and indicates that the client/server protocol should use compression for traffic sent over the network. Options in this group apply to all standard clients. The [mysql] group applies only to the mysql program. The group shown indicates that mysql should use the --safe-updates option.

Note that if an option takes a value, spaces are allowed around the = sign, something that isn't true for options specified on the command line.

Where an option file should be located depends on your operating system. The standard option files are as follows:

  • On Windows, programs use the my.ini file in the Windows directory and the C:\my.cnf file.

  • On Unix, the file /etc/my.cnf serves as a global option file used by all users. You can set up your own option file by creating a file named .my.cnf in your home directory.

To use an option file, create it as a plain text file using an editor. Client programs can access options from multiple option files, if they exist. It isn't an error for an option file to be missing.

To create or modify an option file, you must have write permission for it. Client programs need only read access.

To tell a program to read a specific option file instead of the standard option files, use the --defaults-file option. For example, to use the file C:\my-opts for mysql on Windows, invoke the program like this:

shell> mysql --defaults-file=C:\my-opts

If you use --defaults-file, it must be the first option after the command name.

If a program finds that an option is specified multiple times, either in the same option file or in multiple option files, the option value that occurs last takes precedence. Options specified on the command line take precedence over options found in option files.

3.1.2 Selecting a Default Database

For most client programs, you must specify a database so that the program knows where to find the tables that you want to use. The conventional way to do this is to name the database on the command line following any options. For example, to dump the contents of the world database to an output file named world.sql, you might run mysqldump like this:

shell> mysqldump --password --user=user_name world > world.sql

For the mysql client, a database name on the command line is optional. This is because you can explicitly indicate the database name for any table when you issue queries. For example, the following statement selects rows from the table Country in the world database:

mysql> SELECT * FROM world.Country;

To select or change the default database while running mysql, issue a USE db_name statement, where db_name is the name of the database you'd like to use. For example, the following statement makes world the default database:

mysql> USE world;

The advantage of selecting a default database with USE is that in subsequent queries you can refer to tables in that database without having to specify the database name. For example, with world selected as the default database, the following SELECT statements are equivalent, but the second is easier to write because the table name doesn't need to be qualified with the database name:

mysql> SELECT * FROM world.Country;
mysql> SELECT * FROM Country;

The default database is sometimes called the current database.

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020