MySQL Table Data Lost but structure remains

kaushal01981
kaushal01981 used Ask the Experts™
on
This is related to MySQL.
I ran a command --> mysqldump -u -p DBNAME > [Path of .sql file],
After running this command , I ran --> mysql -u -p DBNAME > [Path of .sql file].
After this, my Table data got overwritten and my Table is blank. Only the table structure remains. Please suggest how to bring back the Table original Data.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>I ran --> mysql -u -p DBNAME > [Path of .sql file].

You redirected the output of MySQL over the SQL file.

Looks like you should have used "<" instead of ">".  If you didn't create a backup of the .sql file before running that MySQL command then I'm afraid it is gone.  Check with your System Administrator to see if they are running some form of file versioning on that folder that might save copies of older versions in the background for you.

Confirm that is the command you executed.
David FavorFractional CTO
Distinguished Expert 2018

Commented:
This command...

mysql -u -p DBNAME > [Path of .sql file]

Open in new window


Would have destroyed your backup file. Hopefully you meant < instead of > as your redirection operator.

Tip: I always use cat or pv piped into mysql to load data, so I avoid accidental backup over writes.
nociSoftware Engineer
Distinguished Expert 2018

Commented:
If you did this then the data should still be in the database..... Only the backup is empty....with startup info from mysql).
(Without any other commands...)

mysqldump does not remove data....  or drop tables.
It write the requested databases to standard output.
The >pathof.sql  saves that data intot the file pathof.sql


To restore the data use:
mysql <pathof.sql

Which will cause mysql to read the data from pathof.sql instead of standard input.

So try again:
mysqldump -u -p DBNAME >pathof.sql
#and then:
mysql -u -p DBNAME <pathof.sql

Open in new window


the mysql -u -p DBNAME >pathof.sql  did destroy the file pathof.sql only.
Tomas Helgi JohannssonDatabase Administrator / Software Engineer

Commented:
Hi,

If your database has the bin-log enabled and you have all bin-logs available then you could do a point-in-time recovery on your data.

https://dev.mysql.com/doc/refman/5.7/en/mysqlbinlog.html
https://dev.mysql.com/doc/refman/5.7/en/point-in-time-recovery.html
http://www.mostlychris.com/blog/2009/07/31/restoring-from-mysql-binlog/
https://www.vpsinfo.com/tutorial/backup-mysql-database/

Regards,
     Tomas Helgi

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial