Chapter 2 Introduction

Table of Contents

2.1 Introduction to MySQL Utilities
2.2 Connecting to MySQL Servers
2.2.1 Connection Parameters
2.2.2 Specifying Connections in Python Library
2.2.3 Specifying User and Password for Replication Options

This chapter introduces MySQL Utilities and presents information on how to access and download MySQL Utilities. It also includes the basics of how to use the account login option common to all utilities.

2.1 Introduction to MySQL Utilities

What are the MySQL Utilities?

It is a package of utilities that are used for maintenance and administration of MySQL servers. These utilities encapsulate a set of primitive commands, and bundles them so they can be used to perform macro operations with a single command.

The utilities are written in Python, available under the GPLv2 license, and are extensible using the supplied library. They are designed to work with Python versions 2.6 or later and there is no support (yet) for Python v3.1.

How do we access the MySQL Utilities?

The MySQL Utilities are command line scripts, which by default are available in your system's PATH. Alternatively, if both MySQL Utilities and MySQL Workbench are installed, you can access their location from MySQL Workbench by selecting Tools from the main menu, and then Start Shell for MySQL Utilities. This opens a terminal/shell window in the mysqluc utility shell. Type "help" to list the available commands.

Figure 2.1 Starting MySQL Utilities from Workbench

Starting MySQL Utilities from Workbench

You can launch any of the utilities listed by typing the name of the command. To find out what options are available, use the option, or read the appropriate manual page.

The utilities are designed to work on MySQL systems with grants enabled but can also operate on servers started with the --skip-grant-tables startup option. However, this practice is strongly discouraged and should be used only in situations where it is appropriate or deemed a last resort.

2.2 Connecting to MySQL Servers

This section describes the ways you can connect to a MySQL server via a MySQL Utility or via the MySQL Utilities library methods.

2.2.1 Connection Parameters

To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

  • Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

  • Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

  • Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

2.2.1.1 Use login-paths (.mylogin.cnf)

The best way to specify server connection information is with your .mylogin.cnf file. Not only is this file encrypted, but any logging of the utility execution does not expose the connection information. Thus, no user names, passwords, ports, etc. are visible in the log. This is the preferred method for using MySQL Utilities to connect to servers.

Utilities support the use of login-paths in the connection string provided they use the following format login-path-name[:port][:socket] where the port and socket parameters are optional. If used, these optional parameters override the respective options from the specified login-path file.

When using login-paths, there are no default values except on Posix systems when specifying a socket. In this case, the host option defaults to localhost on port 3306. This means that combining the values specified in the login-path with the two optional values port and socket, one needs to specify at least a user, a hostname and a port or socket.

