Home > Articles > Data > MySQL

Using MySQL Programs

This chapter is from the book

3.3 Specifying Program Options

There are several ways to specify options for MySQL programs:

  • List the options on the command line following the program name. This is most common for options that apply to a specific invocation of the program.
  • List the options in an option file that the program reads when it starts. This is common for options that you want the program to use each time it runs.
  • List the options in environment variables. This method is useful for options that you want to apply each time the program runs. In practice, option files are used more commonly for this purpose. However, Section 4.13.2, "Running Multiple Servers on Unix," discusses one situation in which environment variables can be very helpful. It describes a handy technique that uses such variables to specify the TCP/IP port number and Unix socket file for both the server and client programs.

MySQL programs determine which options are given first by examining environment variables, then by reading option files, and then by checking the command line. If an option is specified multiple times, the last occurrence takes precedence. This means that environment variables have the lowest precedence and command-line options the highest.

You can take advantage of the way that MySQL programs process options by specifying default values for a program's options in an option file. That enables you to avoid typing them each time you run the program, but also allows you to override the defaults if necessary by using command-line options.

3.3.1 Using Options on the Command Line

Program options specified on the command line follow these rules:

  • Options are given after the command name.
  • An option argument begins with one dash or two dashes, depending on whether it has a short name or a long name. Many options have both forms. For example, -? and --help are the short and long forms of the option that instructs a MySQL program to display a help message.
  • Option names are case sensitive. -v and -V are both legal and have different meanings. (They are the corresponding short forms of the --verbose and --version options.)
  • Some options take a value following the option name. For example, -h localhost or --host=localhost indicate the MySQL server host to a client program. The option value tells the program the name of the host where the MySQL server is running.
  • For a long option that takes a value, separate the option name and the value by an '=' sign. For a short option that takes a value, the option value can immediately follow the option letter, or there can be a space between: -hlocalhost and -h localhost are equivalent. An exception to this rule is the option for specifying your MySQL password. This option can be given in long form as --password= pass_val or as --password. In the latter case (with no password value given), the program prompts you for the password. The password option also may be given in short form as -p pass_val or as -p. However, for the short form, if the password value is given, it must follow the option letter with no intervening space. The reason for this is that if a space follows the option letter, the program has no way to tell whether a following argument is supposed to be the password value or some other kind of argument. Consequently, the following two commands have two completely different meanings:

    shell> mysql -ptest
    shell> mysql -p test
    
          

    The first command instructs mysql to use a password value of test, but specifies no default database. The second instructs mysql to prompt for the password value and to use test as the default database.

Some options control behavior that can be turned on or off. For example, the mysql client supports a --column-names option that determines whether or not to display a row of column names at the beginning of query results. By default, this option is enabled. However, you may want to disable it in some instances, such as when sending the output of mysql into another program that expects to see only data and not an initial header line.

To disable column names, you can specify the option using any of these forms:

--disable-column-names
--skip-column-names
--column-names=0

The --disable and --skip prefixes and the =0 suffix all have the same effect: They turn the option off.

The "enabled" form of the option may be specified in any of these ways:

--column-names
--enable-column-names
--column-names=1

If an option is prefixed by --loose, a program does not exit with an error if it does not recognize the option, but instead issues only a warning:

shell> mysql --loose-no-such-option
mysql: WARNING: unknown option '--no-such-option'

The --loose prefix can be useful when you run programs from multiple installations of MySQL on the same machine and list options in an option file. An option that may not be recognized by all versions of a program can be given using the --loose prefix (or loose in an option file). Versions of the program that recognize the option process it normally, and versions that do not recognize it issue a warning and ignore it.

Another option that may occasionally be useful with mysql is the --execute or -e option, which can be used to pass SQL statements to the server. The statements must be enclosed by single or double quotation marks. If you wish to use quoted values within a statement, you should use double quotes for the statement, and single quotes for any quoted values within the statement. When this option is used, mysql executes the statements and exits.

For example, you can use the following command to obtain a list of user accounts:

shell> mysql -u root -p --execute="SELECT User, Host FROM user" mysql
Enter password: ******
+------+-----------+
| User | Host      |
+------+-----------+
|      | gigan     |
| root | gigan     |
|      | localhost |
| jon  | localhost |
| root | localhost |
+------+-----------+
shell>

