4

If I could have made a proper SQL dump/backup I would have, but OS gave out and could only log into SafeMode, where I couldn't start the MySQL server. Therefore, I could only manually backup the file directories.

  • I have a saved file copy of C:\ProgramData\MySQL\MySQL Server 8.0
  • I have a saved file copy of C:\Program Files\MySQL\MySQL Server 8.0

I'm now running on a new installation of Windows and trying to start this old server up for a few minutes so I can log on from Workbench and make a proper SQL database dump which will I import on a new MySQL installation later.

Consequently, moving the mentioned files in their respective locations - Program Files and ProgramData.

Opening up PowerShell (with Admin rights) and doing:

mysqld --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0" --console

and I get a few hundred lines on the screen that contain the following:

2020-04-10T05:28:56.725818Z 0 [ERROR] [MY-011972] [InnoDB] Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html for information about forcing recovery. 2020-04-10T05:28:56.734460Z 0 [ERROR] [MY-011971] [InnoDB] Tablespace 'innodb_undo_001' Page [page id: space=4294967279, page number=5] log sequence number 1339677380 is in the future! Current system log sequence number 19081380.

Followed by:

2020-04-10T05:28:56.740834Z 0 [ERROR] [MY-011972] [InnoDB] Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html for information about forcing recovery. 2020-04-10T05:28:56.757259Z 0 [System] [MY-010229] [Server] Starting crash recovery... 2020-04-10T05:28:56.769215Z 0 [System] [MY-010232] [Server] Crash recovery finished. 2020-04-10T05:28:57.695386Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2020-04-10T05:28:57.765911Z 0 [System] [MY-010931] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: ready for connections. Version: '8.0.13' socket: '' port: 3306 MySQL Community Server - GPL. 2020-04-10T05:28:57.926850Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060

Great, at least its running.

I can now connect but no databases.

  • What can I do / how should I proceed to successfully get those databases back temporarily?

UPDATE:

Removed the innodb_undo_001 and innodb_undo_002 files from the Data directory and relaunched.

Getting this:

PS C:\Program Files\MySQL\MySQL Server 8.0\bin> .\mysqld --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" --console 2020-04-10T05:55:29.555478Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release. 2020-04-10T05:55:29.558313Z 0 [System] [MY-010116] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe (mysqld 8.0.13) starting as process 3028 2020-04-10T05:55:31.394427Z 1 [Warning] [MY-012351] [InnoDB] Tablespace 1, name 'sys/sys_config', file '.\sys\sys_config.ibd' is missing! 2020-04-10T05:55:32.808645Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2020-04-10T05:55:32.856154Z 0 [System] [MY-010931] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: ready for connections. Version: '8.0.13' socket: '' port: 3306 MySQL Community Server - GPL. 2020-04-10T05:55:32.898055Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060

Running but no databases present.

Tried the Innodb-force-recovery approach - nothing happens. Same as before.

Improve this question
4
  • Have you tried with Innodb-force-recovery? Commented Apr 16, 2020 at 8:18
  • @tcadidot0 I did yes. Nothing. No errors, no recovery, no databases. Commented Apr 16, 2020 at 9:10
  • Do you have the full table info including ibdata1 in the MySQL 8.0/data folder? Commented Apr 16, 2020 at 9:17
  • @tcadidot0 Yup. Windows crashed - 1 hr later I backed everything up so data is intact and all the folders and files are present and where they're supposed to be. Nothing is missing. Commented Apr 16, 2020 at 13:05

2 Answers 2

Reset to default
3
+50

Ok, assuming that all the files are not corrupted, you can try the following:

  1. Create a new folder (in a different drive if possible). Name it as test or something. Then copy all files from the C:\Program Files\MySQL\MySQL Server 8.0 directory into the new folder.

enter image description here

*note: if you don't have my.ini file in the folder, copy it from the C:\ProgramData\MySQL\MySQL Server 8.0 directory into the folder.

  1. Open my.ini file and change the following: enter image description here

  2. Go back to the top of my.ini file and change: enter image description here after changing above, copy the whole line from mysqld --install MySQLXY .. to the end. Don't copy the first # at the beginning. Then save the my.ini file.

  3. Open command prompt (cmd) as administrator then do this: enter image description here

*installing the service manually using cmd.

  1. Go to services (keyboard shortcut windows button+R then type services.msc then press ENTER or OK), find the newly installed MySQL service and click start.

  2. Try connecting to db.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Same thing. No databases showing up after connecting to the server with Workbench. The server runs, no errors.
What does it log into the *.err file?
Hey there, sorry about the delay, Easter holidays and all. I've awarded the bounty since you put some effort in your answer and might help someone else down the line with a similar issue. Like I said previously, it throws NO ERRORS - everything (including by way of your explanation) works fine in the sense that the server installs as a service, starts up, I can connect to it, and so forth. What I'm saying is it doesn't show me ANY of the databases it should - like its ignoring them completely. It's acting as a fresh, new installation without any databases. I'll keep looking, thanks!
I'm just afraid that it's unrecoverable. I've had that experience a few times before and since then I kept this link just in case, maybe this will help you dba.stackexchange.com/questions/16875/… .
Could be. Yup, that's what I came across also while searching these past few days. Thanks for the link!
-1

Check your mysql config file. Make sure the datadir goes to the correct folder (C:\ProgramData\MySQL\MySQL Server 8.0) or wherever you saved put that now

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.