Secure Login For MySQL Via The Command Line With No Password Prompt

The goal here is to be able to type a one word shell command at the shell’s command prompt and have that drop you into a MySQL command prompt on the host machine and database of your choosing.

For example:

shell>mysqldevwm

mysql>\s
--------------
mysql  Ver 14.14 Distrib 5.6.25, for osx10.10 (x86_64) using  EditLine wrapper

Connection id:      5162
Current database:   web_manager
Current user:       xxxxx@xxxxxxxxxxxxx
SSL:            Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.5.43-0ubuntu0.12.04.1 (Ubuntu)
Protocol version:   10
Connection:     dev via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:       3306
Uptime:         33 days 10 hours 12 min 26 sec

Threads: 1  Questions: 1210383  Slow queries: 85  Opens: 1358  Flush tables: 1  Open tables: 114  Queries per second avg: 0.419
--------------

Sure, you could do this with:

shell>mysql -uroot -p -hdev web_manager

But, you’d be prompted for the password every time and that gets old. Of course, you could just include the password on the command line but that would be insecure. Here’s how you fix that.

mysql_config_editor

If you already have the MySQL command line utilities installed on your box, you’re good to go. Just type “mysql –version” to verify you have them. If not, you can download them here: http://dev.mysql.com/downloads/utilities/

The mysql_config__editor command will create an encrypted file with our password and our databases hidden safely inside. Here’s the syntax.

mysql_config_editor set --login-path=pickanyname --host=actualmachine --user=yourusername --password

Here’s an example:

shell>mysql_config_editor set --login-path=mywebdb --host=www.mywebsite.com --user=root --password

shell>Enter password:

At this point the system is prompting you to type in the password. Enter the password for the root user at www.mywebsite.com and press enter. This command creates the encrypted file and stores it in your home directory. The file name is .mylogin.cnf. You can view it’s contents by typing:

mysql_config_editor print all

To add another login for another database, just repeat the above steps.

Automating things with bash

At this point you could type:

shell>mysql --login-path=mywebdb webdb

Where webdb is the name of the actual database. But, since that’s a lot to type every time you want to login, we’re going to create a bash script to make it much simpler. Fire up an editor and put the above line in a file, save it to your home directory as mywebdb, then from a shell prompt type:

shell>chmod +x mywebdb

This will make the script directly executable. Now just type:

shell>./mywebdb

This should log you into your mysql database. If you move the script to a directory that’s in your path for executables, you can drop the ./ before the script name.