Test MySQL credentials from Linux command line?

Question

How do you test MySQL credentials from the command line on a Linux server?

Answer 1

@Phil's Answer and @Mr.Brownstone's Answer should suffice for your question, so +1 for both of them.

For the following, let's assume you are logging in with username myuser

Once you have connected to mysql, you should run the following query:

SELECT USER(),CURRENT_USER();

• USER() reports how you attempted to authenticate in MySQL
• CURRENT_USER() reports how you were allowed to authenticate in MySQL

Sometimes, they are different. This may give you insight into why you are allowed to login to mysql.

Here is another query you need to run:

SELECT CONCAT('''',user,'''@''',host,'''') dbuser,password
FROM mysql.user WHERE user='myuser';

This will show you the ways in which you are allowed to login as myuser.

If you see 'myuser'@'localhost', then you can authenticate from within the DB Server.

If you see 'myuser'@'127.0.0.1' and do not see 'myuser'@'localhost', then you can authenticate from within the DB Server again but you must specify the --protocol=tcp from the command line.

If you see 'myuser'@'%', then you can do remote logins from any server.

If you see 'myuse'r@'10.20.30,%', then you can do remote logins only the from 10.20.30.% netblock.

Once you see what 'mysql.user' has for your user, you may want to allow or restrict myuser from logggin in one way and not the other.

If you simply want to check if the password for myuser is whateverpassword, you can do the following:

SELECT COUNT(1) Password_is_OK FROM mysql.user
WHERE user='myuser'
AND password=PASSWORD('whateverpassword');

You can check from the command line as follows:

PASSWORDISOK=`mysql -uroot -p... -ANe"SELECT COUNT(1) Password_is_OK FROM mysql.user WHERE user='myuser' AND password=PASSWORD('whateverpassword')"`

If you are not root and want to test myuser only, you can do this:

PASSWORDISOK=`mysqladmin -umyuser -pwhateverpassword ping | grep -c "mysqld is alive"`

If you get 1, password for myuser is verified as good.

• Leland Hepworth: PASSWORD() function is deprecated after version 5.7.5. See also stackoverflow.com/questions/52320576
• RolandoMySQLDBA: @LelandHepworth This particular post is 8.5 years old.. Even though the PASSWORD() function is deprecated, millions still use the mysql_native_password plugin. That's why I have the formula for generating that password. See my old posts about it : dba.stackexchange.com/search?tab=votes&q=user%3a877%20UNHEX.
• RolandoMySQLDBA: In fact, i have a client right now that uses the new authentication plugin for MySQL 8.0 and still has 10's of thousands of servers with the old plugin (MySQL and MariaDB).
• Leland Hepworth: I found your answer very useful, but I don't use the mysql_native_password plugin, so I ran into that issue and had to do some extra research to make it work. I posted the comment to help out others in a similar situation. Now I will also need to check out the plugin.

Answer 2

mysql -h host -u user -p<whatever> -e"quit"

This allows you to use the same connection string that you use for programmatically sending queries to the server. You can add || exit 1 to the end to auto exit on invalid arguments. You may also want to redirect stderr to /dev/null if you do not like the auto generated MySQL error message.

• Leland Hepworth: you could even replace "quit" with simply ""

Answer 3

You can use the following command (assuming that you have mysql set up in your PATH):

mysql -h host -u user -p

Just replace host and user with the correct values and then you should be prompted for your password.

• Jake Wilson: I'm wanting to check programmatically. Like something that returns true or false.

Answer 4

mysql --user=user_name --password=your_password dbname

Is your question that simple, or is there a specific case you need to test?