mysql : access denied

English Applications
1

I’ve built a media box based on mythtv, built on Opensuse 11.3 32bit (a clean install) and the packman mythtv rpms (0.24.0).

I’m trying to set up mysql, but any attempts to run mysql fail with an ‘access denied’ error.

mythtv@pippin:~> mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
mythtv@pippin:~> mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
mythtv@pippin:~>

I have not created a root password for mysql - I can’t gain access to even do that.

I’d really appreciate some assistance to get this sorted.

Paul

2

As root, run

mysql_secure_installation

This will prompt you for a root password and also take some steps to secure your installation.

3

Thanks, Ken.

I’ve just tried running mysql_secure_installation.

Unfortunately, I am denied access to proceed with the script, after being prompted for the root user password, and pressing enter for none.

4

Be sure you run it as root from the CLI. If it doesn’t work you may have entered an initial password inadvertently at some point in the installation.

I would suggest shutting down mysql, deleting /var/lib/mysql (provided you have no databases to preserve), reinstalling the package, then try again.

If you had some old databases on the machine, installing mysql would not have deleted them, and the database also contains (mysql) root’s password.

5

Success. Deleting /var/lib/mysql and re-installing was required. Thanks again. Paul

6

Well, good that the OP is back with a working MySQL, but the elegant way to do it would have been:

  1. Stop mysqld and restart it with the --skip-grant-tables option. This enables anyone to connect without a password and with all privileges.

  2. Connect to the mysqld server with this command:

shell> mysql
  1. Issue the following statements in the mysql client. Replace the password with the password that you want to use.
mysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
mysql> FLUSH PRIVILEGES;

The FLUSH statement tells the server to reload the grant tables into memory so that it notices the password change.

You should now be able to connect to the MySQL server as root using the new password. Stop the server and restart it normally (without the --skip-grant-tables option).

7

Yes, this is documented in the MySQL doco site and is the way to do it if the DB contains data you want to retain.

8

The first time you can connect to mysql without the -p option, since you have not yet set a password for ‘root’:


mysql -u root

Once in, you create the password for root, next time you have to use the -p option.

9

Ah, but therein lay the problem! I could not connect to mysql even once.

Possibly, as Ken suggested, I had inadvertently created a root password?

Anyway, I have “cut and saved” good info from this thread into my my little collection of memory joggers.

Thanks everyone!

Paul