CODEWITHSUNDEEP
mysqllinuxbash
HM107
HM107

Reputation: 1401

Adding user through bash in MySQL

This is my first try to pass a bash command in mysql terminal, I'm doing this alot:

creating a dummy user each time I install an app to interact with the database.

however, all my tries failed, and here is my line of code:

new_user(){ mysql -u"$1" -p"$2" -e 'CREATE USER "$3"@"$4" IDENTIFIED BY "$5";
            GRANT ALL PRIVILEGES ON "$6".* TO "$3"@"$4";
            FLUSH PRIVILEGES; 
            exit;' &&
        echo `new MySQL USER has been created ..
            name:"$3" server:"$4" password:"$5" database:"$6"` ; }

however, it is giving me this error:

 ERROR 1064 (42000) at line 1: You have an error in your SQL syntax;
 check the manual that corresponds to your MySQL server version
 for the right syntax to use near '"$4".* TO "$1"@"$2"' at line 1

I have tried using the -e but as shown with no luck.

how to pass bash variables in mysql?

Update:

thanks to the comments, I have updated my code for the quotes, however I still cannot pass the variables to mysql terminal.

new_user() {
    SQL="CREATE USER\"$3\"@\"$4\" IDENTIFIED BY \"$5\"; GRANT ALL PRIVILEGES ON \"$6\".* TO \"$3\"@\"$4\"; FLUSH PRIVILEGES; exit;"
    mysql -uroot -p1 -e "$SQL" &&
    echo new MySQL USER has been created ..  name:\"$3\" server:\"$4\" password:\"$5\" database:\"$6\"

}

Upvotes: 0

Views: 129

Answers (1)

Will
Will

Reputation: 1313

You can avoid figuring out how to escape what quotes by using HEREDOC.

To debug, maybe try breaking your function into two. 

gen_user_sql(){
  cat <<HEREDOC
   CREATE USER '$1'@'$2' IDENTIFIED BY '$3';
   GRANT ALL PRIVILEGES ON $4.* TO '$1'@'$2';
   FLUSH PRIVILEGES;

HEREDOC
}
new_user(){
   sqluser="$1"; shift
   sqlpswd="$1"; shift
   mysql -u"$sqluser" -p"$sqlpswd" -e "$(gen_user_sql $@)"
   echo "new user: $@"
}
# see the query
gen_user_sql user host id db
# actually run it
new_user sqlu sqlp user host id db

some notes in case there is new syntax:

  • $@ is all the variables passed in. shift removes what would be $1 from $@. (after shift, $1 returns what was $2 before shift was called)
  • $(command) captures the output of command and in this case uses it as string input to mysql

Upvotes: 1

Related Questions