skip to Main Content

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 for jobs`.`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 for jobs.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 for jobs`.`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 for jobs.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 for jobs`.`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 for jobs.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 for jobs`.`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 for jobs.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 for jobs`.`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 for jobs.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 for jobs`.`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 for jobs.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 for jobs`.`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 for jobs.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 for jobs`.`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 for jobs.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 for jobs`.`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 for jobs.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 for jobs`.`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 for jobs.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 for jobs`.`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 for jobs.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 for jobs`.`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 for jobs.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 for projects`.`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 for projects.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 for projects`.`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 for projects.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 for projects`.`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 for projects.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 for projects`.`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 for projects.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 for projects`.`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 for projects.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 for projects`.`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 for projects.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


  1. Chosen as BEST ANSWER

    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.


  2. 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,

    1. take snapshot before any change

    2. 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)

    mysqld --console --user=mysql --initialize --datadir=/tmp/mysql
    cp -p /tmp/mysql/ib* /var/lib/mysql
    vi /etc/my.cnf
    

    add one line: innodb_force_recovery=6

    systemctl start mysql
    mysqldump --all-databases > alldb.sql
    
    1. 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)

    mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '7layer';
    mysql> exit;
    
    1. restore data

      mysql < alldb.sql

    (replace the sql file name if needed)

    1. restore user privileges

      mysql> flush privileges;

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search