Cool Dba

  • Home
  • Sql tricks
  • Security
  • Maintainance
  • Backups
  • Recovery
  • Replication
  • Installation
  • Linux

Friday, May 20, 2016

Recovering InnoDB MySQL Tables Data from ibdata and .frm Files

 Anil Joshi     3:35 AM     Recovery     No comments   


First Check Error Logs.

InnoDB: Error: log file ./ib_logfile0 is of different size 0 50331648 bytes
InnoDB: than specified in the .cnf file 0 5242880 bytes!
[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[ERROR] Unknown/unsupported storage engine: InnoDB
[ERROR] Aborting

The method explained below will work only for InnoDB database.
Note: First take a backup of all the MySQL files and database.
Contents Of Mysql Data Directory.
drwx------ 2 mysql mysql     4096 Oct 11  2012 performance_schema
drwx------ 2 mysql mysql     4096 Dec 10  2012 ndbinfo
drwx--x--x 2 mysql mysql     4096 Dec 10  2012 mysql
-rw-rw---- 1 mysql mysql       56 Dec 19  2012 auto.cnf
drwx------ 2 mysql mysql     4096 Jul 30  2013 bugs
-rw-r----- 1 mysql mysql 50331648 Mar 18 10:35 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Apr 22  2013 ib_logfile1
-rw-r----- 1 mysql mysql 35651584 Mar 18 10:35 ibdata1
..

§  Ibdata1 – This file is the InnoDB system table space, which contains multiple InnoDB tables and associated indexes.
§  *.frm – Holds metadata information for all MySQL tables. These files are located inside the folder of the corresponding MySQL database. (for example, inside “bugs” directory)
§  ib_logfile* – All data changes are written into these log files. This is similar to the archive logs concepts that we find in other RDBMS databases.
Copy the Files
 First stop the MySQL server.
# service mysqld stop
Copy the ibdata files, and the database schema folder to some other directory. We will use this to restore our Mysql database. In this case, we’ll copy it to the /tmp directory. The name of the database scheme in this example is Sakila.
cp –r ibdata* ib_logfile* /tmp

cp –r schema_name/  /tmp/schema_name/
Start the MySQL server:
# service mysqld start

Restore the Data

In the my.cnf configuration file, set the value of the following parameter to the current size of the ib_logfile0 file. In the following example, I’ve set it to 48M, as that is the size I see for the ib_logfile0 file when I did “ls -lh ib_logfile0″
innodb_log_file_size=48M
Please note that both the ib_logfile0 and ib_logfile1 file size will be the same.
Copy the previous ibdata files to respective position, inside mysql data directory.
cp –r /tmp/ibdata* /var/lib/mysql/
Create an empty folder inside data directory with the same name as the database schema name that you are trying to restore, and copy the previous .frm files inside this folder as shown below:
cp –r /tmp/ib_logfile* /var/lib/mysql/
cp –r /tmp/schema_name/*.frm /var/lib/mysql/schema_name/
Finally we done,restart the MySQL server.

service mysqld restart
  • Share This:  
  •  Facebook
  •  Twitter
  •  Google+
  •  Stumble
  •  Digg
Email ThisBlogThis!Share to XShare to Facebook
Newer Post Older Post Home

0 comments:

Post a Comment

Deals on Amazon

Mobiles on Amazon

About Me

My photo
Anil Joshi
New Delhi, Delhi, India
I am a simple man with little knowledge of computer software and Tehnology.
View my complete profile

Popular Posts

  • SELinux mechanism in linux environment.
    Security-Enhanced Linux (SELinux) is a mandatory access control (MAC) security mechanism implemented in the kernel. SELinux was first ...
  • Complete uninstallation of Mysql Server on centos
    Step 1: Check list the mysql rpm which is installed on server rpm -qa | grep mysql or yum list installed | grep mysql Step 2 : Re...
  • Finding and preventing ddos attack on Linux Server
    A distributed denial-of-service (DDoS) attack occurs when multiple systems flood the bandwidth or resources of a targeted system, usual...
  • Mysql Security
    Improved  MySQL Database Se curity 1. Secure your server Many known attacks are possible only once physical access to a machi...
  • Dual Master Replication(Master-Master)
    MySQL replication is the most flexible way to deal with scalability and availability. The most common problem with replication is ...
  • Tracing the execution of processes using strace
    Strace is quite simply a tool that traces the execution of system calls. It can trace the execution of a binary from start to end, and ou...
  • Check and Repair Mysql Tables
      Check a Specific Table in a Database If your application throws an error message saying that a specific table is corrupted, exe...
  • Simple bash script's for managing mysql
    1. Monitoring replication health #!/bin/bash sql_thread_running=$(mysql -e "show slave status\G" | awk -F":" ...
  • Mysql Installation using rpm package
        1.Download the required rpm filess MySQL-client-community-5.1.25-0.rhel5.i386.rpm MySQL-server-community-5.1.25-0.rhel5.i386....
  • Running select statement inside an update query
    Running select statement inside an update query You could use a non-correlated subquery to do the work for you: 1.  UPDATE tbl_1 d...
Best Shoes on amazon

Categories

  • Backups
  • Database Maintainance
  • Installation
  • Linux
  • Mysql
  • Recovery
  • Replication
  • Scripts
  • Security
  • SELinux
  • sql tricks
  • Strace

Pages

  • Home

Blog Archive

  • ▼  2016 (19)
    • ►  June (1)
    • ▼  May (18)
      • Finding and preventing ddos attack on Linux Server
      • Tracing the execution of processes using strace
      • SELinux mechanism in linux environment.
      • Simple bash script's for managing mysql
      • Running multiple mysql instance on single server(C...
      • Mysql Installation using rpm package
      • Check and Repair Mysql Tables
      • Dual Master Replication(Master-Master)
      • Filter Replication Variables
      • Master-Slave Replication without downtime
      • How to Export and Import files in Mysql
      • Recovering InnoDB MySQL Tables Data from ibdata ...
      • Check Database Size,Compare tables & column Differ...
      • Store Procedure & Views Backups
      • Mysql Security
      • MySQL Restart Failed
      • Running select statement inside an update query
      • Mass Killing Mysql Process

Sample Text

Copyright © Cool Dba | Powered by Blogger
Design by Hardeep Asrani | Blogger Theme by NewBloggerThemes.com | Distributed By Gooyaabi Templates