First off, where are your backups? If you don't have any, this should be your first task after recovering your data.
Anyhow, as long as you have a copy of your .frm files, you can recover the data. Those files are usually located in the same directory as you DB data, e. g. /var/lib/mysql, but maybe your setup differs. If you have no copy of your .frm files, you need to re-create them. frm-files statically store your table layout, so shouldn't be too hard to re-create, provided you have kept your design papers or you use a provided application such as Wordpress. Then just create new instance of the application and copy the frm-files to your recovery machine.
For recovery follow these simple steps:
- Set up a new MySQL server on another machine, but do not start
mysqld
yet.
- Copy your
ibdata1
, ib_logfile0
and ib_logfile1
to the new machine's /var/lib/mysql
folder.
- Make sure that these files belong to the MySQL user, e. g.
chown mysql ibdata1 ...
- Find the size of the logfiles in bytes, e. g.
ls -l
.
- Become the MySQL user, e. g.
su mysql
.
- Start the mysqld with recovery options, i. e.
/usr/sbin/mysqld –innodb_log_file_size=<size_inbytes> –innodb_force_recovery=6
Now you should see some messages from MySQL, such as which options you set. After a short while mysqld
should be up and running. Once this has happened you can pull a correct copy of your database with mysqldump
and import the copy into your production machine.
perhaps better asked at stackoverflow.com – Tomas – 2014-02-16T11:53:00.420
http://stackoverflow.com/questions/21810413/mysql-lost-all-datainnobd lol,they are sending me to here,and you sending me to there... – fhntv24 – 2014-02-16T11:55:40.447
1@Tomas I'm pretty much sure this is the right place. Configuring and recovering databases are on-topic here. Specifics of SQL queries obviously belong to Stack Overflow. – Olli – 2014-02-16T12:19:03.810
Have you tried force recovery? This may allow MySQL to start up so that you can dump/backup the users table, but won't allow changes. Also, the MySQL error log -- starting with the crash -- would be helpful.
– SgtOJ – 2014-02-16T13:56:28.743