mysql notes

Resolving: Access denied for user: 'root@localhost.localdomain'

If you connect to MySQL like this: mysql -u root -p -h 127.0.0.1 and get and error like:

ERROR 1045: Access denied for user: 'root@localhost.localdomain' (Using password: YES)

...then MySQL is probably picking up the wrong hostname from /etc/hosts. See if there is a line like: 127.0.0.1 localhost.localdomain localhost and change it to simply 127.0.0.1 localhost.

Resolving: ERROR bdb: unable to initialize mutex: Function not implemented.

You may see this on Debian systems upgrading to MySQL 4.1. It results from your server not having the Berkely database library files necessary to support that table type in MySQL. The BDB table type is not that common. So you can just skip support for it. Add a skip-bdb line to your my.cnf file (/etc/mysql/my.cnf on Debian and /etc/my.cnf on RedHat boxes).

Resolving: 'Client does not support authentication protocol requested by server'

If you get an error about client authentication version when connecting to MySQL then it may be because your server is using the new password format, while your mysql client (or PHP script, or JDBC code) is using the old password format. See: http://dev.mysql.com/doc/mysql/en/Application_password_use.html.

You can change a new password to an old password like this:

mysql> update user set password = old_password('passwordhere') where user = 'phpbb'; flush privileges;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select user, password from user;
+-------+-------------------------------------------+
| user  | password                                  |
+-------+-------------------------------------------+
| root  | *BA3E2F47E409A6ABA83D219D70631A02FE28539E |
| root  | *BA3E2F47E409A6ABA83D219D70631A02FE28539E |
|       |                                           |
|       |                                           |
| phpbb | 09a6e3834c6d11c9                          |
+-------+-------------------------------------------+
5 rows in set (0.00 sec)

In this case root has a new, longer password. While the phpbb user has the shorter, old password format. In PHP something like mysql_connect('localhost', 'phpbb', 'passwordhere') should now successfully create a connection.

Forgot your MySQL Password?

We all forget passwords at some point. Fortunately you can reset your MySQL password if you forget it.

Add a "skip-grant-tables" line to /etc/my.cnf under the mysqld section.

Restart MySQL: /etc/init.d/mysql* restart

Set your password to what you like: mysql -e "update user set password = old_password('newpassword') where user = 'root'" mysql

Remove the skip-grant-tables option from /etc/my.cnf.

Restart MySQL: /etc/init.d/mysql* restart

On Debian Etch, there is a good howto to reset your root password here:

http://www.debianadmin.com/recover-mysql-database-root-password.html

MySQL Not Listening On A Public IP

By default, MySQL (on Debian) doesn't listen on the public interface:

#netstat -l
tcp        0      0 127.0.0.1:3306          0.0.0.0:*
LISTEN     1768/mysqld

In /etc/mysql/my.cnf, comment out the bind line:

#bind-address           = 127.0.0.1

And restart MySQL. Then it will listen on the public interface, and you should be able to connect.

#netstat -l
tcp        0      0 0.0.0.0:3306            0.0.0.0:*
LISTEN     14134/mysqld

MySQL optimization links: