0

I plan to dump all databases on one computer and restore them on another using the following 2 commands:-

mysqldump -u username -p --all-databases > dump.sql

mysql -u username -p < dump.sql

My query is : will the second command overwrite an existing table with the same name as on the first computer ? Also, will the second command delete all tables which are NOT there on the first computer?

Can someone please clarify?

0

The exact syntax used varies depending on what version of MySQL you are using, but the basic version of mysqldump (without any other options) you are using will run:

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /;

DROP TABLE IF EXISTS `test1`;
CREATE TABLE `test1` (. . . 

You can check this if you are concerned, before running the second statement, simply look in the file dump.sql (it is just a text file) and you can see clearly what queries it will run.

So to answer your questions:

will the second command overwrite an existing table with the same name as on the first computer ?

Yes it will

will the second command delete all tables which are NOT there on the first computer?

No it won't

You can get a full list of the options available from: https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#mysqldump-option-summary

  • Many thanks. A related query. Suppose I have dumped only ONE database (mydb1) by doing, mysqldump -u root -p --databases mydb1 > mydb1.sql, then we can do mysql -u root -p < mydb1.sql on a new system. Some websites suggest that we first create an empty database with the same name(mydb1) prior to this. See for example this (phoenixnap.com/kb/how-to-backup-restore-a-mysql-database) I don't think that is needed. Am I correct? – user2338823 Jun 24 at 8:53
  • You are correct, because you used --databases it includes the CREATE DATABASE mydb1 statement. In the example you link to, they omit the --databases option, in which case you need to create the new database first, and tell the second command (import) which database to use. – IGGt Jun 24 at 9:16
  • Query : Are the following 2 the same? mysql -u root -p --database=mydb < abc.sql vs mysql -u root -p mydb < abc.sql ? I think the 1st does not need the full path name, while the 2nd needs the full path name. How can I determine the full path for the second option ? – user2338823 Jun 25 at 5:20
  • I'm pretty sure they are both basically the same, and will both need the full path to the file. The path will be wherever the mysqldump script puts the file. e.g. if you dump it to /tmp you would also load it from /tmp – IGGt Jun 25 at 7:35
  • You can of course combine the two mysqldump -hxxx -P3306 -uroot -pxxxx [options] | mysql -hxxx -P3306 -uroot -pxxxx. This means you don't need to worry about saving the file in the first place. – IGGt Jun 25 at 7:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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