MySQL Table Data Lost but structure remains
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.
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.
Who is Participating?
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
This command...
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.
mysql -u -p DBNAME > [Path of .sql file]
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.
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:
the mysql -u -p DBNAME >pathof.sql did destroy the file pathof.sql only.
(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
the mysql -u -p DBNAME >pathof.sql did destroy the file pathof.sql only.
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
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases
From novice to tech pro — start learning today.
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.