Handling Mysql Credentials From Linux Command Line The Right Way

Overview

Credentials should never by typed in the command line, it is better to handle them in configuration files so they are never shown in logs, processes and you don’t need to explicitly type them.

This is specially useful to automate MySQL backups and use it safely in shared environments like a VPS or any shared hosting.

We use MySQL configuration files to configure credentials as they are read at the startup stage.

Setting up the credentials file

We create a configuration file mydb.cnf with the following format.

[client]
host=
user=
password=

Using the config file

Then we can use the defaults-extra-file parameter to tell MySQL to read our configuration.

Usage examples

Having the credentials in a configuration file makes it possible to create scripts and automate tasks easily.

MySQL Backup script

Having this configuration we can easily make a backup script to build a cronjob and automatically backup the database regularly using mysqldump. Edit the file backup.sh with the following content.

#!/bin/bash
# Backup database from console
DATABASE=
DEFAULTS_FILE=$DATABASE.cnf
BACKUP_DIR=
LOGS_DIR=

DB_OUT_FILENAME=$DEFAULTS_FILE-`date +\%Y\%m\%d`.sql.gz
mysqldump --defaults-extra-file=$DEFAULTS_FILE $DATABASE 2>> $LOGS_DIR/$DATABASE.log | gzip - > $BACKUP_DIR/$DB_OUT_FILENAME

Then make it executable chmod +x backup.sh

Truncate MySQL tables from console

We can remove all the content from all the tables of a database automatically with the following script truncate_db.sh:

#!/bin/bash
# Truncate database tables from console
DATABASE=
DEFAULTS_FILE=$DATABASE.cnf

mysql --defaults-extra-file="$DEFAULTS_FILE" -Nse 'show tables' $DATABASE | while read table; do mysql --defaults-extra-file="$DEFAULTS_FILE" -e "truncate table $table" $DATABASE; done

Then make it executable chmod +x truncate_db.sh.

Summary

Using MySQL options file, it is very easy to setup a safer environment than executing commands with the user or password shown in console.

Backing up the database or perform any other task from console with the mysql or mysqldump command becomes trivial.

References

• https://gist.github.com/marcanuy/5977648
• http://dev.mysql.com/doc/refman/5.7/en/option-files.html

*[MySQL]: My Structured Query Language *[VPS]: Virtual Private Server