MySQL on Debian and Ubuntu

Debian Linux (the underlying foundation of Ubuntu) manages the startup/shutdown of MySQL quite differently from the ways I am used to. I am a long-time user of both the MySQL binary provided by Red Hat/SuSE (along with Fedora and clones like CentOS and Oracle Enterprise Linux) and the official binary from mysql.com. After the successful restore of a cold backup, I started mysqld using the Debian provided init script. The script said that mysqld failed to start up, but in reality it did start up. Similarly, stopping mysqld fails.

The output below demonstrates the outputs and the behavior seen on a Ubuntu 8.04 LTS Server:

root:~# ps -ef | grep mysqld
root     20165 19926  0 15:12 pts/4    00:00:00 grep mysqld
root:~# /etc/init.d/mysql start
 * Starting MySQL database server mysqld                                            [fail]
root:~# ps -ef | grep mysqld
root      9291     1  0 13:19 pts/0    00:00:00 /bin/sh /usr/bin/mysqld_safe
mysql     9333  9291  0 13:19 pts/0    00:00:00 /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      9334  9291  0 13:19 pts/0    00:00:00 logger -p daemon.err -t mysqld_safe -i -t mysqld
root      9805  9115  0 13:23 pts/0    00:00:00 grep mysqld
root:~#

root:~# /etc/init.d/mysql stop
 * Stopping MySQL database server mysqld                                            [fail]
root:~# ps -ef | grep mysqld
root      9291     1  0 13:19 pts/0    00:00:00 /bin/sh /usr/bin/mysqld_safe
mysql     9333  9291  0 13:19 pts/0    00:00:00 /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      9334  9291  0 13:19 pts/0    00:00:00 logger -p daemon.err -t mysqld_safe -i -t mysqld
root      9805  9115  0 13:23 pts/0    00:00:00 grep mysqld
root:~#

With the MySQL server provided by Red Hat/SuSE and MySQL.com, when using /etc/init.d/mysqld start, the init script invokes either mysqld_safe (or the older safe_mysqld under MySQL 4.x) or the newer mysqlmanager (which is starting to replace mysqld_safe in the newer 5.1 releases). As part of the start up procedure, the process ID (PID) of the mysqld process is written to a pidfile under /var/run which is later used to terminate mysqld. When /etc/init.d/mysqld stop is invoked, the init script reads the PID of mysqld process from the pidfile, and execute a kill on the process ID. The mysqld process receives the kill signal and shutdowns cleanly.

Side note: This means that you can run kill `cat /var/run/mysql.pid` to shutdown mysqld the way that the startup script does, though the script adds some cleanup work.

Under the Debian/Ubuntu distribution of the MySQL server, things are done differently. The package relies on a special mysql user for the init scripts called debian-sys-maint. The password for the user, which I have replaced with X in this blog post, is stored as a root-owned plain text file in /etc/mysql/debian-my.cnf:

root:~$ ls -l /etc/mysql/debian.cnf 
-rw------- 1 root root 312 2009-01-02 17:41 /etc/mysql/debian.cnf
root:~# cat /etc/mysql/debian.cnf
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = X
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
user     = debian-sys-maint
password = X
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr
root:~#

For the security minded, the password is generated randomly at the time the package is installed, and is unique (and in our example above, replaced with X). Therefore, each installation of the MySQL server on Ubuntu/Debian will have a different randomly generated password — even if you re-install on the same server. The mysql user debian-sys-maint@localhost is added by the Debian package, and has the following privileges:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, 
SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, 
SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION 
SLAVE, REPLICATION CLIENT
      ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY PASSWORD 
'PASSWORD_HASH_OF_X' WITH GRANT OPTION

When MySQL is started using /etc/init.d/mysql start, mysqld_safe is invoked as usual but adds three extra checks on the MySQL database. These checks being done are unique to Debian/Ubuntu:

1. upgrade_system_tables_if_necessary – This check runs the mysql_upgrade script with the username and password provided by the /etc/mysql/debian.cnf. Running mysql_upgrade multiple times does not harm anything since running it on an “upgraded” database does nothing. However, it does add to the time it takes to start up mysqld.
2. check_root_accounts – The script checks for the number of users with the username root that have no passwords. If there are such accounts, then an warning is written to the system log. This warns about open root accounts which may be exploited.
3. check_for_crashed_tables – This check is somewhat misleading in that while it suggests that it checks for crashed tables, in fact it only does it for MyISAM tables. It gets a list of all MyISAM tables from the information_schema and for each table does a select count(*) into @discard from database.table. If any tables are corrupt, the select will output an error; these are captured and emailed to the root user. In theory, this should be a relatively simple operation since MyISAM maintains row counts in the meta-data for each table, but in fact, if there are many tables, this may cause some I/O spikes on the server.

Stopping mysqld using the script provided by Ubuntu (and Debian) does not involve issuing a kill command to the mysqld process, even though the process ID is recorded in the start-up phase. Instead, the script runs the following command to shutdown MySQL.

mysqladmin --defaults-file=/etc/mysql/debian.cnf shutdown

When a backup is restored to a Ubuntu/Debian server, a problem can occur due to one of two possible scenarios. The first is that the the backup comes from another Ubuntu/Debian server that contains the debian-sys-maint user but has a different password from the current server. The second scenario is that the backup is from a distribution of MySQL which do not use the debian-sys-maint user. This assumes that the backups include the mysql database.

When MySQL is first installed on Ubuntu/Debian, this problem never occurs because the package as part of the post installation process randomly creates a password for the debian-sys-maint user, creates the user in MySQL (during the initial installation the root MySQL user has no password so is able to login as root), and creates the /etc/mysql/debian-my.cnf file on the system. When upgrading MySQL using the Ubuntu/Debian packages, the debian-sys-maint user is used, with the password already stored in /etc/mysql/debian-my.cnf. If the password is incorrect, the upgrade will succeed with some warnings but the user is not recreated automatically.

So what’s the solution? Well, there are ways to deal with it. One is to just ignore the error when using the init script to start MySQL and use mysqladmin to shutdown MYSQL using a user with the proper privileges. Another is to create the necessary debian-sys-maint user (or update the password for the user) in MySQL using the information in /etc/mysql/debian-my.cnf file.

I did not report this as a bug since is not one. Debian (and hence, Ubuntu) does things a little differently then others, and has repackaged the MySQL source differently.