MySQL – Secure alternative to include the password in command line scripts using mysql_config_editor and Host Alias

MySQL Master-Master Replication setup in 5 easy steps

If you’re a System Administrator working with MySQL, you’re probably aware about the fact that the most effective way to automatize various tasks on regular basis is using command-line scripts using PHP and bash files (under Linux) or PHP, batch and PowerShell files (under Windows) and then hook them up to cron jobs/scheduled tasks to have them execute once a week, daily, hourly and so on.

This is a rather effective approach, expecially if you don’t want to use an automation GUI that can do the same but will often come with a price. However, such scenario is prone to a major security flaw: the fact that you have to put ad administrative password in clear text within the command-line itself.

Consider the following example:

This is a rather classic repair & optimize script, something that we can put into a batch file and then schedule for daily (or nightly) launch on our DB server. Problem is, we are forced to put our password there in clear text. We certainly wouldn’t want to do that, but there seems to be no other way around.

Well, technically there is one: we could input some default credentials into our my.cnf file in the following way:

And then tell our script to use them:

However, there are no significative improvements in terms of security: we just moved the dirt under the rug.

Luckily enough, starting from MySQL 5.6, there is a rather effective solution we can use to fix this security issue for good. We’re talking about a brand-new feature shipped with the mysql_config_editor command, which is well described in the MySQL 5.6 official docs. We can use this tool to encrypt our user/pass credentials within a HOST ALIAS, which then we can use to run our scripts instead of them.

In order to do this, we just need to run this command once:

The system will then ask us for the user’s password: note that we could also use root user here, even though it might not be the wisest choice in terms of security (which is the reason why we’re doing this, so DON’T): it’s always better to create a service user with the proper authorization, which you can then revoke or modify at will.

As soon as you did that, a new host alias record will be created, meaning that you’ll be able to use that in the following way:

There you go! No more clear-text passwords in your script files.

If you’re curious about what happens under the hood, we suggest you to read the following page from the MySQL official guide. To keep it short, the host aliases are stored in an encrypted login file named .mylogin.cnf, which is located in the   folder on Windows systems and in the current user’s home directory on non-Windows systems.

That’s it for now: happy scripting!

 

About Ryan

IT Project Manager, Web Interface Architect and Lead Developer for many high-traffic web sites & services hosted in Italy and Europe. Since 2010 it's also a lead designer for many App and games for Android, iOS and Windows Phone mobile devices for a number of italian companies. Microsoft MVP for Development Technologies since 2018.

View all posts by Ryan

1 Comment on “MySQL – Secure alternative to include the password in command line scripts using mysql_config_editor and Host Alias”

  1. Maybe worth to mention: if you automate a mysql script (via cron, launchd, scheduled task, etc), keep in mind that the job should run under a user who’s got access to the .mylogin.cnf file. My LaunchDaemons stopped working after I switched to –login-path due to the fact that LaunchDaemons are running under root, therefore it couldn’t find the .cnf file. I had to specify a username in the Daemon and then it worked again.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.