Hide MySQL user and password from command line
- Published: November 15, 2016
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.
$ 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.
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
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
#!/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.
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
mysqldump command becomes trivial.
*[MySQL]: My Structured Query Language *[VPS]: Virtual Private Server