0

I want to do mysqldump to get backup of all my data bases. suppose if any crash occur in my mysql database, then i will restore that backup file. when try to restore the old backup. after doing backup still the newly created tables are present in the database. is there any option to restore only the old data, and erase all other newly added stuff?

i did following 

$ mysqldump -u root -proot  --all-databases > alldb_backup.sql

restore 

$ mysqldump -u root -proot < alldb_backup.sql
Vasu
  • 265
  • 1
  • 10
  • 18
  • you can try to use workbech directly http://stackoverflow.com/questions/22381577/mysql-workbench-how-to-export-mysql-database-to-sql-file/22382468#22382468 – Divya Mar 27 '14 at 06:43
  • i have to write a script for that, i can't use tools here. – Vasu Mar 27 '14 at 06:45
  • Your question is unclear. Do you want to delete the entire database and only restore from your backup or do you want to preserve some existing data in your database and restore old data on top of it? – Beauvais Mar 27 '14 at 06:51
  • i want to delete the entire database and only restore from your backup @DHS – Vasu Mar 27 '14 at 06:52

2 Answers2

2

You should add the --add-drop-database option. This will delete an existing database so you will start on an empty database when importing the backup.

Make a MySQL backup of all databases:

mysqldump --user=root --add-drop-database --complete-insert --extended-insert \
          --verbose --all-databases > alldb_backup.sql

Only make a backup of a single database (easier to handle individually):

mysqldump --user=root --add-drop-database --complete-insert \
          --extended-insert --verbose mysingledb > mysingledb.sql

To restore all databases it is then:

mysql --user=root -p < alldb_backup.sql

Or to restore a single database:

mysql --user=root -p mysingledb < mysingledb.sql
Beauvais
  • 2,149
  • 4
  • 28
  • 63
  • why add-drop option ? – Vasu Mar 27 '14 at 07:00
  • It will then delete the existing database so you can start from scratch with your new import/backup – Beauvais Mar 27 '14 at 07:02
  • when i did restore the old one, its showing fallowing error. i can guess its because of permission to database. is it ? ERROR 1010 (HY000) at line 22: Error dropping database (can't rmdir './PhD', errno: 39) – Vasu Mar 27 '14 at 07:07
  • See http://stackoverflow.com/questions/21403565/my-sql-error-code-1010-error-dropping-database-cant-rmdir-errno-13 – Beauvais Mar 27 '14 at 07:24
0

A simple way, but maybe not the best solution is to drop all your databases except mysql, information_schema,test and OLD db’s

mysql -uroot -p -e "show databases" | grep -v Database | grep -v mysql| grep -v information_schema| grep -v test | grep -v OLD |gawk '{print "drop database " $1 ";select sleep(0.1);"}' | mysql -uroot -ppassword

And then restore your backup

$ mysqldump -u root -proot < alldb_backup.sql

Oscar Rovira
  • 46
  • 5