Documentation Home
MySQL Utilities 1.5 Manual
Related Documentation Download this Manual
PDF (US Ltr) - 1.0Mb
PDF (A4) - 1.0Mb
HTML Download (TGZ) - 192.4Kb
HTML Download (Zip) - 203.7Kb


MySQL Utilities 1.5 Manual  /  ...  /  How do you setup and use a secure (encrypted) connection between Utilities and a MySQL server?

3.4.3 How do you setup and use a secure (encrypted) connection between Utilities and a MySQL server?

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.

Objectives

Use the mysqlserverclone utility to create a new instance of your installed MySQL Server. This new instance is enabled for secure connections using SSL to establish a secure connection by using the SSL options. You can also use an options file to specify the SSL certificates needed for the secure connection.

Example Execution

To meet this objective, you need to supply values for the following options of mysqlserverclone:

  • --basedir

  • --new-port

  • --new-data

  • --mysqld

  • --root-password

If you are unfamiliar with the previous options, you can find more info in the Section 5.20, “mysqlserverclone — Clone Existing Server to Create New Server” section.

In the --mysqld option you need to specify the --ssl-ca --ssl-cert and --ssl-key options with his respective SSL certificate for the new instance of the server. By doing this, the new server instance uses the given certificates to establish a secure connection. If you are uncertain of how to create the SSL certificates, please following the steps indicated on Creating SSL and RSA Certificates and Keys. The --ssl-ca --ssl-cert and --ssl-key options of mysqlserverclone are used to connect to an existing instance of MySQL in case you need to use ssl to connect to it and these options are not used to indicate the certificates to use by the new server instance. For that reason it is necessary to use the --mysqld option of mysqlserverclone.

The following is an example of the running command.

shell> mysqlserverclone --basedir=C:\MySQL\mysql-5.6.15-winx64 \
          --new-data=C:\MySQL\instance_3307 \
          --new-port=3307 --root-password=pass \
          --mysqld="--ssl-ca=C:/newcerts/cacert.pem \
          --ssl-cert=C:/newcerts/server-cert.pem \
          --ssl-key=C:/newcerts/server-key.pem" 
# Cloning the MySQL server located at C:\MySQL\mysql-5.6.15-winx64.
# Creating new data directory...
# Configuring new instance...
# Locating mysql tools...
# Setting up empty database and mysql tables...
# Starting new instance of the server...
# Testing connection to new instance...
# Success!
# Setting the root password...
# Connection Information:
#  -uroot -ppass --port=3307
#...done.

Now we have a new MySQL server instance, and you can confirm the use of the given SSL certificates with the MySQL command-Line tool (also called the monitor or simply the MySQL client tool) by executing the command: "show variables like '%ssl%';".

shell> mysql -uroot -ppass --port=3307 -e"show variables like '%ssl%';"
+---------------+-----------------------------+
| Variable_name | Value                       |
+---------------+-----------------------------+
| have_openssl  | YES                         |
| have_ssl      | YES                         |
| ssl_ca        | C:/newcerts/cacert.pem      |
| ssl_capath    |                             |
| ssl_cert      | C:/newcerts/server-cert.pem |
| ssl_cipher    |                             |
| ssl_crl       |                             |
| ssl_crlpath   |                             |
| ssl_key       | C:/newcerts/server-key.pem  |
+---------------+-----------------------------+

However, at this moment the root account is not using an encrypted ssl connection. You can see this using the MySQL command-Line tool running the "status;" command:

shell> mysql -uroot -ppass --port=3307 -e"status;"
--------------
mysql  Ver 14.14 Distrib 5.6.15, for Win64 (x86_64)

Connection id:          11
Current database:
Current user:           root@localhost
SSL:                    Not in use
...
--------------

You need to add the SSL options necessarily to establish an encrypted connection with SSL, this can be done in the following form:

shell> mysql -uroot -ppass --port=3307 --ssl-ca=C:/newcerts/cacert.pem \
          --ssl-cert=C:/newcerts/server-cert.pem \
          --ssl-key=C:/newcerts/server-key.pem -e"status;"
--------------
mysql  Ver 14.14 Distrib 5.6.15, for Win64 (x86_64)

Connection id:          13
Current database:
Current user:           root@localhost
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
...
--------------

Note

To configure an account to only permit SSL-encrypted connections, the grants for that account must include the REQUIRE SSL clause in your GRANT Syntax statement.

In the same form that you use the SSL options with the MySQL Command-Line Tool, you can use the SSL options on each of the MySQL Utilities. The following is an example of mysqlserverinfo using SSL options:

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.

Or you can indicate the SSL options by Using Option Files as is mentioned in the Section 2.2, “Connecting to MySQL Servers” documentation. This is an example of how it may look for a group with the options in an options file for the command used above.

[instance_3307]
port=3307
user=root
password=pass
host=localhost
ssl-ca=C:/newcerts/cacert.pem
ssl-cert=C:/newcerts/client-cert.pem
ssl-key=C:/newcerts/client-key.pem

In this case, the file is located at C:\MySQL\instance-3307.cnf and by indicating this path and the group name in the --server option, the options for the mysqlserverinfo of the previous example takes this form:

shell> mysqlserverinfo --server=c:\MySQL\instance-3307.cnf[instance_3307] \
          --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.

Discussion

The SSL options (--ssl-ca --ssl-cert and --ssl-key) are available in the MySQL Utilities that requires a connection to a server or servers, as is in the case of the --master and --slave options.

Note

An options file can be used to store the connection values, and the MySQL Utilities can read the values stored in them as mentioned in the Section 2.2, “Connecting to MySQL Servers” documentation.

Permissions Required

Required permissions include the ability to read the SSL certificate files and the path where they are located regardless of the form these SSL certificate paths are given to the MySQL Utilities, in addition of the required permissions that each utility requires to accomplish its specific task.

Tips and Tricks

In the configuration file, different connection options can be stored and separated in groups. The desired group used by the MySQL Utilities can be expressed by indicating the group name in the form config-path["["group-name"]"], such as C:\MySQL\instances.cnf:

[instance_3307]
port=3307
user=root
password=pass
host=localhost
ssl-ca=C:/newcerts/cacert.pem
ssl-cert=C:/newcerts/client-cert.pem
ssl-key=C:/newcerts/client-key.pem

[instance_3308]
port=3308
user=root
password=other-pass
host=localhost
ssl-ca=C:/newcerts/cacert_2.pem
ssl-cert=C:/newcerts/client-cert_2.pem
ssl-key=C:/newcerts/client-key_2.pem

shell> mysqlreplicate --master=c:\MySQL\instances.cnf[instance_3307] \
          --slave=C:\MySQL\instances.cnf[instance_3308]


User Comments
Sign Up Login You must be logged in to post a comment.