Image: Jack Wallen
If you manage multiple MySQL servers and want a simplified means of logging in, see how to use the new passwordless authentication feature.
By Jack Wallen | September 30, 2016, 7:36 AM PST
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.More about IT Security
Governments and nation states are now officially training for cyberwarfare: An inside look
Europe, Canada, USA, Australia, and others are now running training exercises to prepare for the outbreak of cyberwar. Locked Shields is the largest simulation and we take you inside.
Read more
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.
No comments:
Post a Comment