MySQL “lost” all data(InnoBD)

Question

Today I was making some work with site and I get error what MySQL can't rename file (to make changes in BD). Next I tried to restart MySQL, and it failed to stop MySQL. Then I did go to /var/lib/mysql and all I see is: ib_logfile0+1, ibdata1, and site folder, with tables that I was changing (not tables themselves, just #sql-b61_25a8e.MYI).

Can I recover my MySQL data? I don't need all tables, because I have old backup of them. I just need that one table with all users (yea I was changing table with users) And yes, I did shutdown MySQL for some stupid reason.

Answer 1

1. Keep a copy of MySQL datadir before any tries to restore the database. Especially ibdata1 and *.ibd files where InnoDB keeps all data.

2. Start MySQL with innodb_purge_threads=0 and innodb_force_recovery=4 . If it doesn't start - try values 5 and 6.

3. If MySQL starts take a dump of all databases with mysqldump. Then start fresh instance of MySQL and reload data back.

4. If MySQL doesn't start you'd need to recover from a backup if you have. If not - it's still may be possible to extract records from ibdata1 (or *.ibd).

Answer 2

The table starting with #sql is just a temporary table and, if it's not in use, can safely be deleted. MySQL creates this temporary table to perform the various ALTER TABLE operations that you request.

If your original operation never completed, then your data should still be safely in its original table. If not, you can always rename the temporary table yourself and you'll get back whatever is in it.

Answer 3

First of all as @Michael Hampton updated that table name start from #sql are temporary tables created by mysql and as per my understanding removed automatically after use. So you should not worry for it.

If only user table is required (For mysql user rights), then follow below steps:

Step1: copy below tables from /var/lib/mysql/mysql path

user.frm

user.MYD

user.MYI

db.frm

db.MYD

db.MYI

Note: Check your datadir path with the help of below command, it should be /var/lib/mysql

SHOW VARIABLES LIKE 'datadir';

Step2: Now move these tables to any testing mysql server's data directory suppose /var/lib/mysql/mysql

Step3: Provide rights to mysql for these new files with the help of below command-

chown -R mysql.mysql /var/lib/mysql/mysql/.

Step4: Restart mysql on this testing server.

Step5: If every thing is fine here then take dump of complete mysql database or just user and db tables as per your requirement and restore this to your production server where you are planning to restore your db.

Note: If you want to recover complete data of your system then need more information like what command you execute at the time of problem, after that what actions you took and what is innodb_file_per_table status on your server what files are exist in data directory etc.