Steps to recover data when ibdata file gets corrupted or removed
ibdata is really important file in Mysql because it contains following data –
data dictionary – metadata for Innodb related objects.
double write buffer – protection against a crash in the middle of writing a block to disk.
rollback segments and MVCC data – Undo logs which are required if any transaction needs to be rolled back and also useful if any other transaction needs to see the original data (consistent reads).
And if we accidentally dropped the ibdata file, we definitely cannot rebuild it but we can safely recover all the data onto a new Mysql instance.
- First shut down mysql if its already running :
mysqladmin -uroot -p shut
- Let’s try to start it up to see the error we get
bash-4.1$ mysqld_safe &  13698 2017-05-29 20:35:10 14183 [Note] InnoDB: The first specified data file /var/lib/mysql/innodb/data/ibdata1 did not exist: a new database to be created! 2017-05-29 20:35:10 14183 [Note] InnoDB: Setting file /var/lib/mysql/innodb/data/ibdata1 size to 12 MB 2017-05-29 20:35:10 14183 [Note] InnoDB: Database physically writes the file full: wait... 2017-05-29 20:35:10 14183 [Note] InnoDB: Setting log file /var/lib/mysql/innodb/log/ib_logfile101 size to 48 MB 2017-05-29 20:35:10 14183 [ERROR] InnoDB: Cannot create /var/lib/mysql/innodb/log/ib_logfile1 2017-05-29 20:35:10 14183 [ERROR] Plugin 'InnoDB' init function returned error. 2017-05-29 20:35:10 14183 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 2017-05-29 20:35:10 14183 [ERROR] Unknown/unsupported storage engine: InnoDB 2017-05-29 20:35:10 14183 [ERROR] Aborting
As MySQL is instance is down and table(.frm) file is not readable, to view the structure of the table we need mysql-utilities which diagnostics the sql statements and inspect the information of the table.
Note: No special privileges are required to execute GET DIAGNOSTICS. This feature is available from MySQL 5.6.4 and above.
- For reading .frm files we need to install mysql-utilities this works only if we have latest version of mysql-connector-python, so download mysql-utilities binaries along with mysql-connector-python as below (working urls at the time i’m testing)
mysql-connector-python >= 2.0.0 is needed by mysql-utilities version we are using
wget https://cdn.mysql.com//Downloads/MySQLGUITools/mysql-utilities-1.6.5-1.el6.noarch.rpm wget https://cdn.mysql.com//Downloads/Connector-Python/mysql-connector-python-2.1.6-1.el6.x86_64.rpm
- Install the rpms (RHEL/Centos) :
rpm -ivh mysql-connector-python-2.1.6-1.el6.x86_64.rpm rpm -ivh mysql-utilities-1.6.5-1.el6.noarch.rpm
- use mysqlfrm binary to view the structure of the table from .frm file
mysqlfrm --diagnostic /var/lib/mysql/data db:mytable (this will get us the structure of the table)
Login to another instance where we need to restore this table –
- Create the table from the DDL obtained above and discard the tablespace (which removes the newly created ibd file).
CREATE TABLE db.table ( MEMBER_NUMBER int(11) NOT NULL, JOIN_DATE datetime NOT NULL, STATUS_CODE varchar(50) NOT NULL, STATUS_SYSDATE datetime NOT NULL, CLASS_CODE varchar(50) NOT NULL, BRAND_CODE varchar(50) NOT NULL, BIRTH_DATE datetime DEFAULT NULL, NAME_PREFIX varchar(50) DEFAULT NULL, FIRST_NAME varchar(1000) DEFAULT NULL, MIDDLE_NAME varchar(1000) DEFAULT NULL, LAST_NAME varchar(1000) DEFAULT NULL, ) ENGINE=InnoDB; alter table db.table discard tablespace;
- Copy ibd file from the failed database to the new instance –
scp /var/lib/mysql/data/db/table.ibd [email protected]:/var/lib/mysql/data/db/. chown mysql:mysql table.ibd chmod 660 table.ibd
- Import tablespace –
alter table db. mytable import tablespace;
Repeat the above process for all the tables to be recovered.