CODEWITHSUNDEEP
mysqlshellcommand-linefreebsd
Cyclone
Cyclone

Reputation: 15269

Shell - one line query

I need to execute a mysql query in one line using bash.

It should be something like this:

mysql database --user='root' --password='my-password' < query.file

But instead of the < query.file it would like to use a raw query like this:

mysql database --user='root' --password='my-password' < UPDATE `database` SET `field1` = '1' WHERE `id` = 1111;

Is that possible?

Upvotes: 30

Views: 50371

Answers (6)

Nishant
Nishant

Reputation: 55866

Did you try

 mysql -u root -pmy_password -D DATABASENAME -e "UPDATE `database` SET `field1` = '1' WHERE `id` = 1111;" > output.txt

(the > output.txt part can be ignored but, it will be useful to see what was returned by the statement executed by looking at the file.)
Important: note that there should not be a space between -p and your password (my_password in the example)

Upvotes: 60

HappyCoder
HappyCoder

Reputation: 6155

If you are running this on a production environment, it would be better to login to the mysql shell first so as not to expose your db details.

Once logged into shell, why not just use a prepared.sql file?

mysql -u user -p

Next, enter your user password

Now you are logged into shell and you can run commands securely from here:

mysql> use DBNAME mysql> SOURCE file.sql

This is how I operate on the command line with my databases in order for my passwords not to appear in the logs.

Upvotes: -1

zainengineer
zainengineer

Reputation: 13889

I normally prefer Triple less then as its syntax and approach is similar to file redirect. Easy to go back in history and modify query

mysql database --user='root' --password='my-password' <<< "UPDATE `database` SET `field1` = '1' WHERE `id` = 1111"

It is called Here Strings in bash. You can find more about them here http://linux.die.net/abs-guide/x15683.html

It is useful when you want to pipe string to commands.

Upvotes: 1

Ian Mackinnon
Ian Mackinnon

Reputation: 14238

Writing your password in a command is generally a bad idea (people can read it over your shoulder, it probably gets stored in your shell history, etc.), but you can put your credentials in a file. Giving the file a name starting with . makes it hidden, which is also more secure.

# .db.conf
[client]
database=myDatabase
user=myUserName
password=myPassWord

Make sure only you can read the file:

chmod 600 .db.conf

Then call MySQL like so:

mysql --defaults-extra-file=.db.conf -e "UPDATE database SET field1 = '1' WHERE id = 1111;"

or:

echo "UPDATE database SET field1 = '1' WHERE id = 1111;" | mysql --defaults-extra-file=.db.conf

Note that --defaults-extra-file needs to be the first option supplied to mysql otherwise it freaks out.

Upvotes: 1

Some programmer dude
Some programmer dude

Reputation: 409196

Use echo and a pipe:

echo "UPDATE `database` SET `field1` = '1' WHERE `id` = 1111;" | mysql database --user='root' --password='my-password'

Upvotes: 6

user647772
user647772

Reputation:

Use the -e option:

$ mysql -e "UPDATE ..."

Upvotes: 14

Related Questions