Como executar múltiplas consultas mysql de múltiplas linhas com um script de shell?

Question

Eu quero executar várias consultas do MySQL a partir de um script de shell.

Estou usando o seguinte código:

mysql -u <redacted> -p<redacted> servermail << EOF

CREATE TABLE 'virtual_domains' (
'id'  INT NOT NULL AUTO_INCREMENT,
'name' VARCHAR(50) NOT NULL,
PRIMARY KEY ('id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE 'virtual_users' (
'id' INT NOT NULL AUTO_INCREMENT,
'domain_id' INT NOT NULL,
'password' VARCHAR(106) NOT NULL,
'email' VARCHAR(120) NOT NULL,
PRIMARY KEY ('id'),
UNIQUE KEY 'email' ('email'),
FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE 'virtual_aliases' (
'id' INT NOT NULL AUTO_INCREMENT,
'domain_id' INT NOT NULL,
'source' varchar(100) NOT NULL,
'destination' varchar(100) NOT NULL,
PRIMARY KEY ('id'),
FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

EOF

Mas continuo recebendo estas mensagens de erro:

virtual_domains: command not found
No command 'name' found, did you mean:
 Command 'named' from package 'bind9' (main)
 Command 'namei' from package 'util-linux' (main)
 Command 'lame' from package 'lame' (universe)
 Command 'uname' from package 'coreutils' (main)
 Command 'nama' from package 'nama' (universe)
 Command 'mame' from package 'mame' (multiverse)
 Command 'nam' from package 'nam' (universe)
name: command not found
virtual_users: command not found
domain_id: command not found
password: command not found
No command 'email' found, did you mean:
 Command 'mail' from package 'mailutils' (universe)
 Command 'dmail' from package 'uw-mailutils' (universe)
 Command 'kmail' from package 'kmail' (main)
 Command 'cmail' from package 'xboard' (universe)
 Command 'tmail' from package 'uw-mailutils' (universe)
 Command 'rmail' from package 'rmail' (universe)
 Command 'rmail' from package 'exim4-daemon-heavy' (main)
 Command 'rmail' from package 'courier-mta' (universe)
 Command 'rmail' from package 'masqmail' (universe)
 Command 'rmail' from package 'postfix' (main)
 Command 'rmail' from package 'exim4-daemon-light' (main)
 Command 'wmail' from package 'wmail' (universe)
 Command 'emil' from package 'emil' (universe)
email: command not found
No command 'email' found, did you mean:
 Command 'mail' from package 'mailutils' (universe)
 Command 'dmail' from package 'uw-mailutils' (universe)
 Command 'kmail' from package 'kmail' (main)
 Command 'cmail' from package 'xboard' (universe)
 Command 'tmail' from package 'uw-mailutils' (universe)
 Command 'rmail' from package 'rmail' (universe)
 Command 'rmail' from package 'exim4-daemon-heavy' (main)
 Command 'rmail' from package 'courier-mta' (universe)
 Command 'rmail' from package 'masqmail' (universe)
 Command 'rmail' from package 'postfix' (main)
 Command 'rmail' from package 'exim4-daemon-light' (main)
 Command 'wmail' from package 'wmail' (universe)
 Command 'emil' from package 'emil' (universe)
email: command not found
No command 'email' found, did you mean:
 Command 'mail' from package 'mailutils' (universe)
 Command 'dmail' from package 'uw-mailutils' (universe)
 Command 'kmail' from package 'kmail' (main)
 Command 'cmail' from package 'xboard' (universe)
 Command 'tmail' from package 'uw-mailutils' (universe)
 Command 'rmail' from package 'rmail' (universe)
 Command 'rmail' from package 'exim4-daemon-heavy' (main)
 Command 'rmail' from package 'courier-mta' (universe)
 Command 'rmail' from package 'masqmail' (universe)
 Command 'rmail' from package 'postfix' (main)
 Command 'rmail' from package 'exim4-daemon-light' (main)
 Command 'wmail' from package 'wmail' (universe)
 Command 'emil' from package 'emil' (universe)
email: command not found
virtual_aliases: command not found
domain_id: command not found
-bash: source: filename argument required
source: usage: source filename [arguments]
destination: command not found
ERROR 1064 (42000) at line 2: 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 '(
uid=0(root) gid=0(root) groups=0(root)  INT NOT NULL AUTO_INCREMENT,
 VARCHAR(' at line 1

Quando executo os comandos um por um dentro de mysql , eles funcionam.

O que devo tentar em seguida?

    

Answer 1

Aqui, a expansão de variáveis de documentos e a substituição de comandos são executadas, a menos que o delimitador final seja citado. No seu caso, as strings entre backticks como virtual_domains são executadas como comandos pelo shell.

Para o resultado, você gostaria de ter que citar o delimitador final EOF entre aspas simples ou duplas, como abaixo. Isso impedirá a expansão e substituição.

mysql -u <redacted> -p<redacted> servermail << "EOF"

CREATE TABLE 'virtual_domains' (
'id'  INT NOT NULL AUTO_INCREMENT,
'name' VARCHAR(50) NOT NULL,
PRIMARY KEY ('id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Veja Aqui documentos - Unix shells , Bash reference - Aqui os documentos

    

Answer 2

Eu colocaria as instruções SQL em um arquivo digamos sqldump.txt e depois executaria a importação como abaixo. Crie o banco de dados vazio the_database antes, se ele não existir.

mysql -u auser -p apassword the_database< sqldump.txt