Automated executing of MySQL/MariaDB scripts

I am running MariaDB and I wish to execute sql scripts without all this hassle of entering my password. Of course this carries severe security risks with it, that we need to be aware of and, if possible, mitigate.

Via the commandline

It is possible to execute sql scripts via the commandline¹, but the problem here is that the password you use is visible in the process list. So this is a security risk.

Let's not do this.

mysql_config_editor

I firstly checked out mysql_config_editor², which enables you to put the password and other options into an encrypted configuration file. But it turns out that MariaDB does not come with that specific tool. The encryption used seems quite weak, and there's an article about the security issues at [3]. There is also the blogpost at [4] giving some details.

So now what?

Well, there is always the plan to use the configuration file .my.cnf⁵, and you can store your mysql or mariadb password in there and everything would be hunky-dory.

The .my.cnf looks like this:

[client]
password=topsecretpassword

You are no doubt aware that the password is stored in cleartext.

The following security measures should be in place:

• always make sure the permissions on the file are set to -rw-------
• create a user in your database with only those permissions that are required by your scripts. In most cases, this is select/update/delete/insert statements.
• when you are finished with your scripts, it might be a good idea to remove the password from the conf file. I understand that with cron jobs and batch scripts this might not be possible.

There is an example of a my.cnf containing every possible configuration option at /usr/share/mysql/my-large.cnf when you install MariaDB.

References

[1] StackOverflow - How to execute a MySQL command from a shell script?

https://stackoverflow.com/questions/8055694/how-to-execute-a-mysql-command-from-a-shell-script

[2] MysqlManual 5.7 - mysql_config_editor

https://dev.mysql.com/doc/refman/5.7/en/mysql-config-editor.html

[3] MariaDb Blog -

https://mariadb.com/resources/blog/mysql-56-security-through-complacency

[4] Todd's MySQL Blog - Understanding mysql_config_editor’s security aspects

http://mysqlblog.fivefarmers.com/2012/08/16/understanding-mysql_config_editors-security-aspects/

[5] MariaDB - Configuring MariaDB with my.cnf

https://mariadb.com/kb/en/library/configuring-mariadb-with-mycnf/