1

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.

bumped to the homepage by Community yesterday

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

0

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

  • I have two sets of dumps. One from a cronjob yesterday night, and one that I just made before using innodb_force_recovery=1 – Lorenz Meyer Feb 22 '17 at 14:30
  • create a name.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 file to suit your need. – Peter R Feb 22 '17 at 14:32
  • What is that file name.cmd ? – Lorenz Meyer Feb 22 '17 at 14:33
  • copy this and paste on text file. "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump.exe" -uroot -ppassword dbname --result-file="C:\backup\dumpname.sql" name the text file .cmd and then run it – Peter R Feb 22 '17 at 14:35
  • 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 – Peter R Feb 22 '17 at 14:50
0
  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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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