Managing the mysql service.

On most servers you can start and stop the mysql service using commands like the following...

#To start mysql server:
/etc/init.d/mysql start
# To stop mysql server:
/etc/init.d/mysql stop
# To restart mysql server
/etc/init.d/mysql restart

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 = 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, there is a good howto to reset your root password here:

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

On Debian/Ubuntu you can use the debian maintenance user to gain access:
mysql -u debian-sys-maint -p"$(cat /etc/mysql/debian.cnf | grep password | sed 's/.* = //' | head -n 1)"

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

Set proper UTF-8 support in MySQL

A customer pointed out that mysql has some odd defaults for its languages settings. To resolve that add the following to /etc/my.cnf (in /etc/mysql/my.cnf on debian/ubuntu)...

[client]
default-character-set=utf8

[mysqld]
default-character-set=utf8
character-set-server=utf8

...and restart the mysql service.

There is also a lot of goodness about utf-8 support in the official MySQL documentation.

MySQL optimization links:

Several times I've had the following issue: the mysqld is the bottleneck, but there's plenty of free memory.  How to make it use more memory for cache? Here's some links:
http://www.databasejournal.com/features/php/article.php/10898_3110171_1
http://www.day32.com/MySQL/
http://drupal.org/node/85768