Run mysql commands in bash script without logging in or adding -u root to every command

Question

I'm writing a bash script to do some db stuff. New to MySQL. I'm on Mac and have MySQL installed via homebrew.

Am using username "root" right now and there isn't a pw set. I included the pw syntax below just to help others out that may have a pw.

My goal is to have mysql commands be as "clean" as possible in my bash script

Not a hige deal, but would like to do this if possible.

Example

# If I can do it without logging in (*ideal)
mysql CREATE DATABASE dbname;

# Or by logging in with - mysql -u root -pPassword
CREATE DATABASE dbname;

# Instead of
mysql -u root -pPassword -e"CREATE DATABASE dbname";

Tried to simplify it. I have a handful of things I gotta do, so would rather keep my code cleaner if possible. I tried logging in with the bash script, but the script stopped once logged into MySQL and didn't run any commands.

Another option I was considering (but don't really like) would be just to keep username and pw string in a var and call it for every commmand like so

# Set the login string variable
login_details="-u root -p password -e"

# example command
mysql $login_details"CREATE DATABASE dbname";

So any ideas?

score 13 · Accepted Answer · 2016-10-10 05:54:11Z

13

Write a new bash script file and run this file after putting all your commands into it. Don't forget to give right username and password in your bash script.

For bash script:

#!/bin/bash
mysql -u root -pSeCrEt << EOF
use mysql;
show tables;
EOF

If you want to run single mysql command.

mysql -u [user] -p[pass] -e "[mysql commands]"

Example:

mysql -h 192.168.1.10 -u root -pSeCrEt -e "show databases"

To execute multiple mysql commands:

mysql -u $user -p$passsword -Bse "command1;command2;....;commandn"

Note: -B is for batch, print results using tab as the column separator, with each row on a new line. With this option, mysql does not use the history file. Batch mode results in nontabular output format and escaping of special characters. -s is silent mode. Produce less output. -e is to execute the statement and quit

edited Oct 10, 2016 at 5:54

answered Oct 10, 2016 at 5:42

user4398985

Sign up to request clarification or add additional context in comments.

2 Comments

John_911 Over a year ago

Cool got your first method working. Thanks! By any chance do you know how to echo or print a string to the command line while in mysql?

user4398985 Over a year ago

Good to hear that it worked nicely for you. Yes follow this post stackoverflow.com/questions/8147834/… if this doesn't work try the suggestions. Let me of any help 👍

Arshana Atapattu · Accepted Answer · 2024-02-12 06:57:38Z

0

Thanks for "user4398985" answer, that helped me a lot. I also like to add some additional things to his answer.

mysql --defaults-extra-file=<cred_file> -P 3306 --database=<database> -Bse "command"

This way you can execute mysql commands without showing or typing password in the script and point your sql command to a specific database.

In here <cred_file> is the file that contains your credentials. <database> is the specific database that you want to point your code.

Ex for cred_file:

[client]
user=<user>
password=<password>

answered Feb 12, 2024 at 6:57

Arshana Atapattu

511 gold badge1 silver badge4 bronze badges

Collectives™ on Stack Overflow

Run mysql commands in bash script without logging in or adding -u root to every command

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related