31

Taking over a Debian Etch web server with MySQL running.

I usually start, stop and restart msyql using:

/etc/init.d/mysql restart

For some reason on this set up I get the following:

:~# /etc/init.d/mysql stop

Stopping MySQL database server: mysqld failed!

The mysql process is running fine:

:~# ps aux | grep mysql 
root      2045  0.0  0.1   2676  1332 ?        S    Jun25   0:00 /bin/sh /usr/bin/mysqld_safe
mysql     2082  0.6 10.7 752544 111188 ?       Sl   Jun25  18:49 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock
root      2083  0.0  0.0   1568   504 ?        S    Jun25   0:00 logger -p daemon.err -t mysqld_safe -i -t mysqld
root     11063  0.0  0.0   2856   716 pts/0    S+   17:29   0:00 grep mysql

I'm sure there are some really easy way to do it but I want to understand what is going on as well. Why is the typical way not working for me?

EDIT UPDATE as an update:

JBRLSVR001:/var/log/mysql# mysqladmin shutdown
JBRLSVR001:/var/log/mysql# dpkg --list mysql\*
Desired=Unknown/Install/Remove/Purge/Hold
| Status=Not/Installed/Config-files/Unpacked/Failed-config/Half-installed 
|/ Err?=(none)/Hold/Reinst-required/X=both-problems (Status,Err: uppercase=bad)
||/ Name                                         Version                                      Description
 +++-============================================-============================================-========================================================================================================
un  mysql-client                                 <none>                                       (no description available)
un  mysql-client-4.1                             <none>                                       (no description available)
ii  mysql-client-5.0                             5.0.32-7etch8                                mysql database client binaries
ii  mysql-common                                 5.0.32-7etch8                                mysql database common files (e.g. /etc/mysql /my.cnf)
un  mysql-common-4.1                             <none>                                       (no description available)
ii  mysql-server                                 5.0.32-7etch8                                mysql database server (meta package depending on the latest version)
un  mysql-server-4.1                             <none>                                       (no description available)
ii  mysql-server-5.0                             5.0.32-7etch8                                mysql database server binaries

mysqladmin shutdown does work but i'm still curious why the /etc/init.d/mysql commands aren't working.

1
  • For me the issue was that handmade installation looked for /tmp/mysql.sock instead of /var/run/mysqld/mysqld.sock. So the script by Debian maintainers was issuing an error, silently. You just have to fix socket= in the /etc/mysql/debian.cnf
    – Yvan
    Jan 12, 2015 at 18:35

11 Answers 11

26
mysqladmin shutdown

should work to shutdown the server.

I see two likely possibilities:

  1. MySQL has a problem and is refusing to shut down for some reason.
  2. The previous admin did something strange. Either modified the init.d script or didn't bother using the Debian packages at all to install MySQL.

What does dpkg --list mysql\* say?

What does /var/log/mysql.err say? Or the other mysql logs?

EDIT:

So mysqladmin shutdown worked?

According to that, the mysql-server package is installed (mysql-server-5.0; the mysql-server package is probably just a stub). So they may have installed over it? Running debsums mysql-server-5.0 might tell you more. dpkg --listfiles mysql-server-5.0 could help, too...

What's actually in /etc/init.d/mysql? I haven't checked that specific version of the package, but it should try to use mysqladmin shutdown ... Maybe you're lucky and they only broke that...

3
  • cheers, added some more information to the post. Jun 27, 2009 at 17:09
  • I suspect they didn't use the Debian packages to install mysql Jun 27, 2009 at 17:10
  • somebody used a Debian package once, at least. They may have compiled from source and overwrote the actual files, or broke it some other way...
    – freiheit
    Jun 27, 2009 at 17:30
22

Why this is happening

This is a common problem if you do a mysql import and overwrite the mysql database itself, such as when you might be restoring from a mysqldump -A backup.

This is a good thing: you probably want to back up all your mysql users, permissions, etc -- but it can wreak havoc with things like the debian-sys-maint user used to cleanly shutdown mysql.

Although this new database will possibly change both the root password and the debian-sys-maint password, of course it won't automatically change the expected debian-sys-maint password in /etc/mysql/debian.cnf. In fact, unless you also backed up that file, you probably don't even know what that password is anymore!

Resetting the mysql root password (optional)

First things first. If the mysql root password was different between old and new servers, you can use mysqladmin to fix it:

mysql -p -u root password 'newpassword'

However, when you apt-get installed mysql-server, it probably prompted you for the new mysql root password and you probably used the same one that you were using from before.

Fix the debian sys maint password.

