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:
|
1 |
"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqlcheck" --user=USER --password=PASSWORD --auto-repair --optimize --all-databases |
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:
|
1 2 3 |
[client] user=mysqluser password=mysqlpass |
And then tell our script to use them:
|
1 |
"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqlcheck" --defaults-file=/path/to/my.cnf --auto-repair --optimize --all-databases |
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:
|
1 |
mysql_config_editor set --login-path=ALIASNAME --host=localhost --user=USER --password |
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:
|
1 |
"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqlcheck" --login-path=ALIASNAME --auto-repair --optimize --all-databases |
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 \%APPDATA%\MySQL folder on Windows systems and in the current user's home directory on non-Windows systems.
That's it for now: happy scripting!
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.