Import .sql file into multiple databases

Question

How to import .sql file into multiple databases at one shot.

I have tried this:

mysql -uroot -p -all-databases < test.sql

But it asks for database name. Whatever there in test.sql file should reflect in all the databases/Schemas.

Answer 1

If you're running on Linux/Unix, you can do it with a bit of shell:

for DB in `mysql -u root -r --silent -p -e 'show databases;' | egrep -v "_schema$|^mysql$|^sys$"`
do 
  mysql -uroot -p $DB < test.sql
done

Will obviously be easier if you temporarily set it up to be passwordless.

Answer 2

Export:
sudo mysqldump -u root -p --all-databases > mydatabases.sql

Import (with names):
sudo mysql -u root -p < mydatabases.sql

Note that the database names are saved to dump/exported file. Meaning: the database names will be created if you use the mysqldump statement above. You do not need anything fancy on the restore statement. You do NOT specifiy a database name in this case - just the file redirect.

(Tested under: Ver 5.1.73-0ubuntu0.10.04.1 through Ver 5.7.24-0ubuntu0.18.04.1)

See also:
https://stackoverflow.com/questions/9497869/export-and-import-all-mysql-databases-at-one-time