So now look up the debian sys maint password that debian created for you when you installed it on the new server. (You need sudo because this should be a highly protected file.)

sudo cat /etc/mysql/debian.cnf

Now, log in to mysql using the root password you set above:

mysql -p -u root   # use your new password when prompted

Reset the password for the debian-sys-maint user and don't forget to flush privileges:

>  SET PASSWORD FOR 'debian-sys-maint'@'localhost' = PASSWORD('samepassword');
>  FLUSH PRIVILEGES;
>  QUIT

Test to be sure it works:

sudo /etc/init.d/mysql restart

Quick tip

If you ever need to reset the root password for the server without having to bring down the server, this user account has the authority to do it -- just cat the debian.cnf file and log in with that user. N.B. Protect this user account just like root.

3
  • 2
    Thank you, Jamieson, this potentially saved me hours of work.
    – slhck
    Jan 23, 2013 at 21:11
  • 1
    Awesome, and totally welcome! I've had to fall back on that file more often than I'd like to admit ;) Nov 9, 2014 at 19:22
  • 1
    Brilliant answer. Importing a database dump (and with it, the root password) was exactly the reason in my case. The only difference is that my debian.cnf file used the mysql "root" user instead of "debian-sys-maint". I simply put in the root password in debian.cnf and now I can control mysql via "service mysql <command>". Feb 1, 2019 at 11:20
9
pkill mysql

will definitely work

1
  • 1
    This is what my solved my question "stopping mysql database server mysqld failed". You should get a million points! Feb 13, 2016 at 7:45
6

2 more hints:

sh -x /etc/init.d/mysql restart

This will show you the commands executed by the init script.

install the package debsums, and you can test which packages were modified (verify is also available for RPM, but IMHO works better).

1
  • 1
    I had the same problem as OP and running this command suggested "Access denied for user 'debian-sys-maint'@'localhost'", which was absolutely correct: my mysql database had not yet assigned any permissions, so mysql stop did not have the permissions in the database itself to shut down. A manual mysqladmin shutdown worked perfectly.
    – jevon
    Dec 6, 2011 at 22:38
2

You could technically end it with:

pkill -9 mysqld

But you might lose data?

You might be better off asking someone at http://www.serverfault.com

1
  • I'm aware of doing it that way but defo not what I'm looking to do regularly. Jun 27, 2009 at 16:41
2

Assuming the package is somewhat strange the problem could be the pid file. I suspect that the new packages or compiled install did not create /var/run/mysql/ or whatever is standard on Debian for the pid file to be written to or the init script is looking for the mysqld.pid file in another place. If you can fix the init/pid file mismatch things should probably work.

1
  • The init script doesn't use the pid file to stop the server. Jun 27, 2009 at 22:39
2

The mysql shutdown script uses the debian-sys-maint user to run 'mysqladmin shutdown', by reading the password for the user from /etc/mysql/debian.cnf. You should check that this file exists, and that you can run mysqladmin shutdown as this user.

1

Using "pkill mysql" will also likely lose you data, particularly if invoked as "pkill -9" :(

I'd also recommend using 'sh -x' to see what the problem with the init script might be, and you can also go peek in the error logs for MySQL (/var/log/mysql or /var/lib/mysql, depending on config) to see if it is stuck on a really long running query or something and thus not willing to quit gracefully quite yet.

1

To follow up the comment on your question, I'll write down a full answer:

The issue is that the default socket is /tmp/mysql.sock with MySQL source, and /var/run/mysqld/mysqld.sock with Debian binaries.

The solution is to fix the socket path in /etc/mysql/debian.cnf, by providing the good socket=. Or by keeping it, but then change the one in /etc/mysql/my.cnf.

Here's how I found this out: in /etc/init.d/mysql when there the «failed» message, you have this line called:

echo -e "$ps_alive processes alive and '$MYADMIN ping' resulted in\n$ping_output\n" | $ERR_LOGGER -p daemon.debug

This pointed me to $MYADMIN ping, which is mysqladmin --defaults-file=/etc/mysql/debian.cnf ping. Running this very command ends on:

/usr/bin/mysqladmin: connect to server at 'localhost' failed

error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)'

Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!

So I had a look at /etc/mysql/debian.cnf and found out that was the bad socket.

0

use the following command :

$mysqladmin shutdown

this should be available in /usr/bin directory in your case.

0

you need to be a super user to start stop mysql (and most other services) on debian.

Not sure if you are already or not ... if not, you need to do one of

  • log in as root
  • put sudo before your /etc/init.d/mysql restart command (it'll ask you for your password, and you will need to be in the sudoers group)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .