I have a mysqldump of all databases, including mysql.

I'm using mysql 5.6 on Windows 2008 R2. How can I restore my backup ?

The problem is that I cannot start the server when the database mysql is missing.

Edit: This is what happened exactly:

I got this error while running some query:

[ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space

I tried to restart mysqld, but it did not restart because of this error in the log:

InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 2568.
InnoDB: You may have to recover from a backup.

2 Answers 2

  1. I included this line in my.ini according to the doc http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html: innodb_force_recovery = 1
    The server started
  2. I created a dump of each database using mysqldump
  3. According to http://blackbird.si/mysql-corrupted-innodb-tables-recovery-step-by-step-guide/, I ran a database check with mysqlcheck --all-databases and found the offending table.
  4. I tried to delete that table, but it was linked with primary keys (mysql -u ... -p... -e "drop database ..."). I deleted the depending tables, but with no luck, mysqld still refused to start with innodb_force_recovery = 0

I decided to start off from scratch to be sure to get a clean database back.

  1. I deleted everything in the data directory (keeping a backup copy).
  2. I initialized the innodb datafiles using mysqld --initialize, according to https://dev.mysql.com/doc/refman/5.7/en/data-directory-initialization-mysqld.html
  3. I copied the two system database files from the backup (directories mysql and performance_schema)
    Now the server started again with these warnings in the log:

    [Warning] InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
    [Warning] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened.

    the same for mysql/slave_relay_log_info and mysql/slave_worker_info

  4. According to https://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html, I deleted the three files mysql/slave_master_info.frm, mysql/slave_relay_log_info.frm and mysql/slave_worker_info.frm
  5. I imported the three tables from the dump of the mysql database.

At this point I got a working mysql server with a clean database. The olny thing that remained was to import the dumps that I made previously, using mysql -u root -p... mydb < backup-mysql\mydb.sql

And thank God, after one hour of interruption, everything was working again.


create a .cmd file and run the file.

"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump.exe" -uroot -ppassword dbname --result-file="C:\backup\dump.sql"

Edit the dump file to suit your need.

Copy this and paste on text file.

"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe" -uroot -ppassword dbname < "C:\backup\dumpname.sql"

Name the text file .cmd and then run it


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.