I copied the data directory from mysql folder and uninstalled the XAMPP as the MySQL was not starting. I installed XAMPP same version and backed up the data folder into mysql data directory. At first only copied the required database folder named production inside the mysql data directory. PhpMyAdmin showed the tables correctly though no record/row was there and it showed "Table doesn’t exist in engine". Then I copied all the contents from the DATA folder to the mysql data folder and now it does not start. Now the problem is MySQL is not starting. I tried setting the
innodb_force_recovery but it failed to start the mysql.
The error log is given below:
2021-06-11 0:08:23 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2021-06-11 0:08:23 0 [Note] InnoDB: Uses event mutexes
2021-06-11 0:08:23 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-06-11 0:08:23 0 [Note] InnoDB: Number of pools: 1
2021-06-11 0:08:23 0 [Note] InnoDB: Using SSE2 crc32 instructions
2021-06-11 0:08:23 0 [Note] InnoDB: Initializing buffer pool, total size = 16M, instances = 1, chunk size = 16M
2021-06-11 0:08:23 0 [Note] InnoDB: Completed initialization of buffer pool
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation.
2021-06-11 0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2021-06-11 0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: ‘.jobsapplied.ibd’ OS error: 203
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation.
2021-06-11 0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11 0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file forjobs`.`applied
. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11 0:08:23 0 [Warning] InnoDB: Ignoring tablespace forjobs
.applied
because it could not be opened.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation.
2021-06-11 0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2021-06-11 0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: ‘.jobsdata.ibd’ OS error: 203
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation.
2021-06-11 0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11 0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file forjobs`.`data
. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11 0:08:23 0 [Warning] InnoDB: Ignoring tablespace forjobs
.data
because it could not be opened.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation.
2021-06-11 0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2021-06-11 0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: ‘.jobsdata_employer.ibd’ OS error: 203
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation.
2021-06-11 0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11 0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file forjobs`.`data_employer
. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11 0:08:23 0 [Warning] InnoDB: Ignoring tablespace forjobs
.data_employer
because it could not be opened.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation.
2021-06-11 0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2021-06-11 0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: ‘.jobseducation.ibd’ OS error: 203
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation.
2021-06-11 0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11 0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file forjobs`.`education
. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11 0:08:23 0 [Warning] InnoDB: Ignoring tablespace forjobs
.education
because it could not be opened.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation.
2021-06-11 0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2021-06-11 0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: ‘.jobseducation_list.ibd’ OS error: 203
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation.
2021-06-11 0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11 0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file forjobs`.`education_list
. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11 0:08:23 0 [Warning] InnoDB: Ignoring tablespace forjobs
.education_list
because it could not be opened.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation.
2021-06-11 0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2021-06-11 0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: ‘.jobsindustry.ibd’ OS error: 203
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation.
2021-06-11 0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11 0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file forjobs`.`industry
. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11 0:08:23 0 [Warning] InnoDB: Ignoring tablespace forjobs
.industry
because it could not be opened.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation.
2021-06-11 0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2021-06-11 0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: ‘.jobsjobs.ibd’ OS error: 203
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation.
2021-06-11 0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11 0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file forjobs`.`jobs
. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11 0:08:23 0 [Warning] InnoDB: Ignoring tablespace forjobs
.jobs
because it could not be opened.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation.
2021-06-11 0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2021-06-11 0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: ‘.jobslogin.ibd’ OS error: 203
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation.
2021-06-11 0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11 0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file forjobs`.`login
. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11 0:08:23 0 [Warning] InnoDB: Ignoring tablespace forjobs
.login
because it could not be opened.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation.
2021-06-11 0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2021-06-11 0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: ‘.jobslogin_admini.ibd’ OS error: 203
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation.
2021-06-11 0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11 0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file forjobs`.`login_admini
. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11 0:08:23 0 [Warning] InnoDB: Ignoring tablespace forjobs
.login_admini
because it could not be opened.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation.
2021-06-11 0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2021-06-11 0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: ‘.jobslogin_employer.ibd’ OS error: 203
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation.
2021-06-11 0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11 0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file forjobs`.`login_employer
. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11 0:08:23 0 [Warning] InnoDB: Ignoring tablespace forjobs
.login_employer
because it could not be opened.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation.
2021-06-11 0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2021-06-11 0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: ‘.jobsresume.ibd’ OS error: 203
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation.
2021-06-11 0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11 0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file forjobs`.`resume
. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11 0:08:23 0 [Warning] InnoDB: Ignoring tablespace forjobs
.resume
because it could not be opened.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation.
2021-06-11 0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2021-06-11 0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: ‘.jobsselected.ibd’ OS error: 203
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation.
2021-06-11 0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11 0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file forjobs`.`selected
. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11 0:08:23 0 [Warning] InnoDB: Ignoring tablespace forjobs
.selected
because it could not be opened.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation.
2021-06-11 0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2021-06-11 0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: ‘.projectsentries.ibd’ OS error: 203
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation.
2021-06-11 0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11 0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file forprojects`.`entries
. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11 0:08:23 0 [Warning] InnoDB: Ignoring tablespace forprojects
.entries
because it could not be opened.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation.
2021-06-11 0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2021-06-11 0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: ‘.projectsgeoname.ibd’ OS error: 203
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation.
2021-06-11 0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11 0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file forprojects`.`geoname
. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11 0:08:23 0 [Warning] InnoDB: Ignoring tablespace forprojects
.geoname
because it could not be opened.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation.
2021-06-11 0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2021-06-11 0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: ‘.projectsgeonamebackup.ibd’ OS error: 203
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation.
2021-06-11 0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11 0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file forprojects`.`geonamebackup
. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11 0:08:23 0 [Warning] InnoDB: Ignoring tablespace forprojects
.geonamebackup
because it could not be opened.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation.
2021-06-11 0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2021-06-11 0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: ‘.projectspincodes.ibd’ OS error: 203
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation.
2021-06-11 0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11 0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file forprojects`.`pincodes
. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11 0:08:23 0 [Warning] InnoDB: Ignoring tablespace forprojects
.pincodes
because it could not be opened.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation.
2021-06-11 0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2021-06-11 0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: ‘.projectsspecifier.ibd’ OS error: 203
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation.
2021-06-11 0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11 0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file forprojects`.`specifier
. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11 0:08:23 0 [Warning] InnoDB: Ignoring tablespace forprojects
.specifier
because it could not be opened.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 3 in a file operation.
2021-06-11 0:08:23 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2021-06-11 0:08:23 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2021-06-11 0:08:23 0 [ERROR] InnoDB: Cannot open datafile for read-only: ‘.projectsusers.ibd’ OS error: 203
2021-06-11 0:08:23 0 [ERROR] InnoDB: Operating system error number 203 in a file operation.
2021-06-11 0:08:23 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-06-11 0:08:23 0 [ERROR] InnoDB: Could not find a valid tablespace file forprojects`.`users
. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
2021-06-11 0:08:23 0 [Warning] InnoDB: Ignoring tablespace forprojects
.users
because it could not be opened.
2021-06-11 0:08:23 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2021-06-11 0:08:23 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2021-06-11 0:08:23 0 [Note] InnoDB: Setting file ‘D:Program FilesXAMPmysqldataibtmp1’ size to 12 MB. Physically writing the file full; Please wait …
2021-06-11 0:08:23 0 [Note] InnoDB: File ‘D:Program FilesXAMPmysqldataibtmp1’ size is now 12 MB.
2021-06-11 0:08:23 0 [Note] InnoDB: Waiting for purge to start
2021-06-11 0:08:23 0 [Note] InnoDB: 10.4.19 started; log sequence number 1515509790; transaction id 611499
2021-06-11 0:08:23 0 [Note] InnoDB: Loading buffer pool(s) from D:Program FilesXAMPmysqldataib_buffer_pool
2021-06-11 0:08:23 0 [Note] Plugin ‘FEEDBACK’ is disabled.
2021-06-11 0:08:23 0 [Note] Server socket created on IP: ‘::’.
2
Answers
Was able to get back the data.
Step 1: Move all the file from mysql/data directory to data_old directory except mysql and performance_schema directory.
Step 2: Copy back the ibdata1 file from backed up directory also copy back the folder with the name of the database you want to restore.
Step 3: now start mysql, you will be able to see the database. use mysqldump to export to a sql file.
Step 4: Uninstall mysql / xampp. Re-install xampp.
Step 5: create a database with the name of the backed up database. and use mysqldump to restore the data.
Also remember phpMyAdmin may not work after deleting it's database, so you may need to switch to console.
In my case, this solution works:
If the id* files are removed, and there is no snapshot/backup, then re-init mysql would be needed,
take snapshot before any change
if already mysqldump all databases, jump to step 2
mkdir /tmp/mysql
vi /etc/my.cnf (delete following variables if exist: innodb_file_per_table, innodb_force_recovery)
add one line:
innodb_force_recovery=6
initialize mysql
rm -Rf /var/lib/mysql/*
vi /etc/my.cnf (delete variables if exist: innodb_file_per_table, innodb_force_recovery)
mysqld –console –user=mysql –initialize –datadir=/var/lib/mysql
(the output will show temp password, for example, "A temporary password is generated for root@localhost: MWq.j0>RMdV>")
systemctl start mysql
mysql -u root -p
(login with the temp password, and set password for root — replace ‘7layer’ with the default password you currently have in ~/.my.cnf)
restore data
mysql < alldb.sql
(replace the sql file name if needed)
restore user privileges
mysql> flush privileges;