Several MySQL users have reported receiving an error "cp: error reading 'ibdata1': Input/output" when trying to copy the files in the MySQL datadir.
The error is followed by another error message stating that the ib_logfile cannot be copied. The complete error message reads as ""cp: error reading 'ib_logfile1': Input/output error".
What Causes the Error?
The error occurs due to faulty hardware or bad sectors on the hard disk. You may face problems with the hardware due to sudden system shutdown during extensive read and write operations, overheating, etc. The hardware error can turn the ibdata1 corrupt.
Steps to Fix the ""cp: error reading 'ibdata1' input/output" Error
Follow the steps in the sequence given below to resolve the error:
Step 1 ? You can try to copy the ibdata1 and other files (ibd and frm) by ignoring the read (I/O) errors. To do so, run the following disk dump (dd) command:
# dd if=/var/lib/mysql/ibdata1 of=/disk1/ibdata1 conv=noerror
The dd command creates bit by bit copy of the problematic ibdata1 file (/var/lib/mysql/ibdata1) to the output file (/disk1/ibdata1).
Further, when dd is used with conv=noerror, it writes nothing in the output file if there's a bad block.
Step 2 ? After completing the copying operation, check if you can start the MySQL server by running innodb_force_recovery. You can run innodb_force_recovery with values 1 till 6 until the server starts. If it starts, take mysqldump using the following command:
Note: MySQL may crash during the process. To avoid this, try taking a dump (backup) of one table at a time.
mysqldump -u -p db_name tbl_name > dumpfilename.sql
Here, u stands for username and p for password, used for connecting to the MySQL server. Further, db_name is the database name that contains the table (tbl_name) you need to back up, and dumpfilename.sql is the path and the backup file's name.
Learn in detail how to backup and restore MySQL databases using mysqldump command.
What if MySQL Server Doesn't Start?
You cannot recover the MySQL table if the server doesn't start. This is where a MySQL repair tool like Stellar Repair for MySQL comes in handy. The tool helps in repairing databases created using InnoDB and MyISAM storage engines.
Here's how the software works:
- Launch the Stellar Repair for MySQL software after downloading and installing it.
- Select your MySQL version and click Browse to select the data folder containing the ibdata1 file to be repaired. Click OK.
- Select the MySQL database(s) you want to repair and press the Repair.
- The software shows the progress of the repair process.
- When a 'Repair Complete' message box appears, click OK.
- The software previews the recoverable components in the repaired file.
- Select the database objects you need to restore and then click Save Database from the File.
- From the 'Save Database' dialog box, choose MySQL as the file format for saving the repaired file and then click Save.
Note: You can also save the repaired file in CSV, XLS, or HTML file format.
Once the repaired file is saved, you will see a 'Save Complete' message box.
Conclusion
The error "cp: error reading 'ibdata1' input/output" usually occurs due to hardware failure and can turn the ibdata1 corrupt. Try copying the ibdata1 and other data files to some other drive using the dd command. If the MySQL server crashes during the process, you will need to run innodb_force_recovery to start the server. If you fail to recover the data, Stellar Repair for MySQL software can help repair and restore data from the corrupted data file.