delete tables on import of dump created by mysqldump

Question

There are 2 copies of the same database on 2 different storages which need to be in sync.

For example, database named test_db contains tables T1,T2,T3 tables. T2,T3 are deleted. Dump of the 1st copy is created using:

mysqldump --login-path=lp --add-drop-table test_db > dump.sql

and imported on 2nd storage using:

mysql --login-path=kp test_db < dump.sql

But deletion of tables T2,T3 on the 1st storage is not propagated this way to the 2nd storage.

How to propagate deletion of tables from the 1st storage to 2nd storage using mysqldump on the 1st and import of the dump on the 2nd?

Answer 1

You need to drop and re-create the database. So you need to use some extra / different options when creating the dump file:

mysqldump --login-path=lp --add-drop-database --databases test_db > dump.sql

And import with:

mysql --login-path=kp < dump.sql