Why can’t I stop the MySQL service on Debian?

Question

I just tried to upgrade from debian squeeze to unstable by replacing 'squeeze' with 'unstable' in /etc/apt/sources.list. The upgrade went smoothly except for MySQL, which failed because it couldn't stop MySQL.

/etc/init.d/mysql stop simply returns that it failed, but if I try to get the status with /etc/init.d/mysql status it gives me this error:

me@debian:~$ sudo /etc/init.d/mysql status
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'

MySQL is running fine, and I checked the permissions for debian-sys-maint in phpmyadmin and it's allowed to do everything, but only connect from localhost.

Answer 1

Try this:

1. sudo cat /etc/mysql/debian.cnf and look for the password listed under both the [client] and [mysql_upgrade] sections

2. mysql -u root -p password being the original MySQL root password

3. GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY '*the password obtained from step 1*';

4. /etc/init.d/mysql restart

That's the fix and this is the reasoning behind it, if you're interested.

Answer 2

An update to the answer. In step 3, I had to use

GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'the password obtained from step 1';

There are asterisks around the period after "ON". Worked for MySql 5.1

Answer 3

The MySQL Reference Manual says you can do this:

1. Log on to your system as the Unix user that the mysqld server runs as (for example, mysql).
2. Locate the .pid file that contains the server's process ID. The exact location and name of this file depend on your distribution, host name, and configuration. Common locations are /var/lib/mysql/, /var/run/mysqld/, and /usr/local/mysql/data/. Generally, the file name has an extension of .pid and begins with either mysqld or your system's host name.

You can stop the MySQL server by sending a normal kill (not kill -9) to the mysqld process, using the path name of the .pid file in the following command:

    kill `cat /mysql-data-directory/host_name.pid`

This part: cat /mysql-data-directory/host_name.pid returns the contents of the file, which is the process id.