Repair MySQL data directory (for XAMPP)

repair-mysql-data.ps1

# Based on this answer: https://stackoverflow.com/a/61859561/1956278

# Backup old data
Rename-Item -Path "./data" -NewName "./data_old"

# Create new data directory
Copy-Item -Path "./backup" -Destination "./data" -Recurse
Remove-Item "./data/test" -Recurse
$dbPaths = Get-ChildItem -Path "./data_old" -Exclude ('mysql', 'performance_schema', 'phpmyadmin') -Recurse -Directory
Copy-Item -Path $dbPaths.FullName -Destination "./data" -Recurse
Copy-Item -Path "./data_old/ibdata1" -Destination "./data/ibdata1"

# Notify user
Write-Host "Finished repairing MySQL data"
Write-Host "Previous data is located at ./data_old"

Golden!

Thank you guise, this has been such an annoying beast that comes around more than once. Interesting though this has been on the books for 4 years now and still going strong.

you can add a line at the beginning of the script to delete the existing "data_old" directory before creating a new backup.

# Delete existing data_old directory
Remove-Item -Path "./data_old" -Recurse -Force

Thank you all!

Thanks

Thank you!

If you get error as I did about directories not existing, change the file to this, and if paths are different change the location of directories to your absolute path:

Based on this answer: https://stackoverflow.com/a/61859561/1956278

Backup old data

Rename-Item -Path "C:/xampp/mysql/data" -NewName "C:/xampp/mysql/data_old"

Create new data directory

Copy-Item -Path "C:/xampp/mysql/backup" -Destination "C:/xampp/mysql/data" -Recurse
Remove-Item "C:/xampp/mysql/data/test" -Recurse
$dbPaths = Get-ChildItem -Path "C:/xampp/mysql/data_old" -Exclude ('mysql', 'performance_schema', 'phpmyadmin') -Recurse -Directory
Copy-Item -Path $dbPaths.FullName -Destination "C:/xampp/mysql/data" -Recurse
Copy-Item -Path "C:/xampp/mysql/data_old/ibdata1" -Destination "C:/xampp/mysql/data/ibdata1"

Notify user

Write-Host "Finished repairing MySQL data"
Write-Host "Previous data is located at C:/xampp/mysql/data_old"

you are the best

Equivalent bash scripts is

`@echo off
REM Backup old data
rename "data" "data_old"

REM Create new data directory
xcopy "backup" "data" /E /I
rmdir /S /Q "data\test"

REM Copy directories excluding specific ones (mysql, performance_schema, phpmyadmin)
for /d %%i in (data_old*) do (
if /i not "%%~nxi"=="mysql" (
if /i not "%%~nxi"=="performance_schema" (
if /i not "%%~nxi"=="phpmyadmin" (
xcopy "%%i" "data%%~nxi" /E /I
)
)
)
)

REM Copy ibdata1 file
copy "data_old\ibdata1" "data\ibdata1"

REM Notify user
echo Finished repairing MySQL data
echo Previous data is located at ./data_old
`
Run this inside mysql directory, save it like repair.bat , easier to save using notepad++, select bat for file type

you saved my afternoon

Thank you!

One of the best gists

I realize existing Database was not able to access and getting error.

Equivalent bash scripts is

`@echo off REM Backup old data rename "data" "data_old"

REM Create new data directory xcopy "backup" "data" /E /I rmdir /S /Q "data\test"

REM Copy directories excluding specific ones (mysql, performance_schema, phpmyadmin) for /d %%i in (data_old*) do ( if /i not "%%~nxi"=="mysql" ( if /i not "%%~nxi"=="performance_schema" ( if /i not "%%~nxi"=="phpmyadmin" ( xcopy "%%i" "data%%~nxi" /E /I ) ) ) )

REM Copy ibdata1 file copy "data_old\ibdata1" "data\ibdata1"

REM Notify user echo Finished repairing MySQL data echo Previous data is located at ./data_old ` Run this inside mysql directory, save it like repair.bat , easier to save using notepad++, select bat for file type

I've edited this code a bit to include PWD support as my install had issues jumping between disks. ^^

@echo off
REM Change directory to the MySQL directory
cd /d "D:\Path\To\XAMPP\mysql"

REM Backup old data
rename "data" "data_old"

REM Create new data directory
xcopy "backup" "data" /E /I
rmdir /S /Q "data\test"

REM Copy directories excluding specific ones (mysql, performance_schema, phpmyadmin)
for /d %%i in (data_old\*) do (
    if /i not "%%~nxi"=="mysql" (
        if /i not "%%~nxi"=="performance_schema" (
            if /i not "%%~nxi"=="phpmyadmin" (
                xcopy "%%i" "data\%%~nxi" /E /I
            )
        )
    )
)

REM Copy ibdata1 file
copy "data_old\ibdata1" "data\ibdata1"

REM Notify user
echo Finished repairing MySQL data
echo Previous data is located at .\data_old
pause

Thank you, Great work !

@echo off
REM Change directory to the MySQL directory
cd /d "D:\Path\To\XAMPP\mysql"

REM Backup old data
rename "data" "data_old"

REM Create new data directory
xcopy "backup" "data" /E /I
rmdir /S /Q "data\test"

REM Copy directories excluding specific ones (mysql, performance_schema, phpmyadmin)
for /d %%i in (data_old\*) do (
    if /i not "%%~nxi"=="mysql" (
        if /i not "%%~nxi"=="performance_schema" (
            if /i not "%%~nxi"=="phpmyadmin" (
                xcopy "%%i" "data\%%~nxi" /E /I
            )
        )
    )
)

REM Copy ibdata1 file
copy "data_old\ibdata1" "data\ibdata1"

REM Notify user
echo Finished repairing MySQL data
echo Previous data is located at .\data_old
pause

Thanks, this code works correctly.