Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

mysql 5.6 seems to have a new --random-passwords option for mysql_install_db, which lets me discover the root password: http://dev.mysql.com/doc/refman/5.6/en/mysql-install-db.html#option_mysql_install_db_random-passwords

But what can I do with mysql 5.5? The documentation suggests that the root password should be empty, but I cannot seem to connect with no password (or an empty password), so i cannot set a real root password:

For instance:

I initialize the database like so:

$ mysql_install_db --no-defaults --user=murrayc --datadir=/tmp/testmysql/data

I start the server like so:

$ mysqld_safe --no-defaults --user=murrayc --port=3308 --datadir='/tmp/testmysql/data' --socket='/tmp/testmysql/mysqld.sock' --pid-file='/tmp/testmysql/pid'

But when I try to set the root password like so (I believe that --user in this case refers to the mysql user, not the linux user):

$ mysqladmin --no-defaults --port=3308 --user=root --password='' password 'somenewpassword'

I get this error:

mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'
share|improve this question

2 Answers 2

up vote 4 down vote accepted

Upon installation, root@localhost does not have a password.

You should be able to connect without a password by just doing this:

$ mysql -uroot

Try the following:

$ mysqladmin --no-defaults --port=3308 --user=root password 'somenewpassword'

or you can do it the stubborn way:

$ service mysql restart --skip-grant-tables --skip-networking
$ mysql -e"UPDATE mysql.user SET password=password('somenewpassword') WHERE user='root'"
$ service mysql restart
$ mysql -uroot -p

UPDATE 2013-01-02 16:47 EDT

I am sorry, I overlooked the port number.

Here is the problem: you cannot use root@localhost against a non-3306 port unless you use the TCP/IP protocol. Please try this:

$ mysqladmin --no-defaults --port=3308 --user=root --protocol=tcp password 'somenewpassword'
share|improve this answer
    
No, that's the point. Login is not possible with no password: 10:41:12 ~$ mysql -uroot --port=3311 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) Commands such as "service mysql restart" are for the system's regular mysql process. I am initializing a separate database and starting a separate mysqld instance. –  murrayc Jan 2 '13 at 21:42
    
Many thanks for the update. I might never have figures out that it needed the --protocol option. –  murrayc Jan 3 '13 at 10:47

Try to run mkdir -p var tmp before mysql_install_db.

create mysql db failed in my situation.

If var and tmp are Not created previously and use them as the paras of mysql_install_db, we can start mysqld successfully. try to use mysql or mysqladmin connect to server, I got the err: access denied for 'root'@'localhost'

Maybe the question's cause is the same with mine.

share|improve this answer
1  
Try to run that from where/in what directory? What error is that supposed to solve? –  Mat Sep 11 '13 at 7:35
    
basedir of mysql. –  zhuhongk Sep 11 '13 at 9:33
    
Please edit that into your answer. Also I'm not sure I understand your second sentence. Could you clarify? Thanks. –  Mat Sep 11 '13 at 9:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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