Personal blog of Yzmir Ramirez

I log into a lot of different servers running MySQL and one of the first things I do is create a file in my home directory called ‘.my.cnf’ with my credentials to that local mysql instance:

[client]
user=root
password=secret

This means I don’t have to type my password in every time, nor am I tempted to include it on the command line with -p and get the dreaded (but completely accurate):

Warning: Using a password on the command line interface can be insecure.

MySQL 5.6 introduces a utility to make this easier and more secure. First, let’s start with a new mysqld instance with a blank root password and make it more secure:

[vagrant@localhost ~]$ mysqladmin -u root password
New password:secret
Confirm new password:secret
[vagrant@localhost ~]$ mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Ok, so I’ve added a password, now I want to create my .my.cnf file:

[vagrant@localhost ~]$ mysql_config_editor set --user=root --password
Enter password:secret
[vagrant@localhost ~]$ mysql
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 10
Server version: 5.6.21-70.0 Percona Server (GPL), Release 70.0, Revision 688
Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql>

What did ‘mysql_config_editor set’ actually do? It creates a .mylogin.cnf file (which stands in for a .my.cnf) in my home directory that contains my credentials, just in encrypted form:

[vagrant@localhost ~]$ ls -alh .my*
-rw-------. 1 vagrant vagrant 100 Nov 19 16:20 .mylogin.cnf
-rw-------. 1 vagrant vagrant  29 Nov 19 16:20 .mysql_history
[vagrant@localhost ~]$ cat .mylogin.cnf
??>NTv?&?S???/?,	>?$%KZ 9i?V?jK?H[???
           k.
[vagrant@localhost ~]$ mysql_config_editor print
[client]
user = root
password = *****

The mysql client picks this up right away and will use it by default. This file has good default filesystem permissions, is local to my homedir, and is a whole lot better than specifying it on the command line or typing it in every time.

This utility also supports a feature called ‘login-path’ wherein you can add multiple mysql logins (perhaps to different servers) and refer to them with the —login-path option in the mysql client:

[vagrant@localhost ~]$ mysql_config_editor set --login-path=remote --host=remote --user=remote --password
Enter password:secure
[vagrant@localhost ~]$ mysql_config_editor print --all
[client]
user = root
password = *****
[remote]
user = remote
password = *****
host = remote
[vagrant@localhost ~]$ mysql --login-path=remote
ERROR 2005 (HY000): Unknown MySQL server host 'remote' (0)

The ‘remote’ host doesn’t exist here, but you get the idea. You can create as many login-paths as you want with varied hostnames, credentials and other login parameters and quickly access them with any client supporting login-path.

Now, how secure is this really?  This isn’t secure from anyone who roots your DB server.  I would say the benefits are more about reducing careless password storage and tidier management of local credentials.

The post (More) Secure local passwords in MySQL 5.6 and up appeared first on MySQL Performance Blog.

A customer called with an emergency issue: A server that normally runs many MySQL instances wouldn’t start them up. Not only would it not start all of them, it wouldn’t even start the first one. The multiple instances were started through the mysql_multi init script. Perhaps you already know what was wrong!

It turns out that this server’s /etc/init.d/mysql_multi wouldn’t start unless it found the text “mysqld_multi” in the /etc/my.cnf file. Not a [mysqld_multi] config file section, but the text string “mysqld_multi”. It was using this text as a proxy for “I found a [mysqld_multi] configuration section.” This was a rather brittle test, as you can imagine.

After reading the source, I determined that the my.cnf file was fine and the configuration should not be changed, and I could not understand what had changed since it was previously working. Perhaps an automated upgrade or a similar change to the system had broken it.

The fix was to place the following comment into the file.

 # This comment is only necessary to make /etc/init.d/mysql_multi work OK, it greps for mysqld_multi in an 'if' statement

That’s the only time I can recall fixing software by putting a comment into its configuration file. Unfortunately I don’t recall what Linux distribution this was on; I just checked a recent download, and the official MySQL distribution contains a file called mysqld_multi (note the different name) that doesn’t contain this error-prone test.