When running non-interactive MySQL commands one may submit MySQL credentials on the command line (we'll share with you a better option after the break), e.g.:

mysql --password=secret --user=username database_name 
This approach has two drawbacks. First, it is not secure. Second, if you have many scripts with embedded passwords you will have to take care of all of them when the time comes to change your password. A better option is to use a special option file to store login information. At start up, all MySQL programs read from one or more global option files, and lastly from the file called .my.cnf in the user's home directory, if such file exists. All option files have the same format which consists of a section name followed by options. All valid command line arguments can be specified in the option file. For example to enable automatic login your .my.cnf file should look like this:
# the following section will be read by *all* client programs
[client]
user=username
password=secret
database=database_name
Of course, when creating this file make sure it is only user readable! Now you can run the batch query with a more simple command which is suitable for non-interactive jobs, e.g., scheduled with crontab:
mysql 
And most certainly you can now launch an interactive session with this compact command:
mysql 
If you need to connect to a different databases or use another user names it is always possible to override the settings of the option file on the command line.
Another advantage of using user's option file is that it is very easy to create an ad-hoc MySQL clients that can be automatically initialized with user's credentials and other private settings. Here's how you can do it in Python with the help of the standard module ConfigParser:
import  ConfigParser
config = ConfigParser.ConfigParser()
# read ~/.my.cnf if it exists but don't raise error if it doesn't 
config.read(['site.cfg', os.path.expanduser('~/.my.cnf')])
if config.has_section("client"):
  # got options for MySQL clients
  if config.has_option("client", "password"):
    # got default password
    pass = config.get("client", "password")
This Tech Tip comes from Sirgey in Ontario, Canada. Thank you, Sirgey!
Instant fame is easy at Linux Journal. Just send us your useful Tech Tips to share with the Linux Community, and we'll send you a cool t-shirt for your efforts!
Please note: Tech Tips featured in this specific section of LinuxJournal.com are kindly brought to us by readers and are not necessarily tested by LinuxJournal.com editors.