Thesql_install_db: How to set the root password

MySQL

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

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

Related Solutions

MySQL – Differences Between mysqld_safe and mysqld

As we all know, mysqld_safe and mysqld are very different

mysqld : The database server instance daemon

mysqld_safe : Control program that examines and sets the environment for mysqld to execute. The mysqld executable is actually launched in a loop. When mysqld terminates, the mysqld_safe program will examine the return results and decide whether

mysqld terminated normally (intentional shutdown), leaves mysqld_safe
mysqld terminated abnormally (crash or kill -9 of mysqld)
- Loop back, mysqld fails on retry, leaves mysqld_safe
- Loop back, mysqld starts up, stays in the mysqld_safe loop

Why is it important to have mysqld and mysqld_safe using the same MySQL version?

Let me illustrate it this way: Percona Server sometimes has additional features in mysqld_safe for manipulating the OS. For example, I have seen numactl --interleave=all in a Percona Server mysqld_safe. If that line was not there, the mysqld for Percona Server may run into issues with memory and swapping.

The same scenario could possibly be the case for Oracle's (ugh, still hate saying that) mysqld and mysqld_safe. There could be improvements from one major release to another that would be removed if the mysqld_safe was older.

Rather than exploring the possibilities of using a old mysqld_safe and a new mysqld (or vica versa), please make your life simple and reinstall MySQL 5.5.30 from scratch.

Before doing so, please run

updatedb
locate mysqld_safe

in Linux and see if there are two lingering. If there are, get the paths straightened out. Otherwise, you may have to reinstall MySQL 5.5.30.

mysqladmin Not Taking Inline Password – Solution

Command line arguments are subject to interpretation by the system's command shell, changing the behavior of the command or changing the value of the arguments before they are passed into the called program.

When an argument (such as the value for --password) contains an character that the shell may interpret, they need to be either quoted (usually enclosed in single quotes ' in unix or double quote " in Windows) or escaped individually (usually with a backslash \ before each metacharacter) to avoid interpretation by the shell.

While the specific characters are system-specific, some characters to watch out for include:

$ & ! \ [ ] < > `

If the password, for a really bad example, were set to pa$$word ...

mysql --password=pa$$word     # does not work
mysql --password='pa$$word'   # works
mysql --password=pa\$\$word   # works, but slightly unclear what's going on at first glance

Further reading:

Update: to escape either ' single or " double quotes in the password, you can either escape them with a leading backslash, or enclose the entire argument in the opposite style of quotes if there are no other characters that the chosen style of quoting isn't compatible with.

mysql --password="like'this" # password has a single quote in the middle
mysql --password='like"this' # password with a double quote in the middle

If you have a single quote and other special characters as well, you're stuck with backslash escaping because, in unix, the double quote is "weaker" than a single quote and many metacharacters are still expanded when enclosed in double quotes but not single quotes.

This is not MySQL-specific but applies to anything with command line arguments.

You can typically use the echo command to see how the shell is interpreting your arguments.

$ echo foo$bar 
foo                # literal 'foo' plus the (empty) shell variable $bar

$ echo foo\$bar
foo$bar            # backslash prevents expansion of $bar as a variable

$ echo "foo$$bar"  # weaker double quote doesn't prevent expansion so
foo9691bar         # the $$ expands to the unix process id (pid) of the current shell

$ echo 'foo$$bar'
foo$$bar           # "stronger" single quote prevents shell expansion

$ echo "foo'bar"
foo'bar            # double quote allows single quote within the literal

Follow-up: The bash shell (and presumably some others) allows escaping single quotes within single-quoted strings, though the convention is bizarre (probably based on some long-forgotten decision lost now to the mists of time):

Replace each ' inside the string with '\'' before wrapping the entire string in single quotes... so the literal string foo'bar is expressed as 'foo'\''bar'.

Like I said, bizarre. This is needed because a backslash escapes a single quote outside of a single quoted string, a backslash escapes nothing inside a single-quoted string in bash, and single quoted strings can be closed and reopened by multiple single quotes as long as there are no unescaped intervening characters that have special meaning. So '\'' closes the quoting of the string, then supplies the escaped literal, then reopens the quoting of the string.

Best Answer

UPDATE 2013-01-02 16:47 EDT

Related Solutions

MySQL – Differences Between mysqld_safe and mysqld

mysqladmin Not Taking Inline Password – Solution

Related Question