Results 1 to 8 of 8

Thread: Mysql probelm when trying to log in as root

  1. #1

    Default Mysql probelm when trying to log in as root

    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:

    Code:
    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:

    Code:
    $ 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:

    Code:
    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:

    Code:
    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:

    Code:
    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. #2
    Join Date
    Jun 2008
    Location
    UTC+10
    Posts
    9,686
    Blog Entries
    4

    Default Re: Mysql probelm when trying to log in as root

    Run mysql_secure_installation as root.

  3. #3

    Default Re: Mysql probelm when trying to log in as root

    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. #4
    Join Date
    Jun 2008
    Location
    Finland, European Union
    Posts
    1,879

    Default Re: Mysql probelm when trying to log in as root

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


    Let's see what happens then.

  5. #5

    Default Re: Mysql probelm when trying to log in as root

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

    Joe

  6. #6

    Default Re: Mysql probelm when trying to log in as root

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

    Code:
    rm -fr /var/lib/mysql/*
    So I can understand just what happened?

    Joe

  7. #7
    Join Date
    Jun 2008
    Location
    UTC+10
    Posts
    9,686
    Blog Entries
    4

    Default Re: Mysql probelm when trying to log in as root

    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. #8

    Default Re: Mysql probelm when trying to log in as root

    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •