Create SQL file in BASH
I am trying to create a sql
script using bash
, but I keep getting this line after each iteration of my loop
: command not found
This applies to Ubuntu as well as OSX.
At this point, I am not executing the sql
script, I am just trying to create it. What am I missing so that it doesn't try to "fulfill" the request?
Queries are good when testing in phpmyadmin I don't understand why a variable needs to be set $PATH
if I am not doing the actual query, I am just creating a text file.
The code I use is:
SQL="";
cat people.txt | while read line
do
PW="my"$line"db";
DB="test_"$line;
$SQL=$SQL"CREATE DATABASE \`$DB\`;CREATE USER \`$line\`@\`localhost\`;SET PASSWORD FOR \`$line\`@\`localhost\` = PASSWORD(\"$PW\") ;GRANT ALL PRIVILEGES ON $DB.* TO \`$line\`@\`localhost\` IDENTIFIED BY \"$PW\";";
done
echo $SQL > t1.sql;
The list I am using to import:
bob123
john123
jane123
The output I get is:
./02_setup_mysqldb.sh: line 14: =CREATE DATABASE `test_bob123`;CREATEUSER `bob123`@`localhost`;SET PASSWORD FOR `bob123`@`localhost` = PASSWORD("mybob123db") ;GRANT ALL PRIVILEGES ON test_bob123.* TO `bob123`@`localhost` IDENTIFIED BY "mybob123db";: command not found
./02_setup_mysqldb.sh: line 14: =CREATE DATABASE `test_john123`;CREATE USER `john123`@`localhost`;SET PASSWORD FOR `john123`@`localhost` = PASSWORD("myjohn123db") ;GRANT ALL PRIVILEGES ON test_john123.* TO `john123`@`localhost` IDENTIFIED BY "myjohn123db";: command not found
source to share
Notes:
1. The assignment to the variable SQL
is wrong, just change to SQL="$SQL..."
, just remove the symbol $
.
2. When you execute cat people.txt | while read LINE
, you ignore the last line, being necessary to have a blank line after the last line.
3.Your script has a lot of string concatenation in one line, just create variables to make it more readable.
Finally, the code:
#!/bin/bash
while read line
do
PW="my${line}db"
DB="test_$line"
create_database="CREATE DATABASE \`$DB\`;\n"
create_user="CREATE USER \`$line\`@\`localhost\`;\n"
change_password="SET PASSWORD FOR \`$line\`@\`localhost\` = PASSWORD(\"$PW\");\n"
grant_privileges="GRANT ALL PRIVILEGES ON $DB.* TO \`$line\`@\`localhost\` IDENTIFIED BY \"$PW\";\n"
SQL="${SQL}${create_database}${create_user}${change_password}${grant_privileges}"
done < people.txt
echo -e ${SQL} > t1.sql
source to share