Chapter 4 MySQL Programs


内容表

4.1 Overview of MySQL Programs
4.2 Using MySQL Programs
4.2.1 Invoking MySQL Programs
4.2.2 Connecting to the MySQL Server
4.2.3 Connecting Using a URI or Key-Value Pairs
4.2.4 Specifying Program Options
4.2.5 Using Options on the Command Line
4.2.6 Program Option Modifiers
4.2.7 Using Option Files
4.2.8 Command-Line Options that Affect Option-File Handling
4.2.9 Using Options to Set Program Variables
4.2.10 Option Defaults, Options Expecting Values, and the = Sign
4.2.11 Setting Environment Variables
4.3 MySQL Server and Server-Startup Programs
4.3.1 mysqld — The MySQL Server
4.3.2 mysqld_safe — MySQL Server Startup Script
4.3.3 mysql.server — MySQL Server Startup Script
4.3.4 mysqld_multi — Manage Multiple MySQL Servers
4.4 MySQL Installation-Related Programs
4.4.1 comp_err — Compile MySQL Error Message File
4.4.2 mysql_secure_installation — Improve MySQL Installation Security
4.4.3 mysql_ssl_rsa_setup — Create SSL/RSA Files
4.4.4 mysql_tzinfo_to_sql — Load the Time Zone Tables
4.4.5 mysql_upgrade — Check and Upgrade MySQL Tables
4.5 MySQL Client Programs
4.5.1 mysql — The MySQL Command-Line Client
4.5.2 mysqladmin — Client for Administering a MySQL Server
4.5.3 mysqlcheck — A Table Maintenance Program
4.5.4 mysqldump — A Database Backup Program
4.5.5 mysqlimport — A Data Import Program
4.5.6 mysqlpump — A Database Backup Program
4.5.7 mysqlshow — Display Database, Table, and Column Information
4.5.8 mysqlslap — Load Emulation Client
4.6 MySQL Administrative and Utility Programs
4.6.1 ibd2sdi — InnoDB Tablespace SDI Extraction Utility
4.6.2 innochecksum — Offline InnoDB File Checksum Utility
4.6.3 myisam_ftdump — Display Full-Text Index information
4.6.4 myisamchk — MyISAM Table-Maintenance Utility
4.6.5 myisamlog — Display MyISAM Log File Contents
4.6.6 myisampack — Generate Compressed, Read-Only MyISAM Tables
4.6.7 mysql_config_editor — MySQL Configuration Utility
4.6.8 mysqlbinlog — Utility for Processing Binary Log Files
4.6.9 mysqldumpslow — Summarize Slow Query Log Files
4.7 MySQL Program Development Utilities
4.7.1 mysql_config — Display Options for Compiling Clients
4.7.2 my_print_defaults — Display Options from Option Files
4.8 Miscellaneous Programs
4.8.1 lz4_decompress — Decompress mysqlpump LZ4-Compressed Output
4.8.2 perror — Display MySQL Error Message Information
4.8.3 zlib_decompress — Decompress mysqlpump ZLIB-Compressed Output
4.9 MySQL Program Environment Variables

This chapter provides a brief overview of the MySQL command-line programs provided by Oracle Corporation. It also discusses the general syntax for specifying options when you run these programs. Most programs have options that are specific to their own operation, but the option syntax is similar for all of them. Finally, the chapter provides more detailed descriptions of individual programs, including which options they recognize.

4.1 Overview of MySQL Programs

There are many different programs in a MySQL installation. This section provides a brief overview of them. Later sections provide a more detailed description of each one, with the exception of NDB Cluster programs. Each program's description indicates its invocation syntax and the options that it supports. Section 22.4, “NDB Cluster Programs” , describes programs specific to NDB Cluster.

Most MySQL distributions include all of these programs, except for those programs that are platform-specific. (For example, the server startup scripts are not used on Windows.) The exception is that RPM distributions are more specialized. There is one RPM for the server, another for client programs, and so forth. If you appear to be missing one or more programs, see Chapter 2, Installing and Upgrading MySQL , for information on types of distributions and what they contain. It may be that you have a distribution that does not include all programs and you need to install an additional package.

Each MySQL program takes many different options. Most programs provide a --help option that you can use to get a description of the program's different options. For example, try mysql --help .

You can override default option values for MySQL programs by specifying options on the command line or in an option file. See Section 4.2, “Using MySQL Programs” , for general information on invoking programs and specifying program options.

The MySQL server, mysqld , is the main program that does most of the work in a MySQL installation. The server is accompanied by several related scripts that assist you in starting and stopping the server:

Several programs perform setup operations during MySQL installation or upgrading:

MySQL client programs that connect to the MySQL server:

MySQL administrative and utility programs:

MySQL program-development utilities:

Miscellaneous utilities:

Oracle Corporation also provides the MySQL Workbench GUI tool, which is used to administer MySQL servers and databases, to create, execute, and evaluate queries, and to migrate schemas and data from other relational database management systems for use with MySQL. Additional GUI tools include MySQL Notifier and MySQL for Excel .

MySQL client programs that communicate with the server using the MySQL client/server library use the following environment variables.

Environment Variable Meaning
MYSQL_UNIX_PORT The default Unix socket file; used for connections to localhost
MYSQL_TCP_PORT The default port number; used for TCP/IP connections
MYSQL_PWD The default password
MYSQL_DEBUG Debug trace options when debugging
TMPDIR The directory where temporary tables and files are created

For a full list of environment variables used by MySQL programs, see Section 4.9, “MySQL Program Environment Variables” .

Use of MYSQL_PWD is insecure. See Section 6.1.2.1, “End-User Guidelines for Password Security” .

4.2 Using MySQL Programs

4.2.1 Invoking MySQL Programs
4.2.2 Connecting to the MySQL Server
4.2.3 Connecting Using a URI or Key-Value Pairs
4.2.4 Specifying Program Options
4.2.5 Using Options on the Command Line
4.2.6 Program Option Modifiers
4.2.7 Using Option Files
4.2.8 Command-Line Options that Affect Option-File Handling
4.2.9 Using Options to Set Program Variables
4.2.10 Option Defaults, Options Expecting Values, and the = Sign
4.2.11 Setting Environment Variables

4.2.1 Invoking MySQL Programs

To invoke a MySQL program from the command line (that is, from your shell or command prompt), enter the program name followed by any options or other arguments needed to instruct the program what you want it to do. The following commands show some sample program invocations. shell> represents the prompt for your command interpreter; it is not part of what you type. The particular prompt you see depends on your command interpreter. Typical prompts are $ for sh , ksh , or bash , % for csh or tcsh , and C:\> for the Windows command.com or cmd.exe command interpreters.

shell> mysql --user=root test
shell> mysqladmin extended-status variables
shell> mysqlshow --help
shell> mysqldump -u root personnel
                        

Arguments that begin with a single or double dash ( - , -- ) specify program options. Options typically indicate the type of connection a program should make to the server or affect its operational mode. Option syntax is described in Section 4.2.4, “Specifying Program Options” .

Nonoption arguments (arguments with no leading dash) provide additional information to the program. For example, the mysql program interprets the first nonoption argument as a database name, so the command mysql --user=root test indicates that you want to use the test database.

Later sections that describe individual programs indicate which options a program supports and describe the meaning of any additional nonoption arguments.

Some options are common to a number of programs. The most frequently used of these are the --host (or -h ), --user (or -u ), and --password (or -p ) options that specify connection parameters. They indicate the host where the MySQL server is running, and the user name and password of your MySQL account. All MySQL client programs understand these options; they enable you to specify which server to connect to and the account to use on that server. Other connection options are --port (or -P ) to specify a TCP/IP port number and --socket (or -S ) to specify a Unix socket file on Unix (or named pipe name on Windows). For more information on options that specify connection options, see Section 4.2.2, “Connecting to the MySQL Server” .

You may find it necessary to invoke MySQL programs using the path name to the bin directory in which they are installed. This is likely to be the case if you get a program not found error whenever you attempt to run a MySQL program from any directory other than the bin directory. To make it more convenient to use MySQL, you can add the path name of the bin directory to your PATH environment variable setting. That enables you to run a program by typing only its name, not its entire path name. For example, if mysql is installed in /usr/local/mysql/bin , you can run the program by invoking it as mysql , and it is not necessary to invoke it as /usr/local/mysql/bin/mysql .

Consult the documentation for your command interpreter for instructions on setting your PATH variable. The syntax for setting environment variables is interpreter-specific. (Some information is given in Section 4.2.11, “Setting Environment Variables” .) After modifying your PATH setting, open a new console window on Windows or log in again on Unix so that the setting goes into effect.

4.2.2 Connecting to the MySQL Server

This section describes how to establish a connection to the MySQL server. For additional information if you are unable to connect, see Section 6.2.9, “Troubleshooting Problems Connecting to MySQL” .

For a client program to be able to connect to the MySQL server, it must use the proper connection parameters, such as the name of the host where the server is running and the user name and password of your MySQL account. Each connection parameter has a default value, but you can override them as necessary using program options specified either on the command line or in an option file.

The examples here use the mysql client program, but the principles apply to other clients such as mysqldump , mysqladmin , or mysqlshow . For more information on connecting clients such as MySQL Shell by specifying a path, see Section 4.2.3, “Connecting Using a URI or Key-Value Pairs” .

This command invokes mysql without specifying any connection parameters explicitly:

shell> mysql
                        

Because there are no parameter options, the default values apply:

  • The default host name is localhost . On Unix, this has a special meaning, as described later.

  • The default user name is ODBC on Windows or your Unix login name on Unix.

  • No password is sent if neither -p nor --password is given.

  • For mysql , the first nonoption argument is taken as the name of the default database. If there is no such option, mysql does not select a default database.

To specify the host name and user name explicitly, as well as a password, supply appropriate options on the command line:

shell> mysql --host=localhost --user=myname --password=password mydb
shell> mysql -h localhost -u myname -ppassword mydb
                        

For password options, the password value is optional:

  • If you use a -p or --password option and specify the password value, there must be no space between -p or --password= and the password following it.

  • If you use a -p or --password option but do not specify the password value, the client program prompts you to enter the password. The password is not displayed as you enter it. This is more secure than giving the password on the command line. Other users on your system may be able to see a password specified on the command line by executing a command such as ps auxw . See Section 6.1.2.1, “End-User Guidelines for Password Security” .

As just mentioned, including the password value on the command line can be a security risk. To avoid this problem, specify the --password or -p option without any following password value:

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb
                        

When the password option has no password value, the client program prints a prompt and waits for you to enter the password. (In these examples, mydb is not interpreted as a password because it is separated from the preceding password option by a space.)

On some systems, the library routine that MySQL uses to prompt for a password automatically limits the password to eight characters. That is a problem with the system library, not with MySQL. Internally, MySQL does not have any limit for the length of the password. To work around the problem, change your MySQL password to a value that is eight or fewer characters long, or put your password in an option file.

On Unix, MySQL programs treat the host name localhost specially, in a way that is likely different from what you expect compared to other network-based programs.

