up vote 2 down vote favorite
share [g+] share [fb]

It's not the Why? that matters. It's the How can I do it myself?

I'm using Ubuntu 10.04 LTS Server. I just want to install a MySQL server, but for some reason it wont let me. When I run apt-get install mysql-server everything works fine. At the end of the installation a modal window pops up an asks me to enter the root password. The only oddity here is that the encoding of the special chars (like the border of the modal window) is a bit messed up. But I assume that this isn't the major problem.

At the end of the installation MySQL starts automatically and everything works fine, until I try to login. mysql -u root -p prompts me to Enter password:. I enter the password I used during the installation and get this error: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) When I try it with an empty password I get the same error, except of the (using password: YES) part which turns to (using password: NO).

I did some Google research and already tried all that mysqld_safe --skip-grant-tables & thingy.

I also checked the mysql user table. The only entry is the debian-sys-maint user.

mysql> SELECT * FROM user;
+-----------+------------------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| Host      | User             | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections |
+-----------+------------------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| localhost | debian-sys-maint | *A632698FAB70686111EB0BE3F732A4C244B6DE8C | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            |          |            |             |              |             0 |           0 |               0 |                    0 |
+-----------+------------------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
link|improve this question
25% accept rate
It does create a root account. You should change the title of your question. – MDMarra Jan 21 at 23:52
When I used this mysqld_safe --skip-grant-tables & thing and got into the MySQL user table it only showed me the debian-sys-maint, so I guessed a root user wasn't created. If I'm not right, feel free to correct me. – silvinci Jan 21 at 23:56
The encoding of the window is a mess if you're using a terminal not in synch with the configuration of the linux box. If I use puTTY and configure translation to UTF-8, make sure LANG in Ubuntu is set to en_US.UTF-8 and TERM to screen it looks "good" for me. Otherwise you might have to play around with these variables LANG=en_US, TERM=vt100 for example. Try to see what works for you! To try out if you got the charset right you can test the dialogs with: whiptail --yesno oink 10 20 for example. – Mattias Ahnberg Jan 22 at 0:08
Thank you, Mattias! I configured PuTTY and now everything looks good. But the login problem is (of course) still left. – silvinci Jan 22 at 0:12
@silvinci I didn't realize you actually looked at the user table. Sounds like your install didn't complete 100% successfully. – MDMarra Jan 22 at 0:20
feedback

3 Answers

up vote 2 down vote

If there really is no root user when you look at the user table, then something went wrong during your install. The first thing to try is

sudo dpkg-reconfigure

If that does't work, then you can try 

INSERT INTO user VALUES ('localhost','root',password('newpassword'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0);
INSERT INTO user VALUES ('127.0.0.1','root',password('newpassword'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0);
INSERT INTO user VALUES ('ebuntu','root',password('newpassword'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0);
flush privileges;
quit
killall mysqld
/etc/init.d/mysql start

Second set of commands shamelessly stolen from here

link|improve this answer
ERROR 1136 (21S01): Column count doesn't match value count at row 1 I guess there are too many 'Y'? – silvinci Jan 22 at 0:19
I corrected the 'Y' count and am going to test it now. – silvinci Jan 22 at 0:26
Yeah! Celebration! You gave me the right hint. I'll put an answer together. – silvinci Jan 22 at 0:34
feedback
up vote 1 down vote

If debconf asks you for a password for a root user is because it tries to create the root account. If it doesn't is because there's an error, either on your system or in the packaged version you are using.

I don't have an ubuntu to check it, but you should look for similar issues on launchpad.

link|improve this answer
feedback
up vote 1 down vote

Okay. Since we really have no root user, we need to make us one. MySQL creates maintaining user by default. It is called debian-sys-maint. You can find its password in /etc/mysql/debian.cnf.

  1. start mysql - Start MySQL the Upstart way, if you haven't done so far.
  2. vi /etc/mysql/debian.cnf - You'll see a configuration file with some variables. We're looking for password = ****************.
  3. mysql -u debian-sys-maint -p - You will be prompted to enter a password. Enter the password we just found out. Don't wonder, you won't see anything while you are typing. Hit Enter. Bazinga! We're in!
  4. use mysql - Tell MySQL that we want to edit its own configuration database.
  5. SELECT * FROM user - Just for testing. The only row you should see is the debian-sys-maint, the user you are using currently using. If you see more than one row in there you should stop immediately and leave a comment, so you don't break more than you fix.
  6. INSERT INTO user VALUES ('localhost','root',password('YourNewPasswordGoesHere'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0); - Run this SQL-Query. It will insert the user root and grant it all permissions. Don't forget to change 'YourNewPasswordGoesHere' to your desired password.
  7. Do you get an error?
    • ERROR 1136 (21501): Column count doesn't match value count at row 1 - Looks like we're having too many or too less values for our new root user. Check the SELECT * FROM user command and make a copy of debian-sys-maint's values. You'll just have to fix the amount of 'Y', '' and 0.
    • A different error - Stop immediately! Let us know more in the comments.
    • Query OK, 1 row affected (0.00 sec) - That's no error. That's pure success! You can prove it by SELECT * FROM user;. Continue with the next step.
  8. quit - Close your current MySQL session. We're done here.
  9. stop mysql and then start mysql - The changes need a restart to take effect.
  10. mysql -u root -p - Enter your new password when you're prompted to. Hit Enter.
  11. You're done!

If you're having any trouble, let us know and post comment.

I'd like to thank MDMarra who gave me the necessary hint!

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.