1

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

1 Answer 1

2

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.

8
  • Please refresh your page. I updated my answer. Commented Aug 25, 2021 at 21:08
  • could I put the full mysql command, simulating the same dump export conditions? Thanks in advance
    – acgbox
    Commented Aug 25, 2021 at 21:08
  • You would need to pipe the output of the mysqldump straight to mysql. Commented Aug 25, 2021 at 21:09
  • It would something like 'mysqldump -haddr1 -uroot1 -ppassword1... | mysql -haddr2 -uroot2 -ppassword2 Commented Aug 25, 2021 at 21:10
  • the command i use is the one i posted. What I want is to make sure that when I run your command, the database remains the same as when it was exported. forgive me but I'm nerbie
    – acgbox
    Commented Aug 25, 2021 at 21:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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