How to avoid using clear text passwords in MySQL

You will see in this article how to use the option file in MySQL to avoid using clear text password in our daily maintenance tasks.    The first thing you need know is that the default options are generally read from the following files in the given order(unless you specify a new option file location at MySQL start time):

  1. /etc/my.cnf
  2. /etc/mysql/my.cnf
  3. my.cnf in the DEFAULT_SYSCONFDIR specified during the compilation
  4. my.cnf in the path, specified in the environment variable MYSQL_HOME (if any)
  5. the file specified in --defaults-extra-file (if any)
  6. user-home-dir/.my.cnf
So in our case we are going to use the 6th option where we will create the .my.cnf file and define all the option groups to satisfy our needs(log in with no password for example). In MySQL the option file or the configuration file(my.cnf) comes with some predefined groups. What is a group in MySQL option file  -group is the name of the program or group for which you want to set options. After a group line, any option-setting lines apply to the named group until the end of the option file or another group line is given. Is good to know that option group names are not case sensitive. Example of ~/.my.cnf file 
#this option group is read by all client programs
#this enables you to specify options that apply to all clients
[client]
user=root
password=pass

[MySQL]
user=root
password=pass

[mysqldump]
user=root
password=pass

[mysqldiff]
user=root
password=pass
  • i normally use this option file ~/.my.cnf only with this options(password) to make my life easier and also avoid passing clear text password in my crontab MySQL jobs.
Very important : -make sure that you have restricted access to this file, normally i only allow the owner to read and write on it. 
[root@primary ~]# chmod 600 .my.cnf
[root@primary ~]# ls -la .my.cnf
-rw------- 1 root root 33 Mar  4 11:29 .my.cnf

Copyright 2020. All Rights Reserved