Note that the long form (--execute) is followed by an equal sign (=).

In the preceding example, the name of the mysql database was passed as a separate argument. However, the same statement could have been executed using this command, which specifies no default database:

mysql> mysql -u root -p --execute="SELECT User, Host FROM mysql.user"

Multiple SQL statements may be passed on the command line, separated by semicolons:

shell> mysql -u root -p -e "SELECT VERSION();SELECT NOW()"
Enter password: ******
+------------+
| VERSION()  |
+------------+
| 5.0.19-log |
+------------+
+---------------------+
| NOW()               |
+---------------------+
| 2006-01-05 21:19:04 |
+---------------------+

The --execute or -e option may also be used to pass commands in an analogous fashion to the ndb_mgm management client for MySQL Cluster. See Section 9.3.6, "Safe Shutdown and Restart," for an example.

3.3.2 Using Option Files

Most MySQL programs can read startup options from option files (also sometimes called "configuration files"). Option files provide a convenient way to specify commonly used options so that they need not be entered on the command line each time you run a program.

To determine whether a program reads option files, invoke it with the --help option (--verbose and --help for mysqld). If the program reads option files, the help message indicates which files it looks for and which option groups it recognizes.

Note: Option files used with MySQL Cluster programs are covered in Section 9.4, "MySQL Cluster Configuration."

On Windows, MySQL programs read startup options from the following files:

Filename

Purpose

WINDIR \my.ini

Global options

C:\my.cnf

Global options

INSTALLDIR \my.ini

Global options

defaults-extra-file

The file specified with --defaults-extra-file= path , if any

WINDIR represents the location of your Windows directory. This is commonly C:\WINDOWS or C:\WINNT. You can determine its exact location from the value of the WINDIR environment variable using the following command:

C:\> echo %WINDIR%

INSTALLDIR represents the installation directory of MySQL. This is typically C:\ PROGRAMDIR \MySQL\MySQL 5.0 Server where PROGRAMDIR represents the programs directory (usually Program Files on English-language versions of Windows), when MySQL 5.0 has been installed using the installation and configuration wizards. See Section 2.3.4.14, "The Location of the my.ini File."

On Unix, MySQL programs read startup options from the following files:

Filename

Purpose

/etc/my.cnf

Global options

$MYSQL_HOME/my.cnf

Server-specific options

defaults-extra-file

The file specified with --defaults-extra-file= path , if any

~/.my.cnf

User-specific options

MYSQL_HOME is an environment variable containing the path to the directory in which the server-specific my.cnf file resides. (This was DATADIR prior to MySQL version 5.0.3.)

If MYSQL_HOME is not set and you start the server using the mysqld_safe program, mysqld_safe attempts to set MYSQL_HOME as follows:

  • Let BASEDIR and DATADIR represent the pathnames of the MySQL base directory and data directory, respectively.
  • If there is a my.cnf file in DATADIR but not in BASEDIR , mysqld_safe sets MYSQL_HOME to DATADIR .
  • Otherwise, if MYSQL_HOME is not set and there is no my.cnf file in DATADIR , mysqld_safe sets MYSQL_HOME to BASEDIR .

Typically, DATADIR is /usr/local/mysql/data for a binary installation or /usr/local/var for a source installation. Note that this is the data directory location that was specified at configuration time, not the one specified with the --datadir option when mysqld starts. Use of --datadir at runtime has no effect on where the server looks for option files, because it looks for them before processing any options.

MySQL looks for option files in the order just described and reads any that exist. If an option file that you want to use does not exist, create it with a plain text editor.

If multiple instances of a given option are found, the last instance takes precedence. There is one exception: For mysqld, the first instance of the --user option is used as a security precaution, to prevent a user specified in an option file from being overridden on the command line.

Note: On Unix platforms, MySQL ignores configuration files that are world-writable. This is intentional, and acts as a security measure.

Any long option that may be given on the command line when running a MySQL program can be given in an option file as well. To get the list of available options for a program, run it with the --help option.

The syntax for specifying options in an option file is similar to command-line syntax, except that you omit the leading two dashes. For example, --quick or --host=localhost on the command line should be specified as quick or host=localhost in an option file. To specify an option of the form --loose- opt_name in an option file, write it as loose- opt_name .

