If you frequently log into numerous MySQL servers, I bet sometimes you just want in without having to remember which user/password combination goes with which server…or which server is used for which service. This can be exacerbated when you have an entire database server farm from which to choose.
Wouldn’t it be nice if you could use a built-in mechanism to save profiles that would enable you to log into a MySQL server without having to enter or remember that password?
Oh wait…it’s possible.
And quite easy.
I can hear you now. This clearly creates a problem with security. Sure, if:
- Your admin user password on your desktop machine is weak; or
- Anyone getting into your system knows the names of the profiles used for this setup.
This isn’t a setup you’d use on a system that just anyone has access to. Even so, if you are ultra-paranoid about security (which you should be), you’ll want to make sure the machine you use for this is quite secure. For example, you might want to implement two-step login for a remote machine and then use passwordless authentication from there.
SEE: How to set up 2 step authentication for ssh on your Linux servers
The big caveat
Passwordless authentication for MySQL only works on release 5.6 and newer. If you’re using anything earlier than that, don’t even bother. If you’re running Ubuntu, you can upgrade to 5.6 with these commands:
- sudo add-apt-repository ‘deb http://archive.ubuntu.com/ubuntu trusty universe’
- sudo apt-get update
- sudo apt install mysql-server-5.6
SEE: Network Security Policy (Tech Pro Research)
Creating a profile
The first thing you must do is create a profile for passwordless authentication. This is done with the help of the mysql_config_editor tool. Let’s say we’re going to create a profile for the MySQL server, named mysql1, running on IP address 192.168.1.158, with an admin user of dbadmin. To do this, you would issue the command:
mysql_config_editor set –login-path=mysql1 –host=192.168.1.158 –user=dbadmin –password
When you issue that command, you will be prompted for a password. The password you use will be the one associated with user dbadmin on the MySQL server mysql1. Once authenticated, the profile will be created, and you’re ready to connect.
Connecting with a profile
Now let’s connect to that mysql1 server with the profile. From a terminal window, issue the command:
mysql –login-path=mysql1
You will immediately be taken to the MySQL prompt on the MySQL server on 192.168.1.158. That’s it.
Create as many profiles as needed
One of the reasons why I like this tool so much is that it enables me to create as many profiles as I need; this way, I don’t have to remember which user is associated with which MySQL server…I only need to remember the profile login-path variable, and I’m good to go.
Remember: Whatever machine you use with the mysql_config_editor command needs to be very secure; otherwise, you run the risk of someone knowing that you’re using mysql_config_editor profiles and knowing what names you’ve used for those profiles. With that information, they’ll have at your MySQL servers with ease.
