New! MySQL Utilities Now Supports SSL and Configuration Files

The MySQL Utilities Team is pleased to announce a new release that contains our newest features – SSL and configuration file support. These were added to release-1.5.0-alpha.

How can I make a secure connection to my server via Utilities?

Use the new SSL command-line options that are available for all utilities:

–ssl-ca : The path to a file that contains a list of trusted SSL certificate authorities.
–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.

Then just specify the appropriate values on the command-line with any other parameters.

How can I use configuration files?

If typing all of those SSL options seems tedious, you can specify this information in your configuration file and reference the file via a new syntax. You can also supply a path to the file. Observe.

–server=<configuration_file_and_path>[<section>]

Example: –server=/dev/env/test1/my.cnf[server1_ssl]

In this example, the utility will read the SSL option values from the my.cnf file in /dev/env/test1/ looking for them in the server1_ssl section. Thus, you can make as many sections as you have servers and/or combinations of login option values. How cool is that?

Here’s an example with two entries; the first one has SSL options.

[instance_3307]
port=3307
user=root
password=lilly-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

As you can see, you can use this new syntax for any connection – SSL or not! No more typing out the user, password, blah, blah, blah, just plug the information into your configuration file and you’re done with remembering ports and sockets and passwords and … you get the idea.

SSL Examples

Now let’s see the SSL feature in action. It’s really quite simple.

Example1: Using command-line options:

$ 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.

Example2: Using the configuration file specification:

$ 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.

Configuration File Example

Here, I’ve added the following to my configuration file:

[instance_13001]
port=13001
user=root
password=mippippipi
host=localhost

Now I execute a utility:

$ mysqlserverinfo.py –server=my.cnf[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.

Wow, now that’s easier and it also reduces security concerns by removing the password from the command-line.

But wait, there is an even more secure way to specify passwords: login-paths!

Note: MySQL Utilities version 1.2.1 and later support login-paths.

In the above example, the password is stored in plain-text in the option file. However, if you use login-paths, you can store the same information in a encrypted file (.mylogin.cnf). Wow. No more plain text passwords!

Example: Using login-paths

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

$ 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):

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

Now, execute the desired utility specifying the login-path section instead of the usual user:passwd@host:port:socket:

$ 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.

How Can I Download MySQL Utilities?

You can download MySQL Utilities 1.5.0-alpha from the following link using one of the pre-built installation repositories including a source download. Be sure to click the “Development Releases” tab to see the 1.5.0 version download links.

http://dev.mysql.com/downloads/tools/utilities/

Where is the Documentation?

You can find online documentation for MySQL Utilities version 1.5 at:

http://dev.mysql.com/doc/mysql-utilities/1.5/en/index.html

About Chuck Bell

Charles Bell conducts research in emerging technologies. He is a member of the Oracle MySQL Development team and is the team lead for the MySQL Utilities team. He lives in a small town in rural Virginia with his loving wife. He received his Doctor of Philosophy in Engineering from Virginia Commonwealth University in 2005. Dr. Bell is an expert in the database field and has extensive knowledge and experience in software development and systems engineering. His research interests include 3D printers, microcontrollers, three-dimensional printing, database systems, software engineering, and sensor networks. He spends his limited free time as a practicing Maker focusing on microcontroller projects and refinement of three-dimensional printers.

Leave a Reply

Your email address will not be published. Required fields are marked *


nine + 5 =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>