mjhjmtu

The following code drops a DB user and a DB instance by the name of test, if both exists, then creates an authorized, all privileged DB user and a DB instance with the same name (also test).

mysql -u root -p <<-MYSQL
 DROP user IF EXISTS 'test'@'localhost'; SELECT user FROM mysql.user;
 DROP database IF EXISTS test; show databases;

 CREATE user 'test'@'localhost' IDENTIFIED BY '$psw';
 CREATE database test;
 GRANT ALL PRIVILEGES ON test.* TO test@localhost;
MYSQL

I lack too things in this code:

1. Exporting $domain into a $domain.sql mysqldump.


2. Importing $domain.sql mysqldump into the test DB database.

How could I add these two actions but inside the heredocument? I don't want them to be different actions outside the heredocument (what requires entering username and password again and again), rather, I need them as regular mysql queries inside the heredocument, coming right after the last GRANT query.

1. Exporting

Unfortunately, mysql shell can't dump database contents like mysqldump does, so it's impossible to execute SQL queries and dump database in one call to mysql or mysqldump. However you can:

a) Grant user test access to the $domain database:

mysql -u root -p <<-MYSQL
 ...
 GRANT ALL PRIVILEGES ON $domain.* TO 'test'@'localhost';
 GRANT GRANT OPTION ON $domain.* TO 'test'@'localhost';
 FLUSH PRIVILEGES;
MYSQL

subsequently call:

mysqldump -u test -p"$psw" "$domain" >domain.sql

and finally call:

mysql -u test -p"$psw" <<-MYSQL
 REVOKE ALL PRIVILEGES ON $domain.* FROM 'test'@'localhost';
 REVOKE GRANT OPTION ON $domain.* FROM 'test'@'localhost';
MYSQL

No need to enter the password again as it's passed in command line. However passing the password in command line is insecure so you may consider using expect or creation of my.cnf with user/password settings and referring to it with --defaults-extra-file= as motivast suggested.

b) You can read the root password in the beginning of your script and then use it in subsequent mysql calls (this described in motivast comment):

read -s -p 'Enter password: ' root_psw
echo
my_cnf=`tempfile -s .cnf -m 400`
echo "[mysql] >$my_cnf
echo "user=root" >>$my_cnf
echo "password=$root_psw" >>$my_cnf

# Delete the password file after this script finish (even in case of error)
cleanup_my_cnf rm "$my_cnf"; 
trap cleanup_my_cnf INT TERM EXIT

mysql --defaults-extra-file="$my_cnf" <<-MYSQL
 ...
MYSQL

mysqldump --defaults-extra-file="$my_cnf" "$domain" >domain.sql

c) If you only need to dump table structure and you know table names, you could use the SHOW CREATE TABLE SQL:

mysql -u root -p <<-MYSQL
 ...
 use $domain;
 tee domain.dump;
 SHOW CREATE TABLE table1;
 SHOW CREATE TABLE table2;
MYSQL

But this is too exotic and domain.dump would need a bit of editing afterwards.

2. Importing

That's pretty easy with source command (same as in the bash):

mysql -u root -p <<-MYSQL
 ...
 use test;
 source $domain.sql;
MYSQL