Mysql probelm when trying to log in as root

English Applications
1

Guys,

Just installed 11.2 and am loving it. However, I need mysql working and have run into the following problem.

First I start up the server:



Chopper@chopper:~> sudo /etc/init.d/mysql start
root's password:
Starting service MySQL                                                                                                       done

Then I open up the command line and try to log in as the root user:


$ mysql -u root -p

Then, since I have yet set up the root password, I just press enter and I am logged into mysql and see the following:


Chopper@chopper:~> mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.36-log SUSE MySQL RPM

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

mysql>

As you can see, I don’t seem to have privileges to the mysql table with users and what not.

This is confirmed when I try to select the mysql database as the active database:


mysql> use mysql;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql'
mysql>

Anybody know why this is happening? I ran mysql in openSUSE 11.1 fine, and have it running on my ubuntu server as well.

FWIW, if I try to run the mysql_secure_installation script, my output is the following:


Chopper@chopper:~> mysql_secure_installation




NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!


In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

Set root password? [Y/n] y
New password:
Re-enter new password:
ERROR 1142 (42000) at line 1: UPDATE command denied to user ''@'localhost' for table 'user'
Password update failed!

Anybody?

Thanks,

Joe

2

Run mysql_secure_installation as root.

3

Sadly I have also tried this. If I needed/wanted to completely remove mysql and reinstall, is there anything mroe to it than just removing all the packages in YaST?

4

su -
rcmysql stop
rm -fr /var/lib/mysql/
rcmysql start
mysqladmin -u root password ‘rawr’*

Let’s see what happens then.

5

I will try this as soon as I get home. Thank you very much.

Joe

6

Worked just fine. Any chance you’d shed some light on what I’m deleting with this command:


rm -fr /var/lib/mysql/*

So I can understand just what happened?

Joe

7

Removing the mysql package does not remove the databases (for obvious reasons, there might be important info in it). So if the database was left behind from a previous install, or messed up in some way, reinstalling mysql doesn’t fix the problem.

Sometimes people mess up the database by starting mysqld as root manually. Maybe they are following some HOWTO they’ve read somewhere which is not really applicable. You’re not supposed to do that. You’re supposed to start mysql using the init script (or YaST if you don’t know about init scripts). Then mysqld can run as user mysql. Running mysqld as root can change the ownerships of the database files in such a way that you cannot administer the database, nor can you fix the problem by reinstalling. That’s why chrysantine suggested you clear the database and start from scratch.

8

Ah I see, I never stopped to think about whether or not the database gets removed when I remove mysql. Thank you guys so much for the help, I really appreciate it.

Joe