mysql_install_db: How to set the root password

Posted on

Question :

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)'

Answer :

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'

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.

Leave a Reply

Your email address will not be published.