How to backup and restore MySQL database on crashed Windows PC?

Are you looking for how to backup and restore MySQL database on crashed Windows PC? Well, there are tools available for exporting MySQL database and also to import MySQL database if your computer is running normally. However, if the Windows OS is not working, but you can access the hard disk some way, then you can backup the MySQL database from file and restore it. In this post, we shall see How to backup and restore MySQL database from hard disk files on Crashed Windows PC?

Scenario:

1. Windows OS crashed. Hard disk is fine.
2. Unable to recover Windows OS / stuck in Windows boot loop / System Repair Loop
3. Need to reinstall Windows OS, but before that you need to take backup of the MySQL databases

We had a Windows PC running MySQL Server 5.5. Normally we used to use SQLYog to browser the database and also for backup/restore. But the Windows OS crashed and it was stuck in boot loop and system recovery loop. All attempts were made to revive the Windows PC but still the problem persisted.  The computer would not load into Safe Mode and other options from the boot menu as well. Other disk error checking utilities were also used but still the Windows PC would boot. In such a scenario, we can’t use the MySQL tools for taking backups. We also cannot use mysqldump command in such cases. So we needed to reinstall Windows OS, but before that we need to take backup of the MySQL databases.

How to backup MySQL database from Windows disk files?

If Windows OS is running fine, then you can take backup using MySQL tools like SQLYog or other MySQL browsers. Alternatively, you can also take backup from the disk files. Here is how.

1) Stop MySQL Service

• Click Win+R > Type ‘Services.msc‘ > Find MySQL Service
• Click on MySQL Service and stop the service.

2) Locate the MySQL Server data directory and backup

• Open Windows File Explorer
• Look for Program Data folder at C Drive. It is a hidden folder.
• If you do not see the Program Data, then you need to enable to show hidden folder in Windows.

How to show hidden files and folders in Windows 7?

• • Click the “Organize” button on Windows Explorer’s toolbar and select “Folder and search options” to open it.

  • Click the “View” tab at the top of the Folder Options window. Select “Show hidden files, folders, and drives” under Hidden files and folders. Click “OK” to save the new setting.

• Navigate to C:\ProgramData\MySQL\MySQL Server 5.5\data.
• You should be able to see folder names by the database names.
• Copy the entire ‘data‘ folder, which contains all the databases.
• From the copied data folder, delete the following files (otherwise, there may be issues restarting MySQL Service)
  • any *.err and *.pid
  • b_logfile0 and  b_logfile1 files

On the other PC or on the same PC after re-installing MySQL Server, just copy and paste the ‘data‘ folder backed up earlier to  C:\ProgramData\MySQL\MySQL Server 5.5\data replacing files and folders. Start the MySQL Service.

 

How to backup MySQL database from disk files of crashed Windows PC?

If your Windows OS is not booting due to the error described above or for any other reason, you would normally try to restore Windows. The most basic step in this process is to repair your computer. If the repair is successful, great, you can continue to backup MySQL database using the same tools you use. But if the Windows Repair fails, you can still try other methods to recover your Windows. After trying all means, if the OS is still unable to boot normally, then you try to open at least a command prompt in the process.

• Plug in a USB drive to the computer and Restart your computer.
• If Windows is still unable to boot, you may get an option to repair your computer. Select ‘Repair your computer’
• Wait for the repair. If you see ‘Startup Repair cannot repair your computer automatically ‘, then click on “View advanced options for system recovery and support“.
• After that, you will be taken back to the System Recovery Options window and from there select Command Prompt.
• Now at the command prompt, type ‘notepad‘.
• Click FILE > OPEN. This will allow you to browser Windows File Explorer.
  • If C:\ProgramData  folder is visible, navigate to C:\ProgramData\MySQL\MySQL Server 5.5\ and copy the ‘data’ folder to the USB drive.
  • If the ‘Program Data’ folder is not visible,  navigate to the pen drive. Create a new folder and name it appropriately.
• Come back to the Command Prompt.
• Type xcopy /e /c /h/ /i  “C:\ProgramData\MySQL\MySQL Server 5.5” E:\NameOfTheFolder and hit ENTER. (Here E is the USB drive letter).
• The data folder should be copied to the USB drive.
• To confirm, type notepad again in the Command Prompt.
• Click FILE > OPEN, and browse to the USB Drive and confirm if the data folder is copied inside the folder, and check the files within. Make sure that the FILE TYPE in the notepad is set to ‘All Files’ instead of the default ‘Text Document’ to see any type of files.
• Once you copy the ‘data‘ folder from C:\ProgramData\MySQL\MySQL Server 5.5\data to the USB Drive, you can exit the Notepad and command prompt.
• Shut down the computer.
• You can now attempt other means to recover the Windows or reinstall Windows from installation disk.
• Reinstall MySQL Server.

How to restore MySQL database from disk files

Following the same scenario of crashed Windows PC above, lets assume that you have reinstalled Windows  and reinstalled MySQL Server. Now to restore the MySQL data backup, follow the steps below.

1) Stop MySQL Service

• Click Win+R > Type ‘Services.msc‘ > Find MySQL Service
• Click on MySQL Service and stop the service.

2) Restore the ‘data’ backup to the base directory of MySQL Server

• Open Windows File Explorer
• Navigate to C:\ProgramData\MySQL\MySQL Server 5.5\
• Zip the existing ‘data’ folder (you can delete the data.zip later if the restored backup works)
• Now delete the data folder and replace with the data folder you backed up earlier.
• Open the data folder, and make sure the following files does not exist right inside the data folder (no need to look in the sub directories).
  • any *.err and *.pid
  • b_logfile0 and  b_logfile1 files

3) Start MySQL Service

• Click Win+R > Type ‘Services.msc‘ > Find MySQL Service
• Click on MySQL Service and start the service.

Now, connect to the database using your favorite MySQL browser tools and see if databases are visible and restored.

Note: If you want to backup the MySQL configuration, then you can also backup the my.ini file in the same way from C:\Program Files\MySQL\MySQL Server 5.5\