2

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.

migrated from serverfault.com Feb 19 '14 at 12:48

This question came from our site for system and network administrators.

  • 1
    What do you mean by "can't rename file"? What did you want to rename? :X You should do periodically MySQL backup. I do once a day, so it wont kill me to use 1 day old database. You could go with every hour backup if it is so important to you. But I learned one thing after losing 4TB of data a week ago, that backup is #1 in IT world :) – Wiggler Jtag Feb 16 '14 at 12:35
  • that was mysql error when i was editing mysql table...it failed to rename #sql-b61_25a8e.MYI to users.MYI – fhntv24 Feb 16 '14 at 12:42
  • You should rename table from mysql> command line tool, or install phpmyadmin or connect via software like Navicat and so on, this will not let you to make mistake. Try to killall -9 mysqld and then /etc/init.d/mysql {start,restart,stop} – Wiggler Jtag Feb 16 '14 at 13:57
  • I dont want to rename table that isnt "there"....all data get deleted...i know how to rename tables,dont worry ;) – fhntv24 Feb 16 '14 at 16:58
3
  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).

3

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy