Mysql : How to run batch of sql scripts from a folder

Question

I have a folder with o lot of sql scripts. I want to run all of them without specifying names of them. Just specify a folder name. Is it possible?

http://dev.mysql.com/doc/refman/5.0/en/batch-mode.html – Nagaraj S Dec 27 '13 at 10:12 — Nagaraj S, Dec 27 '13 at 10:12

score 15 · Accepted Answer · edited Feb 23 '23 at 23:29

15

You can not do that natively, but here's simple bash command:

for sql_file in `ls -d /path/to/directory/*`; do mysql -uUSER -pPASSWORD DATABASE < $sql_file ; done

here USER, PASSWORD and DATABASE are the corresponding credentials and /path/to/directory is full path to folder that contains your files.

If you want to filter, for example, only sql files, then:

for sql_file in `ls /path/to/directory/*.sql`; do mysql -uUSER -pPASSWORD DATABASE < $sql_file ; done

edited Feb 23 '23 at 23:29

NobleUplift

answered Dec 27 '13 at 10:08

Alma Do

1

Here is why `ls` might be bad as listed here https://stackoverflow.com/a/2152795/1896134 Please look at momer's comment. http://mywiki.wooledge.org/ParsingLs – JayRizzo Jun 20 '17 at 23:40
The `ls` command as it was previously written wasn't functional. It would list off the files in the directory but without the `/path/to/` prefix, thus it wouldn't run anything. – NobleUplift Feb 23 '23 at 23:30

score 2 · Answer 2 · answered Oct 17 '16 at 20:03

2

That was what worked for me: 1. Created a shell script in the folder of my scripts

for f in *.sql
  do
   echo "Processing $f file..."

   mysql -u user "-pPASSWORD" -h HOST DATABASE < $f

 done

answered Oct 17 '16 at 20:03

eduardocurva

2 Answers2