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
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
/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
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
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
mysqld_safe is invoked as usual but adds three extra checks on the MySQL database. These checks being done are unique to Debian/Ubuntu:
- upgrade_system_tables_if_necessary – This check runs the
mysql_upgradescript with the username and password provided by the
mysql_upgrademultiple 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
- check_root_accounts – The script checks for the number of users with the username
rootthat 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.
- 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_schemaand 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.
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
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
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.