Use the mysql_config_editor tool (http://dev.mysql.com/doc/en/mysql-config-editor.html) to add the connection information as follows.

shell> mysql_config_editor set --login-path=instance_13001 --host=localhost --user=root --port=13001 --password
Enter password: <Password is prompted to be inserted in a more secure way>

Next, use the following command to confirm that the login-path data was correctly added to .mylogin.cnf (the encrypted file):

shell> mysql_config_editor print --login-path=instance_13001
[instance_13001]
user = root
password = *****
host = localhost
port = 13001

Once your .mylogin.cnf file is configured, you need only specify the section of the .mylogin.cnf file for the server connection. For example, the section created in the previous example is 'instance_13001'. Thus, we use --server=instance_13001. The following shows the execution of a utility specifying the login-path section.

shell> mysqlserverinfo --server=instance_13001 --format=vertical

# Source on localhost: ... connected.
*************************       1. row *************************
server: localhost:13001
config_file: /etc/my.cnf, /etc/mysql/my.cnf
binary_log: clone-bin.000001
binary_log_pos: 341
relay_log:
relay_log_pos:
version: 5.6.17-log
datadir: /Volumes/Source/source/temp_13001/
basedir: /Volumes/Source/source/bzr/mysql-5.6
plugin_dir: /Volumes/Source/source/bzr/mysql-5.6/lib/plugin/
general_log: OFF
general_log_file:
general_log_file_size:
log_error:
log_error_file_size:
slow_query_log: OFF
slow_query_log_file:
slow_query_log_file_size:
1 row.
#...done.

See the online MySQL Reference Manual for more information about login-paths, the .mylogin.cnf file, and the mysql_config_editor client.

Note

If you have an installation where the MySQL Server version is older (before 5.6.25 or 5.7.8) and my_print_defaults is newer, then the Utilities cannot access the passwords in the .login-path file because the newer versions of my_print_defaults mask the passwords, but older versions do not.

2.2.1.2 Use a Configuration File

MySQL Utilities can also accept a configuration path and section for the server connection data. This allows you to store one or more sections with connection information. Saving the data in configuration files is more secure than specifying the data on the command-line but since the file is text, the data can still be read by anyone who can access the file.

To reference the configuration file, specify the path and file name followed by a section name in square brackets. The path is optional. If you do not specify it, the utility attempts to use your local configuration file (for example, my.cnf) if available.

For example, if you wanted to create a configuration file in /dev/env/test1/my.cnf and you created a section named server1, you would specify it as --server=/dev/env/test1/my.cnf[server1]. The corresponding section in the configuration file may look like the following.

[server1]
port=3308
user=root
password=other-pass
host=localhost

The following shows the execution of a utility using a configuration file.

shell> mysqlserverinfo.py --server=/dev/env/test1/my.cnf[server1] --format=vertical

# Source on localhost: ... connected.
*************************       1. row *************************
server: localhost:13001
config_file: /etc/my.cnf, /etc/mysql/my.cnf
binary_log: clone-bin.000001
binary_log_pos: 341
relay_log:
relay_log_pos:
version: 5.6.17-log
datadir: /Volumes/Source/source/temp_13001/
basedir: /Volumes/Source/source/bzr/mysql-5.6
plugin_dir: /Volumes/Source/source/bzr/mysql-5.6/lib/plugin/
general_log: OFF
general_log_file:
general_log_file_size:
log_error:
log_error_file_size:
slow_query_log: OFF
slow_query_log_file:
slow_query_log_file_size:
1 row.
#...done.

2.2.1.3 Command-line Options

The least secure way to provide connection information for MySQL servers is to specify the data on the command-line. This is least secure because the data is visible on the command-line and is also visible in any log or redirection of the execution.

In this case, we specify the data in the following order: user[:passwd]@host[:port][:socket] where the passwd, port, and socket are optional. Each item is described in more detail below.

  • user

    The name of the user to connect.

  • passwd

    The password to use when connecting. The default if no password is supplied is the empty password.

  • host

    The domain name of the host or the IP address. This field accepts host names, and IPv4 and IPv6 addresses. It also accepts quoted values which are not validated and passed directly to the calling methods. This enables users to specify host names and IP addresses that are outside of the supported validation mechanisms.

  • port

    The port to use when connecting to the server. The default if no port is supplied is 3306 (which is the default port for the MySQL server as well).

  • unix_socket

    The socket to connect to (instead of using the host and port parameters).

The following demonstrates executing a utility using command-line options for connecting to a server.

shell> mysqlserverinfo.py --server=root:other-pass@localhost:3308 --format=vertical
# Source on localhost: ... connected.
*************************       1. row *************************
server: localhost:13001
config_file: /etc/my.cnf, /etc/mysql/my.cnf
binary_log: clone-bin.000001
binary_log_pos: 341
relay_log:
relay_log_pos:
version: 5.6.17-log
datadir: /Volumes/Source/source/temp_13001/
basedir: /Volumes/Source/source/bzr/mysql-5.6
plugin_dir: /Volumes/Source/source/bzr/mysql-5.6/lib/plugin/
general_log: OFF
general_log_file:
general_log_file_size:
log_error:
log_error_file_size:
slow_query_log: OFF
slow_query_log_file:
slow_query_log_file_size:
1 row.
#...done.

As of MySQL Utilities 1.4.4, this deprecated connection method issues a warning if you use this connection method.

2.2.2 Specifying Connections in Python Library

If you build your own utilities using the MySQL Utilities library, there are various methods for connecting to MySQL servers. Methods that deal with connecting to servers can accept the following mechanisms for supplying the data.

  • As a Python dictionary containing the connection parameters.

  • As a connection specification string containing the connection parameters.

  • As a Server instance.

The dictionary lists the values by name as described above. For example, you would create code like the following.

# Set connection values
dest_values = {
    "user" : "root",
    "passwd" : "secret",
    "host" : "localhost",
    "port" : 3308,
    "unix_socket" : None,
}

The connection specification is a string the form user[:passwd]@host[:port][:socket] where the passwd, port, and socket are optional. This string is parsed using the options.parse_connection function.

You can also specify an existing instance of the Server class. In this case, the new class copies the connection information.

2.2.3 Specifying User and Password for Replication Options

There are two commonly used replication options for those utilities that work with MySQL replication. These include the --rpl-user= and --discover-slaves-login= options.

In either case, these options permit users to specify a user account name and (optionally) a password in the form of user[:passwd]. Notice the use of the colon (:) to separate the values. Should a user name or password contain a colon, the utility attempts to use the first colon encountered (left-to-right) as the separator. Thus, you should not use colons in your user names, but you may use colons in the password.

However, it should be noted that some special characters may cause some platforms to mangle the command. Thus, you should use single quotes when specifying passwords with special characters as shown in the example below.

shell> mysqlreplicate [...] --rpl-user=rpl:'L5!w1SJzVuj40(p?tF@' [...] 

2.2.3.1 Specifying Secure Socket Layer (SSL) Options

Security is a big concern and MySQL Utilities is prepared to use a secure connection to MySQL server secure-connections using an encrypted connection with SSL. This section shows you how to use SSL when connecting to MySQL servers from any utility. All of the utilities use the same mechanism for establishing an SSL connection and include the following options.

  • --ssl-ca

    The path to a file that contains a list of trusted SSL CAs.

  • --ssl-cert

    The name of the SSL certificate file to use for establishing a secure connection.

  • --ssl-key

    The name of the SSL key file to use for establishing a secure connection.

  • --ssl

    Specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. Default setting is 0 (SSL not required).

In order to use SSL connections, the MySQL server must be configure using the --ssl-ca --ssl-cert and --ssl-key options with a specific SSL certificate. The --ssl option is used to enforce an SSL option. That is, if an SSL connection cannot be made, do not fall back to a normal connection. This option is not needed unless you want to enforce an SSL connection.

Note

If you are uncertain of how to create the SSL certificates, please following the steps indicated on Creating SSL and RSA Certificates and Keys.

Each utility permits the user to specify the --ssl-ca, --ssl-cert, --ssl-key, and --ssl options to create a SSL connection to a MySQL server. Simply specify the same options used when the server was started.

For example, if we wanted to get the information about a server that supports SSL connections, we first identify the SSL certificate authority (--ssl-ca), SSL certificate (--ssl-cert), and SSL key (--ssl-key). We want the connection to default to a normal connection if an SSL connection cannot be made, thus we omit the --ssl option.

Thus, we use the values from the server SSL configuration with the corresponding options for the utility. The following is an example of the running the serverinfo command with an SSL connection.

shell> mysqlserverinfo --server=root:pass@localhost:3307 \
          --ssl-ca=C:/newcerts/cacert.pem \
          --ssl-cert=C:/newcerts/client-cert.pem \
          --ssl-key=C:/newcerts/client-key.pem \
          --format=vertical
# Source on localhost: ... connected.
*************************       1. row *************************
                   server: localhost:3307
              config_file:
               binary_log:
           binary_log_pos:
                relay_log:
            relay_log_pos:
                  version: 5.6.15
                  datadir: C:\MySQL\instance_3307\
                  basedir: C:\MySQL\mysql-5.6.15-winx64
               plugin_dir: C:\MySQL\mysql-5.6.15-winx64\lib\plugin\
              general_log: OFF
         general_log_file:
    general_log_file_size:
                log_error: C:\MySQL\instance_3307\clone.err
      log_error_file_size: 1569 bytes
           slow_query_log: OFF
      slow_query_log_file:
 slow_query_log_file_size:
1 row.
#...done.