Empty lines in option files are ignored. Non-empty lines can take any of the following forms:

  • # comment , ; comment

    Comment lines start with '#' or ';'. A '#' comment can start in the middle of a line as well.

  • [ group ]

    group is the name of the program or group for which you want to set options. After a group line, any option-setting lines apply to the named group until the end of the option file or another group line is given.

  • opt_name

    This is equivalent to -- opt_name on the command line.

  • opt_name = value

    This is equivalent to -- opt_name = value on the command line. In an option file, you can have spaces around the '=' character, something that is not true on the command line. You can enclose the value within single quotes or double quotes, which is useful if the value contains a '#' comment character or whitespace.

For options that take a numeric value, the value can be given with a suffix of K, M, or G (either uppercase or lowercase) to indicate a multiplier of 1024, 10242 or 10243. For example, the following command tells mysqladmin to ping the server 1024 times, sleeping 10 seconds between each ping:

mysql> mysqladmin --count=1K --sleep=10 ping

Leading and trailing blanks are automatically deleted from option names and values. You may use the escape sequences '\b', '\t', '\n', '\r', '\\', and '\s' in option values to represent the backspace, tab, newline, carriage return, backslash, and space characters, respectively.

Because the '\\' escape sequence represents a single backslash, you must write each '\' as '\\'. Alternatively, you can specify the value using '/' rather than '\' as the pathname separator.

If an option group name is the same as a program name, options in the group apply specifically to that program. For example, the [mysqld] and [mysql] groups apply to the mysqld server and the mysql client program, respectively.

The [client] option group is read by all client programs (but not by mysqld). This allows you to specify options that apply to all clients. For example, [client] is the perfect group to use to specify the password that you use to connect to the server. (But make sure that the option file is readable and writable only by yourself, so that other people cannot find out your password.) Be sure not to put an option in the [client] group unless it is recognized by all client programs that you use. Programs that do not understand the option quit after displaying an error message if you try to run them.

Here is a typical global option file:

[client]
port=3306
socket=/tmp/mysql.sock


[mysqld]
port=3306
socket=/tmp/mysql.sock
key_buffer_size=16M
max_allowed_packet=8M


[mysqldump]
quick

The preceding option file uses var_name = value syntax for the lines that set the key_buffer_size and max_allowed_packet variables.

Here is a typical user option file:

[client]
# The following password will be sent to all standard MySQL clients
password="my_password"

[mysql]
no-auto-rehash
connect_timeout=2


[mysqlhotcopy]
interactive-timeout

If you want to create option groups that should be read by mysqld servers from a specific MySQL release series only, you can do this by using groups with names of [mysqld-4.1], [mysqld-5.0], and so forth. The following group indicates that the --new option should be used only by MySQL servers with 5.0.x version numbers:

[mysqld-5.0]
new

Beginning with MySQL 5.0.4, it is possible to use !include directives in option files to include other option files and !includedir to search specific directories for option files. For example, to include the /home/mydir/myopt.cnf file, you can use the following directive:

!include /home/me/myopt.cnf

To search the /home/mydir directory and read option files found there, you would use this directive:

!includedir /home/mydir

Note: Currently, any files to be found and included using the !includedir directive on Unix operating systems must have filenames ending in .cnf. On Windows, this directive checks for files with the .ini or .cnf extension.

Note that options read from included files are applied in the context of the current option group. Suppose that you were to write the following lines in my.cnf:

[mysqld]
!include /home/mydir/myopt.cnf

In this case, the myopt.cnf file is processed only for the server, and the !include directive is ignored by any client applications. However, if you were to use the following lines, the directory /home/mydir/my-dump-options is checked for option files by mysqldump only, and not by the server or by any other client applications:

[mysqldump]
!includedir /home/mydir/my-dump-options

If you have a source distribution, you can find sample option files named my- xxxx .cnf in the support-files directory. If you have a binary distribution, look in the support-files directory under your MySQL installation directory. On Windows, the sample option files may be located in the MySQL installation directory (see earlier in this section or Chapter 2, "Installing and Upgrading MySQL," if you do not know where this is). Currently, there are sample option files for small, medium, large, and very large systems. To experiment with one of these files, copy it to C:\my.cnf on Windows or to .my.cnf in your home directory on Unix.

