Thursday, October 7, 2010

How to recover one table from dump in MySQL

If you do dumps just of one table on regular basis then you are happy man :-)
  1. Backup of one table:
    # mysqldump -u username -p password > /path/file.sql
  2. Restore one table:
    # mysql --user=username --pass=password --host=localhost db_name < /path/file.sql
If you do dumps of whole DB then do following:
Backup of DB:
  1. # mysqldump -u username -p password > /path/file.sql
Restore of one table from dump of DB:
  1. We have to know where one table starts and where ends:
    # grep -n 'Table structure' /path/file.sql
  2. Then we have to separate part of file with required table into new file using line number gathered in previous command (where 7506 is start and 17684 is end):
    # sed -n '7506,17684 p' /path/file.sql > /path/new_file.sql
  3. Then we can restore it into DB:
    # mysql --user=username --pass=password --host=localhost db_name < /path/file.sql

No comments:

Post a Comment