Linux – Can’t grant MySQL privileges to Debian system maintenance user

debianlinuxMySQLpermissions

I have a dedicated Debian 7 server running multiple websites with MySQL as the database server. When I tried to install the latest updates, MySQL couldn't be updated because the server failed to stop.

I found someone with similar symptoms in this question: https://superuser.com/questions/268053/debian-cant-stop-mysql-permissions

The answers to the above question suggest granting full privileges on all databases to 'debian-sys-maint'@'localhost', but when I try to grant the privileges I get an access denied error.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY '...';
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost';
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Another weird thing is I now have two root users in my mysql.user table. Is this normal or could this be related to my problem? The localhost.localdomain one doesn't even have a password hash. I also have one user for each site I run but I left them out for clarity.

+-----------------------+------------------+
| Host                  | User             |
+-----------------------+------------------+
| localhost             | debian-sys-maint |
| localhost             | root             |
| localhost.localdomain | root             |
+-----------------------+------------------+

Edit: Running dpkg-reconfigure mysql-server says this:

xxx@yyy:~$ sudo dpkg-reconfigure mysql-server
/usr/sbin/dpkg-reconfigure: mysql-server is broken or not fully installed

Searching by that error led me to a forum post suggesting there may be older packages messing things up, but to me it looks like that's not the case here:

xxx@yyy:~$ sudo dpkg --get-selections | grep mysql
dovecot-mysql                                   install
libdbd-mysql-perl                               install
libmysqlclient18:amd64                          install
mysql-client-5.5                                install
mysql-common                                    install
mysql-server                                    install
mysql-server-5.5                                install
mysql-server-core-5.5                           install
php5-mysql                                      install

Best Answer

I have three(3) posts in the StackExchange that quickly explain the root cause

Apr 24, 2014 : mysql: Restore All privileges to admin user
Aug 07, 2013 : MySQL - ERROR 1045 (28000): Access denied for user : permission issue
May 01, 2013 : Can I find out what version of MySQL from the data files?

ROOT CAUSE

You have 37 columns in mysql.user. That's OK for MySQL 5.0
You are running MySQL 5.5. You are supposed to have 42 columns.

SUGGESTION

You will have to run MySQL command

# mysql_upgrade --upgrade-system-tables

Keep in mind that you are leaping two versions of MySQL. Backup the mysql.user table before doing so

UPDATE 2015-02-10 10:59 EST

Based on your comment, I can only suggest one thing: Refer to my post Any known issues upgrading from MySQL 5.1.73 to 5.6.21? under the section "ISSUE #1 : Upgrade Path" on how to upgrade two versions of MySQL.

UPDATE 2015-02-11 12:26 EST

Here is your situation

You have a mysql.user that is MySQL 5.0 compliant
You have to get mysql.user upgraded to work with MySQL 5.5

I have a crazy idea: Add the missing 5 columns by hand

In my DBA StackExchange post Cannot GRANT privileges as root, I display all the columns in the mysql.user table from MySQL 5.6, 5.5, 5.1, 5.0 and 4.x.

From those displays, we will do the following:

Make a backup of mysql.user beforehand as mysql.user_backup
Construct mysql.user to have the same columns as MySQL 5.5, adding the five(5) missing colunms.
Make sure the columns are in the same order
Fill in the missing privs for root users with Y
Deploy new privs

Steps to repair mysql.user

CREATE TABLE mysql.user_backup LIKE mysql.user;
INSERT INTO mysql.user_backup SELECT * FROM mysql.user;
ALTER TABLE mysql.user
    ADD Event_priv enum('N','Y') DEFAULT 'N' AFTER Create_user_priv;
ALTER TABLE mysql.user
    ADD Trigger_priv enum('N','Y') DEFAULT 'N' AFTER Event_priv;
ALTER TABLE mysql.user
    ADD Create_tablespace_priv enum('N','Y') DEFAULT 'N' AFTER Trigger_priv;
ALTER TABLE mysql.user
   ADD plugin CHAR(64) DEFAULT NULL AFTER max_user_connections;
ALTER TABLE mysql.user
    ADD authentication_string text DEFAULT 'N' AFTER plugin;
UPDATE mysql.user SET
    Event_priv='Y',
    Trigger_priv='Y',
    Create_tablespace_priv ='Y'
WHERE user='root' and host='localhost';
FLUSH PRIVILEGES;

If you want to revert it, you can do this

RENAME TABLE mysql.user TO mysql.user_old,mysql.user_backup TO mysql.user;
FLUSH PRIVILEGES;

GIVE IT A TRY !!!

Related Solutions

Debian MySQL User – What is the debian-sys-maint MySQL User?

What is debian-sys-maint used for?

One major thing it is used for is telling the server to roll the logs. It needs at least the reload and shutdown privilege.

See the file /etc/logrotate.d/mysql-server

It is used by the /etc/init.d/mysql script to get the status of the server. It is used to gracefully shutdown/reload the server.

Here is the quote from the README.Debian

* MYSQL WON'T START OR STOP?:
=============================
You may never ever delete the special mysql user "debian-sys-maint". This user
together with the credentials in /etc/mysql/debian.cnf are used by the init
scripts to stop the server as they would require knowledge of the mysql root
users password else.

What is the easiest way to restore it after I've lost it?

The best plan is to simply not lose it. If you really lose the password, reset it, using another account. If you have lost all admin privileges on the mysql server follow the guides to reset the root password, then repair the debian-sys-maint.

You could use a command like this to build a SQL file that you can use later to recreate the account.

mysqldump --complete-insert --extended-insert=0 -u root -p mysql | grep 'debian-sys-maint' > debian_user.sql

Is the password in /etc/mysql/debian.cnf already hashed

The password is not hashed/encrypted when installed, but new versions of mysql now have a way to encrypt the credentials (see: https://serverfault.com/a/750363).

Mysql – How does thesql determine the hostname of its clients

It uses a reverse DNS lookup. It takes the IP address of the client and uses whatever PTR record is returned for that name.

In my opinion doing authentication based on the name is not very useful at all, I suggest you consider using IP addresses instead.

See this document about how Mysql uses DNS.