How to restore one mysql database with mysqldump

Question

to make a backup I run:

mysqldump --host=localhost --user=root --password=whatever --single-transaction --routines --triggers --log-error=error.txt mydatabase > out.sql

Is it possible to do the same in reverse (restore out.sql) with mysqldump?

in case it is not possible, what is the most recommended command to import the database out.sql, in the same conditions in which it was exported by mysqldump

Answer 1

You do not use mysqldump to reload a database dump. You reload with the mysql client.

See my old post Is it possible to mysqldump a subset of a database required to reproduce a query? for example. I perform 3 mysqldumps and then reload the dumps using the mysql client.

for your mysqldump in the question, it would be (in simplified terms)

mysql -u user -p < out.sql

If the dump file has multiple databases, then it would be

mysql -u user -p --one-database mydatabase < out.sql

See mysql option --one-database for more info.

UPDATE 2021-08-21 17:33 EDT

Your command

mysqldump --host=localhost --user=root --password=whatever --single-transaction --routines --triggers --log-error=error.txt mydatabase > out.sql

does not set the target database. You have to tell the mysql client.

mysql --host=anotherhost --user=root --password=whatever -Dtargetdatabase < out.sql

If you want to put the target database inside the dump, do this

mysqldump --host=localhost --user=root --password=whatever --single-transaction --routines --triggers --log-error=error.txt --databases mydatabase > out.sql

Then, the mysql client will be told what the target is

mysql --host=anotherhost --user=root --password=whatever < out.sql

because the line

USE mydatabase;

will appear just before the first table is made and loaded.