mysql_install_db: How to set the root password

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 1

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'

• murrayc: 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: Many thanks for the update. I might never have figures out that it needed the --protocol option.

Answer 2

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.

• Mat: Try to run that from where/in what directory? What error is that supposed to solve?
• zhuhongk: basedir of mysql.
• Mat: Please edit that into your answer. Also I'm not sure I understand your second sentence. Could you clarify? Thanks.