My last post about Basic MySQL Security generated a number of interesting comments, thanks for all your feedback! I'd like to address a few points that were mentioned there:
While the problem seems to be a non-issue on Linux, Keith Murphy stated that the password might still be visible on other Unix operating systems (e.g. Solaris), as described in Bug#11952 in our bug database. According to the bug report, it depends on the implementation of "ps" — there seems to be a BSD variant (/usr/ucb/ps) as well as a SysV implementation (/usr/bin/ps).
However, on my tests on OpenSolaris (2008.11), both still displayed the password! So be aware of this when working on non-Linux systems and better double check the behaviour on your particular platform. The bug report provides a few more details about this issue, apparently it cannot be fixed for all platforms.
I also pointed out that the password will end up in your shell history and Jay Pipes emphasized this in his comment. As I wrote, you need to make sure that your shell history file is properly protected against access by other users! Usually, a chmod 600 ~/.bash_history will fix this. Most shells create these files with appropriate permissions automatically or can be configured to do so (check your shell's manual page with man `basename $SHELL`).
But there are more potential password leaks that I would like to mention, while we're on the topic: the mysql command line client maintains a history file of its own, that you should be aware of. The history is convenient for easily going back in your list of previous SQL statements by using the Up/Down cursor keys or searching for a particular query by using the CTRL+R shortcut. However, the MySQL client stores all your SQL statements in a file ~/.mysql_history in your home directory by default, similar to how your unix shell does it. So if you are adding new MySQL user accounts using the GRANT ... IDENTIFIED BY PASSWORD... statement, the user's password will be written to the history file in plain text, visible to everyone who has the appropriate file system privileges. Keep that in mind when performing administrative tasks on a MySQL server and make sure to restrict access to that file! By default, the client creates the file with only read and write permissions for the user (600), but if you want to be on the safe side you can of course remove it after you entered passwords on the MySQL command line. As an alternative, you can start the MySQL command line client by using the "-q / --quick" option, which skips using the history file for this particular session. If you can live without a command line history in general, you could simply replace that history file with a symbolic link to /dev/null:
$ ln -fs /dev/null ~/.mysql_history
Alternatively you can set the environment variable $MYSQL_HISTFILE to point to either a different file name or to /dev/null directly. By the way, all of this is documented in the mysql(1) man page as well as in the Reference Manual.
Another attack vector for local users to obtain MySQL passwords are the MySQL server log files — anyone with file system access to the binary log files can extract possible GRANT statements from there using the mysqlbinlog command! So you need to make sure that these files are properly secured from being accessed by regular users as well.
In general, the best approach is to not allow regular users to log into your MySQL Server system in the first place. Shell access should be restricted to the system's admin accounts, access to the MySQL server should strictly take place via the MySQL Client/Server protocol. Which, by the way, is not using encryption by default — make sure to use SSL or an SSH tunnel when accessing a MySQL server through an untrusted network. Otherwise you may also reveal confidential information like user passwords to unauthorized entities...