Mark Veenstra Mark Veenstra - 3 months ago 16
MySQL Question

Escaping mysql quotes in bash script

I am trying to execute a script during Vagrant provision that executes some queries on the database to allow root login from any host, create the application database and create the application user, like this:

#!/usr/bin/env bash

sudo bash << EOF

export DEBIAN_FRONTEND=noninteractive

MYSQL_ROOT_PASS='mySecretPass'
MYSQL_APP_PASS='appSecretPass'

mysql --user='root' --password='${MYSQL_ROOT_PASS}' <<QUERY
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '${MYSQL_ROOT_PASS}';
CREATE DATABASE appdb DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
GRANT ALL PRIVILEGES ON apdb.* to 'appuser'@'%' IDENTIFIED BY '${MYSQL_APP_PASS}';
QUERY


EOF


But when I execute above I get the error:

ERROR 1133 (42000) at line 1: Can't find any matching row in the user table


I don't quite get how the escaping now works within bash and mysql. I have been messing around with
eval
instead of here documents and some mixed up escaping between " and ' quotes.

Answer

No need to do sudo bash, just invoke sudo mysql directly and use double quotes for $MYSQL_ROOT_PASS on command line:

#!/usr/bin/env bash

export DEBIAN_FRONTEND=noninteractive

MYSQL_ROOT_PASS='mysql'
MYSQL_APP_PASS='appSecretPass'

sudo mysql --user=root --password="$MYSQL_ROOT_PASS"<<QUERY
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '${MYSQL_ROOT_PASS}';
CREATE DATABASE appdb DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
GRANT ALL PRIVILEGES ON apdb.* to 'appuser'@'%' IDENTIFIED BY '${MYSQL_APP_PASS}';
QUERY