MySQL root user missing, fresh installation

Question

I have a fresh installation of MySQL (5.0.75-0ubuntu10.5) which I installed using apt-get. During the setup I was prompted for my MySQL root password which I set. Ever since, I have tried logging in with the root account to no avail. I always get the message:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I have followed the instructions in order to reset the root password to something else. I noticed that the root user does not appear in the mysql.user table. The only user I have there is debian-sys-maint.

When starting the MySQL server manually, I get the following:

sudo /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
101110 13:08:59  InnoDB: Started; log sequence number 0 43655
101110 13:08:59 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.75-0ubuntu10.5'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)

When starting the MySQL server, the following appear in syslog:

Nov 10 13:11:49 spiros mysqld_safe[14705]: started
Nov 10 13:11:49 spiros mysqld[14709]: 101110 13:11:49  InnoDB: Started; log sequence number 0 43655
Nov 10 13:11:49 spiros mysqld[14709]: 101110 13:11:49 [Note] /usr/sbin/mysqld: ready for connections.
Nov 10 13:11:49 spiros mysqld[14709]: Version: '5.0.75-0ubuntu10.5'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
Nov 10 13:11:50 spiros /etc/mysql/debian-start[14745]: Upgrading MySQL tables if necessary.
Nov 10 13:11:50 spiros /etc/mysql/debian-start[14757]: Looking for 'mysql' as: /usr/bin/mysql
Nov 10 13:11:50 spiros /etc/mysql/debian-start[14757]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
Nov 10 13:11:50 spiros /etc/mysql/debian-start[14757]: This installation of MySQL is already upgraded to 5.0.75, use --force if you still need to run mysql_upgrade
Nov 10 13:11:50 spiros /etc/mysql/debian-start[14759]: Checking for insecure root accounts.
Nov 10 13:11:50 spiros /etc/mysql/debian-start[14763]: Triggering myisam-recover for all MyISAM tables

Ever after reseting the password multiple times and flushing privileges I still cannot login.

Any ideas?

Answer 1

I've been struggling with this for a few hours too, but Spiros' answer worked for me - afterwards I could login using

mysql -uroot -p [enter]

(meaning the password is blank - I then reset the root password by running

sudo dpkg-reconfigure mysql-server-5.1

which prompts for the root password. Then I could login as above with the root user and password. Very odd that it installed with no root user... (but it's working now, thanks Spiros!)

Answer 2

I had this same problem on a Debian install on a VPS (Gigatux). The command "dpkg-reconfigure mysql-server-5.1" would let me set/reset the root MySQL password but authentication would still fail with the error "Access denied for user 'root'@'localhost'"

Ultimately my issue was caused by not having the correct permissions on the /tmp folder when MySQL was installed. My /tmp folder had the permissions "drwxr-xr-x" when it should have been "drwxrwxrwt". Even after setting the correct permissions I could not fix the issue, I had to have the permissions set BEFORE I installed MySQL.

I have documented my experience in more detail at http://www.craigdodd.co.uk/2012/01/missing-root-account-in-mysql/ if anyone is interested.