Guide to use configuration files to handle MySQL user and password information so they are never typed in Linux console.
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.
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.
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
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
#!/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.