MySQL: users, passwords, CLI basics

Like PostgreSQL, MySQL has a concept of "users". In MySQL, users are stored in the mysql database in the user table. (In MySQL terms, this table can be referred to as mysql.user). One of the first things a MySQL administrator must learn to do is change MySQL user passwords. If you're a command-line kind of person (like me), and you know some SQL, the 'mysql' command is your friend.



MySQL users should not be confused with Unix users, by the way. Although they may be conceptually similar, they are not the same thing. For example, the MySQL user 'root' is a different entity from the Unix root user.

To set or change passwords of MySQL users, one must gain access to the 'mysql' command-line interface (CLI, SQL shell) as the 'root' user:

$ mysql -u root -h localhost -p

The -p option will cause MySQL to prompt for the root password. This is not necessary if you have the root password in /etc/my.cnf or ~/.my.cnf

Once at the mysql> prompt, do this:

mysql> use mysql;
Database changed
mysql>

As mentioned above, the mysql database contains a table, called user, containing all MySQL users and their passwords. The passwords are stored as hashes, not in plain text. Here's how you can see all the tables in the database:

mysql> show tables;

And here's how you can see the columns (fields) in the 'user' table:

mysql> show columns from user;

In case you want to see what database you are connected to:

mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

And to see what user you are connected as:

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

Now, you see it may not be necessary to give all the options given above for the mysql command. You can try just 'mysql' and then run 'select database();' and 'select current_user();' to see the defaults.

Anyway, let's move on showing and changing user passwords.

mysql> use mysql;
Database changed
mysql> select host,user,password from user where user='mumbojumbo';

Based on the results of that SELECT statement, you can proceed to actually change the password for 'mumbojumbo':

mysql> update user set password=PASSWORD('wildwoolypass') where user='mumbojumbo';

Just note that you can have multiple passwords for the same user, depending on what host they connect from, in which case it may be necessary to do:

mysql> update user set password=PASSWORD('wildwoolypass') 
    -> where user='mumbojumbo' and host='wild.wooly.net';
+-----------+------------+-------------------------------------------+
| host      | user       | password                                  |
+-----------+------------+-------------------------------------------+
| localhost | mumbojumbo | *3D9D1E792248C4ED327031F34D7D1F1988674A92 |
+-----------+------------+-------------------------------------------+
1 row in set (0.00 sec)