mysql is prompting for password even though my password is empty

Question

I installed mysql on ubuntu server and did not specify password. When I do

mysql -u root -p 

it prompts for password and without providing any input I just hit enter and it works.

Now I need to perform some operation on the database using cron job. My cron job does not work because mysql prompts for a password. I tried doing

mysql -u root -p'' my_database

but that did not work either.

Any suggestion?

Disarrange

Answer 1

Try not asking mysql to prompt for the password, 'mysql -u myuser'. I would suggest you create an account with only the required privileges to do this. Also limit its access to localhost. Put a password on root.

Ultimate

+1, This is technically the answer the user is looking for. To not have MySQL prompt for a password, don't set -p. – Unreadable

wuputah you are right. Don't ask me to set password when I don't want to set password. – Disarrange

I am suggesting you use good security. Running root without a password is high risk. At least limit access to the local system. Best practice for no password accounts is least access. – Ultimate

A ~/.my.cnf file with a password will override the lack of -p and use that password. When you need to connect to a different MySQL instance without a password then you need sneilan's answer. – Sepulture

Answer 2

Go like this mysql -u root --password="" dbname

Originality

This is the correct answer for the question. Yes question author and I and probably anyone looking at this thread know you shouldn't have empty passwords but the question was how to do it if we really want to have empty passwords and this works perfectly. NOTE: MYSQL version 5.5.25a – Broderick

Answer 3

I installed mysql on ubuntu server and did not specify password. When I do

mysql -u root -p

-p brings up the password prompt. If you do not have a password, then you do not want to do that.

Just write:

mysql -u root

---

^{For the love of god, get a password on that account!}

Waxler

Answer 4

For passing the password in the command use -p$PASSWORD. In the following example, user/password is root/root:

mysql -proot -D my-db -u root -h 127.0.0.1 -e "select * from my_table"

IMPORTANT: notice that there is no space between -p and the password in -proot

Protectionism

Answer 5

Check MySQL Documentation for how to reset your password, since I found no way to enter a password either. You could use the following: http://dev.mysql.com/doc/mysql-windows-excerpt/5.0/en/resetting-permissions-windows.html Which states that you have to create a file with the following query:

UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;

And then start up mysqld service with the --init-file parameter (see the documentation for more information about this). This should reset your root password.

Grad

MySQL does not distinguish between "no password" and "empty password". – Waxler

Answer 6

A password prompt also occurred even though the password apple was given as shown below:

mysql -u john -p apple

And:

mysql -u john --password admin

Then, the error below occurs:

ERROR 1044 (42000): Access denied for user 'john'@'%' to database 'admin'

So, I changed -p apple to -papple and --password admin to --password=admin respectively as shown below, then I could log in to MySQL:

mysql -u john -papple

And:

mysql -u john --password=apple

Volcano

Answer 7

Why don't you specify a password for root? For security reasons and your script would work.

Halmstad

I agree, just actually set a root password and then the specified code should work fine. – Chicago

Better yet, OP should create a non-root user and give access for new user to the DB in question. – Halmstad

Answer 8

Mysql's "root" account should have a password; otherwise anyone with an account on your machine has full access to the database.

1. Set a password (e.g. with SET PASSWORD)
2. Add the password to ~/.my.cnf

If you want more sane authentication options, I recommend Postgres.

Infusion