Note: On Windows, the .cnf option file extension might not be displayed.

All MySQL programs that support option files handle the following options. They affect option-file handling, so they must be given on the command line and not in an option file. To work properly, each of these options must immediately follow the command name, with the exception that --print-defaults may be used immediately after --defaults-file or --defaults-extra-file.

  • --no-defaults

    Don't read any option files.

  • --print-defaults

    Print the program name and all options that it gets from option files.

  • --defaults-file= file_name

    Use only the given option file. file_name is the full pathname to the file.

  • --defaults-extra-file= file_name

    Read this option file after the global option file but (on Unix) before the user option file. file_name is the full pathname to the file.

In shell scripts, you can use the my_print_defaults program to parse option files and see what options would be used by a given program. The following example shows the output that my_print_defaults might produce when asked to show the options found in the [client] and [mysql] groups:

shell> my_print_defaults client mysql
--port=3306
--socket=/tmp/mysql.sock
--no-auto-rehash

Note for developers: Option file handling is implemented in the C client library simply by processing all options in the appropriate group or groups before any command-line arguments. This works well for programs that use the last instance of an option that is specified multiple times. If you have a C or C++ program that handles multiply specified options this way but that doesn't read option files, you need add only two lines to give it that capability. Check the source code of any of the standard MySQL clients to see how to do this.

Several other language interfaces to MySQL are based on the C client library, and some of them provide a way to access option file contents. These include Perl and Python. For details, see the documentation for your preferred interface.

3.3.3 Using Environment Variables to Specify Options

To specify an option using an environment variable, set the variable using the syntax appropriate for your command processor. For example, on Windows or NetWare, you can set the USER variable to specify your MySQL account name. To do so, use this syntax:

SET USER=your_name

The syntax on Unix depends on your shell. Suppose that you want to specify the TCP/IP port number using the MYSQL_TCP_PORT variable. Typical syntax (such as for sh, bash, zsh, and so on) is as follows:

MYSQL_TCP_PORT=3306
export MYSQL_TCP_PORT

The first command sets the variable, and the export command exports the variable to the shell environment so that its value becomes accessible to MySQL and other processes.

For csh and tcsh, use setenv to make the shell variable available to the environment:

setenv MYSQL_TCP_PORT 3306

The commands to set environment variables can be executed at your command prompt to take effect immediately, but the settings persist only until you log out. To have the settings take effect each time you log in, place the appropriate command or commands in a startup file that your command interpreter reads each time it starts. Typical startup files are AUTOEXEC.BAT for Windows, .bash_profile for bash, or .tcshrc for tcsh. Consult the documentation for your command interpreter for specific details.

3.3.4 Using Options to Set Program Variables

Many MySQL programs have internal variables that can be set at runtime. Program variables are set the same way as any other long option that takes a value. For example, mysql has a max_allowed_packet variable that controls the maximum size of its communication buffer. To set the max_allowed_packet variable for mysql to a value of 16MB, use either of the following commands:

shell> mysql --max_allowed_packet=16777216
shell> mysql --max_allowed_packet=16M

The first command specifies the value in bytes. The second specifies the value in megabytes. For variables that take a numeric value, the value can be given with a suffix of K, M, or G (either uppercase or lowercase) to indicate a multiplier of 1024, 10242 or 10243. (For example, when used to set max_allowed_packet, the suffixes indicate units of kilobytes, megabytes, or gigabytes.)

In an option file, variable settings are given without the leading dashes:

[mysql]
max_allowed_packet=16777216

Or:

[mysql]
max_allowed_packet=16M

If you like, underscores in a variable name can be specified as dashes. The following option groups are equivalent. Both set the size of the server's key buffer to 512MB:

[mysqld]
key_buffer_size=512M

[mysqld]
key-buffer-size=512M

Note: Before MySQL 4.0.2, the only syntax for setting program variables was --set-variable= option = value (or set-variable= option = value in option files). This syntax still is recognized, but is deprecated as of MySQL 4.0.2.

Many server system variables can also be set at runtime. For details, see Section 4.2.3.2, "Dynamic System Variables."

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