Clients determine what type of connection to make as follows:

  • If the host is not specified or is localhost , a connection to the local host is assumed:

    • On Windows, the client connects using a shared-memory connection, if the server has shared-memory connections enabled.

    • On Unix, the client connects using a Unix socket file. The --socket option or the MYSQL_UNIX_PORT environment variable may be used to specify the socket name.

  • On Windows, if host is . , or TCP/IP is not enabled and --socket is not specified or the host is empty, the client connects using a named pipe, if the server has named-pipe connections enabled. If named-pipe connections are not enabled or if the user making the connection is not a member of the Windows group specified by the named_pipe_full_access_group server system variable, an error occurs.

  • Otherwise, TCP/IP is used.

The --protocol option enables you to establish a particular type of connection even when the other options would normally default to some other protocol. That is, --protocol may be given to specify the connection protocol explicitly and override the preceding rules, even for localhost .

Only connection options that are relevant to the selected protocol are used or checked. Other connection options are ignored. For example, with --host=localhost on Unix, the client attempts to connect to the local server using a Unix socket file. This occurs even if a --port or -P option is given to specify a port number.

To ensure that the client makes a TCP/IP connection to the local server, use --host or -h to specify a host name value of 127.0.0.1 , or the IP address or name of the local server. You can also specify the connection protocol explicitly, even for localhost , by using the --protocol=TCP option. For example:

shell> mysql --host=127.0.0.1
shell> mysql --protocol=TCP
                        

If the server is configured to accept IPv6 connections, clients can connect over IPv6 using --host=::1 . See Section 5.1.12, “IPv6 Support” .

On Windows, you can force a MySQL client to use a named-pipe connection by specifying the --pipe or --protocol=PIPE option, or by specifying . (period) as the host name. If named-pipe connections are not enabled or if the user making the connection is not a member of the Windows group specified by the named_pipe_full_access_group server system variable, an error occurs. Use the --socket option to specify the name of the pipe if you do not want to use the default pipe name.

Connections to remote servers always use TCP/IP. This command connects to the server running on remote.example.com using the default port number (3306):

shell> mysql --host=remote.example.com
                        

To specify a port number explicitly, use the --port or -P option:

shell> mysql --host=remote.example.com --port=13306
                        

You can specify a port number for connections to a local server, too. However, as indicated previously, connections to localhost on Unix will use a socket file by default. You will need to force a TCP/IP connection as already described or any option that specifies a port number will be ignored.

For this command, the program uses a socket file on Unix and the --port option is ignored:

shell> mysql --port=13306 --host=localhost
                        

To cause the port number to be used, invoke the program in either of these ways:

shell> mysql --port=13306 --host=127.0.0.1
shell> mysql --port=13306 --protocol=TCP
                        

The following list summarizes the options that can be used to control how client programs connect to the server:

  • --default-auth= plugin

    A hint about the client-side authentication plugin to use. See Section 6.3.10, “Pluggable Authentication” .

  • --host= host_name , -h host_name

    The host where the server is running. The default value is localhost .

  • --password[= pass_val ] , -p[ pass_val ]

    The password of the MySQL account. As described earlier, the password value is optional, but if given, there must be no space between -p or --password= and the password following it. The default is to send no password.

  • --pipe , -W

    On Windows, connect to the server using a named pipe. The server must be started with the --enable-named-pipe option to enable named-pipe connections. In addition, the user making the connection must be a member of the Windows group specified by the named_pipe_full_access_group server system variable.

  • --port= port_num , -P port_num

    The port number to use for the connection, for connections made using TCP/IP. The default port number is 3306.

  • --protocol={TCP|SOCKET|PIPE|MEMORY}

    This option explicitly specifies a protocol to use for connecting to the server. It is useful when the other connection parameters normally would cause a protocol to be used other than the one you want. For example, connections on Unix to localhost are made using a Unix socket file by default:

    shell> mysql --host=localhost
                                        

    To force a TCP/IP connection to be used instead, specify a --protocol option:

    shell> mysql --host=localhost --protocol=TCP
                                        

    The following table shows the permissible --protocol option values and indicates the platforms on which each value may be used. The values are not case-sensitive.

    --protocol Connection Protocol Permissible Operating Systems
    TCP TCP/IP connection to local or remote server All
    SOCKET Unix socket file connection to local server Unix only
    PIPE Named-pipe connection to local or remote server Windows only
    MEMORY Shared-memory connection to local server Windows only
  • --shared-memory-base-name= name

    On Windows, the shared-memory name to use, for connections made using shared memory to a local server. The default value is MYSQL . The shared-memory name is case sensitive.

    The server must be started with the --shared-memory option to enable shared-memory connections.

  • --socket= file_name , -S file_name

    On Unix, the name of the Unix socket file to use, for connections made using a named pipe to a local server. The default Unix socket file name is /tmp/mysql.sock .

    On Windows, the name of the named pipe to use, for connections to a local server. The default Windows pipe name is MySQL . The pipe name is not case-sensitive.

    The server must be started with the --enable-named-pipe option to enable named-pipe connections. In addition, the user making the connection must be a member of the Windows group specified by the named_pipe_full_access_group server system variable.

  • --ssl*

    Options that begin with --ssl are used for establishing a secure connection to the server using SSL, if the server is configured with SSL support. For details, see Section 6.4.2, “Command Options for Encrypted Connections” .

  • --tls-version= protocol_list

    The protocols the client permits for encrypted connections. The value is a list of one or more comma-separated protocol names. The protocols that can be named for this option depend on the SSL library used to compile MySQL. For details, see Section 6.4.6, “Encrypted Connection Protocols and Ciphers” .

  • --user= user_name , -u user_name

    The user name of the MySQL account you want to use. The default user name is ODBC on Windows or your Unix login name on Unix.

It is possible to specify different default values to be used when you make a connection so that you need not enter them on the command line each time you invoke a client program. This can be done in a couple of ways:

4.2.3 Connecting Using a URI or Key-Value Pairs

In addition to specifying the connection parameters to an instance of MySQL server documented at Section 4.2.2, “Connecting to the MySQL Server” , you can specify the connection using a URI-type string or key-value pairs. The following MySQL clients support specifying the connection to MySQL server instance using a URI-type string or key-value pairs:

  • MySQL Shell

  • MySQL Router

  • MySQL Connectors which implement X DevAPI

Many of the parameters you use to specify a connection in this way are similar to those used with the command options, and this section documents all of the valid parameters. The connection's parameters can be specified as:

The connection parameters are not case sensitive and can only be defined once. If a parameter is defined more than once, an error is generated.

This section consists of:

Base Connection Parameters

This section describes the parameters available when specifying a connection to MySQL. The following parameters can be provided as either a URI type string or in a key-value pairs. In a URI type string they conform to the base URI, see Connecting using a URI String . Alternatively they can be specified as key-value pairs, see Connecting using Key-value Pairs .

  • scheme : specifies the connection protocol to use. Use mysqlx for X Protocol connections and mysql for classic MySQL protocol connections. If no protocol is specified, the server attempts to guess the protocol.

  • user : specifies the MySQL user account to be used for the authentication process.

  • password : specifies the password to be used for the authentication process.

    Warning

    Storing the password in the connection is insecure and not recommended.

  • host : specifies the server instance the connection refers to. Can be either an IPv4 address, an IPv6 address or a hostname. If not specified, localhost is used by default.

  • port : specifies a network port which the target MySQL server is listening on for connections. If not specified, 33060 is used by default for X Protocol connections, and 3306 is the default for classic MySQL protocol connections.

  • socket : path to a Unix socket or Windows named-pipe. Values are local file paths and must be encoded in URI type strings, using percent encoding or surrounding the path with parentheses, which removes the need to percent encode characters such as the common directory separator / . To connect as root@localhost using the Unix socket /tmp/mysqld.sock either specify the path using parenthesis, for example root@localhost?socket=(/tmp/mysqld.sock) , or using percent encoding, for example root@localhost?socket=%2Ftmp%2Fmysqld.sock .

  • schema : specifies the database to be set as default when the connection is established.

Connection Options

You can specify options for the connection, either as part of a URI type string by appending ? attribute=value , or as key-value pairs. The following options are available:

  • ssl-mode : the SSL mode to be used for the connection. The following values are valid:

    • DISABLED

    • PREFERRED

    • REQUIRED

    • VERIFY_CA

    • VERIFY_IDENTITY

  • ssl-ca : the path to the X.509 certificate authority in PEM format.

  • ssl-capath : the path to the directory that contains the X.509 certificates authorities in PEM format.

  • ssl-cert : The path to the X.509 certificate in PEM format.

  • ssl-key : The path to the X.509 key in PEM format.

  • ssl-crl : The path to file that contains certificate revocation lists.

  • ssl-crlpath : The path to the directory that contains certificate revocation list files.

  • ssl-cipher : the SSL cipher to use.

  • tls-version : TLS version permitted for secure connections. The following values are valid:

    • TLSv1

    • TLSv1.1

    • TLSv1.2 (Supported only by commercial edition)

  • auth-method : Authentication method used for the connection. Defaults to AUTO , meaning that the server attempts to guess. The following values are valid:

    • AUTO

    • MYSQL41

    • SHA256_MEMORY

    • FROM_CAPABILITIES

    • FALLBACK

    • PLAIN

    When using an X Protocol connection, any configured auth-method is overridden to this sequence of authentication methods: MYSQL41 , SHA256_MEMORY , PLAIN .

  • get-server-public-key : Request public key from the server required for RSA key pair-based password exchange. Use when connecting to MySQL 8.0 servers over classic MySQL protocol with SSL mode DISABLED . You must specify the protocol in this case, for example:

    mysql://user@localhost:3306?get-server-key=true
                                            
  • server-public-key-path : The path name to a file containing a client-side copy of the public key required by the server for RSA key pair-based password exchange. Use when connecting to MySQL 8.0 servers over classic MySQL protocol with SSL mode DISABLED .

  • connect-timeout : an integer value used to configure the number of seconds clients, such as MySQL Shell, wait until the client stops trying to connect to an unresponsive MySQL server.

  • compression : When set to true (or 1), this option enables compression of all information sent between the client and the server if both support compression. The default is no compression ( false or 0). This option is available for MySQL Shell connections using classic MySQL protocol only.

Connecting using a URI String

You can specify a connection to MySQL Server using a URI type string format. Such strings can be used with the MySQL Shell with the --uri command option, the MySQL Shell \connect command, MySQL Connectors which implement X DevAPI, and tools such as MySQL Router.

A URI type string has the following format:

[scheme://][user[:[password]]@]target[:port][/schema][?attribute1=value1&attribute2=value2...
                            
Important

Percent encoding must be used for reserved characters in the elements of the URI type string. For example, if you specify a string that includes the @ character, the character must be replaced by %40 . If you include a zone ID in an IPv6 address, the % character used as the separator must be replaced with %25 .

The parameters you can use in a URI type string for a connection are described at Base Connection Parameters .

If no password is specified using the URI type string, which is recommended, then the password is prompted for. The following examples show how to specify URI type strings with the user name user , in each case the password is prompted for:

  • A classic MySQL protocol connection to a local server instance listening at port 3333.

    mysql://user@localhost:3333
                                            
  • An X Protocol connection to a local server instance listening at port 33065.

    mysqlx://user@localhost:33065
                                            
  • An X Protocol connection to a remote server instance, using a host name, an IPv4 address and an IPv6 address.

    mysqlx://user@server.example.com/
    mysqlx://user@198.51.100.14:123
    mysqlx://user@[2001:db8:85a3:8d3:1319:8a2e:370:7348]
                                            
  • An X Protocol connection using a socket, with the path either provided using percent encoding or parenthesis.

    mysqlx://user@/path%2Fto%2Fsocket.sock
    mysqlx://user@(/path/to/socket.sock)
                                            
  • An optional path can be specified, which represents a database schema.

    mysqlx://user@198.51.100.1/world%5Fx
    mysqlx://user@198.51.100.2:33060/world
                                            
  • An optional query can be specified, consisting of values in the form of a key=value pair or as a single key . The , character is used as a separator for values, a combination of multiple pairs and keys can be specified. Values can be of type list, list values are ordered by appearance. Strings must be either percent encoded or surrounded by parenthesis. The following are equivalent.

    ssluser@127.0.0.1?ssl-ca=%2Froot%2Fclientcert%2Fca-cert.pem\
    &ssl-cert=%2Froot%2Fclientcert%2Fclient-cert.pem\
    &ssl-key=%2Froot%2Fclientcert%2Fclient-key
    ssluser@127.0.0.1?ssl-ca=(/root/clientcert/ca-cert.pem)\
    &ssl-cert=(/root/clientcert/client-cert.pem)\
    &ssl-key=(/root/clientcert/client-key)
                                            

The previous examples assume that connections require a password, and with interactive clients the specified user's password is requested at the login prompt. If the user has a password-less account, which is insecure and not recommended, or if socket peer-credential authentication is in use (for example with Unix socket connections), you must explicitly specify in the URI type string that no password is being provided and the password prompt is not required. To do this, place a : after the user in the URI type string but do not specify a password after it. For example:

mysqlx://user:@localhost
                            

Connecting using Key-value Pairs

You can specify a connection to MySQL Server using key-value pairs. These key-value pairs are supplied in language natural constructs for the implementation. This means you can supply connection parameters using key-value pairs as a JSON object in JavaScript, or using key-value pairs in a dictionary in Python. Regardless of the way the key-value pairs are supplied, the concept remains the same - the keys as specified in this section can be assigned values that are used to specify a connection. You can specify connections using key-value pairs in MySQL Shell's shell.connect() method or InnoDB cluster's dba.createCluster() method, and with some of the MySQL Connectors which implement X DevAPI.

Generally, key-value pairs are surrounded by { and } characters and the , character is used as a separator between key-value pairs. The : character is used between keys and values, and strings must be delimited, for example using the ' character. It is not necessary to percent encode strings, unlike URI type strings.

A connection specified as key-value pairs has the following format:

{ key: value, key: value, ...}
                            

The parameters you can use as keys for a connection are described at Base Connection Parameters .

If no password is specified, which is recommended, then in interactive clients the password is prompted for. The following examples show how to specify connections using key-value pairs with the user name user :

  • An X Protocol connection to a local server instance listening at port 33065.

    {user:'user', host:'localhost', port:33065}
                                            
  • A classic MySQL protocol connection to a local server instance listening at port 3333.

    {user:'user', host:'localhost', port:3333}
                                            
  • An X Protocol connection to a remote server instance, using a host name, an IPv4 address and an IPv6 address.

    {user:'user', host:'server.example.com'}
    {user:'user', host:198.51.100.14:123}
    {user:'user', host:[2001:db8:85a3:8d3:1319:8a2e:370:7348]}
                                            
  • An X Protocol connection using a socket.

    {user:'user', socket:'/path/to/socket/file/'}
                                            
  • An optional schema can be specified, which represents a database.

    {user:'user', host:'localhost', schema:'world'}
                                            

The previous examples assume that connections require a password, and with interactive clients the specified user's password is requested at the login prompt. If the user has a password-less account, which is insecure and not recommended, or if socket peer-credential authentication is in use (for example with Unix socket connections), you must explicitly specify that no password is being provided and the password prompt is not required. To do this, provide an empty string using '' after the password key. For example:

{user:'user', password:'', host:'localhost'}
                            

4.2.4 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 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 (see Section 4.2.11, “Setting 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, but Section 5.8.3, “Running Multiple MySQL Instances 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 the server and for client programs.

Options are processed in order, so if an option is specified multiple times, the last occurrence takes precedence. The following command causes mysql to connect to the server running on localhost :

shell> mysql -h example.com -h localhost
                        

If conflicting or related options are given, later options take precedence over earlier options. The following command runs mysql in no column names mode:

shell> mysql --column-names --skip-column-names
                        

MySQL programs determine which options are given first by examining environment variables, then by processing option files, and then by checking the command line. This means that environment variables have the lowest precedence and command-line options the highest.

For the server, one exception applies: The mysqld-auto.cnf option file in the data directory is processed last, so it takes precedence even over command-line options.

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

4.2.5 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 is a short form or long form of the option name. Many options have both short and long forms. For example, -? and --help are the short and long forms of the option that instructs a MySQL program to display its 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.

  • Within option names, dash ( - ) and underscore ( _ ) may be used interchangeably. For example, --skip-grant-tables and --skip_grant_tables are equivalent. (However, the leading dashes cannot be given as underscores.)

  • For options that take a numeric value, the value can be given with a suffix of K , M , or G to indicate a multiplier of 1024, 1024 2 or 1024 3 . As of MySQL 8.0.14, a suffix can also be T , P , and E to indicate a multiplier of 1024 4 , 1024 5 or 1024 6 . Suffix letters can be uppercase or lowercase.

    For example, the following command tells mysqladmin to ping the server 1024 times, sleeping 10 seconds between each ping:

    shell> mysqladmin --count=1K --sleep=10 ping
                                        
  • When specifying file names as option values, avoid the use of the ~ shell metacharacter because it might not be interpreted as you expect.

Option values that contain spaces must be quoted when given on the command line. For example, the --execute (or -e ) option can be used with mysql to pass SQL statements to the server. When this option is used, mysql executes the statements in the option value and exits. The statements must be enclosed by quotation marks. 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 mysql.user"
Enter password: ******
+------+-----------+
| User | Host      |
+------+-----------+
|      | gigan     |
| root | gigan     |
|      | localhost |
| jon  | localhost |
| root | localhost |
+------+-----------+
shell>
                        
Note

The long form ( --execute ) is followed by an equals sign ( = ).

If you wish to use quoted values within a statement, you will either need to escape the inner quotation marks, or use a different type of quotation marks within the statement from those used to quote the statement itself. The capabilities of your command processor dictate your choices for whether you can use single or double quotation marks and the syntax for escaping quote characters. For example, if your command processor supports quoting with single or double quotation marks, you can use double quotation marks around the statement, and single quotation marks for any quoted values within the statement.

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

shell> mysql -u root -p -e "SELECT VERSION();SELECT NOW()"
Enter password: ******
+------------+
| VERSION()  |
+------------+
| 8.0.11     |
+------------+
+---------------------+
| NOW()               |
+---------------------+
| 2018-08-05 20:00:20 |
+---------------------+
                        

4.2.6 Program Option Modifiers

Some options are boolean and 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
                        

The values ON , TRUE , OFF , and FALSE are also recognized for boolean options (not case-sensitive).

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

The --maximum prefix is available for mysqld only and permits a limit to be placed on how large client programs can set session system variables. To do this, use a --maximum prefix with the variable name. For example, --maximum-max_heap_table_size=32M prevents any client from making the heap table size limit larger than 32M.

The --maximum prefix is intended for use with system variables that have a session value. If applied to a system variable that has only a global value, an error occurs. For example, with --maximum-back_log=200 , the server produces this error:

Maximum value of 'back_log' cannot be set
                        

4.2.7 Using Option Files

Most MySQL programs can read startup options from option files (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. (For mysqld , use --verbose and --help .) If the program reads option files, the help message indicates which files it looks for and which option groups it recognizes.

Note

A MySQL program started with the --no-defaults option reads no option files other than .mylogin.cnf .

A server started with the persisted_globals_load system variable disabled does not read mysqld-auto.cnf .

Many option files are plain text files, created using any text editor. The exceptions are:

MySQL looks for option files in the order described in the following discussion and reads any that exist. If an option file you want to use does not exist, create it using the appropriate method, as just discussed.

Note

For information about option files used with NDB Cluster programs, see Section 22.3, “Configuration of NDB Cluster” .

On Windows, MySQL programs read startup options from the files shown in the following table, in the specified order (files listed first are read first, files read later take precedence).

Table 4.1 Option Files Read on Windows Systems

File Name Purpose
%WINDIR% \my.ini , %WINDIR% \my.cnf Global options
C:\my.ini , C:\my.cnf Global options
BASEDIR \my.ini , BASEDIR \my.cnf Global options
defaults-extra-file The file specified with --defaults-extra-file , if any
%APPDATA% \MySQL\.mylogin.cnf Login path options (clients only)
DATADIR \mysqld-auto.cnf System variables persisted with SET PERSIST or SET PERSIST_ONLY (server only)

In the preceding table, %WINDIR% represents the location of your Windows directory. This is commonly C:\WINDOWS . Use the following command to determine its exact location from the value of the WINDIR environment variable:

C:\> echo %WINDIR%
                        

%APPDATA% represents the value of the Windows application data directory. Use the following command to determine its exact location from the value of the APPDATA environment variable:

C:\> echo %APPDATA%
                        

BASEDIR represents the MySQL base installation directory. When MySQL 8.0 has been installed using MySQL Installer, this is typically C:\ PROGRAMDIR \MySQL\MySQL 8.0 Server where PROGRAMDIR represents the programs directory (usually Program Files on English-language versions of Windows), See Section 2.3.3, “MySQL Installer for Windows” .

DATADIR represents the MySQL data directory. As used to find mysqld-auto.cnf , its default value is the data directory location built in when MySQL was compiled, but can be changed by --datadir specified as an option-file or command-line option processed before mysqld-auto.cnf is processed.

On Unix and Unix-like systems, MySQL programs read startup options from the files shown in the following table, in the specified order (files listed first are read first, files read later take precedence).

Note

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

Table 4.2 Option Files Read on Unix and Unix-Like Systems

File Name Purpose
/etc/my.cnf Global options
/etc/mysql/my.cnf Global options
SYSCONFDIR /my.cnf Global options
$MYSQL_HOME/my.cnf Server-specific options (server only)
defaults-extra-file The file specified with --defaults-extra-file , if any
~/.my.cnf User-specific options
~/.mylogin.cnf User-specific login path options (clients only)
DATADIR /mysqld-auto.cnf System variables persisted with SET PERSIST or SE PERSIST_ONLY (server only)

In the preceding table, ~ represents the current user's home directory (the value of $HOME ).

SYSCONFDIR represents the directory specified with the SYSCONFDIR option to CMake when MySQL was built. By default, this is the etc directory located under the compiled-in installation directory.

MYSQL_HOME is an environment variable containing the path to the directory in which the server-specific my.cnf file resides. If MYSQL_HOME is not set and you start the server using the mysqld_safe program, mysqld_safe sets it to BASEDIR , the MySQL base installation directory.

DATADIR represents the MySQL data directory. As used to find mysqld-auto.cnf , its default value is the data directory location built in when MySQL was compiled, but can be changed by --datadir specified as an option-file or command-line option processed before mysqld-auto.cnf is processed.

If multiple instances of a given option are found, the last instance takes precedence, with 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.

The following description of option file syntax applies to files that you edit manually. This excludes .mylogin.cnf , which is created using mysql_config_editor and is encrypted, and mysqld-auto.cnf , which the server creates in JSON format.

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. (For mysqld , use --verbose and --help .)

The syntax for specifying options in an option file is similar to command-line syntax (see Section 4.2.5, “Using Options on the Command Line” ). However, in an option file, you omit the leading two dashes from the option name and you specify only one option per line. For example, --quick and --host=localhost on the command line should be specified as quick and host=localhost on separate lines 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. Nonempty 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. Option group names are not case-sensitive.

  • 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. The value optionally can be enclosed within single quotation marks or double quotation marks, which is useful if the value contains a # comment character.

Leading and trailing spaces are automatically deleted from option names and values.

You can 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. In option files, these escaping rules apply:

  • A backslash followed by a valid escape sequence character is converted to the character represented by the sequence. For example, \s is converted to a space.

  • A backslash not followed by a valid escape sequence character remains unchanged. For example, \S is retained as is.

The preceding rules mean that a literal backslash can be given as \\ , or as \ if it is not followed by a valid escape sequence character.

The rules for escape sequences in option files differ slightly from the rules for escape sequences in string literals in SQL statements. In the latter context, if x is not a valid escape sequence character, \ x becomes x rather than \ x . See Section 9.1.1, “String Literals” .

The escaping rules for option file values are especially pertinent for Windows path names, which use \ as a path name separator. A separator in a Windows path name must be written as \\ if it is followed by an escape sequence character. It can be written as \\ or \ if it is not. Alternatively, / may be used in Windows path names and will be treated as \ . Suppose that you want to specify a base directory of C:\Program Files\MySQL\MySQL Server 8.0 in an option file. This can be done several ways. Some examples:

basedir="C:\Program Files\MySQL\MySQL Server 8.0"
basedir="C:\\Program Files\\MySQL\\MySQL Server 8.0"
basedir="C:/Program Files/MySQL/MySQL Server 8.0"
basedir=C:\\Program\sFiles\\MySQL\\MySQL\sServer\s8.0
                        

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 provided in MySQL distributions (but not by mysqld ). To understand how third-party client programs that use the C API can use option files, see the C API documentation at Section 28.7.7.50, “mysql_options()” .

The [client] group enables you to specify options that apply to all clients. For example, [client] is the appropriate group to use to specify the password for connecting to the server. (But make sure that the option file is accessible only by yourself, so that other people cannot discover 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.

List more general option groups first and more specific groups later. For example, a [client] group is more general because it is read by all client programs, whereas a [mysqldump] group is read only by mysqldump . Options specified later override options specified earlier, so putting the option groups in the order [client] , [mysqldump] enables mysqldump -specific options to override [client] options.

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=128M
[mysqldump]
quick
                        

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
                        

To create option groups to be read only by mysqld servers from specific MySQL release series, use groups with names of [mysqld-5.7] , [mysqld-8.0] , and so forth. The following group indicates that the sql_mode setting should be used only by MySQL servers with 8.0.x version numbers:

[mysqld-8.0]
sql_mode=TRADITIONAL
                        

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, use the following directive:

!include /home/mydir/myopt.cnf
                        

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

!includedir /home/mydir
                        

MySQL makes no guarantee about the order in which option files in the directory will be read.

Note

Any files to be found and included using the !includedir directive on Unix operating systems must have file names ending in .cnf . On Windows, this directive checks for files with the .ini or .cnf extension.

Write the contents of an included option file like any other option file. That is, it should contain groups of options, each preceded by a [ group ] line that indicates the program to which the options apply.

While an included file is being processed, only those options in groups that the current program is looking for are used. Other groups are ignored. Suppose that a my.cnf file contains this line:

!include /home/mydir/myopt.cnf
                        

And suppose that /home/mydir/myopt.cnf looks like this:

[mysqladmin]
force
[mysqld]
key_buffer_size=16M
                        

If my.cnf is processed by mysqld , only the [mysqld] group in /home/mydir/myopt.cnf is used. If the file is processed by mysqladmin , only the [mysqladmin] group is used. If the file is processed by any other program, no options in /home/mydir/myopt.cnf are used.

The !includedir directive is processed similarly except that all option files in the named directory are read.

If an option file contains !include or !includedir directives, files named by those directives are processed whenever the option file is processed, no matter where they appear in the file.

4.2.8 Command-Line Options that Affect Option-File Handling

Most MySQL programs that support option files handle the following options. Because these options affect option-file handling, they must be given on the command line and not in an option file. To work properly, each of these options must be given before other options, with these exceptions:

When specifying file names as option values, avoid the use of the ~ shell metacharacter because it might not be interpreted as you expect.

  • --defaults-extra-file= file_name

    Read this option file after the global option file but (on Unix) before the user option file and (on all platforms) before the login path file. (For information about the order in which option files are used, see Section 4.2.7, “Using Option Files” .) If the file does not exist or is otherwise inaccessible, an error occurs. file_name is interpreted relative to the current directory if given as a relative path name rather than a full path name.

    See the introduction to this section regarding constraints on the position in which this option may be specified.

  • --defaults-file= file_name

    Read only the given option file. If the file does not exist or is otherwise inaccessible, an error occurs. file_name is interpreted relative to the current directory if given as a relative path name rather than a full path name.

    Exceptions: Even with --defaults-file , mysqld reads mysqld-auto.cnf and client programs read .mylogin.cnf .

    See the introduction to this section regarding constraints on the position in which this option may be specified.

  • --defaults-group-suffix= str

    Read not only the usual option groups, but also groups with the usual names and a suffix of str . For example, the mysql client normally reads the [client] and [mysql] groups. If the --defaults-group-suffix=_other option is given, mysql also reads the [client_other] and [mysql_other] groups.

  • --login-path= name

    Read options from the named login path in the .mylogin.cnf login path file. A login path is an option group containing options that specify which MySQL server to connect to and which account to authenticate as. To create or modify a login path file, use the mysql_config_editor utility. See Section 4.6.7, “ mysql_config_editor — MySQL Configuration Utility” .

    A client program reads the option group corresponding to the named login path, in addition to option groups that the program reads by default. Consider this command:

    shell> mysql --login-path=mypath
                                        

    By default, the mysql client reads the [client] and [mysql] option groups. So for the command shown, mysql reads [client] and [mysql] from other option files, and [client] , [mysql] , and [mypath] from the login path file.

    Client programs read the login path file even when the --no-defaults option is used.

    To specify an alternate login path file name, set the MYSQL_TEST_LOGIN_FILE environment variable.

    See the introduction to this section regarding constraints on the position in which this option may be specified.

  • --no-defaults

    Do not read any option files. If program startup fails due to reading unknown options from an option file, --no-defaults can be used to prevent them from being read.

    The exception is that client programs read the .mylogin.cnf login path file, if it exists, even when --no-defaults is used. This permits passwords to be specified in a safer way than on the command line even if --no-defaults is present. ( .mylogin.cnf is created by the mysql_config_editor utility. See Section 4.6.7, “ mysql_config_editor — MySQL Configuration Utility” .)

  • --print-defaults

    Print the program name and all options that it gets from option files. Password values are masked.

    See the introduction to this section regarding constraints on the position in which this option may be specified.

4.2.9 Using Options to Set Program Variables

Many MySQL programs have internal variables that can be set at runtime using the SET statement. See Section 13.7.5.1, “SET Syntax for Variable Assignment” , and Section 5.1.9, “Using System Variables” .

Most of these program variables also can be set at server startup by using the same syntax that applies to specifying program options. 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 to indicate a multiplier of 1024, 1024 2 or 1024 3 . (For example, when used to set max_allowed_packet , the suffixes indicate units of kilobytes, megabytes, or gigabytes.) As of MySQL 8.0.14, a suffix can also be T , P , and E to indicate a multiplier of 1024 4 , 1024 5 or 1024 6 . Suffix letters can be uppercase or lowercase.

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
                        

A variable can be specified by writing it in full or as any unambiguous prefix. For example, the max_allowed_packet variable can be set for mysql as --max_a , but not as --max because the latter is ambiguous:

shell> mysql --max=1000000
mysql: ambiguous option '--max=1000000' (max_allowed_packet, max_join_size)
                        

Be aware that the use of variable prefixes can cause problems in the event that new variables are implemented for a program. A prefix that is unambiguous now might become ambiguous in the future.

Suffixes for specifying a value multiplier can be used when setting a variable at server startup, but not to set the value with SET at runtime. On the other hand, with SET , you can assign a variable's value using an expression, which is not true when you set a variable at server startup. For example, the first of the following lines is legal at server startup, but the second is not:

shell> mysql --max_allowed_packet=16M
shell> mysql --max_allowed_packet=16*1024*1024
                        

Conversely, the second of the following lines is legal at runtime, but the first is not:

mysql> SET GLOBAL max_allowed_packet=16M;
mysql> SET GLOBAL max_allowed_packet=16*1024*1024;
                        

4.2.10 Option Defaults, Options Expecting Values, and the = Sign

By convention, long forms of options that assign a value are written with an equals ( = ) sign, like this:

shell> mysql --host=tonfisk --user=jon
                        

For options that require a value (that is, not having a default value), the equals sign is not required, and so the following is also valid:

shell> mysql --host tonfisk --user jon
                        

In both cases, the mysql client attempts to connect to a MySQL server running on the host named tonfisk using an account with the user name jon .

Due to this behavior, problems can occasionally arise when no value is provided for an option that expects one. Consider the following example, where a user connects to a MySQL server running on host tonfisk as user jon :

shell> mysql --host 85.224.35.45 --user jon
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 8.0.17 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| jon@%          |
+----------------+
1 row in set (0.00 sec)
                        

Omitting the required value for one of these option yields an error, such as the one shown here:

shell> mysql --host 85.224.35.45 --user
mysql: option '--user' requires an argument
                        

In this case, mysql was unable to find a value following the --user option because nothing came after it on the command line. However, if you omit the value for an option that is not the last option to be used, you obtain a different error that you may not be expecting:

shell> mysql --host --user jon
ERROR 2005 (HY000): Unknown MySQL server host '--user' (1)
                        

Because mysql assumes that any string following --host on the command line is a host name, --host --user is interpreted as --host=--user , and the client attempts to connect to a MySQL server running on a host named --user .

Options having default values always require an equals sign when assigning a value; failing to do so causes an error. For example, the MySQL server --log-error option has the default value host_name .err , where host_name is the name of the host on which MySQL is running. Assume that you are running MySQL on a computer whose host name is tonfisk , and consider the following invocation of mysqld_safe :

shell> mysqld_safe &
[1] 11699
shell> 080112 12:53:40 mysqld_safe Logging to '/usr/local/mysql/var/tonfisk.err'.
080112 12:53:40 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var
shell>
                        

After shutting down the server, restart it as follows:

shell> mysqld_safe --log-error &
[1] 11699
shell> 080112 12:53:40 mysqld_safe Logging to '/usr/local/mysql/var/tonfisk.err'.
080112 12:53:40 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var
shell>
                        

The result is the same, since --log-error is not followed by anything else on the command line, and it supplies its own default value. (The & character tells the operating system to run MySQL in the background; it is ignored by MySQL itself.) Now suppose that you wish to log errors to a file named my-errors.err . You might try starting the server with --log-error my-errors , but this does not have the intended effect, as shown here:

shell> mysqld_safe --log-error my-errors &
[1] 31357
shell> 080111 22:53:31 mysqld_safe Logging to '/usr/local/mysql/var/tonfisk.err'.
080111 22:53:32 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var
080111 22:53:34 mysqld_safe mysqld from pid file /usr/local/mysql/var/tonfisk.pid ended
[1]+  Done                    ./mysqld_safe --log-error my-errors
                        

The server attempted to start using /usr/local/mysql/var/tonfisk.err as the error log, but then shut down. Examining the last few lines of this file shows the reason:

shell> tail /usr/local/mysql/var/tonfisk.err
2013-09-24T15:36:22.278034Z 0 [ERROR] Too many arguments (first extra is 'my-errors').
2013-09-24T15:36:22.278059Z 0 [Note] Use --verbose --help to get a list of available options!
2013-09-24T15:36:22.278076Z 0 [ERROR] Aborting
2013-09-24T15:36:22.279704Z 0 [Note] InnoDB: Starting shutdown...
2013-09-24T15:36:23.777471Z 0 [Note] InnoDB: Shutdown completed; log sequence number 2319086
2013-09-24T15:36:23.780134Z 0 [Note] mysqld: Shutdown complete
                        

Because the --log-error option supplies a default value, you must use an equals sign to assign a different value to it, as shown here:

shell> mysqld_safe --log-error=my-errors &
[1] 31437
shell> 080111 22:54:15 mysqld_safe Logging to '/usr/local/mysql/var/my-errors.err'.
080111 22:54:15 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var
shell>
                        

Now the server has been started successfully, and is logging errors to the file /usr/local/mysql/var/my-errors.err .

Similar issues can arise when specifying option values in option files. For example, consider a my.cnf file that contains the following:

[mysql]
host
user
                        

When the mysql client reads this file, these entries are parsed as --host --user or --host=--user , with the result shown here:

shell> mysql
ERROR 2005 (HY000): Unknown MySQL server host '--user' (1)
                        

However, in option files, an equals sign is not assumed. Suppose the my.cnf file is as shown here:

[mysql]
user jon
                        

Trying to start mysql in this case causes a different error:

shell> mysql
mysql: unknown option '--user jon'
                        

A similar error would occur if you were to write host tonfisk in the option file rather than host=tonfisk . Instead, you must use the equals sign:

[mysql]
user=jon
                        

Now the login attempt succeeds:

shell> mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 8.0.17 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SELECT USER();
+---------------+
| USER()        |
+---------------+
| jon@localhost |
+---------------+
1 row in set (0.00 sec)
                        

This is not the same behavior as with the command line, where the equals sign is not required:

shell> mysql --user jon --host tonfisk
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 8.0.17 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SELECT USER();
+---------------+
| USER()        |
+---------------+
| jon@tonfisk   |
+---------------+
1 row in set (0.00 sec)
                        

Specifying an option requiring a value without a value in an option file causes the server to abort with an error. Suppose that my.cnf contains the following:

[mysqld]
log_error
relay_log
relay_log_index
                        

This causes the server to fail on startup, as shown here:

shell> mysqld_safe &
130924 10:41:46 mysqld_safe Logging to '/home/jon/bin/mysql/var/tonfisk.err'.
130924 10:41:46 mysqld_safe Starting mysqld daemon with databases from /home/jon/bin/mysql/var
130924 10:41:47 mysqld_safe mysqld from pid file /home/jon/bin/mysql/var/tonfisk.pid ended
                        

The --log-error option does not require an argument; however, the --relay-log option requires one, as shown in the error log (which in the absence of a specified value, defaults to datadir / hostname .err ):

shell> tail -n 3 ../var/tonfisk.err
130924 10:41:46 mysqld_safe Starting mysqld daemon with databases from /home/jon/bin/mysql/var
2013-09-24T15:41:47.217180Z 0 [ERROR] /home/jon/bin/mysql/libexec/mysqld: option '--relay-log' requires an argument
2013-09-24T15:41:47.217479Z 0 [ERROR] Aborting
                        

This is a change from previous behavior, where the server would have interpreted the last two lines in the example my.cnf file as --relay-log=relay_log_index and created a relay log file using relay_log_index as the base name. (Bug #25192)

4.2.11 Setting Environment Variables

Environment variables can be set at the command prompt to affect the current invocation of your command processor, or set permanently to affect future invocations. To set a variable permanently, you can set it in a startup file or by using the interface provided by your system for this purpose. Consult the documentation for your command interpreter for specific details. Section 4.9, “MySQL Program Environment Variables” , lists all environment variables that affect MySQL program operation.

To specify a value for an environment variable, use the syntax appropriate for your command processor. For example, on Windows, 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 , ksh , 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, use the interface provided by your system or place the appropriate command or commands in a startup file that your command interpreter reads each time it starts.

On Windows, you can set environment variables using the System Control Panel (under Advanced).

On Unix, typical shell startup files are .bashrc or .bash_profile for bash , or .tcshrc for tcsh .

Suppose that your MySQL programs are installed in /usr/local/mysql/bin and that you want to make it easy to invoke these programs. To do this, set the value of the PATH environment variable to include that directory. For example, if your shell is bash , add the following line to your .bashrc file:

PATH=${PATH}:/usr/local/mysql/bin
                        

bash uses different startup files for login and nonlogin shells, so you might want to add the setting to .bashrc for login shells and to .bash_profile for nonlogin shells to make sure that PATH is set regardless.

If your shell is tcsh , add the following line to your .tcshrc file:

setenv PATH ${PATH}:/usr/local/mysql/bin
                        

If the appropriate startup file does not exist in your home directory, create it with a text editor.

After modifying your PATH setting, open a new console window on Windows or log in again on Unix so that the setting goes into effect.

4.3 MySQL Server and Server-Startup Programs

This section describes mysqld , the MySQL server, and several programs that are used to start the server.

4.3.1 mysqld — The MySQL Server

mysqld , also known as MySQL Server, is the main program that does most of the work in a MySQL installation. MySQL Server manages access to the MySQL data directory that contains databases and tables. The data directory is also the default location for other information such as log files and status files.

Note

Some installation packages contain a debugging version of the server named mysqld-debug . Invoke this version instead of mysqld for debugging support, memory allocation checking, and trace file support (see Section 29.5.1.2, “Creating Trace Files” ).

When MySQL server starts, it listens for network connections from client programs and manages access to databases on behalf of those clients.

The mysqld program has many options that can be specified at startup. For a complete list of options, run this command:

shell> mysqld --verbose --help
                    

MySQL Server also has a set of system variables that affect its operation as it runs. System variables can be set at server startup, and many of them can be changed at runtime to effect dynamic server reconfiguration. MySQL Server also has a set of status variables that provide information about its operation. You can monitor these status variables to access runtime performance characteristics.

For a full description of MySQL Server command options, system variables, and status variables, see Section 5.1, “The MySQL Server” . For information about installing MySQL and setting up the initial configuration, see Chapter 2, Installing and Upgrading MySQL .

4.3.2 mysqld_safe — MySQL Server Startup Script

mysqld_safe is the recommended way to start a mysqld server on Unix. mysqld_safe adds some safety features such as restarting the server when an error occurs and logging runtime information to an error log. A description of error logging is given later in this section.

Note

For some Linux platforms, MySQL installation from RPM or Debian packages includes systemd support for managing MySQL server startup and shutdown. On these platforms, mysqld_safe is not installed because it is unnecessary. For more information, see Section 2.5.9, “Managing MySQL Server with systemd” .

mysqld_safe tries to start an executable named mysqld . To override the default behavior and specify explicitly the name of the server you want to run, specify a --mysqld or --mysqld-version option to mysqld_safe . You can also use --ledir to indicate the directory where mysqld_safe should look for the server.

Many of the options to mysqld_safe are the same as the options to mysqld . See Section 5.1.7, “Server Command Options” .

Options unknown to mysqld_safe are passed to mysqld if they are specified on the command line, but ignored if they are specified in the [mysqld_safe] group of an option file. See Section 4.2.7, “Using Option Files” .

mysqld_safe reads all options from the [mysqld] , [server] , and [mysqld_safe] sections in option files. For example, if you specify a [mysqld] section like this, mysqld_safe will find and use the --log-error option:

[mysqld]
log-error=error.log
                    

For backward compatibility, mysqld_safe also reads [safe_mysqld] sections, but to be current you should rename such sections to [mysqld_safe] .

mysqld_safe accepts options on the command line and in option files, as described in the following table. For information about option files used by MySQL programs, see Section 4.2.7, “Using Option Files” .

Table 4.3 mysqld_safe Options

Format 描述
--basedir Path to MySQL installation directory
--core-file-size Size of core file that mysqld should be able to create
--datadir Path to data directory
--defaults-extra-file Read named option file in addition to usual option files
--defaults-file Read only named option file
--help Display help message and exit
--ledir Path to directory where server is located
--log-error Write error log to named file
--malloc-lib Alternative malloc library to use for mysqld
--mysqld Name of server program to start (in ledir directory)
--mysqld-safe-log-timestamps Timestamp format for logging
--mysqld-version Suffix for server program name
--nice Use nice program to set server scheduling priority
--no-defaults Read no option files
--open-files-limit Number of files that mysqld should be able to open
--pid-file Path name of server process ID file
--plugin-dir Directory where plugins are installed
--port Port number on which to listen for TCP/IP connections
--skip-kill-mysqld Do not try to kill stray mysqld processes
--skip-syslog Do not write error messages to syslog; use error log file
--socket Socket file on which to listen for Unix socket connections
--syslog Write error messages to syslog
--syslog-tag Tag suffix for messages written to syslog
--timezone Set TZ time zone environment variable to named value
--user Run mysqld as user having name user_name or numeric user ID user_id

If you execute mysqld_safe with the --defaults-file or --defaults-extra-file option to name an option file, the option must be the first one given on the command line or the option file will not be used. For example, this command will not use the named option file:

mysql> mysqld_safe --port=port_num --defaults-file=file_name
                    

Instead, use the following command:

mysql> mysqld_safe --defaults-file=file_name --port=port_num
                    

The mysqld_safe script is written so that it normally can start a server that was installed from either a source or a binary distribution of MySQL, even though these types of distributions typically install the server in slightly different locations. (See Section 2.1.4, “Installation Layouts” .) mysqld_safe expects one of the following conditions to be true:

  • The server and databases can be found relative to the working directory (the directory from which mysqld_safe is invoked). For binary distributions, mysqld_safe looks under its working directory for bin and data directories. For source distributions, it looks for libexec and var directories. This condition should be met if you execute mysqld_safe from your MySQL installation directory (for example, /usr/local/mysql for a binary distribution).

  • If the server and databases cannot be found relative to the working directory, mysqld_safe attempts to locate them by absolute path names. Typical locations are /usr/local/libexec and /usr/local/var . The actual locations are determined from the values configured into the distribution at the time it was built. They should be correct if MySQL is installed in the location specified at configuration time.

Because mysqld_safe tries to find the server and databases relative to its own working directory, you can install a binary distribution of MySQL anywhere, as long as you run mysqld_safe from the MySQL installation directory:

shell> cd mysql_installation_directory
shell> bin/mysqld_safe &
                    

If mysqld_safe fails, even when invoked from the MySQL installation directory, specify the --ledir and --datadir options to indicate the directories in which the server and databases are located on your system.

mysqld_safe tries to use the sleep and date system utilities to determine how many times per second it has attempted to start. If these utilities are present and the attempted starts per second is greater than 5, mysqld_safe waits 1 full second before starting again. This is intended to prevent excessive CPU usage in the event of repeated failures. (Bug #11761530, Bug #54035)

When you use mysqld_safe to start mysqld , mysqld_safe arranges for error (and notice) messages from itself and from mysqld to go to the same destination.

There are several mysqld_safe options for controlling the destination of these messages:

  • --log-error= file_name : Write error messages to the named error file.

  • --syslog : Write error messages to syslog on systems that support the logger program.

  • --skip-syslog : Do not write error messages to syslog . Messages are written to the default error log file ( host_name .err in the data directory), or to a named file if the --log-error option is given.

If none of these options is given, the default is --skip-syslog .

When mysqld_safe writes a message, notices go to the logging destination ( syslog or the error log file) and stdout . Errors go to the logging destination and stderr .

Note

Controlling mysqld logging from mysqld_safe is deprecated. Use the server's native syslog support instead. For more information, see Section 5.4.2.3, “Error Logging to the System Log” .

4.3.3 mysql.server — MySQL Server Startup Script

MySQL distributions on Unix and Unix-like system include a script named mysql.server , which starts the MySQL server using mysqld_safe . It can be used on systems such as Linux and Solaris that use System V-style run directories to start and stop system services. It is also used by the macOS Startup Item for MySQL.

mysql.server is the script name as used within the MySQL source tree. The installed name might be different; for example, mysqld or mysql . In the following discussion, adjust the name mysql.server as appropriate for your system.

Note

For some Linux platforms, MySQL installation from RPM or Debian packages includes systemd support for managing MySQL server startup and shutdown. On these platforms, mysql.server and mysqld_safe are not installed because they are unnecessary. For more information, see Section 2.5.9, “Managing MySQL Server with systemd” .

To start or stop the server manually using the mysql.server script, invoke it from the command line with start or stop arguments:

shell> mysql.server start
shell> mysql.server stop
                    

mysql.server changes location to the MySQL installation directory, then invokes mysqld_safe . To run the server as some specific user, add an appropriate user option to the [mysqld] group of the global /etc/my.cnf option file, as shown later in this section. (It is possible that you must edit mysql.server if you've installed a binary distribution of MySQL in a nonstandard location. Modify it to change location into the proper directory before it runs mysqld_safe . If you do this, your modified version of mysql.server may be overwritten if you upgrade MySQL in the future; make a copy of your edited version that you can reinstall.)

mysql.server stop stops the server by sending a signal to it. You can also stop the server manually by executing mysqladmin shutdown .

To start and stop MySQL automatically on your server, you must add start and stop commands to the appropriate places in your /etc/rc* files:

  • If you use the Linux server RPM package ( MySQL-server- VERSION .rpm ), or a native Linux package installation, the mysql.server script may be installed in the /etc/init.d directory with the name mysqld or mysql . See Section 2.5.4, “Installing MySQL on Linux Using RPM Packages from Oracle” , for more information on the Linux RPM packages.

  • If you install MySQL from a source distribution or using a binary distribution format that does not install mysql.server automatically, you can install the script manually. It can be found in the support-files directory under the MySQL installation directory or in a MySQL source tree. Copy the script to the /etc/init.d directory with the name mysql and make it executable:

    shell> cp mysql.server /etc/init.d/mysql
    shell> chmod +x /etc/init.d/mysql
                                    

    After installing the script, the commands needed to activate it to run at system startup depend on your operating system. On Linux, you can use chkconfig :

    shell> chkconfig --add mysql
                                    

    On some Linux systems, the following command also seems to be necessary to fully enable the mysql script:

    shell> chkconfig --level 345 mysql on
                                    
  • On FreeBSD, startup scripts generally should go in /usr/local/etc/rc.d/ . Install the mysql.server script as /usr/local/etc/rc.d/mysql.server.sh to enable automatic startup. The rc(8) manual page states that scripts in this directory are executed only if their base name matches the *.sh shell file name pattern. Any other files or directories present within the directory are silently ignored.

  • As an alternative to the preceding setup, some operating systems also use /etc/rc.local or /etc/init.d/boot.local to start additional services on startup. To start up MySQL using this method, append a command like the one following to the appropriate startup file:

    /bin/sh -c 'cd /usr/local/mysql; ./bin/mysqld_safe --user=mysql &'
                                    
  • For other systems, consult your operating system documentation to see how to install startup scripts.

mysql.server reads options from the [mysql.server] and [mysqld] sections of option files. For backward compatibility, it also reads [mysql_server] sections, but to be current you should rename such sections to [mysql.server] .

You can add options for mysql.server in a global /etc/my.cnf file. A typical my.cnf file might look like this:

[mysqld]
datadir=/usr/local/mysql/var
socket=/var/tmp/mysql.sock
port=3306
user=mysql
[mysql.server]
basedir=/usr/local/mysql
                    

The mysql.server script supports the options shown in the following table. If specified, they must be placed in an option file, not on the command line. mysql.server supports only start and stop as command-line arguments.

Table 4.4 mysql.server Option-File Options

Option Name 描述 Type
basedir Path to MySQL installation directory Directory name
datadir Path to MySQL data directory Directory name
pid-file File in which server should write its process ID File name
service-startup-timeout How long to wait for server startup Integer

  • basedir= dir_name

    The path to the MySQL installation directory.

  • datadir= dir_name

    The path to the MySQL data directory.

  • pid-file= file_name

    The path name of the file in which the server should write its process ID. The server creates the file in the data directory unless an absolute path name is given to specify a different directory.

    If this option is not given, mysql.server uses a default value of host_name .pid . The PID file value passed to mysqld_safe overrides any value specified in the [mysqld_safe] option file group. Because mysql.server reads the [mysqld] option file group but not the [mysqld_safe] group, you can ensure that mysqld_safe gets the same value when invoked from mysql.server as when invoked manually by putting the same pid-file setting in both the [mysqld_safe] and [mysqld] groups.

  • service-startup-timeout= seconds

    How long in seconds to wait for confirmation of server startup. If the server does not start within this time, mysql.server exits with an error. The default value is 900. A value of 0 means not to wait at all for startup. Negative values mean to wait forever (no timeout).

4.3.4 mysqld_multi — Manage Multiple MySQL Servers

mysqld_multi is designed to manage several mysqld processes that listen for connections on different Unix socket files and TCP/IP ports. It can start or stop servers, or report their current status.

Note

For some Linux platforms, MySQL installation from RPM or Debian packages includes systemd support for managing MySQL server startup and shutdown. On these platforms, mysqld_multi is not installed because it is unnecessary. For information about using systemd to handle multiple MySQL instances, see Section 2.5.9, “Managing MySQL Server with systemd” .

mysqld_multi searches for groups named [mysqld N ] in my.cnf (or in the file named by the --defaults-file option). N can be any positive integer. This number is referred to in the following discussion as the option group number, or GNR . Group numbers distinguish option groups from one another and are used as arguments to mysqld_multi to specify which servers you want to start, stop, or obtain a status report for. Options listed in these groups are the same that you would use in the [mysqld] group used for starting mysqld . (See, for example, Section 2.10.5, “Starting and Stopping MySQL Automatically” .) However, when using multiple servers, it is necessary that each one use its own value for options such as the Unix socket file and TCP/IP port number. For more information on which options must be unique per server in a multiple-server environment, see Section 5.8, “Running Multiple MySQL Instances on One Machine” .

To invoke mysqld_multi , use the following syntax:

shell> mysqld_multi [options] {start|stop|reload|report} [GNR[,GNR] ...]
                    

start , stop , reload (stop and restart), and report indicate which operation to perform. You can perform the designated operation for a single server or multiple servers, depending on the GNR list that follows the option name. If there is no list, mysqld_multi performs the operation for all servers in the option file.

Each GNR value represents an option group number or range of group numbers. The value should be the number at the end of the group name in the option file. For example, the GNR for a group named [mysqld17] is 17 . To specify a range of numbers, separate the first and last numbers by a dash. The GNR value 10-13 represents groups [mysqld10] through [mysqld13] . Multiple groups or group ranges can be specified on the command line, separated by commas. There must be no whitespace characters (spaces or tabs) in the GNR list; anything after a whitespace character is ignored.

This command starts a single server using option group [mysqld17] :

shell> mysqld_multi start 17
                    

This command stops several servers, using option groups [mysqld8] and [mysqld10] through [mysqld13] :

shell> mysqld_multi stop 8,10-13
                    

For an example of how you might set up an option file, use this command:

shell> mysqld_multi --example
                    

mysqld_multi searches for option files as follows:

For additional information about these and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling” .

Option files read are searched for [mysqld_multi] and [mysqld N ] option groups. The [mysqld_multi] group can be used for options to mysqld_multi itself. [mysqld N ] groups can be used for options passed to specific mysqld instances.

The [mysqld] or [mysqld_safe] groups can be used for common options read by all instances of mysqld or mysqld_safe . You can specify a --defaults-file= file_name option to use a different configuration file for that instance, in which case the [mysqld] or [mysqld_safe] groups from that file will be used for that instance.

mysqld_multi supports the following options.

  • --help

    Display a help message and exit.

  • --example

    Display a sample option file.

  • --log= file_name

    Specify the name of the log file. If the file exists, log output is appended to it.

  • --mysqladmin= prog_name

    The mysqladmin binary to be used to stop servers.

  • --mysqld= prog_name

    The mysqld binary to be used. Note that you can specify mysqld_safe as the value for this option also. If you use mysqld_safe to start the server, you can include the mysqld or ledir options in the corresponding [mysqld N ] option group. These options indicate the name of the server that mysqld_safe should start and the path name of the directory where the server is located. (See the descriptions for these options in Section 4.3.2, “ mysqld_safe — MySQL Server Startup Script” .) Example:

    [mysqld38]
    mysqld = mysqld-debug
    ledir  = /opt/local/mysql/libexec
                                    
  • --no-log

    Print log information to stdout rather than to the log file. By default, output goes to the log file.

  • --password= password

    The password of the MySQL account to use when invoking mysqladmin . Note that the password value is not optional for this option, unlike for other MySQL programs.

  • --silent

    Silent mode; disable warnings.

  • --tcp-ip

    Connect to each MySQL server through the TCP/IP port instead of the Unix socket file. (If a socket file is missing, the server might still be running, but accessible only through the TCP/IP port.) By default, connections are made using the Unix socket file. This option affects stop and report operations.

  • --user= user_name

    The user name of the MySQL account to use when invoking mysqladmin .

  • --verbose

    Be more verbose.

  • --version

    Display version information and exit.

Some notes about mysqld_multi :

  • Most important : Before using mysqld_multi be sure that you understand the meanings of the options that are passed to the mysqld servers and why you would want to have separate mysqld processes. Beware of the dangers of using multiple mysqld servers with the same data directory. Use separate data directories, unless you know what you are doing. Starting multiple servers with the same data directory does not give you extra performance in a threaded system. See Section 5.8, “Running Multiple MySQL Instances on One Machine” .

    Important

    Make sure that the data directory for each server is fully accessible to the Unix account that the specific mysqld process is started as. Do not use the Unix root account for this, unless you know what you are doing. See Section 6.1.5, “How to Run MySQL as a Normal User” .

  • Make sure that the MySQL account used for stopping the mysqld servers (with the mysqladmin program) has the same user name and password for each server. Also, make sure that the account has the SHUTDOWN privilege. If the servers that you want to manage have different user names or passwords for the administrative accounts, you might want to create an account on each server that has the same user name and password. For example, you might set up a common multi_admin account by executing the following commands for each server:

    shell> mysql -u root -S /tmp/mysql.sock -p
    Enter password:
    mysql> CREATE USER 'multi_admin'@'localhost' IDENTIFIED BY 'multipass';
    mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost';
                                    

    See Section 6.2, “The MySQL Access Privilege System” . You have to do this for each mysqld server. Change the connection parameters appropriately when connecting to each one. Note that the host name part of the account name must permit you to connect as multi_admin from the host where you want to run mysqld_multi .

  • The Unix socket file and the TCP/IP port number must be different for every mysqld . (Alternatively, if the host has multiple network addresses, you can use --bind-address to cause different servers to listen to different interfaces.)

  • The --pid-file option is very important if you are using mysqld_safe to start mysqld (for example, --mysqld=mysqld_safe ) Every mysqld should have its own process ID file. The advantage of using mysqld_safe instead of mysqld is that mysqld_safe monitors its mysqld process and restarts it if the process terminates due to a signal sent using kill -9 or for other reasons, such as a segmentation fault.

  • You might want to use the --user option for mysqld , but to do this you need to run the mysqld_multi script as the Unix superuser ( root ). Having the option in the option file doesn't matter; you just get a warning if you are not the superuser and the mysqld processes are started under your own Unix account.

The following example shows how you might set up an option file for use with mysqld_multi . The order in which the mysqld programs are started or stopped depends on the order in which they appear in the option file. Group numbers need not form an unbroken sequence. The first and fifth [mysqld N ] groups were intentionally omitted from the example to illustrate that you can have gaps in the option file. This gives you more flexibility.

# This is an example of a my.cnf file for mysqld_multi.
# Usually this file is located in home dir ~/.my.cnf or /etc/my.cnf
[mysqld_multi]
mysqld     = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user       = multi_admin
password   = my_password
[mysqld2]
socket     = /tmp/mysql.sock2
port       = 3307
pid-file   = /usr/local/mysql/data2/hostname.pid2
datadir    = /usr/local/mysql/data2
language   = /usr/local/mysql/share/mysql/english
user       = unix_user1
[mysqld3]
mysqld     = /path/to/mysqld_safe
ledir      = /path/to/mysqld-binary/
mysqladmin = /path/to/mysqladmin
socket     = /tmp/mysql.sock3
port       = 3308
pid-file   = /usr/local/mysql/data3/hostname.pid3
datadir    = /usr/local/mysql/data3
language   = /usr/local/mysql/share/mysql/swedish
user       = unix_user2
[mysqld4]
socket     = /tmp/mysql.sock4
port       = 3309
pid-file   = /usr/local/mysql/data4/hostname.pid4
datadir    = /usr/local/mysql/data4
language   = /usr/local/mysql/share/mysql/estonia
user       = unix_user3
[mysqld6]
socket     = /tmp/mysql.sock6
port       = 3311
pid-file   = /usr/local/mysql/data6/hostname.pid6
datadir    = /usr/local/mysql/data6
language   = /usr/local/mysql/share/mysql/japanese
user       = unix_user4
                    

See Section 4.2.7, “Using Option Files” .

4.4 MySQL Installation-Related Programs

The programs in this section are used when installing or upgrading MySQL.

4.4.1 comp_err — Compile MySQL Error Message File

comp_err creates the errmsg.sys file that is used by mysqld to determine the error messages to display for different error codes. comp_err normally is run automatically when MySQL is built. It compiles the errmsg.sys file from the text file located at sql/share/errmsg-utf8.txt in MySQL source distributions.

comp_err also generates mysqld_error.h , mysqld_ername.h , and sql_state.h header files.

For more information about how error messages are defined, see the MySQL Internals Manual .

Invoke comp_err like this:

shell> comp_err [options]
                    

comp_err supports the following options.

4.4.2 mysql_secure_installation — Improve MySQL Installation Security

This program enables you to improve the security of your MySQL installation in the following ways:

  • You can set a password for root accounts.

  • You can remove root accounts that are accessible from outside the local host.

  • You can remove anonymous-user accounts.

  • You can remove the test database (which by default can be accessed by all users, even anonymous users), and privileges that permit anyone to access databases with names that start with test_ .

mysql_secure_installation helps you implement security recommendations similar to those described at Section 2.10.4, “Securing the Initial MySQL Account” .

Normal usage is to connect to the local MySQL server; invoke mysql_secure_installation without arguments:

shell> mysql_secure_installation
                    

When executed, mysql_secure_installation prompts you to determine which actions to perform.

The validate_password component can be used for password strength checking. If the plugin is not installed, mysql_secure_installation prompts the user whether to install it. Any passwords entered later are checked using the plugin if it is enabled.

Most of the usual MySQL client options such as --host and --port can be used on the command line and in option files. For example, to connect to the local server over IPv6 using port 3307, use this command:

shell> mysql_secure_installation --host=::1 --port=3307
                    

mysql_secure_installation supports the following options, which can be specified on the command line or in the [mysql_secure_installation] and [client] groups of an option file. For information about option files used by MySQL programs, see Section 4.2.7, “Using Option Files” .

Table 4.5 mysql_secure_installation Options

Format 描述 Introduced
--defaults-extra-file Read named option file in addition to usual option files
--defaults-file Read only named option file
--defaults-group-suffix Option group suffix value
--help Display help message and exit
--host Host to connect to (IP address or host name)
--no-defaults Read no option files
--password Accepted but always ignored. Whenever mysql_secure_installation is invoked, the user is prompted for a password, regardless.
--port TCP/IP port number for connection
--print-defaults Print default options
--protocol Connection protocol to use
--socket For connections to localhost, the Unix socket file to use
--ssl-ca File that contains list of trusted SSL Certificate Authorities
--ssl-capath Directory that contains trusted SSL Certificate Authority certificate files
--ssl-cert File that contains X.509 certificate
--ssl-cipher List of permitted ciphers for connection encryption
--ssl-crl File that contains certificate revocation lists
--ssl-crlpath Directory that contains certificate revocation list files
--ssl-fips-mode Whether to enable FIPS mode on the client side 8.0.11
--ssl-key File that contains X.509 key
--tls-version Protocols permitted for encrypted connections
--use-default Execute with no user interactivity
--user MySQL user name to use when connecting to server

4.4.3 mysql_ssl_rsa_setup — Create SSL/RSA Files

This program creates the SSL certificate and key files and RSA key-pair files required to support secure connections using SSL and secure password exchange using RSA over unencrypted connections, if those files are missing. mysql_ssl_rsa_setup can also be used to create new SSL files if the existing ones have expired.

Note

mysql_ssl_rsa_setup uses the openssl command, so its use is contingent on having OpenSSL installed on your machine.

Another way to generate SSL and RSA files, for MySQL distributions compiled using OpenSSL, is to have the server generate them automatically. See Section 6.4.3.1, “Creating SSL and RSA Certificates and Keys using MySQL” .

Important

mysql_ssl_rsa_setup helps lower the barrier to using SSL by making it easier to generate the required files. However, certificates generated by mysql_ssl_rsa_setup are self-signed, which is not very secure. After you gain experience using the files created by mysql_ssl_rsa_setup , consider obtaining a CA certificate from a registered certificate authority.

Invoke mysql_ssl_rsa_setup like this:

shell> mysql_ssl_rsa_setup [options]
                    

Typical options are --datadir to specify where to create the files, and --verbose to see the openssl commands that mysql_ssl_rsa_setup executes.

mysql_ssl_rsa_setup attempts to create SSL and RSA files using a default set of file names. It works as follows:

  1. mysql_ssl_rsa_setup checks for the openssl binary at the locations specified by the PATH environment variable. If openssl is not found, mysql_ssl_rsa_setup does nothing. If openssl is present, mysql_ssl_rsa_setup looks for default SSL and RSA files in the MySQL data directory specified by the --datadir option, or the compiled-in data directory if the --datadir option is not given.

  2. mysql_ssl_rsa_setup checks the data directory for SSL files with the following names:

    ca.pem
    server-cert.pem
    server-key.pem
                                    
  3. If any of those files are present, mysql_ssl_rsa_setup creates no SSL files. Otherwise, it invokes openssl to create them, plus some additional files:

    ca.pem               Self-signed CA certificate
    ca-key.pem           CA private key
    server-cert.pem      Server certificate
    server-key.pem       Server private key
    client-cert.pem      Client certificate
    client-key.pem       Client private key
                                    

    These files enable secure client connections using SSL; see Section 6.4.1, “Configuring MySQL to Use Encrypted Connections” .

  4. mysql_ssl_rsa_setup checks the data directory for RSA files with the following names:

    private_key.pem      Private member of private/public key pair
    public_key.pem       Public member of private/public key pair
                                    
  5. If any of these files are present, mysql_ssl_rsa_setup creates no RSA files. Otherwise, it invokes openssl to create them. These files enable secure password exchange using RSA over unencrypted connections for accounts authenticated by the sha256_password or caching_sha2_password plugin; see Section 6.5.1.2, “SHA-256 Pluggable Authentication” , and Section 6.5.1.3, “Caching SHA-2 Pluggable Authentication” .

For information about the characteristics of files created by mysql_ssl_rsa_setup , see Section 6.4.3.1, “Creating SSL and RSA Certificates and Keys using MySQL” .

At startup, the MySQL server automatically uses the SSL files created by mysql_ssl_rsa_setup to enable SSL if no explicit SSL options are given other than --ssl (possibly along with --ssl-cipher ). If you prefer to designate the files explicitly, invoke clients with the --ssl-ca , --ssl-cert , and --ssl-key options at startup to name the ca.pem , server-cert.pem , and server-key.pem files, respectively.

The server also automatically uses the RSA files created by mysql_ssl_rsa_setup to enable RSA if no explicit RSA options are given.

If the server is SSL-enabled, clients use SSL by default for the connection. To specify certificate and key files explicitly, use the --ssl-ca , --ssl-cert , and --ssl-key options to name the ca.pem , client-cert.pem , and client-key.pem files, respectively. However, some additional client setup may be required first because mysql_ssl_rsa_setup by default creates those files in the data directory. The permissions for the data directory normally enable access only to the system account that runs the MySQL server, so client programs cannot use files located there. To make the files available, copy them to a directory that is readable (but not writable) by clients:

  • For local clients, the MySQL installation directory can be used. For example, if the data directory is a subdirectory of the installation directory and your current location is the data directory, you can copy the files like this:

    cp ca.pem client-cert.pem client-key.pem ..
                                    
  • For remote clients, distribute the files using a secure channel to ensure they are not tampered with during transit.

If the SSL files used for a MySQL installation have expired, you can use mysql_ssl_rsa_setup to create new ones:

  1. Stop the server.

  2. Rename or remove the existing SSL files. You may wish to make a backup of them first. (The RSA files do not expire, so you need not remove them. mysql_ssl_rsa_setup will see that they exist and not overwrite them.)

  3. Run mysql_ssl_rsa_setup with the --datadir option to specify where to create the new files.

  4. Restart the server.

mysql_ssl_rsa_setup supports the following command-line options, which can be specified on the command line or in the [mysql_ssl_rsa_setup] and [mysqld] groups of an option file. For information about option files used by MySQL programs, see Section 4.2.7, “Using Option Files” .

Table 4.6 mysql_ssl_rsa_setup Options

Format 描述
--datadir Path to data directory
--help Display help message and exit
--suffix Suffix for X.509 certificate Common Name attribute
--uid Name of effective user to use for file permissions
--verbose Verbose mode
--version Display version information and exit

  • --help , ?

    Display a help message and exit.

  • --datadir= dir_name

    The path to the directory that mysql_ssl_rsa_setup should check for default SSL and RSA files and in which it should create files if they are missing. The default is the compiled-in data directory.

  • --suffix= str

    The suffix for the Common Name attribute in X.509 certificates. The suffix value is limited to 17 characters. The default is based on the MySQL version number.

  • --uid=name , -v

    The name of the user who should be the owner of any created files. The value is a user name, not a numeric user ID. In the absence of this option, files created by mysql_ssl_rsa_setup are owned by the user who executes it. This option is valid only if you execute the program as root on a system that supports the chown() system call.

  • --verbose , -v

    Verbose mode. Produce more output about what the program does. For example, the program shows the openssl commands it runs, and produces output to indicate whether it skips SSL or RSA file creation because some default file already exists.

  • --version , -V

    Display version information and exit.

4.4.4 mysql_tzinfo_to_sql — Load the Time Zone Tables

The mysql_tzinfo_to_sql program loads the time zone tables in the mysql database. It is used on systems that have a zoneinfo database (the set of files describing time zones). Examples of such systems are Linux, FreeBSD, Solaris, and OS X. One likely location for these files is the /usr/share/zoneinfo directory ( /usr/share/lib/zoneinfo on Solaris). If your system does not have a zoneinfo database, you can use the downloadable package described in Section 5.1.13, “MySQL Server Time Zone Support” .

mysql_tzinfo_to_sql can be invoked several ways:

shell> mysql_tzinfo_to_sql tz_dir
shell> mysql_tzinfo_to_sql tz_file tz_name
shell> mysql_tzinfo_to_sql --leap tz_file
                    

For the first invocation syntax, pass the zoneinfo directory path name to mysql_tzinfo_to_sql and send the output into the mysql program. For example:

shell> mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
                    

mysql_tzinfo_to_sql reads your system's time zone files and generates SQL statements from them. mysql processes those statements to load the time zone tables.

The second syntax causes mysql_tzinfo_to_sql to load a single time zone file tz_file that corresponds to a time zone name tz_name :

shell> mysql_tzinfo_to_sql tz_file tz_name | mysql -u root mysql
                    

If your time zone needs to account for leap seconds, invoke mysql_tzinfo_to_sql using the third syntax, which initializes the leap second information. tz_file is the name of your time zone file:

shell> mysql_tzinfo_to_sql --leap tz_file | mysql -u root mysql
                    

After running mysql_tzinfo_to_sql , it is best to restart the server so that it does not continue to use any previously cached time zone data.

4.4.5 mysql_upgrade — Check and Upgrade MySQL Tables

mysql_upgrade examines all tables in all databases for incompatibilities with the current version of MySQL Server. mysql_upgrade also upgrades the system tables so that you can take advantage of new privileges or capabilities that might have been added.

If mysql_upgrade finds that a table has a possible incompatibility, it performs a table check and, if problems are found, attempts a table repair. If the table cannot be repaired, see Section 2.11.3, “Rebuilding or Repairing Tables or Indexes” for manual table repair strategies.

You should execute mysql_upgrade each time you upgrade MySQL.

mysql_upgrade communicates directly with the MySQL server, sending it the SQL statements required to perform an upgrade.

Caution

You should always back up your current MySQL installation before performing an upgrade. See Section 7.2, “Database Backup Methods” .

Some upgrade incompatibilities may require special handling before you upgrade your MySQL installation and run mysql_upgrade . See Section 2.11.1, “Upgrading MySQL” , for instructions on determining whether any such incompatibilities apply to your installation and how to handle them.

To use mysql_upgrade , make sure that the server is running. Then invoke it like this to check and repair tables and to upgrade the system tables:

shell> mysql_upgrade [options]
                    

After running mysql_upgrade , stop the server and restart it so that any changes made to the system tables take effect.

If you have multiple MySQL server instances running, invoke mysql_upgrade with connection parameters appropriate for connecting to the desired server. For example, with servers running on the local host on parts 3306 through 3308, upgrade each of them by connecting to the appropriate port:

shell> mysql_upgrade --protocol=tcp -P 3306 [other_options]
shell> mysql_upgrade --protocol=tcp -P 3307 [other_options]
shell> mysql_upgrade --protocol=tcp -P 3308 [other_options]
                    

For local host connections on Unix, the --protocol=tcp option forces a connection using TCP/IP rather than the Unix socket file.

Note

If you run the server with the disabled_storage_engines system variable set to disable certain storage engines (for example, MyISAM ), mysql_upgrade might fail with an error like this:

mysql_upgrade: [ERROR] 3161: Storage engine MyISAM is disabled
(Table creation is disallowed).
                        

To handle this, restart the server with disabled_storage_engines disabled. Then you should be able to run mysql_upgrade successfully. After that, restart the server with disabled_storage_engines set to its original value.

mysql_upgrade processes all tables in all databases, which might take a long time to complete. Each table is locked and therefore unavailable to other sessions while it is being processed. Check and repair operations can be time-consuming, particularly for large tables.

For details about what table-checking operations entail, see the description of the FOR UPGRADE option of the CHECK TABLE statement (see Section 13.7.3.2, “CHECK TABLE Syntax” ).

All checked and repaired tables are marked with the current MySQL version number. This ensures that next time you run mysql_upgrade with the same version of the server, it can tell whether there is any need to check or repair the table again.

mysql_upgrade also saves the MySQL version number in a file named mysql_upgrade_info in the data directory. This is used to quickly check whether all tables have been checked for this release so that table-checking can be skipped. To ignore this file and perform the check regardless, use the --force option.

mysql_upgrade checks user table rows and, for any row with an empty plugin column, sets that column to 'mysql_native_password' if the credentials use a hash format compatible with that plugin. Rows with a pre-4.1 password hash must be upgraded manually.

mysql_upgrade does not upgrade the contents of the help tables. For upgrade instructions, see Section 5.1.15, “Server-Side Help” .

Unless invoked with the --skip-sys-schema option, mysql_upgrade installs the sys schema if it is not installed, and upgrades it to the current version otherwise. mysql_upgrade returns an error if a sys schema exists but has no version view, on the assumption that its absence indicates a user-created schema:

Error occurred: A sys schema exists with no sys.version view. If
you have a user created sys schema, this must be renamed for the
upgrade to succeed.
                    

To upgrade in this case, remove or rename the existing sys schema first.

mysql_upgrade checks for partitioned InnoDB tables that were created using the generic partitioning handler and attempts to upgrade them to InnoDB native partitioning. You can upgrade such tables individually in the mysql client using the ALTER TABLE ... UPGRADE PARTITIONING SQL statement.

By default, mysql_upgrade runs as the MySQL root user. If the root password is expired when you run mysql_upgrade , you will see a message that your password is expired and that mysql_upgrade failed as a result. To correct this, reset the root password to unexpire it and run mysql_upgrade again. First, connect to the server as root :

shell> mysql -u root -p
Enter password: ****  <- enter root password here
                    

Reset the password using ALTER USER :

mysql> ALTER USER USER() IDENTIFIED BY 'root-password';
                    

Then exit mysql and run mysql_upgrade again:

shell> mysql_upgrade [options]
                    

mysql_upgrade supports the following options, which can be specified on the command line or in the [mysql_upgrade] and [client] groups of an option file. For information about option files used by MySQL programs, see Section 4.2.7, “Using Option Files” .

Table 4.7 mysql_upgrade Options

Format 描述 Introduced
--bind-address Use specified network interface to connect to MySQL Server
--character-sets-dir Directory where character sets are installed
--compress Compress all information sent between client and server
--debug Write debugging log
--debug-check Print debugging information when program exits
--debug-info Print debugging information, memory, and CPU statistics when program exits
--default-auth Authentication plugin to use
--default-character-set Specify default character set
--defaults-extra-file Read named option file in addition to usual option files
--defaults-file Read only named option file
--defaults-group-suffix Option group suffix value
--force Force execution even if mysql_upgrade has already been executed for current version of MySQL
--get-server-public-key Request RSA public key from server 8.0.3
--help Display help message and exit
--host Connect to MySQL server on given host
--login-path Read login path options from .mylogin.cnf
--max-allowed-packet Maximum packet length to send to or receive from server
--net-buffer-length Buffer size for TCP/IP and socket communication
--no-defaults Read no option files
--password Password to use when connecting to server
--pipe On Windows, connect to server using named pipe
--plugin-dir Directory where plugins are installed
--port TCP/IP port number for connection
--print-defaults Print default options
--protocol Connection protocol to use
--server-public-key-path Path name to file containing RSA public key 8.0.4
--shared-memory-base-name The name of shared memory to use for shared-memory connections
--skip-sys-schema Do not install or upgrade the sys schema
--socket For connections to localhost, the Unix socket file to use
--ssl-ca File that contains list of trusted SSL Certificate Authorities
--ssl-capath Directory that contains trusted SSL Certificate Authority certificate files
--ssl-cert File that contains X.509 certificate
--ssl-cipher List of permitted ciphers for connection encryption
--ssl-crl File that contains certificate revocation lists
--ssl-crlpath Directory that contains certificate revocation list files
--ssl-fips-mode Whether to enable FIPS mode on the client side 8.0.11
--ssl-key File that contains X.509 key
--ssl-mode Security state of connection to server
--tls-version Protocols permitted for encrypted connections
--upgrade-system-tables Update only system tables, not data
--user MySQL user name to use when connecting to server
--verbose Verbose mode
--version-check Check for proper server version
--write-binlog Write all statements to binary log

4.5 MySQL Client Programs

This section describes client programs that connect to the MySQL server.

4.5.1 mysql — The MySQL Command-Line Client

mysql is a simple SQL shell with input line editing capabilities. It supports interactive and noninteractive use. When used interactively, query results are presented in an ASCII-table format. When used noninteractively (for example, as a filter), the result is presented in tab-separated format. The output format can be changed using command options.

If you have problems due to insufficient memory for large result sets, use the --quick option. This forces mysql to retrieve results from the server a row at a time rather than retrieving the entire result set and buffering it in memory before displaying it. This is done by returning the result set using the mysql_use_result() C API function in the client/server library rather than mysql_store_result() .

Note

Alternatively, MySQL Shell offers access to the X DevAPI. For details, see MySQL Shell 8.0 (part of MySQL 8.0) .

Using mysql is very easy. Invoke it from the prompt of your command interpreter as follows:

shell> mysql db_name
                    

Or:

shell> mysql --user=user_name --password db_name
Enter password: your_password
                    

Then type an SQL statement, end it with ; , \g , or \G and press Enter.

Typing Control+C interrupts the current statement if there is one, or cancels any partial input line otherwise.

You can execute SQL statements in a script file (batch file) like this:

shell> mysql db_name < script.sql > output.tab
                    

On Unix, the mysql client logs statements executed interactively to a history file. See Section 4.5.1.3, “mysql Client Logging” .

4.5.1.1 mysql Client Options

mysql supports the following options, which can be specified on the command line or in the [mysql] and [client] groups of an option file. For information about option files used by MySQL programs