Guide to use configuration files to handle MySQL user and password information so they are never typed in Linux console.

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.

Option files provide a convenient way to specify commonly used options so that they need not be entered on the command line each time you run a program.

Always avoid typing the user and password $ mysql --host=localhost --user=myname --password=mypass mydb

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.

–defaults-extra-file=file_name

Read this option file after the global option file but (on Unix) before the user option file and (on all platforms) before the login path file. (For information about the order in which option files are used, see Section 5.2.6, “Using Option Files”.) If the file does not exist or is otherwise inaccessible, an error occurs. file_name is interpreted relative to the current directory if given as a relative path name rather than a full path name.

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

TRUNCATE TABLE empties a table completely. It requires the DROP privilege.

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

Uruguay
Marcelo Canina
I'm Marcelo Canina, a developer from Uruguay. I build websites and web-based applications from the ground up and share what I learn here.