My mysql database crashed, how to recover

Question

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.

Answer 1

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.

Answer 2

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