Create mySQL database through ssh in a bash script

Question

I want to create a database on a remote server through ssh with a bash script. Something like this

#!/bin/sh
ssh [email protected] 'mysql -h example.com -uroot -pMYPASSWD -e "CREATE DATABASE $1;"'

So that I in terminal can run it like

$ myBashScript nameOfNewDatabase

manpagez.com/man/1/mysqladmin gives details of options, e.g. --user=root --password=MYPASSWD etc. — ed.
– ed., Commented Oct 15, 2012 at 22:52
Got error bash: mysqladmin: command not found, got the same when I tried the one I wrote in desc, but mysql is installed. I'll check if mysqladmin is installed. — Spoeken
– Spoeken, Commented Oct 15, 2012 at 22:54
Mysqladmin is installed, but when I try to run the command remotely with ssh [email protected] "mysqladmin create $1" it gives me the error above. — Spoeken
– Spoeken, Commented Oct 15, 2012 at 22:57
Be aware that "mysqladmin" is not the local one, it's the one installed on the server. Try something like ssh [email protected] "/path/to/mysqladmin create $1" and forget about relying on your remote PATH settings .. — wh81752
– wh81752, Commented Jan 30, 2013 at 14:41

Wolfgang Fahl · Accepted Answer · 2012-10-16 08:00:52Z

3

The following script can remotely execute arbitrary commands

#!/bin/bash
# call mysql on a remote server to execute the given command 

# show usage and exit
usage() {
  echo "$0 server sql-command"  1>&2
    exit 1
}

# check number of arguments
if [ $# -lt 2 ]
then
  usage
fi

# get server name and sql command
server="$1"
sql="$2"

# copy command to a temporary file
tmpsql="/tmp/sql$$"
echo "$sql" > $tmpsql

# copy command file to server
scp $tmpsql $server:$tmpsql
# run command remotely with removing command file afterwards
ssh $server "mysql -uroot -pPASSWORD < $tmpsql;rm $tmpsql"
# remove local copy of command file
rm $tmpsql

answered Oct 16, 2012 at 8:00

Wolfgang Fahl

15.7k11 gold badges101 silver badges204 bronze badges

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

8 Comments

Spoeken Over a year ago

Awesome! So, it scp's a script file to the server, then executes it? I'll check it out later today.

Spoeken Over a year ago

It throws me: bash: mysql: command not found Is there some setting I'm missing on the server?

Wolfgang Fahl Over a year ago

you need to make sure the PATH is set in the ssh session or specify the fully qualifying name of mysql in the script. Use "which mysql" in a ssh terminal session to find it e.g. >which mysql /usr/local/mysql/bin/mysql

Spoeken Over a year ago

So it does not help that the PATH is set in the bash profile on the server, I have to somehow declare it in this script as well? How would that be done?

Wolfgang Fahl Over a year ago

with macports I have to put the PATH declaration in .ssh/environment e.g. PATH=/usr/bin:/bin:/usr/sbin:/sbin:/opt/local/bin

|

Collectives™ on Stack Overflow

Create mySQL database through ssh in a bash script

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related