More Blog Entries

MySQL Command Line Tricks - Pesky Passwords

Posted by John Ash on Thursday, April 22, 2010.


Though many GUIs exist for managing Mysql databases, my preference is still to use the powerful set of command-line utilities that come with it. The primary commands I use are:mysql, mysqladmin, mysqlshow, and mysqldump. I will write several blog entries on a number of personal tricks using these utilities that I have learned through the years.

After most mysql installs, there is usually a strong warning to set an initial root password. This is important to do... otherwise all your data and possibly even your entire server are vulnerable to attack.  Setting an initial root password can be done with:

$ mysqladmin -u root password newpassword
$ mysqladmin -u root -h hostname password newpassword

The problem is... once this password is set, most command-line mysql commands appear to fail, for example: 

 $ mysqlshow
 mysqlshow: Access denied for user 'root'@'localhost'; (using password: NO)

Why is this? Well... mysql commands default to using the currently logged in user (in this case, root) and an empty password.  But since we just set a root password for the server, we can no longer connect with an empty password.  The ugliest solution is to provide the password on each call.  For example:

 $ mysqlshow -password=sekrit_password

This is a pain to type, plus it's a slight security hole. Each time you type a command such as that, your password is recorded in your shell history (eventually stored in ~/.bash_history or similar place). Usually an up-arrow will retrieve these previous commands... password and all. You can even view commands immediately after a fresh login-from the last time (or last person) who was logged in.

A better, but still painful approach is to do something like:

 $ mysqlshow -p
 Enter password:
Now your password is not recorded in your command history, so we're more secure. But we still have to type that blooming password each time. Spare the fingers!

Wouldn't it be nice if we didn't need to type a password at all?! Well, here's how to do that:

First, you need to create a file called .my.cnf in your home directory. This file can contain default mysql settings. In the unix shell, ~ refers to your home directory, so you would type something like:

 $ vi ~/.my.cnf
My favorite editor is vi... but you may prefer a more intuitive editor like nano. The file should contain this:

 [client]
   user=mysql_user
   password=mysql_password

Notice that you can set a default user different from the currently logged in user. For example, maybe we want to default to always using the mysql user "joe" even when logged in as root. If you don't want to set a default user, but only set a default password you can leave off the user= line.

Try it out... and now all the mysql command-line commands magically work as well as they did before. Yay! Peaches and cream!

A word of caution: Before we merrily go on our way there should be something bothering you. We typed our password into a text file! That's typically not a good idea. What if someone else comes along and reads that file. Viola!& Now they have my password! Oof.

Using this safely:

  1. Do not set the mysql root password to be the same as your system root password! In fact, don't have any mysql user/password match a system user/password. This should be a security no-brainer... but you'd be surprised how many people fall for this.
  2. Protect your home directory. On Unix, Linux, or OS X machines, make sure that files in your home directory are only accessible by you. Unless you have some odd setup, this should do the trick:
    $ chmod 700 ~

Now we can enjoy all the power of Mysql command tools.


More Blog Entries

Post a comment


  • (not displayed)

© 2010 K3 Integrations, LLC | 888.524.6150 | software@K3integrations.com