1

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.

  • 2
    If you do not want to enter multiple times password maybe you can read password once and pass it to multiple commands: echo -n "Enter root mysql password:" read -s DBROOT_PASS mysql -u root -p$DBROOT_PASS -e "$SQL". I personally use one file with mysql root credentials with only root access and execute mysql commands like mysql --defaults-extra-file=/root/.my.cnf dbname -e "$SQL". It doesn't require any password only root access. – kierzniak Dec 8 '17 at 7:55
  • This is a main part of the correct solution dear @motivast and I invite you to write an answer with it. – Arcticooling Dec 11 '17 at 0:59
  • @motivast you might be able to save the day here as well (I'm quite stuck with that): stackoverflow.com/questions/47744613/… – Arcticooling Dec 11 '17 at 1:42
2
+100

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.