Login with inline password in command line
Normally we don’t need to use inline password to login or we don’t do that. But sometimes it is a must when you need to login from a bash script or when we need to execute some SQL Statements from bash script or run them in a cron. If you check MySQL manual they have just included the simple command :
shell> mysql –host=localhost –user=myname –password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb
But when I tried both of them it did not work and the reason seems that my password has special characters in it and it does not work even I put it in double/single quotes like this:
shell> mysql –host=localhost –user=myname –password=”my#some%pass@word” mydb
I try to search in the Google but there was no luck. Everywhere they were suggesting the same as official manual page itself says the same. When I tried myself several ways quoting the parameters and values separately, even that did not work. Finally while trying I ended up with using single quote for both parameter and the value as follows :
shell> mysql –host=localhost –user=myname ‘–password=my#some%pass@word’ mydb
But I did not quote any others but only password section.
Problem: Executing the SQL Statement from Command line itself
This is simple once we know the parameter of mysql command. The parameter –execute is used to execute the SQL statements from command line. But this again needs the inline password to work.
mysql –user=root ‘–password=sdfdsfsdfs’ –database=firsthelp –execute=”UPDATE table SET field=value WHERE 1″
Executing SQL Statement from Cron
Using the same above command we can run it from cron as follows:
30 * * * * mysql –user=root ‘–password=sdfdsfsdfs’ –database=firsthelp –execute=”UPDATE table SET field=value WHERE 1″
This will execute the statement in every half an hour. Or we can create a bash script and run it from cron.