How can I check the mysql connection for a user/password using batch/shell script

Question

Hi,

How can I check the mysql connection for a user/password using batch/shell script?

I've user name and password, and I need to authenticate whether they are correct or not, but how?

I tried this way:

1. I've created a batch file "run.bat" with "mysql -u User--password=UserPassword < commands.sql"
2. "commands.sql" file contains "\q"
3. When I run the file "run.bat" the output is nothing when User/Password are correct and "ERROR 1045 (28000): Access denied for user ... " when User/Password are incorrect. Now can we capture this output, and decide whether the connection is successful or not, if yes how?

Regards

Answer 1

you could use a ".my.cnf" file

I do this, although id strongly recommend against using your mysql root login

[root@daaorc900c ~]# cat ./.my.cnf 
[client]
user=monitoruser
password=whatismonitor
[root@daaorc900c ~]# 

Looks like you might be one windows so here is the doc for the "options files" in widnows

http://dev.mysql.com/doc/refman/5.1/en/option-files.html

Answer 2

You can check the return status of mysql. It is stored in the ERRORLEVEL enviroment variable:

mysql -u User--password=UserPassword < commands.sql
if "%ERRORLEVEL%" EQU "0" (
  echo OK
) else (
  echo FAIL
)

If you are lucky, mysql.exe even returns a specific status for "logon failed" that you can react on. Most applications return 0 on success and something != 0 on failure. Use echo %ERRORLEVEL% right after a command to find out the current value.

A more advanced approach would be to capture and evaluate the STDERR stream of the application. This, however, would be material for a different question.

Answer 3

I found a solution as below:

@echo OFF
echo \q | mysql -u User --password=UserPassword 2>nul

if "%ERRORLEVEL%" == "0" (
  echo CONNECTION SUCCESSFUL
) else (
  echo CONNECTION FAILED
)