Created
June 24, 2025 07:14
-
-
Save delphinpro/7c21ce19102bfc771e4afd92acffc822 to your computer and use it in GitHub Desktop.
OSPanel 6: Batch database backup and recovery
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| @echo off | |
| set BASE_BACKUP_DIR=D:\dev\.local_databases | |
| set MYSQL_USER=root | |
| set MYSQL_PASSWORD= | |
| set MYSQL_HOST=mysql-8.0 | |
| set MYSQL_PORT=3306 | |
| set TIMESTAMP=%date:~-4,4%%date:~-7,2%%date:~0,2%_%time:~0,2%%time:~3,2% | |
| set TIMESTAMP=%TIMESTAMP: =0% | |
| set BACKUP_DIR=%BASE_BACKUP_DIR%\%TIMESTAMP% | |
| echo Backup to %BACKUP_DIR% | |
| if not exist "%BASE_BACKUP_DIR%" mkdir "%BASE_BACKUP_DIR%" | |
| if not exist "%BACKUP_DIR%" mkdir "%BACKUP_DIR%" | |
| set EXCLUDE_DBS=information_schema performance_schema mysql sys | |
| if "%MYSQL_PASSWORD%"=="" ( | |
| set AUTH_OPTIONS=-u%MYSQL_USER% | |
| ) else ( | |
| set AUTH_OPTIONS=-u%MYSQL_USER% -p%MYSQL_PASSWORD% | |
| ) | |
| for /f "tokens=1 delims= " %%a in ('mysql %AUTH_OPTIONS% -h%MYSQL_HOST% -P%MYSQL_PORT% -e "show databases" -s --skip-column-names') do ( | |
| echo %EXCLUDE_DBS% | find "%%a" >nul || ( | |
| echo Dumping database: %%a | |
| mysqldump %AUTH_OPTIONS% -h%MYSQL_HOST% -P%MYSQL_PORT% --routines --triggers --events --single-transaction %%a > "%BACKUP_DIR%\%TIMESTAMP%_%%a.sql" | |
| ) | |
| ) | |
| echo All databases have been backed up to %BACKUP_DIR% |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| @echo off | |
| set MYSQL_USER=root | |
| set MYSQL_PASSWORD= | |
| set MYSQL_HOST=mysql-8.0 | |
| set MYSQL_PORT=3306 | |
| set BASE_BACKUP_DIR=D:\dev\.local_databases | |
| if "%~1"=="" ( | |
| echo Error: Backup folder name parameter is required | |
| echo Usage: %0 [backup_folder_name] | |
| echo Example: %0 20250622_1729 | |
| goto end | |
| ) | |
| set BACKUP_FOLDER=%~1 | |
| set FULL_BACKUP_PATH=%BASE_BACKUP_DIR%\%BACKUP_FOLDER% | |
| if not exist "%FULL_BACKUP_PATH%" ( | |
| echo Backup directory does not exist: %FULL_BACKUP_PATH% | |
| goto end | |
| ) | |
| echo Restoring from %FULL_BACKUP_PATH% | |
| if "%MYSQL_PASSWORD%"=="" ( | |
| set AUTH_OPTIONS=-u%MYSQL_USER% | |
| ) else ( | |
| set AUTH_OPTIONS=-u%MYSQL_USER% -p%MYSQL_PASSWORD% | |
| ) | |
| for %%f in ("%FULL_BACKUP_PATH%\*.sql") do ( | |
| for /f "tokens=2* delims=_" %%a in ("%%~nf") do ( | |
| set DB_NAME=%%b | |
| echo Restoring database: %%b | |
| mysql %AUTH_OPTIONS% -h%MYSQL_HOST% -P%MYSQL_PORT% -e "CREATE DATABASE IF NOT EXISTS %%b" | |
| mysql %AUTH_OPTIONS% -h%MYSQL_HOST% -P%MYSQL_PORT% %%b < "%%f" | |
| if errorlevel 1 ( | |
| echo [ERROR] Failed to restore %%b | |
| ) else ( | |
| echo [SUCCESS] Restored %%b | |
| ) | |
| ) | |
| ) | |
| echo Restoration complete from %FULL_BACKUP_PATH% | |
| :end |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment