How to create a credential file for MySQL

This tutorial will show you how to create a credential file for MySQL. The credential file allows us to store our sensitive username and password to allow automated access to MySQL and its tools.

Having to enter your username and password every time you access MySQL’s console or use one of its utilities can be a pain, especially if you follow password best practices, where you use a 16 character, random character password.

Create a Credential File

The credential file syntax is similar to MySQL’s configuration file, and that’s because it is a configuration file. Any of the parameters we place in this file could also be added to my.cnf.

To add credentials we first tell MySQL where they will be applied. For example, you could set it to [mysql] to set credentials for the MySQL client. In our example, we are assigning them to [mysqldump] so that they will be used by that tool.

[mysqldump]
user=<username>
password=<password>

Make a new directory to store your credentials file.
```
mkdir ~/.mysql
```
Ensure the directory permissions are appropriate by granting only your account access.
```
chmod 0700 .mysql
```
Create the configuration file in the new directory.
```
touch mysqldump.cnf
```
Add the following lines, replacing the values to fit your environment.
```
[mysqldump]
user=backup_operator
password=Fa$2av;$a3q5g
```
Change the file permissions to permit read access only to your account.
```
chmod 0400 ~/.mysql/mysqldump.cnf
```

Using the Credentials File

Now that we have created our credentials file we no longer need to type our username or password into the command-line. This is even more beneficial when using scripts to automate our works, as we no longer need to place sensitive information in them.

To use your credentials file you use the –defaults-extra-file flag in your MySQL command. For example, to access the MySQL console with your credential file you would do the following:

mysqldump --defaults-extra-file=~/.mysql/mysqldump.cnf

Encrypting your Credentials

Limiting access to your credentials is a good step to protect your information. Unfortunately, there is a lot of risks just leaving your information in clear text files. If an attacker was able to use a vulnerability to grant themselves higher privileges, they will be able to view your information.

To further safeguard our credentials, MySQL 5.6 introduced a method to encrypt them in a stored profile. To use your stored credentials you just need to tell MySQL which profile to use.

Creating the Profile

The command to set your credentials and store them under a profile is mysql_config_editor.

Execute the following command to set a username and password under a profile called backups.
```
mysql_config_editor set --login-path=backups --host=localhost --user=backupops --password
```
When prompted, enter a password.

Using the Profile

To use your secured credentials with MySQL, mysqldump, or other mysql tools you just need to specify the profile. In our example, we are using the credentials stored under our backups profile.

mysql --login-path=backups

No related posts found