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?

Answer 1

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